Featured image of post 电商用户行为分析
项目

电商用户行为分析

本文简要介绍了电商用户行为分析离线数仓的搭建及效果展示。

一、项目介绍

  • 项目概述

    本项目旨在构建一个完整的电商用户行为离线数据仓库系统，用于存储、处理和分析电商平台的用户行为数据及业务数据，为企业的经营分析、用户画像、推荐系统等提供数据支持。

  • 项目进度

    • 已完成数据采集架构搭建和HDFS存储设计
    • 正在进行数据仓库分层建模和ETL流程开发
    • 后续将完善数据质量监控和可视化展示

  • 项目价值

    • 用户行为分析：分析用户路径、转化漏斗、停留时长等，优化用户体验
    • 精准营销：基于用户画像实现个性化推荐和精准营销
    • 经营分析：监控核心业务指标(KPI)，辅助经营决策
    • 库存优化：分析商品销售趋势，优化库存管理

  • 项目效果（暂无）

二、项目规划

  • 集群规划

    四台虚拟机（centos7）、Termius（虚拟机连接）

    服务名称 子服务 服务器 hadoop102 服务器 hadoop103 服务器 hadoop104
    HDFS NameNode √
    DataNode √ √ √
    SecondaryNameNode √
    Yarn NodeManager √ √ √
    Resourcemanager √
    Zookeeper Zookeeper Server √ √ √
    Flume（采集日志） Flume √ √
    Kafka Kafka √ √ √
    Flume（消费Kafka日志） Flume √
    Flume（消费Kafka业务） Flume √
    Hive √ √ √
    MySQL MySQL √
    DataX √ √ √
    Spark √ √ √
    DolphinScheduler ApiApplicationServer √
    AlertServer √
    MasterServer √
    WorkerServer √ √ √
    LoggerServer √ √ √
    Superset Superset √
    Flink √
    ClickHouse √
    Redis √
    Hbase √

  • 项目流程

    data_warehouse_flow

  • 项目架构

    • 数据采集层（cluster.sh）

      用户行为日志采集：

      • 使用Flume构建日志收集系统，实时采集用户在前端的点击、浏览、搜索、加购、下单等行为日志
      • 日志数据以JSON格式存储到HDFS分布式文件系统
      • 自定义拦截器进行数据初步过滤、格式化以及零点漂移问题

      业务数据同步：

      • 使用Maxwell监控MySQL binlog，实时捕获业务数据库的变更
      • 使用Maxwell-bootstrap实现历史全量数据同步
      • 使用DataX进行批量数据同步，处理全量历史数据
      • 自定义拦截器解决零点漂移问题
      • 业务数据包括用户信息、商品信息、订单数据、支付数据等

    • 数据存储层

      • 采用HDFS作为底层分布式存储系统
      • 使用Hive构建数据仓库，实现结构化数据存储
      • 分区表设计按日期分区，优化查询性能

    • 数据处理层

      • 使用Hive SQL进行ETL处理，通过DataGrip实现
      • 实现数据清洗、转换、聚合等操作
      • 采用DolphinScheduler进行任务调度

    • 数据服务层

      • 使用DataX将分析结果导出到关系型数据库供业务系统使用
      • 通过Superset工具实现数据可视化

三、数仓开发

  • ER建模 or 维度建模

    data_warehouse_model

  • 维度建模

    • 事实表：用户行为事件、订单事实、支付事实等
    • 维度表：用户维度、商品维度、时间维度、地区维度等

  • 分层设计

    1. ODS层(原始数据层)：
      • ODS层的表结构设计依托于业务系统同步过来的数据结构
      • ODS层要保存全部历史数据，故其压缩格式应选择压缩比较高的，此处选择gzip
      • ODS层表名的命名规范为：ods_表名_单分区增量/全量标识（inc / full）
    2. DIM层（维度层）
      • DIM层的设计依据是维度建模理论，该层存储维度模型的维度表
      • DIM层的数据存储格式为orc列式存储+snappy压缩
      • DIM层表名的命名规范为dim_表名_全量表或者拉链表标识（full / zip）
    3. DWD层(明细数据层)：
      • DWD层的设计依据是维度建模理论，该层存储维度模型的事实表
      • DWD层的数据存储格式为orc列式存储+snappy压缩
      • DWD层表名的命名规范为dwd_数据域_表名_单分区增量/全量标识（inc / full）
    4. DWS层(汇总数据层)：

      • DWS层的设计参考指标体系

      • DWS层的数据存储格式为orc列式存储+snappy压缩

      • DWS层表名的命名规范为dws_数据域_统计粒度_业务过程_统计周期（1d/nd/td）

        1d表示最近1日，nd表示最近n日，td表示历史至今

    5. ADS层(应用数据层)：
      • 面向具体业务场景的高度聚合数据
      • 包含用户留存分析、转化漏斗、商品销量排行等主题

四、设计细节

  • ODS层 

      1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316

    -- ODS层
    -- Operate Data Store
        -- 存储从mysql业务数据库和日志服务器的日志文件中采集到的数据
            -- 日志数据
                -- 格式：JSON
            -- 业务数据
                -- 历史数据
                -- 格式：
                    -- 全量
                        -- DataX：TSV
                    -- 增量
                        -- Maxwell：JSON
        -- 汇总数据
            -- 希望用最少的资源存最多的数据
                -- 压缩：gzip
                    -- gzip：Hadoop默认支持的，压缩效率不高，压缩率极高
                    -- lzo：Hadoop默认不支持的，压缩效率一般，压缩率高
                    -- snappy：Hadoop默认不支持的，压缩效率极高，压缩率不高
                -- 数据格式尽可能不变
                -- 压缩格式尽可能不变
        -- 命名规范
            -- 在数据仓库中，表其实都是放置在一起的。从逻辑上进行区分，进行分层
            -- 表从名称上区分每一层
            -- 分层标记（ods_） + 同步数据的表名称 + 全量/增量标识（full/inc）

-- 日志表
    -- 表的数据就是同步过来的日志数据
        -- 页面浏览日志：JSON
        -- APP启动日志：JSON
    -- ods_log_inc
        -- 字段
    -- 建表语句
        -- EXTERNAL
            -- 外部表
        -- LOCATION
            -- 位置
    -- 日志数据格式：
        -- 页面浏览日志
            -- JSON中含有JSON
                -- JSON表中的JSON数据如果存在嵌套的情况
                    -- 一般会将最外层的JSON对象的属性作为JSON表的字段
                -- common
                -- actions
                -- displays
                -- page
                -- err
                -- ts
            -- 表的字段类型应该采用特殊类型
        -- APP启动日志
            -- JSON中含有JSON
                -- common
                -- start
                -- err
                -- ts

-- DROP TABLE IF EXISTS ods_log_inc;
-- CREATE EXTERNAL TABLE ods_log_inc
-- (
--     -- common：struct<ar:string, br:string ...>
--     -- actions：array<struct<action_id:string, item:string ...>>
--     -- displays：array<struct<action_id:string, item:string ...>>
--     -- page：struct
--     -- err：struct
--     -- ts：bigint
--     -- start：struct
-- ) COMMENT '活动信息表'
-- PARTITIONED BY (`dt` STRING)
-- ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
-- LOCATION '/warehouse/gmall/ods/ods_log_inc/';

DROP TABLE IF EXISTS ods_log_inc;
CREATE EXTERNAL TABLE ods_log_inc
(
    `common` STRUCT<ar :STRING,
        ba :STRING,
        ch :STRING,
        is_new :STRING,
        md :STRING,
        mid :STRING,
        os :STRING,
        sid :STRING,
        uid :STRING,
        vc :STRING> COMMENT '公共信息',
    `page` STRUCT<during_time :STRING,
        item :STRING,
        item_type :STRING,
        last_page_id :STRING,
        page_id :STRING,
        from_pos_id :STRING,
        from_pos_seq :STRING,
        refer_id :STRING> COMMENT '页面信息',
    `actions` ARRAY<STRUCT<action_id:STRING,
        item:STRING,
        item_type:STRING,
        ts:BIGINT>> COMMENT '动作信息',
    `displays` ARRAY<STRUCT<display_type :STRING,
        item :STRING,
        item_type :STRING,
        `pos_seq` :STRING,
        pos_id :STRING>> COMMENT '曝光信息',
    `start` STRUCT<entry :STRING,
        first_open :BIGINT,
        loading_time :BIGINT,
        open_ad_id :BIGINT,
        open_ad_ms :BIGINT,
        open_ad_skip_ms :BIGINT> COMMENT '启动信息',
    `err` STRUCT<error_code:BIGINT,
            msg:STRING> COMMENT '错误信息',
    `ts` BIGINT  COMMENT '时间戳'
) COMMENT '活动信息表'
    PARTITIONED BY (`dt` STRING)
    ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
LOCATION '/warehouse/gmall/ods/ods_log_inc/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

-- 装载数据
    -- load：HDFS
    -- save：ods_log_inc
-- hive：file -> load -> table

-- load data：剪切
load data inpath '/origin_data/gmall/log/topic_log/2022-06-08' into table 
ods_log_inc partition(dt='2022-06-08');

-- 分区表
    -- hive中的表一般都包含分区
    -- hive中表的数据通常非常多，底层采用很多数据文件进行保存
        -- 查询时，会从数据文件中按照条件进行查询
            -- 一旦数据多、文件大、文件多，都会影响查询效率
        -- 通过建立分区的操作，让查询效率提高
            -- 将数据文件不是放置在一起，而是根据某些条件存储到不同的路径下
                -- 这里的条件就是分区
        -- 分区表，存在分区字段，这个字段不是数据字段，而是用于文件目录的划分，不会存储到数据文件中
            -- dt：date
DROP TABLE IF EXISTS test_part;
CREATE TABLE test_part
(
    id int
) COMMENT '活动信息表'
PARTITIONED BY (`dt` STRING)
LOCATION '/test/test_part';

insert into table test_part values (1, '2022-06-08'); -- 真实的数据字段 虚拟的分区字段
-- 静态分区：分区字段的值为固定值
insert into table test_part partition (dt = '2022-06-08') values (1);
-- 动态分区：分区字段取自于查询结果
    -- 分区字段不能赋值
    -- 查询字段应该在最后增加一个额外的字段用于分区操作，所以称之为分区字段
    -- 默认情况下 Hive 没有开启动态分区的处理
set hive.exec.dynamic.partition.mode=nonstrict;
insert into table test_part partition (dt)
select 2, '2022-06-09';

set hive.mapred.mode=nonstrict;
select
    *
from test_part
where dt = "2022-06-09";
--------------------------------------------------------
-- log：JSON
    -- 默认情况下，Hive的表无法解析JSON格式
    -- 如果 Hive 表可以解析JSON格式的数据，那么一般称之为JSON表
        -- 如果JSON属性和表的字段相同，那么可以正常解析
        -- 如果JSON属性少于表的字段，那么存在的属性可以正常解析，不存在的字段会被设定为null
        -- 如果JSON属性多于表的字段，那么多余属性不做解析
        -- 如果JSON属性和表的字段大小写有差异，会进行不区分大小写的解析
DROP TABLE IF EXISTS test_log;
CREATE TABLE test_log
(
    id bigint,
    name string,
    age bigint
) COMMENT '活动信息表'
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
LOCATION '/test/test_log/';

-- map, array, struct
    -- array
        -- 使用 [] 表示数组，访问时就是采用中括号加索引的方式进行访问
        -- 如果索引超出范围，并不会发生错误，而是直接返回null
DROP TABLE IF EXISTS test_datatype;
CREATE TABLE test_datatype
(
    ids array<int>,
    dat map<string, string>,
    obj struct<id:int, name:string>
) COMMENT '活动信息表'
LOCATION '/test/test_datatype/';
/*
 子查询 (select array(1, 2, 3, 4) as ids) t
 创建了一个临时表 t，其中包含一列 ids，值为数组 [1, 2, 3, 4]
 外层查询 从临时表 t 中选择 ids 列作为结果
 */
select
    ids,
    ids[5],
    array( ids[1], ids[2] ),
    array_contains(ids, 5)
from
(select `array`(1, 2, 3, 4) ids )
t where array_contains(ids, 8)
;

-- map
    -- 显示效果类似于JSON对象
        -- map数据的访问，不能直接通过key来操作
        -- 访问数据时，采用类似于数组的方式，但是不是采用索引，而是key
        -- 如果key不存在，那么直接返回null
select
    dat,
    dat[0],
    dat['a'],
    map_keys(dat),
    map_values(dat),
    if (array_contains(map_keys(dat), 'c'), 3, 4)
from
    (select `map`('a', 'b', 'c', 1) dat ) -- <'a', 'b'> <'c', 1>
t;

-- struct：对象
    -- 显示效果类似于JSON对象
        -- struct：会将所有数据作为属性值存在
        -- 访问对象中存在的属性时，不能采用中括号，直接采用点的方式就可以
        -- 访问对象中不存在的属性时，会直接发生错误

select struct('a', 'b', 'c', 'd');

-- map和struct区别
    -- 泛型
    -- struct中的属性名称是固定的，只要约束好不能发生变化
    -- map中的key不是固定的，可以动态判断
select
    obj,
    obj.a,
    obj.e
from (select named_struct('a', 'b', 'c', 1) obj) t;

-- 业务表
    -- 全量表：DataX 表结构和业务表保持一致即可
        -- TSV
            -- mysql：column[id, name, age]
            -- data：1001    zhangsan    30
            -- hive：column[id, name, age]
    -- 增量表：Maxwell
        -- JSON
            -- 最外层JSON对象的属性作为表的字段

DROP TABLE IF EXISTS ods_activity_info_full;
CREATE EXTERNAL TABLE ods_activity_info_full
(
    `id`              STRING COMMENT '活动id',
    `activity_name` STRING COMMENT '活动名称',
    `activity_type` STRING COMMENT '活动类型',
    `activity_desc` STRING COMMENT '活动描述',
    `start_time`     STRING COMMENT '开始时间',
    `end_time`        STRING COMMENT '结束时间',
    `create_time`    STRING COMMENT '创建时间',
    `operate_time`   STRING COMMENT '修改时间'
) COMMENT '活动信息表'
    PARTITIONED BY (`dt` STRING)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    NULL DEFINED AS ''
