hive with

背景： 

1 DB2 ：  临时表 （会话级）  -- Store Procedure 执行完后，临时表数据自动清空。

 ( The DECLARE GLOBAL TEMPORARY TABLE statement defines a temporary table for the current session ) 

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_EMP
(

EMPNO CHAR(6) NOT NULL,
SALARY DECIMAL(9, 2),
BONUS DECIMAL(9, 2),
COMM DECIMAL(9, 2)

) ON COMMIT PRESERVE ROWS

 

2 ORACLE：临时表 （会话级）

EXECUTE IMMEDIATE 'DELETE FROM DW28_EP.GBL_TBL_INCR_TEMP';  --Store Procedure 执行完后，临时表不会清空。  需要手工清理。

CREATE GLOBAL TEMPORARY TABLE GBL_TBL_INCR_TEMP(
　　 HASHCODE VARCHAR2(4000)
　　,VERSIONID VARCHAR2(4000)
　　,SEQUENCENUMBER VARCHAR2(4000)
　　,CREATE_TIME DATE DEFAULT SYSDATE
　　,TS TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP
) ON COMMIT PRESERVE ROWS;

 

传统数据仓库，ETL 处理业务逻辑，用到临时表概率很大。 同样在HIVE中存复杂业务逻辑也会用到类似临时表的功能。 WITH部分功能类似临时表。（WIHT 比临时表功能弱，同一个 Statement 里具备临时表功能）

  

 

 

 

 

 

部分代码：

