sqoop主要的功能是将hdfs中的数据和mysql数据库中的数据互相导入导出
- 查看数据库:
sqoop list-databases -connect jdbc:mysql:localhost:3306 -username xxxx -password xxx
- 查看特定数据库中的数据化
sqoop list-table -connect jdbc:mysql:localhost:3306/xxx -username xxx -password xxxx
- 将数据库中数据导入到hdfs中
sqoop import -connect jdbc:mysql:localhost:3306/xxx -username xxx -password xxx -table xxx -m 1 -target-dir /hdfs中目录
- 将hdfs中的数据导出到mysql中
sqoop export -connect jdbc:mysql:localhost:3306/xxx -username xxx -password xxx -table xxx -export-dir hdfs://localhost:9000/xxxx
#注意首先要在mysql中加你一个表结构 - 创建一个job任务
sqoop job -create myjob -- import -connect jdbc:mysql://localhost:3306/ mysqoop -username ......
- 查看job任务
sqoop job --list
- 执行一个job任务
sqoop job --exec myjob
- 删除一个job任务
sqoop job --delete myjob
sqoop是一个用于在Hadoop和关系型数据库(Oracle,Mysql…)间数据传递的开源工具。下面以Oracle为例,介绍使用sqoop将数据从Oracle导入到Hadoop中(HDFS、Hive和HBase)。
1、导入命令及参数介绍
命令格式
-
$ sqoop <span class="hljs-keyword"><span class="hljs-keyword">import</span></span> (generic-args) (<span class="hljs-keyword"><span class="hljs-keyword">import</span></span>-args)</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">$ 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> | 非字符类型字段的空值字符串 |
2、导入数据到HDFS
-
create
table T_SQOOP_TEST
-
(
-
id
NUMBER primary
key,
-
name VARCHAR2(
32),
-
create_date
DATE
default
sysdate,
-
version
NUMBER(
4)
-
)
$ 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_TEST
-
16/
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,导入速度会更快一点。
-
$ hadoop fs -ls
-
drwxr-xr-x - hadoop supergroup
0
2016-
12-
12
12
:
21 /user/hadoop/T_SQOOP_TEST
可以看到在hdfs中生成了一个以导入表表名命名的文件夹,查看文件夹内容:
-
$ hadoop fs -ls /user/hadoop/T_SQOOP_TEST/</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">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/_SUCCESS
-
drwxr-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-<span class="hljs-number"><span class="hljs-number">00000</span></span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-symbol"><span class="hljs-symbol">Warning:</span></span> $HADOOP_HOME is deprecated.
-
-
1,zhangsan,
2016-
12-
20
00
:
00
:
00.
0,
1
可以看到,每一行对应数据库中一行记录,每个字段的值用逗号进行分隔。
导入时需要注意:
1)数据库表名需要大写;
Error during import: No primary key could be found for table xxx.
When importing query results in parallel, you must specify –split-by. Must specify destination with –target-dir.
-
$ sqoop <span class="hljs-keyword"><span class="hljs-keyword">import</span></span> --connect jdbc:oracle:thin:@192.168.1.10:1521:TEST --username test --password test </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">--e 'select * from <span class="hljs-type"><span class="hljs-type">T_SQOOP_TEST</span></span> <span class="hljs-keyword"><span class="hljs-keyword">where</span></span> $
CONDITIONS’ –verbose –
split-by
ID –target-dir temp3 –m
1
3、导入到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字符。 |
4、导入到hbase
-
$ 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 | 开启批量加载模式,可以提高导入性能 |
Sqoop’s direct mode does not support imports of BLOB, CLOB, or LONGVARBINARY columns.
1.4.4以后版本增加了–hbase-bulkload参数,使用–hbase-bulkload此参数可以将LOB字段导入到HBase中。
-
$ 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 shell
-
hbase
(
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=
1
-
5 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 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 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进行操作
-
$ sqoop job (<span class="hljs-keyword"><span class="hljs-keyword">generic</span></span>-args) (job-args) [-- [subtool-<span class="hljs-keyword"><span class="hljs-keyword">name</span></span>] (subtool-args)]</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">$ 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 –list Available jobs: test-job
显示job参数:
-
$ sqoop-job –show test-job
-
-
Job: test-job
-
Tool: import
-
Options:
-
—————————-
-
verbose =
false
-
incremental.last.value =
6
-
db.connect.string =
jdbc:
oracle:
thin:@192.
168.1.
10
:
1521
:TEST
-
codegen.output.delimiters.escape =
0
-
codegen.output.delimiters.enclose.required =
false
-
codegen.input.delimiters.field =
0
-
hbase.create.table =
false
-
db.
require.password =
false
-
hdfs.append.dir =
true
-
db.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_TEST
-
16/
12/
12
13:
09:
46 INFO tool.ImportTool: Incremental
import based
on
column
ID
-
16/
12/
12
13:
09:
46 INFO tool.ImportTool:
Lower
bound
value:
6
-
16/
12/
12
13:
09:
46 INFO tool.ImportTool:
Upper
bound
value:
7
-
…
查询T_SQOOP_TEST表ID字段的最大值,如果最大值大于–last-value参数值(6)时,则执行导入数据。
此时–last-value参数值变为7,上次导入数据ID字段的最大值。
$ 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_TEST 16/12/12 13:15:45 INFO tool.ImportTool: Incremental import based on column ID 16/12/12 13:15:45 INFO tool.ImportTool: Lower bound value: 7 16/12/12 13:15:45 INFO tool.ImportTool: Upper bound value: 8 …
-
<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分钟执行一次;
参考文献
后面是附录,我把前面攒得一些东西放在这里了。
–connect <jdbc-uri> jdbc连接地址
–connection-manager <class-name> 连接管理者
–driver <class-name> 驱动类
–hadoop-mapred-home <dir> $HADOOP_MAPRED_HOME
–help help信息
-P 从命令行输入密码
–password <password> 密码
–username <username> 账号
–verbose 打印信息
–connection-param-file <filename> 可选参数
Argument Description
–append 添加到hdfs中已经存在的dataset
–as-avrodatafile 导入数据作为avrodata
–as-sequencefile 导入数据位SequenceFiles
–as-textfile 默认导入数据为文本
–boundary-query <statement> 创建splits的边界
–columns <col,col,col…> 选择列
–direct 使用直接导入快速路径
–direct-split-size <n> 在快速模式下每n字节使用一个split
–fetch-size <n> 一次读入的数量
–inline-lob-limit <n> 最大数值 an inline LOB
-m,–num-mappers <n> 通过实行多少个map,默认是4个,某些数据库8 or 16性能不错
-e,–query <statement> 通过查询语句导入
–split-by <column-name> 创建split的列,默认是主键
–table <table-name> 要导入的表名
–target-dir <dir> HDFS 目标路径
–warehouse-dir <dir> HDFS parent for table destination
–where <where clause> where条件
-z,–compress Enable compression
–compression-codec <c> 压缩方式,默认是gzip
–null-string <null-string> 字符列null值
–null-non-string <null-string> 非字符列null值
export主要参数
–direct 快速导入
–export-dir <dir> HDFS到处数据的目录
-m,–num-mappers <n> 都少个map线程
–table <table-name> 导出哪个表
–call <stored-proc-name> 存储过程
–update-key <col-name> 通过哪个字段来判断更新
–update-mode <mode> 插入模式,默认是只更新,可以设置为allowinsert.
–input-null-string <null-string> 字符类型null处理
–input-null-non-string <null-string> 非字符类型null处理
–staging-table <staging-table-name> 临时表
–clear-staging-table 清空临时表
–batch 批量模式
转义字符相关参数。
Argument Description
–enclosed-by <char> 设置字段结束符号
–escaped-by <char> 用哪个字符来转义
–fields-terminated-by <char> 字段之间的分隔符
–lines-terminated-by <char> 行分隔符
–mysql-delimiters 使用mysql的默认分隔符: , lines: \n escaped-by: \ optionally-enclosed-by: ’
–op