LOCATION '/warehouse/gmall/ods/ods_activity_info_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

DROP TABLE IF EXISTS ods_activity_rule_full;
CREATE EXTERNAL TABLE ods_activity_rule_full
(
    `id`                  STRING COMMENT '编号',
    `activity_id`       STRING COMMENT '活动ID',
    `activity_type`     STRING COMMENT '活动类型',
    `condition_amount` DECIMAL(16, 2) COMMENT '满减金额',
    `condition_num`     BIGINT COMMENT '满减件数',
    `benefit_amount`    DECIMAL(16, 2) COMMENT '优惠金额',
    `benefit_discount` DECIMAL(16, 2) COMMENT '优惠折扣',
    `benefit_level`     STRING COMMENT '优惠级别',
    `create_time`       STRING COMMENT '创建时间',
    `operate_time`      STRING COMMENT '修改时间'
) COMMENT '活动规则表'
    PARTITIONED BY (`dt` STRING)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    NULL DEFINED AS ''
LOCATION '/warehouse/gmall/ods/ods_activity_rule_full/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');


DROP TABLE IF EXISTS ods_cart_info_inc;
CREATE EXTERNAL TABLE ods_cart_info_inc
(
    `type` STRING COMMENT '变动类型',
    `ts`   BIGINT COMMENT '变动时间',
    `data` STRUCT<id :STRING,
        user_id :STRING,
        sku_id :STRING,
        cart_price :DECIMAL(16, 2),
        sku_num :BIGINT,
        img_url :STRING,
        sku_name :STRING,
        is_checked :STRING,
        create_time :STRING,
        operate_time :STRING,
        is_ordered :STRING,
        order_time:STRING> COMMENT '数据',
    `old`  MAP<STRING,STRING> COMMENT '旧值' -- old中不确定字段的个数（修改字段）
) COMMENT '购物车增量表'
    PARTITIONED BY (`dt` STRING)
    ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
LOCATION '/warehouse/gmall/ods/ods_cart_info_inc/'
TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

DROP TABLE IF EXISTS test_loc;
CREATE TABLE test_loc
(
    id int
) COMMENT '活动信息表'
LOCATION '/test/test_loc';

  • DIM层 

      1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820

    -- DIM层
    -- Dimension：维度
        -- 所谓的维度其实就是分析数据的角度表
            -- 性别
            -- 年龄
            -- 品牌
            -- 品类
        -- 维度层保存维度表，所以建模理论应该遵循维度建模理论
            -- 维度层中的维度表主要用于统计分析
                -- 数据存储方式应该为列式存储：orc（行式存储不利于统计分析）
                -- 数据压缩效率越高越好（时间短）：snappy
            -- 数据源
                -- ODS层的数据为整个数据仓库做准备
                -- DIM层数据源就是ODS层
        --命名规范
            -- 分层标记 (dim_)_维度名称_全量/拉链（标识）
                -- 全量：维度表的全部数据
                    -- 状态数据为了避免数据出现问题，最好的方式就是每一天都保存全部数据
                -- 绝大多数的维度表都是全量表，特殊情况采用拉链的方式

    -- 建模理论
        -- ER模型
            -- ODS
        -- 维度模型
            -- 维度（状态）表
            -- 事实（行为）表
    -- 维度表
        -- 表
            -- 维度（角度），一个维度就是一张表
                -- t_order, t_sex, t_age
            -- 从实践来讲，一般会将有关联性的维度设置为一张表，不同的维度就是这张表的字段
                -- t_order, t_user(sex, age)
                -- t_order, t_tm, t_category -> t_order, t_sku(tm, category)
            -- 如果维度特别简单，特别独立，只在特殊场合用，可不创建，在事实表中直接使用，无需单独创建
                -- t_payment_type：微信支付、支付宝支付、银联支付
        -- 字段
            -- 维度：只要能用来进行分析的维度，都是字段（有些表的字段，如密码、头像无需分析，不能成为字段）
        -- 数据（字段）来源：参考业务数据库的表字段
            -- 主维表：业务数据库中主要用于分析维度字段的表
            -- 相关维表：业务数据库中相关用于分析维度字段的表

            -- 维度字段的确定
                -- 尽可能生成丰富的维度属性：字段越多越好
                -- 字段越多不会影响统计分析（列式存储），需要哪列取哪列，不用即不需要取
                -- 编码和文字共存
                -- 沉淀出（计算）通用的维度属性
                    -- 时间 2023-02-01
                    -- tel

-- 商品维度表
    -- dim_sku_full
        -- 全量维度表：以天为单位将数据全部同步到维度表的相同时间分区中
        -- 业务数据库的表
            -- 主维表：sku_info
            -- 相关维表：
            -- sku_attr_value
            -- sku_sale_attr_value

-- 建表语句
DROP TABLE IF EXISTS dim_sku_full;
CREATE EXTERNAL TABLE dim_sku_full
(
    `id`                   STRING COMMENT 'SKU_ID',
    `price`                DECIMAL(16, 2) COMMENT '商品价格',
    `sku_name`             STRING COMMENT '商品名称',
    `sku_desc`             STRING COMMENT '商品描述',
    `weight`               DECIMAL(16, 2) COMMENT '重量',
    `is_sale`              BOOLEAN COMMENT '是否在售',
    `spu_id`               STRING COMMENT 'SPU编号',
    `spu_name`             STRING COMMENT 'SPU名称',
    `category3_id`         STRING COMMENT '三级品类ID',
    `category3_name`       STRING COMMENT '三级品类名称',
    `category2_id`         STRING COMMENT '二级品类id',
    `category2_name`       STRING COMMENT '二级品类名称',
    `category1_id`         STRING COMMENT '一级品类ID',
    `category1_name`       STRING COMMENT '一级品类名称',
    `tm_id`                  STRING COMMENT '品牌ID',
    `tm_name`               STRING COMMENT '品牌名称',
    `sku_attr_values`      ARRAY<STRUCT<attr_id :STRING,
        value_id :STRING,
        attr_name :STRING,
        value_name:STRING>> COMMENT '平台属性',
    `sku_sale_attr_values` ARRAY<STRUCT<sale_attr_id :STRING,
        sale_attr_value_id :STRING,
        sale_attr_name :STRING,
        sale_attr_value_name:STRING>> COMMENT '销售属性',
    `create_time`          STRING COMMENT '创建时间'
) COMMENT '商品维度表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dim/dim_sku_full/'
    TBLPROPERTIES ('orc.compress' = 'snappy');  -- 默认gzip


-- 装载数据
    -- load
        -- 补全数据
            -- 补全行：union
            -- 补全列：join
                -- join, left join, right join, full join
    -- save
        -- 以时间为单位（分区）进行保存
        -- 采集汇总哪一天的数据，就保存到哪一天的分区中
        -- insert into：插入
        -- insert overwrite：覆盖
        -- 大数据中没有事务的概念，如果执行到中间失败，下面不会再执行，需要retry整个脚本，使用insert into会导致已成功执行的重复
insert overwrite table dim_sku_full partition (dt='2022-06-08')
select
     sku.`id`                  ,--STRING COMMENT 'SKU_ID',
    `price`                ,--DECIMAL(16, 2) COMMENT '商品价格',
    `sku_name`             ,--STRING COMMENT '商品名称',
    `sku_desc`             ,--STRING COMMENT '商品描述',
    `weight`               ,--DECIMAL(16, 2) COMMENT '重量',
    `is_sale`              ,--BOOLEAN COMMENT '是否在售',
    `spu_id`               ,--STRING COMMENT 'SPU编号',
    `spu_name`             ,--STRING COMMENT 'SPU名称',
    `category3_id`         ,--STRING COMMENT '三级品类ID',
    `category3_name`       ,--STRING COMMENT '三级品类名称',
    `category2_id`         ,--STRING COMMENT '二级品类id',
    `category2_name`       ,--STRING COMMENT '二级品类名称',
    `category1_id`         ,--STRING COMMENT '一级品类ID',
    `category1_name`       ,--STRING COMMENT '一级品类名称',
    `tm_id`                ,--  STRING COMMENT '品牌ID',
    `tm_name`              ,-- STRING COMMENT '品牌名称',
    `sku_attr_values`      ,
    `sku_sale_attr_values` ,
    `create_time`           --STRING COMMENT '创建时间'
from (
    select
        `id`                   ,--STRING COMMENT 'SKU_ID',
        `price`                ,--DECIMAL(16, 2) COMMENT '商品价格',
        `sku_name`             ,--STRING COMMENT '商品名称',
        `sku_desc`             ,--STRING COMMENT '商品描述',
        `weight`               ,--DECIMAL(16, 2) COMMENT '重量',
        `is_sale`              ,--BOOLEAN COMMENT '是否在售',
        `spu_id`               ,--STRING COMMENT 'SPU编号',
        --`spu_name`             ,--STRING COMMENT 'SPU名称',
        `category3_id`         ,--STRING COMMENT '三级品类ID',
        --`category3_name`       ,--STRING COMMENT '三级品类名称',
        --`category2_id`         ,--STRING COMMENT '二级品类id',
        --`category2_name`       ,--STRING COMMENT '二级品类名称',
        --`category1_id`         ,--STRING COMMENT '一级品类ID',
        --`category1_name`       ,--STRING COMMENT '一级品类名称',
        `tm_id`                ,--STRING COMMENT '品牌ID',
        --`tm_name`              ,--STRING COMMENT '品牌名称',
        `create_time`           --STRING COMMENT '创建时间'
    from ods_sku_info_full
    where dt = '2022-06-08'
) sku
left join (
    select
        id,
        spu_name
    from ods_spu_info_full
    where dt = '2022-06-08'
) spu on sku.spu_id = spu.id
left join (
    select
        id,
        tm_name
    from ods_base_trademark_full
    where dt = '2022-06-08'
) tm on sku.tm_id = tm.id
left join (
    select
        id,
        name category3_name,
        category2_id
    from ods_base_category3_full
    where dt = '2022-06-08'
) c3 on sku.category3_id = c3.id
left join (
    select
        id,
        name category2_name,
        category1_id
    from ods_base_category2_full
    where dt = '2022-06-08'
) c2 on c3.category2_id = c2.id
left join (
    select
        id,
        name category1_name
    from ods_base_category1_full
    where dt = '2022-06-08'
) c1 on c2.category1_id = c1.id
left join (
    select
        sku_id,
        collect_list(named_struct("attr_id", attr_id, "value_id", value_id, "attr_name", attr_name, "value_name", value_name)) sku_attr_values
    from ods_sku_attr_value_full
    where dt = '2022-06-08'
    group by sku_id
) sav on sav.sku_id = sku.id
left join (
    select
        sku_id,
        collect_list(named_struct("sale_attr_id", sale_attr_id, "sale_attr_value_id", sale_attr_value_id, "sale_attr_name", sale_attr_name, "sale_attr_value_name", sale_attr_value_name)) sku_sale_attr_values
    from ods_sku_sale_attr_value_full
    where dt = '2022-06-08'
    group by sku_id
) ssav on ssav.sku_id = sku.id;

-- 1. struct
-- 2. 相同的sku n * struct -> array
    -- 聚合函数 -> array
select
    sku_id,
    collect_list(named_struct("attr_id", attr_id, "value_id", value_id, "attr_name", attr_name, "value_name", value_name))
from ods_sku_attr_value_full
where dt = '2022-06-08'
group by sku_id;

-- CTE
select
    *
from (
    select *
    from (
        select
            *
        from ods_user_info_inc
        where dt = '2022-06-08'
    ) t1
) t
join (
    select
        *
    from (
        select *
        from ods_user_info_inc
        where dt = '2022-06-08'
    ) t1
) t2;

with t as (select * from ods_user_info_inc where dt = '2022-06-08')
select
    *
from (
    select
        *
    from t
) t1
join (
    select
        *
    from t
) t2;


-- 优惠券维度表
    -- 主维表：coupon_info
    -- 相关维表
        -- coupon_range（冗余）
        -- coupon_use（行为）
DROP TABLE IF EXISTS dim_coupon_full;
CREATE EXTERNAL TABLE dim_coupon_full
(
    `id`                  STRING COMMENT '优惠券编号',
    `coupon_name`       STRING COMMENT '优惠券名称',
    `coupon_type_code` STRING COMMENT '优惠券类型编码', -- 编码与名称共存
    `coupon_type_name` STRING COMMENT '优惠券类型名称',
    `condition_amount` DECIMAL(16, 2) COMMENT '满额数',
    `condition_num`     BIGINT COMMENT '满件数',
    `activity_id`       STRING COMMENT '活动编号',
    `benefit_amount`   DECIMAL(16, 2) COMMENT '减免金额',
    `benefit_discount` DECIMAL(16, 2) COMMENT '折扣',
    `benefit_rule`     STRING COMMENT '优惠规则:满元*减*元，满*件打*折', -- 沉淀，通过计算得到
    `create_time`       STRING COMMENT '创建时间',
    `range_type_code`  STRING COMMENT '优惠范围类型编码',
    `range_type_name`  STRING COMMENT '优惠范围类型名称',
    `limit_num`         BIGINT COMMENT '最多领取次数',
    `taken_count`       BIGINT COMMENT '已领取次数',
    `start_time`        STRING COMMENT '可以领取的开始时间',
    `end_time`          STRING COMMENT '可以领取的结束时间',
    `operate_time`      STRING COMMENT '修改时间',
    `expire_time`       STRING COMMENT '过期时间'
) COMMENT '优惠券维度表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dim/dim_coupon_full/'
    TBLPROPERTIES ('orc.compress' = 'snappy');


-- 数据装载
    -- load
        -- coupon_info
        -- base_dic
    -- save
