SQOOP安装&RDBMS导入HDFS&RDBMS导入HIVE&HDFS导入RDBMS&HIVE导入RDBMS&SQOOP的ETL案例&在SHELL中操作MYSQL

首先抛出两个场景

数据数据在RDBMS中，你想使用Hive进行处理，怎么做
使用Hive统计分析好了，数据还在Hive中，如何导到RDBMS中

Sqoop安装

下载并解压

下载地址：http://archive.cloudera.com/cdh5/cdh/5/sqoop-1.4.6-cdh5.16.2.tar.gz

上传安装包sqoop-1.4.6-cdh5.16.2.tar.gz到主机中

解压sqoop安装包到指定目录，如：$ tar -zxf sqoop-1.4.6-cdh5.16.2.tar.gz -C /opt/module/

修改配置文件

重命名配置文件

$ mv sqoop-env-template.sh sqoop-env.sh

修改配置文件

export HADOOP_COMMON_HOME=/opt/module/hadoop
export HADOOP_MAPRED_HOME=/opt/module/hadoop
export HIVE_HOME=/opt/module/hive
export ZOOKEEPER_HOME=/opt/module/zookeeper
export ZOOCFGDIR=/opt/module/zookeeper
export HBASE_HOME=/opt/module/hbase

拷贝JDBC驱动

$ cp mysql-connector-java-5.1.27-bin.jar /opt/module/sqoop/lib/

验证Sqoop

$ bin/sqoop help

出现一些Warning警告（警告信息已省略），并伴随着帮助命令的输出：

Available commands:
  codegen            Generate code to interact with database records
  create-hive-table  Import a table definition into Hive
  eval               Evaluate a SQL statement and display the results
  export             Export an HDFS directory to a database table
  help               List available commands
  import             Import a table from a database to HDFS
  import-all-tables  Import tables from a database to HDFS
  import-mainframe   Import datasets from a mainframe server to HDFS
  job                Work with saved jobs
  list-databases     List available databases on a server
  list-tables        List available tables in a database
  merge              Merge results of incremental imports
  metastore          Run a standalone Sqoop metastore
  version            Display version information

测试Sqoop是否能够成功连接数据库
sqoop \
list-databases \
--connect jdbc:mysql://aliyun:3306/ \
--username root \
--password root
数据库用到的参数:
- “\“：代表换行
- “connect”：连接数据库
- username：用户
- password：密码
显示所有的数据库列表
information_schema
leyou1
metastore
mypython
mysql
performance_schema
sys
travel
tunan

RDBMS到HDFS

MySQL准备表和数据

确定mysql服务开启正常

在mysql中创建库表

create database company;
create table company.staff(
    id int(4) primary key not null auto_increment, 
    name varchar(255), 
    sex varchar(255)
);

插入数据

insert into company.staff(name, sex) values('Thomas', 'Male');
insert into company.staff(name, sex) values('Catalina', 'FeMale');

查看数据

select * from staff;

+----+----------+--------+
| id | name     | sex    |
+----+----------+--------+
|  1 | Thomas   | Male   |
|  2 | Catalina | FeMale |
+----+----------+--------+

将MySQL数据导入到HDFS

全部导入

sqoop import \
--connect jdbc:mysql://aliyun:3306/company \
--username root \
--password root \
--table staff \
--target-dir /user/company \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t"

全部导入用到的参数:

table：指定被导入的表名
target-dir：指定导入路径
delete-target-dir：如果目标目录存在就删除它
num-mappers：mapper的个数
fields-terminated-by：指定字段分隔符

查询导入(query)

query参数就可以让用户随意写sql语句来查询了。query和table参数是互斥的。

sqoop import \
--connect jdbc:mysql://aliyun:3306/company \
--username root \
--password root \
--target-dir /user/company \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t" \
--query 'select name,sex from staff where id <=1 and $CONDITIONS;'

query：指定查询SQL where条件要有$CONDITIONS

注意: must contain '$CONDITIONS' in WHERE clause.