WITH T1 AS(
SELECT
FROM_UNIXTIME(UNIX_TIMESTAMP(EINDT,'yyyyMMdd'),'yyyy-MM-dd') AS STATS_DATE -- 统计日期
,EBELN AS EBELN -- 采购凭证号
,EBELP AS EBELP -- 采购凭证的项目编号
,MENGE AS MENGE -- 采购订单数量
,EINDT AS EINDT -- 项目交货日期
,0 DELAY_WARN_TH -- 到货延迟预警阈值
FROM DW580_ADM.A_MM_PURCHASE_ORDER_ERP_DETAIL
)
,T2 AS(
SELECT
EBELN
,EBELP
,COUNT(*) TRACKING_NOTE_COUNT -- 关联物流单数
,SUM(NVL(DELIVERY_QUANTITY,0)) DELIVERY_QUANTITY -- 物流单送货数量
,SUM(CASE WHEN LGSTS_STATUS = '30' THEN 1 ELSE 0 END ) CUR30_VALUE
FROM DW580_ODM.B_MM_TRACKING_NOTE
GROUP BY EBELN,EBELP
)
,T3 AS(
SELECT
T31.EBELN
,T31.EBELP
,CASE
WHEN T31.CUR30_VALUE=0 THEN '10'
WHEN T31.CUR30_VALUE>0 AND T31.CUR30_VALUE<T31.TRACKING_NOTE_COUNT THEN '20'
WHEN T31.CUR30_VALUE>0 AND T31.CUR30_VALUE=T31.TRACKING_NOTE_COUNT AND NVL(T31.DELIVERY_QUANTITY,0)<NVL(T32.MENGE,0) THEN '20'
WHEN T31.CUR30_VALUE>0 AND T31.CUR30_VALUE=T31.TRACKING_NOTE_COUNT AND NVL(T31.DELIVERY_QUANTITY,0)>=NVL(T32.MENGE,0) THEN '30'
END PO_LGSTS_STATUS
FROM T2 T31 LEFT JOIN T1 T32 ON T31.EBELN=T32.EBELN AND T31.EBELP=T32.EBELP
),
T4 AS (
SELECT
T41.EBELN
,T41.EBELP
,T41.ETA AS LATEST_ETA
FROM(
SELECT EBELN
,EBELP
,ETA
,ROW_NUMBER()OVER (PARTITION BY EBELN,EBELP ORDER BY ETA DESC) ROWNUMBER
FROM DW580_ODM.B_MM_TRACKING_NOTE
) T41
WHERE T41.ROWNUMBER=1
),
T5 AS (
SELECT
T51.EBELN
,T51.EBELP
,T51.ATA AS LATEST_ATA
FROM(
SELECT EBELN
,EBELP
,ATA
,ROW_NUMBER()OVER (PARTITION BY EBELN,EBELP ORDER BY ATA DESC) ROWNUMBER
FROM DW580_ODM.B_MM_TRACKING_NOTE
) T51
WHERE T51.ROWNUMBER=1
),
T6 AS (
SELECT
T61.EBELN
,T61.EBELP
,CASE WHEN T61.PO_LGSTS_STATUS IN ('10','20') THEN T62.LATEST_ETA
WHEN T61.PO_LGSTS_STATUS='30' THEN T63.LATEST_ATA END LATEST_REF_TA

FROM T3 T61 LEFT JOIN T4 T62 ON T61.EBELN=T62.EBELN AND T61.EBELP=T62.EBELP
LEFT JOIN T5 T63 ON T61.EBELN=T63.EBELN AND T61.EBELP=T63.EBELP
)
INSERT OVERWRITE TABLE DW580_ADM.A_MM_PO_LOGISTICS_INFO
SELECT
T1.STATS_DATE -- 统计日期
,T1.EBELN AS EBELN -- 采购凭证号
,T1.EBELP AS EBELP -- 采购凭证的项目编号
,T2.TRACKING_NOTE_COUNT -- 关联物流单数
,T2.DELIVERY_QUANTITY -- 物流单送货数量
,T3.PO_LGSTS_STATUS -- 订单物流状态
,CASE WHEN T3.PO_LGSTS_STATUS='10' THEN '未到货'
WHEN T3.PO_LGSTS_STATUS='20' THEN '部分到货'
WHEN T3.PO_LGSTS_STATUS='30' THEN '完全到货' END AS PO_LGSTS_STATUS_DESC -- 订单物流状态描述
,T4.LATEST_ETA -- 最晚预计到货时间
,T5.LATEST_ATA -- 最晚实际到货时间
,T6.LATEST_REF_TA -- 最晚参考到货时间
,T1.DELAY_WARN_TH -- 到货延迟预警阈值
,CASE WHEN (T3.PO_LGSTS_STATUS IN ('10','20'))
AND (DATEDIFF(FROM_UNIXTIME(UNIX_TIMESTAMP(T4.LATEST_ETA,'yyyy-MM-dd'),'yyyy-MM-dd') ,FROM_UNIXTIME(UNIX_TIMESTAMP(T1.EINDT,'yyyyMMdd'),'yyyy-MM-dd')) > T1.DELAY_WARN_TH) THEN 1 ELSE 0 END DELAY_WARN_FLAG -- 到货延迟预警标识
,CASE WHEN (DATEDIFF(FROM_UNIXTIME(UNIX_TIMESTAMP(T6.LATEST_REF_TA,'yyyy-MM-dd'),'yyyy-MM-dd'),FROM_UNIXTIME(UNIX_TIMESTAMP(T1.EINDT,'yyyyMMdd'),'yyyy-MM-dd')) > T1.DELAY_WARN_TH) THEN 1 ELSE 0 END DELAY_FLAG -- 到货延迟标识
,CURRENT_TIMESTAMP() AS ETL_DATE
FROM T1 LEFT JOIN T2 ON T1.EBELN=T2.EBELN AND T1.EBELP=T2.EBELP
LEFT JOIN T3 ON T1.EBELN=T3.EBELN AND T1.EBELP=T3.EBELP
LEFT JOIN T4 ON T1.EBELN=T4.EBELN AND T1.EBELP=T4.EBELP
LEFT JOIN T5 ON T1.EBELN=T5.EBELN AND T1.EBELP=T5.EBELP
LEFT JOIN T6 ON T1.EBELN=T6.EBELN AND T1.EBELP=T6.EBELP
WHERE T2.TRACKING_NOTE_COUNT>0 AND T1.EINDT<>'00000000' AND T1.EINDT IS NOT NULL;