概述

ADS层完成后，接下来就是进行ADS数据导出。

ODDA数据导出

ads数据导出

查找文件

[root@node01 ~]# hdfs dfs -ls /warehouse/mall/ads/ads_sale_tm_category1_stat_mn
Found 1 items
-rwxr-xr-x   2 root supergroup         50 2021-03-30 02:55 /warehouse/mall/ads/ads_sale_tm_category1_stat_mn/000000_0

//拷贝到本地
hdfs dfs -copyToLocal  /warehouse/mall/ads/ads_sale_tm_category1_stat_mn/000000_0 .

[root@node01 ~]# cat 000000_0 
\N      \N      \N      152     141     0.93    116     0.84    2021-03 2021-03-24

1. 建表

在node02上创建mysql_sale.sql：

-- 进入数据库
use mall;

-- 创建复购率表
create  table ads_sale_tm_category1_stat_mn
(   
    tm_id varchar(200) comment '品牌id ' ,
    category1_id varchar(200) comment '1级品类id ',
    category1_name varchar(200) comment '1级品类名称 ',
    buycount   varchar(200) comment  '购买人数',
    buy_twice_last varchar(200) comment '两次以上购买人数',
    buy_twice_last_ratio varchar(200) comment  '单次复购率', 
    buy_3times_last   varchar(200) comment   '三次以上购买人数',
    buy_3times_last_ratio varchar(200)  comment  '多次复购率' ,
    stat_mn varchar(200) comment '统计月份',
    stat_date varchar(200) comment '统计日期' 
)  

执行建表语句

export MYSQL_PWD=DBa2020*
mysql -uroot mall < /home/warehouse/sql/mysql_sale.sql

验证

[root@node02 sql]# mysql
mysql> use mall;
mysql> show tables;
+-------------------------------+
| Tables_in_mall                |
+-------------------------------+
| ads_sale_tm_category1_stat_mn |
| base_category1                |
| base_category2                |
| base_category3                |
| order_detail                  |
| order_info                    |
| payment_info                  |
| sku_info                      |
| user_info                     |
+-------------------------------+
9 rows in set (0.00 sec)

2.脚本

node03创建导出脚本sqoop_export.sh:

mkdir /home/warehouse/shell -p
vi sqoop_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"
;;
   "all")
     export_data "ads_sale_tm_category1_stat_mn"
;;
esac

执行脚本：

[root@node03 shell]# ./sqoop_export.sh all

验证

mysql> select * from ads_sale_tm_category1_stat_mn;
+-------+--------------+----------------+----------+----------------+----------------------+-----------------+-----------------------+---------+------------+
| tm_id | category1_id | category1_name | buycount | buy_twice_last | buy_twice_last_ratio | buy_3times_last | buy_3times_last_ratio | stat_mn | stat_date  |
+-------+--------------+----------------+----------+----------------+----------------------+-----------------+-----------------------+---------+------------+
| NULL  | NULL         | NULL           | 152      | 141            | 0.93                 | 116             | 0.84                  | 2021-03 | 2021-03-24 |
+-------+--------------+----------------+----------+----------------+----------------------+-----------------+-----------------------+---------+------------+
1 row in set (0.00 sec)