說明：此卷為2022年全國職業(yè)院校技能大賽河南省省賽離線模塊。此卷難度、繁瑣度較大，需處理近20張表，其中包括三張Hbase表導(dǎo)入導(dǎo)出（最簡(jiǎn)單的是指標(biāo)部分，往年都是指標(biāo)最難）此文章包括SQL及DSL語法，其包括SQL全部解題，DSL語法部分解題，未用DSL語法主要為Hbase導(dǎo)入導(dǎo)出及指標(biāo)，DSL語法中有for循環(huán)解題（較為簡(jiǎn)潔）。Spark版本為3.1.1，Hadoop版本為3.1.3，Scala版本為2.12，Hive版本為3.1.2。數(shù)據(jù)暫不共享

任務(wù)一:離線數(shù)據(jù)采集

編寫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 的數(shù)據(jù)增量抽取到 Hive 的 ods 庫中對(duì)應(yīng)表 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 的增量數(shù)據(jù)進(jìn)入 Hive 的 ods 庫中表order_master。根據(jù) ods.order_master 表中 modified_time 作為增量字段只將新增的數(shù)據(jù)抽入，字段名稱、類型不變，同時(shí)添加靜態(tài)分區(qū)，分區(qū)字段為etl_date，類型為 String，且值為當(dāng)前比賽日的前一天日期 (分區(qū)字段格式為 yyyyMMdd)。使用 hive cli 執(zhí)行 show partitions ods.order_master命令，將執(zhí)行結(jié)果截圖粘貼至客戶端桌面[Release\模塊 B 提交結(jié)果.docx]中對(duì)應(yīng)的任務(wù)序號(hào)下;

2、抽取 ds_db01 庫中 order_detail 的增量數(shù)據(jù)進(jìn)入 Hive 的 ods 庫中表order_detail。根據(jù) ods.order_detail表中 modified_time 作為增量字段只將新增的數(shù)據(jù)抽入，字段名稱、類型不變，同時(shí)添加靜態(tài)分區(qū)，分區(qū)字段為etl_date，類型為 String，且值為當(dāng)前比賽日的前一天日期 (分區(qū)字段格式為 yyyyMMdd)。使用 hive cli 執(zhí)行 show partitions ods.order_detail命令，將執(zhí)行結(jié)果截圖粘貼至客戶端桌面[Release\模塊 B 提交結(jié)果.docx]中對(duì)應(yīng)的任務(wù)序號(hào)下:

3、抽取 ds_db0l庫中 coupon_info的增量數(shù)據(jù)進(jìn)入 Hive 的 ods 庫中表coupon_info，根據(jù) ods.coupon_info 表中 modified_time 作為增量字段，只將新增的數(shù)據(jù)抽入，字段名稱、類型不變，同時(shí)添加靜態(tài)分區(qū)，分區(qū)字段為etl_date，類型為String，且值為當(dāng)前比賽日的前一天日期 (分區(qū)字段格式為 yyyyMMdd)。使用 hive cli 執(zhí)行 show partitions ods.coupon_info命令，將執(zhí)行結(jié)果截圖粘貼至客戶端桌面[Release 模塊 B 提交結(jié)果.docx]中對(duì)應(yīng)的任務(wù)序號(hào)下:

?

4、抽取 ds_db01 庫中 coupon_use 的增量數(shù)據(jù)進(jìn)入 Hive 的 ods 庫中表coupon_use，增量字段取 ods.coupon_use 表中 get_time、used_timepay_time 中的最大者，只將新增的數(shù)據(jù)抽入，字段名稱、類型不變，同時(shí)添加靜態(tài)分區(qū)，分區(qū)字段為 etl_date，類型為String，且值為當(dāng)前比賽日的前一天日期(分區(qū)字段格式為 yyyyMMdd) 。使用 Hive cli 查詢最新分區(qū)數(shù)據(jù)總條數(shù)，將執(zhí)行結(jié)果截圖粘貼至客戶端桌面[Release 模塊 B 提交結(jié)果.docx] 中對(duì)應(yīng)的任務(wù)序號(hào)下:

?

5、抽取 ds_db01庫中 product_browse 的增量數(shù)據(jù)進(jìn)入 Hive 的 ods 庫中表product_browse，根據(jù)ods.product_browse 表中 modified_time 作為增量字段，只將新增的數(shù)據(jù)抽入，字段名稱、類型不變，同時(shí)添加靜態(tài)分區(qū)，分區(qū)字段為etl_date，類型為String，且值為當(dāng)前比賽日的前一天日期 (分區(qū)字段格式為 yyyyMMdd)。使用 hive cli執(zhí)行 show partitionsods.product_browse 命令，將執(zhí)行結(jié)果截圖粘貼至客戶端桌面[Release\模塊 B 提交結(jié)果.docx]中對(duì)應(yīng)的任務(wù)序號(hào)下。

