Sqoop使用手册

 

• 1 Sqoop概述
• 2 版本说明
• 3 驱动安装
  • 3.1 MySQL
• 4 基本用法
  • 4.1 导入
    • 4.1.1 保护密码
    • 4.1.2 使用其他文件格式
    • 4.1.3 压缩导入的数据
    • 4.1.4 提高传输速度
    • 4.1.5 自定义类型映射
    • 4.1.6 并行控制
    • 4.1.7 对NULL值进行编码
    • 4.1.8 导入所有表
  • 4.2 增量导入
    • 4.2.1 只导入细腻数据
    • 4.2.2 增量导入可变数据
    • 4.2.3 保存last-value
    • 4.2.4 在metastore中保存密码
    • 4.2.5 客户端之间共享metastore
  • 4.3 导出
    • 4.3.1 hive导出数据到mysql
    • 4.3.2 批量插入
    • 4.3.3 导出所有数据或不导出任何数据
    • 4.3.4 更新已有数据
    • 4.3.5 更新或插入数据
    • 4.3.6 只导出某些列
    • 4.3.7 编码NULL值
• 5 附：import和export参数详解
  • 5.1 import和export工具通用选项
  • 5.2 数据导入工具import参数详解
  • 5.3 数据导出工具export参数详解
  • 5.4 Sqoop Job参数详解

<span id="1"></span>

1 Sqoop概述

Sqoop是一个旨在Hadoop和关系数据库或主机之间传输数据的工具。你可以使用Sqoop从关系型数据库管理系统（RDBMS），如MySQL、Oracle或大型机到Hadoop分布式文件系统（HDFS）导入数据，在Hadoop中的MapReduce转换数据，然后将数据导出到一个RDBMS 。Sqoop使用MapReduce导入和导出数据，它提供了并行操作，以及容错。

<span id="2"></span>

2 版本说明

本文档适用于Sqoop1。

软件	版本
Sqoop	1.4.5-cdh5.3.0
Mysql JDBC驱动	5.1.32
ORACLE JDBC驱动	和oracle版本保持一致

<span id="3"></span>

3 驱动安装

<span id="31"></span>

3.1 MySQL

将驱动对应的jar包放置到 /usr/share/java/下。

如：/usr/share/java/mysql-connector-java-5.1.32-bin.jar
<span id="4"></span>

4 基本用法

<span id="41"></span>

4.1 导入

sqoop import \
--connect  JDBC_URL      \
--username USERNAME      \
--password PASSWORD   \
--table TABLE        \
--hive-import        \
--split-by SPLIT_BY_COL \
--num-mappers N        \
--hive-database HIVE_DB_NAME    \ 
--hive-table HIVE_TABLE_NAME    \ 
(--hive-partition-key partition_name    \
--hive-partition-value partititon_value    \
附：如果是分区表则需指出分区名称和分区值)
--columns col1，clo2,col3…     \
--warehouse-dir /user/hive/warehouse/       \
--fields-terminated-by ‘|’     \
--direct                       \
--compress

参数说明

参数	说明
--connect	连接RDBMS的jdbc连接字符串。 示例：--connect jdbc:mysql:// MYSQL_SERVER: PORT / DBNAME 其中：mysql默认端口号为3306； 示例：--connect jdbc:oracle:thin:USERNAME/PASSWORD@ORACLE_SERVER:PORT: SID 其中：Oracle默认端口号为1521； thin：是驱动方式，“瘦”的意思，直接使用原生的Oracle JDBC驱动； SID：是一个数据库的唯一标识符，是建立一个数据库时系统自动赋予的一个初始ID。
--username	连接RDBMS所使用的用户名。
--password	连接RDBMS所使用的密码。
--table	将要导入到hive的表。
--split-by	分割导入任务所使用的字段。需要明确指定，推荐使用主键。
--hive-import	插入数据到hive当中，使用hive默认的分隔符。
-m, --num-mappers< n>	使用n个map任务并行导入数据。是指生成的map任务的总数量， 不是同时处于RUNNING状态的数量。
--hive-database	hive当中的数据库。
-- hive-table	hive当中的表名
--hive-partition-key	hive分区的列名 。
--hive-partition-value	hive分区的值。
--columns < col,col,col…>	从表中导出指定的一组列的数据，用逗号分隔， oracle中列名需要大写。
--warehouse-dir	（必选）可以指定为-warehouse-dir/user/hive/warehouse/ 即导入数据的存放路径，如果该路径不存在，会首先创建。 在该路径下会生成和TABLE（--table）同名的文件夹，该文件夹下存放数据文件。 如果路径存在，需要保证该文件夹下不存在与TABLE(--table)同名文件。 如果不手动指定，默认是启动sqoop任务的用户的home目录。
--direct	使用快速模式导入
--compress	启用压缩，生成的文件为经过压缩的文件。 默认使用GZIP算法。 通过--compression-codec设置压缩算法。 通常当空间不够时可以使用压缩，不过需要注意，如果压缩率过大可能导致CPU占用过高。 如果可以，推荐使用snappy。 另外，如果配置了mapreduce的“map输出压缩”， 那么即使不适用—compress开关， 导入的数据文件也会使用对应的codec进行压缩。
--compression-codec	使用Hadoop Codec。（默认gzip）前提是设置了—cpmpress。
其他	可使用 sqoop import 命令查看帮助。

