Sqoop学习笔记：Sqoop安装及使用

Sqoop的安装

测试环境

hive-1.1.0-cdh5.14.0
hbase-1.2.0-cdh5.14.0
hadoop-2.6.0-cdh5.14.0
zookeeper-3.4.5-cdh5.14.0
jdk1.8.0_171
sqoop-1.4.6-cdh5.14.0

1.解压Sqoop到/opt/software下
2.拷贝mysql-connector-java-5.1.40-bin和java-json.jar到sqoop-1.4.6-cdh5.14.0\lib下
3.配置添加环境变量，并使环境变量生效

HIVE_CONF_DIR=/opt/software/hive-1.1.0-cdh5.14.0
SQOOP_HOME=/opt/software/sqoop-1.4.6-cdh5.14.0
HADOOP_COMMON_LIB_NATIVE_DIR=$HADOOP_HOME/lib/native
HADOOP_OPTS="-Djava.library.path=$HADOOP_HOME/lib/native"
HADOOP_COMMON_HOME=/opt/software/hadoop-2.6.0-cdh5.14.0
HADOOP_MAPRED_HOME=/opt/software/hadoop-2.6.0-cdh5.14.0
HADOOP_CLASSPATH=$HIVE_HOME/lib/*

export JAVA_HOME
export HIVE_CONF_DIR
export HADOOP_CLASSPATH
export HADOOP_COMMON_HOME
export HADOOP_MAPRED_HOME

PATH=$JAVA_HOME/bin:$JRE_HOME/bin:$HADOOP_HOME/bin:$HIVE_HOME/bin:$HBASE_HOME/bin:$ZOOKEEPER_HOME/bin:$PIG_HOME/bin:$SQOOP_HOME/bin:$ECLIPSE_HOME:$HADOOP_HOME/sbin:$MAVEN_HOME/bin:$PROTOBUF_HOME/bin:$PHONEIX_HOME/bin:$PATH
export PATH

4.找到 sqoop-env-template.sh，修改为 sqoop-env.sh，并将相关变量的值配上对应路径

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

5.检查sqoop是否正常运行

Sqoop version

Sqoop原理

导入：在导入开始之前，Sqoop使用JDBC来检查将要导入的表，检索出表中所有的字段和字段的数据类型，这些数据类型将会被映射成Java的数据类型，生成反序列化代码和配置InputFormat后，Sqoop将Job发送到集群，使用只有Map的MapReduce任务执行查询并将ResultSet数据反序列化到生成类的实例，这些数据会被保存在SequenceFile文件中，或者在写到HDFS前被转换成分割的文本。在向HDFS导入数据时，重要的是确保访问的是数据源的一致性快照。

导出：将HDFS作为数据源，远程的数据库作为目标，在导出的时候必须在数据库中创建一张用于接收数据的目标表。在执行导出之前，Sqoop会使用JDBC作为导出，然后Sqoop会根据目标表的定义生成一个Java类，这个类能够从文本中解析记录，并且向表中插入类型合适的值。接着启动MapReduce任务，从HDFS中读取数据文件，使用生成的类解析记录，通过JDBC产生一批insert语句，向目标表插入记录，该过程使用多线程操作。在启动导出作业前，应当在数据库中设置表的约束（例如定义一个主键），以保证数据行的唯一性，并且，在导出过程完成前，不要对目标表进行操作以保证数据的完整和一致。

Sqoop命令

codegen            将关系型数据库表的记录映射为一个java文件，相关的class文件和生成文件会被打包为 
                   jar包
create-hive-table  生成与关系数据库表的表结构对应的Hive表
eval               让Sqoop使用SQL语句对关系型数据库进行操作，用以评估计算SQL语句
export             从HDFS中将数据导出到关系型数据库中
help               列出可用的命令
import             将数据表中的数据导入到HDFS或Hive中
import-all-tables  将数据库里所有表导入HDFS中，每个表在HDFS中对应一个独立的目录
import-mainframe   将一个数据集整个导入到HDFS中
job                产生一个Sqoop的作业，但不会立即执行，需要手动执行
list-databases     列出关系型数据库的所有数据库名
list-tables        列出某个数据库中的所有表名
merge              将HDFS上的两份数据进行合并，在合并的同时进行数据去重
metastore          记录Sqoop的元数据信息，如果不启动MetaStore实例，则默认的元数据存储目录为 
                   ~/.sqoop，可以在sqoop-site.xml中对该存储目录进行修改
version            显示版本信息

注意：Sqoop输入命令时可用 \ 作为分行，这样看起来可读性较高

list-databases示例

[[e-mail] software]# sqoop list-databases \
> --connect jdbc:mysql://centos:3306/ \
> --username root \
> --password root

list-tables示例

[[e-mail] software]# sqoop list-tables \
> --connect jdbc:mysql://centos:3306/test \
> --username root \
> --password root

创建一张跟mysql中的help_keyword表一样的hive表

[[e-mail] software]# sqoop create-hive-table \
> --connect jdbc:mysql://centos:3306/mysql \
> --username root \
> --password root \
> --table help_keyword \
> --hive-table help_keyword

 

Sqoop数据导入：从RDBMS到HDFS

这里的样例是将mysql中的help_keyword表用不同的参数执行导入

1.不带其他参数，仅导入，-m参数指定的是mapper的个数

sqoop import \
> --connect jdbc:mysql://centos:3306/mysql \
> --username root \
> --password root \
> --table help_keyword \
> --m 1

没有指定导入路径的情况下， 数据会存放在HDFS的/user/root中

2.指定分隔符和导入路径，注意，--target-dir指定的路径在HDFS中是不可以先存在的，如果存在要先删除，还是因为MapReduce的锅233333

sqoop import \
> --connect jdbc:mysql://hadoop111:3306/mysql \
> --username root \
> --password root \
> --table help_keyword \
> --target-dir /sqoop_output \
> --fields-teminated-by ',' \
> --m 1

Sqoop数据导入：增量导入

       生产情况下，表的数据总是在不断增加的，如果每次导入到HDFS都执行全部导入的话费时费力，这个时候就可以使用增量导入。使用增量导入需要指定一些参数：
       --incremental append：表示使用增量导入
       --check-column：使用哪一列作为增量的标准，比如表里的主键
       --last-value：上一次导入的最后一个值，这里可以手动指定，或者使用Job来进行记忆

sqoop import \
> --connect jdbc:mysql://centos:3306/mysql \
> --username root \
> --password root \
> --table help_keyword \
> --target-dir /sqoop_incremental
> --incremental append \
> --check-column help_keyword_id \
> --last-value 500
> --m 1

Sqoop数据导入：从RDBMS导入到Hive

向Hive中导入数据，需要启动Hive的服务，以及Sqoop有Hive相关的jar包

sqoop import \
> --connect jdbc:mysql://centos:3306/mysql \
> --username root \
> --password root \
> --table help_keyword \
> --target-dir /user/sqoop6 \
> --hive-import
> --m 1

如果默认不指定路径，Sqoop会在/user/hive/warehouse下创建路径，名字和表名一样。
Sqoop一般会跟Hive的外部表进行搭配，默认的分隔符为"\u0001"。
同样的，导入到Hive也支持增量导入。

Sqoop数据导出：从RDBMS导出到HDFS

sqoop export \
> --connect jdbc:mysql://centos:3306/sqoopdb \
> --username root \
> --password root \
> --table student \
> --export-dir /sqoopExport \
> --fields-terminated-by ','

Sqoop数据导出：从RDBMS导出到Hive

sqoop export \
--connect jdbc:mysql://centos:3306/sqoopdb \
--username root \
--password root \
--table person \
--export-dir /user/hive/warehouse/person \
--fields-terminated-by ','
--m 1

事实上，用import/export都是可以完成相互之间的数据传递的，上面只是例子，看心情写就好>,<

Sqoop杂项

1.密码保护

在Sqoop命令中显式指定密码是很不安全的操作，所以可以使用-P参数在执行命令时输入密码

2.压缩与并行控制

可以使用--compress/-z参数，使数据在HDFS中压缩，默认的压缩算法是GZip，但可以使用--compression-codec参数对使用的压缩算法进行指定，比如org.apache.hadoop.io.compress.SnappyCodec
默认情况下Sqoop的并行数是4，我们可以使用-num-mappers/-m 参数对mapper的数量进行指定
在使用压缩之前，需要使用hadoop checknative检查本地集群环境所支持的压缩算法

3.只导入部分数据

（1）使用query参数通过查询语句导出部分的数据

sqoop import  \
--connect jdbc:mysql://centos:3306/mysql \
--username root \
--query 'select help_keyword_id from help_keyword where name = "STRING" and $CONDITIONS' \
--delete-target-dir \
--target-dir /user/sqoop20 \
--split-by help_keyword_id \
-P \
--m 1

其中--delete-target-dir参数的作用是，如果指定目录存在，则先删除掉
--query参数后面跟查询语句，注意，where子句必须要有and $CONDITIONS，在实际执行产生的log中可以发现这个变量被替换为了1=1之类的存在，具体作用还没有弄明白
--split-by参数，指定某个字段作为分割数据的根据，一般指定主键列，然后按mapper的个数进行切分

（2）使用columns参数导出指定的字段

sqoop import  \
--connect jdbc:mysql://centos:3306/mysql \
--username root \
--table help_keyword \
--columns help_keyword_id \
--delete-target-dir \
--target-dir /user/sqoop20 \
--split-by help_keyword_id \
-P \
--m 1

（3）使用where参数导出符合条件的数据

sqoop import  \
--connect jdbc:mysql://centos:3306/mysql \
--username root \
--table help_keyword \
--where "name='STRING'" \
--delete-target-dir \
--target-dir /user/sqoop20 \
--split-by help_keyword_id \
-P \
--m 1

4.设置导出文件格式

这里使用parquet文件格式作为示例

sqoop import  \
--connect jdbc:mysql://centos:3306/mysql \
--username root \
--table help_keyword \
--where "name='STRING'" \
--delete-target-dir \
--target-dir /user/sqoop20 \
--split-by help_keyword_id \
-P \
--m 1 \
--as-parquetfile

5.对Null值编码

对于文本类型的列，使用--null-string来设置替代NULL值的字符串；
对于其他类型的列，使用--null-non-string来设置非NULL值
如果想使用\N来进行编码，则参数后面跟的值要使用"\"进行转义，即'\\N'

6.快速导入模式

       添加--direct参数，可以使用快速模式进行导入，但是注意Sqoop的快速导入模式只支持MySQL和PostageSQL。在direct模式下使用数据库提供的本地工具进行数据传输，比如MySQL的mysqldump和mysqlimport，PostageSQL的pg_dump，而且在文件格式上也只支持sequencefile和avrodatafile。

7.使用Sqoop Job

       Job可以用来创建和处理保存的作业，保存的作业里存放了指定作业所使用的参数，因此可以通过重新调用该作业来执行相关语句。如果保存的是增量导入作业，则会在保存的Job中对最近导入的行进行状态更新，使该作业只导入最新行。默认情况下，Job的描述（metastore）会保存在~/.sqoop下

创建一个增量导入的Job，以及Job相关命令

sqoop job --create helpJob \
-- import  \
-- connect jdbc:mysql://centos:3306/mysql \
-- username root \
-- table help_keyword \
-- where "name='STRING'" \
-- delete-target-dir \
-- target-dir /user/sqoop20 \
-- split-by help_keyword_id \
-P \
--m 1

//执行job
sqoop job --exec helpjob
//删除job
sqoop job --delete helpjob
//查看job
sqoop job --show helpjob

通过show参数可以查看last-value的当前值

8.脚本实现

可以在脚本中添加变量，然后在sqoop的语句中引用它们，这样有利于脚本的复用

9.导入时删除string类型字段的特殊字符

如果指定了\n为Sqoop导入的换行符，mysql的某个string字段的值如果包含了\n，则会导致Sqoop导入多出一行记录

参考

http://sqoop.apache.org/docs/1.4.6/SqoopUserGuide.html
https://www.cnblogs.com/xiaodf/p/6030102.html