?

6、抽取 ds_db0l 庫中 product_info 的增量數(shù)據(jù)進(jìn)入 Hive 的 ods 庫中表product_info，根據(jù)ods.product_info 表中 modified_time 作為增量字段只將新增的數(shù)據(jù)抽入，字段名稱、類型不變，同時(shí)添加靜態(tài)分區(qū)，分區(qū)字段為 etl_date，類型為 String，且值為當(dāng)前比賽日的前一天日期 (分區(qū)字段格式為 yyyyMMdd)。使用 hive cli 執(zhí)行 show partitions ods.product_info命令，將執(zhí)行結(jié)果截圖粘貼至客戶端桌面[Release\模塊 B 提交結(jié)果.docx]中對(duì)應(yīng)的任務(wù)序號(hào)下;

?

7、抽取 ds_db01 庫中 customer_inf 的增量數(shù)據(jù)進(jìn)入 Hive 的 ods 庫中表customer_inf，根據(jù) ods.customer_inf 表中 modified_time 作為增量字段只將新增的數(shù)據(jù)抽入，字段名稱、類型不變，同時(shí)添加靜態(tài)分區(qū)，分區(qū)字段為 etl_date，類型為 String，且值為當(dāng)前比賽日的前一天日期 (分區(qū)字段格式為 yyyyMMdd)。使用 hive cli 執(zhí)行 show partitions ods.customer_inf命令，將執(zhí)行結(jié)果截圖粘貼至客戶端桌面[Release\模塊 B 提交結(jié)果.docx]中對(duì)應(yīng)的任務(wù)序號(hào)下:

?

8、抽取 ds_db01庫中 customer_login_log 的增量數(shù)據(jù)進(jìn)入 Hive 的ods 庫中表customer_login_log，根據(jù)ods.customer_login_log 表中l(wèi)ogin_time 作為增量字段，只將新增的數(shù)據(jù)抽入，字段名稱、類型不變，同時(shí)添加靜態(tài)分區(qū)分區(qū)字段為 etl_date，類型為 String，且值為當(dāng)前比賽日的前一天日期(分區(qū)字段格式為yyyyMMdd)。使用 hive cli 執(zhí)行 show partitions ods.customer_login_log 命令，將執(zhí)行結(jié)果截圖粘貼至客戶端桌面[Release模塊 B 提交結(jié)果.docx] 中對(duì)應(yīng)的任務(wù)序號(hào)下:

?

9、抽取 ds_db0l 庫中 order_cart 的增量數(shù)據(jù)進(jìn)入Hive 的 ods 庫中表order_cart，根據(jù)ods.order_cart 表中 modified_time 作為增量字段，只將新增的數(shù)據(jù)抽入，字段名稱、類型不變，同時(shí)添加靜態(tài)分區(qū)，分區(qū)字段為etl_date，類型為 String，且值為當(dāng)前比賽日的前一天日期 (分區(qū)字段格式為yyyyMMdd)。使用 hive cli 執(zhí)行 show partitions ods.order_cart 命令將執(zhí)行結(jié)果截圖粘貼至客戶端桌面[Release\模塊 B 提交結(jié)果.docx]中對(duì)應(yīng)的任務(wù)序號(hào)下;

?

10、抽取 ds_db0l 庫中 customer_addr 的增量數(shù)據(jù)進(jìn)入 Hive 的 ods 庫中表customer_addr，根據(jù)ods.customer_addr 表中 modified_time 作為增量字段，只將新增的數(shù)據(jù)抽入，字段名稱、類型不變，同時(shí)添加靜態(tài)分區(qū)，分區(qū)字段為etl_date，類型為 String，且值為當(dāng)前比賽日的前一天日期 (分區(qū)字段格式為 yyyyMMdd)。使用 hive cli 執(zhí)行 show partitions ods.customer_addr 命令，將執(zhí)行結(jié)果截圖粘貼至客戶端桌面[Release\模塊 B提交結(jié)果.docx]中對(duì)應(yīng)的任務(wù)序號(hào)下;

?