附：对于--warehouse-dir需要指定为/user/hive/warehouse/但在该路径下不能存在与TABLE(--table)同名的文件，否则导入失败。当导入成功时，会在该路径下生成数据文件part-m-XXXXX并且生成与TABLE(--table)同名文件，存放导入成功的标志文件_SUCCESS。

<span id="411"></span>

4.1.1 保护密码

在sqoop命令中显式指定密码会是很不安全的操作，使用操作系统的列出正在执行的命令的方式可以很容易的获取到密码。有两种方式可以解决这个问题。
方式一：使用-P（大写）参数，在执行命令时再输入密码。
方式二：使用--password-file参数，即将密码存放在参数指定的文件中。
<span id="412"></span>

4.1.2 使用其他文件格式

Sqoop支持3中不同的文件格式，其中一种是文本格式，两外两种是二进制格式。二进制格式分别是Avro和SequenceFile。使用--as-avrodatafile或--as-sequencefile以指定具体使用哪种二进制格式。
<span id="413"></span>

4.1.3 压缩导入的数据

使用—compress或-z参数以压缩导入之后的数据。默认的压缩算法为GZip，所有文件的后缀名均为.gz。可以使用—compress-codec来指定其他的codec。如

--compression-codec org.apache.hadoop.io.compress.BZip2Codec

使用压缩需要将mapreduce中对应的设置项开启，如mapreduce.output.
compress。
<span id="414"></span>

4.1.4 提高传输速度

不同于JDBC接口，direct模式下使用数据库提供的本地工具进行数据传输。在MySQL中使用mysqldump和mysqlimport。对于PostgreSQL，sqoop会使用pg_dump工具来导入数据。使用本地工具会极大提高性能，因为他们针对数据传输做了优化，以降低数据库服务器的负担。当然也有很多限制，比如并不是所有的数据库都提供本地工具。目前sqoop的direct模式只支持MySQL和PostgreSQL。
<span id="415"></span>

4.1.5 自定义类型映射

使用—amp-column-java参数来将列列映射到java类以覆盖sqoop提供的默认的映射关系。
如要将c1、c2、c3分别映射为Float、String、String，对应的设置如下所示。

sqoop import --map-column-java c1=Float,c2=String,c3=String ...

<span id="416"></span>

4.1.6 并行控制

Sqoop默认使用4个并发的map任务来项hadoop传输数据。当数据量比较大时可以考虑增加并发执行的map任务的数量以提高传输速度。使用参数—num-mappers来控制map任务的数量。
<span id="417"></span>

4.1.7 对NULL值进行编码

Sqoop使用“null”字符串来代替数据库中的NULL值。对于文本类型的列，使用—null-string来设置替代NULL值得字符串，对于其他类型的列，则使用—null-non-string来设置。

如果想使用\N来编码NULL值，则对应sqoop命令中的值为\N， \在JAVA中是转义字符。

--null-string '\\N' \
--null-non-string '\\N'

<span id="418"></span>

4.1.8 导入所有表

使用如下命令导入所有表。sqoop会一次导入每张表，以避免对数据库服务器造成额外的负担。

sqoop import-all-tables \

   --connect jdbc:mysql://mysql.example.com/sqoop \

   --username sqoop \

   --password sqoop

<span id="42"></span>

4.2 增量导入

增量导入是每次只导入新增加的或者需要更新的数据。增量导入会大大降低数据库服务器的负担。
<span id="421"></span>

4.2.1 只导入细腻数据

假设我们有INTEGER类型的主键，并且只追加新列，并且需要定期将表的状态同步到Hadoop中。我们需要使用增量导入的功能。典型代码如下所示。

sqoop import \

   --connect jdbc:mysql://mysql.example.com/sqoop \

   --username sqoop \

   --password sqoop \

   --table visits \

   --incremental append \

   --check-column id \

   --last-value 1

--incremental参数表示使用增量导入，参数值为使用的增量导入的类型。，由于我们只添加而不修改列的值，所以使用append。增量导入中还需要额外的两个参数：--check-column指定一个列，用于检查新增加的数据，--last-value包含上次成功导入到Hadoop的值。
<span id="422"></span>

4.2.2 增量导入可变数据

使用lastmodified模式而不是append模式来导入变化的数据。例如使用如下命令导入所last_update_date列大于“2013-05-22 01:01:01”的行。

sqoop import \

   --connect jdbc:mysql://mysql.example.com/sqoop \

   --username sqoop \

   --password sqoop \

   --table visits \

   --incremental lastmodified \

   --check-column last_update_date \

   --last-value "2013-05-22 01:01:01"

增量导入模式lastmodified需要一个日期（可选的类型有date、time、datetime和timestamp）列来保存列被修改的时间。

注：--last-value的值在增量导入时是包含在需要导入的范围之内的。
Sqoop不会检查数据是否重复，即不会按照MySQL中那样更新数据。
<span id="423"></span>

4.2.3 保存last-value

Sqoop导入任务完成后会给出新的last-value，我们可以保存当前下来，以便下次使用。
Sqoop的metastore会保存任务的信息，所以我们创建对应的任务即可。

sqoop job \
   --create visits \
   -- \
   import \
   --connect jdbc:mysql://mysql.example.com/sqoop \
   --username sqoop \
   --password sqoop \
   --table visits \
   --incremental append \
   --check-column id \
   --last-value 0

执行任务

sqoop job --exec visits

删除任务

