一、准备数据

导入SQL如果报错了，设置一个全局变量：set global log_bin_trust_function_creators=TRUE;
导入SQL语句：

通过存储过程（类似于java代码中的方法），生成数据：

查看用户表信息中大约有几千条数据即可，不要太多，也不要太少。

二、分析一下项目

1、可以通过ER图，查看各个表关系（在有外键关联的情况下）
2、在公司中，一般都会数据库设计详细的文档。
分析的指标：
2022-04-21日当天，整个外卖平台，在家里，在学校，在公司的一个下单情况。
2022-04-21日当天，整个外卖平台，凌晨，早上，上午，中午，下午，晚上各个时间段的一个下单量。
想分析上面的指标，需要哪几个表呢？
us_order 表中的下单时间，推测 是凌晨，早上，上午，中午，下午，晚上。
在哪里下单在哪些表中出现？
us_order 、 user 、 user_addr
三个表是如何关联的？
us_order 有 user_id , user_addr 也有 user_id.
整体的一个做法：
1、将mysql中的数据，拉去到hive表中的ods层。（ods层就是原始数据层，最接近原始数据的层级）
2、dim层
用户信息表 ， 用户的地址表
3、dw层
制作一个订单主题的宽表（将几个表中的字段都放在一个表中，字段很多）
4、dm层
统计出来的各种指标(数据集市)
5、将dm层的数据导出到mysql中即可。

三、实战

以上分析出各种层级，落地就是数据库(Hive中创建)。

创建四个数据库：

create database ods_el;
create database dim_el;
create database dw_el;
create database dm_el;

接着创建ods层中的表：us_order , order_delivery

在ods_el数据库中，常见us_order

CREATE TABLE `us_order` (
  `order_id` bigint,
  `order_no` string,
  `order_date` string,
  `user_id` bigint,
  `user_name` string,
  `order_money` double,
  `order_type` int ,
  `order_status` int ,
  `pay_status` int,
  `pay_type` int ,
  `order_source` string,
  `update_time` string
)
partitioned by (dt string)
row format delimited
fields terminated by ','
lines terminated by '\n'
stored as textfile
;

CREATE TABLE if not exists ods_el.order_delivery (
 `order_id` bigint,
 `order_no` string,
 `consignee` string,
 `area_id` bigint,
 `area_name` string,
 `address` string,
 `mobile` bigint,
 `phone` string,
 `coupon_id` bigint,
 `coupon_money` double,
 `carriage_money` double,
 `create_time` string,
 `update_time` string,
 `addr_id` bigint
)
partitioned by(dt string)
row format delimited
fields terminated by ','
lines terminated by '\n'
stored as textfile;

在维度层中创建 user 相关的表user, user_addr ：

use dim_el;

CREATE TABLE if not exists dim_el.`user` (
 `user_id` bigint,
 `user_name` string,
 `user_gender` tinyint,
 `user_birthday` string,
 `user_age` int,
 `constellation` string,
 `province` string,
 `city` string,
 `city_level` tinyint,
 `e_mail` string,
 `op_mail` string,
 `mobile` bigint,
 `num_seg_mobile` int,
 `op_Mobile` string,
 `register_time` string,
 `login_ip` string,
 `login_source` string,
 `request_user` string,
 `total_score` double,
 `used_score` double,
 `is_blacklist` tinyint,
 `is_married` tinyint,
 `education` string,
 `monthly_income` double,
 `profession` string,
 `create_date` string
)row format delimited
fields terminated by ','
lines terminated by '\n'
stored as textfile;

CREATE TABLE if not exists dim_el.user_addr (
 `user_id` bigint,
 `order_addr` string,
 `user_order_flag` tinyint,
 `addr_id` bigint,
 `arear_id` int
)
row format delimited
fields terminated by ','
lines terminated by '\n'
stored as textfile;

通过Sqoop工具，将mysql中的数据导入到hive中。

普通表：
新建hive表，会在hdfs上，生成一个文件地址，我向这个地址传递文件，hive表中就会有数据了。