11、抽取 ds_db01庫中 customer_level_inf 的增量數(shù)據(jù)進(jìn)入 Hive 的 ods 庫中國表 customer_level_inf ， 根 據(jù) ods.customer_level_inf 表中modified_time 作為增量字段，只將新增的數(shù)據(jù)抽入，字段名稱、類型不變，同時(shí)添加靜態(tài)分區(qū)，分區(qū)字段為 etl_date，類型為 String，且值為當(dāng)前比賽日的前一天日期(分區(qū)字段格式為 yyyyMMdd)。使用 hive cli 執(zhí)行 showpartitions ods.customer_level_inf 命令，將執(zhí)行結(jié)果截圖粘貼至客戶端桌面[Release\模塊 B 提交結(jié)果.docx] 中對(duì)應(yīng)的任務(wù)序號(hào)下；

以下為SQL解法

以下為DSL解法

任務(wù)二:離線數(shù)據(jù)清洗（此卷最難部分）

編寫 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庫中對(duì)應(yīng)表中。表中有涉及到 timestamp 類型的，均要求按照yyyy-MM-dd HH:mm:ss，不記錄毫秒數(shù)，若原數(shù)據(jù)中只有年月日，則在時(shí)分秒的位置添加 00:00:00，添加之后使其符合 yyyy-MM-dd HH:mm:ss。

1、? 抽取 ods 庫中表 customer_inf 最新分區(qū)數(shù)據(jù)，并結(jié)合 dim_customer_inf 最新分區(qū)現(xiàn)有的數(shù)據(jù)，根據(jù) customer_id 合并數(shù)據(jù)到dwd 庫中dim_customer_inf 的分區(qū)表 (合并是指對(duì) dwd 層數(shù)據(jù)進(jìn)行插入或修改，需修改的數(shù)據(jù)以customer_id 為合并字段，根據(jù) modified_time 排序取最新的一條)，分區(qū)字段為etl_date 且值與 ods 庫的相對(duì)應(yīng)表該值相等，并添加dwd_insert_user、dwd_insert_time、 dwd _modify_user、 dwd_modify_time四列,其中dwd_insert_user、dwd_modify_user 均填寫“userl”。若該條記錄第一次進(jìn)入數(shù)倉dwd 層則dwd_insert_time、dwd_modify_time 均存當(dāng)前操作時(shí)間，并進(jìn)行數(shù)據(jù)類型轉(zhuǎn)換。若該數(shù)據(jù)在進(jìn)入 dwd 層時(shí)發(fā)生了合并修改，則dwd_insert_time 時(shí)間不變，dwd_modify_time 存當(dāng)前操作時(shí)間，其余列存最新的值。使用 hive cli查詢modified_time 為 2022年10月01日當(dāng)天的數(shù)據(jù)，查詢字段為 customer_id、customer_email、modified_timedwd_insert_time、dwd_modify_time，并按照 customer_id 進(jìn)行升序排序?qū)⒔Y(jié)果截圖粘貼至客戶端桌面[Release\模塊 D提交結(jié)果.docx]中對(duì)應(yīng)的任務(wù)序號(hào)下;

?

2、? 抽取 ods 庫中表 coupon_info 最新分區(qū)數(shù)據(jù)，并結(jié)合 dim_coupon_info 最新分區(qū)現(xiàn)有的數(shù)據(jù)，根據(jù) coupon_id 合并數(shù)據(jù)到 dwd 庫中 dim_coupon_info的分區(qū)表(合并是指對(duì) dwd 層數(shù)據(jù)進(jìn)行插入或修改,需修改的數(shù)據(jù)以 coupon_id為合并字段,根據(jù) modified_time 排序取最新的一條),分區(qū)字段為 etl_date且值與 ods 庫的相對(duì)應(yīng)表該值相等，并添加 dwd_insert_user、dwd_insert_time 、 dwd_modify_user 、 dwd_modify_time 四列，其dwd_insert_user、dwd_modify_user 均填寫“user1”。若該條記錄第一次進(jìn)入數(shù)倉 dwd 層則dwd_insert_time、dwd_modify_time 均存當(dāng)前操作時(shí)間并進(jìn)行數(shù)據(jù)類型轉(zhuǎn)換。若該數(shù)據(jù)在進(jìn)入 dwd 層時(shí)發(fā)生了合并修改，則dwd_insert_time 時(shí)間不變，dwd_modify_time 存當(dāng)前操作時(shí)間，其余列存最新的值。使用 hive cli 執(zhí)行 show partitions dwd.dim_coupon_info命令，將結(jié)果截圖粘貼至客戶端桌面[Release\模塊 D提交結(jié)果.docx]中對(duì)應(yīng)的任務(wù)序號(hào)下:

