with as insert overwrite table

最新推荐文章于 2023-10-18 11:16:14 发布

指南z

最新推荐文章于 2023-10-18 11:16:14 发布

阅读量4.2k

点赞数 8

分类专栏： hive mysql 文章标签： hive sql with insert

本文链接：https://blog.csdn.net/Adnerly/article/details/87857763

版权

hive 同时被 2 个专栏收录

6 篇文章 0 订阅

订阅专栏

mysql

2 篇文章 0 订阅

订阅专栏

习惯写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