通过表名的方式导入数据：
sqoop import --connect jdbc:mysql://bigdata01:3306/qf_el \
--username root --password 123456 \
--table user \
--target-dir /user/hive/warehouse/dim_el.db/user \
--delete-target-dir \
-m 1

导入数据后，查询一下。有数据：

select * from `user` limit 10;

导入数据到hive表中，可以使用另一种形式：

sqoop import --connect jdbc:mysql://192.168.32.100:3306/qf_el \
--username root \
--password 123456 \
--table user \
--hive-import \
--hive-overwrite \
--hive-table "user" \
--hive-database dim_el \
-m 1

继续导入user_addr表：
如果之前建好了表，也可以执行如下的命令：

sqoop import \
--connect jdbc:mysql://bigdata01:3306/qf_el \
--username root \
--password 123456 \
--query "SELECT * FROM qf_el.user_addr where 1=1 and \$CONDITIONS" \
--fields-terminated-by ',' \
--target-dir /user/hive/warehouse/dim_el.db/user_addr \
--delete-target-dir \
--m 1

如果之前没有创建user_addr这个表的话：可以使用mySQL 导入 hive的方法：

sqoop import --connect jdbc:mysql://192.168.32.100:3306/qf_el \
--username root \
--password 123456 \
--table user_addr \
--hive-import \
--hive-overwrite \
--hive-table "user_addr" \
--hive-database dim_el \
-m 1

如果之前已经创建了表结构，没有删除，那么执行以上命令不会报错，但是查询出来的结果为null。
如果碰到一下问题的解决办法：

在jdk路径下 jre/lib/security/java.policy ，添加：

permission javax.management.MBeanTrustPermission "register";

切换ods_el，导入us_order 以及 order_delivery 这两个表的数据：

sqoop import \
--connect jdbc:mysql://bigdata01:3306/qf_el \
--username root \
--password 123456 \
--query "SELECT * FROM qf_el.us_order uo WHERE DATE(uo.order_date) =
'2022-04-21' and \$CONDITIONS" \
--fields-terminated-by ',' \
--target-dir /user/hive/warehouse/ods_el.db/us_order/dt=2022-04-21 \
--delete-target-dir \
-m 1

查询发现没有数据，需要给us_order 表添加分区：

 alter table us_order add partition(dt='2022-04-21');

如果你想分享三天的数据，需要导入其他两天的数据。

update order_delivery
 set create_time='2022-04-21 11:59:59';

因为我们的us_order 中的数据是4月21日的数据，但是order_delivery 中，没有对应的日期，所以修改一下，让其有对应的数据。
抽取order_delivery 中的数据到 hive中

sqoop import \
--connect jdbc:mysql://bigdata01:3306/qf_el \
--username root \
--password 123456 \
--query "SELECT * FROM qf_el.order_delivery uo WHERE DATE(uo.create_time) =
'2022-04-21' and \$CONDITIONS" \
--fields-terminated-by ',' \
--target-dir /user/hive/warehouse/ods_el.db/order_delivery/dt=2022-04-21 \
--delete-target-dir \
-m 1

alter table order_delivery add partition(dt='2022-04-21');

编写DW层：

在 dw_el 数据库中。
创建一个关于order订单的宽表。
创建这个表：
这个表中的字段一定要跟我们分析的指标靠近，略有富裕。

create table order_wide_detail(
  `order_id` bigint ,
  `order_no` string,
  `order_date` string,
  `user_id` bigint ,
  `user_name` string,
  `order_money` double,
  `order_type` int ,
  `order_status` int ,
  `pay_status` int ,
  `pay_type` int ,
  `order_source` string,
  `update_time` string,
  `user_gender` tinyint,
  `user_birthday` string,
  `user_age` int ,
  `constellation` string,
  `province` string,
  `city` string,
  `city_level` tinyint ,
  `user_order_flag` tinyint
)
partitioned by(dt string)
row format delimited
fields terminated by ',';

