TOP

大数据踩过的坑——Hive insert
2018-12-02 17:28:11 】 浏览:784
Tags:数据 Hive insert

版权声明: https://blog.csdn.net/xueyao0201/article/details/79374065

我在对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
from 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 (
col_1,col2,col3
)

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

但是目标表db_name.table_name_1包含col_1,col_2,col_3,col_4,col_5等多个字段。上述insert语句执行后,又报了如下错误:

  • Error while compiling statement: FAILED: NullPointerException null

错误原因:Hive SQL中的Insert不支持插入部分字段

解决方案:将字段补全,没有数据的字段插入空值,修改如下:

with temp_table_1 as (
select id,col_2
from 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 (
col_1,col_2,col_3,col_4,col_5
)
select a.id,a.col_2,b.col_3,null,null
from temp_table_1 a

left join temp_table_2 b on a.id= b.id



大数据踩过的坑——Hive insert https://www.cppentry.com/bencandy.php?fid=117&id=193295

】【打印繁体】【投稿】【收藏】 【推荐】【举报】【评论】 【关闭】 【返回顶部
上一篇hive json数据生成和处理 下一篇hive内置操作符与内置函数