insert overwrite table dim_coupon_full partition (dt='2022-06-08')
select
    `id`                  ,--STRING COMMENT '优惠券编号',
    `coupon_name`         ,--STRING COMMENT '优惠券名称',
    `coupon_type_code`    ,--STRING COMMENT '优惠券类型编码', -- 编码与名称共存
    `coupon_type_name`    ,--STRING COMMENT '优惠券类型名称',
    `condition_amount`    ,--DECIMAL(16, 2) COMMENT '满额数',
    `condition_num`       ,--BIGINT COMMENT '满件数',
    `activity_id`         ,--STRING COMMENT '活动编号',
    `benefit_amount`      ,--DECIMAL(16, 2) COMMENT '减免金额',
    `benefit_discount`    ,--DECIMAL(16, 2) COMMENT '折扣',
    `benefit_rule`        ,--STRING COMMENT '优惠规则:满元*减*元，满*件打*折', -- 沉淀，通过计算得到
    `create_time`         ,--STRING COMMENT '创建时间',
    `range_type_code`     ,--STRING COMMENT '优惠范围类型编码',
    `range_type_name`     ,--STRING COMMENT '优惠范围类型名称',
    `limit_num`           ,--BIGINT COMMENT '最多领取次数',
    `taken_count`         ,--BIGINT COMMENT '已领取次数',
    `start_time`          ,--STRING COMMENT '可以领取的开始时间',
    `end_time`            ,--STRING COMMENT '可以领取的结束时间',
    `operate_time`        ,--STRING COMMENT '修改时间',
    `expire_time`         --STRING COMMENT '过期时间'
from (
    select
        `id`                  ,--STRING COMMENT '优惠券编号',
        `coupon_name`         ,--STRING COMMENT '优惠券名称',
        coupon_type `coupon_type_code`    ,--STRING COMMENT '优惠券类型编码', -- 编码与名称共存
        --`coupon_type_name`    ,--STRING COMMENT '优惠券类型名称',
        `condition_amount`    ,--DECIMAL(16, 2) COMMENT '满额数',
        `condition_num`       ,--BIGINT COMMENT '满件数',
        `activity_id`         ,--STRING COMMENT '活动编号',
        `benefit_amount`      ,--DECIMAL(16, 2) COMMENT '减免金额',
        `benefit_discount`    ,--DECIMAL(16, 2) COMMENT '折扣',
        case coupon_type
            when '3201' then concat('满', condition_amount, '元减', benefit_amount, '元')
            when '3202' then concat('满', condition_num, '件打', benefit_discount, '折')
            when '3203' then concat('减', benefit_amount, '元')
        end `benefit_rule`        ,--STRING COMMENT '优惠规则:满元*减*元，满*件打*折', -- 沉淀，通过计算得到
        `create_time`         ,--STRING COMMENT '创建时间',
        range_type `range_type_code`     ,--STRING COMMENT '优惠范围类型编码',
        --`range_type_name`     ,--STRING COMMENT '优惠范围类型名称',
        `limit_num`           ,--BIGINT COMMENT '最多领取次数',
        `taken_count`         ,--BIGINT COMMENT '已领取次数',
        `start_time`          ,--STRING COMMENT '可以领取的开始时间',
        `end_time`            ,--STRING COMMENT '可以领取的结束时间',
        `operate_time`        ,--STRING COMMENT '修改时间',
        `expire_time`         --STRING COMMENT '过期时间'
    from ods_coupon_info_full
    where dt = '2022-06-08'
) cp
join (
    select
        dic_code,
        dic_name coupon_type_name
    from ods_base_dic_full
    where dt = '2022-06-08'
    and parent_code= '32'
) dic1 on dic1.dic_code = cp.coupon_type_code
join (
    select
        dic_code,
        dic_name range_type_name
    from ods_base_dic_full
    where dt = '2022-06-08'
    and parent_code= '33'
) dic2 on dic2.dic_code = cp.range_type_code;

-- 活动维度表
    -- activity_info：相关维表
    -- activity_rule：主维表
    -- activity_sku (x)

DROP TABLE IF EXISTS dim_activity_full;
CREATE EXTERNAL TABLE dim_activity_full
(
    `activity_rule_id`   STRING COMMENT '活动规则ID',
    `activity_id`         STRING COMMENT '活动ID',
    `activity_name`       STRING COMMENT '活动名称',
    `activity_type_code` STRING COMMENT '活动类型编码',
    `activity_type_name` STRING COMMENT '活动类型名称',
    `activity_desc`       STRING COMMENT '活动描述',
    `start_time`           STRING COMMENT '开始时间',
    `end_time`             STRING COMMENT '结束时间',
    `create_time`          STRING COMMENT '创建时间',
    `condition_amount`    DECIMAL(16, 2) COMMENT '满减金额',
    `condition_num`       BIGINT COMMENT '满减件数',
    `benefit_amount`      DECIMAL(16, 2) COMMENT '优惠金额',
    `benefit_discount`   DECIMAL(16, 2) COMMENT '优惠折扣',
    `benefit_rule`        STRING COMMENT '优惠规则',
    `benefit_level`       STRING COMMENT '优惠级别'
) COMMENT '活动维度表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dim/dim_activity_full/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

-- 数据装载
insert overwrite table dim_activity_full partition (dt='2022-06-08')
select
    `activity_rule_id`   ,--STRING COMMENT '活动规则ID',
    `activity_id`        ,-- STRING COMMENT '活动ID',
    `activity_name`      ,-- STRING COMMENT '活动名称',
    `activity_type_code` ,--STRING COMMENT '活动类型编码',
    `activity_type_name` ,--STRING COMMENT '活动类型名称',
    `activity_desc`      ,-- STRING COMMENT '活动描述',
    `start_time`         ,--  STRING COMMENT '开始时间',
    `end_time`           ,--  STRING COMMENT '结束时间',
    `create_time`        ,--  STRING COMMENT '创建时间',
    `condition_amount`   ,-- DECIMAL(16, 2) COMMENT '满减金额',
    `condition_num`      ,-- BIGINT COMMENT '满减件数',
    `benefit_amount`     ,-- DECIMAL(16, 2) COMMENT '优惠金额',
    `benefit_discount`   ,--DECIMAL(16, 2) COMMENT '优惠折扣',
    `benefit_rule`       ,-- STRING COMMENT '优惠规则',
    `benefit_level`      -- STRING COMMENT '优惠级别'
from (
    select
        `id` `activity_rule_id`, -- STRING COMMENT '活动规则ID',
        `activity_id`        ,-- STRING COMMENT '活动ID',
        --`activity_name`      ,-- STRING COMMENT '活动名称',
        activity_type `activity_type_code` ,-- STRING COMMENT '活动类型编码',
        --`activity_type_name` ,-- STRING COMMENT '活动类型名称',
        `create_time`        ,-- STRING COMMENT '创建时间',
        `condition_amount`   ,-- DECIMAL(16, 2) COMMENT '满减金额',
        `condition_num`      ,-- BIGINT COMMENT '满减件数',
        `benefit_amount`     ,-- DECIMAL(16, 2) COMMENT '优惠金额',
        `benefit_discount`   ,-- DECIMAL(16, 2) COMMENT '优惠折扣',
        case activity_type
            when '3101' then concat('满', condition_amount, '元减', benefit_amount, '元')
            when '3102' then concat('满', condition_num, '件打', benefit_discount, '折')
            when '3103' then concat('打', benefit_discount, '折')
        end `benefit_rule`  ,-- STRING COMMENT '优惠规则',
        `benefit_level`      -- STRING COMMENT '优惠级别'
    from ods_activity_rule_full
    where dt = '2022-06-08'
) rule
left join (
    select
        id,
        activity_name,
        `activity_desc`      ,-- STRING COMMENT '活动描述',
        `start_time`         ,-- STRING COMMENT '开始时间',
        `end_time`            -- STRING COMMENT '结束时间',
    from ods_activity_info_full
    where dt = '2022-06-08'

) info on rule.activity_id = info.id
left join (
    select
        dic_code,
        dic_name activity_type_name
    from ods_base_dic_full
    where dt = '2022-06-08'
    and parent_code = '31'
) dic on rule.activity_type_code = dic.dic_code;

-- 地区维度表
    -- 建表语句
DROP TABLE IF EXISTS dim_province_full;
CREATE EXTERNAL TABLE dim_province_full
(
    `id`              STRING COMMENT '省份ID',
    `province_name` STRING COMMENT '省份名称',
    `area_code`     STRING COMMENT '地区编码',
    `iso_code`      STRING COMMENT '旧版国际标准地区编码，供可视化使用',
    `iso_3166_2`    STRING COMMENT '新版国际标准地区编码，供可视化使用',
    `region_id`     STRING COMMENT '地区ID',
    `region_name`   STRING COMMENT '地区名称'
) COMMENT '地区维度表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dim/dim_province_full/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

-- 数据装载
insert overwrite table dim_province_full partition (dt='2022-06-08')
select
    prv.`id`              ,--STRING COMMENT '省份ID',
    `province_name`       ,--STRING COMMENT '省份名称',
    `area_code`           ,--STRING COMMENT '地区编码',
    `iso_code`            ,--STRING COMMENT '旧版国际标准地区编码，供可视化使用',
    `iso_3166_2`          ,--STRING COMMENT '新版国际标准地区编码，供可视化使用',
    `region_id`           ,--STRING COMMENT '地区ID',
    `region_name`         --STRING COMMENT '地区名称'
from (
    select
        `id`              ,--STRING COMMENT '省份ID',
        name `province_name`   ,--STRING COMMENT '省份名称',
        `area_code`       ,--STRING COMMENT '地区编码',
        `iso_code`        ,--STRING COMMENT '旧版国际标准地区编码，供可视化使用',
        `iso_3166_2`      ,--STRING COMMENT '新版国际标准地区编码，供可视化使用',
        `region_id`       --STRING COMMENT '地区ID',
        --`region_name`     --STRING COMMENT '地区名称'
    from ods_base_province_full
    where dt = '2022-06-08'
) prv
left join (
    select
        id,
        region_name
    from ods_base_region_full
    where dt = '2022-06-08'
) region on prv.region_id = region.id;

-- 营销坑位表
    -- 建表语句
DROP TABLE IF EXISTS dim_promotion_pos_full;
CREATE EXTERNAL TABLE dim_promotion_pos_full
(
    `id`                 STRING COMMENT '营销坑位ID',
    `pos_location`       STRING COMMENT '营销坑位位置',
    `pos_type`           STRING COMMENT '营销坑位类型 ',
    `promotion_type`     STRING COMMENT '营销类型',
    `create_time`        STRING COMMENT '创建时间',
    `operate_time`       STRING COMMENT '修改时间'
) COMMENT '营销坑位维度表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dim/dim_promotion_pos_full/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

insert overwrite table dim_promotion_pos_full partition (dt='2022-06-08')
select
    `id`                ,--STRING COMMENT '营销坑位ID',
    `pos_location`      ,--STRING COMMENT '营销坑位位置',
    `pos_type`          ,--STRING COMMENT '营销坑位类型 ',
    `promotion_type`    ,--STRING COMMENT '营销类型',
    `create_time`       ,--STRING COMMENT '创建时间',
    `operate_time`       --STRING COMMENT '修改时间'
from ods_promotion_pos_full
where dt = '2022-06-08';

-- 营销渠道维度表
    -- 建表语句
DROP TABLE IF EXISTS dim_promotion_refer_full;
CREATE EXTERNAL TABLE dim_promotion_refer_full
(
    `id`                  STRING COMMENT '营销渠道ID',
    `refer_name`          STRING COMMENT '营销渠道名称',
    `create_time`         STRING COMMENT '创建时间',
    `operate_time`        STRING COMMENT '修改时间'
) COMMENT '营销渠道维度表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dim/dim_promotion_refer_full/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

insert overwrite table dim_promotion_refer_full partition (dt='2022-06-08')
select
    `id`                  ,--STRING COMMENT '营销渠道ID',
    `refer_name`          ,--STRING COMMENT '营销渠道名称',
    `create_time`         ,--STRING COMMENT '创建时间',
    `operate_time`         --STRING COMMENT '修改时间'
from ods_promotion_refer_full
where dt = '2022-06-08';

-- 日期维度表
    -- 建表语句
DROP TABLE IF EXISTS dim_date;
CREATE EXTERNAL TABLE dim_date
(
    `date_id`    STRING COMMENT '日期ID',
    `week_id`    STRING COMMENT '周ID,一年中的第几周',
    `week_day`   STRING COMMENT '周几',
    `day`         STRING COMMENT '每月的第几天',
    `month`       STRING COMMENT '一年中的第几月',
    `quarter`    STRING COMMENT '一年中的第几季度',
    `year`        STRING COMMENT '年份',
    `is_workday` STRING COMMENT '是否是工作日',
    `holiday_id` STRING COMMENT '节假日'
) COMMENT '日期维度表'
    STORED AS ORC
    LOCATION '/warehouse/gmall/dim/dim_date/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

-- 数据装载
    -- 创建临时表
DROP TABLE IF EXISTS tmp_dim_date_info;
CREATE EXTERNAL TABLE tmp_dim_date_info (
    `date_id`       STRING COMMENT '日',
    `week_id`       STRING COMMENT '周ID',
    `week_day`      STRING COMMENT '周几',
    `day`            STRING COMMENT '每月的第几天',
    `month`          STRING COMMENT '第几月',
    `quarter`       STRING COMMENT '第几季度',
    `year`           STRING COMMENT '年',
    `is_workday`    STRING COMMENT '是否是工作日',
    `holiday_id`    STRING COMMENT '节假日'
) COMMENT '时间维度表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' -- tsv
LOCATION '/warehouse/gmall/tmp/tmp_dim_date_info/';

-- tsv（行式） -> orc（列式）
-- Hive -> SQL -> Spark
insert into table dim_date select * from tmp_dim_date_info; -- 自动转换格式

-- 用户维度表（用增量的方式来表示全量）
    -- user_info
    -- user_address
    -- 建表语句
DROP TABLE IF EXISTS dim_user_zip;
CREATE EXTERNAL TABLE dim_user_zip
(
    `id`           STRING COMMENT '用户ID',
    `name`         STRING COMMENT '用户姓名',
    `phone_num`    STRING COMMENT '手机号码',
    `email`        STRING COMMENT '邮箱',
    `user_level`   STRING COMMENT '用户等级',
    `birthday`     STRING COMMENT '生日',
    `gender`       STRING COMMENT '性别',
    `create_time`  STRING COMMENT '创建时间',
    `operate_time` STRING COMMENT '操作时间',
    `start_date`   STRING COMMENT '开始日期',  -- 类似于聚合
    `end_date`     STRING COMMENT '结束日期'
) COMMENT '用户维度表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dim/dim_user_zip/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