?

3、? 抽取ods 庫中表 product_info 最新分區(qū)的數(shù)據(jù)，并結(jié)合 dim_product_info最新分區(qū)現(xiàn)有的數(shù)據(jù)，根據(jù) product_core 合并數(shù)據(jù)到 dwd 庫中dim_product_info 的分區(qū)表(合并是指對(duì) dwd 層數(shù)據(jù)進(jìn)行插入或修改，需修改的數(shù)據(jù)以 product_core 為合并字段，根據(jù) modified_time 排序取最新的一條)，分區(qū)字段為 etl_date 且值與 ods 庫的相對(duì)應(yīng)表該值相等，并添加dwd_insert_user、dwd_insert_time、 dwd_modify_user、dwd_modify_time四列,其中 dwd_insert_user、dwd_modify_user 均填寫“userl”。若該條記錄第一次進(jìn)入數(shù)倉 dwd層則 dwd_insert_time、dwd_modify_time 均存當(dāng)前操作時(shí)間，并進(jìn)行數(shù)據(jù)類型轉(zhuǎn)換。若該數(shù)據(jù)在進(jìn)入 dwd 層時(shí)發(fā)生了合并修改，則 dwd_insert_time 時(shí)間不變，dwd_modify_time 存當(dāng)前操作時(shí)間，其余列存最新的值。 使用 hive cli 執(zhí) 行 show partitions dwd.dim_product_info 命令，將結(jié)果截圖粘貼至客戶端桌面[Release\模塊D 提交結(jié)果.docx]中對(duì)應(yīng)的任務(wù)序號(hào)下;

?

4、? 抽取 ods 庫中表 order_master 最新分區(qū)的數(shù)據(jù)，并結(jié)合 HBase中order_master_offline 表中的數(shù)據(jù)合并抽取到 dwd 庫中 fact_order_master的分區(qū)表，分區(qū)字段為 etl_date 且值與 ods 庫的相對(duì)應(yīng)表該值相等，并添加 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 均填寫當(dāng)前操作時(shí)間(年月日必須是今天，時(shí)分秒只需在比賽時(shí)間范圍內(nèi)即可) ，抽取 HBase 中的數(shù)據(jù)時(shí)，只抽取2022年10月01日的數(shù)據(jù) (以rowkey 為準(zhǔn))，并進(jìn)行數(shù)據(jù)類型轉(zhuǎn)換。使用hive cli查詢modified_time 為 2022年10月01日當(dāng)天的數(shù)據(jù)，查詢字段為order_id、order_sn、shipping_user、create_time、 shipping_time,并按照 order_id 進(jìn)行升序排序，將結(jié)果截圖復(fù)制粘貼至客戶端桌面[Release\模塊 D 提交結(jié)果.docx] 中對(duì)應(yīng)的任務(wù)序號(hào)下;

ods:order_master_offline 數(shù)據(jù)結(jié)構(gòu)如下:

5、? 抽取 ods 庫中表 order_detail 表最新分區(qū)的數(shù)據(jù)，并結(jié)合 HBase 中order_detail_offline 表中的數(shù)據(jù)合并抽取到 dwd 庫中 fact_order_detail的分區(qū)表，分區(qū)字段為 etl_date 且值與 ods 庫的相對(duì)應(yīng)表該值相等，并添加 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 均填寫當(dāng)前操作時(shí)間(年月日必須是今天，時(shí)分秒只需在比賽時(shí)間范圍內(nèi)即可) ，抽取 HBase 中的數(shù)據(jù)時(shí)，只抽取2022 年10月01日的數(shù)據(jù)(以rowkey 為準(zhǔn))，并進(jìn)行數(shù)據(jù)類型轉(zhuǎn)換。使用hive cli 查詢modified_time 為 2022年10月01日當(dāng)天的數(shù)據(jù)，查詢字段為 order_detail_id、order_sn、 product_name、 create_time，并按照order_detail_id 進(jìn)行升序排序，將結(jié)果截圖粘貼至客戶端桌面[Releasel模塊 D提交結(jié)果.docx] 中對(duì)應(yīng)的任務(wù)序號(hào)下;

ods:order_detail_offline 數(shù)據(jù)結(jié)構(gòu)如下:

