SQOOP数据导入

sqoop是一个用于在Hadoop和关系型数据库（Oracle，Mysql...）间数据传递的开源工具。下面以Oracle为例，介绍使用sqoop将数据从Oracle导入到Hadoop中（HDFS、Hive和HBase）。

1、导入命令及参数介绍

命令格式

$ sqoop import (generic-args) (import-args)$ sqoop-import (generic-args) (import-args)

generic参数必须放在import参数之前，generic参数是与hadoop相关的参数，这里不做介绍。本文主要介绍import参数，import参数没有顺序要求，下面我们对常用的import参数进行介绍。

（1）通用参数：

参数名

参数说明

--connect <jdbc-uri>JDBC连接字符串--username <username>数据库用户名--password <password>数据库密码-P导入时，从控制台获取数据库密码--password-file从指定的文件中获取数据库密码--verbose导入时，输出更多的日志信息

import的通用参数还包括：--connection-manager ，--driver ，--hadoop-mapred-home ，--help ，--connection-param-file，--relaxed-isolation，可以在sqoop的官方文档中查看参数说明。

（2）控制参数

参数名

参数说明

--append将数据追加到一个已经存在于HDFS中的数据集中--target-dir <dir>导入到HDFS目标目录--table <table-name>要导入的表的表名--columns <col,col,col…>要导入的列名，多个列名与逗号分隔-e,--query <statement>从查询语句导入，'select * from ...'--where <where clause>导入时where子句--split-by <column-name>导入时进行任务分割的字段，不能和--autoreset-to-one-mapper参数同时使用--autoreset-to-one-mapper如果导入表没有主键或者没有使用split-by指定分割字段时，使用1个mapper进行数据导入，不能和--split-by参数同时使用-m,--num-mappers <n>使用n个并行任务导入数据--inline-lob-limit <n>内嵌LOB的最大长度(byte)-z,--compress导入时对数据进行压缩--compression-codec指定Hadoop压缩格式（默认为gzip）--null-string <null-string>字符类型字段的空值字符串--null-non-string <null-string>非字符类型字段的空值字符串null-string和null-non-string是可选参数，不指定时，使用“null”作为空值字符串。其他的控制参数可以在官方文档中查看详细说明。下面我们将会通过实际例子了解import参数的用法和注意事项。

2、导入数据到HDFS

首先，我们在oracle数据库已建立一个准备导入的数据表：

create table T_SQOOP_TEST(  id          NUMBER primary key,  name        VARCHAR2(32),  create_date DATE default sysdate,  version     NUMBER(4))

ID为主键。表数据：

我们通过以下命令将T_SQOOP_TEST表数据导入到HDFS中：

$ sqoop import --connect jdbc:oracle:thin:@192.168.1.10:1521:TEST --username test --password test --table T_SQOOP_TEST --verbose

我们没有设置split-by参数指定任务分割字段，sqoop默认以主键作为分割字段。我们没有通过-m,--num-mappers参数指定任务数，sqoop默认启动4个map-reduce任务。通过以下导入日志，我们可以看出，sqoop通过查询任务分割字段（ID）的最大值和最小值，计算出每个任务的导入范围。

16/12/12 12:20:42 INFO db.DataDrivenDBInputFormat: BoundingValsQuery: SELECT MIN(ID), MAX(ID) FROM T_SQOOP_TEST16/12/12 12:20:42 DEBUG db.DataDrivenDBInputFormat: Creating input split with lower bound 'ID >= 1' and upper bound 'ID < 2'16/12/12 12:20:42 DEBUG db.DataDrivenDBInputFormat: Creating input split with lower bound 'ID >= 2' and upper bound 'ID < 3'16/12/12 12:20:42 DEBUG db.DataDrivenDBInputFormat: Creating input split with lower bound 'ID >= 3' and upper bound 'ID < 4'16/12/12 12:20:42 DEBUG db.DataDrivenDBInputFormat: Creating input split with lower bound 'ID >= 4' and upper bound 'ID <= 5'

导入成功后，能看到以下日志信息（只展示了部分）：

16/12/12 12:21:32 INFO mapreduce.ImportJobBase: Transferred 172 bytes in 52.3155 seconds (3.2877 bytes/sec)16/12/12 12:21:32 INFO mapreduce.ImportJobBase: Retrieved 5 records.