insert overwrite table dim_user_zip partition (dt='2022-06-08')
select
    *
from ods_user_info_inc
where dt = '2022-06-08';

-- 数据的基数
    -- 每日全量数据
        -- 每天同步一份全部的数据
            -- 40 * 400 * 5 = 80000（行） * 1kb = 80Mb
            -- 40w * 400 * 5 = 800Gb
        -- 用户
            -- 2000w * 400 * 5 = 40Tb
    -- 将数据状态的变化记录下来，而不是全部数据，而是指定时间范围 (start, end)
        -- 在表的设计中，需要增加两个额外的字段，用于标识时间范围 (start, end)
        -- 表设计为拉链表(zip)即可
        -- 一般情况下，拉链表中截止到今天依然有效的状态的结束时间，为了不修改，所以设置为时间极大值 9999-12-31

-- 拉链表的数据源应该是ods层增量表
    -- 增量表
        -- maxwell
            -- 数据格式：json
            -- 同步方式：
                -- 首日：bootstrap(select)
                -- 每日：insert, update, delete(binlog)
-- 首日：全量
    -- 2022-06-08：数仓上线首日：user_info_all
        -- 首日获取的全量数据只有用户的最新状态数据，不存在历史数据，所以无法判断状态的开始
            -- 所以折中地认为首日就是当前最新状态的开始日期
            -- 将最新状态的结束时间设为时间极大值
    -- 拉链表的分区策略
        -- 将数据存储到哪一个分区，更方便我们的查询
            -- 假设将用户数据保存到用户的开始日期的分区中
                -- zhangsan 2022-06-08 9999-12-31 => 2022-06-08
                -- lisi     2022-06-08 2022-06-08 => 2022-06-08
                -- lisi     2022-06-09 9999-12-31 => 2022-06-09
                -- select * from t_user where dt = '2022-06-08';
            -- 假设将用户数据保存到用户的结束日期的分区中
                -- zhangsan 2022-06-08 9999-12-31 => 9999-12-31
                -- lisi     2022-06-08 2022-06-08 => 2022-06-08
                -- lisi     2022-06-09 9999-12-31 => 9999-12-31
                -- select * from t_user where dt = '2022-06-08';
        -- 将数据存储到结束时间所在分区
insert overwrite table dim_user_zip partition (dt='9999-12-31')
select
    type,
    data.`id`           ,--STRING COMMENT '用户ID',
    data.`name`         ,--STRING COMMENT '用户姓名',
    data.`phone_num`    ,--STRING COMMENT '手机号码',
    data.`email`        ,--STRING COMMENT '邮箱',
    data.`user_level`   ,--STRING COMMENT '用户等级',
    data.`birthday`     ,--STRING COMMENT '生日',
    data.`gender`       ,--STRING COMMENT '性别',
    data.`create_time`  ,--STRING COMMENT '创建时间',
    data.`operate_time` ,--STRING COMMENT '操作时间',
    '2022-06-08'   ,--STRING COMMENT '开始日期',
    '9999-12-31'     --STRING COMMENT '结束日期'
from ods_user_info_inc
where dt = '2022-06-08'
and type = 'bootstrap-insert';

-- 每日：增量
    -- 增加的用户信息
        -- type：insert
        -- start：当天（2022-06-09）
        -- end：9999-12-31
        -- partition：9999-12-31
    -- 修改的用户信息
        -- 修改后
            -- type：update
            -- start：当天（2022-06-09）
            -- end：9999-12-31
            -- partition：9999-12-31
        -- 修改前
            -- dim_user_zip (dt=9999-12-31)
            -- start：start
            -- end：9999-12-31 -> 2022-06-08
            -- partition：9999-12-31 -> 2022-06-08

-- 修改
-- insert overwrite table  xxx
-- select
--     '2022-06-08' dt
-- from (
--     select '9999-12-31' dt
-- ) t

-- 每日数据装载
    -- union：去重
    -- union all：不去重
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dim_user_zip partition (dt) -- 动态分区
select
    `id`           ,--STRING COMMENT '用户ID',
    `name`         ,--STRING COMMENT '用户姓名',
    `phone_num`    ,--STRING COMMENT '手机号码',
    `email`        ,--STRING COMMENT '邮箱',
    `user_level`   ,--STRING COMMENT '用户等级',
    `birthday`     ,--STRING COMMENT '生日',
    `gender`       ,--STRING COMMENT '性别',
    `create_time`  ,--STRING COMMENT '创建时间',
    `operate_time` ,--STRING COMMENT '操作时间',
    `start_date`   ,--STRING COMMENT '开始日期',
    if ( rn == 2, date_sub('2022-06-09', 1), '9999-12-31'),
    if ( rn == 2, date_sub('2022-06-09', 1), '9999-12-31')
from (
    select
        *,
        row_number() over ( partition by id order by start_date desc ) rn
    from (
        select
            `id`           ,--STRING COMMENT '用户ID',
            `name`         ,--STRING COMMENT '用户姓名',
            `phone_num`    ,--STRING COMMENT '手机号码',
            `email`        ,--STRING COMMENT '邮箱',
            `user_level`   ,--STRING COMMENT '用户等级',
            `birthday`     ,--STRING COMMENT '生日',
            `gender`       ,--STRING COMMENT '性别',
            `create_time`  ,--STRING COMMENT '创建时间',
            `operate_time` ,--STRING COMMENT '操作时间',
            `start_date`   ,--STRING COMMENT '开始日期',
            `end_date`      --STRING COMMENT '结束日期'
        from dim_user_zip
        where dt = '9999-12-31'
        union all
        select
            data.`id`           ,--STRING COMMENT '用户ID',
            data.`name`         ,--STRING COMMENT '用户姓名',
            data.`phone_num`    ,--STRING COMMENT '手机号码',
            data.`email`        ,--STRING COMMENT '邮箱',
            data.`user_level`   ,--STRING COMMENT '用户等级',
            data.`birthday`     ,--STRING COMMENT '生日',
            data.`gender`       ,--STRING COMMENT '性别',
            data.`create_time`  ,--STRING COMMENT '创建时间',
            data.`operate_time` ,--STRING COMMENT '操作时间',
            '2022-06-09'   ,--STRING COMMENT '开始日期',  -- 类似于聚合
            '9999-12-31'      --STRING COMMENT '结束日期'
        from ods_user_info_inc
        where dt = '2022-06-09'
        and type in ('insert', 'update')
    ) t
) t1;

-- 比对方式
    -- 将数据集合并（union）在一起进行比对，需要查找数据的规律
    -- 将数据集连接（join）在一起进行比对
        -- full join + inner join

set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dim_user_zip partition (dt) -- 动态分区
select
    `id`           ,--STRING COMMENT '用户ID',
    `name`         ,--STRING COMMENT '用户姓名',
    `phone_num`    ,--STRING COMMENT '手机号码',
    `email`        ,--STRING COMMENT '邮箱',
    `user_level`   ,--STRING COMMENT '用户等级',
    `birthday`     ,--STRING COMMENT '生日',
    `gender`       ,--STRING COMMENT '性别',
    `create_time`  ,--STRING COMMENT '创建时间',
    `operate_time` ,--STRING COMMENT '操作时间',
    `start_date`   ,--STRING COMMENT '开始日期',
    if ( rn == 2, date_sub('2022-06-09', 1), '9999-12-31'),
    if ( rn == 2, date_sub('2022-06-09', 1), '9999-12-31')
from (
    select
        *,
        row_number() over ( partition by id order by start_date desc ) rn
    from (
        select
            `id`           ,--STRING COMMENT '用户ID',
            `name`         ,--STRING COMMENT '用户姓名',
            `phone_num`    ,--STRING COMMENT '手机号码',
            `email`        ,--STRING COMMENT '邮箱',
            `user_level`   ,--STRING COMMENT '用户等级',
            `birthday`     ,--STRING COMMENT '生日',
            `gender`       ,--STRING COMMENT '性别',
            `create_time`  ,--STRING COMMENT '创建时间',
            `operate_time` ,--STRING COMMENT '操作时间',
            `start_date`   ,--STRING COMMENT '开始日期',
            `end_date`      --STRING COMMENT '结束日期'
        from dim_user_zip
        where dt = '9999-12-31'
        union all
        select
            `id`           ,--STRING COMMENT '用户ID',
            `name`         ,--STRING COMMENT '用户姓名',
            `phone_num`    ,--STRING COMMENT '手机号码',
            `email`        ,--STRING COMMENT '邮箱',
            `user_level`   ,--STRING COMMENT '用户等级',
            `birthday`     ,--STRING COMMENT '生日',
            `gender`       ,--STRING COMMENT '性别',
            `create_time`  ,--STRING COMMENT '创建时间',
            `operate_time` ,--STRING COMMENT '操作时间',
            '2022-06-09'   ,--STRING COMMENT '开始日期',
            '9999-12-31'      --STRING COMMENT '结束日期'
        from (
            select
                data.`id`           ,--STRING COMMENT '用户ID',
                data.`name`         ,--STRING COMMENT '用户姓名',
                data.`phone_num`    ,--STRING COMMENT '手机号码',
                data.`email`        ,--STRING COMMENT '邮箱',
                data.`user_level`   ,--STRING COMMENT '用户等级',
                data.`birthday`     ,--STRING COMMENT '生日',
                data.`gender`       ,--STRING COMMENT '性别',
                data.`create_time`  ,--STRING COMMENT '创建时间',
                data.`operate_time` ,--STRING COMMENT '操作时间',
                row_number() over (partition by data.id order by ts desc) num
            from ods_user_info_inc
            where dt = '2022-06-09'
            and type in ('insert', 'update')
        ) a where num = 1
    ) t
) t1

  • DWD层 

       1
   2
   3
   4
   5
   6
   7
   8
   9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35
  36
  37
  38
  39
  40
  41
  42
  43
  44
  45
  46
  47
  48
  49
  50
  51
  52
  53
  54
  55
  56
  57
  58
  59
  60
  61
  62
  63
  64
  65
  66
  67
  68
  69
  70
  71
  72
  73
  74
  75
  76
  77
  78
  79
  80
  81
  82
  83
  84
  85
  86
  87
  88
  89
  90
  91
  92
  93
  94
  95
  96
  97
  98
  99
 100
 101
 102
 103
 104
 105
 106
 107
 108
 109
 110
 111
 112
 113
 114
 115
 116
 117
 118
 119
 120
 121
 122
 123
 124
 125
 126
 127
 128
 129
 130
 131
 132
 133
 134
 135
 136
 137
 138
 139
 140
 141
 142
 143
 144
 145
 146
 147
 148
 149
 150
 151
 152
 153
 154
 155
 156
 157
 158
 159
 160
 161
 162
 163
 164
 165
 166
 167
 168
 169
 170
 171
 172
 173
 174
 175
 176
 177
 178
 179
 180
 181
 182
 183
 184
 185
 186
 187
 188
 189
 190
 191
 192
 193
 194
 195
 196
 197
 198
 199
 200
 201
 202
 203
 204
 205
 206
 207
 208
 209
 210
 211
 212
 213
 214
 215
 216
 217
 218
 219
 220
 221
 222
 223
 224
 225
 226
 227
 228
 229
 230
 231
 232
 233
 234
 235
 236
 237
 238
 239
 240
 241
 242
 243
 244
 245
 246
 247
 248
 249
 250
 251
 252
 253
 254
 255
 256
 257
 258
 259
 260
 261
 262
 263
 264
 265
 266
 267
 268
 269
 270
 271
 272
 273
 274
 275
 276
 277
 278
 279
 280
 281
 282
 283
 284
 285
 286
 287
 288
 289
 290
 291
 292
 293
 294
 295
 296
 297
 298
 299
 300
 301
 302
 303
 304
 305
 306
 307
 308
 309
 310
 311
 312
 313
 314
 315
 316
 317
 318
 319
 320
 321
 322
 323
 324
 325
 326
 327
 328
 329
 330
 331
 332
 333
 334
 335
 336
 337
 338
 339
 340
 341
 342
 343
 344
 345
 346
 347
 348
 349
 350
 351
 352
 353
 354
 355
 356
 357
 358
 359
 360
 361
 362
 363
 364
 365
 366
 367
 368
 369
 370
 371
 372
 373
 374
 375
 376
 377
 378
 379
 380
 381
 382
 383
 384
 385
 386
 387
 388
 389
 390
 391
 392
 393
 394
 395
 396
 397
 398
 399
 400
 401
 402
 403
 404
 405
 406
 407
 408
 409
 410
 411
 412
 413
 414
 415
 416
 417
 418
 419
 420
 421
 422
 423
 424
 425
 426
 427
 428
 429
 430
 431
 432
 433
 434
 435
 436
 437
 438
 439
 440
 441
 442
 443
 444
 445
 446
 447
 448
 449
 450
 451
 452
 453
 454
 455
 456
 457
 458
 459
 460
 461
 462
 463
 464
 465
 466
 467
 468
 469
 470
 471
 472
 473
 474
 475
 476
 477
 478
 479
 480
 481
 482
 483
 484
 485
 486
 487
 488
 489
 490
 491
 492
 493
 494
 495
 496
 497
 498
 499
 500
 501
 502
 503
 504
 505
 506
 507
 508
 509
 510
 511
 512
 513
 514
 515
 516
 517
 518
 519
 520
 521
 522
 523
 524
 525
 526
 527
 528
 529
 530
 531
 532
 533
 534
 535
 536
 537
 538
 539
 540
 541
 542
 543
 544
 545
 546
 547
 548
 549
 550
 551
 552
 553
 554
 555
 556
 557
 558
 559
 560
 561
 562
 563
 564
 565
 566
 567
 568
 569
 570
 571
 572
 573
 574
 575
 576
 577
 578
 579
 580
 581
 582
 583
 584
 585
 586
 587
 588
 589
 590
 591
 592
 593
 594
 595
 596
 597
 598
 599
 600
 601
 602
 603
 604
 605
 606
 607
 608
 609
 610
 611
 612
 613
 614
 615
 616
 617
 618
 619
 620
 621
 622
 623
 624
 625
 626
 627
 628
 629
 630
 631
 632
 633
 634
 635
 636
 637
 638
 639
 640
 641
 642
 643
 644
 645
 646
 647
 648
 649
 650
 651
 652
 653
 654
 655
 656
 657
 658
 659
 660
 661
 662
 663
 664
 665
 666
 667
 668
 669
 670
 671
 672
 673
 674
 675
 676
 677
 678
 679
 680
 681
 682
 683
 684
 685
 686
 687
 688
 689
 690
 691
 692
 693
 694
 695
 696
 697
 698
 699
 700
 701
 702
 703
 704
 705
 706
 707
 708
 709
 710
 711
 712
 713
 714
 715
 716
 717
 718
 719
 720
 721
 722
 723
 724
 725
 726
 727
 728
 729
 730
 731
 732
 733
 734
 735
 736
 737
 738
 739
 740
 741
 742
 743
 744
 745
 746
 747
 748
 749
 750
 751
 752
 753
 754
 755
 756
 757
 758
 759
 760
 761
 762
 763
 764
 765
 766
 767
 768
 769
 770
 771
 772
 773
 774
 775
 776
 777
 778
 779
 780
 781
 782
 783
 784
 785
 786
 787
 788
 789
 790
 791
 792
 793
 794
 795
 796
 797
 798
 799
 800
 801
 802
 803
 804
 805
 806
 807
 808
 809
 810
 811
 812
 813
 814
 815
 816
 817
 818
 819
 820
 821
 822
 823
 824
 825
 826
 827
 828
 829
 830
 831
 832
 833
 834
 835
 836
 837
 838
 839
 840
 841
 842
 843
 844
 845
 846
 847
 848
 849
 850
 851
 852
 853
 854
 855
 856
 857
 858
 859
 860
 861
 862
 863
 864
 865
 866
 867
 868
 869
 870
 871
 872
 873
 874
 875
 876
 877
 878
 879
 880
 881
 882
 883
 884
 885
 886
 887
 888
 889
 890
 891
 892
 893
 894
 895
 896
 897
 898
 899
 900
 901
 902
 903
 904
 905
 906
 907
 908
 909
 910
 911
 912
 913
 914
 915
 916
 917
 918
 919
 920
 921
 922
 923
 924
 925
 926
 927
 928
 929
 930
 931
 932
 933
 934
 935
 936
 937
 938
 939
 940
 941
 942
 943
 944
 945
 946
 947
 948
 949
 950
 951
 952
 953
 954
 955
 956
 957
 958
 959
 960
 961
 962
 963
 964
 965
 966
 967
 968
 969
 970
 971
 972
 973
 974
 975
 976
 977
 978
 979
 980
 981
 982
 983
 984
 985
 986
 987
 988
 989
 990
 991
 992
 993
 994
 995
 996
 997
 998
 999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136

    -- DWD层
    -- Data Warehouse Detail
        -- detail：详细、明细
        -- 对ODS层的数据进行加工，为统计分析做准备
        -- DIM层主要功能其实是分析数据（面向状态）
        -- DWD层主要功能其实是统计分析（面向行为）
        -- DWD层的表中主要保存的就是业务行为数据，表的设计需要遵循建模理论 - 维度建模 - 事实（行为）表
        -- 数据存储格式：列式存储
        -- 数据压缩格式：snappy
        -- 业务过程其实指代的就是业务行为
    -- 命名规范
        -- 分层标记（dwd_） + 数据域（分类） + 原子行为（login_success） + 全量 / 增量
            -- 原则上来讲，所有的行为都应该是增量数据
                -- 特殊情况下，会采用全量方式实现行为统计
    -- 事实表
        -- 包含维度
            -- 维度越多，行为越详细；维度越少，行为越简单
        -- 包含度量值
            -- 所有的行为必须可以用于统计，这里用于统计的值（字段）就是度量值
    -- 事实表分类
        -- 事务事实表
            -- 绝大多数的事实表都是事务型事实表
                -- 事务：原子性
                -- 原子操作（行为）
                    -- login success (table)
                    -- login fail (table)
            -- 粒度：行为描述的详细程度，称之为粒度
                -- 描述的越详细，粒度越细，称之为细粒度
                    -- 维度越多，粒度越细
                -- 描述的越简单，粒度越粗，称之为粗粒度
                    -- 维度越少，粒度越粗
            -- 建立表的时候，尽可能让粒度越细
            -- 创建表的步骤：
                -- 选择业务过程：确定创建什么表
                -- 声明粒度：确定行
                -- 确认维度：确定列
                -- 确认事实：确定度量值
        -- 周期快照事实表
        -- 累计快照事实表