6、? 抽取 ods 庫中表 coupon_use 最新分區(qū)的數(shù)據(jù)到 dwd 庫中 fact_coupon_use的分區(qū)表，分區(qū)字段為 etl_date 且值與 ods 庫的相對(duì)應(yīng)表該值相等，并添 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 均填寫當(dāng)前操作時(shí)間(年月日必須是今天，時(shí)分秒只需在比賽時(shí)間范圍內(nèi)即可)，并進(jìn)行數(shù)據(jù)類型轉(zhuǎn)換。使用 hivecli 執(zhí)行 show partitions dwd.fact_coupon_use 命令，將結(jié)果截圖粘貼至客戶端桌面[Release\模塊D提交結(jié)果.docx]中對(duì)應(yīng)的任務(wù)序號(hào)下;

?

7、? 抽取 ods 庫中表 customer_login_log 最新分區(qū)的數(shù)據(jù)到 dwd 庫中l(wèi)og_customer_login 的分區(qū)表，分區(qū)字段為 etl_date 且值與ods 庫的相對(duì)應(yīng)表該值相等，并添加 dwd_insert_user 、 dwd_insert_time、dwd_modify_user、dwd_modify_time 四列，其中dwd_insert_userdwd_modify_user 均填寫“userl”，dwd_insert_time、dwd_modify_time均填寫當(dāng)前操作時(shí)間(年月日必須是今天，時(shí)分秒只需在比賽時(shí)間范圍內(nèi)即可)，并進(jìn)行數(shù)據(jù)類型轉(zhuǎn)換。使用 hive cli執(zhí)行 show partitions dwd.log_customer_login 命令，將結(jié)果截圖粘貼至客戶端桌面[Release模塊 D提交結(jié)果.docx] 中對(duì)應(yīng)的任務(wù)序號(hào)下;

?

8、? 抽取 ods 庫中表 order_cart 最新分區(qū)的數(shù)據(jù)到 dwd 庫中 fact_order_cart的分區(qū)表，分區(qū)字段為 etl_date 且值與 ods 庫的相對(duì)應(yīng)表該值相等，并添加 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 均填寫當(dāng)前操作時(shí)間(月日必須是今天，時(shí)分秒只需在比賽時(shí)間范圍內(nèi)即可)，并進(jìn)行數(shù)據(jù)類型轉(zhuǎn)換。使用 hivecli 執(zhí)行 show partitions dwd.fact_order_cart 命令，將結(jié)果截圖粘貼至客戶端桌面[Release\模塊 D 提交結(jié)果.docx]中對(duì)應(yīng)的任務(wù)序號(hào)下:

?

9、? 抽取 ods 庫中表 product_browse 最新分區(qū)的數(shù)據(jù)，并結(jié)合 HBase 中product_browse_offline 表中的數(shù)據(jù)合并抽取到 dwd 庫中l(wèi)og_product_browse 的分區(qū)表，分區(qū)字段為 etl_date 且值與 ods 庫的相對(duì)應(yīng)表該值相等，并添加 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均填寫當(dāng)前操作時(shí)間(年月日必須是今天，時(shí)分秒只需在比賽時(shí)間范圍內(nèi)即可)，抽取HBase 中的數(shù)據(jù)時(shí)，只抽取 2022 年10月01日的數(shù)據(jù)(以rowkey為準(zhǔn))，并進(jìn)行數(shù)據(jù)類型轉(zhuǎn)換。使用 hive cli 查詢 modified_time 為2022年10月01日當(dāng)天的數(shù)據(jù)，查詢字段為 log_id、product_id、order_snmodified_time，并按照 log_id 進(jìn)行升序排序，將結(jié)果截圖粘貼至客戶端桌面[Release\模塊 D 提交結(jié)果.docx]中對(duì)應(yīng)的任務(wù)序號(hào)下;

ods:product_browse_offline 數(shù)據(jù)結(jié)構(gòu)如下:

10、????????????? 抽取 ods 庫中表 customer_level_inf 最新分區(qū)的數(shù)據(jù)到 dwd 庫中dim_customer_level_inf 的分區(qū)表，分區(qū)字段為 etl_date 且值與 ods 庫的相對(duì)應(yīng)表該值相等，并添加 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均填寫當(dāng)前操作時(shí)間(年月日必須是今天，時(shí)分秒只需在比賽時(shí)間范圍內(nèi)即可)，并進(jìn)行數(shù)據(jù)類型轉(zhuǎn)換。使用 hive cli 執(zhí)行 show partitions dwd.dim customer_level_inf 命令，將結(jié)果截圖粘貼至客戶端桌面[Releasel模塊 D提交結(jié)果.docx] 中對(duì)應(yīng)的任務(wù)序號(hào)下:

