CDH数仓项目(二) —— 用户行为数仓和业务数仓搭建

た入场券 2024-03-27 09:28 80阅读 0赞

## 0 说明 ##

本文基于[《CDH数仓项目(一) —— CDH安装部署搭建详细流程》][CDH_ _ CDH]开始搭建数仓

## 1 数仓搭建环境准备 ##

### 1.1 Flume安装部署 ###

1）添加服务  
![在这里插入图片描述][9308eb16639745939cb9513e55abcb73.png]  
2) 选择Flume  
3）选择依赖  
![在这里插入图片描述][b61fa0bd5e734b7e9908ab3249979647.png]  
4)选择部署节点  
![在这里插入图片描述][3ce4029fde804c65901f0b96a3344d7e.png]  
5) 安装完成

### 1.2 安装Sqoop ###

1）添加服务  
2）选择Sqoop  
3）选择部署节点  
![在这里插入图片描述][df46843758494ad0bbaded81e8c397da.png]  
4）完成安装部署  
![在这里插入图片描述][b1bf3b4ca58d45279ad767bd25c7cb84.png]

### 1.3 配置Hadoop支持LZO ###

1）点击主机，在下拉菜单中点击Parcel  
![在这里插入图片描述][5eea36a9a70d465ca854084aeb5d556c.png]  
2）点击配置

3）任意选一个加上parcel库的url  
本地url: http://chen102:8900/cloudera-repos/gplextras6/6.2.1/parcels/  
远程url: https://archive.cloudera.com/gplextras6/6.2.1/parcels/  
![在这里插入图片描述][fa4b0e3e07b04d03b89ab00d3510e18c.png]  
4）静待片刻，Parcel列表中出现了GPLEXTERAS，依次点击下载、分配、激活。  
![在这里插入图片描述][17d1ff7f74f745ee85ad9d01e498c45c.png]  
5）修改HDFS配置  
在HDFS配置项中搜索“压缩编码解码器”，加入com.hadoop.compression.lzo.LzopCodec  
![在这里插入图片描述][d8e2718941f74eccb137bb386cebe683.png]  
6）修改Hive配置  
在Hive配置项中搜索“Hive 辅助 JAR 目录”，加入/opt/cloudera/parcels/GPLEXTRAS/lib/hadoop/lib  
![在这里插入图片描述][303a797a1ce44f7f8f384096dbeb7633.png]

7）修改Sqoop配置  
在Sqoop的配置项中搜索“sqoop-conf/sqoop-env.sh 的 Sqoop 1 Client 客户端高级配置代码段（安全阀）”，加入以下字段

