说明：此卷为2022年全国职业院校技能大赛河南省省赛离线模块。此卷难度、繁琐度较大，需处理近20张表，其中包括三张Hbase表导入导出（最简单的是指标部分，往年都是指标最难）此文章包括SQL及DSL语法，其包括SQL全部解题，DSL语法部分解题，未用DSL语法主要为Hbase导入导出及指标，DSL语法中有for循环解题（较为简洁）。Spark版本为3.1.1，Hadoop版本为3.1.3，Scala版本为2.12，Hive版本为3.1.2。数据暂不共享

任务一:离线数据采集

编写Scala 工程代码，将 MySQL 的 ds_db01 库中表 order_masterorder_detail、coupon_info、 coupon_use、product_browse、product_info、customer_inf、 customer_login_log 、 order_cart、 customer_level_infcustomer_addr 的数据增量抽取到 Hive 的 ods 库中对应表 order masterorder_detail、coupon_info、coupon_use、product_browse、product_infocustomer_inf、customer_login_log 、 order_cart 、 customer_level_infcustomer_addr 中。

1、抽取 ds_db0l 库中 order_master 的增量数据进入 Hive 的 ods 库中表order_master。根据 ods.order_master 表中 modified_time 作为增量字段只将新增的数据抽入，字段名称、类型不变，同时添加静态分区，分区字段为etl_date，类型为 String，且值为当前比赛日的前一天日期 (分区字段格式为 yyyyMMdd)。使用 hive cli 执行 show partitions ods.order_master命令，将执行结果截图粘贴至客户端桌面[Release\模块 B 提交结果.docx]中对应的任务序号下;

2、抽取 ds_db01 库中 order_detail 的增量数据进入 Hive 的 ods 库中表order_detail。根据 ods.order_detail表中 modified_time 作为增量字段只将新增的数据抽入，字段名称、类型不变，同时添加静态分区，分区字段为etl_date，类型为 String，且值为当前比赛日的前一天日期 (分区字段格式为 yyyyMMdd)。使用 hive cli 执行 show partitions ods.order_detail命令，将执行结果截图粘贴至客户端桌面[Release\模块 B 提交结果.docx]中对应的任务序号下:

3、抽取 ds_db0l库中 coupon_info的增量数据进入 Hive 的 ods 库中表coupon_info，根据 ods.coupon_info 表中 modified_time 作为增量字段，只将新增的数据抽入，字段名称、类型不变，同时添加静态分区，分区字段为etl_date，类型为String，且值为当前比赛日的前一天日期 (分区字段格式为 yyyyMMdd)。使用 hive cli 执行 show partitions ods.coupon_info命令，将执行结果截图粘贴至客户端桌面[Release 模块 B 提交结果.docx]中对应的任务序号下:

4、抽取 ds_db01 库中 coupon_use 的增量数据进入 Hive 的 ods 库中表coupon_use，增量字段取 ods.coupon_use 表中 get_time、used_timepay_time 中的最大者，只将新增的数据抽入，字段名称、类型不变，同时添加静态分区，分区字段为 etl_date，类型为String，且值为当前比赛日的前一天日期(分区字段格式为 yyyyMMdd) 。使用 Hive cli 查询最新分区数据总条数，将执行结果截图粘贴至客户端桌面[Release 模块 B 提交结果.docx] 中对应的任务序号下:

5、抽取 ds_db01库中 product_browse 的增量数据进入 Hive 的 ods 库中表product_browse，根据ods.product_browse 表中 modified_time 作为增量字段，只将新增的数据抽入，字段名称、类型不变，同时添加静态分区，分区字段为etl_date，类型为String，且值为当前比赛日的前一天日期 (分区字段格式为 yyyyMMdd)。使用 hive cli执行 show partitionsods.product_browse 命令，将执行结果截图粘贴至客户端桌面[Release\模块 B 提交结果.docx]中对应的任务序号下。

6、抽取 ds_db0l 库中 product_info 的增量数据进入 Hive 的 ods 库中表product_info，根据ods.product_info 表中 modified_time 作为增量字段只将新增的数据抽入，字段名称、类型不变，同时添加静态分区，分区字段为 etl_date，类型为 String，且值为当前比赛日的前一天日期 (分区字段格式为 yyyyMMdd)。使用 hive cli 执行 show partitions ods.product_info命令，将执行结果截图粘贴至客户端桌面[Release\模块 B 提交结果.docx]中对应的任务序号下;