sqoop job --delete visits

查看任务信息，信息中会包含last-value的当前值。

sqoop job --show visits

<span id="424"></span>

4.2.4 在metastore中保存密码

很不幸，每次使用sqoop job执行任务都需要手动输入密码。
解决方式有两种：
第一种方式，使用password-file（“导入”一章中有介绍）；
第二种方式，在sqoop-site.xml中添加如下属性即可（添加后第一次仍然需要输入密码 ）。

<property>
   <name>sqoop.metastore.client.record.password</name>
   <value>true</value>
</property>

<span id="425"></span>

4.2.5 客户端之间共享metastore

启动metastore服务

sqoop metastore

客户端连接到metastore服务

sqoop job
   --create visits \
   --meta-connect \              
      jdbc:hsqldb:hsql://metastore.example.com:16000/sqoop \
   -- \
   import \
   --table visits
      ...

显示任务

sqoop  job --list --meta-connect jdbc:hsqldb:hsql://metastore.example.com:16000/sqoop

<span id="43"></span>

4.3 导出

<span id="431"></span>

4.3.1 hive导出数据到mysql

sqoop export
--connect jdbc:mysql://MYSQL_SERVER:PORT/DBNAME    \
--username USERNAME   \
--table TABLE         \
--export-dir /user/hive/warehouse/HIVE_TABLE_NAME/       \
--num-mappers N           \
--fields-terminated-by ','         

附：对于hive中有null时，导入到MySQL中依然是NULL。
<span id="432"></span>

4.3.2 批量插入

sqoop使用独立的insert语句来添加每一行记录，使用如下的方式可以每次插入多条记录。即批量插入，每次插入10条。

sqoop export \
   -Dsqoop.export.records.per.statement=10 \
   --connect jdbc:mysql://mysql.example.com/sqoop \
   --username sqoop \
   --password sqoop \
   --table cities \
   --export-dir cities \
   --batch

<span id="433"></span>

4.3.3 导出所有数据或不导出任何数据

我们需要确保或者所有数据均导出成功，或者不导出任何数据。为此我们使用临时表，即先将数据导出到临时表（staging-table）中，然后再转移到目标表中。

sqoop export \
   --connect jdbc:mysql://mysql.example.com/sqoop \
   --username sqoop \
   --password sqoop \
   --table cities \
   --staging-table staging_cities

<span id="434"></span>

4.3.4 更新已有数据

使用—update-key参数指定一列，该列可以识别出被修改的行，通常是表中的主键。例如下面的示例中使用列id来更新cities表。

sqoop export \
   --connect jdbc:mysql://mysql.example.com/sqoop \
   --username sqoop \
   --password sqoop \
   --table cities \
   --update-key id

可以指定多个列，列之间用逗号隔开。
注意，该操作只会更新已有的数据，不会插入新的数据，
<span id="435"></span>

4.3.5 更新或插入数据

使用如下的参数可以同时插入新数据或更新已有数据。

sqoop export \
   --connect jdbc:mysql://mysql.example.com/sqoop \
   --username sqoop \
   --password sqoop \
   --table cities \
   --update-key id \
   --update-mode allowinsert

<span id="436"></span>

4.3.6 只导出某些列

Sqoop默认hdfs中的数据和数据库中有相同数量的列并且顺序相同，使用—columns参数可以指定需要导出到数据库中的列或者指定导出列之间的顺序。如只导出coutry和city列，就可以使用如下示例。

sqoop export \
   --connect jdbc:mysql://mysql.example.com/sqoop \
   --username sqoop \
   --password sqoop \
   --table cities \
   --columns country,city

<span id="437"></span>

4.3.7 编码NULL值

你可以使用—input-null-string和—input-null-no-string参数来覆盖NULL值的替换字符串，例如使用‘\N’替换NULL值。

sqoop export \
   --connect jdbc:mysql://mysql.example.com/sqoop \
   --username sqoop \
   --password sqoop \
   --table cities \
   --input-null-string '\\N' \
   --input-null-non-string '\\N'

<span id="5"></span>

5 附：import和export参数详解

<span id="51"></span>

5.1 import和export工具通用选项

选项	说明
--connect < jdbc-uri >	指定JDBC连接字符串
--connection-manager < class-name >	指定要使用的连接管理器类
--driver < class-name >	指定要使用的JDBC驱动类
--hadoop-mapred-home < dir >	指定$HADOOP_MAPRED_HOME路径
--help	打印用法帮助信息
--password-file	设置用于存放认证的密码信息文件的路径
-P	从控制台读取输入的密码
--password < password >	设置认证密码
--username < username >	设置认证用户名
--verbose	打印详细的运行信息
--connection-param-file < filename >	可选，指定存储数据库连接参数的属性文件

<span id="52"></span>

5.2 数据导入工具import参数详解

import工具，是将HDFS平台外部的结构化存储系统中的数据导入到Hadoop平台，便于后续分析。我们先看一下import工具的基本选项及其含义，如下表所示：

