【HiveETL】物流行业 -- 订单跟踪、SLA

最新推荐文章于 2022-09-22 16:02:17 发布

图特摩斯科技

最新推荐文章于 2022-09-22 16:02:17 发布

阅读量1.3k

点赞数

分类专栏： hive Impala

本文链接：https://blog.csdn.net/lovebyz/article/details/51779115

版权

hive 同时被 2 个专栏收录

21 篇文章 1 订阅

订阅专栏

Impala

5 篇文章 0 订阅

订阅专栏

--========== 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

raini@biyuzhe:~/tmp/db_case1$ hadoop fs -ls /user/hive/warehouse

*/
CREATE EXTERNAL TABLE order_created (
    orderNumber STRING
  , event_time  STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/home/raini/tmp/db_case1/order_created';

/*创建分区表 */
CREATE EXTERNAL TABLE order_created_partition (
    orderNumber STRING
  , event_time  STRING
)
<strong><span style="color:#cc0000;">PARTITIONED BY (event_month string)</span></strong>
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/home/raini/tmp/db_case1/order_created_partition';

/*hive> show tables;
hive><strong> msck repair table </strong>order_created_partition;
hive> select * from order_created_partition where even_mouth="2014-05";

*/

CREATE TABLE order_created_dynamic_partition (
    orderNumber STRING
  , event_time  STRING
)
<strong>PARTITIONED BY (event_month string)</strong>
;
insert into table order_created_dynamic_partition PARTITION (event_month)
<span style="white-space:pre">	</span>select orderNumber, event_time, substr(event_time, 1, 7) as event_month from order_created;
<strong>set hive.exec.dynamic.partition.mode=nonstrict;</strong>

/*
<span style="color:#999999;">    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</span>
*/

select INPUT__FILE__NAME, ordernumber, event_time, BLOCK__OFFSET__INSIDE__FILE / (length(ordernumber) + length(event_time) + 2) + 1 from order_created_dynamic_partition;
select <strong>INPUT__FILE__NAME,</strong> ordernumber, event_time, <strong>round(BLOCK__OFFSET__INSIDE__FILE / (length(ordernumber) + length(event_time) + 2) + 1)</strong> 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;

<strong>MSCK REPAIR TABLE </strong>order_created_dynamic_partition_parquet;

-- set to text file format
ALTER TABLE order_created_dynamic_partition_parquet PARTITION (event_month='2014-06') SET SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe';
<strong>ALTER TABLE </strong>order_created_dynamic_partition_parquet <strong>PARTITION </strong>(event_month='2014-06')<strong> SET FILEFORMAT </strong>textfile<strong>;</strong>

ALTER TABLE order_created_dynamic_partition_parquet PARTITION (event_month='2014-07') SET FILEFORMAT parquet;

<strong>insert into table </strong>order_created_dynamic_partition_parquet <strong>PARTITION (event_month='2014-07')</strong> 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';


--==================第一种写法UNION ALL===================--

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;


--==================第二种写法left outer join===================--

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;



--=================LSA查询====================--

第一种写法：
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
   )
;