HADOOP_CLASSPATH=$HADOOP_CLASSPATH:/opt/cloudera/parcels/GPLEXTRAS/lib/hadoop/lib/*
    JAVA_LIBRARY_PATH=$JAVA_LIBRARY_PATH:/opt/cloudera/parcels/GPLEXTRAS/lib/hadoop/lib/native

![在这里插入图片描述][95ee0d2b498549819471aeba7d1eef79.png]

### 1.4 修改yarn配置 ###

1）在yarn配置项中搜索“yarn.nodemanager.resource.memory-mb”，修改成4G。  
![在这里插入图片描述][c5fe893af24c411798f41b569125c49d.png]

2）在yarn配置项中搜索“yarn.scheduler.maximum-allocation-mb”，修改成2G。

3）重启相关组件  
![在这里插入图片描述][9789e5f79a4a47b28d7867e4ac8bd604.png]

## 2 HUE使用概述 ##

### 2.1 来源 ###

HUE=Hadoop User Experience（Hadoop用户体验），直白来说就一个开源的Apache Hadoop UI系统，由Cloudera Desktop演化而来，最后Cloudera公司将其贡献给Apache基金会的Hadoop社区，它是基于Python Web框架Django实现的。通过使用HUE我们可以在浏览器端的Web控制台上与Hadoop集群进行交互来分析处理数据。

### 2.2 Hue用户管理 ###

HUE的初始管理用户为admin，密码为admin。  
1）在HUE中新建一个用户组——hive，并在该组下新建一个用户——hive。  
（1）创建hive组  
![在这里插入图片描述][1062f59c34cf49ca801be48f399b565b.png]  
![在这里插入图片描述][80211522b7e348d28bb216c31fdcf619.png]

（2）创建hive用户  
![在这里插入图片描述][60c99342813347e2a46b13fa2b35b4e0.png]  
![在这里插入图片描述][a53ad68c931e4e5497b03f932c9dc096.png]

2）切换为hive用户

## 3 用户行为数仓搭建 ##

### 3.1 日志采集Flume ###

### 3.1.1 用户行为日志生成 ###

1）将log-collector-1.0-SNAPSHOT-jar-with-dependencies.jar上传到chen102  
该jar包百度云连接如下：

> 链接：https://pan.baidu.com/s/1aoFH-Uu8OhG1siRqHQQZSQ  
> 提取码：zx1q

2）分发log-collector-1.0-SNAPSHOT-jar-with-dependencies.jar到chen103

### 3.1.2 日志采集Flume配置 ###

1）集群规划

<table> 
 <thead> 
  <tr> 
   <th>服务器chen102</th> 
   <th>服务器chen103</th> 
   <th>服务器chen104</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Flume（日志采集）</td> 
   <td>Flume</td> 
   <td>Flume</td> 
  </tr> 
 </tbody> 
</table>

2）Flume配置分析  
![在这里插入图片描述][ca0aa8b1739e4e1c9a13dcb0ee864a8f.png]  
Flume直接读log日志的数据，log日志的格式是app-yyyy-mm-dd.log。  
3）Flume的具体配置如下：  
（1）在CM管理页面上点击Flume  
（2）点击实例  
（3）点击chen102的Agent选项  
![在这里插入图片描述][0af71f1603fa43c287782c33ee611b5f.png]  
（4）点击配置  
（5）对Flume Agent进行具体配置  
内容如下：

a1.sources=r1
    a1.channels=c1 c2 
    a1.sinks=k1 k2 
    
    # configure source
    a1.sources.r1.type = TAILDIR
    a1.sources.r1.filegroups = f1
    a1.sources.r1.filegroups.f1 = /tmp/logs/app.+
    a1.sources.r1.fileHeader = true
    a1.sources.r1.channels = c1 c2
    
    #interceptor
    a1.sources.r1.interceptors = i1 i2
    a1.sources.r1.interceptors.i1.type = com.atguigu.flume.interceptor.LogETLInterceptor$Builder
    a1.sources.r1.interceptors.i2.type = com.atguigu.flume.interceptor.LogTypeInterceptor$Builder
    
    # selector
    a1.sources.r1.selector.type = multiplexing
    a1.sources.r1.selector.header = topic
    a1.sources.r1.selector.mapping.topic_start = c1
    a1.sources.r1.selector.mapping.topic_event = c2
    
    # configure channel
    a1.channels.c1.type = memory
    a1.channels.c1.capacity=10000
    a1.channels.c1.byteCapacityBufferPercentage=20
    
    a1.channels.c2.type = memory
    a1.channels.c2.capacity=10000
    a1.channels.c2.byteCapacityBufferPercentage=20
    
    # configure sink
    # start-sink
    a1.sinks.k1.type = org.apache.flume.sink.kafka.KafkaSink
    a1.sinks.k1.kafka.topic = topic_start
    a1.sinks.k1.kafka.bootstrap.servers = chen102:9092,chen103:9092,chen104:9092
    a1.sinks.k1.kafka.flumeBatchSize = 2000
    a1.sinks.k1.kafka.producer.acks = 1
    a1.sinks.k1.channel = c1
    
    # event-sink
    a1.sinks.k2.type = org.apache.flume.sink.kafka.KafkaSink
    a1.sinks.k2.kafka.topic = topic_event
    a1.sinks.k2.kafka.bootstrap.servers = chen102:9092,chen103:9092,chen104:9092
    a1.sinks.k2.kafka.flumeBatchSize = 2000
    a1.sinks.k2.kafka.producer.acks = 1
    a1.sinks.k2.channel = c2

注意：com.atguigu.flume.interceptor.LogETLInterceptor和com.atguigu.flume.interceptor.LogTypeInterceptor是自定义的拦截器的全类名。需要根据用户自定义的拦截器做相应修改。
    （6）在chen103上重复相同的操作

4）将自定义的拦截器flume-interceptor-1.0-SNAPSHOT.jar包放入到chen102的/opt/cloudera/parcels/CDH/lib/flume-ng/lib文件夹下面。如果没有自定义拦截器可以取消  
5）分发Flume的jar包到chen103

### 3.1.3 消费Kafka Flume配置 ###

1）集群规划

<table> 
 <thead> 
  <tr> 
   <th>chen102</th> 
   <th>chen103</th> 
   <th>chen104</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td></td> 
   <td></td> 
   <td>Flume（消费kafka）</td> 
  </tr> 
 </tbody> 
</table>

2）Flume配置分析  
![在这里插入图片描述][c84b90ed7a8f4a22ac3d5d4fe4c20e2d.png]  
3）Flume的具体配置如下：  
（1）在CM管理页面chen104上Flume的配置中找到代理名称

在配置文件如下内容(kafka-hdfs)

## 组件
    a1.sources=r1 r2
    a1.channels=c1 c2
    a1.sinks=k1 k2
    
    ## source1
    a1.sources.r1.type = org.apache.flume.source.kafka.KafkaSource
    a1.sources.r1.batchSize = 5000
    a1.sources.r1.batchDurationMillis = 2000
    a1.sources.r1.kafka.bootstrap.servers = hadoop102:9092,hadoop103:9092,hadoop104:9092
    a1.sources.r1.kafka.topics=topic_start
    
    ## source2
    a1.sources.r2.type = org.apache.flume.source.kafka.KafkaSource
    a1.sources.r2.batchSize = 5000
    a1.sources.r2.batchDurationMillis = 2000
    a1.sources.r2.kafka.bootstrap.servers = chen102:9092,chen103:9092,chen104:9092
    a1.sources.r2.kafka.topics=topic_event
    
    ## channel1
    a1.channels.c1.type=memory
    a1.channels.c1.capacity=100000
    a1.channels.c1.transactionCapacity=10000
    
    ## channel2
    a1.channels.c2.type=memory
    a1.channels.c2.capacity=100000
    a1.channels.c2.transactionCapacity=10000
    
    ## sink1
    a1.sinks.k1.type = hdfs
    a1.sinks.k1.hdfs.proxyUser=hive
    a1.sinks.k1.hdfs.path = /origin_data/gmall/log/topic_start/%Y-%m-%d
    a1.sinks.k1.hdfs.filePrefix = logstart-
    a1.sinks.k1.hdfs.round = true
    a1.sinks.k1.hdfs.roundValue = 10
    a1.sinks.k1.hdfs.roundUnit = second
    
    ##sink2
    a1.sinks.k2.type = hdfs
    a1.sinks.k2.hdfs.proxyUser=hive
    a1.sinks.k2.hdfs.path = /origin_data/gmall/log/topic_event/%Y-%m-%d
    a1.sinks.k2.hdfs.filePrefix = logevent-
    a1.sinks.k2.hdfs.round = true
    a1.sinks.k2.hdfs.roundValue = 10
    a1.sinks.k2.hdfs.roundUnit = second
    
    ## 不要产生大量小文件
    a1.sinks.k1.hdfs.rollInterval = 10
    a1.sinks.k1.hdfs.rollSize = 134217728
    a1.sinks.k1.hdfs.rollCount = 0
    
    a1.sinks.k2.hdfs.rollInterval = 10
    a1.sinks.k2.hdfs.rollSize = 134217728
    a1.sinks.k2.hdfs.rollCount = 0
    
    ## 控制输出文件是原生文件。
    a1.sinks.k1.hdfs.fileType = CompressedStream 
    a1.sinks.k2.hdfs.fileType = CompressedStream 
    
    a1.sinks.k1.hdfs.codeC = lzop
    a1.sinks.k2.hdfs.codeC = lzop
    
    ## 拼装
    a1.sources.r1.channels = c1
    a1.sinks.k1.channel= c1
    
    a1.sources.r2.channels = c2
    a1.sinks.k2.channel= c2

### 3.1.4 模拟生成日志 ###

1）确保Flume、Kafka等服务正常运行

2）在HDFS创建/origin\_data路径，并修改所有者为hive  
sudo -u hdfs hadoop fs -mkdir /origin\_data  
sudo -u hdfs hadoop fs -chown hive:hive /origin\_data  
![在这里插入图片描述][ebd2c48bd5794903b899e77986da9f9c.png]  
3）调用命令或脚本生成日志

4）登录HDFS-WEBUI  
可以发现能够正常采集到相应的日志数据，并生成了目录  
![在这里插入图片描述][e9c24cb99a274e14a06b8738d7851f8a.png]

### 3.2 数仓ODS层 ###

#### 3.2.1 创建数据库 ####

1）创建数据仓库目录，并修改所有者

sudo -u hdfs hadoop fs -mkdir /warehouse
    sudo -u hdfs hadoop fs -chown hive:hive /warehouse

2）修改hive配置  
![在这里插入图片描述][6144bf2b819d48b1a0492c97ba82cab8.png]

3）使用hue组件的hive用户进行操作  
4）创建gmall数据库  
![在这里插入图片描述][c66a936408124a2d8cbdb9532e17b355.png]  
5）使用gmall数据库

#### 3.2.2 创建启动日志表 ####

CREATE EXTERNAL TABLE ods_start_log (`line` string)
    PARTITIONED BY (`dt` string)
    STORED AS
      INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
      OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
    LOCATION '/warehouse/gmall/ods/ods_start_log';

![在这里插入图片描述][f5812eaa1de741679ac0ea261b28b6e4.png]

#### 3.2.3 ods层加载数据脚本 ####

#!/bin/bash
    
    # 定义变量方便修改
    APP=gmall
    
    # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
    if [ -n "$1" ] ;then
       do_date=$1
    else
       do_date=`date -d "-1 day" +%F`
    fi
    
    echo "===日志日期为 $do_date==="
    sql="
    load data inpath '/origin_data/gmall/log/topic_start/$do_date' into table "$APP".ods_start_log partition(dt='$do_date');
    
    "
    beeline -u "jdbc:hive2://chen102:10000/" -n hive -e "$sql"

#### 3.2.4 执行脚本 ####

sh ods_log.sh 2023-01-27

![在这里插入图片描述][e985541cb52b4ad3932241fd9b108f11.png]  
可在hue页面查看数据是否导入成功  
![在这里插入图片描述][53c67138244246358636858bb577195e.png]

### 3.3 DWD层 ###

#### 3.3.1 创建启动表 ####

drop table if exists dwd_start_log;
    CREATE EXTERNAL TABLE dwd_start_log(
    `mid_id` string,
    `user_id` string, 
    `version_code` string, 
    `version_name` string, 
    `lang` string, 
    `source` string, 
    `os` string, 
    `area` string, 
    `model` string,
    `brand` string, 
    `sdk_version` string, 
    `gmail` string, 
    `height_width` string,  
    `app_time` string,
    `network` string, 
    `lng` string, 
    `lat` string, 
    `entry` string, 
    `open_ad_type` string, 
    `action` string, 
    `loading_time` string, 
    `detail` string, 
    `extend1` string
    )
    PARTITIONED BY (dt string)
    location '/warehouse/gmall/dwd/dwd_start_log/';

#### 3.3.2 DWD数据导入脚本 ####

#!/bin/bash
    
    # 定义变量方便修改
    APP=gmall
    
    # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
    if [ -n "$1" ] ;then
    	do_date=$1
    else 
    	do_date=`date -d "-1 day" +%F`  
    fi 
    
    sql="
    set hive.exec.dynamic.partition.mode=nonstrict;
    
    insert overwrite table "$APP".dwd_start_log
    PARTITION (dt='$do_date')
    select 
        get_json_object(line,'$.mid') mid_id,
        get_json_object(line,'$.uid') user_id,
        get_json_object(line,'$.vc') version_code,
        get_json_object(line,'$.vn') version_name,
        get_json_object(line,'$.l') lang,
        get_json_object(line,'$.sr') source,
        get_json_object(line,'$.os') os,
        get_json_object(line,'$.ar') area,
        get_json_object(line,'$.md') model,
        get_json_object(line,'$.ba') brand,
        get_json_object(line,'$.sv') sdk_version,
        get_json_object(line,'$.g') gmail,
        get_json_object(line,'$.hw') height_width,
        get_json_object(line,'$.t') app_time,
        get_json_object(line,'$.nw') network,
        get_json_object(line,'$.ln') lng,
        get_json_object(line,'$.la') lat,
        get_json_object(line,'$.entry') entry,
        get_json_object(line,'$.open_ad_type') open_ad_type,
        get_json_object(line,'$.action') action,
        get_json_object(line,'$.loading_time') loading_time,
        get_json_object(line,'$.detail') detail,
        get_json_object(line,'$.extend1') extend1
    from "$APP".ods_start_log 
    where dt='$do_date';
    "
    
    beeline -u "jdbc:hive2://chen102:10000/" -n hive -e "$sql"

#### 3.3.3 执行脚本并验证 ####

![在这里插入图片描述][77af1d9416f64167829e7ddb763e1f55.png]  
![在这里插入图片描述][844a7a25a5c54ad0a2f8b73ac718248b.png]

### 3.4 DWS层 ###

目标：统计当日、当周、当月活动的每个设备明细

#### 3.4.1 每日设备活跃明细 ####

建表

drop table if exists dws_uv_detail_day;
    create external table dws_uv_detail_day
    (
        `mid_id` string COMMENT '设备唯一标识',
        `user_id` string COMMENT '用户标识', 
        `version_code` string COMMENT '程序版本号', 
        `version_name` string COMMENT '程序版本名', 
        `lang` string COMMENT '系统语言', 
        `source` string COMMENT '渠道号', 
        `os` string COMMENT '安卓系统版本', 
        `area` string COMMENT '区域', 
        `model` string COMMENT '手机型号', 
        `brand` string COMMENT '手机品牌', 
        `sdk_version` string COMMENT 'sdkVersion', 
        `gmail` string COMMENT 'gmail', 
        `height_width` string COMMENT '屏幕宽高',
        `app_time` string COMMENT '客户端日志产生时的时间',
        `network` string COMMENT '网络模式',
        `lng` string COMMENT '经度',
        `lat` string COMMENT '纬度'
    )
    partitioned by(dt string)
    stored as parquet
    location '/warehouse/gmall/dws/dws_uv_detail_day'
    ;

#### 3.4.2 数据导入脚本 ####

#!/bin/bash
    
    # 定义变量方便修改
    APP=gmall
    
    # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
    if [ -n "$1" ] ;then
    	do_date=$1
    else 
    	do_date=`date -d "-1 day" +%F`  
    fi 
    
    
    sql="
      set hive.exec.dynamic.partition.mode=nonstrict;
    
      insert overwrite table "$APP".dws_uv_detail_day partition(dt='$do_date')
      select  
        mid_id,
        concat_ws('|', collect_set(user_id)) user_id,
        concat_ws('|', collect_set(version_code)) version_code,
        concat_ws('|', collect_set(version_name)) version_name,
        concat_ws('|', collect_set(lang)) lang,
        concat_ws('|', collect_set(source)) source,
        concat_ws('|', collect_set(os)) os,
        concat_ws('|', collect_set(area)) area, 
        concat_ws('|', collect_set(model)) model,
        concat_ws('|', collect_set(brand)) brand,
        concat_ws('|', collect_set(sdk_version)) sdk_version,
        concat_ws('|', collect_set(gmail)) gmail,
        concat_ws('|', collect_set(height_width)) height_width,
        concat_ws('|', collect_set(app_time)) app_time,
        concat_ws('|', collect_set(network)) network,
        concat_ws('|', collect_set(lng)) lng,
        concat_ws('|', collect_set(lat)) lat
      from "$APP".dwd_start_log
      where dt='$do_date'  
      group by mid_id;
    "
    
    beeline -u "jdbc:hive2://chen102:10000/" -n hive -e "$sql"

#### 3.4.3 执行并验证 ####

![在这里插入图片描述][6f8b1e0ed8d24dd4823c017a79db314d.png]  
![在这里插入图片描述][0baad5ae4c4a4dfaa30205114fd8851b.png]

### 3.5 ADS层 ###

目标：当日活跃设备数

#### 3.5.1 建表 ####

drop table if exists ads_uv_count;
    create external table ads_uv_count( 
        `dt` string COMMENT '统计日期',
        `day_count` bigint COMMENT '当日用户数量'
    ) COMMENT '活跃设备数' 
    row format delimited fields terminated by '\t'
    location '/warehouse/gmall/ads/ads_uv_count/'
    ;

#### 3.5.2 ADS层加载脚本 ####

#!/bin/bash
    
    # 定义变量方便修改
    APP=gmall
    
    # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
    if [ -n "$1" ] ;then
    	do_date=$1
    else 
    	do_date=`date -d "-1 day" +%F`  
    fi 
    
    sql="
      set hive.exec.dynamic.partition.mode=nonstrict;
    
    insert into table "$APP".ads_uv_count 
    select  
      '$do_date' dt,
       daycount.ct
    from 
    (
       select  
          '$do_date' dt,
           count(*) ct
       from "$APP".dws_uv_detail_day
       where dt='$do_date'  
    )daycount;
    "
    beeline -u "jdbc:hive2://chen102:10000/" -n hive -e "$sql"

#### 3.5.3 执行并验证 ####

![在这里插入图片描述][ce114a82d1fa4b749ce691db881f4206.png]  
![在这里插入图片描述][6afb0cff11894b04ae1fa9d410954cd4.png]

## 4 业务数仓搭建 ##

### 4.1 业务数据生成 ###

1）连接Mysql创建数据库gmall  
2）设置数据库编码  
![在这里插入图片描述][851b94cd8cfe4aa8beec37f175bb37df.png]

3）导入建表语句（1建表脚本）  
sql脚本如下：

链接：https://pan.baidu.com/s/1WX3xVMQvAApSUZMobWLiLQ 
    提取码：8emk

1.  生成数据

CALL init_data('2019-02-10',1000,200,300,TRUE);

### 4.2 Sqoop导入脚本 ###

#!/bin/bash
    export HADOOP_USER_NAME=hive
    db_date=$2
    echo $db_date
    db_name=gmall
    
    import_data() {
    sqoop import \
    --connect jdbc:mysql://chen102:3306/$db_name \
    --username root \
    --password Chen.123456 \
    --target-dir  /origin_data/$db_name/db/$1/$db_date \
    --delete-target-dir \
    --num-mappers 1 \
    --fields-terminated-by "\t" \
    --query "$2"' and  $CONDITIONS;'
    }
    
    import_sku_info(){
      import_data  "sku_info"  "select 
    id, spu_id, price, sku_name, sku_desc, weight, tm_id,
    category3_id, create_time 
      from sku_info  where 1=1"
    }
    
    import_user_info(){
      import_data "user_info" "select 
    id, name, birthday, gender, email, user_level, 
    create_time 
    from user_info where 1=1"
    }
    
    import_base_category1(){
      import_data "base_category1" "select 
    id, name from base_category1 where 1=1"
    }
    
    import_base_category2(){
      import_data "base_category2" "select 
    id, name, category1_id from base_category2 where 1=1"
    }
    
    import_base_category3(){
      import_data "base_category3" "select id, name, category2_id from base_category3 where 1=1"
    }
    
    import_order_detail(){
      import_data   "order_detail"   "select 
        od.id, 
        order_id, 
        user_id, 
        sku_id, 
        sku_name, 
        order_price, 
        sku_num, 
        o.create_time  
      from order_info o , order_detail od 
      where o.id=od.order_id 
      and DATE_FORMAT(create_time,'%Y-%m-%d')='$db_date'"
    }
    
    import_payment_info(){
      import_data "payment_info"   "select 
        id,  
        out_trade_no, 
        order_id, 
        user_id, 
        alipay_trade_no, 
        total_amount,  
        subject, 
        payment_type, 
        payment_time 
      from payment_info 
      where DATE_FORMAT(payment_time,'%Y-%m-%d')='$db_date'"
    }
    
    import_order_info(){
      import_data   "order_info"   "select 
        id, 
        total_amount, 
        order_status, 
        user_id, 
        payment_way, 
        out_trade_no, 
        create_time, 
        operate_time  
      from order_info 
      where  (DATE_FORMAT(create_time,'%Y-%m-%d')='$db_date' or DATE_FORMAT(operate_time,'%Y-%m-%d')='$db_date')"
    }
    
    case $1 in
      "base_category1")
         import_base_category1
    ;;
      "base_category2")
         import_base_category2
    ;;
      "base_category3")
         import_base_category3
    ;;
      "order_info")
         import_order_info
    ;;
      "order_detail")
         import_order_detail
    ;;
      "sku_info")
         import_sku_info
    ;;
      "user_info")
         import_user_info
    ;;
      "payment_info")
         import_payment_info
    ;;
       "all")
       import_base_category1
       import_base_category2
       import_base_category3
       import_order_info
       import_order_detail
       import_sku_info
       import_user_info
       import_payment_info
    ;;
    esac

执行结果如下：

修改/orgin\_data/gmall/db路径的访问权限

sudo -u hdfs hadoop fs -chmod -R 777 /origin_data/gmall/db

### 4.3 ODS层 ###

#### 4.3.1 创建订单表 ####

drop table if exists ods_order_info;
    create external table ods_order_info ( 
        `id` string COMMENT '订单编号',
        `total_amount` decimal(10,2) COMMENT '订单金额', 
        `order_status` string COMMENT '订单状态', 
        `user_id` string COMMENT '用户id' ,
        `payment_way` string COMMENT '支付方式',  
        `out_trade_no` string COMMENT '支付流水号',  
        `create_time` string COMMENT '创建时间',  
        `operate_time` string COMMENT '操作时间' 
    ) COMMENT '订单表'
    PARTITIONED BY ( `dt` string)
    row format delimited  fields terminated by '\t' 
    location '/warehouse/gmall/ods/ods_order_info/'
    ;

#### 4.3.2 订单详情表 ####

drop table if exists ods_order_detail;
    create external table ods_order_detail( 
        `id` string COMMENT '订单编号',
        `order_id` string  COMMENT '订单号', 
        `user_id` string COMMENT '用户id' ,
        `sku_id` string COMMENT '商品id',  
        `sku_name` string COMMENT '商品名称',  
        `order_price` string COMMENT '商品价格',  
        `sku_num` string COMMENT '商品数量',  
        `create_time` string COMMENT '创建时间'
    ) COMMENT '订单明细表'
    PARTITIONED BY ( `dt` string)
    row format delimited  fields terminated by '\t' 
    location '/warehouse/gmall/ods/ods_order_detail/'

#### 4.3.3 创建商品表 ####

drop table if exists ods_sku_info;
    create external table ods_sku_info( 
        `id` string COMMENT 'skuId',
        `spu_id` string   COMMENT 'spuid', 
        `price` decimal(10,2) COMMENT '价格' ,
        `sku_name` string COMMENT '商品名称',  
        `sku_desc` string COMMENT '商品描述',  
        `weight` string COMMENT '重量',  
        `tm_id` string COMMENT '品牌id',  
        `category3_id` string COMMENT '品类id',  
        `create_time` string COMMENT '创建时间'
    ) COMMENT '商品表'
    PARTITIONED BY ( `dt` string)
    row format delimited  fields terminated by '\t' 
    location '/warehouse/gmall/ods/ods_sku_info/'
    ;

#### 4.3.4 创建用户表 ####

drop table if exists ods_user_info;
    create external table ods_user_info( 
        `id` string COMMENT '用户id',
        `name`  string COMMENT '姓名', 
        `birthday` string COMMENT '生日' ,
        `gender` string COMMENT '性别',  
        `email` string COMMENT '邮箱',  
        `user_level` string COMMENT '用户等级',  
        `create_time` string COMMENT '创建时间'
    ) COMMENT '用户信息'
    PARTITIONED BY ( `dt` string)
    row format delimited  fields terminated by '\t' 
    location '/warehouse/gmall/ods/ods_user_info/'
    ;

#### 4.3.5 创建一级分类表 ####

drop table if exists ods_base_category1;
    create external table ods_base_category1( 
        `id` string COMMENT 'id',
        `name`  string COMMENT '名称'
    ) COMMENT '商品一级分类'
    PARTITIONED BY ( `dt` string)
    row format delimited  fields terminated by '\t' 
    location '/warehouse/gmall/ods/ods_base_category1/'
    ;

#### 4.3.6 创建二级分类表 ####

drop table if exists ods_base_category2;
    create external table ods_base_category2( 
        `id` string COMMENT ' id',
        `name`  string COMMENT '名称',
        category1_id string COMMENT '一级品类id'
    ) COMMENT '商品二级分类'
    PARTITIONED BY ( `dt` string)
    row format delimited  fields terminated by '\t' 
    location '/warehouse/gmall/ods/ods_base_category2/'
    ;

#### 4.3.7 创建三级分类表 ####

drop table if exists ods_base_category3;
    create external table ods_base_category3( 
        `id` string COMMENT ' id',
        `name`  string COMMENT '名称',
        category2_id string COMMENT '二级品类id'
    ) COMMENT '商品三级分类'
    PARTITIONED BY ( `dt` string)
    row format delimited  fields terminated by '\t' 
    location '/warehouse/gmall/ods/ods_base_category3/'
    ;

#### 4.3.8 创建支付流水表 ####

drop table if exists `ods_payment_info`;
    create external table  `ods_payment_info`(
        `id`   bigint COMMENT '编号',
        `out_trade_no`    string COMMENT '对外业务编号',
        `order_id`        string COMMENT '订单编号',
        `user_id`         string COMMENT '用户编号',
        `alipay_trade_no` string COMMENT '支付宝交易流水编号',
        `total_amount`    decimal(16,2) COMMENT '支付金额',
        `subject`         string COMMENT '交易内容',
        `payment_type` string COMMENT '支付类型',
        `payment_time`   string COMMENT '支付时间'
       )  COMMENT '支付流水表'
    PARTITIONED BY ( `dt` string)
    row format delimited  fields terminated by '\t' 
    location '/warehouse/gmall/ods/ods_payment_info/'
    ;

#### 4.3.8 ODS数据导入脚本 ####

#!/bin/bash
    
    APP=gmall
    
    # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
    if [ -n "$1" ] ;then
    	do_date=$1
    else 
    	do_date=`date -d "-1 day" +%F`  
    fi
    
    sql=" 
    load data inpath '/origin_data/$APP/db/order_info/$do_date'  OVERWRITE into table "$APP".ods_order_info partition(dt='$do_date');
    
    load data inpath '/origin_data/$APP/db/order_detail/$do_date'  OVERWRITE into table "$APP".ods_order_detail partition(dt='$do_date');
    
    load data inpath '/origin_data/$APP/db/sku_info/$do_date'  OVERWRITE into table "$APP".ods_sku_info partition(dt='$do_date');
    
    load data inpath '/origin_data/$APP/db/user_info/$do_date' OVERWRITE into table "$APP".ods_user_info partition(dt='$do_date');
    
    load data inpath '/origin_data/$APP/db/payment_info/$do_date' OVERWRITE into table "$APP".ods_payment_info partition(dt='$do_date');
    
    load data inpath '/origin_data/$APP/db/base_category1/$do_date' OVERWRITE into table "$APP".ods_base_category1 partition(dt='$do_date');
    
    load data inpath '/origin_data/$APP/db/base_category2/$do_date' OVERWRITE into table "$APP".ods_base_category2 partition(dt='$do_date');
    
    load data inpath '/origin_data/$APP/db/base_category3/$do_date' OVERWRITE into table "$APP".ods_base_category3 partition(dt='$do_date'); 
    "
    beeline -u "jdbc:hive2://hadoop102:10000/" -n hive -e "$sql"

执行结果如下：  
![在这里插入图片描述][6ac20f28a057419dbcbebadf30a2a7d4.png]

### 4.4 DWD层 ###

#### 4.4.1 创建订单表 ####

drop table if exists dwd_order_info;
    create external table dwd_order_info ( 
        `id` string COMMENT '',
        `total_amount` decimal(10,2) COMMENT '', 
        `order_status` string COMMENT ' 1  2  3  4  5', 
        `user_id` string COMMENT 'id' ,
        `payment_way` string COMMENT '',  
        `out_trade_no` string COMMENT '',  
        `create_time` string COMMENT '',  
        `operate_time` string COMMENT '' 
    ) 
    PARTITIONED BY ( `dt` string)
    stored as  parquet
    location '/warehouse/gmall/dwd/dwd_order_info/'

#### 4.4.2 创建订单详情表 ####

drop table if exists dwd_order_detail;
    create external table dwd_order_detail( 
        `id` string COMMENT '',
        `order_id` decimal(10,2) COMMENT '', 
        `user_id` string COMMENT 'id' ,
        `sku_id` string COMMENT 'id',  
        `sku_name` string COMMENT '',  
        `order_price` string COMMENT '',  
        `sku_num` string COMMENT '', 
        `create_time` string COMMENT ''
    ) 
    PARTITIONED BY (`dt` string)
    stored as parquet
    location '/warehouse/gmall/dwd/dwd_order_detail/'
    ;

#### 4.4.3 创建用户表 ####

drop table if exists dwd_user_info;
    create external table dwd_user_info( 
        `id` string COMMENT 'id',
        `name`  string COMMENT '', 
        `birthday` string COMMENT '' ,
        `gender` string COMMENT '',  
        `email` string COMMENT '',  
        `user_level` string COMMENT '',  
        `create_time` string COMMENT ''
    ) 
    PARTITIONED BY (`dt` string)
    stored as  parquet
    location '/warehouse/gmall/dwd/dwd_user_info/'
    ;

#### 4.4.4 创建支付流水表 ####

drop table if exists `dwd_payment_info`;
    create external  table  `dwd_payment_info`(
        `id`   bigint COMMENT '',
        `out_trade_no`    string COMMENT '',
        `order_id`        string COMMENT '',
        `user_id`         string COMMENT '',
        `alipay_trade_no` string COMMENT '',
        `total_amount`    decimal(16,2) COMMENT '',
        `subject`         string COMMENT '',
        `payment_type`    string COMMENT '',
        `payment_time`    string COMMENT ''
       )  
    PARTITIONED BY ( `dt` string)
    stored as  parquet
    location '/warehouse/gmall/dwd/dwd_payment_info/'
    ;

#### 4.4.5 创建商品表 ####

drop table if exists dwd_sku_info;
    create external table dwd_sku_info( 
        `id` string COMMENT 'skuId',
        `spu_id` string COMMENT 'spuid', 
        `price` decimal(10,2) COMMENT '' ,
        `sku_name` string COMMENT '',  
        `sku_desc` string COMMENT '',  
        `weight` string COMMENT '',  
        `tm_id` string COMMENT 'id',  
        `category3_id` string COMMENT '1id',  
        `category2_id` string COMMENT '2id',  
        `category1_id` string COMMENT '3id',  
        `category3_name` string COMMENT '3',  
        `category2_name` string COMMENT '2',  
        `category1_name` string COMMENT '1',  
        `create_time` string COMMENT ''
    ) 
    PARTITIONED BY ( `dt` string)
    stored as  parquet
    location '/warehouse/gmall/dwd/dwd_sku_info/'
    ;

#### 4.4.6 DWD层数据导入脚本 ####

#!/bin/bash
    
    # 定义变量方便修改
    APP=gmall
    
    # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
    if [ -n "$1" ] ;then
    	do_date=$1
    else 
    	do_date=`date -d "-1 day" +%F`  
    fi 
    
    sql="
    
    set hive.exec.dynamic.partition.mode=nonstrict;
    
    insert overwrite table   "$APP".dwd_order_info partition(dt)
    select * from "$APP".ods_order_info 
    where dt='$do_date'  and id is not null;
     
    insert overwrite table   "$APP".dwd_order_detail partition(dt)
    select * from "$APP".ods_order_detail 
    where dt='$do_date'   and id is not null;
    
    insert overwrite table   "$APP".dwd_user_info partition(dt)
    select * from "$APP".ods_user_info
    where dt='$do_date'   and id is not null;
     
    insert overwrite table   "$APP".dwd_payment_info partition(dt)
    select * from "$APP".ods_payment_info
    where dt='$do_date'  and id is not null;
    
    insert overwrite table   "$APP".dwd_sku_info partition(dt)
    select  
        sku.id,
        sku.spu_id, 
        sku.price,
        sku.sku_name,  
        sku.sku_desc,  
        sku.weight,  
        sku.tm_id,  
        sku.category3_id,  
        c2.id category2_id ,  
        c1.id category1_id,  
        c3.name category3_name,  
        c2.name category2_name,  
        c1.name category1_name,  
        sku.create_time,
        sku.dt
    from
        "$APP".ods_sku_info sku 
    join "$APP".ods_base_category3 c3 on sku.category3_id=c3.id 
        join "$APP".ods_base_category2 c2 on c3.category2_id=c2.id 
        join "$APP".ods_base_category1 c1 on c2.category1_id=c1.id 
    where sku.dt='$do_date'  and c2.dt='$do_date'  
    and  c3.dt='$do_date' and  c1.dt='$do_date' 
    and sku.id is not null;
    "
    beeline -u "jdbc:hive2://chen102:10000/" -n hive -e "$sql"

执行结果如下：  
![在这里插入图片描述][57476d296ebe415a9e4e8f35eed4273f.png]

### 4.5 DWS层 ###

1）为什么要建宽表  
需求目标，把每个用户单日的行为聚合起来组成一张多列宽表，以便之后关联用户维度信息后进行，不同角度的统计分析。

#### 4.5.1 创建用户行为宽表 ####

drop table if exists dws_user_action;
    create external table dws_user_action 
    (   
        user_id          string      comment '用户 id',
        order_count     bigint      comment '下单次数 ',
        order_amount    decimal(16,2)  comment '下单金额 ',
        payment_count   bigint      comment '支付次数',
        payment_amount  decimal(16,2) comment '支付金额 '
    ) COMMENT '每日用户行为宽表'
    PARTITIONED BY (`dt` string)
    stored as parquet
    location '/warehouse/gmall/dws/dws_user_action/'
    tblproperties ("parquet.compression"="snappy");

#### 4.5.2 用户行为宽表导入脚本 ####

#!/bin/bash
    
    # 定义变量方便修改
    APP=gmall
    
    # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
    if [ -n "$1" ] ;then
    	do_date=$1
    else 
    	do_date=`date -d "-1 day" +%F`  
    fi 
    
    sql="
    with  
    tmp_order as
    (
        select 
            user_id, 
            count(*)  order_count,
            sum(oi.total_amount) order_amount
        from "$APP".dwd_order_info oi
        where date_format(oi.create_time,'yyyy-MM-dd')='$do_date'
        group by user_id
    ) ,
    tmp_payment as
    (
        select
            user_id, 
            sum(pi.total_amount) payment_amount, 
            count(*) payment_count 
        from "$APP".dwd_payment_info pi 
        where date_format(pi.payment_time,'yyyy-MM-dd')='$do_date'
        group by user_id
    )
    insert overwrite table "$APP".dws_user_action partition(dt='$do_date')
    select
        user_actions.user_id,
        sum(user_actions.order_count),
        sum(user_actions.order_amount),
        sum(user_actions.payment_count),
        sum(user_actions.payment_amount)
    from 
    (
        select
            user_id,
            order_count,
            order_amount,
            0 payment_count,
            0 payment_amount
        from tmp_order
    
        union all
        select
            user_id,
            0 order_count,
            0 order_amount,
            payment_count,
            payment_amount
        from tmp_payment
     ) user_actions
    group by user_id;
    "
    beeline -u "jdbc:hive2://chen102:10000/" -n hive -e "$sql"

执行结果如下：  
![在这里插入图片描述][399ff8835e724af5af1bb9938c4a7df4.png]

### 4.6 ADS层 ###

求GMV成交总额

#### 4.6.1 建表 ####

drop table if exists ads_gmv_sum_day;
    create external table ads_gmv_sum_day(
        `dt` string COMMENT '统计日期',
        `gmv_count`  bigint COMMENT '当日gmv订单个数',
        `gmv_amount`  decimal(16,2) COMMENT '当日gmv订单总金额',
        `gmv_payment`  decimal(16,2) COMMENT '当日支付金额'
    ) COMMENT 'GMV'
    row format delimited fields terminated by '\t'
    location '/warehouse/gmall/ads/ads_gmv_sum_day/'
    ;

#### 4.6.2 数据导入脚本 ####

#!/bin/bash
    
    # 定义变量方便修改
    APP=gmall
    
    # 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
    if [ -n "$1" ] ;then
    	do_date=$1
    else 
    	do_date=`date -d "-1 day" +%F`
    fi 
    
    sql="
    insert into table "$APP".ads_gmv_sum_day 
    select 
        '$do_date' dt,
        sum(order_count)  gmv_count,
        sum(order_amount) gmv_amount,
        sum(payment_amount) payment_amount 
    from "$APP".dws_user_action 
    where dt ='$do_date'
    group by dt;
    "
    
    beeline -u "jdbc:hive2://chen102:10000/" -n hive -e "$sql"

执行结果如下：  
![在这里插入图片描述][7263a544e67e4d67ada7fa055645c84b.png]

#### 4.6.3 数据导出脚本 ####

1）在MySQL中创建ads\_gmv\_sum\_day表

DROP TABLE IF EXISTS ads_gmv_sum_day;
    CREATE TABLE ads_gmv_sum_day(
      `dt` varchar(200) DEFAULT NULL COMMENT '统计日期',
      `gmv_count` bigint(20) DEFAULT NULL COMMENT '当日gmv订单个数',
      `gmv_amount` decimal(16, 2) DEFAULT NULL COMMENT '当日gmv订单总金额',
      `gmv_payment` decimal(16, 2) DEFAULT NULL COMMENT '当日支付金额'
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '每日活跃用户数量' ROW_FORMAT = Dynamic;

#!/bin/bash
    
    export HADOOP_USER_NAME=hive
    
    db_name=gmall
    
    export_data() {
        
    sqoop export \
    --connect "jdbc:mysql://chen102:3306/${db_name}?useUnicode=true&characterEncoding=utf-8"  \
    --username root \
    --password Atguigu.123456 \
    --table $1 \
    --num-mappers 1 \
    --export-dir /warehouse/$db_name/ads/$1 \
    --input-fields-terminated-by "\t" \
    --update-mode allowinsert \
    --update-key $2 \
    --input-null-string '\\N'    \
    --input-null-non-string '\\N'
    }
    
    case $1 in
      "ads_gmv_sum_day")
         export_data "ads_gmv_sum_day" "dt"
    ;;
       "all")
         export_data "ads_gmv_sum_day" "dt"
    ;;
    esac

执行结果如下：  
![在这里插入图片描述][3249ab12d13d4bda8a46e31022b803b0.png]

#### 4.6.4 执行脚本并验证 ####

select * from ads_gmv_sum_day

![在这里插入图片描述][6b976ab1ef844560b5376226f77c18a3.png]  
至此，用户行为数仓和业务数仓搭建完成。接下来是CDH即席查询——Impala

## 5 Oozie基于Hue实现GMV指标全流程调度 ##

### 5.1 在Hue中创建Oozie任务GMV ###

1）生成新的业务数据

CALL init_data('2023-01-29',300,200,300,FALSE);

2）将oozie调度脚本上传到HDFS

sudo -u hive hadoop fs -mkdir /user/hive/bin/
    cp /root/bin/*.sh /var/lib/hive/
    sudo -u hive hadoop fs -put /var/lib/hive/*.sh /user/hive/bin

![在这里插入图片描述][0552728a02804c299e5a1d72537d3921.png]  
3）编辑workflow  
![在这里插入图片描述][1551ec646eee4ccbaceb7f027ebe9857.png]  
4）添加工作流

5）保存并执行workflow  
等待执行成功  
![在这里插入图片描述][b7eab9f0112048868d0221ace8961e58.png]  
![在这里插入图片描述][991e423ca698425fb980d3ec6eeee593.png]  
6) 查看Mysql执行结果  
![在这里插入图片描述][df23667514f74c108cbfcfb133203b30.png]

## 6 即席查询 ##

### 6.1 Impala安装 ###

1）添加服务  
![在这里插入图片描述][dbd98d150a574366b9b86d78bf8c0ecb.png]  
2）角色分配  
![在这里插入图片描述][0fe0177a43ef486fbd2d4c55548577e4.png]

注意：最好将StateStore和CataLog Sever单独部署在同一节点上。

3）配置并启动Impala  
![在这里插入图片描述][6f699bf63b274b63a4bdfd7aa48aa8ab.png]

### 6.2 配置Hue支持Impala ###

1）进入HUE配置页面，搜索“impala”，开启HUE中的impala服务  
![在这里插入图片描述][c405c362575d4a70a2a577e72e98f8f5.png]  
2）搜索“hue\_safety\_valve.ini 的 Hue 服务高级配置代码段（安全阀）”，输入以下代码段，确定HUE支持impala搜索引擎  
![在这里插入图片描述][b38bd7940af04b2eb1b395257b5cd98d.png]

### 6.3 Impala基于Hue查询 ###

1.  打开hue
2.  进入impala查询  
    分别用impala和hive查询以下SQL语句，对比查询速度。

select  
        sku.id,
        sku.spu_id, 
        sku.price,
        sku.sku_name,  
        sku.sku_desc,  
        sku.weight,  
        sku.tm_id,  
        sku.category3_id,  
        c2.id category2_id ,  
        c1.id category1_id,  
        c3.name category3_name,  
        c2.name category2_name,  
        c1.name category1_name,  
        sku.create_time,
        sku.dt
    from
        ods_sku_info sku 
    join ods_base_category3 c3 on sku.category3_id=c3.id 
        join ods_base_category2 c2 on c3.category2_id=c2.id 
        join ods_base_category1 c1 on c2.category1_id=c1.id 
    where sku.dt='2023-01-27'  and c2.dt='2023-01-29';

hive查询耗时22秒  
![在这里插入图片描述][557655f7d8a74bd9b9e61096cf6c7e43.png]  
impala耗时2秒左右  
![在这里插入图片描述][d63d4322bfef4d1d9f5f66819eeaf776.png]

接下来是Kerberos认证和Sentry权限管理相关内容，详见《[CDH数仓项目(四) —— 集群性能测试/资源管理/清理CDH集群》][CDH_ _ _CDH]

[CDH_ _ CDH]: https://blog.csdn.net/Keyuchen_01/article/details/128742159?spm=1001.2014.3001.5502
[9308eb16639745939cb9513e55abcb73.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/e4f86b1516d8494cbc12ac76dd9104da.png
[b61fa0bd5e734b7e9908ab3249979647.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/93021b092a1246b2ac9ff9a7de47f8d7.png
[3ce4029fde804c65901f0b96a3344d7e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/24e671e322a84a72a30859063b36bdbf.png
[df46843758494ad0bbaded81e8c397da.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/39e89cab7aa14d8fa597b8a940833788.png
[b1bf3b4ca58d45279ad767bd25c7cb84.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/b01589b07954457097110630feb97e9f.png
[5eea36a9a70d465ca854084aeb5d556c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/b540e455893a4a2e9268bb411476fc0b.png
[fa4b0e3e07b04d03b89ab00d3510e18c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/58d8c5f762f847e29991dcf2c86b467d.png
[17d1ff7f74f745ee85ad9d01e498c45c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/2803b9b65c934ca1a92e10478c619ae8.png
[d8e2718941f74eccb137bb386cebe683.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/e100a8ab176b408abdac0255938c8d09.png
[303a797a1ce44f7f8f384096dbeb7633.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/7cdeb69be3834e58a7a382463f76d31b.png
[95ee0d2b498549819471aeba7d1eef79.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/5b60944f18c74d1f822e8418b06492bd.png
[c5fe893af24c411798f41b569125c49d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/347944342d1542549b589fb271981a82.png
[9789e5f79a4a47b28d7867e4ac8bd604.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/480b9d925b7b40ddb28f03e16871f836.png
[1062f59c34cf49ca801be48f399b565b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/f4c7d34ef8fb40419f9ec49b0e9d2c73.png
[80211522b7e348d28bb216c31fdcf619.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/e114632a50c043349265652160b9b4ec.png
[60c99342813347e2a46b13fa2b35b4e0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/4b34f4f4b7664e51b9b5582a440ece97.png
[a53ad68c931e4e5497b03f932c9dc096.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/b660e73d59074f9fa134dd9f3358eba0.png
[ca0aa8b1739e4e1c9a13dcb0ee864a8f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/350e185709d54bd2a656566492b3490a.png
[0af71f1603fa43c287782c33ee611b5f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/244c7aeff1914d029543fffc154a10c3.png
[c84b90ed7a8f4a22ac3d5d4fe4c20e2d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/543f07c5e12c43f0bb844b2c38cd2018.png
[ebd2c48bd5794903b899e77986da9f9c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/0f1f3658070c4000a3dc9b115b7c8945.png
[e9c24cb99a274e14a06b8738d7851f8a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/4e86693ba0214299a1b15a479d3b2f57.png
[6144bf2b819d48b1a0492c97ba82cab8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/e3cb84fb6dfa45a18a67980b7ddd98a9.png
[c66a936408124a2d8cbdb9532e17b355.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/5d2cb3563e3a4d72893791555cafbea6.png
[f5812eaa1de741679ac0ea261b28b6e4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/d0de3b3c5fad4ac2a9457fba111ced71.png
[e985541cb52b4ad3932241fd9b108f11.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/1c3e90302fff446785b017e582653b77.png
[53c67138244246358636858bb577195e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/37895e4968e547c399489b4000ca76bc.png
[77af1d9416f64167829e7ddb763e1f55.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/87b32e4ba5b44824b82b5e489847c1c7.png
[844a7a25a5c54ad0a2f8b73ac718248b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/d173c45b1eb94a2ba3e5517131a74ef5.png
[6f8b1e0ed8d24dd4823c017a79db314d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/1d8b54570ea3426490dcaf4664dedc43.png
[0baad5ae4c4a4dfaa30205114fd8851b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/fd2a055ab8ba4237a889b6b2da8918a3.png
[ce114a82d1fa4b749ce691db881f4206.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/a41a396d92134032af749a00e0bada06.png
[6afb0cff11894b04ae1fa9d410954cd4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/62b2185b3ad14ea48e289691b4a59d7a.png
[851b94cd8cfe4aa8beec37f175bb37df.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/6504509954114c96b473133c1cb784ae.png
[6ac20f28a057419dbcbebadf30a2a7d4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/a0bfeedf3d1345ab9d982cd349613d71.png
[57476d296ebe415a9e4e8f35eed4273f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/b3505af7c3754ebba2acb2c0d1f08969.png
[399ff8835e724af5af1bb9938c4a7df4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/6727bf9a5e0f499989e02cf37fc95314.png
[7263a544e67e4d67ada7fa055645c84b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/b6445fa6b7e74f9a80c666c4009d27db.png
[3249ab12d13d4bda8a46e31022b803b0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/58d44d4444ad437993b4a7bb6cc6025f.png
[6b976ab1ef844560b5376226f77c18a3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/9544e958ed4045709d8c12e1301fadc9.png
[0552728a02804c299e5a1d72537d3921.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/bb6749965df24ad88a4bcf7b640bbb1e.png
[1551ec646eee4ccbaceb7f027ebe9857.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/bea631185232444d9ac016708091861f.png
[b7eab9f0112048868d0221ace8961e58.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/dba06ea6dea349c499d2c850b69afd6e.png
[991e423ca698425fb980d3ec6eeee593.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/7be4f38733e34cf8abec4e7e0b3528d3.png
[df23667514f74c108cbfcfb133203b30.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/450af8878ff24f0aacaf7ad313a1aadf.png
[dbd98d150a574366b9b86d78bf8c0ecb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/d0441edb33b14ffc9276d958b7a75a94.png
[0fe0177a43ef486fbd2d4c55548577e4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/57b5a7a1f2704c6ba8e9dcb01707bc9b.png
[6f699bf63b274b63a4bdfd7aa48aa8ab.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/2c4f22bea00345fca04f593fffaefae2.png
[c405c362575d4a70a2a577e72e98f8f5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/161e2fec0b474a7eb657e8add7e1724c.png
[b38bd7940af04b2eb1b395257b5cd98d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/e733acdb962d4e8b8f5513551ab9996f.png
[557655f7d8a74bd9b9e61096cf6c7e43.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/95af78ee863d4e15ba24d14897889219.png
[d63d4322bfef4d1d9f5f66819eeaf776.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/a24ae5666d064cda8e330b4474ce62b8.png
[CDH_ _ _CDH]: https://blog.csdn.net/Keyuchen_01/article/details/128857207