选项	说明
--append	将数据追加到HDFS上一个已存在的数据集上
--as-avrodatafile	将数据导入到Avro数据文件
--as-sequencefile	将数据导入到SequenceFile
--as-textfile	将数据导入到普通文本文件（默认）
--boundary-query < statement >	边界查询，用于创建分片（InputSplit）
--columns < col,col,col…>	从表中导出指定的一组列的数据
--delete-target-dir	如果指定目录存在，则先删除掉
--direct	使用直接导入模式（优化导入速度）
--direct-split-size < n >	分割输入stream的字节大小（在直接导入模式下）
--fetch-size < n >	从数据库中批量读取记录数
--inline-lob-limit < n >	设置内联的LOB对象的大小
-m,--num-mappers < n >	使用n个map任务并行导入数据
-e,--query < statement >	导入的查询语句
--split-by < column-name >	指定按照哪个列去分割数据
--table < table-name >	导入的源表表名
--target-dir < dir >	导入HDFS的目标路径
--warehouse-dir < dir >	HDFS存放表的根路径
--where < where clause>	指定导出时所使用的查询条件
-z,--compress	启用压缩
--compression-codec < c >	指定Hadoop的codec方式（默认gzip）
--null-string < null-string >	如果指定列为字符串类型，使用指定字符串替换值为null的该类列的值
--null-non-string < null-string >	如果指定列为非字符串类型，使用指定字符串替换值为null的该类列的值

hive参数

选项	说明
--hive-home < dir >	Override $HIVE_HOME
--hive-import	插入数据到hive当中，使用hive的默认分隔符
--hive-overwrite	覆盖hive表中的数据
--create-hive-table	建表，如果表已经存在，该操作会报错
--hive-table < table-name >	设置到hive当中的表名
--hive-drop-import-delims	导入到hive时删除 \n, \r, and \01
--hive-delims-replacement	导入到hive时用自定义的字符替换掉\n, \r, and \01
--hive-partition-key	hive分区的key
--hive-partition-value < v >	hive分区的值
--map-column-hive < map >	类型匹配，sql类型对应到hive类型

HBase参数

选项	说明
--column-family < family >	把内容导入到hbase当中，默认是用主键作为split列		--hive-partition-key	hive分区的key
--hbase-create-table	创建Hbase表
--hbase-row-key < col >	指定字段作为row key ，如果输入表包含复合主键，用逗号分隔
--hbase-table < table-name >	指定hbase表

<span id="53"></span>

5.3 数据导出工具export参数详解

export工具，是将HDFS平台的数据，导出到外部的结构化存储系统中，可能会为一些应用系统提供数据支持。我们看一下export工具的基本选项及其含义，如下表所示：

选项	说明
--validate < class-name >	启用数据副本验证功能，仅支持单表拷贝，可以指定验证使用的实现类
--validation-threshold < class-name >	指定验证门限所使用的类表
--direct	使用直接导出模式（优化速度）
--export-dir < dir >	导出过程中HDFS源路径
-m,--num-mappers < n >	使用n个map任务并行导出
--table < table-name >	导出的目的表名称
--call < stored-proc-name >	导出数据调用的指定存储过程名
--update-key col-name >	更新参考的列名称，多个列名使用逗号分隔
--update-mode < mode >	指定更新策略，包括：updateonly（默认）、allowinsert
--input-null-string < null-string >	使用指定字符串，替换字符串类型值为null的列
--input-null-non-string < null-string >	使用指定字符串，替换非字符串类型值为null的列
--staging-table < staging-table-name>	在数据导出到数据库之前，数据临时存放的表名称
--clear-staging-table	清除工作区中临时存放的数据
--batch	使用批量模式导出

<span id="54"></span>

5.4 Sqoop Job参数详解

选项	说明
--create < job-id >	定义sqoop job
--delete < job-id>	删除sqoop job
--exec < job-id>	执行sqoop job
--show < job-id>	查看sqoop job状态
--list	查看所有sqoop job

 

Sqoop 使用详解（内含对官方文档的解析）

 

Sqoop 是 Cloudera 公司创造的一个数据同步工具，现在已经完全开源了。 

目前已经是 hadoop 生态环境中数据迁移的首选，另外还有 ali 开发的 DataX 属于同类型工具，由于社区的广泛使用和文档的健全，调研之后决定使用 Sqoop 来做我们之后数据同步的工具。

我们首先来看下 Sqoop 的工作流

 

 他将我们传统的关系型数据库 | 文件型数据库 | 企业数据仓库 同步到我们的 hadoop 生态集群中。

同时也可以将 hadoop 生态集群中的数据导回到传统的关系型数据库 | 文件型数据库 | 企业数据仓库中。

那么 Sqoop 如何抽取数据呢

1. 首先 Sqoop 去 rdbms 抽取元数据。

2. 当拿到元数据之后将任务切成多个任务分给多个 map。

3. 然后再由每个 map 将自己的任务完成之后输出到文件。

 

Sqoop import Command:

先从最简单的任务开始

sqoop import\
  --connect jdbc:mysql://10.66.38.125:3306/user_db \
  --username cloudera \
  --password secretkey \
  --table department \
  --target-dir /sqoopdata/departments \　　　　　　# HDFS 的目标存储位置
  --where "department_id = 1000" \　　　　　　　　　# 指定条件，只有达成这个条件的才会被 import 进来
  -- m 1

就这个语句就可以将我们关系型数据库中的某个表 import 进 HDFS 的某个位置。

 

同样我们可以 import 某些字段进来生成文件

sqoop import \
  --connect jdbc:mysql://localhost:3306/retry_db \
  --username cloudera \ 
  --password secret \ 
  --table departments \
  --columns "dept_id, name" \　　# 指定需要的字段
  --as-avrodatafile　　　　　　　　# 指定存成 avro 数据文件

 

