Sqoop的命令使用

转载：http://blog.csdn.net/zleven/article/details/53781111

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

1、导入命令及参数介绍

命令格式

[plain]  view plain  copy

1. $ sqoop import (generic-args) (import-args)  
2. $ 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数据库已建立一个准备导入的数据表：

[sql]  view plain  copy

1. create table T_SQOOP_TEST  
2. (  
3.   id          NUMBER primary key,  
4.   name        VARCHAR2(32),  
5.   create_date DATE default sysdate,  
6.   version     NUMBER(4)  
7. )  

ID为主键。表数据：

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

[plain]  view plain  copy

1. $ 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）的最大值和最小值，计算出每个任务的导入范围。

[plain]  view plain  copy

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

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

[plain]  view plain  copy

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

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

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

[plain]  view plain  copy

1. $ hadoop fs -ls  
2. drwxr-xr-x   - hadoop supergroup          0 2016-12-12 12:21 /user/hadoop/T_SQOOP_TEST  

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

[plain]  view plain  copy

1. $ hadoop fs -ls /user/hadoop/T_SQOOP_TEST/  
2. Warning: $HADOOP_HOME is deprecated.  
3.   
4. Found 6 items  
5. -rw-r--r--   1 hadoop supergroup          0 2016-12-12 12:21 /user/hadoop/T_SQOOP_TEST/_SUCCESS  
6. drwxr-xr-x   - hadoop supergroup          0 2016-12-12 12:20 /user/hadoop/T_SQOOP_TEST/_logs  
7. -rw-r--r--   1 hadoop supergroup         35 2016-12-12 12:20 /user/hadoop/T_SQOOP_TEST/part-m-00000  
8. -rw-r--r--   1 hadoop supergroup         31 2016-12-12 12:21 /user/hadoop/T_SQOOP_TEST/part-m-00001  
9. -rw-r--r--   1 hadoop supergroup         33 2016-12-12 12:21 /user/hadoop/T_SQOOP_TEST/part-m-00002  
10. -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命令可以查看文件内容：

[plain]  view plain  copy

1. $ hadoop fs -cat  /user/hadoop/T_SQOOP_TEST/part-m-00000  
2. Warning: $HADOOP_HOME is deprecated.  
3.   
4. 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.

如：

[plain]  view plain  copy

1. $ sqoop import  --connect jdbc:oracle:thin:@192.168.1.10:1521:TEST --username test --password test   
2. --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，则所有大对象将会单独存储。

6）如果在导入时候使用了查询语句，如果查询语句中使用了where查询，需要在在后边这么使用：

select  * from table1,table2   where table1.id=table2.id  and $CONDITIONS

完整的使用方法：sqoop import --connect jdbc:mysql://数据库ip:3306/数据库名--username 用户名--password 密码 --query '上边的查询语句' --split-by 以哪个表的id开始分割  --target-dir /user/warehouse/WaiWei/hdfs上的数据表名文件夹

如果你在select语句使用了“”括起来了，则 where中的这样使用：

where table1.id=table2.id  and \$CONDITIONS

3、导入到hive

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

[plain]  view plain  copy

1. $ sqoop import --hive-import --connect jdbc:oracle:thin:@192.168.1.10:1521:TEST --username test --password test   
2. --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参数指定的表中。

[plain]  view plain  copy

1. $ sqoop import --connect jdbc:oracle:thin:@192.168.1.10:1521:TEST --username test --password test --table T_SQOOP_TEST   
2. --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中，

[plain]  view plain  copy

1. 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中

[plain]  view plain  copy

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

查看导入情况

[plain]  view plain  copy

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

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

[plain]  view plain  copy

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

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

[plain]  view plain  copy

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

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

5、增量导入

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

[html]  view plain  copy

1. $ sqoop import --connect jdbc:oracle:thin:@192.168.1.10:1521:TEST --username test --password test --table T_SQOOP_TEST   
2. --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参数值）。

如：

[plain]  view plain  copy

1. $ 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   
2. --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进行操作

[plain]  view plain  copy

1. $ sqoop job (generic-args) (job-args) [-- [subtool-name] (subtool-args)]  
2. $ 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：

[plain]  view plain  copy

1. 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列表：

[html]  view plain  copy

1. $ sqoop-job --list  
2.   
3. Available jobs:  
4.   test-job  

显示job参数：

[plain]  view plain  copy

1. $ sqoop-job --show test-job  
2.   
3. Job: test-job  
4. Tool: import  
5. Options:  
6. ----------------------------  
7. verbose = false  
8. incremental.last.value = 6  
9. db.connect.string = jdbc:oracle:thin:@192.168.1.10:1521:TEST  
10. codegen.output.delimiters.escape = 0  
11. codegen.output.delimiters.enclose.required = false  
12. codegen.input.delimiters.field = 0  
13. hbase.create.table = false  
14. db.require.password = false  
15. hdfs.append.dir = true  
16. db.table = T_SQOOP_TEST  
17. ...  

执行job：

[plain]  view plain  copy

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

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

再次执行job：

[html]  view plain  copy

1. $ sqoop-job -exec test-job  
2. ...  
3. 16/12/12 13:15:45 INFO tool.ImportTool: Maximal id query for free form incremental import: SELECT MAX(ID) FROM T_SQOOP_TEST  
4. 16/12/12 13:15:45 INFO tool.ImportTool: Incremental import based on column ID  
5. 16/12/12 13:15:45 INFO tool.ImportTool: Lower bound value: 7  
6. 16/12/12 13:15:45 INFO tool.ImportTool: Upper bound value: 8  
7. ...  

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

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

[html]  view plain  copy

1. <property>  
2.     <name>sqoop.metastore.client.record.password</name>  
3.     <value>true</value>  
4.     <description>If true, allow saved passwords in the metastore.  
5.     </description>  
6. </property>  

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

[plain]  view plain  copy

1. $ crontab -e  

添加定时任务

[html]  view plain  copy

1. */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