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/bashhive -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/bashsqoop 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/bashhive -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/bashhive -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/bashmysql -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/bashecho 'start -----------------' >>/opt/etl/etl.logecho `date +%Y/%m/%d-%H:%M:%H` >>/opt/etl/etl.logsh /opt/etl/auto_create_hive_table.shsh /opt/etl/import_data_hive.shsh /opt/etl/hive_create_result_table.shsh /opt/etl/insert_data_emp_result.shsh /opt/etl/auto_create_mysql_table.shsh /opt/etl/export_data_mysql.shecho 'end ==================' >>/opt/etl/etl.logecho `date +%Y/%m/%d-%H:%M:%H`>>/opt/etl/etl.log

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