大数据踩过的坑——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 (
        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
    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 (
        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