通过日志信息，我们可以看到共导入了5条记录，导入时间为52.3155秒。如果导入的表记录少时，可以使用-m,--num-mappers参数将导入任务设置为1，导入速度会更快一点。

通过HDFS命令，查看导入文件：

$ hadoop fs -lsdrwxr-xr-x   - hadoop supergroup          0 2016-12-12 12:21 /user/hadoop/T_SQOOP_TEST

可以看到在hdfs中生成了一个以导入表表名命名的文件夹，查看文件夹内容：

$ hadoop fs -ls /user/hadoop/T_SQOOP_TEST/Warning: $HADOOP_HOME is deprecated.Found 6 items-rw-r--r--   1 hadoop supergroup          0 2016-12-12 12:21 /user/hadoop/T_SQOOP_TEST/_SUCCESSdrwxr-xr-x   - hadoop supergroup          0 2016-12-12 12:20 /user/hadoop/T_SQOOP_TEST/_logs-rw-r--r--   1 hadoop supergroup         35 2016-12-12 12:20 /user/hadoop/T_SQOOP_TEST/part-m-00000-rw-r--r--   1 hadoop supergroup         31 2016-12-12 12:21 /user/hadoop/T_SQOOP_TEST/part-m-00001-rw-r--r--   1 hadoop supergroup         33 2016-12-12 12:21 /user/hadoop/T_SQOOP_TEST/part-m-00002-rw-r--r--   1 hadoop supergroup         73 2016-12-12 12:21 /user/hadoop/T_SQOOP_TEST/part-m-00003

_SUCCESS文件为Map-Reduce任务执行成功的标志文件，_logs为日志文件，part开头的文件为导入的数据文件，每个任务生成一个文件，通过cat命令可以查看文件内容：

$ hadoop fs -cat  /user/hadoop/T_SQOOP_TEST/part-m-00000Warning: $HADOOP_HOME is deprecated.1,zhangsan,2016-12-20 00:00:00.0,1

可以看到，每一行对应数据库中一行记录，每个字段的值用逗号进行分隔。

导入时需要注意：
1）数据库表名需要大写；

Imported Failed: There is no column found in the target table xxx. Please ensure that your table name is correct.

2）数据库表没有主键时，需要指定--split-by参数或者使用--autoreset-to-one-mapper参数；
Error during import: No primary key could be found for table xxx.

3）使用查询语句（--e或--query）导入时，需要指定--split-by参数及--target-dir参数；
When importing query results in parallel, you must specify --split-by. Must specify destination with --target-dir.

4）使用查询语句导入时，需要在where子句中加入$CONDITIONS

java.io.IOException: Query [select * from xxx] must contain '$CONDITIONS' in WHERE clause.

如：

$ sqoop import  --connect jdbc:oracle:thin:@192.168.1.10:1521:TEST --username test --password test --e 'select * from T_SQOOP_TEST where $CONDITIONS' --verbose --split-by ID --target-dir temp3 --m 1

5）sqoop默认使用“,”（逗号）作为列分隔符，\n（换行符）作为行分隔符。当导入的数据中包含","或\n时可以通过--fields-terminated-by <char>参数指定列分隔符；使用--lines-terminated-by <char>参数指定行分隔符。

6）sqoop对大对象（CLOB和BLOB字段）有2种处理方式：一种方式是内嵌方式，直接将大对象和其他字段数据放在一起；另一种方式是将大对象单独存储，然后和主数据做一个关联。

通常，小于16MB的大对象字段采用第一种方式大对象和主数据一起存储。超过16MB的大对象采用第二种方式单独存储在导入目录的_lobs子目录下，每个文件最大能容纳2^63字节。可以通过--inline-lob-limit参数设置内嵌字段的大小，如果设置为0，则所有大对象将会单独存储。

3、导入到hive

在导入命令中添加--hive-import参数则将数据导入到hive中。

$ sqoop import --hive-import --connect jdbc:oracle:thin:@192.168.1.10:1521:TEST --username test --password test --table T_SQOOP_TEST --m 1 --delete-target-dir --verbose

参数说明：

参数名

参数说明

