MySQL(Data)->Hive (Analyze&Statistics)->MySQL

目标

将MySQL的数据拿到Hive进行分析统计，将统计结果返回到MySQL。

分析：
1) 在hive中创建一个emp_etl对应的表
2) 使用sqoop将mysql中的emp_etl表导入到hive表中
3) 在hive中进行统计分析(每个部门多少人)，然后结果写入到hive结果表中
4) 将hive结果表通过sqoop导出到mysql表中
5)shell封装整个过程，通过调度工具定时调度

一、MySQL原始数据

mysql> select * from emp_etl;
+-------+--------+-----------+------+------------+-------+------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal   | comm | deptno |
+-------+--------+-----------+------+------------+-------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |   800 | NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-2-20  |  1600 |  300 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-2-22  |  1250 |  500 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-4-2   |  2975 | NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-9-28  |  1250 | 1400 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-5-1   |  2850 | NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-6-9   |  2450 | NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-4-19  |  3000 | NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 |  5000 | NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-9-8   |  1500 |    0 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-5-23  |  1100 | NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-3  |   950 | NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-3  |  3000 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-1-23  |  1300 | NULL |     10 |
|  8888 | HIVE   | PROGRAM   | 7839 | 1988-1-23  | 10300 | NULL |   10 |
+-------+--------+-----------+------+------------+-------+------+--------+

二、Hive中创建对应的表并导入数据
1、创建表

hive (default)> create table emp_analy(
               empno int, ename string, job string, mgr int, hiredate string, sal double, comm double, deptno int
               )row format delimited fields terminated by '\t';

2、数据导入到Hiv 的emp_analy表

sqoop import \
--connect jdbc:mysql://hadoop001:3306/sqoop \
--username root \
--password 123456 \
--table emp_etl \
-m 1 \
--hive-import \
--hive-table  emp_analy \
--fields-terminated-by '\t' 

三、分析统计需要的结果（每个部门总人数）
1、创建存储统计结果的表

hive (default)> create table emp_result(deptno int ,nums int) row format delimited fields terminated by '\t';

2、统计结果数据插入到emp_result表

hive (default)> insert overwrite table emp_result select deptno,count(*) as nums from emp_analy group by deptno;

四、结果数据导出到MySQL中
1、MySQL中创建接收结果数据的表

mysql> create table emp_result_end(deptno int ,nums int);

2、从Hive将结果数据导出到MySQL

sqoop export \
--connect jdbc:mysql://hadoop001:3306/sqoop \
--username root \
--password 123456 \
--table emp_result_end \
--export-dir /user/hive/warehouse/emp_result \
-m 1 \
--verbose \
--fields-terminated-by '\t' 

3、MySQL中查看统计结果

mysql> select * from emp_result_end;
+--------+------+
| deptno | nums |
+--------+------+
|     10 |    4 |
|     20 |    5 |
|     30 |    6 |
+--------+------+
3 rows in set (0.00 sec)

五、shell封装
1、自动创建Hive表
vi auto_create_hive_table.sh

#! /bin/bash
hive -e "
use default;
create table if not exists emp_analy (
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)row format delimited fields terminated by '\t';"

2、原始数据到Hive表
vi import_data_hive.sh

#! /bin/bash
sqoop import \
--connect jdbc:mysql://hadoop001:3306/sqoop \
--username root \
--password 123456 \
--table emp_etl \
-m 1 \
--hive-import \
--hive-table  emp_analy \
--fields-terminated-by '\t'

3、Hive创建结果表
vi hive_create_result_table.sh

#！/bin/bash
hive -e "
use default;
create table emp_result(
deptno int ,
nums int) row format delimited fields terminated by '\t';
"

4、统计结果插入到Hive结果表
vi insert_data_emp_result.sh

#! /bin/bash
hive -e "
use default;
insert overwrite table emp_result 
select deptno,count(*) as nums from emp_analy group by deptno;
"

5、MySQL创建接收统计数据的表
vi auto_create_mysql_table.sh

#!/bin/bash
mysql -uroot  -p123456 sqoop -e "
create table if not exists emp_result_end(deptno int ,nums int); 
truncate table emp_result_end;
"

5、统计数据导出到MySQL
vi export_data_mysql.sh

sqoop export \
--connect jdbc:mysql://hadoop001:3306/sqoop \
--username root \
--password 123456 \
--table emp_result_end \
--export-dir /user/hive/warehouse/emp_result \
-m 1 \
--verbose \
--fields-terminated-by '\t' 

6、创建日志文件

touch etl.log

7、所有脚本封装到一个脚本中统一运行
vi etl.sh

#! /bin/bash
echo 'start -----------------' >>/opt/etl/etl.log
echo `date +%Y/%m/%d-%H:%M:%H` >>/opt/etl/etl.log
sh /opt/etl/auto_create_hive_table.sh
sh /opt/etl/import_data_hive.sh
sh /opt/etl/hive_create_result_table.sh
sh /opt/etl/insert_data_emp_result.sh
sh /opt/etl/auto_create_mysql_table.sh
sh /opt/etl/export_data_mysql.sh
echo 'end ==================' >>/opt/etl/etl.log
echo `date +%Y/%m/%d-%H:%M:%H`>>/opt/etl/etl.log

只需要执行etl.sh脚本统计结果就会导出到MySQL了，由于脚本设置不严谨，所以执行脚本前需删除已存在的表。当脚本完善后可以通过调度器进行调度。