with as insert overwrite table

最新推荐文章于 2025-09-05 16:22:55 发布

原创最新推荐文章于 2025-09-05 16:22:55 发布 · 4.6k 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#hive #sql #with #insert

hive 同时被 2 个专栏收录

6 篇文章

订阅专栏

mysql

2 篇文章

订阅专栏

本文解决了在使用WITH语法进行INSERT OVERWRITE操作时遇到的ParseException错误。通过调整SQL语句结构，即将WITH子句放置于INSERT OVERWRITE之前，成功避免了语法冲突，实现了数据的正确覆盖写入。

习惯写sql的时候把insert overwrite table 写在首行,但是在使用with 语法的时候报错:

Error while compiling statement: FAILED: ParseException line 2:0 cannot recognize input near 'with' 't1' 'as' in statement

最初还以为是insert时不支持with语法,后来看with语法的文档时才发现正确用法是:with as xxx (xxx) insert overwrite table select xxx.

原sql:

insert overwrite table iptv_mis_car_registration_ELFV
with  t1 as (select mis,production_month,cnt    from
    iptv_mis_car_registration_CTED )
select 0 as mis,production_month,sum(case when mis>=0 then cnt else 0 end) cnt  from t1 GROUP BY production_month  ORDER BY production_month union all
select 1 as mis,production_month,sum(case when mis>=1 then cnt else 0 end) cnt  from t1 GROUP BY production_month  ORDER BY production_month union all
select 2 as mis,production_month,sum(case when mis>=2 then cnt else 0 end) cnt  from t1 GROUP BY production_month  ORDER BY production_month union all
select 3 as mis,production_month,sum(case when mis>=3 then cnt else 0 end) cnt  from t1 GROUP BY production_month  ORDER BY production_month union all
select 4 as mis,production_month,sum(case when mis>=4 then cnt else 0 end) cnt  from t1 GROUP BY production_month  ORDER BY production_month

修改后的sql:

with  t1 as (select mis,production_month,cnt    from
    iptv_mis_car_registration_CTED )
insert overwrite table iptv_mis_car_registration_ELFV
select 0 as mis,production_month,sum(case when mis>=0 then cnt else 0 end) cnt  from t1 GROUP BY production_month  ORDER BY production_month union all
select 1 as mis,production_month,sum(case when mis>=1 then cnt else 0 end) cnt  from t1 GROUP BY production_month  ORDER BY production_month union all
select 2 as mis,production_month,sum(case when mis>=2 then cnt else 0 end) cnt  from t1 GROUP BY production_month  ORDER BY production_month union all
select 3 as mis,production_month,sum(case when mis>=3 then cnt else 0 end) cnt  from t1 GROUP BY production_month  ORDER BY production_month union all
select 4 as mis,production_month,sum(case when mis>=4 then cnt else 0 end) cnt  from t1 GROUP BY production_month  ORDER BY production_month union all
select 5 as mis,production_month,sum(case when mis>=5 then cnt else 0 end) cnt  from t1 GROUP BY production_month  ORDER BY production_month union all
select 6 as mis,production_month,sum(case when mis>=6 then cnt else 0 end) cnt  from t1 GROUP BY production_month  ORDER BY production_month union all
select 7 as mis,production_month,sum(case when mis>=7 then cnt else 0 end) cnt  from t1 GROUP BY production_month  ORDER BY production_month union all
select 8 as mis,production_month,sum(case when mis>=8 then cnt else 0 end) cnt  from t1 GROUP BY production_month  ORDER BY production_month union all
select 9 as mis,production_month,sum(case when mis>=9 then cnt else 0 end) cnt  from t1 GROUP BY production_month  ORDER BY production_month