?

11、????????????? 抽取 ods 庫中表 customer_addr 最新分區(qū)的數(shù)據(jù)到dwd 庫中dim_customer_addr 的分區(qū)表,分區(qū)字段為 etl_date 且值與 ods 庫的相對(duì)應(yīng)表該值相等，并添加 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 均填寫當(dāng)前操作時(shí)間(年月日必須是今天，時(shí)分秒只需在比賽時(shí)間范圍內(nèi)即可)，并進(jìn)行數(shù)據(jù)類型轉(zhuǎn)換。使用hive cli執(zhí)行 show partitions dwd.dim_customer_addr 命令,將結(jié)果截圖粘貼至客戶端桌面[Release模塊 D提交結(jié)果.docx] 中對(duì)應(yīng)的任務(wù)序號(hào)下:

?

12、????????????? dim_ customer_ addr將dwd庫中dim_ customer_inf、dim_customer_ level_ inf 表 的 數(shù) 據(jù) 關(guān) 聯(lián) 到dws 庫國中customer_addr_level_aggr 的分區(qū)表,分區(qū)字段為 etl_date,類型為 String且值為當(dāng)前比賽日的前一天日期 (分區(qū)字段格式為 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 均填寫當(dāng)前操作時(shí)間(年月日必須是今天，時(shí)分秒只需在比賽時(shí)間范圍內(nèi)即可)，并進(jìn)行數(shù)據(jù)類型轉(zhuǎn)換。使用 hivecli 統(tǒng)計(jì)最新分區(qū)中得數(shù)據(jù)總量，將結(jié)果截圖粘貼至客戶端桌面[Release模塊 D提交結(jié)果.docx] 中對(duì)應(yīng)的任務(wù)序號(hào)下。

以下為SQL解法

?

以下為DSL解法

任務(wù)二:離線指標(biāo)計(jì)算

1、? 編寫 Scala 工程代碼，根據(jù) dwd 的訂單表 dwd.fact_ order_master，求各省份下單時(shí)間為 2022 年的支付轉(zhuǎn)化率，并將計(jì)算結(jié)果按照下述表結(jié)構(gòu)寫入clickhouse的 ds_result 庫的 payment_cvr 表。在Linux的 clickhouse 命令行中根據(jù) ranking 字段查詢出轉(zhuǎn)化率前三的省份，將 SQL 語句與執(zhí)行結(jié)果截圖粘貼至客戶端桌面[Release\模塊D提交結(jié)果.docx]中對(duì)應(yīng)的任務(wù)序號(hào)下;

注:支付轉(zhuǎn)化率 = 完成支付的訂單數(shù) / 已下單數(shù)

payment_cvr 表結(jié)構(gòu):

2、? 編寫 Scala 工程代碼，根據(jù) dwd 的 fact_order_master 表最新分區(qū)關(guān)聯(lián)fact_order_detail 表，計(jì)算所有訂單中各商品所有訂單(若該訂單存在“已退款”狀態(tài)則該訂單不做計(jì)算，其余情況都參與計(jì)算) 總銷售金額 (購買商品單價(jià)*購買商品數(shù)量)排名,并將計(jì)算結(jié)果按照下述表結(jié)構(gòu)寫入 clickhouse的 ds_result 庫的 sales_amount_rank 表。然后在 Linux 的 clickhouse 命令行中根據(jù) sales_ank 升序查詢前 5行，將 SQL 語句與執(zhí)行結(jié)果截圖粘貼至客戶端桌面[Release\模塊 D 提交結(jié)果.docx] 中對(duì)應(yīng)的任務(wù)序號(hào)下;

sales_amount_rank 表結(jié)構(gòu);

3、? 編寫 Scala 工程代碼，根據(jù) dwd 的登錄日志表 dwd.log_customer_login，求login_time 字段值為 2022-08-10 的最近連續(xù)三周登錄的用戶數(shù)，并將計(jì)算結(jié)果按照下述表結(jié)構(gòu)寫入 clickhouse 的 ds_result 庫的 continuous_3week表。然后在Linux的 clickhouse 命令行中根據(jù)active_total 降序查詢，將SQL 語句與執(zhí)行結(jié)果截圖粘貼至客戶端桌面[Release\模塊 D 提交結(jié)果.docx]中對(duì)應(yīng)的任務(wù)序號(hào)下。

continuous_ 3week 表結(jié)構(gòu):

以下為SQL解法

此解中第三題使用了5種不同的方法來解（茴的五種寫法）