sparksql中使用with子查询，insert overwrite table的正确用法示例

qq_43193797

于 2023-08-01 16:36:07 发布

阅读量1.7k

点赞数

文章标签： sql

本文链接：https://blog.youkuaiyun.com/qq_43193797/article/details/132045638

版权

本文介绍了如何在Hive中启用动态分区(sethive.exec.dynamic.partition=true)以及设置非严格模式(sethive.exec.dynamic.partition.mode=nonstrict)，同时探讨了如何利用SparkSQL的适应性功能(setspark.sql.adaptive.enabled=true)和shuffle目标大小。通过with子句执行复杂查询后，结果被插入到表dws_biz.dws_biz_customer_cmpartition中，重点关注了`dmonth`分区。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

set hive.exec.dynamic.partition=true;--是否允许动态分区
set hive.exec.dynamic.partition.mode=nonstrict;--分区模式设置
set spark.sql.adaptive.enabled=true;
set spark.sql.adaptive.shuffle.targetPostShuffleInputSize=128MB;

with v_is_freight_outstanding_cust as(
    select sum(un_receive_fee_amount) as un_receive_fee_amount,      /**未收款额**/
           sum(market_cushion_amount) as market_cushion_amount,      /**垫款金额**/
    	   payment_customer_id,  
    	   dmonth   
    from table
    group by bill_base.payment_customer_id,bill_base.dmonth  
)

insert overwrite table dws_biz.dws_biz_customer_cm  partition(dmonth)
select *
from v_is_freight_outstanding_cust