--hive-import数据导入到Hive--hive-overwrite覆盖Hive表中已存在的数据--create-hive-table设置了此参数，导入时如果hive中表已经存在，则导入任务失败。默认为false--hive-table <table-name>指定导入到Hive中的表名--hive-drop-import-delims导入Hive时，去除字符型字段中的\n（换行符）,\r（回车符）和\01（标题开始符）字符。--hive-delims-replacement导入Hive时，用用户定义的字符串替换字符型字段中的\n,\r和\01字符。

导入时需要注意：

1）Hive默认使用\01字符作为列分隔符（字段分隔符），\n和\r作为行分隔符。因此，如果导入的字符型字段的数据中包含这些字符时，就会有问题。

如：T_SQOOP_TEST表ID为2的行，NAME字段值中包含换行符，导入到Hive中，数据出现异常：

可以使用--hive-drop-import-delims参数，将导入数据中的\n,\r,\01字符去掉。也可以使用--hive-delims-replacement替换\n,\r和\01。

2）要导入的表字段名最好遵守命名规范，不要包含："\"（斜杠），","（逗号）等特殊字符，否则导入时可能会报错：

Causedby: java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

ExecutionError, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask.java.lang.RuntimeException:

MetaException(message:org.apache.hadoop.hive.serde2.SerDeExceptionorg.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe: columns has 5 elementswhile columns.types has 4 elements!)

4、导入到hbase

通过--hbase-table参数，可以将数据导入到hbase中。sqoop会把数据导入到--hbase-table参数指定的表中。

$ sqoop import --connect jdbc:oracle:thin:@192.168.1.10:1521:TEST --username test --password test --table T_SQOOP_TEST --hbase-table t_sqoop_test --hbase-create-table --column-family rowinfo

参数说明：

参数名

参数说明

--hbase-table <table-name>数据导入到hbase的表名--hbase-row-key指定要导入表的哪些列作为hbase表的row key，如果是组合列，需要将列名用逗号进行分隔--column-family <family>指定hbase表列族名称--hbase-create-table导入时如果hbase表不存在，则创建表--hbase-bulkload开启批量加载模式，可以提高导入性能

导入时需要注意：

1）如果没有使用--hbase-row-key参数，则sqoop默认使用--split-by参数指定的字段作为row key；

2）导出的每列数据都会放到相同的列族下，因此必须指定--column-family参数；

3）导入时不能使用direct模式（--direct）；

4）组合row key只能在使用--hbase-row-key参数时才有效；

5）sqoop导入时会忽略所有空值字段，row key列除外。

6）导入LOB字段：

sqoop 1.4.4 不能导入LOB字段到hbase中，

Sqoop’s direct mode does not support imports of BLOB, CLOB, or LONGVARBINARY columns.

1.4.4以后版本增加了--hbase-bulkload参数，使用--hbase-bulkload此参数可以将LOB字段导入到HBase中。

如：表T_SQOOP_TEST，字段REMARK为CLOB类型

导入到HBase表t_sqoop_test中

$ sqoop import --connect jdbc:oracle:thin:@192.168.1.10:1521:TEST --username test --password test --table T_SQOOP_TEST --hbase-table t_sqoop_test --hbase-create-table --column-family rowinfo --split-by ID --verbose --hbase-bulkload

查看导入情况

