Sqoop
背景： Sqoop是什么
Sqoop是一个用于Hadoop和结构化数据存储（如关系型数据库）之间进行高效传输大批量数据的工具。它包括以下两个方面：
可以使用Sqoop将数据从关系型数据库管理系统(如MySql)导入到Hadoop系统(如HDFS、Hive、HBase)中；将数据从Hadoop系统 中抽取并导出到关系型数据库(如MySql)
常见数据库开源工具：
- Sqoop
- Datax
- Kettle
- Cannal
1 Sqoop简介以及使用
1.1 底层实现原理
Sqoop的核心设计思想是利用MapReduce加快数据传输速度。也就是说Sqoop的导入和导出功能是通过基于Map Task（只有map）的MapReduce作业实现的。所以它是一种批处理方式进行数据传输，难以实现实时的数据进行导入和导出。
官网介绍：
Apache Sqoop™ is a tool designed for efficiently transferring bulk
data between Apache Hadoop and structured datastores such as relational databases.
Sqoop结构图:
1.2 特点
- 优点：它可以将跨平台的数据进行整合。
- 缺点：它不是很灵活。
主要执行操作
Sqoop的重要的几个关键词
- import ： 从关系型数据库到Hadoop
- export ： 从Hadoop到关系型数据库。
2 Sqoop的安装
注意：在安装Sqoop之前要配置好本机的Java环境和Hadoop环境
先把spoop的安装包 sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz 拷贝在系统目录下的 /root/softwares下面
2.1 解压配置环境变量
# 解压tar.gz包
[root@qf01 local] tar -zxvf /root/sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz -C /usr/local/
#把Sqoop的安装路径修改为sqoop-1.4.7,方便以后配置和调用
[root@qf01 local]# mv sqoop-1.4.7.bin__hadoop-2.6.0 sqoop-1.4.7
[root@qf01 sqoop-1.4.7]# vi /etc/profile
# 追加内容如下:
export SQOOP_HOME=/usr/local/sqoop-1.4.7
export PATH=$PATH:$SQOOP_HOME/bin
2.2 新建配置文件
[root@qf01 sqoop-1.4.7] mv ./conf/sqoop-env-template.sh ./conf/sqoop-env.sh
2.3 修改配置文件
配置文件：
[root@qf01 sqoop-1.4.7] vi ./conf/sqoop-env.sh
按照本系统实际安装的Hadoop系列目录配置好下面的路径:
export HADOOP_COMMON_HOME=/usr/local/hadoop-3.2.1
export HADOOP_MAPRED_HOME=/usr/local/hadoop-3.2.1
export HIVE_HOME=/usr/local/hive-3.1.2
export ZOOCFGDIR=/usr/local/zookeeper-3.6.2
2.4 拷贝MySql驱动
因为我们现在通过JDBC让MySql和HDFS等进行数据的导入导出,所以我们先必须把JDBC的驱动包拷贝到sqoop/lib路径下,如下
PS: 因为Linux下安装MySQL是8.0版本所以SQL驱动也要是8.0版本，不能是下面这个驱动
这个驱动不用外界上传，hive-3.1.2/lib 路径下就有mysql驱动 所以只要执行 拷贝操作就可以
[root@qf01 lib]# cp ./mysql-connector-java-8.0.26.jar /usr/local/sqoop-1.4.7/lib/
[root@qf01 sqoop-1.4.7] cp /root/mysql-connector-java-5.1.18.jar ./lib/
PS: 这个驱动主要是对应mysql5.x版本
2.5 验证安装
#查看Sqoop的版本
[root@qf01 sqoop-1.4.7] sqoop version
3 Sqoop命令执行
3.1 常见命令执行参数
通过Sqoop加不同参数可以执行导入导出,通过sqoop help 可以查看常见的命令行
#常见Sqoop参数
[root@qf01 sqoop-1.4.7] sqoop help
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 mainframe datasets to HDFS
list-databases List available databases on a server
list-tables List available tables in a database
version Display version information
3.2 直接执行命令
Sqoop运行的时候不需要启动后台进程,直接执行sqoop命令加参数即可.简单举例如下:
# #通过参数用下面查看数据库
[root@qf01 sqoop-1.4.7] sqoop list-databases --connect jdbc:mysql://localhost:3306 --username root --password 123456;
注意：可能会出现如下错误
导入commons-lang-2.6.jar到lib下即可解决
3.3 通过文件传递参数(脚本)
在执行Sqoop命令时,如果每次执行的命令都相似,那么把相同的参数可以抽取出来,放在一个文本文件中,把执行时的参数加入到这个文本文件为参数即可. 这个文本文件可以用--options-file来指定,平时可以用定时任务来执行这个脚本,避免每次手工操作.
把3.2章节中命令中的JDBC连接的参数一般是不变的,可以把它抽取出来放在一个文件中/.../sqoop-1.4.7/config.conf,如下:
list-databases
--connect
jdbc:mysql://localhost:3306
--username
root
--password
123456
那么上面的执行的命令就可以变为:
[root@qf01 sqoop-1.4.7] bin/sqoop --options-file config.conf
为了让配置文件config.conf的可读性更强,可以加入空行和注释,不会影响文件内容的读取,如下:
# 指令: 列出mysql中的所有数据库
list-databases
# 指定连接字符串
--connect
jdbc:mysql://localhost:3306
--username
root
--password
123456
3.4 Import 详解
import是从关系数据库导入到Hadoop操作【HDFS、Hive、HBase】,下面是一些通用参数介绍:
3.4.1 通用参数
如下:
| Argument | Description |
|---|---|
| –connect | 指定JDBC连接字符串 |
--connection-manager | 指定连接管理类 |
--driver | 指定连接的驱动程序 |
-P | 从控制台读入密码(可以防止密码显示中控制台) |
| –password | 指定访问数据库的密码 |
| –username | 指定访问数据库的用户名 |
3.4.1.1 连接数据库
Sqoop的设计就是把数据库数据导入HDFS,所以必须指定连接字符串才能访问数据库,这个连接字符串类似于URL,这个连接字符串通过--connect参数指定,它描述了连接的数据库地址和具体的连接数据库,譬如:
[root@qf01 sqoop-1.4.7] sqoop import --connect jdbc:mysql://database.example.com/databaseName
#指定连接的服务器地址是database.example.com ,要连接的数据库是databaseName
上面连接命令只是指定数据库,默认情况下数据库都是需要用户名和参数的,在这里可以用--username 和--password来指定,譬如:
#指定用户名和密码来连接数据库
[root@qf01 sqoop-1.4.7] sqoop import --connect jdbc:mysql://localhost:3306/mysql --username root --password 123456;
3.4.1.2 查看数据库
在Sqoop中,可以通过list-databases参数来查看MySql的数据库,这样在导入之前可以得到所有的数据库的名字,具体案例如下:
# 列出所有数据库
[root@qf01 sqoop-1.4.7] sqoop list-databases --connect jdbc:mysql://localhost:3306 --username root --password 123456;
3.4.1.3 查看所有表
在得到所有数据库的名字后,也可以查看当前数据库中的所有表,可以使用 list-tables参数来进行查看,查看的时候在url连接中一定要指定数据库的名字.
# 列出数据库中所有表
[root@qf01 sqoop-1.4.7] sqoop list-tables --connect jdbc:mysql://localhost:3306/qfdb --username root --password 123456;
3.4.2 Import的控制参数
常见import的控制参数有如下几个:
| Argument | Description |
|---|---|
--append | 通过追加的方式导入到HDFS |
--as-avrodatafile | 导入为 Avro Data 文件格式 |
--as-sequencefile | 导入为 SequenceFiles文件格式 |
--as-textfile | 导入为文本格式 (默认值) |
--as-parquetfile | 导入为 Parquet 文件格式 |
--columns | 指定要导入的列 |
--delete-target-dir | 如果目标文件夹存在,则删除 |
--fetch-size | 一次从数据库读取的数量大小 |
-m,--num-mappers | m 用来指定map tasks的数量,用来做并行导入【-m指定表中需要有主键】 |
-e,--query | 指定要查询的SQL语句 |
--split-by | 用来指定分片的列 |
--table | 需要导入的表名 |
--target-dir | HDFS 的目标文件夹 |
--where | 用来指定导入数据的where条件 |
-z,--compress | 是否要压缩 |
--compression-codec | 使用Hadoop压缩 (默认是 gzip) |
3.4.2.1 指定表导入
数据准备【是在Linux下的Mysql中提供数据】
在本地MySql数据库中新建一个qfdb数据库,sql代码在data/qfdb.sql中,如下:
CREATE TABLE emp(
empno INT primary key,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm decimal(7,2),
deptno INT
) ;
INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
Sqoop的典型导入都是把关系数据库中的表导入到HDFS中,使用--table参数可以指定具体的表导入到HDFS,譬如用 --table emp,默认情况下是全部字段导入.如下:
[root@qf01 sqoop-1.4.7]# bin/sqoop import --connect jdbc:mysql://localhost:3306/qfdb \
--username root --password 123456 \
--table emp \
--target-dir hdfs://qf01:9820/sqoopdata/emppppp \
--delete-target-dir
注意：如果使用MySQL8.X版本可能会与到的问题【暂定这个异常是一个警告异常，不会影响最终数据的生成】
Caused by: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
---------------------------------------------------------------------------
解决：将mysql回收空闲连接的时间变长，mysql默认回收时间是8小时，可以在mysql目录下的/etc/my.cnf中增加下面配置，将时间改为1天。
打开
[root@qf01 sqoop-1.4.7]# vim /etc/my.cnf
# 添加如下两个参数
wait_timeout=31536000
interactive_timeout=31536000
重启服务，即可解决问题
[root@qf01 sqoop-1.4.7]# systemctl restart mysqld
强烈建议~！！！！！！！！！！！！！！~！！！！！！！！！！！
将localhost 修改为 本主机的IP地址 不要使用 localhost 不要 不要 不要
可以快速使用hdfs的命令查询结果
[root@qf01 sqoop-1.4.7]# hdfs dfs -cat /sqoopdata/emp/par*
3.4.2.2 指定列导入
如果想导入某几列,可以使用 --columns,如下:
[root@qf01 sqoop-1.4.7]# bin/sqoop import --connect jdbc:mysql://localhost:3306/qfdb \
--username root --password 123456 \
--table emp \
--columns 'empno,mgr' \
--target-dir hdfs://qf01:9820/sqoopdata/emp \
--delete-target-dir
可以使用下面hdfs命令快速查看结果
[root@qf01 sqoop-1.4.7]# hdfs dfs -cat /sqoopdata/emp/par*
3.4.2.3 指定条件导入
在导入表的时候,也可以通过指定where条件来导入,具体参数使用 --where,譬如要导入员工号大于7800的记录,可以用下面参数:
[root@qf01 sqoop-1.4.7]# bin/sqoop import --connect jdbc:mysql://localhost:3306/qfdb \
--username root --password 123456 \
--table emp \
--columns 'empno,mgr' \
--where 'empno>7800' \
--target-dir hdfs://qf01:9820/sqoopdata/5 \
--delete-target-dir
用命令查询结果:
[root@qf01 sqoop-1.4.7]# hdfs dfs -cat /sqoopdata/emp/par*
结果如下:
7839,null
7844,7698
7876,7788
7900,7698
7902,7566
7934,7782
3.4.2.4 指定Sql导入
上面的可以通过表,字段,条件进行导入,但是还不够灵活,其实Sqoop还可以通过自定义的sql来进行导入,可以通过--query 参数来进行导入,这样就最大化的用到了Sql的灵活性.如下:
[root@qf01 sqoop-1.4.7]# bin/sqoop import --connect jdbc:mysql://localhost:3306/qfdb \
--username root --password 123456 \
--query 'select empno,mgr,job from emp WHERE empno>7800 and $CONDITIONS' \
--target-dir hdfs://qf01:9820/sqoopdata/emp \
--delete-target-dir \
--split-by empno \
-m 1
sqoop import --connect jdbc:mysql://localhost:3306/qfdb --username root --password 123456 --query 'select empno,mgr,job from emp where empno>7800 and $CONDITIONS' --target-dir hdfs://qf01:9820/sqoopdata/empquery --delete-target-dir --split-by empno -m 1
注意:在通过--query来导入数据时,必须要指定--target-dir
如果你想通过并行的方式导入结果，每个map task需要执行sql查询语句的副本，结果会根据Sqoop推测的边界条件分区。query必须包含$CONDITIONS。这样每个Sqoop程序都会被替换为一个独立的条件。同时你必须指定--split-by.分区
-m 1 是指定通过一个Mapper来执行流程
查询执行结果
[root@qf01 sqoop-1.4.7]# hdfs dfs -cat /sqoopdata/emp/par*
结果如下:
7839,null,PRESIDENT
7844,7698,SALESMAN
7876,7788,CLERK
7900,7698,CLERK
7902,7566,ANALYST
7934,7782,CLERK
3.4.2.5 单双引号区别
在导入数据时,默认的字符引号是单引号,这样Sqoop在解析的时候就安装字面量来解析,不会做转移:例如:
--query 'select empno,mgr,job from emp WHERE empno>7800 and $CONDITIONS'
如果使用了双引号,那么Sqoop在解析的时候会做转义的解析,这时候就必须要加转义字符: 如下:
--query "select empno,mgr,job from emp WHERE empno>7800 and \$CONDITIONS"
3.4.2.6 MySql缺主键问题
1如果MySql的表没有主键，将会报错：
19/12/02 10:39:50 ERROR tool.ImportTool: Import
failed: No primary key could be found for table u1. Please specify one with
-- split- by or perform a sequential import with '-m 1
解决方案:
通过 --split-by 来指定要分片的列
代码如下:
[root@qf01 sqoop-1.4.7]# bin/sqoop import --connect jdbc:mysql://localhost:3306/qfdb \
--username root --password 123456 \
--query 'select empno,mgr,job from emp WHERE empno>7800 and $CONDITIONS' \
--target-dir hdfs://qf01:9820/sqoopdata/emp \
--delete-target-dir \
--split-by empno \
-m 1
总结
sqoop import --connect jdbc:mysql://ip地址:端口号/数据库名称 \
--username 用户名 --password 密码 \
--table 表名
--target-dir hdfs存储路径 \
--delete-target-dir \
---》 全表数据导出
sqoop import --connect jdbc:mysql://ip地址:端口号/数据库名称 \
--username 用户名 --password 密码 \
--table 表名 \
--columns '列名'
--target-dir hdfs存储路径 \
--delete-target-dir \
---》 指定整个表中某个列导出数据 列可以有多个使用【,】分隔
sqoop import --connect jdbc:mysql://ip地址:端口号/数据库名称 \
--username 用户名 --password 密码 \
--table 表名 \
--columns '列名'
--where '列的判断条件' \
--target-dir hdfs存储路径 \
--delete-target-dir \
---》 指定整个表中符合where条件的列的数据进行导出操作 列可以有多个使用【,】分隔
结论: table 、columns 、where 是一起使用
sqoop import --connect jdbc:mysql://ip地址:端口号/数据库名称 \
--username 用户名 --password 密码 \
--query 'sql查询语句'
--target-dir hdfs存储路径 \
--delete-target-dir \
--split-by 分区列名 \
--m 数值
---》 使用query的方式 提供sql查询语句 然后需要提供 $CONDITIONS 和--split-by是一套联合使用 可以用过m 数值指定并行处理操作，但是需要提供分区（split-by），这个分区必须在query 语句以$CONDITIONS 方式获取
强烈建议~！！！！！！！！！！！！！！~！！！！！！！！！！！
将localhost 修改为 本主机的IP地址 不要使用 localhost 不要 不要 不要
现在先暂时无视Caused by: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure异常 ，可以正确出现结果
但是如果在执行完毕之后 出现 ERROR tool.ImportTool: Import failed: Import job failed! 有限检查语句是否有问题，如果没有问题 二次尝试执行语句 去除m1
3.4.3 导入到Hive中
3.4.3.1 说明
Sqoop的导入工具的主要功能是将数据上传到HDFS中的文件中。如果您有一个与HDFS集群相关联的Hive，Sqoop还可以通过生成和执行CREATETABLE语句来定义Hive中的数据，从而将数据导入到Hive中。将数据导入到Hive中就像在Sqoop命令行中添加–hive-import选项。
如果Hive表已经存在，则可以指定--hive-overwrite选项，以指示必须替换单元中的现有表。在将数据导入HDFS或省略此步骤之后，Sqoop将生成一个Hive脚本，其中包含使用Hive的类型定义列的CREATE表操作，并生成LOAD Data INPATH语句将数据文件移动到Hive的仓库目录中。
在导入Hive之前先要配置Hadoop的Classpath才可以,否则会报类找不到错误,在/etc/profile末尾添加如下配置:
配置补充：
export HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$HIVE_HOME/lib/*
#刷新配置
source /etc/profile
3.4.3.2 参数说明
具体的参数如下:
| Argument | Description |
|---|---|
--hive-home | 覆盖环境配置中的$HIVE_HOME,默认可以不配置 |
| –hive-import | 指定导入数据到Hive中 |
--hive-overwrite | 覆盖当前已有的数据 |
--create-hive-table | 是否创建hive表,如果已经存在,则会失败 |
--hive-table | 设置要导入的Hive中的表名 |
4.3.4.3.3 实际导入案例
具体导入演示代码如下:
提示: 为了看到演示效果,可以先在Hive删除emp表
[root@qf01 sqoop-1.4.7] bin/sqoop import --connect jdbc:mysql://qf01:3306/qfdb \
--username root \
--password 123456 \
--table emp \
--hive-import \
--hive-overwrite \
--hive-table "emp3" \
--hive-database db2 \
-m 1 ---》 可以先不添加
在Hive中查看表:
hive> show tables;
#结果如下:
OK
emp
可以在Hive中查看数据是否导入:
select * from emp;
#结果如下:
7369 SMITH CLERK 7902 1980-12-17 800.0 NULL 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600.0 300.0 30
7521 WARD SALESMAN 7698 1981-02-22 1250.0 500.0 30
7566 JONES MANAGER 7839 1981-04-02 2975.0 NULL 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250.0 1400.0 30
7698 BLAKE MANAGER 7839 1981-05-01 2850.0 NULL 30
7782 CLARK MANAGER 7839 1981-06-09 2450.0 NULL 10
7788 SCOTT ANALYST 7566 1987-04-19 3000.0 NULL 20
7839 KING PRESIDENT NULL 1981-11-17 5000.0 NULL 10
7844 TURNER SALESMAN 7698 1981-09-08 1500.0 0.0 30
7876 ADAMS CLERK 7788 1987-05-23 1100.0 NULL 20
7900 JAMES CLERK 7698 1981-12-03 950.0 NULL 30
7902 FORD ANALYST 7566 1981-12-03 3000.0 NULL 20
7934 MILLER CLERK 7782 1982-01-23 1300.0 NULL 10
4 Sqoop导入实战
4.1 Sqoop-import
案例1
表没有主键，需要指定map task的个数为1个才能执行
Sqoop导入原理:
Sqoop默认是并行的从数据库源导入数据。您可以使用-m或–num-mappers参数指定用于执行导入的map任务(并行进程)的数量。每个参数都取一个整数值，该整数值对应于要使用的并行度。默认情况下，使用四个任务。一些数据库可以通过将这个值增加到8或16来改善性能。
默认情况下，Sqoop将标识表中的主键id列用作拆分列。从数据库中检索分割列的高值和低值，map任务操作整个范围的大小均匀的组件。譬如ID的范围是0-800,那么Sqoop默认运行4个进程,通过执行
SELECT MIN(id), MAX(id) FROM emp找出id的范围,然后把4个任务的id设置范围是(0-200),(200-400),(400-600),(600-800)但是当一个表没有主键时,上面的切分就无法进行,Sqoop导入时就会出错,这时候可以通过-m把mapper的数量设为1,只有一个Mapper在运行,这时候就不需要切分,也可以避免主键不存在时候报错的问题.
#错误信息 ERROR tool.ImportTool: Import failed: No primary key could be found for table emp. Please specify one with --split-by or perform a sequential import with '-m 1'.
强烈建议！！！！！！！！！！！！！！！！！！！！！！！！！
将localhost 修改为 本主机的IP地址 不要使用 localhost 不要 不要 不要
导入代码:
[root@qf01 sqoop-1.4.7]# bin/sqoop import --connect jdbc:mysql://localhost:3306/qfdb \
--username root --password 123456 \
--table emp -m 1
4.2 DBMS-HDFS
案例2
表没有主键，使用–split-by指定执行split的字段
问题同上,如果表没有主键,那么还有个办法就是手工指定要拆分的列,通过
--split-by来指定–split-by 拆分列原则即使 【id列】
[root@qf01 sqoop-1.4.7]# bin/sqoop import --connect jdbc:mysql://localhost:3306/qfdb \
--username root --password 123456 \
--table emp \
--split-by empno \
--delete-target-dir \
--target-dir hdfs://qf01:8020/sqoopdata/emp
-- 出错
Caused by: java.sql.SQLException: null, message from server: "Host 'qf01' is not allowed to connect to this MySQL server"
解决方案：
先连接MySql：
[root@qf01 sqoop-1.4.7]# mysql -uroot -p
(执行下面的语句 .:所有库下的所有表 %：任何IP地址或主机都可以连接)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'mysql' WITH GRANT OPTION;
FLUSH PRIVILEGES;
案例3:条件导入(增量导入)
需要导入的数据不是全部的，而是带条件导入
[root@qf01 sqoop-1.4.7]# bin/sqoop import --connect jdbc:mysql://localhost:3306/qfdb \
--username root --password 123456 \
--table emp \
--split-by empno \
--where 'empno > 7777' \
--target-dir hdfs://qf01:8020/sqoopdata/emp
案例4:部分字段导入
要导入的数据，不想包含全部字段，只需要部分字段
注意:这种跟where差不多,使用时更灵活一些
配合使用 where和columns 这个两个配置也可以执行部分字段导入
[root@qf01 sqoop-1.4.7] bin/sqoop import --connect jdbc:mysql://localhost:3306/qfdb \
--username root --password 123456 \
--split-by empno \
--query 'select empno,ename,job from emp where empno > 7777 and $CONDITIONS' \
--target-dir hdfs://qf01:8020/sqoopdata/7
4.3 DBMS-Hive
案例5：将数据导入到Hive中
[root@qf01 sqoop-1.4.7]# bin/sqoop import --connect jdbc:mysql://localhost:3306/qfdb
--username root
--password 123456
--table emp
--hive-import
-m 1
4.4 DBMS-HBase（没有HBase暂时不操作）
把数据导入到HBase中
hbase中创建表：
create 'mysql2hbase','info'
# 方法一：
[root@qf01 sqoop-1.4.7]# sqoop import --connect jdbc:mysql://qf01:3306/qfdb \
--username root \
--password 123456 \
--table emp \
--hbase-table mysql2hbase \
--column-family info \
--hbase-create-table \
--hbase-row-key empno \
-m 1 \
注意：如果使用的是Hbase2.X版本以上，那么需要添加依赖（1.6版本的依赖），不然会出现如下错误
Exception in thread "main" java.lang.NoSuchMethodError: org.apache.hadoop.hbase.client.HBaseAdmin.<init>(Lorg/apache/hadoop/conf/Configuration;)V
下载安装包：https://archive.apache.org/dist/hbase/1.6.0/
操作方式：将1.6版本的Hbase的依赖lib全部拉去到Sqoop对应文件夹lib下面，再次执行上面的命令
测试：
hbase(main):008:0> scan 'mysql2hbase'
ROW COLUMN+CELL
1 column=info:hobby, timestamp=1585852383291, value=1
1 column=info:profile, timestamp=1585852383291, value=\xE6\xBC\x94\xE5\x91\x98
1 column=info:uname, timestamp=1585852383291, value=bingbing
2 column=info:hobby, timestamp=1585852383291, value=2
2 column=info:profile, timestamp=1585852383291, value=\xE6\xBC\x94\xE5\x91\x98
2 column=info:uname, timestamp=1585852383291, value=feifei
3 column=info:hobby, timestamp=1585852383291, value=1
3 column=info:profile, timestamp=1585852383291, value=\xE5\x94\xB1\xE6\xAD\x8C
3 column=info:uname, timestamp=1585852383291, value=\xE5\x8D\x8E\xE4\xBB\x94
3 row(s) in 2.2770 seconds
# 方法二：
hbase(main):004:0> create 'mysql2hbase11','info'
[root@qf01 sqoop-1.4.7]# sqoop import --connect jdbc:mysql://qf01:3306/qfdb \
--username root \
--password 123456 \
--table emp \
--hbase-table mysql2hbase11 \
--delete-target-dir \
--column-family info \
--hbase-create-table \
--hbase-row-key empno \
-m 1 \
--hbase-bulkload
运行后在结尾处有结果(Trying to load hfile)：
s20/04/03 10:41:11 WARN mapreduce.LoadIncrementalHFiles: Skipping non-directory hdfs://qf01:8020/user/root/user_info/_SUCCESS
h20/04/03 10:41:12 INFO hfile.CacheConfig: CacheConfig:disabled
a20/04/03 10:41:12 INFO mapreduce.LoadIncrementalHFiles: Trying to load hfile=hdfs://qf01:8020/user/root/emp/info/1aef7d02d1a646008f18d49cbb23f20f first=1 last=3
注：
-- hbase-bulkload 不用输入路径，会自己默认导出到某目录，然后完成后自行装载数据到hbase表中;
-m 需要再--hbase-bulkload之前出现
# 测试：
hbase(main):004:0> scan 'mysql2hbase1'
ROW COLUMN+CELL
1 column=info:hobby, timestamp=1585881667767, value=1
1 column=info:profile, timestamp=1585881667767, value=\xE6\xBC\x94\xE5\x91\x98
1 column=info:uname, timestamp=1585881667767, value=bingbing
2 column=info:hobby, timestamp=1585881667767, value=2
2 column=info:profile, timestamp=1585881667767, value=\xE6\xBC\x94\xE5\x91\x98
2 column=info:uname, timestamp=1585881667767, value=feifei
3 column=info:hobby, timestamp=1585881667767, value=1
3 column=info:profile, timestamp=1585881667767, value=\xE5\x94\xB1\xE6\xAD\x8C
3 column=info:uname, timestamp=1585881667767, value=\xE5\x8D\x8E\xE4\xBB\x94
3 row(s) in 0.6170 seconds
4.5 增量导入数据
4.5.1 使用场景
- 经常被操作不断产生数据的表，建议增量。
- 当某表基数很大，但是变化很小，也建议增量
4.5.2 使用方式A
-
query where ： 能精确锁定数据范围
-
incremental ： 增量，最后记录值来做的
4.5.2.1 query where方式【推荐】
通过查询具体日期的方式进行导入
新建一个脚本文件
mysql中的表格:
CREATE TABLE qfdb.sales_order(
orderid INT PRIMARY KEY,
order_date DATE
)
insert into sales_order values(1,'2019-01-01'),(2,'2019-01-02'),(3,'2019-01-11'),(4,'2019-01-22'),(5,'2019-02-03'),(6,'2019-01-15'),(7,'2019-04-01'),(8,'2019-01-15'),(9,'2019-02-01'),(10,'2019-01-30');
[root@qf01 sqoop-1.4.7] vi ./import.sh
写入以下内容:
#!/bin/bash
# yesterday=`date -d "1 days ago" "+%Y-%m-%d"`
yesterday=$1
sqoop import --connect jdbc:mysql://qf01:3306/qfdb \
--username root \
--password 123456 \
--query "select * from sales_order where DATE(order_date) = '${yesterday}' and \$CONDITIONS" \
--delete-target-dir \
--target-dir /user/hive/warehouse/sales_order/dt=${yesterday} \
-m 1 \
--fields-terminated-by '\t'
执行
[root@qf01 sqoop-1.4.7]# bash import.sh 2019-02-01
通过下面HDFS可以快速查询到结果:
[root@qf01 sqoop-1.4.7]# hdfs dfs -cat /user/hive/warehouse/sales_order/dt=2019-02-01/pa*
4.4.2.2 increment的append方式
核心：需要记录上一次数据末尾的id值，然后再进行增量时候从上一次末尾id值开始往后的数据，所以麻烦就在于需要手动维护这个id值 即 --last-value的值
#将会手动维护last-value
[root@qf01 sqoop-1.4.7]# sqoop import --connect jdbc:mysql://qf01:3306/qfdb \
--username root \
--password 123456 \
--table sales_order \
--driver com.mysql.jdbc.Driver \ ---> 这个不用添加 添加也不是这个 com.mysql.cj.jdbc.Driver
--target-dir /user/hive/warehouse/sales_order1/dt=2019-12-30 \
--split-by orderid \
-m 1 \
--check-column orderid \
--incremental append \
--last-value 800 \
--fields-terminated-by '\t'
注意:--last-value 80000 \ 从80000开始检查,如果后面有新的数据就会进行增量导入,如果没有新的数据会提示下面的信息
21/12/12 01:52:16 INFO tool.ImportTool: Incremental import based on column order_date
21/12/12 01:52:16 INFO tool.ImportTool: No new rows detected since last import.
使用下面命令查看:
[root@qf01 sqoop-1.4.7]# hdfs dfs -cat /user/hive/warehouse/sales_order1/dt=2019-12-30/pa*
4.4.2.3 导入填充空值数据【记忆】
[root@qf01 ~]# sqoop import --connect jdbc:mysql://localhost:3306/qfdb --username root --password 123456 --table emp --delete-target-dir --target-dir hdfs://qf01:9820/sqoopdata/emp --null-string '\\N' --null-non-string '0'
关键参数
--null-string '\\N' ## 遇到空字符串会填充\N字符
--null-non-string '0' # 遇到空数字会填充0
5 Sqoop导出
5.1 普通导出
在Sqoop中,使用export进行导出,指的是从HDFS中导出数据到MySql中：
- 构建MySql的表：
CREATE TABLE `u2` (
`id` int(11) DEFAULT NULL,
`age` int(11) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- HDFS导出到MySql
第一种:
上传数据到HDFS
[root@qf01 ~]# vim u2.txt
1,18
2,20
3,30
[root@qf01 ~]# hdfs dfs -put u2.txt /
导出语句:
[root@qf01 sqoop-1.4.7]# sqoop export --connect jdbc:mysql://qf01:3306/qfdb \
--username root \
--password 123456 \
--table u2 \
--export-dir '/u2.txt' \
--input-fields-terminated-by ',' \
-m 1
要注意以下问题
- MySql表的编码格式做为utf8，HDFS文件中的列数类型和MySql表中的字段数一样,最好指定分隔符
- 导出暂不能由Hbase表导出MySql关系型数据库中
--export-dir是一个hdfs中的目录，它不识别_SUCCESS文件- –query导入的时候注意设置问题。
- 导出数据中有些列值有"null"，会报没法解析
- 导出数据的类型需要和MySql中的一致(能自动转没有问题)
5.2 更新并插入导出
场景:
多维结果数据导出；异常重跑数据
--update-mode ：
updateonly，是默认，仅更新,不会新增数据；
allowinsert ：更新并允许插入
--update-key ： 指定更新字段
CREATE TABLE `upv` (
`country_id` int(11) NOT NULL AUTO_INCREMENT,
`visits` int(11) DEFAULT NULL,
PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
导入数据:
[root@qf01 sqoop-1.4.7]# sqoop import --connect jdbc:mysql://qf01:3306/qfdb \
--username root \
--password 123456 \
--table upv \
--target-dir /sqoopdata/ \
-m 1 \
--fields-terminated-by ',' \
--null-string '\\N' \
--null-non-string '0';
导出语句：
[root@qf01 sqoop-1.4.7]# sqoop export --connect jdbc:mysql://qf01:3306/qfdb \
--username root \
--password 123456 \
--table upv \
--export-dir /sqoopdata/upv/* \
--input-fields-terminated-by "," \
--update-mode allowinsert \
--update-key country_id
5.3 Sqoop导出parquet格式的数据
导入数据到HDFS中为parquet格式：
[root@qf01 sqoop-1.4.7]# sqoop import --connect jdbc:mysql://qf01:3306/qfdb \
--username root \
--password 123456 \
--table u2 \
--delete-target-dir \
--target-dir '/sqoopdata/u2' \
-m 1 \
--fields-terminated-by '\t' \
--null-string '\\N' \
--null-non-string '0' \
--as-parquetfile
5.4 Sqoop的Hcatalog方式
概括来说，HCatalog 提供了一个统一的元数据服务，允许不同的工具如 Pig、MapReduce 等通过 HCatalog 直接访问存储在 HDFS 上的底层文件。
HCatalog 使用了 Hive 的元数据存储，这样就使得像 MapReduce 这样的第三方应用可以直接从 Hive 的数据仓库中读写数据。同时，HCatalog 还支持用户在 MapReduce 程序中只读取需要的表分区和字段，而不需要读取整个表。也就是提供一种逻辑上的视图来读取数据，而不仅仅是从物理文件的维度。
HCatalog 还提供了一个消息通知服务，这样对于 Oozie 这样的工作流工具，在数据仓库提供新数据时，可以通知到这些工作流工具。
那么写到这里，就已经很清晰了，HCatalog 主要解决了这样一个问题：将以前各自为政的数据处理工具（如 Hive、Pig、MapReduce）有机的整合在一起，使其相互之间能够顺畅合作，进而提升效率。
# 创建表：
CREATE TABLE `par` (
`id` int(11) NOT NULL DEFAULT '0',
`age` int(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# hive创建表：
hive> create table if not exists par(
`id` int,
`age` int
)
row format delimited fields terminated by '\t'
stored as parquet
location '/sqoopdata/u2/';
将hive包中的lib目录下的hcatalog相关包拷贝到sqoop的lib目录中去：
考虑将hcatalog的包放到sqoop中或者做环境变量。
必须保证hive对mysql有执行权限
grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
导出parquet格式语句：
[root@qf01 sqoop-1.4.7] sqoop export \
--connect jdbc:mysql://qf01:3306/qfdb \
--username root \
--password 123456 \
--table par \
--hcatalog-database default \
--hcatalog-table par \
-m 1
参数说明：
--table：MySQL库中的表名
--hcatalog-database：Hive中的库名
--hcatalog-table：Hive库中的表名，需要抽数的表
6 Sqoop的Job
6.1 Job操作
job的好处：
1、一次创建，后面不需要创建，可重复执行job即可
2、它可以帮我们记录增量导入数据的最后记录值
3、job的元数据默认存储目录：$HOME/.sqoop/
4、job的元数据也可以存储于mysql中。
Sqoop提供一系列的Job语句来操作Sqoop。
$ sqoop job (generic-args) (job-args) [-- [subtool-name] (subtool-args)]
$ sqoop-job (generic-args) (job-args) [-- [subtool-name] (subtool-args)]
使用方法：
usage: sqoop job [GENERIC-ARGS] [JOB-ARGS] [-- [<tool-name>] [TOOL-ARGS]]
Job management arguments:
--create <job-id> Create a new saved job
--delete <job-id> Delete a saved job
--exec <job-id> Run a saved job
--help Print usage instructions
--list List saved jobs
--meta-connect <jdbc-uri> Specify JDBC connect string for the
metastore
--show <job-id> Show the parameters for a saved job
--verbose Print more information while working
列出Sqoop的Job：
[root@qf01 sqoop-1.4.7] sqoop job --list
创建一个Sqoop的Job：
[root@qf01 sqoop-1.4.7]# sqoop job --create sq2 -- import --connect jdbc:mysql://qf01:3306/qfdb \
--username root \
--password 123456 \
--table u2 \
--delete-target-dir \
--target-dir '/sqoopdata/u3' \
-m 1
注意:第一行的--与import之间有空格
执行Sqoop的Job：
#如报错json包找不到，则需要手动添加
sqoop job --exec sq1
执行的时候会让输入密码：
输入该节点用户的对应的密码即可
# 1、配置客户端记住密码(sqoop-site.xml)追加
<property>
<name>sqoop.metastore.client.record.password</name>
<value>true</value>
</property>
# 2、将密码配置到hdfs的某个文件，我们指向该密码文件
说明：在创建Job时，使用--password-file参数，而且非--passoword。主要原因是在执行Job时使用--password参数将有警告，并且需要输入密码才能执行Job。当我们采用--password-file参数时，执行Job无需输入数据库密码。
[root@qf01 sqoop-1.4.7]# echo -n "123456" > sqoop.pwd
[root@qf01 sqoop-1.4.7]# hdfs dfs -mkdir /input
[root@qf01 sqoop-1.4.7]# hdfs dfs -put sqoop.pwd /input/sqoop.pwd
[root@qf01 sqoop-1.4.7]# hdfs dfs -chmod 400 /input/sqoop.pwd
[root@qf01 sqoop-1.4.7]# hdfs dfs -ls /input
-r-------- 1 hadoop supergroup 6 2018-01-15 18:38 /input/sqoop.pwd
# 3. 重新创建Job
sqoop job --create u2 -- import --connect jdbc:mysql://qf01:3306/qfdb --username root --table u2 --delete-target-dir --target-dir '/sqoopdata/u3' -m 1 --password-file '/input/sqoop.pwd'
查看Sqoop的Job:
[root@qf01 sqoop-1.4.7] sqoop job --show sq1
删除Sqoop的Job:
[root@qf01 sqoop-1.4.7] sqoop job --delete sq1
问题:
1 创建job报错：19/12/02 23:29:17 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.NullPointerException
java.lang.NullPointerException
at org.json.JSONObject.<init>(JSONObject.java:144)
解决办法：
添加java-json.jar包到sqoop的lib目录中。
如果上述办法没有办法解决，请注意hcatlog的版本是否过高，过高将其hcatlog包剔除sqoop的lib目录即可。
2 报错：Caused by: java.lang.ClassNotFoundException: org.json.JSONObject
解决办法：
添加java-json.jar包到sqoop的lib目录中。
7 Sqoop优化
7.1 -m与split-by的优化
- 小量数据时(200M左右) :最好使用一一个map，快且减少小文件。
- 大量数据时:要特别考虑数据的特征，对于split- by最完美的情况是有一个:均匀分布的数字(如自增列)或时间字段，且这个字段还有索引(最好字段是int、tinyin)，这样在抽取时使得并发的每个sq1处理相近的数据量，并且Sqoop附加的where条件可以使用索引。
- split-by id，-m 2， 数据量1-100。第 一个mapper:(0,50]第二个mapper: (50, 100],对于m要综合考虑数据量、I0、源数据库的性能、集群的资源等等。一种简单的考虑是最大不超过yarn.上分配给这个用户的core个数，最小“数据量/m”要够一个128MB的文件。如果条件允许可以先设置一个值跑着试试，然后观察源数据库负载、集群I0以及运行时长等，再进行相应调整。
7.2 --fetch-size n
一次取MySql中批量读取的数据条数。建议优化如下:
- 考虑一条数据的量。(如果2个字段和200个字段的–fetch-size不能一样)
- 考虑数据库的性能
- 考虑网络速度
- 最好的状态是一 次–fetch-size能满足一个mapper
1624
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
