一、sqoop概述
1.1 简介
sqoop用于在hadoop（hdfs、hive）和关系型数据库等结构化数据存储之间相关导数据的场景。Sqoop于2012年3月孵化出来，现在是一个顶级的Apache项目。
请注意，1.99.7与1.4.6不兼容，且特征不完整，它并不打算用于生产部署。
1.2 基本原理
将导入或导出命令翻译成mapreduce程序来实现。
在翻译出的mapreduce中主要是对inputformat和outputformat进行定制。
二、部署sqoop
首先得准备好hadoop和java环境，这里不重复说。
这里使用 sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz。
解压程序：
tar -zxvf sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz -C /opt/modules/修改配置文件：
进入sqoop的解压路径，进入conf目录
mv sqoop-env-template.sh sqoop-env.sh
修改内容：
#export HADOOP_COMMON_HOME=
export HADOOP_COMMON_HOME=/opt/modules/hadoop-2.8.4
#Set path to where hadoop-*-core.jar is available
#export HADOOP_MAPRED_HOME=
export HADOOP_MAPRED_HOME=/opt/modules/hadoop-2.8.4
#Set the path to where bin/hive is available
#export HIVE_HOME=
export HIVE_HOME=/opt/modules/hive-1.2.1-bin
#Set the path for where zookeper config dir is
#export ZOOCFGDIR=
export ZOOCFGDIR=/opt/modules/zookeeper-3.4.10/conf准备需要连接的关系型数据库的连接驱动，这里使用mysql，所以下载 mysql-connector-java-5.1.27-bin.jar，然后
cp -a mysql-connector-java-5.1.27-bin.jar /opt/module/sqoop-1.4.6.bin__hadoop-2.0.4-alpha/lib接着配置环境变量：
vim /etc/profile.d/sqoop.sh
#!/bin/bash
export SQOOP_HOME=/opt/modules/sqoop-1.4.6.bin__hadoop-2.0.4-alpha
export PATH=$PATH:${SQOOP_HOME}/bin验证sqoop：
sqoop help三、sqoop简单使用案例
在详细说明sqoop的用法之前，先来点简单案例熟悉下
3.1 导入数据
在Sqoop中，“导入”概念指：从非大数据集群（RDBMS）向大数据集群（HDFS，HIVE，HBASE等）中传输数据，叫做：导入，即使用import关键字。
3.1.1 RDBMS到HDFS
先在mysql中准备些数据：
$ mysql -uroot -p000000
mysql> create database company;
mysql> create table company.staff(id int(4) primary key not null auto_increment, name varchar(255), sex varchar(255));
mysql> insert into company.staff(name, sex) values('Thomas', 'Male');
mysql> insert into company.staff(name, sex) values('Catalina', 'FeMale');导入全部数据：
sqoop import /
--connect jdbc:mysql://bigdata121:3306/company / jdbc的连接串
--username root / 用户名
--password 000000 / 密码
--table staff / 数据源的表
--target-dir /user/company / hdfs指定目录
--delete-target-dir / 目标目录存在就删除
--num-mappers 1 / map数目
--fields-terminated-by "/t" 输出到hdfs的分隔符查询导入：
sqoop import /
--connect jdbc:mysql://bigdata121:3306/company /
--username root /
--password 000000 /
--target-dir /user/company /
--delete-target-dir /
--num-mappers 1 /
--fields-terminated-by "/t" / 写入到hdfs文件中的字段分隔符
--query 'select name,sex from staff where id <=3 and $CONDITIONS;' 执行的sql查询语句
尖叫提示1：must contain '$CONDITIONS' in WHERE clause.即where语句的最后面一定要加上 and $CONDITIONS
尖叫提示2：如果query后使用的是双引号，则$CONDITIONS前必须加转义符，防止shell识别为自己的变量。导入指定列：
$ bin/sqoop import /
--connect jdbc:mysql://bigdata113:3306/company /
--username root /
--password 000000 /
--target-dir /user/company /
--delete-target-dir /
--num-mappers 1 /
--fields-terminated-by "/t" /
--columns id,sex / 导入指定列
--table staff
尖叫提示：columns中如果涉及到多列，用逗号分隔，分隔时不要添加空格
像这种只是简单对列进行筛选的，用上面的方式就好，如果需要进行where之类的条件筛选，那么还是使用查询导入使用sqoop关键字筛选查询导入数据：
sqoop import /
--connect jdbc:mysql://bigdata113:3306/company /
--username root /
--password 000000 /
--target-dir /user/company /
--delete-target-dir /
--num-mappers 1 /
--fields-terminated-by "/t" /
--table staff /
--where "id=2" 这个其实相当于select中的where语句
这个方式其实就相当于--query方式进行查询导入
尖叫提示：在Sqoop中可以使用sqoop import -D property.name=property.value这样的方式加入执行任务的参数，多个参数用空格隔开。3.1.2 RDBMS到hive
sqoop import /
--connect jdbc:mysql://bigdata113:3306/Andy /
--username root /
--password 000000 /
--table aca /
--num-mappers 1 /
--hive-import / 指定导入到hive中
--fields-terminated-by "/t" / 指定字段分隔符
--hive-overwrite / 覆盖导入
--hive-table staff_hive 指定导入到hive中的表
尖叫提示1：从MYSQL到Hive，本质是从MYSQL => HDFS => load To Hive
尖叫提示2：hive表不存在时，自动创建。表存在默认就会自动覆盖数据3.2 导出数据
在Sqoop中，“导出”概念指：从大数据集群（HDFS，HIVE，HBASE等）向非大数据集群（RDBMS）中传输数据，叫做：导出，即使用export关键字。
mysql创建aca表
create table abc(id int,name VARCHAR(5));
hive导入mysql
sqoop export /
--connect jdbc:mysql://bigdata113:3306/test/
--username root /
--password 000000 /
--export-dir /user/hive/warehouse/staff_hive /
--table abc /
--num-mappers 1 /
--input-fields-terminated-by "/t"
尖叫提示1：Mysql中如果表不存在，不会自动创建。表不存在会报错
尖叫提示2：数据是追加的，不是覆盖3.3 脚本打包
使用opt格式文件打包sqoop命令，文件后缀必须是.opt
vi ./job_HDFS2RDBMS.opt
#以下命令是从staff_hive中追加导入到mysql的aca表中
# 格式基本上是 option /n value
export
--connect
jdbc:mysql://bigdata113:3306/Andy
--username
root
--password
000000
--table
aca
--num-mappers
1
--export-dir
/user/hive/warehouse/staff_hive
--input-fields-terminated-by
"/t"
格式必须验证按照上面的写，选项和参数各自独立一行执行脚本：
sqoop --options-file job_HDFS2RDBMS.opt四、sqoop常用参数
sqoop的用法其实是以 sqoop subcommand options 的形式的，有很多子命令，选项也有公有选项以及独有选项，下面看看每个子命令的用法
4.1 数据库连接参数
| 参数 | 说明 |
|---|---|
| –connect | 连接关系型数据库的URL |
| –driver | Hadoop根目录 |
| –connection-manager | 指定要使用的连接管理类 |
| –password | 连接数据库的密码 |
| –username | 连接数据库的用户名 |
4.2 import公用参数
| 参数 | 说明 |
|---|---|
| –enclosed-by <char> | 给字段值前加上指定的字符 |
| –escaped-by <char> | 对字段中的双引号加转义符 |
| –fields-terminated-by <char> | 设定每个字段是以什么符号作为结束，默认为逗号 |
| –lines-terminated-by <char> | 设定每行记录之间的分隔符，默认是/n |
4.3 export公用参数
| 参数 | 说明 |
|---|---|
| –input-enclosed-by <char> | 对字段值前后加上指定字符 |
| –input-escaped-by <char> | 对含有转移符的字段做转义处理 |
| –input-fields-terminated-by <char> | 字段之间的分隔符 |
| –input-lines-terminated-by <char> | 行之间的分隔符 |
4.4hive的公用参数
| 参数 | 说明 |
|---|---|
| –hive-delims-replacement <arg> | 用自定义的字符串替换掉数据中的/r/n和/013 /010等字符 |
| –hive-drop-import-delims | 在导入数据到hive时，去掉数据中的/r/n/013/010这样的字符 |
| –map-column-hive <arg> | 生成hive表时，可以更改生成字段的数据类型 |
| –hive-partition-key | 创建分区，后面直接跟分区名，分区字段的默认类型为string |
| –hive-partition-value <v> | 导入数据时，指定某个分区的值 |
| –hive-import | 将数据从关系数据库中导入到hive表中 |
| –hive-overwrite | 覆盖掉在hive表中已经存在的数据 |
| –create-hive-table | 默认是false，即，如果目标表已经存在了，那么创建任务失败。 |
| –hive-table | 后面接要创建的hive表,默认使用MySQL的表名 |
| –table | 指定关系数据库的表名 |
4.5 import–导入到hadoop
将关系型数据库中的数据导入到HDFS（包括Hive，HBase）中，如果导入的是Hive，那么当Hive中没有对应表时，则自动创建。导入数据时，默认是追加的方式。
导入到hive中
sqoop import /
--connect jdbc:mysql://bigdata113:3306/Andy /
--username root /
--password 000000 /
--table access /
--hive-import /
--fields-terminated-by "/t"增量导入：
有几个关键参数
| 参数 | 说明 |
|---|---|
| –check-column 字段名 | 检查增量的字段名 |
| –incremental append | lastmodified | 两种增加方式，后面有讲区别 |
| –last-value value | 指定增量的最后一个值的界限 |
append模式：
sqoop import /
--connect jdbc:mysql://bigdata113:3306/Andy /
--username root /
--password 000000 /
--table aca /
--num-mappers 1 /
--fields-terminated-by "/t" /
--target-dir /user/hive/warehouse/staff_hive /
--check-column id /
--incremental append /
--last-value 10 在check-column指定的字段中，下一次导入的开始的行的id的值
尖叫提示1：append不能与--hive-等参数同时使用
尖叫提示2：如果 --last-value N , N > MYSQL中最大行数，则HDFS会创建一个空文件。如果N<=0 , 那么就是所有数据lastmodified模式：
先在mysql中建表并插入几条数据：
mysql> create table company.staff_timestamp(id int(4), name varchar(255), sex varchar(255), last_modified timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
mysql> insert into company.staff_timestamp (id, name, sex) values(1, 'AAA', 'female');
mysql> insert into company.staff_timestamp (id, name, sex) values(2, 'BBB', 'female');
先导入一部分数据：
$ bin/sqoop import /
--connect jdbc:mysql://bigdata113:3306/company /
--username root /
--password 000000 /
--table staff_timestamp /
--delete-target-dir /
--hive-import /
--fields-terminated-by "/t" /
--m 1
再增量导入一部分数据:
mysql> insert into company.staff_timestamp (id, name, sex) values(3, 'CCC', 'female');
$ bin/sqoop import /
--connect jdbc:mysql://bigdata113:3306/company /
--username root /
--password 000000 /
--table staff_timestamp /
--check-column last_modified /
--incremental lastmodified / 指定为lastmodified模式
--m 1 /
--last-value "2019-05-17 09:50:12" /
--append 指定为追加模式
尖叫提示1：使用lastmodified方式导入数据要指定增量数据是要--append（追加）还是要--merge-key COLUMN（合并）
尖叫提示2：在Hive中，如果不指定输出路径，可以去看以下两个目录
1. /user/root（此为用户名）追加模式中会将文件输出到这里
如果需要导入到hive中的表时，需要—target-dir 指定输出目录到hive的目录
2. /user/hive/warehouse 个人配置的目录，普通模式下会输出到这里
尖叫提示3：last-value指定的值是会包含于增量导入的数据中
注意：lastmodified模式下，如果不指定输出hdfs路径，就是输出到/user/root/TABLE_NAME/ 下，不会再 /user/hive/warehouse/ 下，这点要注意Last-modified和append的区别：
1、append，在导入的新数据ID值是连续时采用，对数据进行附加 加不加–last-value的区别在于：数据是否冗余，如果不加，则会导入源表中的所有数据导致数据冗余。加了就表示将大于 last-value值的行导入，小于的就不导入。而且作为 –check-column的字段必须是可比较的类型，字符是不行的，通常是数字，比如表的主键id
2、lastmodified，在源表中有数据更新的时候使用，检查列就必须是一个时间戳或日期类型的字段，更新完之后，last-value会被设置为执行增量导入时的当前系统时间，当使用–incremental lastmodified模式进行导入且导入目录已存在时，需要使用–merge-key或–append ，--merge-key会将所有的行根据指定的key进行合并
导入>=last-value的值。--incremental lastmodified --check-column created --last-value '2012-02-01 11:0:00'
就是只导入修改时间 比'2012-02-01 11:0:00'更大的数据，如果存在就覆盖。
--append 就是将指定时间之后的数据追加，不会做覆盖4.6 export导出数据到mysql
从HDFS（包括Hive和HBase）中将数据导出到关系型数据库中。
从hdfs导出到mysql
sqoop export /
--connect jdbc:mysql://bigdata113:3306/Andy /
--username root /
--password 000000 /
--export-dir /user/hive/warehouse/staff_hive / hdfs导出路径
--table aca /
--num-mappers 1 /
--input-fields-terminated-by "/t"4.7 codegen–数据打包成jar
将关系型数据库中的表映射为一个Java类，在该类中有各列对应的各个字段。
sqoop codegen /
--connect jdbc:mysql://bigdata113:3306/company /
--username root /
--password 000000 /
--table staff /
--bindir /opt/Desktop/staff /
--class-name Staff /
--fields-terminated-by "/t"
用法和import类似，只是把import换成codegen，打包成jar，但是不执行。
默认会在/tmp/sqoop-root/compile/[JOB_ID]/ 生成对应的jar包常用参数：
| 参数 | 说明 |
|---|---|
| –bindir <dir> | 指定生成的Java文件、编译成的class文件及将生成文件打包为jar的文件输出路径 |
| –class-name <name> | 设定生成的Java文件指定的名称 |
4.8 create-hive-table创建hive表
用于单独创建hive表
sqoop create-hive-table /
--connect jdbc:mysql://bigdata113:3306/company /
--username root /
--password 000000 /
--table staff /
--hive-table hive_staff1 指定创建的表名4.9 eval–在命令行下执行sql语句
可以快速的使用SQL语句对关系型数据库进行操作，经常用于在import数据之前，了解一下SQL语句是否正确，数据是否正常，并可以将结果显示在控制台。
sqoop eval /
--connect jdbc:mysql://bigdata113:3306/company /
--username root /
--password 000000 /
--query "SELECT * FROM staff" 查询语句4.10 import-all-tables导入所有表
可以将RDBMS中的所有表导入到HDFS中，每一个表都对应一个HDFS目录
bin/sqoop import-all-tables /
--connect jdbc:mysql://bigdata113:3306/company /
--username root /
--password 000000 /
--hive-import /
--fields-terminated-by "/t"4.11 job生成sqoop任务
用来生成一个sqoop任务，生成后不会立即执行，需要手动执行。
生成job
$ bin/sqoop job /
--create myjob -- import-all-tables /
--connect jdbc:mysql://bigdata113:3306/company /
--username root /
--password 000000
显示job
$ bin/sqoop job /
--list
执行job
$ bin/sqoop job /
--exec myjob
尖叫提示：注意import-all-tables和它左边的--之间有一个空格
尖叫提示：如果需要连接metastore，则--meta-connect
执行的结果在HDFS：/user/root/ 目录中，即导出所有表到/user/root中
4.12 list-databases和list-tables
显示mysql库：
sqoop list-databases /
--connect jdbc:mysql://bigdata113:3306/ /
--username root /
--password 000000显示mysql表：
sqoop list-tables /
--connect jdbc:mysql://bigdata113:3306/company /
--username root /
--password 0000004.13 merge合并hdfs文件
将HDFS中不同目录下面的数据合并在一起并放入指定目录中
数据环境：注意:以下数据自己手动改成/t
new_staff
1 AAA male
2 BBB male
3 CCC male
4 DDD male
old_staff
1 AAA female
2 CCC female
3 BBB female
6 DDD female
尖叫提示：上边数据的列之间的分隔符应该为/t，行与行之间的分割符为/n，如果直接复制，请检查。
开始合并
创建JavaBean：将数据以及任务打包成jar包
$ bin/sqoop codegen /
--connect jdbc:mysql://bigdata113:3306/company /
--username root /
--password 000000 /
--table staff /
--bindir /opt/Desktop/staff /
--class-name Staff /
--fields-terminated-by "/t"
开始合并：注：是hdfs路径，运行上面的jar包.记得将上面的数据文件上传到hdfs
$ bin/sqoop merge /
--new-data /test/new/ /
--onto /test/old/ /
--target-dir /test/merged /
--jar-file /opt/Desktop/staff/Staff.jar /
--class-name Staff /
--merge-key id
结果：
1 AAA MALE
2 BBB MALE
3 CCC MALE
4 DDD MALE
6 DDD FEMALE参数：
| 参数 | 说明 |
|---|---|
| –new-data <path> | HDFS 待合并的数据目录，合并后在新的数据集中保留（重复的话这里的数据保留） |
| –onto <path> | HDFS合并后，重复的部分在新的数据集中被覆盖（重复的话这里的数据被覆盖） |
| –merge-key <col> | 合并键，一般是主键ID |
| –jar-file <file> | 合并时引入的jar包，该jar包是通过Codegen工具生成的jar包 |
| –class-name <class> | 对应的表名或对象名，该class类是包含在jar包中的 |
| –target-dir <path> | 合并后的数据在HDFS里存放的目录 |
原创文章，作者：carmelaweatherly，如若转载，请注明出处：https://blog.ytso.com/185012.html