SQL_ODPS-D2-离线数仓-7-临时查询

SQL_ODPS-D2-离线数仓-7-临时查询

包括ods dwd dws ads数仓的建表语句 以及 手动导入数据语句

test

--odps sql 
--********************************************************************--
--author:i
--create time:0000-00-00 00:00:00
--********************************************************************--
SELECT  *
FROM    ods_base_log
WHERE   ds = '00000000'
;

--测试FlatEventUDTF自定义函数 一进多出
SELECT  FLATEVENTUDTF(GET_JSON_OBJECT(log_string,'$.et')) AS (event_time,event_name,event_json)    --$.et 就是事件
FROM    ods_base_log
WHERE   ds = '00000000'
;

--手动将ods层数据导入到dwd层
INSERT OVERWRITE TABLE dwd_start_log PARTITION(ds,hh,mm)
SELECT  GET_JSON_OBJECT(log_string,'$.cm.mid') mid
        ,GET_JSON_OBJECT(log_string,'$.cm.uid') user_id
        ,GET_JSON_OBJECT(log_string,'$.cm.vc' ) version_code
        ,GET_JSON_OBJECT(log_string,'$.cm.vn') version_name
        ,GET_JSON_OBJECT(log_string,'$.cm.l') lang
        ,GET_JSON_OBJECT(log_string,'$.cm.sr') source
        ,GET_JSON_OBJECT(log_string,'$.cm.os') os
        ,GET_JSON_OBJECT(log_string,'$.cm.ar') area
        ,GET_JSON_OBJECT(log_string,'$.cm.md') model
        ,GET_JSON_OBJECT(log_string,'$.cm.ba') brand
        ,GET_JSON_OBJECT(log_string,'$.cm.sv') sdk_version
        ,GET_JSON_OBJECT(log_string,'$.cm.hw') height_width
        ,GET_JSON_OBJECT(log_string,'$.cm.g') email
        ,GET_JSON_OBJECT(log_string,'$.cm.hw') sv
        ,GET_JSON_OBJECT(log_string,'$.cm.ln') ln
        ,GET_JSON_OBJECT(log_string,'$.cm.la') la
        ,GET_JSON_OBJECT(event_view.event_json,'$.entry') entry
        ,GET_JSON_OBJECT(event_view.event_json,'$.loading_time') loading_time
        ,GET_JSON_OBJECT(event_view.event_json,'$.action') action
        ,GET_JSON_OBJECT(event_view.event_json,'$.open_ad_type') open_ad_type
        ,GET_JSON_OBJECT(event_view.event_json,'$.detail') detail
        ,event_view.event_time
        ,ds
        ,hh
        ,mm
FROM    ods_base_log
LATERAL VIEW FLATEVENTUDTF(GET_JSON_OBJECT(log_string,'$.et')) event_view AS event_time,event_name,event_json
WHERE   ds = '00000000'
AND     event_view.event_name = 'start'
;

--查看导入结果
SELECT  *
FROM    dwd_start_log
WHERE   ds = '00000000'
LIMIT   3
;
-- mid	user_id	version_code	version_name	lang	source	os	area	model	brand	sdk_version	email	height_width	network	lng	lat	entry	open_ad_type	action	loading_time	detail	event_time	ds	hh	mm
-- 0	0	9	1.0.7	es	V	8.1.1	MX	HTC-0	HTC	V2.5.9	640*960	QED8OB5D@gmail.com	640*960	-112.5	29.2	3	13	1	2		1000001265269	00000000	01	15
--手动将dwd层数据导入到dws层

INSERT OVERWRITE TABLE dws_uv_detail_d PARTITION(ds,hh,mm)
SELECT  mid
        ,user_id
        ,version_code
        ,version_name
        ,lang
        ,source
        ,os
        ,area
        ,model
        ,brand
        ,sdk_version
        ,email
        ,height_width
        ,network
        ,lng
        ,lat
        ,event_time
        ,ds
        ,hh
        ,mm
FROM    (
            SELECT  *
                    ,row_number() OVER(PARTITION BY mid ORDER BY event_time ASC) rn
            FROM    dwd_start_log
            WHERE   ds = '00000000'
        ) 
WHERE   rn = 1
;

--查看导入结果
SELECT  *
FROM    dws_uv_detail_d
WHERE   ds = '00000000'
LIMIT   5
;
-- mid	user_id	version_code	version_name	lang	source	os	area	model	brand	sdk_version	email	height_width	network	lng	lat	event_time	ds	hh	mm
-- 999	999	16	1.2.0	es	W	8.1.0	MX	sumsung-4	Sumsung	V2.2.9	1080*1920	W4ZM4MEQ@gmail.com	1080*1920	-86.9	12.8	1583581530558	00000000	01	15
-- 1	1	11	1.2.0	es	R	8.1.4	MX	Huawei-4	Huawei	V2.1.7	640*1136	QF4951T5@gmail.com	640*1136	-101.2	21.8	1583553705570	00000000	01	15
-- 10	10	16	1.2.2	en	E	8.0.8	MX	sumsung-10	Sumsung	V2.7.8	750*1134	K3ZL1FP0@gmail.com	750*1134	-96.7	0.8	1583514130824	00000000	01	15
-- 100	100	18	1.0.5	pt	G	8.1.6	MX	HTC-11	HTC	V2.7.3	640*960	W5920706@gmail.com	640*960	-84.8	25.9	1583564803755	00000000	01	15
-- 101	101	14	1.2.1	en	L	8.2.0	MX	Huawei-0	Huawei	V2.5.5	750*1134	1Q2V0YZ5@gmail.com	750*1134	-72.9	-4.6	1583574821599	00000000	01	15

--ods层原数据层建表语句
CREATE TABLE `ods_order_info_di` 
(
    `id` STRING COMMENT '订单编号'
    ,`total_amount` DOUBLE COMMENT '订单金额'
    ,`order_status`STRING COMMENT '订单状态'
    ,`user_id` STRING COMMENT '用户id'
    ,`payment_way` STRING COMMENT '支付方式'
    ,`out_trade_no` STRING COMMENT '支付流水号'
    ,`create_time` STRING COMMENT '创建时间'
    ,`operate_time` STRING COMMENT '操作时间'
    ,`province_id`STRING COMMENT '省份'
)
COMMENT '订单表'
PARTITIONED BY 
(
    ds STRING
)
;

CREATE TABLE `ods_order_deatail_di` 
(
    `id` STRING COMMENT '明细id'
    ,`order_id` STRING COMMENT '订单id'
    ,`sku_id` DOUBLE COMMENT '商品id'
    ,`sku_name` STRING COMMENT '商品名称'
    ,`order_price` DOUBLE COMMENT '购买价格'
    ,`sku_num` BIGINT COMMENT '购物数量'
    ,`create_time` STRING COMMENT '创建时间'
)
COMMENT '订单明细'
PARTITIONED BY 
(
    ds STRING
)
;

CREATE TABLE `ods_sku_info_df` 
(
    `id` STRING COMMENT 'skuid'
    ,`spu_id` STRING COMMENT '订单id'
    ,`price` DOUBLE COMMENT '价格'
    ,`sku_name` STRING COMMENT '商品名称'
    ,`sku_desc