-- 交易域加购事务事实表
    -- 交易域
    -- 加购
        -- 购物车中没有这个商品，新增商品
        -- 购物车中有这个商品，增加购买商品的数量
    -- 事务事实表：至少将行为描述清楚
        -- 表：dwd_trade_add_cart_inc
        -- 行：用户 + 时间 + 商品 + 商品数量
        -- 列：user + date + sku + num
        -- 度量值：num + 次数

    -- 建表语句
DROP TABLE IF EXISTS dwd_trade_cart_add_inc;
CREATE EXTERNAL TABLE dwd_trade_cart_add_inc
(
    `id`                  STRING COMMENT '编号',
    `user_id`            STRING COMMENT '用户ID',
    `sku_id`             STRING COMMENT 'SKU_ID',
    `date_id`            STRING COMMENT '日期ID',  -- yyyy-MM-dd 关联 dim_date
    `create_time`        STRING COMMENT '加购时间', -- yyyy-MM-dd hh:mm:ss
    `sku_num`            BIGINT COMMENT '加购物车件数'
) COMMENT '交易域加购事务事实表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dwd/dwd_trade_cart_add_inc/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

-- 数据装载
    -- load
        -- 获取加购行为的数据
            -- maxwell：增量数据（insert, update）
    -- save
-- 从ods层的增量表中获取数据，能想到什么？
    -- 数据格式：JSON
    -- 首日：全量
        -- 历史数据（4、5、6、7、8）
        -- 无法判断行为
            -- 折中地认为当前的数据全部都是新增购物
    -- 每日：增量
        -- 当天新增及变化
    -- 分区策略
        -- 哪一天的行为数据就存放到哪一天的分区中
insert overwrite table dwd_trade_cart_add_inc partition (dt)
select
    data.`id`                 ,--STRING COMMENT '编号',
    data.`user_id`            ,--STRING COMMENT '用户ID',
    data.`sku_id`             ,--STRING COMMENT 'SKU_ID',
    date_format(data.`create_time`, 'yyyy-MM-dd') `date_id`            ,--STRING COMMENT '日期ID',  -- yyyy-MM-dd 关联 dim_date
    data.`create_time`        ,--STRING COMMENT '加购时间', -- yyyy-MM-dd HH:mm:ss
    data.`sku_num`            ,--BIGINT COMMENT '加购物车件数'
    date_format(data.`create_time`, 'yyyy-MM-dd')
from ods_cart_info_inc
where dt = '2022-06-08'
and type = 'bootstrap-insert';

-- 每日数据装载：不包含历史数据，主要当天新增及变化的数据
    -- 首日：2022-06-08
    -- 每日：2022-06-09，10，11 ……
    -- maxwell：{update, old : Map}
insert overwrite table dwd_trade_cart_add_inc partition (dt='2022-06-09')
select
    data.id,
    data.user_id,
    data.sku_id,
    date_format(if( type = 'insert', data.create_time, data.operate_time), 'yyyy-MM-dd'),
    if( type = 'insert', data.create_time, data.operate_time),
    if( type = 'insert', data.sku_num, data.sku_num - old['sku_num']) -- 自动转换
from ods_cart_info_inc
where dt = '2022-06-09'
and (
    type = 'insert'
    or
    (
        type = 'update'
            and
        array_contains(map_keys(old), 'sku_num')
            and
        data.sku_num > cast(old['sku_num'] as bigint)
    )
);

-- long -> string(date) -> date_format(string, date)
insert overwrite table dwd_trade_cart_add_inc partition (dt='2022-06-09')
select
    data.id,
    data.user_id,
    data.sku_id,
    date_format(ts, 'yyyy-MM-dd'),
    ts,
    if( type = 'insert', data.sku_num, data.sku_num - old['sku_num']) -- 自动转换
from ods_cart_info_inc
where dt = '2022-06-09'
and (
    type = 'insert'
    or
    (
        type = 'update'
            and
        array_contains(map_keys(old), 'sku_num')
            and
        data.sku_num > cast(old['sku_num'] as bigint)
    )
);

-- from_unixtime：0时区的时间
select
    ts,
    from_unixtime(ts, 'yyyy-MM-dd HH:mm:ss'),
    date_format(from_utc_timestamp(ts * 1000, 'GMT+8'), 'yyyy-MM-dd HH:mm:ss')
from ods_cart_info_inc
where dt = '2022-06-08'
and type = 'bootstrap-insert';

-- 交易域下单事务事实表
    -- 交易域
    -- 下单
        -- 用户 + 时间 + 商品 + 订单 + 商品数量 + 商品金额
    -- 事务事实表
    -- 建表语句
DROP TABLE IF EXISTS dwd_trade_order_detail_inc;
CREATE EXTERNAL TABLE dwd_trade_order_detail_inc
(
    `id`                     STRING COMMENT '编号',
    `order_id`              STRING COMMENT '订单ID',
    `user_id`               STRING COMMENT '用户ID',
    `sku_id`                STRING COMMENT '商品ID',
    `province_id`          STRING COMMENT '省份ID',
    `activity_id`          STRING COMMENT '参与活动ID',
    `activity_rule_id`    STRING COMMENT '参与活动规则ID',
    `coupon_id`             STRING COMMENT '使用优惠券ID',
    `date_id`               STRING COMMENT '下单日期ID',
    `create_time`           STRING COMMENT '下单时间',
    `sku_num`                BIGINT COMMENT '商品数量',
    `split_original_amount` DECIMAL(16, 2) COMMENT '原始价格',
    `split_activity_amount` DECIMAL(16, 2) COMMENT '活动优惠分摊',
    `split_coupon_amount`   DECIMAL(16, 2) COMMENT '优惠券优惠分摊',
    `split_total_amount`    DECIMAL(16, 2) COMMENT '最终价格分摊'
) COMMENT '交易域下单事务事实表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dwd/dwd_trade_order_detail_inc/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

-- 数据装载
    -- load
    -- save

-- 首日：历史数据
insert overwrite table dwd_trade_order_detail_inc partition (dt)
select
    od.`id`                  ,--STRING COMMENT '编号',
    `order_id`               ,--STRING COMMENT '订单ID',
    `user_id`                ,--STRING COMMENT '用户ID',
    `sku_id`                 ,--STRING COMMENT '商品ID',
    `province_id`            ,--STRING COMMENT '省份ID',
    `activity_id`            ,--STRING COMMENT '参与活动ID',
    `activity_rule_id`       ,--STRING COMMENT '参与活动规则ID',
    `coupon_id`              ,--STRING COMMENT '使用优惠券ID',
    `date_id`                ,--STRING COMMENT '下单日期ID',
    `create_time`            ,--STRING COMMENT '下单时间',
    `sku_num`                ,--BIGINT COMMENT '商品数量',
    `split_original_amount`  ,--DECIMAL(16, 2) COMMENT '原始价格',
    `split_activity_amount`  ,--DECIMAL(16, 2) COMMENT '活动优惠分摊',
    `split_coupon_amount`    ,--DECIMAL(16, 2) COMMENT '优惠券优惠分摊',
    `split_total_amount`     ,--DECIMAL(16, 2) COMMENT '最终价格分摊'
    date_id
from (
    select
        data.`id`                    ,--STRING COMMENT '编号',
        data.`order_id`              ,--STRING COMMENT '订单ID',
        --data.`user_id`               ,--STRING COMMENT '用户ID',
        data.`sku_id`                ,--STRING COMMENT '商品ID',
        --data.`province_id`           ,--STRING COMMENT '省份ID',
        --data.`activity_id`           ,--STRING COMMENT '参与活动ID',
        --data.`activity_rule_id`      ,--STRING COMMENT '参与活动规则ID',
        --data.`coupon_id`             ,--STRING COMMENT '使用优惠券ID',
        --data.`date_id`               ,--STRING COMMENT '下单日期ID',
        --data.`create_time`           ,--STRING COMMENT '下单时间',
        data.`sku_num`               ,--BIGINT COMMENT '商品数量',
        data.`sku_num` * data.`order_price` `split_original_amount` ,--DECIMAL(16, 2) COMMENT '原始价格',
        nvl(data.`split_activity_amount`, 0) split_activity_amount ,--DECIMAL(16, 2) COMMENT '活动优惠分摊',
        nvl(data.`split_coupon_amount`, 0) split_coupon_amount ,--DECIMAL(16, 2) COMMENT '优惠券优惠分摊',
        data.`split_total_amount`     --DECIMAL(16, 2) COMMENT '最终价格分摊'
    from ods_order_detail_inc
    where dt = '2022-06-08'
    and type = 'bootstrap-insert'
) od
left join (
    select
        data.id,
        data.user_id,
        data.province_id,
        date_format(data.create_time, 'yyyy-MM-dd') date_id,
        data.create_time
    from ods_order_info_inc
    where dt = '2022-06-08'
    and type = 'bootstrap-insert'
) oi on od.order_id = oi.id
left join (
    select
        data.order_detail_id,
        data.activity_id,
        data.activity_rule_id
    from ods_order_detail_activity_inc
    where dt = '2022-06-08'
    and type = 'bootstrap-insert'
) act on od.id = act.order_detail_id
left join (
    select
        data.order_detail_id,
        data.coupon_id
    from ods_order_detail_coupon_inc
    where dt = '2022-06-08'
    and type = 'bootstrap-insert'
) cp on od.id = cp.order_detail_id;