$ hbase shellhbase(main):001:0> scan 't_sqoop_test'ROW                           COLUMN+CELL 1                            column=rowinfo:CREATE_DATE, timestamp=1482374139001, value=2016-12-20 00:00:00.0 1                            column=rowinfo:NAME, timestamp=1482374139001, value=zhangsan,zhangs 1                            column=rowinfo:REMARK, timestamp=1482374139001, value=fdsafdsafd\x0Afdsafd\x0Afds\                              x0Aaf\x0Adsa\x0Af\x0Adsa 1                            column=rowinfo:VERSION, timestamp=1482374139001, value=1 2                            column=rowinfo:CREATE_DATE, timestamp=1482374139001, value=2016-12-16 00:00:00.0 2                            column=rowinfo:NAME, timestamp=1482374139001, value=lisi\x0Alis 2                            column=rowinfo:REMARK, timestamp=1482374139001, value=111\x0A22\x0A33\x0A4 2                            column=rowinfo:VERSION, timestamp=1482374139001, value=2 3                            column=rowinfo:CREATE_DATE, timestamp=1482374139001, value=2016-12-18 00:00:00.0 3                            column=rowinfo:NAME, timestamp=1482374139001, value=wangwu 3                            column=rowinfo:REMARK, timestamp=1482374139001, value=aaa\x0Abb\x0Acc\x0Add 3                            column=rowinfo:VERSION, timestamp=1482374139001, value=1 4                            column=rowinfo:CREATE_DATE, timestamp=1482374139001, value=2016-12-21 00:00:00.0 4                            column=rowinfo:NAME, timestamp=1482374139001, value=zhaozilong 4                            column=rowinfo:REMARK, timestamp=1482374139001, value=AA\x0ABB\x0ACC\x0ADD 4                            column=rowinfo:VERSION, timestamp=1482374139001, value=3 5                            column=rowinfo:CREATE_DATE, timestamp=1482374139001, value=2016-12-07 00:00:00.0 5                            column=rowinfo:NAME, timestamp=1482374139001, value=sunwukong 5                            column=rowinfo:VERSION, timestamp=1482374139001, value=15 row(s) in 0.6010 seconds

lob数据和其他数据存储在一起，可以使用 --inline-lob-limit 0 参数将lob数据独立存储

$ sqoop import --connect jdbc:oracle:thin:@192.168.1.10:1521:TEST --username test --password test --table T_SQOOP_TEST --hbase-table t_sqoop_test --hbase-create-table --column-family rowinfo --split-by ID --verbose --hbase-bulkload  --inline-lob-limit 0 

导入后，hbase数据如下（部分数据）：