7、抽取 ds_db01 库中 customer_inf 的增量数据进入 Hive 的 ods 库中表customer_inf，根据 ods.customer_inf 表中 modified_time 作为增量字段只将新增的数据抽入，字段名称、类型不变，同时添加静态分区，分区字段为 etl_date，类型为 String，且值为当前比赛日的前一天日期 (分区字段格式为 yyyyMMdd)。使用 hive cli 执行 show partitions ods.customer_inf命令，将执行结果截图粘贴至客户端桌面[Release\模块 B 提交结果.docx]中对应的任务序号下:

8、抽取 ds_db01库中 customer_login_log 的增量数据进入 Hive 的ods 库中表customer_login_log，根据ods.customer_login_log 表中login_time 作为增量字段，只将新增的数据抽入，字段名称、类型不变，同时添加静态分区分区字段为 etl_date，类型为 String，且值为当前比赛日的前一天日期(分区字段格式为yyyyMMdd)。使用 hive cli 执行 show partitions ods.customer_login_log 命令，将执行结果截图粘贴至客户端桌面[Release模块 B 提交结果.docx] 中对应的任务序号下:

9、抽取 ds_db0l 库中 order_cart 的增量数据进入Hive 的 ods 库中表order_cart，根据ods.order_cart 表中 modified_time 作为增量字段，只将新增的数据抽入，字段名称、类型不变，同时添加静态分区，分区字段为etl_date，类型为 String，且值为当前比赛日的前一天日期 (分区字段格式为yyyyMMdd)。使用 hive cli 执行 show partitions ods.order_cart 命令将执行结果截图粘贴至客户端桌面[Release\模块 B 提交结果.docx]中对应的任务序号下;

10、抽取 ds_db0l 库中 customer_addr 的增量数据进入 Hive 的 ods 库中表customer_addr，根据ods.customer_addr 表中 modified_time 作为增量字段，只将新增的数据抽入，字段名称、类型不变，同时添加静态分区，分区字段为etl_date，类型为 String，且值为当前比赛日的前一天日期 (分区字段格式为 yyyyMMdd)。使用 hive cli 执行 show partitions ods.customer_addr 命令，将执行结果截图粘贴至客户端桌面[Release\模块 B提交结果.docx]中对应的任务序号下;

11、抽取 ds_db01库中 customer_level_inf 的增量数据进入 Hive 的 ods 库中国表 customer_level_inf ， 根 据 ods.customer_level_inf 表中modified_time 作为增量字段，只将新增的数据抽入，字段名称、类型不变，同时添加静态分区，分区字段为 etl_date，类型为 String，且值为当前比赛日的前一天日期(分区字段格式为 yyyyMMdd)。使用 hive cli 执行 showpartitions ods.customer_level_inf 命令，将执行结果截图粘贴至客户端桌面[Release\模块 B 提交结果.docx] 中对应的任务序号下；

以下为SQL解法

以下为DSL解法

任务二:离线数据清洗（此卷最难部分）

编写 Scala 工程代码，将 ods 库中表 order_master、order_detailcoupon_info、coupon_use、 product_browse、product_info、customer_inf、customer_login_log、order_cart、customer_level_inf、customer_addr 抽取到Hive的 dwd库中对应表中。表中有涉及到 timestamp 类型的，均要求按照yyyy-MM-dd HH:mm:ss，不记录毫秒数，若原数据中只有年月日，则在时分秒的位置添加 00:00:00，添加之后使其符合 yyyy-MM-dd HH:mm:ss。

