hive ETL之物流行业-订单跟踪SLA sql

-- case1 ----========== order_created ==========--/*10703007267488    2014-05-01 06:01:12.334+0110101043505096    2014-05-01 07:28:12.342+0110103043509747    2014-05-01 07:50:12.33+0110103043501575    2014-05-01 09:27:12.33+0110104043514061    2014-05-01 09:03:12.324+01*/CREATE EXTERNAL TABLE order_created (    orderNumber STRING  , event_time  STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'LOCATION '/tmp/db_case1/order_created';CREATE EXTERNAL TABLE order_created_partition (    orderNumber STRING  , event_time  STRING)PARTITIONED BY (event_month string)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'LOCATION '/tmp/db_case1/order_created_partition';CREATE TABLE order_created_dynamic_partition (    orderNumber STRING  , event_time  STRING)PARTITIONED BY (event_month string);insert into table order_created_dynamic_partition PARTITION (event_month)select orderNumber, event_time, substr(event_time, 1, 7) as event_month from order_created;set hive.exec.dynamic.partition.mode=nonstrict;/*    hive.exec.dynamic.partition=false    hive.exec.dynamic.partition.mode=strict    hive.exec.max.dynamic.partitions.pernode=100    Maximum number of dynamic partitions allowed to be created in each mapper/reducer node    hive.exec.max.dynamic.partitions=1000           Maximum number of dynamic partitions allowed to be created in total    hive.exec.max.created.files=100000              Maximum number of HDFS files created by all mappers/reducers in a MapReduce job    hive.error.on.empty.partition=false*/select INPUT__FILE__NAME, ordernumber, event_time, BLOCK__OFFSET__INSIDE__FILE / (length(ordernumber) + length(event_time) + 2) + 1 from order_created_dynamic_partition;select INPUT__FILE__NAME, ordernumber, event_time, round(BLOCK__OFFSET__INSIDE__FILE / (length(ordernumber) + length(event_time) + 2) + 1) from order_created_dynamic_partition;desc formatted order_created_dynamic_partition;desc formatted order_created_dynamic_partition partition (event_month='2014-05');CREATE TABLE order_created_dynamic_partition_parquet (    orderNumber STRING  , event_time  STRING)PARTITIONED BY (event_month string)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'STORED AS parquet;MSCK REPAIR TABLE order_created_dynamic_partition_parquet;-- set to text file format, bug in hiveALTER TABLE order_created_dynamic_partition_parquet PARTITION (event_month='2014-06') SET SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe';ALTER TABLE order_created_dynamic_partition_parquet PARTITION (event_month='2014-06') SET FILEFORMAT textfile;-- impalaALTER TABLE order_created_dynamic_partition_parquet PARTITION (event_month='2014-06') SET FILEFORMAT textfile;-- set to parquet file format, hive <= 0.12ALTER TABLE order_created_dynamic_partition_parquet PARTITION (event_month='2014-07') SET SERDE 'parquet.hive.serde.ParquetHiveSerDe';ALTER TABLE order_created_dynamic_partition_parquet PARTITION (event_month='2014-07') SET FILEFORMAT INPUTFORMAT 'parquet.hive.DeprecatedParquetInputFormat' OUTPUTFORMAT 'parquet.hive.DeprecatedParquetOutputFormat';-- impala or hive 0.13ALTER TABLE order_created_dynamic_partition_parquet PARTITION (event_month='2014-07') SET FILEFORMAT parquet;insert into table order_created_dynamic_partition_parquet PARTITION (event_month='2014-07') select orderNumber, event_time from order_created;--========== order_picked ==========--/*10703007267488    2014-05-01 07:02:12.334+0110101043505096    2014-05-01 08:29:12.342+0110103043509747    2014-05-01 10:55:12.33+01*/CREATE EXTERNAL TABLE order_picked (    orderNumber STRING  , event_time  STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'LOCATION '/tmp/db_case1/order_picked';--========== order_shipped ==========--/*10703007267488    2014-05-01 10:00:12.334+0110101043505096    2014-05-01 18:39:12.342+01*/CREATE EXTERNAL TABLE order_shipped (    orderNumber STRING  , event_time  STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'LOCATION '/tmp/db_case1/order_shipped';--========== order_received ==========--/*10703007267488    2014-05-02 12:12:12.334+01*/CREATE EXTERNAL TABLE order_received (    orderNumber STRING  , event_time  STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'LOCATION '/tmp/db_case1/order_received';--========== order_cancelled ==========--/*10103043501575    2014-05-01 12:12:12.334+01*/CREATE EXTERNAL TABLE order_cancelled (    orderNumber STRING  , event_time  STRING)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'LOCATION '/tmp/db_case1/order_cancelled';--=====================================--CREATE TABLE order_tracking ASSELECT orderNumber     , max(CASE WHEN type_id="order_created"   THEN event_time ELSE '0' END) AS order_created_ts     , max(CASE WHEN type_id="order_picked"    THEN event_time ELSE '0' END) AS order_picked_ts     , max(CASE WHEN type_id="order_shipped"   THEN event_time ELSE '0' END) AS order_shipped_ts     , max(CASE WHEN type_id="order_received"  THEN event_time ELSE '0' END) AS order_received_ts     , max(CASE WHEN type_id="order_cancelled" THEN event_time ELSE '0' END) AS order_cancelled_tsFROM (    select orderNumber, "order_created"   as type_id, event_time FROM order_created  UNION ALL    select orderNumber, "order_picked"    as type_id, event_time FROM order_picked  UNION ALL    select orderNumber, "order_shipped"   as type_id, event_time FROM order_shipped  UNION ALL    select orderNumber, "order_received"  as type_id, event_time FROM order_received  UNION ALL    select orderNumber, "order_cancelled" as type_id, event_time FROM order_cancelled) ugroup by orderNumber;select * from order_tracking order by order_created_ts limit 5;--=====================================--CREATE TABLE order_tracking_join ASselect t1.orderNumber     , t1.event_time as order_created_ts     , t2.event_time as order_picked_ts     , t3.event_time as order_shipped_ts     , t4.event_time as order_received_ts     , t5.event_time as order_cancelled_tsfrom (  select ordernumber, max(event_time) as event_time from order_created group by ordernumber) t1left outer join (  select ordernumber, max(event_time) as event_time from order_picked group by ordernumber) t2on t1.ordernumber = t2.ordernumberleft outer join (  select ordernumber, max(event_time) as event_time from order_shipped group by ordernumber) t3on t1.ordernumber = t3.ordernumberleft outer join (  select ordernumber, max(event_time) as event_time from order_received group by ordernumber) t4on t1.ordernumber = t4.ordernumberleft outer join (  select ordernumber, max(event_time) as event_time from order_cancelled group by ordernumber) t5on t1.ordernumber = t5.ordernumber;select * from order_tracking_join order by order_created_ts limit 5;--=====================================--select orderNumber     , order_created_ts     , order_picked_ts     , order_shipped_ts     , order_received_ts     , order_cancelled_ts  from order_tracking WHERE order_created_ts != '0' AND order_cancelled_ts = '0'   AND (    COALESCE(unix_timestamp(order_picked_ts, 'yyyy-MM-dd HH:mm:ss.S'), 0) - unix_timestamp(order_created_ts, 'yyyy-MM-dd HH:mm:ss.S') > 2 * 60 * 60    OR    COALESCE(unix_timestamp(order_shipped_ts, 'yyyy-MM-dd HH:mm:ss.S'), 0) - unix_timestamp(order_created_ts, 'yyyy-MM-dd HH:mm:ss.S') > 4 * 60 * 60    OR    COALESCE(unix_timestamp(order_shipped_ts, 'yyyy-MM-dd HH:mm:ss.S'), 0) - unix_timestamp(order_created_ts, 'yyyy-MM-dd HH:mm:ss.S') > 48 * 60 * 60   );select orderNumber     , order_created_ts     , order_picked_ts     , order_shipped_ts     , order_received_ts     , order_cancelled_ts  from order_tracking_join WHERE order_created_ts IS NOT NULL AND order_cancelled_ts IS NULL   AND (    COALESCE(unix_timestamp(order_picked_ts, 'yyyy-MM-dd HH:mm:ss.S'), 0) - unix_timestamp(order_created_ts, 'yyyy-MM-dd HH:mm:ss.S') > 2 * 60 * 60    OR    COALESCE(unix_timestamp(order_shipped_ts, 'yyyy-MM-dd HH:mm:ss.S'), 0) - unix_timestamp(order_created_ts, 'yyyy-MM-dd HH:mm:ss.S') > 4 * 60 * 60    OR    COALESCE(unix_timestamp(order_shipped_ts, 'yyyy-MM-dd HH:mm:ss.S'), 0) - unix_timestamp(order_created_ts, 'yyyy-MM-dd HH:mm:ss.S') > 48 * 60 * 60   );