如果我们要 import 一个库里面的所有表可以使用

sqoop import-all-tables \
  --connect jdbc:mysql://localhost:3306/retry_db \
  --username cloudera \
  --password secret \
  --warehouse-dir /mydata    # HDFS parent for table 这个会将所有这些表都放到 HDFS 这个文件夹下面

 

Sqoop import Command:

我们将数据从 Hadooop HDFS 导出向 RDBMS

sqoop export \
  --connect jdbc:mysql://localhost:3306/retry_db \
  --username cloudera \
  --password departments \
  --export-dir /sqoopdata/departments \　　　　# HDFS source path for the export
  --table departments

 

Sqoop Job:

Sqoop 提供一种能力，可以把我们经常会执行的任务存储成 jobs. 这些 jobs 可以在未来任何一个时间点被我们拿来使用。

sqoop job \
  --create job_name \
  --import \
  --connect jdbc:mysql://localhost:3306/retry_db \
  --username cloudera \
  --password departments 

 

常用姿势上面就介绍完了，当我们需要将 MySQL 数据同步到 Hive 去的时候如果表还没有创建我们只需要执行：

sudo-u hive sqoop import \
--connect jdbc:mysql://10.66.38.15:3306/user \　　　　　　# 连接需要被同步的 MySQL
--username xxx \
--password xxx \
--table user \　　　　　　　　　　　　　　　　　　　　　　　　　# 需要被同步的表
--delete-target-dir \　　　　　　　　　　　　　　　　　　　　 # 之前有同步的文件已经存在删除掉- m 1 \　　　　　　　　　　　　　　　　　　　　　　　　　　　　　# 开一个 map 这个值得注意，不是每个 source 表都可以轻松被分为多个 map 的。如果你这里要填写非 1 的数最好去熟悉一些细节
--hive-import \　　　　　　　　　　　　　　　　　　　　　　　　 
--hive-tableuser.user \
--create-hive-table \　　　　　　　　　　　　　　　　　　　　 # 创建 hive 表
--hive-drop-import-delims　　　　　　　　　　　　　　　　　　# Drops \n, \r, and \01 from string fields when importing to Hive.

 

如果是表已经创建好而需要全量同步数据执行：

sudo -u hive sqoop import\
--connect jdbc:mysql://10.66.38.125:16033/user \
--username xxx \
--password xxx \
--table user \
--delete-target-dir \
--hive-overwrite \　　　　　　　　　　# 全量重写数据
- m 1 \
--hive-import \
--hive-table user.user \
--hive-drop-import-delims

 

同样的 Sqoop 还支持 Hive 的增量同步。但是基于 mapreduce 的全量同步速度也快得超出想象。实测在三机集群上(12核 | 32内存)机器上1分钟基本能完成对 20 个字段左右的表 250w 条记录的抽取。并且对目标数据库机器的压力不算大。是非常理想的大数据同步工具。

 

Sqoop 的配置参数非常之多，在使用的时候建议先撸一遍文档（文档不长大概撸一次 2 3 个小时左右），找到自己需要注意的地方和更多适合自己的功能在使用的时候就能避免踩坑。比如上面使用的   hive-drop-import-delims 参数的使用就是还没看完文档就使用造成的坑。我们数据库中有字段没有过滤 \n 。有个用户的名字被误操作使用 \n 开头导致 hive 误以为遇到了换行符，该数据不仅错乱而且后面字段全部被置为 NULL。要避免这种问题一方面要对这个使用链上各个组件有所了解，更是应该读一读文档可以最大程度的避免踩坑。

----------------------------------------------------------分割线----------------------------------------------------------

下面将纪录一下我全量阅读 Sqoop 文档觉得需要纪录的一些东西。

 

7.2. 语法（Syntax）

首先我们上面看到命令 Sqoop Command 这个 Command 其实是指定 Sqoop 使用哪种 Tool 。

$ sqoop help
usage: sqoop COMMAND [ARGS]

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 mainframe datasets to HDFS
  list-databases     List available databases on a server
  list-tables        List available tables in a database
  version            Display version information

See 'sqoop help COMMAND' for information on a specific command.

可以看到我上面举例的所有内容都只是简单的使用到了 export 和 import 还有 import-all-tables  工具。 还有非常多的工具没有使用到。

因为 sqoop 是依赖 hadoop 生态的关系，所以也有响应的查找链，因为使用了 CDH 大礼包，所以我只是简单的安装了一下，相关的依赖都已经被配置好了包括 path

lrwxrwxrwx 1 root root 23 Nov 13 20:55 /usr/bin/sqoop -> /etc/alternatives/sqoop

 

7.2.1 连接数据库服务器（Connecting to a Database Server）

下面我们在使用 import tool 的时候遵循这个原则：

sqoop import (generic-args) (import-args)
sqoop-import (generic-args) (import-args)
While the Hadoop generic arguments must precede any import arguments, you can type the import arguments in any order with respect to one another.

当我们在写语句的时候应该首先使用了 generic-args 参数可以是以下的参数。

Argument	Description
--connect <jdbc-uri>	Specify JDBC connect string
--connection-manager <class-name>	Specify connection manager class to use
--driver <class-name>	Manually specify JDBC driver class to use
--hadoop-mapred-home <dir>	Override $HADOOP_MAPRED_HOME
--help	Print usage instructions
--password-file	Set path for a file containing the authentication password
-P	Read password from console
--password <password>	Set authentication password
--username <username>	Set authentication username
--verbose	Print more information while working
--connection-param-file <filename>	Optional properties file that provides connection parameters
--relaxed-isolation	Set connection transaction isolation to read uncommitted for the mappers.

