It is possible insert data into a table A from another B by subquery in HIVE?

[#1]

Hello,

I want execute the follow sql :

INSERT INTO TABLE db_h_gss.tb_h_teste_insert values( teste_2, teste_3, teste_1, PARTITION (cod_index=1) ) from SELECT CAST(teste_1 AS VARCHAR(200)), CAST(teste_2 AS VARCHAR(200)), CAST(teste_3 AS VARCHAR(200)), FROM db_h_gss.tb_h_teste_orig limit 10;

It's possible, insert with subquery? This command doesnt work, i tried many ways and none worked.

This is the create table (destination)

CREATE TABLE db_h_gss.tb_h_teste_insert (teste_1 VARCHAR(200), teste_2 VARCHAR(200), teste_3 VARCHAR(200) ) PARTITIONED BY(cod_index int ) STORED AS ORC;

Thanks

[#2]

Yeah I still don't get what you get out of it, you could just add a comment to it with the column names of the target table. But I don't see any functional enhancement.

Regarding Eugene's link I just tried it, essentially the trick is if your target table has more columns than your select and you want to set the remainder null you can now select a subset of columns in the target table. Like:

insert into test30 (id) select * from test31;

insert into test34 (id_target) select id from test31;

In this case any additional column in the target table is null.

However I just tried it with partitioned tables and it doesn't seem to work for me. Looks like a bug. You could open a support case if that doesn't work. However for your usecase where you have all columns in both tables I really don't see the point.

insert into table test32(id) partition(dt=3) select * from test31;

NoViableAltException(192@[])

at org.apache.hadoop.hive.ql.parse.HiveParser.regularBody(HiveParser.java:41226)

at org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpressionBody(HiveParser.java:40421)

...

[#3]

it should be insert into table test32 partition(dt=3) (id) select * from test31;

[#4]

ah thanks.

[#5]

Well, i give up, i will use the command that works....heheh

thanks to all...

[#6]

Hello! The WITH HQL clause (inherited from SQL) may help you be a bit more explicit in your transformations and accomplish something similar to your case 2:

WITH TABLE1 
AS
(
	SELECT 
	cod_index,
	CAST(teste_1 AS VARCHAR(200)), 
	CAST(teste_2 AS VARCHAR(200)), 
	CAST(teste_3 AS VARCHAR(200))
	FROM db_h_gss.tb_h_teste_orig
)
INSERT INTO TABLE db_h_gss.tb_h_teste_insert PARTITION (cod_index = 1)
SELECT
	teste_1,
	teste_2,
	teste_3
FROM TABLE1 WHERE cod_index = 1;