向表中插入数据：

select * from 
   ods_el.us_order uo
   left join ods_el.order_delivery od
   on uo.order_no = od.order_no
   left join dim_el.user_addr ua
   on od.addr_id = ua.addr_id
   left join dim_el.`user` uu
   on uo.user_id = uu.user_id
   where uo.dt='2022-04-21';

将查询的数据导入到宽表中：

insert into order_wide_detail partition(dt='2022-04-21')
select 
uo.`order_id`  ,
  uo.`order_no` ,
  uo.`order_date` ,
  uo.`user_id`  ,
  uo.`user_name` ,
  uo.`order_money` ,
  uo.`order_type`  ,
  uo.`order_status`  ,
  uo.`pay_status`  ,
  uo.`pay_type`  ,
  uo.`order_source` ,
  uo.`update_time` ,
  uu.`user_gender` ,
  uu.`user_birthday` ,
  uu.`user_age`  ,
  uu.`constellation` ,
  uu.`province` ,
  uu.`city` ,
  uu.`city_level`  ,
  ua.`user_order_flag` 
from 
ods_el.us_order uo
   left join ods_el.order_delivery od
   on uo.order_no = od.order_no
   left join dim_el.user_addr ua
   on od.addr_id = ua.addr_id
   left join dim_el.`user` uu
   on uo.user_id = uu.user_id
   where uo.dt='2022-04-21';

宽表中有数据了，这个表就是我们进行分析的依据。

DM层—数据集市—就是各种统计的指标

1、统计2022-04-21 当天，在学校，公司，以及家里的下单量，以及凌晨，上午，中午，下午，晚上的下单量。
创建一个外部表：

create external table order_hour_locat_dist(
   `date` string,
   school_orders int,
   company_orders int,
   home_orders int,
   orders_0_5 int,
   orders_6_12 int,
   orders_13_15 int,
   orders_16_20 int,
   orders_21_24 int
)
partitioned by(dt string)
row format delimited fields terminated by ',';

分析数据：

insert into dm_el.order_hour_locat_dist partition(dt='2022-04-21')
select 
 to_date(order_date) `date`,
 sum(case when user_order_flag=1 then 1 else 0 end) school_orders,
 sum(case when user_order_flag=2 then 1 else 0 end) company_orders,
 sum(case when user_order_flag=3 then 1 else 0 end) home_orders,
 sum(case when hour(order_date) between 0 and 5 then 1 else 0 end) orders_0_5,
 sum(case when hour(order_date) between 6 and 12 then 1 else 0 end) orders_6_12,
 sum(case when hour(order_date) between 13 and 15 then 1 else 0 end) orders_13_15,
 sum(case when hour(order_date) between 16 and 20 then 1 else 0 end) orders_16_20,
 sum(case when hour(order_date) between 21 and 23 then 1 else 0 end) orders_21_24

from 
dw_el.order_wide_detail owd
where to_date(order_date) = '2022-04-21'
group by to_date(order_date);

查询表结果：

select * from order_hour_locat_dist;

分析结果导出：

在mysql中创建一个表，用于接收导出的数据：

CREATE TABLE `order_hour_location_dist` (
  `date` varchar(255) DEFAULT NULL,
  `school_orders` int DEFAULT NULL,
  `company_orders` int DEFAULT NULL,
  `home_orders` int DEFAULT NULL,
  `orders_0_5` int DEFAULT NULL,
  `orders_6_12` int DEFAULT NULL,
  `orders_13_15` int DEFAULT NULL,
  `orders_16_20` int DEFAULT NULL,
  `orders_21_24` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

使用sqoop导出结果：

sqoop export \
--connect jdbc:mysql://bigdata01:3306/el_report \
--username root \
--password 123456 \
--table order_hour_location_dist \
--fields-terminated-by ',' \
--export-dir /user/hive/warehouse/dm_el.db/order_hour_locat_dist/dt=2022-04-21 \
-m 1