概述
經(jīng)過前一步的ads建模之后狸眼，接下來就是數(shù)據(jù)導(dǎo)出到mysql中。
ADS層分析
建表
- 創(chuàng)建表結(jié)構(gòu)
-- 進(jìn)入數(shù)據(jù)庫
use mall;
-- 創(chuàng)建gmv表
create table ads_gmv_sum_day
(
dt varchar(200) comment '統(tǒng)計(jì)日期 ' ,
gmv_count varchar(200) comment '每日gmv訂單個(gè)數(shù)',
gmv_amount varchar(200) COMMENT '每日gmv訂單總金額',
gmv_payment varchar(200) COMMENT '當(dāng)日支付金額'
) COMMENT '每日GMV統(tǒng)計(jì)';
- 執(zhí)行建表
export MYSQL_PWD=DBa2020*
mysql -uroot mall < /home/warehouse/sql/mysql_gmv_ddl.sql
腳本
- 在之前的sqoop_export.sh腳本上涵亏，加上ads_gmv_sum_day表選項(xiàng)，改成sqoop_gmv_export.sh
#!/bin/bash
db_name=mall
export_data() {
sqoop export \
--connect "jdbc:mysql://node02:3306/${db_name}?useUnicode=true&characterEncoding=utf-8" \
--username root \
--password DBa2020* \
--table $1 \
--num-mappers 1 \
--export-dir /warehouse/$db_name/ads/$1 \
--input-fields-terminated-by "\t" \
--update-key "tm_id,category1_id,stat_mn,stat_date" \
--update-mode allowinsert \
--input-null-string '\\N' \
--input-null-non-string '\\N'
}
case $1 in
"ads_sale_tm_category1_stat_mn")
export_data "ads_sale_tm_category1_stat_mn"
;;
"ads_gmv_sum_day")
export_data "ads_gmv_sum_day"
;;
"all")
export_data "ads_sale_tm_category1_stat_mn"
;;
esac
- 執(zhí)行腳本
mysql> select * from ads_gmv_sum_day;
+------------+-----------+------------+-------------+
| dt | gmv_count | gmv_amount | gmv_payment |
+------------+-----------+------------+-------------+
| 2021-03-24 | 300 | 160700 | 80931 |
+------------+-----------+------------+-------------+
