CODEWITHSUNDEEP
sqlhadoophivetemp-tables
Bruno
Bruno

Reputation: 1471

Create Temporary Table with Select and Values

I'm trying to create a temporary table in Hive as follows:

CREATE TEMPORARY TABLE mydb.tmp2 
AS SELECT * FROM (VALUES (0, 'abc')) 
AS T (id , mystr);

But that gives me the following error:

SemanticException [Error 10296]: Values clause with table constructor not yet supported

Is there another way to create a temporary table by explicitly and directly providing the values in the same command?

My ultimate goal is to run a MERGE command, and the temporary table would be inserted after the USING command. So something like this:

MERGE INTO mydb.mytbl
USING <temporary table>
...

Upvotes: 1

Views: 6632

Answers (2)

leftjoin
leftjoin

Reputation: 38325

Use subquery instead of temporary table:

MERGE INTO mydb.mytbl t
USING  (SELECT 0 as id, 'abc' as mystr) tmp on tmp.id = t.id

Upvotes: 1

Jainik
Jainik

Reputation: 2452

Hive does not support values constructor yet. You can achieve this using below query:

CREATE TEMPORARY TABLE mydb.tmp2 
AS SELECT 0 as id, 'abc' as mystr;

For merge, you can use temporary table as below:

merge into target_table
using ( select * from mydb.tmp2) temp
on temp.id = target_table.id
when matched then update set ...
when not matched then insert values (...);

Upvotes: 0

Related Questions