后面的 import args 可选项就非常丰富。

比如可以导入校验使用的 class 删除控制参数啥的。

Argument	Description
--validate	Enable validation of data copied, supports single table copy only.
--validator <class-name>	Specify validator class to use.
--validation-threshold <class-name>	Specify validation threshold class to use.
--validation-failurehandler <class-name>	Specify validation failure handler class to use.

 

Argument	Description
--append	Append data to an existing dataset in HDFS
--as-avrodatafile	Imports data to Avro Data Files
--as-sequencefile	Imports data to SequenceFiles
--as-textfile	Imports data as plain text (default)
--as-parquetfile	Imports data to Parquet Files
--boundary-query <statement>	Boundary query to use for creating splits
--columns <col,col,col…>	Columns to import from table
--delete-target-dir	Delete the import target directory if it exists
--direct	Use direct connector if exists for the database
--fetch-size <n>	Number of entries to read from database at once.
--inline-lob-limit <n>	Set the maximum size for an inline LOB
-m,--num-mappers <n>	Use n map tasks to import in parallel
-e,--query <statement>	Import the results of statement.
--split-by <column-name>	Column of the table used to split work units. Cannot be used with --autoreset-to-one-mapper option.
--split-limit <n>	Upper Limit for each split size. This only applies to Integer and Date columns. For date or timestamp fields it is calculated in seconds.
--autoreset-to-one-mapper	Import should use one mapper if a table has no primary key and no split-by column is provided. Cannot be used with --split-by <col> option.
--table <table-name>	Table to read
--target-dir <dir>	HDFS destination dir
--temporary-rootdir <dir>	HDFS directory for temporary files created during import (overrides default "_sqoop")
--warehouse-dir <dir>	HDFS parent for table destination
--where <where clause>	WHERE clause to use during import
-z,--compress	Enable compression
--compression-codec <c>	Use Hadoop codec (default gzip)
--null-string <null-string>	The string to be written for a null value for string columns
--null-non-string <null-string>	The string to be written for a null value for non-string columns

 

7.2.3 自由的表查询导入（Free-form Query Imports）

包括支持 free-form query .使用 --query 参数然后写一个 sql 来过滤自己想要 import 的数据 just like 

$ sqoop import \
  --query 'SELECT a.*, b.* FROM a JOIN b on (a.id == b.id) WHERE $CONDITIONS' \
  --split-by a.id --target-dir /user/foo/joinresults

这个使用方法必须要使用 --target-dir 

 

7.2.4 掌控并行处理（Controlling Parallelism）

如果需要控制并行操作普遍使用的是 -m 参数，--num-mapers参数。我们可以显示的指定使用的用来并行分配的键，使用例如 --split-by employee_id 达到目标。

如果说我们没有使用 --split-by 参数主键也不是 int 型，可能会导致指定 -m 大于 1 的时候出问题。因为程序没有办法知道应该根据哪个键来分配 map 任务。

另外我们可以使用 --autoreset-to-one-mapper 选项 --autoreset-to-one-mapper Import should use one mapper if a table has no primary key and no split-by column is provided. Cannot be used with --split-by <col> option.

 

7.2.5 掌控分布式缓存（Controlling Distributed Cache）

使用 Oozie 调起 Sqoop job 执行任务的时候要注意一个 Controlling Distributed Cache 的问题。在第一个Sqoop作业期间，Oozie只会在每个工作节点上对Sqoop依赖项进行一次本地化，并会在工作节点上重用jar来执行子节点作业。在Oozie启动Sqoop命令时使用option - skip-dist-cache，可以跳过Sqoop将依赖项复制到作业缓存并保存大量I/O的步骤。达到优化的目的。

 

7.2.6 掌控导入过程（Controlling the Import Process）

在控制导入的过程中也有很多优化的地方可以做，例如我们在对关系行数据库 MySQL 进行导入的时候，可以通过使用关键字 --direct 加速导入的速度。他的原理是默认情况下我们会使用 JDBC 对数据库进行连接，但是有一些数据库提供了更高性能可以指定数据库进行转移的工具。比如 MySQL 提供的 MySQL 提供的工具 mysqldump 使用 --direct 参数就可以尝试让 Sqoop 使用这种方式去导出数据，可能会得到更高的效能。当我们在使用 --direct option 的时候还可以传递一些潜在的参数给这个命令类似于这样 将命令跟在 -- 后面

$ sqoop import --connect jdbc:mysql://server.foo.com/db --table bar \
    --direct -- --default-character-set=latin1

就可以将后面的 --default-character-set=latin1 传递给 mysqldump 。

在 import 表的时候有两个指定路径的参数是冲突的  --warehouse-dir 和 --target-dir 都用于指定将生成的表放到指定的这个目录下面。他们俩是冲突的，指定其中一个 option 即可。

在默认情况下 import 这个工具都会将表导到一个新的路径下面。如果路径下面已经有相同名字的文件存在了，将会被拒绝导入。

如果使用 --append 参数 Sqoop将会将文件导入到临时的文件目录，然后重命名该文件成不与目标文件夹里面名字冲突的名字。

 