hbase(main):001:0> scan 't_sqoop_test'ROW                           COLUMN+CELL 1                            column=rowinfo:CREATE_DATE, timestamp=1482375258497, value=2016-12-20 00:00:00.0 1                            column=rowinfo:DATA, timestamp=1482375258497, value=externalLob(lf,hdfs://192.168.1                              .12:9000/tmp/sqoop-hbase-attempt_201611161443_0060_m_000000_0/_lob/large_obj_atte                              mpt_201611161443_0060_m_000000_01.lob,68,7) 1                            column=rowinfo:NAME, timestamp=1482375258497, value=zhangsan,zhangs 1                            column=rowinfo:REMARK, timestamp=1482375258497, value=externalLob(lf,hdfs://192.168.1                              .12:9000/tmp/sqoop-hbase-attempt_201611161443_0060_m_000000_0/_lob/large_obj_at                              tempt_201611161443_0060_m_000000_00.lob,68,34) 1                            column=rowinfo:VERSION, timestamp=1482375258497, value=1

DATA为BLOB字段，REMARK为CLOB字段，两个字段值为LOB数据存储的路径。

5、增量导入

Sqoop提供了增量导入的模式，能够只导入新增加的数据。

$ sqoop import --connect jdbc:oracle:thin:@192.168.1.10:1521:TEST --username test --password test --table T_SQOOP_TEST --split-by ID --verbose --m 1 --check-column ID --incremental append --last-value 5

参数说明

参数名

参数说明

--check-column (col)校验列，导入时校验此列的值，只导入满足条件的记录。（此列类型不能是 CHAR/NCHAR/VARCHAR/VARNCHAR/ LONGVARCHAR/LONGNVARCHAR）--incremental (mode)新纪录判断模式，包括：append和lastmodified--last-value (value)上一次导入时，校验列的最大值

Sqoop支持两种增量导入模式：append和lastmodified。可以通过--incremental参数指定按哪一种种模式导入数据。

append模式：追加模式，适合于导入新增数据，每次导入校验列值比--last-value参数值大的数。

lastmodified模式：修改模式，适合于导入修改后的数据，数据表需要设置一个记录更新时间的字段（check-column），每次修改记录时，记录当前时间戳，导入数据时，只导入比--last-value参数值新的数据（大于--last-value参数值）。

如：

$ sqoop import --connect jdbc:oracle:thin:@192.168.1.10:1521:TEST --username test --password test --table T_SQOOP_TEST --split-by ID --verbose --m 1 --check-column CREATE_DATE --incremental lastmodified --last-value '2016-12-20 00:00:00'

校验CREATE_DATE字段，导入2016-12-20 00:00:00以后的数据。

6、定时增量导入

上一节中我们讲述了如何实现增量导入，但每次都需要手动设置导入参数，然后执行导入命令。很多时候，我们希望增量导入能够自动执行，下面我们介绍如何实现自动增量导入。

Sqoop提供了job的支持，我们可以将导入命令保存到job中，这样我们需要执行导入命令的时候就不需要重新输入，直接调用job就可以。使用job进行增量导入任务时，每次执行任务后，sqoop会记录校验列的最大值，下一次执行时，会将记录的最大值作为--last-value参数值，从而保证每次执行job都能导入最新的数据。
可以使用如下命令，对sqoop job进行操作

$ sqoop job (generic-args) (job-args) [-- [subtool-name] (subtool-args)]$ sqoop-job (generic-args) (job-args) [-- [subtool-name] (subtool-args)]

参数说明

参数名

参数说明

--create <job-id>定义一个指定名称（job-id）的job。job要执行的命令用--进行分割。--delete <job-id>删除指定名称（job-id）的job--exec <job-id>执行指定名称（job-id）的job--show <job-id>显示指定名称（job-id）job的参数--list列出所有已定义的job

创建job：

sqoop-job --create test-job -- import --connect jdbc:oracle:thin:@192.168.1.10:1521:TEST --username test --password test --table T_SQOOP_TEST --split-by ID --m 1 --check-column ID --incremental append --last-value '6'

查看job列表：

$ sqoop-job --listAvailable jobs:  test-job

显示job参数：

$ sqoop-job --show test-jobJob: test-jobTool: importOptions:----------------------------verbose = falseincremental.last.value = 6db.connect.string = jdbc:oracle:thin:@192.168.1.10:1521:TESTcodegen.output.delimiters.escape = 0codegen.output.delimiters.enclose.required = falsecodegen.input.delimiters.field = 0hbase.create.table = falsedb.require.password = falsehdfs.append.dir = truedb.table = T_SQOOP_TEST...

执行job：

$ sqoop-job -exec test-job...16/12/12 13:09:44 INFO tool.ImportTool: Maximal id query for free form incremental import: SELECT MAX(ID) FROM T_SQOOP_TEST16/12/12 13:09:46 INFO tool.ImportTool: Incremental import based on column ID16/12/12 13:09:46 INFO tool.ImportTool: Lower bound value: 616/12/12 13:09:46 INFO tool.ImportTool: Upper bound value: 7...

查询T_SQOOP_TEST表ID字段的最大值，如果最大值大于--last-value参数值（6）时，则执行导入数据。

再次执行job：

$ sqoop-job -exec test-job...16/12/12 13:15:45 INFO tool.ImportTool: Maximal id query for free form incremental import: SELECT MAX(ID) FROM T_SQOOP_TEST16/12/12 13:15:45 INFO tool.ImportTool: Incremental import based on column ID16/12/12 13:15:45 INFO tool.ImportTool: Lower bound value: 716/12/12 13:15:45 INFO tool.ImportTool: Upper bound value: 8...

此时--last-value参数值变为7，上次导入数据ID字段的最大值。

注意：执行job时，控制台会提示输入数据库密码，但我们已经在命令中设置了数据库密码了，为什么还要重新输入密码呢？原来，sqoop为完全考虑，默认是不保存数据库密码的，为了方便测试，我们可以修改sqoop的配置文件，将数据库密码也保存到job中，修改 conf/sqoop-site.xml文件，将sqoop.metastore.client.record.password设置为true。

<property>    <name>sqoop.metastore.client.record.password</name>    <value>true</value>    <description>If true, allow saved passwords in the metastore.    </description></property>

最后，我们将job添加到linux定时任务中，由linux定时任务来自动执行sqoop job进行增量导入：

$ crontab -e

添加定时任务

*/5 * * * * /home/hadoop/sqoop-1.4.6.bin__hadoop-1.0.0/bin/sqoop job --exec test-job > test-job.out 2>&1 &

*/5 ：表示每5分钟执行一次；

执行日志输出到当前用户主目录的test-job.out文件中。


参考文献

sqoop1.4.6官方文档：http://sqoop.apache.org/docs/1.4.6/SqoopUserGuide.html