Hive那些事儿之八-大数据踩过的坑——Hive insert
我在对Hive表数据清洗后,使用了如下SQL将结果集插入到新表中:
insert into db_name.table_name_1 (
col_1,col2,col3
)
with temp_table_1 as (
select id,col_2
from db_name.table_name_2 where id = condatition
),
temp_table_2 as (
select id,col_3
from db_name.table_name_3 where id = condatition
)
select a.id,a.col_2,b.col_3
from temp_table_1 a
left join temp_table_2 b on a.id= b.id
出现了如下报错信息:
Error while compiling statement: FAILED: ParseException line 15:0 cannot recognize input near ‘with’ ‘temp_table_1’ ‘as’ in statement
错误原因:
Hive是支持with语法的,但是当与insert搭配使用时,语法与标准SQL语法规则不一样,需要将with放在insert之前,如下所示:
with temp_table_1 as (
select id,col_2
f rom db_name.table_name_2 where id = condatition
),
temp_table_2 as (
select id,col3
from db_name.table_name_3 where id = condatition
)
insert into db_name.table_name_1 (