7.2.7 掌控事务隔离级别（Controlling transaction isolation）

Sqoop 提供读取数据库 read-uncommitted 事务的能力，只需要带上参数 --relaxed-isolation 即可。这个操作真是非常骚啊，一般应该不会用到而且也不是所有数据库都支持，比如官方文档说 ORACLE 就是不支持的。

 

7.2.8 掌控 mapping 时候的字段类型（Controlling type mapping）

可以对指定同步的表进行 schema 的映射转换，并且可以指定通过 java 或者 hive 类型的转换。例如：

Argument    Description
--map-column-java <mapping>    Override mapping from SQL to Java type for configured columns.
--map-column-hive <mapping>    Override mapping from SQL to Hive type for configured columns.

Sqoop is expecting comma separated list of mapping in form <name of column>=<new type>. For example:

$ sqoop import ... --map-column-java id=String,value=Integer

另外需要注意的是  --map-column-hive 使用该参数需要使用 urlencode 对转换 key value 进行转换。例如

use DECIMAL(1%2C%201) instead of DECIMAL(1, 1)

如果转换不正确，Sqoop 会 raise exception

 

7.2.10 增量更新（Incremental Imports）

关于使用 Sqoop 进行增量更新处理, Sqoop 提供了三个字段来处理增量更新相关的内容

Argument	Description
--check-column (col)	Specifies the column to be examined when determining which rows to import. (the column should not be of type CHAR/NCHAR/VARCHAR/VARNCHAR/ LONGVARCHAR/LONGNVARCHAR)
--incremental (mode)	Specifies how Sqoop determines which rows are new. Legal values for mode include append and lastmodified.
--last-value (value)	Specifies the maximum value of the check column from the previous import.

Sqoop 本身支持两种不同的方式进行增量更新，分别是 append 和 lastmodified 我们使用 --incremental 参数去指定要使用的增量更新类型。

增量更新的文章有很多基本上建立在两个基础上。（之前的数据如果被 update 没有办法通过这两种增量更新机制被更新）

1. 可以提供类似于自增 id 这样的字段，并且小于这个点的字段可以从上次这个点位继续往后增加。使用 --last-value 需要注意的是可以使用 Sqoop job 在第一次指定了开始的 last-value 值之后 Sqoop 会保存下来这次执行完之后 last-value 值的节点，下次执行的时候会基于这个继续执行。

2. 可以提供一个最后修改的字段，例如 update_time 这样的字段，所有大于这个 update_time 时间的字段将在下个节点被增量追加到后面。--check-column update_time

 

7.2.11 文档格式化（File Formats）

我们通常导入两种格式的文件形式，一种是 textfile 也是默认类型。还有一种是 SequenceFiles

我们可以通过指定 --as-textfile 参数显示指定使用 textfile 导入。textfile 又称 delimited text 在非二进制数据情况下非常通用，而且很容易支持类似于像 Hive 这种数据库表的生成。

SequenceFiels 是一种二进制格式用于往自定义的记录指定的 data types 中存储独立的记录。这些 data types 表现为 java 的类。

另外我们也可以使用表协议 比如我们可以使用 Apache Avro。

默认情况下 Sqoop 不会帮我们压缩文件使用 -z 或者 --compress 参数或者使用其他压缩参数比如 --compression-codec 对 SequenceFile text 或者 Avro 文件进行压缩。

 

7.2.12. 大的对象的处理(Large Objects)

Sqoop 对 blob 和 clob  columns 都有特别的处理方式。他们尽量不要像常规字段这样全部 load 进内存进行操作。而是使用流式的方法来进行处理，并且和其他数据进行内联存储。（这一块我完全没有看懂是什么意思，水平不够可以自行前往官方文档查看。。。。。。）

 

Table 6. Output line formatting arguments:

Argument	Description
--enclosed-by <char>	Sets a required field enclosing character
--escaped-by <char>	Sets the escape character
--fields-terminated-by <char>	Sets the field separator character
--lines-terminated-by <char>	Sets the end-of-line character
--mysql-delimiters	Uses MySQL’s default delimiter set: fields: , lines: \n escaped-by: \ optionally-enclosed-by: '
--optionally-enclosed-by <char>	Sets a field enclosing character

 默认情况下 Sqoop 会使用逗号 comma(,) 来作为字段之间的分隔符，使用换行符 \n 来区别每一条记录。

Sqoop 官方文档推荐我们使用 unambiguous 也就是显示清晰的去指定字段分隔符和行分隔符。比如直接使用 --mysql-delimiters

下面的叙述我想了很久想翻译成中文我都觉得不是很直接 所以还是直接贴文档吧。

If unambiguous delimiters cannot be presented, then use enclosing and escaping characters. The combination of (optional) enclosing and escaping characters will allow unambiguous parsing of lines. For example, suppose one column of a dataset contained the following values:

Some string, with a comma.
Another "string with quotes"

The following arguments would provide delimiters which can be unambiguously parsed:

$ sqoop import --fields-terminated-by , --escaped-by \\ --enclosed-by '\"' ...

(Note that to prevent the shell from mangling the enclosing character, we have enclosed that argument itself in single-quotes.)

The result of the above arguments applied to the above dataset would be:

"Some string, with a comma.","1","2","3"...
"Another \"string with quotes\"","4","5","6"...

