189 8069 5689
-- case1 -- --========== order_created ==========-- /* 10703007267488 2014-05-01 06:01:12.334+01 10101043505096 2014-05-01 07:28:12.342+01 10103043509747 2014-05-01 07:50:12.33+01 10103043501575 2014-05-01 09:27:12.33+01 10104043514061 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 hive ALTER 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; -- impala ALTER TABLE order_created_dynamic_partition_parquet PARTITION (event_month='2014-06') SET FILEFORMAT textfile; -- set to parquet file format, hive <= 0.12 ALTER 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.13 ALTER 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+01 10101043505096 2014-05-01 08:29:12.342+01 10103043509747 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+01 10101043505096 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 AS SELECT 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_ts FROM ( 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 ) u group by orderNumber; select * from order_tracking order by order_created_ts limit 5; --=====================================-- CREATE TABLE order_tracking_join AS select 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_ts from ( select ordernumber, max(event_time) as event_time from order_created group by ordernumber ) t1 left outer join ( select ordernumber, max(event_time) as event_time from order_picked group by ordernumber ) t2 on t1.ordernumber = t2.ordernumber left outer join ( select ordernumber, max(event_time) as event_time from order_shipped group by ordernumber ) t3 on t1.ordernumber = t3.ordernumber left outer join ( select ordernumber, max(event_time) as event_time from order_received group by ordernumber ) t4 on t1.ordernumber = t4.ordernumber left outer join ( select ordernumber, max(event_time) as event_time from order_cancelled group by ordernumber ) t5 on 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 ) ;