如果query后使用的是双引号，则$CONDITIONS前必须加转移符，防止shell识别为自己的变量。

导入指定列(columns)

sqoop import \
--connect jdbc:mysql://aliyun:3306/company \
--username root \
--password root \
--target-dir /user/company \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t" \
--columns id,sex \
--table staff

columns：指定导入的列

筛选查询导入数据(where)

where参数可以进行一些简单的筛选

sqoop import \
--connect jdbc:mysql://aliyun:3306/company \
--username root \
--password root \
--target-dir /user/company \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t" \
--table staff \
--where "id=1"

where：指定查询过滤条件

增量导入

增量导入的一个场景就是昨天导入了一批数据，今天又增加了部分数据，现在要把这部分数据也导入到hdfs中。

sqoop import \
--connect jdbc:mysql://localhost:3306/company \
--username root \
--password root \
--target-dir /user/company/ \
--num-mappers 1 \
--table staff \
--null-string "" \
--null-non-string "0" \
--check-column "id" \
--incremental append \
--fields-terminated-by '\t' \
--last-value 0

null-string：字符串为null怎么处理
null-non-string：其他类型为null怎么处理
check-column：根据哪一行做增量导入
last-value：开始增量导入的上个位置

RDBMS导入到Hive

在导入hive之前先在hive创建一样的表结构

create external table staff(
    id int(4) , 
    name varchar(255), 
    sex varchar(255)
)
row format delimited
fields terminated by '\t';

使用sqoop导入数据

sqoop import \
--connect jdbc:mysql://aliyun:3306/company \
--password root \
--username root \
--table staff \
--hive-overwrite \
--delete-target-dir \
--null-string "" \
--null-non-string "0" \
--hive-import \
--hive-database default \
--hive-table staff \
--fields-terminated-by '\t' \
--num-mappers 1

hive-import：数据从关系数据库中导入到hive表中
hive-overwrite：覆盖掉在hive表中已经存在的数据
hive-table：后面接hive表,默认使用MySQL的表名
如果导入的是分区表，需要指定分区的key和value
--hive-partition-key key \
--hive-partition-value value \

该过程分为两步，第一步将数据导入到HDFS，第二步将导入到HDFS的数据迁移到Hive仓库，第一步默认的临时目录是/user/hadoop/表名

查看hive表中的数据:

select * from staff;

id|name    |sex   |
--|--------|------|
 1|Thomas  |Male  |
 2|Catalina|FeMale|

HDFS导出到RDBMS

首先保证MySQL创建了一张和Hive一样表结构的表用来接收数据

注意表结构和分隔符都要一样

写Sqoop代码(批量导入)

sqoop export \
-Dsqoop.export.records.per.statement=10 \
--connect jdbc:mysql://aliyun:3306/company \
--password root \
--username root \
--table staff \
--export-dir /user/company/ \
--null-string "" \
--null-non-string "0" \
--columns "id,name" \
--fields-terminated-by '\t' \
-m 1

Dsqoop.export.records.per.statement：批量更新，每隔10条提交一次
export-dir：导出的hdfs目录
table：导入的表名
columns：指定导入的列

注意: MySQL中如果表不存在，不会自动创建

Hive导出到RDBMS

首先保证MySQL创建了一张和Hive一样表结构的表用来接收数据

注意表结构和分隔符都要一样
写Sqoop代码
sqoop export \
--connect jdbc:mysql://aliyun:3306/company \
--username root \
--password root \
--table staff \
--num-mappers 1 \
--export-dir /user/hive/warehouse/staff \
--input-fields-terminated-by "\t"
- export-dir：指定被导出的目录
- input-fields-terminated-by：导入的分隔符格式，和导入的fields-terminated-by有区别
注意: Mysql中如果表不存在，不会自动创建

查看MySQL数据库中的数据

select * from staff;

+----+----------+--------+
| id | name     | sex    |
+----+----------+--------+
|  1 | Thomas   | Male   |
|  2 | Catalina | FeMale |
|  3 | tunan    | Male   |
+----+----------+--------+