-- 每日：当天数据
insert overwrite table dwd_trade_order_detail_inc partition (dt='2022-06-09')
select
    od.`id`                  ,--STRING COMMENT '编号',
    `order_id`               ,--STRING COMMENT '订单ID',
    `user_id`                ,--STRING COMMENT '用户ID',
    `sku_id`                 ,--STRING COMMENT '商品ID',
    `province_id`            ,--STRING COMMENT '省份ID',
    `activity_id`            ,--STRING COMMENT '参与活动ID',
    `activity_rule_id`       ,--STRING COMMENT '参与活动规则ID',
    `coupon_id`              ,--STRING COMMENT '使用优惠券ID',
    `date_id`                ,--STRING COMMENT '下单日期ID',
    `create_time`            ,--STRING COMMENT '下单时间',
    `sku_num`                ,--BIGINT COMMENT '商品数量',
    `split_original_amount`  ,--DECIMAL(16, 2) COMMENT '原始价格',
    `split_activity_amount`  ,--DECIMAL(16, 2) COMMENT '活动优惠分摊',
    `split_coupon_amount`    ,--DECIMAL(16, 2) COMMENT '优惠券优惠分摊',
    `split_total_amount`      --DECIMAL(16, 2) COMMENT '最终价格分摊'
from (
    select
        data.`id`                    ,--STRING COMMENT '编号',
        data.`order_id`              ,--STRING COMMENT '订单ID',
        --data.`user_id`               ,--STRING COMMENT '用户ID',
        data.`sku_id`                ,--STRING COMMENT '商品ID',
        --data.`province_id`           ,--STRING COMMENT '省份ID',
        --data.`activity_id`           ,--STRING COMMENT '参与活动ID',
        --data.`activity_rule_id`      ,--STRING COMMENT '参与活动规则ID',
        --data.`coupon_id`             ,--STRING COMMENT '使用优惠券ID',
        --data.`date_id`               ,--STRING COMMENT '下单日期ID',
        --data.`create_time`           ,--STRING COMMENT '下单时间',
        data.`sku_num`               ,--BIGINT COMMENT '商品数量',
        data.`sku_num` * data.`order_price` `split_original_amount` ,--DECIMAL(16, 2) COMMENT '原始价格',
        nvl(data.`split_activity_amount`, 0) split_activity_amount ,--DECIMAL(16, 2) COMMENT '活动优惠分摊',
        nvl(data.`split_coupon_amount`, 0) split_coupon_amount ,--DECIMAL(16, 2) COMMENT '优惠券优惠分摊',
        data.`split_total_amount`     --DECIMAL(16, 2) COMMENT '最终价格分摊'
    from ods_order_detail_inc
    where dt = '2022-06-09'
    and type = 'insert' -- 只有insert
) od
left join (
    select
        data.id,
        data.user_id,
        data.province_id,
        date_format(data.create_time, 'yyyy-MM-dd') date_id,
        data.create_time
    from ods_order_info_inc
    where dt = '2022-06-09'
    and type = 'insert' -- 此处假设 update操作不会修改我们想要的字段
) oi on od.order_id = oi.id
left join (
    select
        data.order_detail_id,
        data.activity_id,
        data.activity_rule_id
    from ods_order_detail_activity_inc
    where dt = '2022-06-09'
    and type = 'insert' -- 只有insert
) act on od.id = act.order_detail_id
left join (
    select
        data.order_detail_id,
        data.coupon_id
    from ods_order_detail_coupon_inc
    where dt = '2022-06-09'
    and type = 'insert'
) cp on od.id = cp.order_detail_id;

-- 交易域支付成功事务事实表：最细粒度
    -- 交易域
    -- 支付成功
        -- 用户 + 时间 + 订单 + 商品 + 支付金额
    -- 事务事实表
    -- 建表语句
    -- 维度表是否应该创建
        -- 原则上来讲，每一个维度都应该创建一张表
            -- 但是如果维度比较简单，数据少，应用场景少，那么无需创建维度表，可以直接将维度声明在事实表，一般称之为'维度退化'
DROP TABLE IF EXISTS dwd_trade_pay_detail_suc_inc;
CREATE EXTERNAL TABLE dwd_trade_pay_detail_suc_inc
(
    `id`                      STRING COMMENT '编号',
    `order_id`               STRING COMMENT '订单ID',
    `user_id`                STRING COMMENT '用户ID',
    `sku_id`                 STRING COMMENT 'SKU_ID',
    `province_id`           STRING COMMENT '省份ID',
    `activity_id`           STRING COMMENT '参与活动ID',
    `activity_rule_id`     STRING COMMENT '参与活动规则ID',
    `coupon_id`              STRING COMMENT '使用优惠券ID',
    `payment_type_code`     STRING COMMENT '支付类型编码',
    `payment_type_name`     STRING COMMENT '支付类型名称',
    `date_id`                STRING COMMENT '支付日期ID',
    `callback_time`         STRING COMMENT '支付成功时间',
    `sku_num`                 BIGINT COMMENT '商品数量',
    `split_original_amount` DECIMAL(16, 2) COMMENT '应支付原始金额',
    `split_activity_amount` DECIMAL(16, 2) COMMENT '支付活动优惠分摊',
    `split_coupon_amount`   DECIMAL(16, 2) COMMENT '支付优惠券优惠分摊',
    `split_payment_amount`  DECIMAL(16, 2) COMMENT '支付金额' -- 支付金额与下单金额不是一回事
) COMMENT '交易域支付成功事务事实表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dwd/dwd_trade_pay_detail_suc_inc/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

-- 首日数据装载
insert overwrite table dwd_trade_pay_detail_suc_inc partition (dt)
select
    od.`id`                    ,--   STRING COMMENT '编号',
    od.`order_id`              ,--  STRING COMMENT '订单ID',
    `user_id`               ,--  STRING COMMENT '用户ID',
    `sku_id`                ,--  STRING COMMENT 'SKU_ID',
    `province_id`           ,-- STRING COMMENT '省份ID',
    `activity_id`           ,-- STRING COMMENT '参与活动ID',
    `activity_rule_id`      ,--STRING COMMENT '参与活动规则ID',
    `coupon_id`             ,--  STRING COMMENT '使用优惠券ID',
    `payment_type_code`     ,-- STRING COMMENT '支付类型编码',
    `payment_type_name`     ,-- STRING COMMENT '支付类型名称',
    `date_id`               ,--  STRING COMMENT '支付日期ID',
    `callback_time`         ,-- STRING COMMENT '支付成功时间',
    `sku_num`               ,--   BIGINT COMMENT '商品数量',
    `split_original_amount` ,-- DECIMAL(16, 2) COMMENT '应支付原始金额',
    `split_activity_amount` ,-- DECIMAL(16, 2) COMMENT '支付活动优惠分摊',
    `split_coupon_amount`   ,-- DECIMAL(16, 2) COMMENT '支付优惠券优惠分摊',
    `split_payment_amount`  ,-- DECIMAL(16, 2) COMMENT '支付金额'
    date_id
from (
    select
        data.`id`                    ,--   STRING COMMENT '编号',
        data.`order_id`              ,--  STRING COMMENT '订单ID',
        --data.`user_id`               ,--  STRING COMMENT '用户ID',
        data.`sku_id`                ,--  STRING COMMENT 'SKU_ID',
        --data.`province_id`           ,-- STRING COMMENT '省份ID',
        --data.`activity_id`           ,-- STRING COMMENT '参与活动ID',
        --data.`activity_rule_id`      ,--STRING COMMENT '参与活动规则ID',
        --data.`coupon_id`             ,--  STRING COMMENT '使用优惠券ID',
        --data.`payment_type_code`     ,-- STRING COMMENT '支付类型编码',
        --data.`payment_type_name`     ,-- STRING COMMENT '支付类型名称',
        --data.`date_id`               ,--  STRING COMMENT '支付日期ID',
        --data.`callback_time`         ,-- STRING COMMENT '支付成功时间',
        data.`sku_num`               ,--   BIGINT COMMENT '商品数量',
        data.sku_num * data.order_price `split_original_amount` ,-- DECIMAL(16, 2) COMMENT '应支付原始金额',
        data.`split_activity_amount` ,-- DECIMAL(16, 2) COMMENT '支付活动优惠分摊',
        data.`split_coupon_amount`    -- DECIMAL(16, 2) COMMENT '支付优惠券优惠分摊',
        --data.`split_payment_amount`   -- DECIMAL(16, 2) COMMENT '支付金额'
    from ods_order_detail_inc
    where dt = '2022-06-08'
    and type = 'bootstrap-insert'
) od
join (  --  join !!! 有订单详情但不一定有支付信息，没有支付信息的不要
    select
        data.order_id,
        data.payment_type payment_type_code,
        date_format(data.callback_time, 'yyyy-MM-dd') date_id,
        data.callback_time,
        data.total_amount split_payment_amount
    from ods_payment_info_inc
    where dt = '2022-06-08'
    and type = 'bootstrap-insert'
    and data.payment_status = '1602'
) pay on od.order_id = pay.order_id
left join (
    select
        data.id,
        data.user_id,
        data.province_id
    from ods_order_info_inc
    where dt = '2022-06-08'
    and type = 'bootstrap-insert'
) oi on od.order_id = oi.id
left join (
    select
        data.order_detail_id,
        data.activity_id,
        data.activity_rule_id
    from ods_order_detail_activity_inc
    where dt = '2022-06-08'
    and type = 'bootstrap-insert'
) act on od.id = act.order_detail_id
left join (
    select
        data.order_detail_id,
        data.coupon_id
    from ods_order_detail_coupon_inc
    where dt = '2022-06-08'
    and type = 'bootstrap-insert'
) cp on od.id = cp.order_detail_id
left join (
    select
        dic_code,
        dic_name payment_type_name
    from ods_base_dic_full
    where dt = '2022-06-08'
    and parent_code = '11'
) dic on pay.payment_type_code = dic.dic_code;


-- 每日数据装载
insert overwrite table dwd_trade_pay_detail_suc_inc partition (dt='2022-06-09')
select
    od.`id`                    ,--   STRING COMMENT '编号',
    od.`order_id`              ,--  STRING COMMENT '订单ID',
    `user_id`               ,--  STRING COMMENT '用户ID',
    `sku_id`                ,--  STRING COMMENT 'SKU_ID',
    `province_id`           ,-- STRING COMMENT '省份ID',
    `activity_id`           ,-- STRING COMMENT '参与活动ID',
    `activity_rule_id`      ,--STRING COMMENT '参与活动规则ID',
    `coupon_id`             ,--  STRING COMMENT '使用优惠券ID',
    `payment_type_code`     ,-- STRING COMMENT '支付类型编码',
    `payment_type_name`     ,-- STRING COMMENT '支付类型名称',
    `date_id`               ,--  STRING COMMENT '支付日期ID',
    `callback_time`         ,-- STRING COMMENT '支付成功时间',
    `sku_num`               ,--   BIGINT COMMENT '商品数量',
    `split_original_amount` ,-- DECIMAL(16, 2) COMMENT '应支付原始金额',
    `split_activity_amount` ,-- DECIMAL(16, 2) COMMENT '支付活动优惠分摊',
    `split_coupon_amount`   ,-- DECIMAL(16, 2) COMMENT '支付优惠券优惠分摊',
    `split_payment_amount`   -- DECIMAL(16, 2) COMMENT '支付金额'
from (
    select
        data.`id`                    ,--   STRING COMMENT '编号',
        data.`order_id`              ,--  STRING COMMENT '订单ID',
        --data.`user_id`               ,--  STRING COMMENT '用户ID',
        data.`sku_id`                ,--  STRING COMMENT 'SKU_ID',
        --data.`province_id`           ,-- STRING COMMENT '省份ID',
        --data.`activity_id`           ,-- STRING COMMENT '参与活动ID',
        --data.`activity_rule_id`      ,--STRING COMMENT '参与活动规则ID',
        --data.`coupon_id`             ,--  STRING COMMENT '使用优惠券ID',
        --data.`payment_type_code`     ,-- STRING COMMENT '支付类型编码',
        --data.`payment_type_name`     ,-- STRING COMMENT '支付类型名称',
        --data.`date_id`               ,--  STRING COMMENT '支付日期ID',
        --data.`callback_time`         ,-- STRING COMMENT '支付成功时间',
        data.`sku_num`               ,--   BIGINT COMMENT '商品数量',
        data.sku_num * data.order_price `split_original_amount` ,-- DECIMAL(16, 2) COMMENT '应支付原始金额',
        data.`split_activity_amount` ,-- DECIMAL(16, 2) COMMENT '支付活动优惠分摊',
        data.`split_coupon_amount`    -- DECIMAL(16, 2) COMMENT '支付优惠券优惠分摊',
        --data.`split_payment_amount`   -- DECIMAL(16, 2) COMMENT '支付金额'
    from ods_order_detail_inc
    where (dt = '2022-06-09' or dt = date_sub('2022-06-09', 1))
    and (type = 'insert' or type = 'bootstrap-insert')
) od
join (  --  join !!! 有订单详情但不一定有支付信息，没有支付信息的不要
    select
        data.order_id,
        data.payment_type payment_type_code,
        date_format(data.callback_time, 'yyyy-MM-dd') date_id,
        data.callback_time,
        data.total_amount split_payment_amount
    from ods_payment_info_inc
    where dt = '2022-06-09'
    and type = 'update'
    and array_contains(map_keys(old), 'payment_status')
    and data.payment_status = '1602'
) pay on od.order_id = pay.order_id
left join (
    select
        data.id,
        data.user_id,
        data.province_id
    from ods_order_info_inc
    where (dt = '2022-06-09' or dt = date_sub('2022-06-09', 1))
    and (type = 'insert' or type = 'bootstrap-insert')
) oi on od.order_id = oi.id
left join (
    select
        data.order_detail_id,
        data.activity_id,
        data.activity_rule_id
    from ods_order_detail_activity_inc
    where (dt = '2022-06-09' or dt = date_sub('2022-06-09', 1))
    and (type = 'insert' or type = 'bootstrap-insert')
) act on od.id = act.order_detail_id
left join (
    select
        data.order_detail_id,
        data.coupon_id
    from ods_order_detail_coupon_inc
    where (dt = '2022-06-09' or dt = date_sub('2022-06-09', 1))
    and (type = 'insert' or type = 'bootstrap-insert')
) cp on od.id = cp.order_detail_id
left join (
    select
        dic_code,
        dic_name payment_type_name
    from ods_base_dic_full
    where dt = '2022-06-09'
    and parent_code = '11'
) dic on pay.payment_type_code = dic.dic_code;

