辅助框架之Sqoop数据迁移工具
1. Sqoop是什么？
Sqoop是apache旗下的一款 ”Hadoop和关系数据库之间传输数据”的工具。
（1）导入数据：将MySQL，Oracle导入数据到Hadoop的HDFS、HIVE、HBASE等数据存储系统；
（2）导出数据：从Hadoop的文件系统中导出数据到关系数据库。
2. Sqoop的工作机制
将导入和导出的命令翻译成mapreduce程序实现；在翻译出的mapreduce中主要是对inputformat和outputformat进行定制。
3. Sqoop基本架构
4. Sqooq数据的导入
注意：需要先开启zookeeper集群、Hadoop集群、再开启hbase，然后再执行sqoop导入
4.1列举出所有的数据库
（1）命令行查看帮助文档
sqoop list-databases --help
（2）列出node03上mysql数据库中所有的数据库名称
sqoop list-databases --connect jdbc:mysql://node03:3306/ --username root --password 123456
（3）查看某一个数据库下面的所有数据表
sqoop list-tables --connect jdbc:mysql://node03:3306/hive --username root --password 123456
4.2导入数据库表数据到HDFS
（1）在MySQL数据库服务器中创建一个数据库userdb, 然后在创建一张表 emp，添加点测试数据到表中；
- 建库：
CREATE DATABASE IF NOT EXISTS userdb DEFAULT CHARACTER SET utf8 ;
USE userdb;
- 建表：
CREATE TABLE `emp` (
`id` INT(11) NOT NULL,
`name` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
`deg` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
`salary` DOUBLE(255, 2) NULL DEFAULT NULL,
`dept` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE) ENGINE = INNODB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = DYNAMIC;
- 插入数据：
INSERT INTO `emp` VALUES (1201, 'gopal', 'manager', 50000.00, 'TP');
INSERT INTO `emp` VALUES (1202, 'manisha', 'Proof reader', 50000.00, 'TP');
INSERT INTO `emp` VALUES (1203, 'khalil', 'php dev', 30000.00, 'AC');
INSERT INTO `emp` VALUES (1204, 'prasanth', 'php dev', 20000.00, 'AC');
INSERT INTO `emp` VALUES (1205, 'kranthi', 'admin', 10000.00, 'TP');
INSERT INTO `emp` VALUES (1206, 'tom', 'admin', 50000.00, 'TP');
（2）从MySQL数据库服务器中的userdb数据库下的emp表导入HDFS上：
sqoop import \
--connect jdbc:mysql://node03:3306/userdb \
--username root \
--password 123456 \
--table emp \
--m 1
#参数解释
--connect 指定mysql链接地址
--username 连接mysql的用户名
--password 连接mysql的密码
--table 指定要导入的mysql表名称
--m: 表示这个MR程序需要多少个MapTask去运行，默认为4
提交之后，会运行一个MR程序，最后查看HDFS上的目录看是否有数据生成：
4.3导入数据库表数据到HDFS指定目录
使用参数 --target-dir来指定导出目的地；
使用参数--delete-target-dir来判断导出目录是否存在，如果存在就删掉。
sqoop import
--connect jdbc:mysql://node03:3306/userdb
--username root
--password 123456
--table emp
--target-dir /sqoop/emp
--delete-target-dir
--m 1
查看hdfs上的数据：
4.4 导入数据库表数据到HDFS指定目录并且指定数据字段的分隔符
使用参数：--fields-terminated-by 分隔符
sqoop import \
--connect jdbc:mysql://node03:3306/userdb \
--username root \
--password 123456 \
--delete-target-dir \
--table emp \
--target-dir /sqoop/emp1 \
--fields-terminated-by '#' \
--m 1
查看hdfs数据：
4.5导入关系表到Hive中
（1）需要将hive的hive-exec-1.1.0-cdh5.14.2.jar包拷贝到sqoop的lib目录下。
cp /install/hive-1.1.0-cdh5.14.2/lib/hive-exec-1.1.0-cdh5.14.2.jar /install/sqoop-1.4.6-cdh5.14.2/lib/
（2）在hive中创建一个数据库和表：
database sqooptohive;
create external table sqooptohive.emp_hive(id int,name string,deg string,salary double ,dept string) row format delimited fields terminated by '\001';
（3）把mysql表数据导入到hive表中
sqoop import \
--connect jdbc:mysql://node03:3306/userdb \
--username root \
--password 123456 \
--table emp \
--fields-terminated-by '\001' \
--hive-import \
--hive-table sqooptohive.emp_hive \
--hive-overwrite \
--delete-target-dir \
--m 1##参数解释
--hive-table 指定要导入到hive表名
--hive-import 导入数据到hive表中
--hive-overwrite 覆盖hive表中已存有的数据
（4）执行完成了查看hive中表的数据：select * from sqooptohive.emp_hive;
4.6 导入数据库表数据到hive中(并自动创建hive表)
sqoop import \
--connect jdbc:mysql://node03:3306/userdb \
--username root \
--password 123456 \
--table emp \
--hive-database sqooptohive \
--hive-table emp1 \
--hive-import \
--m 1#将mysql中的emp表插入到hive的sqooptohive数据库中的emp1表中
4.7 导入表数据子集
通过where参数来导入子集。
sqoop import \
--connect jdbc:mysql://node03:3306/userdb \
--username root \
--password 123456 \
--table emp \
--target-dir /sqoop/emp_where \
--delete-target-dir \
--where "dept = 'TP'" \
--m 1
4.8 sql语句查找导入hdfs
通过 query参数来指定我们的sql语句，通过sql语句来过滤我们的数据进行导入。
sqoop import \
--connect jdbc:mysql://node03:3306/userdb \
--username root \
--password 123456 \
--target-dir /sqoop/emp_sql \
--delete-target-dir \
--query 'select * from emp where salary >30000 and $CONDITIONS' \
--m 1
$CONTITONS是linux系统的变量，如果你想通过并行的方式导入结果，每个map task需要执行sql查询后脚语句的副本，结果会根据sqoop推测的边界条件分区。query必须包含$CONDITIONS。这样每个sqoop程序都会被替换为一个独立的条件。同时你必须指定 --split-by '字段'，后期是按照字段进行数据划分，最后可以达到多个MapTask并行运行。
如：
sqoop import \
--connect jdbc:mysql://node03:3306/userdb \
--username root \
--password 123456 \
--target-dir /sqoop/emp_sql_2 \
--delete-target-dir \
--query 'select * from emp where salary >30000 and $CONDITIONS' \
--split-by 'id' \
--m 2
--split-by '字段'： 后期按照字段进行数据划分实现并行运行多个MapTask。
4.9 增量导入
增量导入是仅导入新添加的表中的行的技术。
它需要添加 ‘incremental’, ‘check-column’, 和 ‘last-value’选项来执行增量导入。
- --incremental <mode>
- --check-column <column name>
- --last value <last check column value>
（1）第一种增量导入实现
基于递增列的增量数据导入（Append方式）；（不能加上 --delete-target-dir 参数）
sqoop import \
--connect jdbc:mysql://node03:3306/userdb \
--username root \
--password 123456 \
--table emp \
--incremental append \
--check-column id \
--last-value 1202 \ #导入emp表当中id大于1202的数据--target-dir /sqoop/increment \
--m 1##参数解释
--incremental 这里使用基于递增列的增量数据导入
--check-column 递增列字段
--last-value 指定上一次导入中检查列指定字段最大值
--target-dir 数据导入的目录
（2）第二种增量导入实现
基于时间列的增量数据导入（LastModified方式）：要求原有表中有time字段，它能指定一个时间戳。
sqoop import \
--connect jdbc:mysql://node03:3306/userdb \
--username root \
--password 123456 \
--table user \
--incremental lastmodified \
--check-column createTime \
--last-value '2019-10-01 10:30:00' \
--target-dir /sqoop/increment2 \
--m 1##参数解释
--incremental 这里使用基于时间列的增量导入
--check-column 时间字段
--last-value 指定上一次导入中检查列指定字段最大值
--target-dir 数据导入的目录；如果该目录存在(可能已经有数据)，再使用的时候需要添加 --merge-key or --append
--merge-key 指定合并key（对于有修改的）
--append 直接追加修改的数据
4.10 mysql表的数据导入到hbase中
需求：实现把一张mysql表数据导入到hbase中。
注意：sqoop导入hbase表时需指定表名、列族、rowkey等，即如下红色字体：
sqoop import \
--connect jdbc:mysql://node03:3306/userdb \
--username root \
--password 123456 \
--table user \
--hbase-table mysqluser \
--column-family f1 \
--hbase-create-table \
--hbase-row-key id \
--m 1#参数说明
--hbase-table 指定hbase表名
--column-family 指定表的列族
--hbase-create-table 表不存在就创建
--hbase-row-key 指定hbase表的id
--m 指定使用的MapTask个数
5. Sqoop数据的导出
将数据从HDFS把文件导出到RDBMS数据库。
- 导出前，目标表必须存在于目标数据库中。
- 默认操作是从将文件中的数据使用INSERT语句插入到表中
- 更新模式下，是生成UPDATE语句更新表数据
5.1hdfs文件导出到mysql表中
（1）创建一张mysql表（一定要先创建！此时只是空表）
CREATE TABLE userdb.fromhdfs (
id INT DEFAULT NULL,
name VARCHAR(100) DEFAULT NULL,
age int DEFAULT NULL,
address VARCHAR(100) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;
（2）执行导出命令
sqoop export \
--connect jdbc:mysql://node03:3306/userdb \
--username root \
--password 123456 \
--table fromhdfs \
--export-dir /user/hive/warehouse/hive_source.txt \ #数据存在此文件中
--input-fields-terminated-by " "##参数解释
--table 指定导出的mysql表名
--export-dir 指定hdfs数据文件目录
--input-fields-terminated-by 指定文件数据字段的分隔符
命令执行后：