Sqoop的综合操作

需求：emp和dept表是在MySQL，把MySQL数据抽取到Hive进行统计分析，然后把统计的结果回写到MySQL中

在Hive中创建与MySQL中emp和dept表相对应的表emp_hive，dept_hive

emp_hive表

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

dept_hive表

create external table dept_hive(
deptno int,
dname string,
loc string
)
row format delimited
fields terminated by '\t';

创建在Hive中的中间结果表

create external table mid_hive(
empno int,
ename string,
deptno int,
dname string
)
row format delimited
fields terminated by '\t';

将MySQL中的emp表中的数据传到emp_hive中

sqoop import \
--connect jdbc:mysql://aliyun:3306/tunan \
--username root \
--password root \
--table emp \
--hive-overwrite \
--delete-target-dir \
--null-string "-" \
--null-non-string "0" \
--hive-import \
--hive-database default \
--hive-table emp_hive \
--fields-terminated-by '\t' \
-m 1

将MySQL中的dept表中的数据传到dept_hive

sqoop import \
--connect jdbc:mysql://aliyun:3306/tunan \
--username root \
--password root \
--table dept \
--delete-target-dir \
--null-string "-" \
--null-non-string '0' \
--hive-import \
--hive-database default \
--hive-table dept_hive \
--fields-terminated-by '\t' \
-m 1

将Hive中的表进行业务处理

insert into table 
	mid_hive
select
	e.empno,e.ename,d.deptno,d.dname
from
	emp_hive  e
join
	dept_hive  d
on
	e.deptno=d.deptno
select
	*
from 
	mid_hive;

查看中间表的数据

select * from mid_hive;

empno|ename |deptno|dname     |
-----|------|------|----------|
 7369|SMITH |    20|RESEARCH  |
 7499|ALLEN |    30|SALES     |
 7521|WARD  |    30|SALES     |
 7566|JONES |    20|RESEARCH  |
 7654|MARTIN|    30|SALES     |
 7698|BLAKE |    30|SALES     |
 7782|CLARK |    10|ACCOUNTING|
 7788|SCOTT |    20|RESEARCH  |
 7839|KING  |    10|ACCOUNTING|
 7844|TURNER|    30|SALES     |
 7876|ADAMS |    20|RESEARCH  |
 7900|JAMES |    30|SALES     |
 7902|FORD  |    20|RESEARCH  |
 7934|MILLER|    10|ACCOUNTING|

在MySQL中创建返回数据的表

create table `mid`(
empno int(11),
ename varchar(20),
deptno int(11),
dname varchar(20)
)

将处理好的数据用Sqoop发回MySQL

sqoop export \
--connect jdbc:mysql://aliyun:3306/tunan \
--username root \
--password root \
--table mid \
-m 1 \
--export-dir /user/hive/warehouse/mid_hive \
--input-fields-terminated-by '\t'

查看MySQL中已经发回的数据

select * from mid;

+-------+--------+--------+------------+
| empno | ename  | deptno | dname      |
+-------+--------+--------+------------+
|  7369 | SMITH  |     20 | RESEARCH   |
|  7499 | ALLEN  |     30 | SALES      |
|  7521 | WARD   |     30 | SALES      |
|  7566 | JONES  |     20 | RESEARCH   |
|  7654 | MARTIN |     30 | SALES      |
|  7698 | BLAKE  |     30 | SALES      |
|  7782 | CLARK  |     10 | ACCOUNTING |
|  7788 | SCOTT  |     20 | RESEARCH   |
|  7839 | KING   |     10 | ACCOUNTING |
|  7844 | TURNER |     30 | SALES      |
|  7876 | ADAMS  |     20 | RESEARCH   |
|  7900 | JAMES  |     30 | SALES      |
|  7902 | FORD   |     20 | RESEARCH   |
|  7934 | MILLER |     10 | ACCOUNTING |
+-------+--------+--------+------------+

shell操作数据库

mysql -uroot -pruozedata <<EOF
	use sqoop;
	truncate etl_result;
EOF