-- 交易域购物车周期快照事实表
    -- 交易域
    -- 购物车
    -- 周期快照事实表
        -- 特殊的需求：各品类商品购物车存量(Top3)
            -- zhangsan： 鞋  生活用品  5
            -- zhangsan：手机 电子产品  1
            -- lisi：    衣服 生活用品  1
        ---------------------------------
            -- 生活用品[6]
            -- 电子产品[1]
        -- 实现思路：行为数据（事实表） + 状态数据（维度表）
            -- dwd_trade_cart_add_inc(加购) + dim_sku_full
            -- dwd_trade_cart_sub_inc(减购) + dim_sku_full
                -- 1001 [1, 2, 5, 6]
                -- 1001 [3, 2, 1]
                -- [join] 1001 add(14) sub(6) => [1001 storage(8)]
        -- 特殊的需求不需要特殊计算，因为效率太低，那么可以直接从业务数据中获取特殊字段，比如存量字段（库存、余额……）
            -- 特殊字段不需要进行多张表的关联计算，直接从业务数据库中周期性地获取即可
    -- 建表语句
DROP TABLE IF EXISTS dwd_trade_cart_full;
CREATE EXTERNAL TABLE dwd_trade_cart_full
(
    `id`         STRING COMMENT '编号',
    `user_id`   STRING COMMENT '用户ID',
    `sku_id`    STRING COMMENT 'SKU_ID',
    `sku_name`  STRING COMMENT '商品名称',
    `sku_num`   BIGINT COMMENT '现存商品件数'
) COMMENT '交易域购物车周期快照事实表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dwd/dwd_trade_cart_full/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

-- 度量值的类型
    -- 可加事实：度量值可加
    -- 半可加事实：度量值在某些场景中可加，在某些场景不可加
    -- 不可加事实：比率型

-- 数据装载(full - tsv)
insert overwrite table dwd_trade_cart_full partition (dt = '2022-06-08')
select
    `id`        ,--STRING COMMENT '编号',
    `user_id`   ,--STRING COMMENT '用户ID',
    `sku_id`    ,--STRING COMMENT 'SKU_ID',
    `sku_name`  ,--STRING COMMENT '商品名称',
    `sku_num`    --BIGINT COMMENT '现存商品件数'
from ods_cart_info_full
where dt = '2022-06-08'
and is_ordered = '0';

-- 交易域交易流程累积快照事实表
    -- 交易域
    -- 交易流程
    -- 累积快照事实表
        -- 特殊的需求：下单到支付时间间隔平均值
        -- 将一个流程中的多个行为的状态数据累积到一张表中
    -- 建表语句
        -- 行为和行为之间的关系，粒度不是越细越好
DROP TABLE IF EXISTS dwd_trade_trade_flow_acc;
CREATE EXTERNAL TABLE dwd_trade_trade_flow_acc
(
    `order_id`               STRING COMMENT '订单ID',
    `user_id`                STRING COMMENT '用户ID',
    `province_id`           STRING COMMENT '省份ID',
    `order_date_id`         STRING COMMENT '下单日期ID',
    `order_time`             STRING COMMENT '下单时间',
    `payment_date_id`        STRING COMMENT '支付日期ID',
    `payment_time`           STRING COMMENT '支付时间',
    `finish_date_id`         STRING COMMENT '确认收货日期ID',
    `finish_time`             STRING COMMENT '确认收货时间',
    `order_original_amount` DECIMAL(16, 2) COMMENT '下单原始价格',
    `order_activity_amount` DECIMAL(16, 2) COMMENT '下单活动优惠分摊',
    `order_coupon_amount`   DECIMAL(16, 2) COMMENT '下单优惠券优惠分摊',
    `order_total_amount`    DECIMAL(16, 2) COMMENT '下单最终价格分摊',
    `payment_amount`         DECIMAL(16, 2) COMMENT '支付金额'
) COMMENT '交易域交易流程累积快照事实表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dwd/dwd_trade_trade_flow_acc/'
TBLPROPERTIES ('orc.compress' = 'snappy');

-- 加载数据
-- 首日
    -- 保存数据时，分区策略
        -- 哪天的数据存放到哪天的分区
            -- 使用下单日期 order_date_id 作为分区
    -- 如果表中存在多个和业务相关的时间字段，一般会选择其中的一个业务时间作为分区字段
        -- 一般选择时间靠后的字段作为分区字段
            -- DIM_USER_ZIP
        -- 存在一个问题，最后一个时间字段可能没值，一般不会采用null来表示分区，会采用时间极大值 9999-12-31
insert overwrite table dwd_trade_trade_flow_acc partition (dt)
select
    oi.id `order_id`        ,--  STRING COMMENT '订单ID',
    `user_id`               ,--  STRING COMMENT '用户ID',
    `province_id`           ,-- STRING COMMENT '省份ID',
    `order_date_id`         ,-- STRING COMMENT '下单日期ID',
    `order_time`            ,--  STRING COMMENT '下单时间',
    `payment_date_id`       ,--  STRING COMMENT '支付日期ID',
    `payment_time`          ,--  STRING COMMENT '支付时间',
    `finish_date_id`        ,--  STRING COMMENT '确认收货日期ID',
    `finish_time`           ,--   STRING COMMENT '确认收货时间',
    `order_original_amount` ,-- DECIMAL(16, 2) COMMENT '下单原始价格',
    `order_activity_amount` ,-- DECIMAL(16, 2) COMMENT '下单活动优惠分摊',
    `order_coupon_amount`   ,-- DECIMAL(16, 2) COMMENT '下单优惠券优惠分摊',
    `order_total_amount`    ,-- DECIMAL(16, 2) COMMENT '下单最终价格分摊',
    `payment_amount`        ,--  DECIMAL(16, 2) COMMENT '支付金额'
    nvl(finish_date_id, '9999-12-31')
from (
    select
        data.id,
        data.user_id,
        data.province_id,
        date_format(data.create_time, 'yyyy-MM-dd') order_date_id,
        data.create_time order_time,
        data.original_total_amount order_original_amount,
        data.activity_reduce_amount order_activity_amount,
        data.coupon_reduce_amount order_coupon_amount,
        data.total_amount order_total_amount
    from ods_order_info_inc
    where dt = '2022-06-08'
    and type = 'bootstrap-insert'
) oi
left join (
    select
        data.order_id,
        date_format(data.callback_time, 'yyyy-MM-dd') payment_date_id,
        data.callback_time payment_time,
        data.total_amount payment_amount
    from ods_payment_info_inc
    where dt = '2022-06-08'
    and type = 'bootstrap-insert'
    and data.payment_status = '1602'
) pay on oi.id = pay.order_id
left join (
    -- 订单状态流水表
    select
        data.order_id,
        date_format(data.create_time, 'yyyy-MM-dd') finish_date_id,
        data.create_time finish_time
    from ods_order_status_log_inc
    where dt = '2022-06-08'
    and type = 'bootstrap-insert'
    and data.order_status = '1004'
) log on oi.id = log.order_id;

-- 分区策略
    -- 查询的数据如何存储到表的分区中
        -- 查询数据的效率，数据有效

    -- ODS层
        -- 一天采集到的数据就存储到表的一天的分区

    -- DIM层
        -- 全量：每天一份全量数据
        -- 拉链：会采用结束时间作为分区字段（null -> 9999-12-31）

    -- DWD层
        -- 事务型事实表：一天的业务行为数据存到一天的分区中
        -- 周期型快照事实表：将每一天的全部状态数据保存到这一天的分区中（行为 -> 状态）
        -- 累计型快照事实表：将业务流程中最后的时间字段作为分区字段

-- 每日
    -- [insert order] * [update payment] * [update finish]
        -- union all
    -- [old order] * [update payment] * [update finish]
    -- (insert order union all old order) * payment * finish

insert overwrite table dwd_trade_trade_flow_acc partition (dt)
select
    oi.`order_id`              ,-- STRING COMMENT '订单ID',
    `user_id`               ,-- STRING COMMENT '用户ID',
    `province_id`           ,--STRING COMMENT '省份ID',
    `order_date_id`         ,--STRING COMMENT '下单日期ID',
    `order_time`            ,-- STRING COMMENT '下单时间',
    if(pay.`payment_time` is not null, pay.`payment_date_id`, oi.`payment_date_id`)      ,--  STRING COMMENT '支付日期ID',
    if(pay.`payment_time` is not null, pay.`payment_time`, oi.`payment_time`)         ,--  STRING COMMENT '支付时间',
    if(log.finish_time is not null, log.finish_date_id, null)        ,-- STRING COMMENT '确认收货日期ID',
    if(log.finish_time is not null, log.finish_time, null)           ,--  STRING COMMENT '确认收货时间',
    `order_original_amount` ,--DECIMAL(16, 2) COMMENT '下单原始价格',
    `order_activity_amount` ,--DECIMAL(16, 2) COMMENT '下单活动优惠分摊',
    `order_coupon_amount`   ,--DECIMAL(16, 2) COMMENT '下单优惠券优惠分摊',
    `order_total_amount`    ,--DECIMAL(16, 2) COMMENT '下单最终价格分摊',
    if(pay.`payment_time` is not null, pay.`payment_amount`, oi.payment_amount)         ,-- DECIMAL(16, 2) COMMENT '支付金额'
    if(log.finish_time is not null, log.finish_date_id, '9999-12-31')
from (
    select
        `order_id`             ,--  STRING COMMENT '订单ID',
        `user_id`              ,--  STRING COMMENT '用户ID',
        `province_id`          ,-- STRING COMMENT '省份ID',
        `order_date_id`        ,-- STRING COMMENT '下单日期ID',
        `order_time`           ,--  STRING COMMENT '下单时间',
        `payment_date_id`      ,-- STRING COMMENT '支付日期ID',
        `payment_time`         ,-- STRING COMMENT '支付时间',
        `finish_date_id`       ,--  STRING COMMENT '确认收货日期ID',
        `finish_time`          ,--   STRING COMMENT '确认收货时间',
        `order_original_amount`,-- DECIMAL(16, 2) COMMENT '下单原始价格',
        `order_activity_amount`,-- DECIMAL(16, 2) COMMENT '下单活动优惠分摊',
        `order_coupon_amount`  ,-- DECIMAL(16, 2) COMMENT '下单优惠券优惠分摊',
        `order_total_amount`   ,-- DECIMAL(16, 2) COMMENT '下单最终价格分摊',
        `payment_amount`        --  DECIMAL(16, 2) COMMENT '支付金额'
    from dwd_trade_trade_flow_acc
    where dt = '9999-12-31'
    union all
    select
        data.id,
        data.user_id,
        data.province_id,
        date_format(data.create_time, 'yyyy-MM-dd') order_date_id,
        data.create_time order_time,
        null,
        null,
        null,
        null,
        data.original_total_amount order_original_amount,
        data.activity_reduce_amount order_activity_amount,
        data.coupon_reduce_amount order_coupon_amount,
        data.total_amount order_total_amount,
        null
    from ods_order_info_inc
    where dt = '2022-06-09'
    and type = 'insert'
) oi
left join (
    select
        data.order_id,
        date_format(data.callback_time, 'yyyy-MM-dd') payment_date_id,
        data.callback_time payment_time,
        data.total_amount payment_amount
    from ods_payment_info_inc
    where dt = '2022-06-09'
    and type = 'update'
    and array_contains(map_keys(old), 'payment')
) pay on oi.order_id = pay.order_id
left join (
    select
        data.order_id,
        date_format(data.create_time, 'yyyy-MM-dd') finish_date_id,
        data.create_time finish_time
    from ods_order_status_log_inc
    where dt = '2022-06-09'
    and type = 'insert'
    and data.order_status = '1004'
) log;


-- 工具域优惠券使用(支付)事务事实表
    -- 工具域
    -- 优惠券使用(支付)
        -- 用户 + 时间 + 订单 + 优惠券 + （次数）
    -- 事务事实表
DROP TABLE IF EXISTS dwd_tool_coupon_used_inc;
CREATE EXTERNAL TABLE dwd_tool_coupon_used_inc
(
    `id`           STRING COMMENT '编号',
    `coupon_id`    STRING COMMENT '优惠券ID',
    `user_id`      STRING COMMENT '用户ID',
    `order_id`     STRING COMMENT '订单ID',
    `date_id`      STRING COMMENT '日期ID',
    `payment_time` STRING COMMENT '使用(支付)时间'
) COMMENT '优惠券使用（支付）事务事实表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dwd/dwd_tool_coupon_used_inc/'
    TBLPROPERTIES ("orc.compress" = "snappy");

-- 数据装载
-- 首日
insert overwrite table dwd_tool_coupon_used_inc partition (dt)
select
    data.`id`           ,--STRING COMMENT '编号',
    data.`coupon_id`    ,--STRING COMMENT '优惠券ID',
    data.`user_id`      ,--STRING COMMENT '用户ID',
    data.`order_id`     ,--STRING COMMENT '订单ID',
    date_format(data.used_time, 'yyyy-MM-dd') `date_id`      ,--STRING COMMENT '日期ID',
    data.used_time `payment_time` ,--STRING COMMENT '使用(支付)时间'
    date_format(data.used_time, 'yyyy-MM-dd')
from ods_coupon_use_inc
where dt = '2022-06-08'
and type = 'bootstrap-insert'
and data.used_time is not null;

-- 每日
insert overwrite table dwd_tool_coupon_used_inc partition (dt = '2022-06-09')
select
    data.`id`           ,--STRING COMMENT '编号',
    data.`coupon_id`    ,--STRING COMMENT '优惠券ID',
    data.`user_id`      ,--STRING COMMENT '用户ID',
    data.`order_id`     ,--STRING COMMENT '订单ID',
    date_format(data.used_time, 'yyyy-MM-dd') `date_id`      ,--STRING COMMENT '日期ID',
    data.used_time `payment_time` --STRING COMMENT '使用(支付)时间'
from ods_coupon_use_inc
where dt = '2022-06-09'
and type = 'update'
-- and array_contains(map_keys(old), 'used_time') -- 同下
and data.used_time is not null;