Here the imported strings are shown in the context of additional columns ("1","2","3", etc.) to demonstrate the full effect of enclosing and escaping. The enclosing character is only strictly necessary when delimiter characters appear in the imported text. The enclosing character can therefore be specified as optional:

$ sqoop import --optionally-enclosed-by '\"' (the rest as above)...

Which would result in the following import:

"Some string, with a comma.",1,2,3...
"Another \"string with quotes\"",4,5,6...

Note

Even though Hive supports escaping characters, it does not handle escaping of new-line character. Also, it does not support the notion of enclosing characters that may include field delimiters in the enclosed string. It is therefore recommended that you choose unambiguous field and record-terminating delimiters without the help of escaping and enclosing characters when working with Hive; this is due to limitations of Hive’s input parsing abilities.

The --mysql-delimiters argument is a shorthand argument which uses the default delimiters for the mysqldump program. If you use the mysqldump delimiters in conjunction with a direct-mode import (with --direct), very fast imports can be achieved.

While the choice of delimiters is most important for a text-mode import, it is still relevant if you import to SequenceFiles with --as-sequencefile. The generated class' toString() method will use the delimiters you specify, so subsequent formatting of the output data will rely on the delimiters you choose.

 

Table 8. Hive arguments:

Argument	Description
--hive-home <dir>	Override $HIVE_HOME
--hive-import	Import tables into Hive (Uses Hive’s default delimiters if none are set.)
--hive-overwrite	Overwrite existing data in the Hive table.
--create-hive-table	If set, then the job will fail if the target hive
	table exists. By default this property is false.
--hive-table <table-name>	Sets the table name to use when importing to Hive.
--hive-drop-import-delims	Drops \n, \r, and \01 from string fields when importing to Hive.
--hive-delims-replacement	Replace \n, \r, and \01 from string fields with user defined string when importing to Hive.
--hive-partition-key	Name of a hive field to partition are sharded on
--hive-partition-value <v>	String-value that serves as partition key for this imported into hive in this job.
--map-column-hive <map>	Override default mapping from SQL type to Hive type for configured columns. If specify commas in this argument, use URL encoded keys and values, for example, use DECIMAL(1%2C%201) instead of DECIMAL(1, 1).

我们想要使用 Sqoop 抽取 RDBMS 的数据到 Hive 可能是再常见不过的情形了，所以这一部分很重要也可能是我们最常使用的部分。

 

7.2.13 导入数据到 Hive (Importing Data Into Hive)

Sqoop 抽取 RDBMS 的数据到 Hive 会先将数据抽取出来在 HDFS 上的指定路径上放一下。如果指定路径上已经有文件，但是 Hive 里面却没有你的表你还需要指定 --delete-target-dir 来删除 HDFS 的文件，然后重新上传一份。当上传到 HDFS 结束之后，会使用 Hive 的命令 LOAD DATA INPATH 将文件移动到 Hive 的 warehouse 目录下面如果指定了 Hive 表的创建表参数会直接创建 Hive 表并且映射上数据。

如果表已经存在了 可以使用 --hive-overwrite 将数据直接覆盖。虽然Hive支持转义字符，但它不处理换行字符。此外，它不支持在封闭字符串中包含字段分隔符的封闭字符的概念。因此，在使用Hive时，建议您选择明确的字段和记录终止分隔符，而无需转义和包围字符;这是由于Hive的输入解析能力的限制。如果您在将数据导入到Hive时使用了--escapby，--enclosed-by, or -optionally-enclosed-by, Sqoop将打印一条警告消息。

Hive 默认会使用 \n 分割行，使用\01 分割字段。如果说我们的数据里面有这些字段就可能会有冲突，我们需要使用 --hive-drop-import-delims 把这些都替换掉。上面表可以参照这个 option 的意义。另外也可以使用 --hive-delims-replacement 将冲突的字段给替换掉。

另外还有一个值得注意的地方 Hive 表默认将从 RDBMS 里面抽取出来的 NULL value 数据转换成 null string 。这个在使用的时候就会出现问题，因为之前是一个空，现在却变成了一个 null 字符串。所以我们需要处理一下， Hive在自己的体系里面使用 \N 来表示 NULL 我们使用 --null-string 和 --null-non-string 参数处理 import job 使用 --input-null-string 和 --input-null-non-string  处理 export job 。举个🌰

$ sqoop import  ... --null-string '\\N' --null-non-string '\\N'

另外我们可以使用 --hive-partition-key 和 --hive-partition-value 参数来指定分区键提升 hive 的处理能力。

这一块 sqoop 只支持单分区导入。

这一块更详细的可以参考一下 hive 文档。

27.2.5. MySQL: Import of TINYINT(1) from MySQL behaves strangely

Problem: Sqoop is treating TINYINT(1) columns as booleans, which is for example causing issues with HIVE import. This is because by default the MySQL JDBC connector maps the TINYINT(1) to java.sql.Types.BIT, which Sqoop by default maps to Boolean.

Solution: A more clean solution is to force MySQL JDBC Connector to stop converting TINYINT(1) to java.sql.Types.BIT by adding tinyInt1isBit=false into your JDBC path (to create something like jdbc:mysql://localhost/test?tinyInt1isBit=false). Another solution would be to explicitly override the column mapping for the datatype TINYINT(1) column. For example, if the column name is foo, then pass the following option to Sqoop during import: --map-column-hive foo=tinyint. In the case of non-Hive imports to HDFS, use --map-column-java foo=integer.