1、  抽取 ods 库中表 customer_inf 最新分区数据，并结合 dim_customer_inf 最新分区现有的数据，根据 customer_id 合并数据到dwd 库中dim_customer_inf 的分区表 (合并是指对 dwd 层数据进行插入或修改，需修改的数据以customer_id 为合并字段，根据 modified_time 排序取最新的一条)，分区字段为etl_date 且值与 ods 库的相对应表该值相等，并添加dwd_insert_user、dwd_insert_time、 dwd _modify_user、 dwd_modify_time四列,其中dwd_insert_user、dwd_modify_user 均填写“userl”。若该条记录第一次进入数仓dwd 层则dwd_insert_time、dwd_modify_time 均存当前操作时间，并进行数据类型转换。若该数据在进入 dwd 层时发生了合并修改，则dwd_insert_time 时间不变，dwd_modify_time 存当前操作时间，其余列存最新的值。使用 hive cli查询modified_time 为 2022年10月01日当天的数据，查询字段为 customer_id、customer_email、modified_timedwd_insert_time、dwd_modify_time，并按照 customer_id 进行升序排序将结果截图粘贴至客户端桌面[Release\模块 D提交结果.docx]中对应的任务序号下;

2、  抽取 ods 库中表 coupon_info 最新分区数据，并结合 dim_coupon_info 最新分区现有的数据，根据 coupon_id 合并数据到 dwd 库中 dim_coupon_info的分区表(合并是指对 dwd 层数据进行插入或修改,需修改的数据以 coupon_id为合并字段,根据 modified_time 排序取最新的一条),分区字段为 etl_date且值与 ods 库的相对应表该值相等，并添加 dwd_insert_user、dwd_insert_time 、 dwd_modify_user 、 dwd_modify_time 四列，其dwd_insert_user、dwd_modify_user 均填写“user1”。若该条记录第一次进入数仓 dwd 层则dwd_insert_time、dwd_modify_time 均存当前操作时间并进行数据类型转换。若该数据在进入 dwd 层时发生了合并修改，则dwd_insert_time 时间不变，dwd_modify_time 存当前操作时间，其余列存最新的值。使用 hive cli 执行 show partitions dwd.dim_coupon_info命令，将结果截图粘贴至客户端桌面[Release\模块 D提交结果.docx]中对应的任务序号下:

3、  抽取ods 库中表 product_info 最新分区的数据，并结合 dim_product_info最新分区现有的数据，根据 product_core 合并数据到 dwd 库中dim_product_info 的分区表(合并是指对 dwd 层数据进行插入或修改，需修改的数据以 product_core 为合并字段，根据 modified_time 排序取最新的一条)，分区字段为 etl_date 且值与 ods 库的相对应表该值相等，并添加dwd_insert_user、dwd_insert_time、 dwd_modify_user、dwd_modify_time四列,其中 dwd_insert_user、dwd_modify_user 均填写“userl”。若该条记录第一次进入数仓 dwd层则 dwd_insert_time、dwd_modify_time 均存当前操作时间，并进行数据类型转换。若该数据在进入 dwd 层时发生了合并修改，则 dwd_insert_time 时间不变，dwd_modify_time 存当前操作时间，其余列存最新的值。 使用 hive cli 执 行 show partitions dwd.dim_product_info 命令，将结果截图粘贴至客户端桌面[Release\模块D 提交结果.docx]中对应的任务序号下;

4、  抽取 ods 库中表 order_master 最新分区的数据，并结合 HBase中order_master_offline 表中的数据合并抽取到 dwd 库中 fact_order_master的分区表，分区字段为 etl_date 且值与 ods 库的相对应表该值相等，并添加 dwd_insert_user、dwd_insert_time、dwd_modify_user,dwd_modify_time四列，其中dwd_insert_user、 dwd_modify_user 均填写“userl”dwd_insert_time、dwd_modify_time 均填写当前操作时间(年月日必须是今天，时分秒只需在比赛时间范围内即可) ，抽取 HBase 中的数据时，只抽取2022年10月01日的数据 (以rowkey 为准)，并进行数据类型转换。使用hive cli查询modified_time 为 2022年10月01日当天的数据，查询字段为order_id、order_sn、shipping_user、create_time、 shipping_time,并按照 order_id 进行升序排序，将结果截图复制粘贴至客户端桌面[Release\模块 D 提交结果.docx] 中对应的任务序号下;

ods:order_master_offline 数据结构如下:

5、  抽取 ods 库中表 order_detail 表最新分区的数据，并结合 HBase 中order_detail_offline 表中的数据合并抽取到 dwd 库中 fact_order_detail的分区表，分区字段为 etl_date 且值与 ods 库的相对应表该值相等，并添加 dwd_insert_user,dwd_insert_time,dwd_modify_user,dwd_modify_time四列，其中 dwd_insert_user、 dwd_modify_user 均填写“userl”dwd_insert_time、dwd_modify_time 均填写当前操作时间(年月日必须是今天，时分秒只需在比赛时间范围内即可) ，抽取 HBase 中的数据时，只抽取2022 年10月01日的数据(以rowkey 为准)，并进行数据类型转换。使用hive cli 查询modified_time 为 2022年10月01日当天的数据，查询字段为 order_detail_id、order_sn、 product_name、 create_time，并按照order_detail_id 进行升序排序，将结果截图粘贴至客户端桌面[Releasel模块 D提交结果.docx] 中对应的任务序号下;

ods:order_detail_offline 数据结构如下:

6、  抽取 ods 库中表 coupon_use 最新分区的数据到 dwd 库中 fact_coupon_use的分区表，分区字段为 etl_date 且值与 ods 库的相对应表该值相等，并添 dwd_insert_user、dwd_insert_time、dwd_modify_user,dwd_modify_time四列，其中dwd_insert_user、 dwd_modify_user 均填写“userl”dwd_insert_time、dwd_modify_time 均填写当前操作时间(年月日必须是今天，时分秒只需在比赛时间范围内即可)，并进行数据类型转换。使用 hivecli 执行 show partitions dwd.fact_coupon_use 命令，将结果截图粘贴至客户端桌面[Release\模块D提交结果.docx]中对应的任务序号下;

7、  抽取 ods 库中表 customer_login_log 最新分区的数据到 dwd 库中log_customer_login 的分区表，分区字段为 etl_date 且值与ods 库的相对应表该值相等，并添加 dwd_insert_user 、 dwd_insert_time、dwd_modify_user、dwd_modify_time 四列，其中dwd_insert_userdwd_modify_user 均填写“userl”，dwd_insert_time、dwd_modify_time均填写当前操作时间(年月日必须是今天，时分秒只需在比赛时间范围内即可)，并进行数据类型转换。使用 hive cli执行 show partitions dwd.log_customer_login 命令，将结果截图粘贴至客户端桌面[Release模块 D提交结果.docx] 中对应的任务序号下;

8、  抽取 ods 库中表 order_cart 最新分区的数据到 dwd 库中 fact_order_cart的分区表，分区字段为 etl_date 且值与 ods 库的相对应表该值相等，并添加 dwd_insert_user、dwd_insert_time、dwd_modify_user、dwd_modify_time四列，其中 dwd_insert_user 、 dwd_modify_user 均填写“user1”dwd_insert_time、dwd_modify_time 均填写当前操作时间(月日必须是今天，时分秒只需在比赛时间范围内即可)，并进行数据类型转换。使用 hivecli 执行 show partitions dwd.fact_order_cart 命令，将结果截图粘贴至客户端桌面[Release\模块 D 提交结果.docx]中对应的任务序号下:

9、  抽取 ods 库中表 product_browse 最新分区的数据，并结合 HBase 中product_browse_offline 表中的数据合并抽取到 dwd 库中log_product_browse 的分区表，分区字段为 etl_date 且值与 ods 库的相对应表该值相等，并添加 dwd_insert_user 、 dwd_insert_time、dwd_modify_user 、 dwd_modify_time 四列，其中dwd_insert_user、dwd_modify_user 均填写“user1”，dwd_insert_time、 dwd_modify_time均填写当前操作时间(年月日必须是今天，时分秒只需在比赛时间范围内即可)，抽取HBase 中的数据时，只抽取 2022 年10月01日的数据(以rowkey为准)，并进行数据类型转换。使用 hive cli 查询 modified_time 为2022年10月01日当天的数据，查询字段为 log_id、product_id、order_snmodified_time，并按照 log_id 进行升序排序，将结果截图粘贴至客户端桌面[Release\模块 D 提交结果.docx]中对应的任务序号下;

ods:product_browse_offline 数据结构如下:

10、              抽取 ods 库中表 customer_level_inf 最新分区的数据到 dwd 库中dim_customer_level_inf 的分区表，分区字段为 etl_date 且值与 ods 库的相对应表该值相等，并添加 dwd_insert_user 、dwd_insert_time、dwd_modify_user、 dwd_modify_time 四列，其中dwd_insert_user、dwd_modify_user 均填写“user1”， dwd_insert_time、 dwd_modify_time均填写当前操作时间(年月日必须是今天，时分秒只需在比赛时间范围内即可)，并进行数据类型转换。使用 hive cli 执行 show partitions dwd.dim customer_level_inf 命令，将结果截图粘贴至客户端桌面[Releasel模块 D提交结果.docx] 中对应的任务序号下:

11、              抽取 ods 库中表 customer_addr 最新分区的数据到dwd 库中dim_customer_addr 的分区表,分区字段为 etl_date 且值与 ods 库的相对应表该值相等，并添加 dwd_insert_user、dwd_insert_time、dwd_modify_user、dwd_modify_time 四列，其中 dwd_insert_user、dwd_modify_user 均填写“user1”，dwd_insert_time、dwd_ modify_time 均填写当前操作时间(年月日必须是今天，时分秒只需在比赛时间范围内即可)，并进行数据类型转换。使用hive cli执行 show partitions dwd.dim_customer_addr 命令,将结果截图粘贴至客户端桌面[Release模块 D提交结果.docx] 中对应的任务序号下:

12、              dim_ customer_ addr将dwd库中dim_ customer_inf、dim_customer_ level_ inf 表 的 数 据 关 联 到dws 库国中customer_addr_level_aggr 的分区表,分区字段为 etl_date,类型为 String且值为当前比赛日的前一天日期 (分区字段格式为 yyyyMMdd) ，并添加dws_insert_ user、dws_ insert_ time、 dws_ modify_user、dws_modify_time四列，其中 dws_ insert_ user 、 dws_ modify_user 均填写“userl”dws_insert_time、dws_ modify_ time 均填写当前操作时间(年月日必须是今天，时分秒只需在比赛时间范围内即可)，并进行数据类型转换。使用 hivecli 统计最新分区中得数据总量，将结果截图粘贴至客户端桌面[Release模块 D提交结果.docx] 中对应的任务序号下。

以下为SQL解法

以下为DSL解法

任务二:离线指标计算

1、  编写 Scala 工程代码，根据 dwd 的订单表 dwd.fact_ order_master，求各省份下单时间为 2022 年的支付转化率，并将计算结果按照下述表结构写入clickhouse的 ds_result 库的 payment_cvr 表。在Linux的 clickhouse 命令行中根据 ranking 字段查询出转化率前三的省份，将 SQL 语句与执行结果截图粘贴至客户端桌面[Release\模块D提交结果.docx]中对应的任务序号下;

注:支付转化率 = 完成支付的订单数 / 已下单数

payment_cvr 表结构:

2、  编写 Scala 工程代码，根据 dwd 的 fact_order_master 表最新分区关联fact_order_detail 表，计算所有订单中各商品所有订单(若该订单存在“已退款”状态则该订单不做计算，其余情况都参与计算) 总销售金额 (购买商品单价*购买商品数量)排名,并将计算结果按照下述表结构写入 clickhouse的 ds_result 库的 sales_amount_rank 表。然后在 Linux 的 clickhouse 命令行中根据 sales_ank 升序查询前 5行，将 SQL 语句与执行结果截图粘贴至客户端桌面[Release\模块 D 提交结果.docx] 中对应的任务序号下;

sales_amount_rank 表结构;

3、  编写 Scala 工程代码，根据 dwd 的登录日志表 dwd.log_customer_login，求login_time 字段值为 2022-08-10 的最近连续三周登录的用户数，并将计算结果按照下述表结构写入 clickhouse 的 ds_result 库的 continuous_3week表。然后在Linux的 clickhouse 命令行中根据active_total 降序查询，将SQL 语句与执行结果截图粘贴至客户端桌面[Release\模块 D 提交结果.docx]中对应的任务序号下。

continuous_ 3week 表结构:

以下为SQL解法

此解中第三题使用了5种不同的方法来解（茴的五种写法）