-- 互动域收藏商品事务事实表
    -- 互动域
    -- 收藏商品
        -- 用户 + 时间 + 商品
    -- 事务事实表
    -- 建表语句
DROP TABLE IF EXISTS dwd_interaction_favor_add_inc;
CREATE EXTERNAL TABLE dwd_interaction_favor_add_inc
(
    `id`          STRING COMMENT '编号',
    `user_id`     STRING COMMENT '用户ID',
    `sku_id`      STRING COMMENT 'SKU_ID',
    `date_id`     STRING COMMENT '日期ID',
    `create_time` STRING COMMENT '收藏时间'
) COMMENT '互动域收藏商品事务事实表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dwd/dwd_interaction_favor_add_inc/'
    TBLPROPERTIES ("orc.compress" = "snappy");

-- 数据装载
insert overwrite table dwd_interaction_favor_add_inc partition (dt)
select
    data.id,
    data.user_id,
    data.sku_id,
    date_format(data.create_time, 'yyyy-MM-dd') date_id,
    data.create_time,
    date_format(data.create_time, 'yyyy-MM-dd')
from ods_favor_info_inc
where dt = '2022-06-08'
and type = 'bootstrap-insert';
--and data.is_cancel = '0' -- 曾经收藏过也是收藏行为
-- id user_id ... is_cancel
-- 1   1001           0
-- 2   1002           1    -- 这条数据曾经被收藏过

insert overwrite table dwd_interaction_favor_add_inc partition (dt = '2022-06-09')
select
    data.id,
    data.user_id,
    data.sku_id,
    date_format(data.create_time, 'yyyy-MM-dd') date_id,
    data.create_time
from ods_favor_info_inc
where dt = '2022-06-09'
and type = 'insert';

-- 流量域页面浏览事务事实表
    -- 流量域
    -- 页面浏览
        -- 用户 + 时间 + 上一个页面 + 当前页面 + 停留时间
        -- 日志
    -- 事务事实表
    -- 建表语句
DROP TABLE IF EXISTS dwd_traffic_page_view_inc;
CREATE EXTERNAL TABLE dwd_traffic_page_view_inc
(
    `province_id`    STRING COMMENT '省份ID',
    `brand`           STRING COMMENT '手机品牌',
    `channel`         STRING COMMENT '渠道',
    `is_new`          STRING COMMENT '是否首次启动',
    `model`           STRING COMMENT '手机型号',
    `mid_id`          STRING COMMENT '设备ID', -- 用户
    `operate_system` STRING COMMENT '操作系统',
    `user_id`         STRING COMMENT '会员ID',  -- 登录后的ID
    `version_code`   STRING COMMENT 'APP版本号',
    `page_item`       STRING COMMENT '目标ID',
    `page_item_type` STRING COMMENT '目标类型',
    `last_page_id`    STRING COMMENT '上页ID',
    `page_id`          STRING COMMENT '页面ID ',
    `from_pos_id`     STRING COMMENT '点击坑位ID',
    `from_pos_seq`    STRING COMMENT '点击坑位位置',
    `refer_id`         STRING COMMENT '营销渠道ID',
    `date_id`          STRING COMMENT '日期ID',
    `view_time`       STRING COMMENT '跳入时间',
    `session_id`      STRING COMMENT '所属会话ID',
    `during_time`     BIGINT COMMENT '持续时间毫秒'
) COMMENT '流量域页面浏览事务事实表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dwd/dwd_traffic_page_view_inc'
    TBLPROPERTIES ('orc.compress' = 'snappy');

-- 数据装载
    -- 日志表的数据分为两大类
        -- 页面浏览日志
        -- APP启动日志
    -- Hive Bug
        -- 升级（扩展）
set hive.cbo.enable=false;
insert overwrite table dwd_traffic_page_view_inc partition (dt='2022-06-08')
select
    common.ar `province_id`     ,--STRING COMMENT '省份ID',
    common.ba `brand`           ,--STRING COMMENT '手机品牌',
    common.ch `channel`         ,--STRING COMMENT '渠道',
    common.`is_new`          ,--STRING COMMENT '是否首次启动',
    common.md `model`           ,--STRING COMMENT '手机型号',
    common.mid `mid_id`          ,--STRING COMMENT '设备ID', -- 用户
    common.os `operate_system`  ,--STRING COMMENT '操作系统',
    common.uid `user_id`         ,--STRING COMMENT '会员ID',  -- 登录后的ID
    common.vc `version_code`     ,--STRING COMMENT 'APP版本号',
    page.item `page_item`       ,--STRING COMMENT '目标ID',
    page.item_type `page_item_type`  ,--STRING COMMENT '目标类型',
    page.`last_page_id`    ,--STRING COMMENT '上页ID',
    page.`page_id`         ,--STRING COMMENT '页面ID ',
    page.`from_pos_id`     ,--STRING COMMENT '点击坑位ID',
    page.`from_pos_seq`    ,--STRING COMMENT '点击坑位位置',
    page.`refer_id`        ,--STRING COMMENT '营销渠道ID',
    date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd') `date_id`          ,--STRING COMMENT '日期ID',
    date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd HH:mm:ss') `view_time`        ,--STRING COMMENT '跳入时间',
    common.sid `session_id`       ,--STRING COMMENT '所属会话ID',
    page.`during_time`       --BIGINT COMMENT '持续时间毫秒'
from ods_log_inc
where dt = '2022-06-08'
and page is not null;
set hive.cbo.enable=true;

-- 用户域用户注册事务事实表
    -- 用户域
    -- 用户注册成功
        -- 用户（会员）+ 时间
        -- user_info(insert)
    -- 事务事实表
    -- 建表语句
DROP TABLE IF EXISTS dwd_user_register_inc;
CREATE EXTERNAL TABLE dwd_user_register_inc
(
    `user_id`          STRING COMMENT '用户ID',
    `date_id`          STRING COMMENT '日期ID',
    `create_time`     STRING COMMENT '注册时间',
    `channel`          STRING COMMENT '应用下载渠道',
    `province_id`     STRING COMMENT '省份ID',
    `version_code`    STRING COMMENT '应用版本',
    `mid_id`           STRING COMMENT '设备ID',
    `brand`            STRING COMMENT '设备品牌',
    `model`            STRING COMMENT '设备型号',
    `operate_system` STRING COMMENT '设备操作系统'
) COMMENT '用户域用户注册事务事实表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dwd/dwd_user_register_inc/'
    TBLPROPERTIES ("orc.compress" = "snappy");

-- 数据装载
    -- 页面浏览日志：离开页面的时候
-- insert overwrite table dwd_user_register_inc partition (dt='2022-06-08')
-- select
--     common.uid `user_id`         ,-- STRING COMMENT '用户ID',
--     date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd') `date_id`         ,-- STRING COMMENT '日期ID',
--     date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd HH:mm:ss') `create_time`     ,--STRING COMMENT '注册时间',
--     common.ch `channel`         ,-- STRING COMMENT '应用下载渠道',
--     common.ar `province_id`     ,--STRING COMMENT '省份ID',
--     common.vc `version_code`    ,--STRING COMMENT '应用版本',
--     common.mid `mid_id`          ,-- STRING COMMENT '设备ID',
--     common.ba `brand`           ,-- STRING COMMENT '设备品牌',
--     common.md `model`           ,-- STRING COMMENT '设备型号',
--     common.os `operate_system`   --STRING COMMENT '设备操作系统'
-- from ods_log_inc
-- where dt = '2022-06-08'
-- and page.page_id = 'register'
-- and common.uid is not null;

-- 首日
insert overwrite table dwd_user_register_inc partition (dt)
select
    `user_id`        ,--  STRING COMMENT '用户ID',
    `date_id`        ,--  STRING COMMENT '日期ID',
    `create_time`    ,-- STRING COMMENT '注册时间',
    `channel`        ,--  STRING COMMENT '应用下载渠道',
    `province_id`    ,-- STRING COMMENT '省份ID',
    `version_code`   ,-- STRING COMMENT '应用版本',
    `mid_id`         ,--  STRING COMMENT '设备ID',
    `brand`          ,--  STRING COMMENT '设备品牌',
    `model`          ,--  STRING COMMENT '设备型号',
    `operate_system` ,--STRING COMMENT '设备操作系统'
    date_id
from (
    select
        data.id,
        date_format(data.create_time, 'yyyy-MM-dd') date_id,
        data.create_time
    from ods_user_info_inc
    where dt = '2022-06-08'
    and type = 'bootstrap-insert'
) ui
left join (
    select
        common.uid `user_id`         ,-- STRING COMMENT '用户ID',
        common.ch `channel`         ,-- STRING COMMENT '应用下载渠道',
        common.ar `province_id`     ,--STRING COMMENT '省份ID',
        common.vc `version_code`    ,--STRING COMMENT '应用版本',
        common.mid `mid_id`          ,-- STRING COMMENT '设备ID',
        common.ba `brand`           ,-- STRING COMMENT '设备品牌',
        common.md `model`           ,-- STRING COMMENT '设备型号',
        common.os `operate_system`   --STRING COMMENT '设备操作系统'
    from ods_log_inc
    where dt = '2022-06-08'
    and page.page_id = 'register'
    and common.uid is not null
) log on ui.id = log.user_id;

-- 每日
insert overwrite table dwd_user_register_inc partition (dt = '2022-06-09')
select
    `user_id`        ,--  STRING COMMENT '用户ID',
    `date_id`        ,--  STRING COMMENT '日期ID',
    `create_time`    ,-- STRING COMMENT '注册时间',
    `channel`        ,--  STRING COMMENT '应用下载渠道',
    `province_id`    ,-- STRING COMMENT '省份ID',
    `version_code`   ,-- STRING COMMENT '应用版本',
    `mid_id`         ,--  STRING COMMENT '设备ID',
    `brand`          ,--  STRING COMMENT '设备品牌',
    `model`          ,--  STRING COMMENT '设备型号',
    `operate_system`  --STRING COMMENT '设备操作系统'
from (
    select
        data.id,
        date_format(data.create_time, 'yyyy-MM-dd') date_id,
        data.create_time
    from ods_user_info_inc
    where dt = '2022-06-09'
    and type = 'insert'
) ui
left join (  -- 日志有丢失
    select
        common.uid `user_id`         ,-- STRING COMMENT '用户ID',
        common.ch `channel`         ,-- STRING COMMENT '应用下载渠道',
        common.ar `province_id`     ,--STRING COMMENT '省份ID',
        common.vc `version_code`    ,--STRING COMMENT '应用版本',
        common.mid `mid_id`          ,-- STRING COMMENT '设备ID',
        common.ba `brand`           ,-- STRING COMMENT '设备品牌',
        common.md `model`           ,-- STRING COMMENT '设备型号',
        common.os `operate_system`   --STRING COMMENT '设备操作系统'
    from ods_log_inc
    where dt = '2022-06-09'
    and page.page_id = 'register'
    and common.uid is not null
) log on ui.id = log.user_id;

-- 用户域用户登录事务事实表
    -- 用户域
    -- 用户登录成功
        -- 用户 + 时间 + （次数）
    -- 事务事实表
    -- 建表语句
DROP TABLE IF EXISTS dwd_user_login_inc;
CREATE EXTERNAL TABLE dwd_user_login_inc
(
    `user_id`         STRING COMMENT '用户ID',
    `date_id`         STRING COMMENT '日期ID',
    `login_time`     STRING COMMENT '登录时间',
    `channel`         STRING COMMENT '应用下载渠道',
    `province_id`    STRING COMMENT '省份ID',
    `version_code`   STRING COMMENT '应用版本',
    `mid_id`          STRING COMMENT '设备ID',
    `brand`           STRING COMMENT '设备品牌',
    `model`           STRING COMMENT '设备型号',
    `operate_system` STRING COMMENT '设备操作系统'
) COMMENT '用户域用户登录事务事实表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dwd/dwd_user_login_inc/'
    TBLPROPERTIES ("orc.compress" = "snappy");

-- 数据装载
-- 会话内第一个uid不为null的页面就对应一次登录操作
insert overwrite table dwd_user_login_inc partition (dt = '2022-06-08')
select
    `user_id`         ,--STRING COMMENT '用户ID',
    date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd') `date_id`         ,--STRING COMMENT '日期ID',
    date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd HH:mm:ss') `login_time`      ,--STRING COMMENT '登录时间',
    `channel`         ,--STRING COMMENT '应用下载渠道',
    `province_id`     ,--STRING COMMENT '省份ID',
    `version_code`    ,--STRING COMMENT '应用版本',
    `mid_id`          ,--STRING COMMENT '设备ID',
    `brand`           ,--STRING COMMENT '设备品牌',
    `model`           ,--STRING COMMENT '设备型号',
    `operate_system`   --STRING COMMENT '设备操作系统'
from (
    select
        ts,
        common.uid `user_id`         ,-- STRING COMMENT '用户ID',
        common.ch `channel`         ,-- STRING COMMENT '应用下载渠道',
        common.ar `province_id`     ,--STRING COMMENT '省份ID',
        common.vc `version_code`    ,--STRING COMMENT '应用版本',
        common.mid `mid_id`          ,-- STRING COMMENT '设备ID',
        common.ba `brand`           ,-- STRING COMMENT '设备品牌',
        common.md `model`           ,-- STRING COMMENT '设备型号',
        common.os `operate_system`  ,--STRING COMMENT '设备操作系统'
        row_number() over (partition by common.sid order by ts) rn  -- 开窗
    from ods_log_inc
    where dt = '2022-06-08'
    and page is not null
    and common.uid is not null
) t where rn = 1;

  • DWS层

  • ADS层

五、知识get

  • 维度分析

    data_dim_analysis

  • Hive中的数据转换

    hive_SerDe

  • Hive BUG

    • cbo优化会导致hive struct 结构体判空操作失效，执行计划缺少对应的 filter
    • 解决思路：set hive.cbo.enable=false;
    • 参考issue：https://issues.apache.org/jira/browse/HIVE-21778
© 2020 - 2025 © 2023-2025 洋洋 | 原创不易，转载请注明出处~
✨ 本站由 Hugo + Stack 主题搭建 | 不忘初心，慢慢成长 ✨
使用 Hugo 构建
主题 Stack 由 Jimmy 设计