UPDATE/DELETE/INSERT OVERWRITE in Hive table using Presto

Presto

UPDATE/DELETE/INSERT OVERWRITE in Hive table using Presto

Hi All,

I'm trying to read data from Hive table, apply some logic and load data into another hive table. We have Teradata Query Grid (version 1.2) and Presto (version 148t). I'm trying to achive it by FOREIGN TABLE syntax.

When I'm trying to DELETE/ INSERT OVERWRITE the hive table it's not allowing me and saying syntax error.

 

How can I delete from /drop/insert overwrite hive table using FOREIGN TABLE syntax?

DROP FOREIGN TABLE "default".ips_target@presto_server;
----------------------------------------------------------------------------------
INSERT OVERWRITE "default".ips_target@presto_server
(C1
,C2
,C3
,C4
,C5
,C6
,C7
,C8
,C9
)
SELECT 
C1
,C2
,C3
,C4
,C5
,C6
,C7
,C8
,C9
FROM FOREIGN TABLE (SELECT * FROM "default".ips_tmp_target)@presto_server ips_tmp_target
;

Please help.

 

Thanks & Regards,

Arpan.

 

1 REPLY
Teradata Employee

Re: UPDATE/DELETE/INSERT OVERWRITE in Hive table using Presto

Hi there,

 

You cannot execute DELETE / DROP statements using FOREIGN TABLE syntax. Instead, what you can do is pass the query down to directly run on Presto. From the documentation, here is one example of how to do it:

 

To run a pass-through query directly on Presto:

SELECT * FROM FOREIGN TABLE(
    SELECT count(*) as c
    FROM nation
)@presto_server AS presto_query


So for your case, you can execute the following to drop the table:

SELECT * FROM FOREIGN TABLE (DROP TABLE "default”.ips_target)@presto_server AS presto_query;


For INSERT, you can either run a pass-through query or I believe that you can directly run the INSERT statement on the FOREIGN TABLE like this:

INSERT INTO "default".ips_target@presto_server SELECT * FROM FOREIGN TABLE "default".ips_source@presto_server;

Also, note that INSERT OVERWRITE is not directly supported in Presto.

 

Hope this helps,
Amruta