11分布式数据仓库 HIVE -- HIVE案例实战2 SLA
来源:互联网 发布:陈奕迅唱功知乎 编辑:程序博客网 时间:2024/06/10 09:19
--========== 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
)
;
- 11分布式数据仓库 HIVE -- HIVE案例实战2 SLA
- 10分布式数据仓库 HIVE -- HIVE案例实战1 apache common日志分析
- 09分布式数据仓库 HIVE -- HIVE优化
- 07分布式数据仓库 HIVE -- 函数
- Hive-数据仓库
- HIVE在腾讯分布式数据仓库实践
- 01分布式数据仓库 HIVE -- 数据类型-文件格式
- 04分布式数据仓库 HIVE -- 导出分析结果
- 05分布式数据仓库 HIVE -- 分区表的操作
- 08分布式数据仓库 HIVE -- UDF和UDAF
- HIVE实战:官方案例练习
- 02分布式数据仓库 HIVE -- 表的相关操作
- 03分布式数据仓库 HIVE -- 数据的相关操作
- Hive数据仓库(11)之解析Json格式文件
- hive(数据仓库工具)
- 数据仓库工具--Hive
- 数据仓库工具:Hive
- Hive(一个数据仓库)
- 第六章思维导图
- python 的 re 模块
- adb devices显示no permissions adb shell 指定设备
- SUID与SGID、SBIT
- Java8 Lambda表达式教程
- 11分布式数据仓库 HIVE -- HIVE案例实战2 SLA
- Where there is life, there is beyond. Android中Parcelable接口用法
- Block 详解
- 第一次献给宝宝的爱——Milumil米路米奶粉
- liferay form 小案例
- 【C#】笔记本
- apache 支持include(服务端包含配置)
- Java中JDBC获取各种类型数据库连接
- 快速排序