sqoop是一个用来将hadoop（hive、hbase）和关系型数据库中的数据相互转移的工具，可以将一个关系型数据库（例如：mysql ,oracle ,postgres等）中的数据导入到hadoop的hdfs中，也可以将hdfs的数据导入到关系型数据库中。sqoop目前已经是apache的顶级项目了，
sqoop是一个用来将hadoop（hive、hbase）和关系型数据库中的数据相互转移的工具，可以将一个关系型数据库（例如：mysql ,oracle ,postgres等）中的数据导入到hadoop的hdfs中，也可以将hdfs的数据导入到关系型数据库中。
sqoop目前已经是apache的顶级项目了，目前版本是1.4.4 和 sqoop2 1.99.3，本文以1.4.4的版本为例讲解基本的安装配置和简单应用的演示。
- 安装配置
- 准备测试数据
- 导入数据到hdfs
- 导入数据到hive
- 导入数据到hbase
[一]、安装配置
选择sqoop 1.4.4 版本：sqoop-1.4.4.bin__hadoop-2.0.4-alpha.tar.gz
1.1、下载后解压配置：
tar -zxvf sqoop-1.4.4.bin__hadoop-2.0.4-alpha.tar.gz /usr/local/
cd /usr/local
ln -s sqoop-1.4.4.bin__hadoop-2.0.4-alpha sqoop
1.2、环境变量配置 vi ~/.bash_profile ：
#sqoop add by micmiu.com
export sqoop_home=/usr/local/sqoop
export path=$sqoop_home/bin:$path
1.3、配置sqoop参数：
复制/conf/sqoop-env-template.sh 一份重命名为：/conf/sqoop-env.sh
vi ?/conf/sqoop-env.sh
# 指定各环境变量的实际配置
# set hadoop-specific environment variables here.
#set path to where bin/hadoop is available
#export hadoop_common_home=
#set path to where hadoop-*-core.jar is available
#export hadoop_mapred_home=
#set the path to where bin/hbase is available
#export hbase_home=
#set the path to where bin/hive is available
#export hive_home=
ps：因为我当前用户的默认环境变量中已经配置了相关变量，故该配置文件无需再修改：
# hadoop
export hadoop_prefix="/usr/local/hadoop"
export hadoop_home=${hadoop_prefix}
export path=$path:$hadoop_prefix/bin:$hadoop_prefix/sbin
export hadoop_common_home=${hadoop_prefix}
export hadoop_hdfs_home=${hadoop_prefix}
export hadoop_mapred_home=${hadoop_prefix}
export hadoop_yarn_home=${hadoop_prefix}
# native path
export hadoop_common_lib_native_dir=${hadoop_prefix}/lib/native
export hadoop_opts="-djava.library.path=$hadoop_prefix/lib/native"
# hadoop end
#hive
export hive_home=/usr/local/hive
export path=$hive_home/bin:$path
#hbase
export hbase_home=/usr/local/hbase
export path=$hbase
#add by micmiu.com
1.4、驱动jar包
下面测试演示以mysql为例，则需要把mysql对应的驱动lib文件copy到 /lib 目录下。
[二]、测试数据准备
以mysql 为例：
- 192.168.6.77（hostname:master.hadoop）
- database: test
- 用户：root 密码：micmiu
准备两张测试表一个有主键表demo_blog，一个无主键表 demo_log：
create table `demo_blog` (
`id` int(11) not null auto_increment,
`blog` varchar(100) not null,
primary key (`id`)
) engine=myisam default charset=utf8;
create table `demo_log` (
`operator` varchar(16) not null,
`log` varchar(100) not null
) engine=myisam default charset=utf8;
插入测试数据：
insert into demo_blog (id, blog) values (1, "micmiu.com");
insert into demo_blog (id, blog) values (2, "ctosun.com");
insert into demo_blog (id, blog) values (3, "baby.micmiu.com");
insert into demo_log (operator, log) values ("micmiu", "create");
insert into demo_log (operator, log) values ("micmiu", "update");
insert into demo_log (operator, log) values ("michael", "edit");
insert into demo_log (operator, log) values ("michael", "delete");
[三]、导入数据到hdfs
3.1、导入有主键的表
比如我需要把表 demo_blog (含主键) 的数据导入到hdfs中，执行如下命令：
sqoop import --connect jdbc:mysql://192.168.6.77/test --username root --password micmiu --table demo_blog
执行过程如下：
$ sqoop import --connect jdbc:mysql://192.168.6.77/test --username root --password micmiu --table demo_blog
warning: /usr/lib/hcatalog does not exist! hcatalog jobs will fail.
please set $hcat_home to the root of your hcatalog installation.
14/04/09 09:58:43 warn tool.basesqooptool: setting your password on the command-line is insecure. consider using -p instead.
14/04/09 09:58:43 info manager.mysqlmanager: preparing to use a mysql streaming resultset.
14/04/09 09:58:43 info tool.codegentool: beginning code generation
14/04/09 09:58:43 info manager.sqlmanager: executing sql statement: select t.* from `demo_blog` as t limit 1
14/04/09 09:58:43 info manager.sqlmanager: executing sql statement: select t.* from `demo_blog` as t limit 1
14/04/09 09:58:43 info orm.compilationmanager: hadoop_mapred_home is /usr/local/hadoop
note: /tmp/sqoop-hadoop/compile/e8fd26a5bca5b7f51cdb03bf847ce389/demo_blog.java uses or overrides a deprecated api.
note: recompile with -xlint:deprecation for details.
14/04/09 09:58:44 info orm.compilationmanager: writing jar file: /tmp/sqoop-hadoop/compile/e8fd26a5bca5b7f51cdb03bf847ce389/demo_blog.jar
14/04/09 09:58:44 warn manager.mysqlmanager: it looks like you are importing from mysql.
14/04/09 09:58:44 warn manager.mysqlmanager: this transfer can be faster! use the --direct
14/04/09 09:58:44 warn manager.mysqlmanager: option to exercise a mysql-specific fast path.
14/04/09 09:58:44 info manager.mysqlmanager: setting zero datetime behavior to converttonull (mysql)
14/04/09 09:58:44 info mapreduce.importjobbase: beginning import of demo_blog
slf4j: class path contains multiple slf4j bindings.
slf4j: found binding in [jar:file:/usr/local/hadoop-2.2.0/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/staticloggerbinder.class]
slf4j: found binding in [jar:file:/usr/local/hbase-0.98.0-hadoop2/lib/slf4j-log4j12-1.6.4.jar!/org/slf4j/impl/staticloggerbinder.class]
slf4j: see http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
slf4j: actual binding is of type [org.slf4j.impl.log4jloggerfactory]
14/04/09 09:58:44 info configuration.deprecation: mapred.jar is deprecated. instead, use mapreduce.job.jar
14/04/09 09:58:45 info configuration.deprecation: mapred.map.tasks is deprecated. instead, use mapreduce.job.maps
14/04/09 09:58:45 info client.rmproxy: connecting to resourcemanager at master.hadoop/192.168.6.77:8032
14/04/09 09:58:47 info db.datadrivendbinputformat: boundingvalsquery: select min(`id`), max(`id`) from `demo_blog`
14/04/09 09:58:47 info mapreduce.jobsubmitter: number of splits:3
14/04/09 09:58:47 info configuration.deprecation: mapred.job.classpath.files is deprecated. instead, use mapreduce.job.classpath.files
14/04/09 09:58:47 info configuration.deprecation: user.name is deprecated. instead, use mapreduce.job.user.name
14/04/09 09:58:47 info configuration.deprecation: mapred.cache.files.filesizes is deprecated. instead, use mapreduce.job.cache.files.filesizes
14/04/09 09:58:47 info configuration.deprecation: mapred.cache.files is deprecated. instead, use mapreduce.job.cache.files
14/04/09 09:58:47 info configuration.deprecation: mapred.reduce.tasks is deprecated. instead, use mapreduce.job.reduces
14/04/09 09:58:47 info configuration.deprecation: mapred.output.value.class is deprecated. instead, use mapreduce.job.output.value.class
14/04/09 09:58:47 info configuration.deprecation: mapreduce.map.class is deprecated. instead, use mapreduce.job.map.class
14/04/09 09:58:47 info configuration.deprecation: mapred.job.name is deprecated. instead, use mapreduce.job.name
14/04/09 09:58:47 info configuration.deprecation: mapreduce.inputformat.class is deprecated. instead, use mapreduce.job.inputformat.class
14/04/09 09:58:47 info configuration.deprecation: mapred.output.dir is deprecated. instead, use mapreduce.output.fileoutputformat.outputdir
14/04/09 09:58:47 info configuration.deprecation: mapreduce.outputformat.class is deprecated. instead, use mapreduce.job.outputformat.class
14/04/09 09:58:47 info configuration.deprecation: mapred.cache.files.timestamps is deprecated. instead, use mapreduce.job.cache.files.timestamps
14/04/09 09:58:47 info configuration.deprecation: mapred.output.key.class is deprecated. instead, use mapreduce.job.output.key.class
14/04/09 09:58:47 info configuration.deprecation: mapred.working.dir is deprecated. instead, use mapreduce.job.working.dir
14/04/09 09:58:47 info mapreduce.jobsubmitter: submitting tokens for job: job_1396936838233_0001
14/04/09 09:58:47 info impl.yarnclientimpl: submitted application application_1396936838233_0001 to resourcemanager at master.hadoop/192.168.6.77:8032
14/04/09 09:58:47 info mapreduce.job: the url to track the job: http://master.hadoop:8088/proxy/application_1396936838233_0001/
14/04/09 09:58:47 info mapreduce.job: running job: job_1396936838233_0001
14/04/09 09:59:00 info mapreduce.job: job job_1396936838233_0001 running in uber mode : false
14/04/09 09:59:00 info mapreduce.job: map 0% reduce 0%
14/04/09 09:59:14 info mapreduce.job: map 33% reduce 0%
14/04/09 09:59:16 info mapreduce.job: map 67% reduce 0%
14/04/09 09:59:19 info mapreduce.job: map 100% reduce 0%
14/04/09 09:59:19 info mapreduce.job: job job_1396936838233_0001 completed successfully
14/04/09 09:59:19 info mapreduce.job: counters: 27
file system counters
file: number of bytes read=0
file: number of bytes written=271866
file: number of read operations=0
file: number of large read operations=0
file: number of write operations=0
hdfs: number of bytes read=295
hdfs: number of bytes written=44
hdfs: number of read operations=12
hdfs: number of large read operations=0
hdfs: number of write operations=6
job counters
launched map tasks=3
other local map tasks=3
total time spent by all maps in occupied slots (ms)=43032
total time spent by all reduces in occupied slots (ms)=0
map-reduce framework
map input records=3
map output records=3
input split bytes=295
spilled records=0
failed shuffles=0
merged map outputs=0
gc time elapsed (ms)=590
cpu time spent (ms)=6330
physical memory (bytes) snapshot=440934400
virtual memory (bytes) snapshot=3882573824
total committed heap usage (bytes)=160563200
file input format counters
bytes read=0
file output format counters
bytes written=44
14/04/09 09:59:19 info mapreduce.importjobbase: transferred 44 bytes in 34.454 seconds (1.2771 bytes/sec)
14/04/09 09:59:19 info mapreduce.importjobbase: retrieved 3 records.
验证导入到hdfs上的数据：
$ hdfs dfs -ls /user/hadoop/demo_blog
found 4 items
-rw-r--r-- 3 hadoop supergroup 0 2014-04-09 09:59 /user/hadoop/demo_blog/_success
-rw-r--r-- 3 hadoop supergroup 13 2014-04-09 09:59 /user/hadoop/demo_blog/part-m-00000
-rw-r--r-- 3 hadoop supergroup 13 2014-04-09 09:59 /user/hadoop/demo_blog/part-m-00001
-rw-r--r-- 3 hadoop supergroup 18 2014-04-09 09:59 /user/hadoop/demo_blog/part-m-00002
[hadoop@master ~]$ hdfs dfs -cat /user/hadoop/demo_blog/part-m-0000*
1,micmiu.com
2,ctosun.com
3,baby.micmiu.com
ps：默认设置下导入到hdfs上的路径是：?/user/username/tablename/(files)，比如我的当前用户是hadoop，那么实际路径即：?/user/hadoop/demo_blog/(files)。
如果要自定义路径需要增加参数：--warehouse-dir 比如：
sqoop import --connect jdbc:mysql://master.hadoop/test --username root --password micmiu --table demo_blog --warehouse-dir /user/micmiu/sqoop
3.2、导入不含主键的表
比如需要把表 demo_log（无主键） 的数据导入到hdfs中，执行如下命令：
sqoop import --connect jdbc:mysql://192.168.6.77/test --username root --password micmiu --table demo_log --warehouse-dir /user/micmiu/sqoop --split-by operator
ps：无主键表的导入需要增加参数? --split-by xxx ?或者 -m 1
执行过程：
$ sqoop import --connect jdbc:mysql://192.168.6.77/test --username root --password micmiu --table demo_log --warehouse-dir /user/micmiu/sqoop --split-by operator
warning: /usr/lib/hcatalog does not exist! hcatalog jobs will fail.
please set $hcat_home to the root of your hcatalog installation.
14/04/09 15:02:06 warn tool.basesqooptool: setting your password on the command-line is insecure. consider using -p instead.
14/04/09 15:02:06 info manager.mysqlmanager: preparing to use a mysql streaming resultset.
14/04/09 15:02:06 info tool.codegentool: beginning code generation
14/04/09 15:02:06 info manager.sqlmanager: executing sql statement: select t.* from `demo_log` as t limit 1
14/04/09 15:02:06 info manager.sqlmanager: executing sql statement: select t.* from `demo_log` as t limit 1
14/04/09 15:02:06 info orm.compilationmanager: hadoop_mapred_home is /usr/local/hadoop
note: /tmp/sqoop-hadoop/compile/dddc1bcdba30515f95a2d604f22e4fe9/demo_log.java uses or overrides a deprecated api.
note: recompile with -xlint:deprecation for details.
14/04/09 15:02:07 info orm.compilationmanager: writing jar file: /tmp/sqoop-hadoop/compile/dddc1bcdba30515f95a2d604f22e4fe9/demo_log.jar
14/04/09 15:02:07 warn manager.mysqlmanager: it looks like you are importing from mysql.
14/04/09 15:02:07 warn manager.mysqlmanager: this transfer can be faster! use the --direct
14/04/09 15:02:07 warn manager.mysqlmanager: option to exercise a mysql-specific fast path.
14/04/09 15:02:07 info manager.mysqlmanager: setting zero datetime behavior to converttonull (mysql)
14/04/09 15:02:07 info mapreduce.importjobbase: beginning import of demo_log
slf4j: class path contains multiple slf4j bindings.
slf4j: found binding in [jar:file:/usr/local/hadoop-2.2.0/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/staticloggerbinder.class]
slf4j: found binding in [jar:file:/usr/local/hbase-0.98.0-hadoop2/lib/slf4j-log4j12-1.6.4.jar!/org/slf4j/impl/staticloggerbinder.class]
slf4j: see http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
slf4j: actual binding is of type [org.slf4j.impl.log4jloggerfactory]
14/04/09 15:02:07 info configuration.deprecation: mapred.jar is deprecated. instead, use mapreduce.job.jar
14/04/09 15:02:08 info configuration.deprecation: mapred.map.tasks is deprecated. instead, use mapreduce.job.maps
14/04/09 15:02:08 info client.rmproxy: connecting to resourcemanager at master.hadoop/192.168.6.77:8032
14/04/09 15:02:10 info db.datadrivendbinputformat: boundingvalsquery: select min(`operator`), max(`operator`) from `demo_log`
14/04/09 15:02:10 warn db.textsplitter: generating splits for a textual index column.
14/04/09 15:02:10 warn db.textsplitter: if your database sorts in a case-insensitive order, this may result in a partial import or duplicate records.
14/04/09 15:02:10 warn db.textsplitter: you are strongly encouraged to choose an integral split column.
14/04/09 15:02:10 info mapreduce.jobsubmitter: number of splits:4
14/04/09 15:02:10 info configuration.deprecation: mapred.job.classpath.files is deprecated. instead, use mapreduce.job.classpath.files
14/04/09 15:02:10 info configuration.deprecation: user.name is deprecated. instead, use mapreduce.job.user.name
14/04/09 15:02:10 info configuration.deprecation: mapred.cache.files.filesizes is deprecated. instead, use mapreduce.job.cache.files.filesizes
14/04/09 15:02:10 info configuration.deprecation: mapred.cache.files is deprecated. instead, use mapreduce.job.cache.files
14/04/09 15:02:10 info configuration.deprecation: mapred.reduce.tasks is deprecated. instead, use mapreduce.job.reduces
14/04/09 15:02:10 info configuration.deprecation: mapred.output.value.class is deprecated. instead, use mapreduce.job.output.value.class
14/04/09 15:02:10 info configuration.deprecation: mapreduce.map.class is deprecated. instead, use mapreduce.job.map.class
14/04/09 15:02:10 info configuration.deprecation: mapred.job.name is deprecated. instead, use mapreduce.job.name
14/04/09 15:02:10 info configuration.deprecation: mapreduce.inputformat.class is deprecated. instead, use mapreduce.job.inputformat.class
14/04/09 15:02:10 info configuration.deprecation: mapred.output.dir is deprecated. instead, use mapreduce.output.fileoutputformat.outputdir
14/04/09 15:02:10 info configuration.deprecation: mapreduce.outputformat.class is deprecated. instead, use mapreduce.job.outputformat.class
14/04/09 15:02:10 info configuration.deprecation: mapred.cache.files.timestamps is deprecated. instead, use mapreduce.job.cache.files.timestamps
14/04/09 15:02:10 info configuration.deprecation: mapred.output.key.class is deprecated. instead, use mapreduce.job.output.key.class
14/04/09 15:02:10 info configuration.deprecation: mapred.working.dir is deprecated. instead, use mapreduce.job.working.dir
14/04/09 15:02:10 info mapreduce.jobsubmitter: submitting tokens for job: job_1396936838233_0003
14/04/09 15:02:10 info impl.yarnclientimpl: submitted application application_1396936838233_0003 to resourcemanager at master.hadoop/192.168.6.77:8032
14/04/09 15:02:10 info mapreduce.job: the url to track the job: http://master.hadoop:8088/proxy/application_1396936838233_0003/
14/04/09 15:02:10 info mapreduce.job: running job: job_1396936838233_0003
14/04/09 15:02:17 info mapreduce.job: job job_1396936838233_0003 running in uber mode : false
14/04/09 15:02:17 info mapreduce.job: map 0% reduce 0%
14/04/09 15:02:28 info mapreduce.job: map 25% reduce 0%
14/04/09 15:02:30 info mapreduce.job: map 50% reduce 0%
14/04/09 15:02:33 info mapreduce.job: map 100% reduce 0%
14/04/09 15:02:33 info mapreduce.job: job job_1396936838233_0003 completed successfully
14/04/09 15:02:33 info mapreduce.job: counters: 27
file system counters
file: number of bytes read=0
file: number of bytes written=362536
file: number of read operations=0
file: number of large read operations=0
file: number of write operations=0
hdfs: number of bytes read=516
hdfs: number of bytes written=56
hdfs: number of read operations=16
hdfs: number of large read operations=0
hdfs: number of write operations=8
job counters
launched map tasks=4
other local map tasks=4
total time spent by all maps in occupied slots (ms)=44481
total time spent by all reduces in occupied slots (ms)=0
map-reduce framework
map input records=4
map output records=4
input split bytes=516
spilled records=0
failed shuffles=0
merged map outputs=0
gc time elapsed (ms)=429
cpu time spent (ms)=6650
physical memory (bytes) snapshot=587669504
virtual memory (bytes) snapshot=5219356672
total committed heap usage (bytes)=205848576
file input format counters
bytes read=0
file output format counters
bytes written=56
14/04/09 15:02:33 info mapreduce.importjobbase: transferred 56 bytes in 25.2746 seconds (2.2157 bytes/sec)
14/04/09 15:02:33 info mapreduce.importjobbase: retrieved 4 records.
验证导入的数据：
$ hdfs dfs -ls /user/micmiu/sqoop/demo_log
found 5 items
-rw-r--r-- 3 hadoop supergroup 0 2014-04-09 15:02 /user/micmiu/sqoop/demo_log/_success
-rw-r--r-- 3 hadoop supergroup 28 2014-04-09 15:02 /user/micmiu/sqoop/demo_log/part-m-00000
-rw-r--r-- 3 hadoop supergroup 0 2014-04-09 15:02 /user/micmiu/sqoop/demo_log/part-m-00001
-rw-r--r-- 3 hadoop supergroup 0 2014-04-09 15:02 /user/micmiu/sqoop/demo_log/part-m-00002
-rw-r--r-- 3 hadoop supergroup 28 2014-04-09 15:02 /user/micmiu/sqoop/demo_log/part-m-00003
$ hdfs dfs -cat /user/micmiu/sqoop/demo_log/part-m-0000*
michael,edit
michael,delete
micmiu,create
micmiu,update
[四]、导入数据到hive
比如把表demo_blog 数据导入到hive中，增加参数 --hive-import?：
sqoop import --connect jdbc:mysql://192.168.6.77/test --username root --password micmiu --table demo_blog --warehouse-dir /user/sqoop --hive-import --create-hive-table
执行过程如下：
$ sqoop import --connect jdbc:mysql://192.168.6.77/test --username root --password micmiu --table demo_blog --warehouse-dir /user/sqoop --hive-import --create-hive-table
warning: /usr/lib/hcatalog does not exist! hcatalog jobs will fail.
please set $hcat_home to the root of your hcatalog installation.
14/04/09 10:44:21 warn tool.basesqooptool: setting your password on the command-line is insecure. consider using -p instead.
14/04/09 10:44:21 info tool.basesqooptool: using hive-specific delimiters for output. you can override
14/04/09 10:44:21 info tool.basesqooptool: delimiters with --fields-terminated-by, etc.
14/04/09 10:44:21 warn tool.basesqooptool: it seems that you've specified at least one of following:
14/04/09 10:44:21 warn tool.basesqooptool: --hive-home
14/04/09 10:44:21 warn tool.basesqooptool: --hive-overwrite
14/04/09 10:44:21 warn tool.basesqooptool: --create-hive-table
14/04/09 10:44:21 warn tool.basesqooptool: --hive-table
14/04/09 10:44:21 warn tool.basesqooptool: --hive-partition-key
14/04/09 10:44:21 warn tool.basesqooptool: --hive-partition-value
14/04/09 10:44:21 warn tool.basesqooptool: --map-column-hive
14/04/09 10:44:21 warn tool.basesqooptool: without specifying parameter --hive-import. please note that
14/04/09 10:44:21 warn tool.basesqooptool: those arguments will not be used in this session. either
14/04/09 10:44:21 warn tool.basesqooptool: specify --hive-import to apply them correctly or remove them
14/04/09 10:44:21 warn tool.basesqooptool: from command line to remove this warning.
14/04/09 10:44:21 info tool.basesqooptool: please note that --hive-home, --hive-partition-key,
14/04/09 10:44:21 info tool.basesqooptool: hive-partition-value and --map-column-hive options are
14/04/09 10:44:21 info tool.basesqooptool: are also valid for hcatalog imports and exports
14/04/09 10:44:21 info manager.mysqlmanager: preparing to use a mysql streaming resultset.
14/04/09 10:44:21 info tool.codegentool: beginning code generation
14/04/09 10:44:21 info manager.sqlmanager: executing sql statement: select t.* from `demo_blog` as t limit 1
14/04/09 10:44:21 info manager.sqlmanager: executing sql statement: select t.* from `demo_blog` as t limit 1
14/04/09 10:44:21 info orm.compilationmanager: hadoop_mapred_home is /usr/local/hadoop
note: /tmp/sqoop-hadoop/compile/c071f02ecad006293202fd2c2fad0dce/demo_blog.java uses or overrides a deprecated api.
note: recompile with -xlint:deprecation for details.
14/04/09 10:44:22 info orm.compilationmanager: writing jar file: /tmp/sqoop-hadoop/compile/c071f02ecad006293202fd2c2fad0dce/demo_blog.jar
14/04/09 10:44:22 warn manager.mysqlmanager: it looks like you are importing from mysql.
14/04/09 10:44:22 warn manager.mysqlmanager: this transfer can be faster! use the --direct
14/04/09 10:44:22 warn manager.mysqlmanager: option to exercise a mysql-specific fast path.
14/04/09 10:44:22 info manager.mysqlmanager: setting zero datetime behavior to converttonull (mysql)
14/04/09 10:44:22 info mapreduce.importjobbase: beginning import of demo_blog
slf4j: class path contains multiple slf4j bindings.
slf4j: found binding in [jar:file:/usr/local/hadoop-2.2.0/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/staticloggerbinder.class]
slf4j: found binding in [jar:file:/usr/local/hbase-0.98.0-hadoop2/lib/slf4j-log4j12-1.6.4.jar!/org/slf4j/impl/staticloggerbinder.class]
slf4j: see http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
slf4j: actual binding is of type [org.slf4j.impl.log4jloggerfactory]
14/04/09 10:44:22 info configuration.deprecation: mapred.jar is deprecated. instead, use mapreduce.job.jar
14/04/09 10:44:23 info configuration.deprecation: mapred.map.tasks is deprecated. instead, use mapreduce.job.maps
14/04/09 10:44:23 info client.rmproxy: connecting to resourcemanager at master.hadoop/192.168.6.77:8032
14/04/09 10:44:25 info db.datadrivendbinputformat: boundingvalsquery: select min(`id`), max(`id`) from `demo_blog`
14/04/09 10:44:25 info mapreduce.jobsubmitter: number of splits:3
14/04/09 10:44:25 info configuration.deprecation: mapred.job.classpath.files is deprecated. instead, use mapreduce.job.classpath.files
14/04/09 10:44:25 info configuration.deprecation: user.name is deprecated. instead, use mapreduce.job.user.name
14/04/09 10:44:25 info configuration.deprecation: mapred.cache.files.filesizes is deprecated. instead, use mapreduce.job.cache.files.filesizes
14/04/09 10:44:25 info configuration.deprecation: mapred.cache.files is deprecated. instead, use mapreduce.job.cache.files
14/04/09 10:44:25 info configuration.deprecation: mapred.reduce.tasks is deprecated. instead, use mapreduce.job.reduces
14/04/09 10:44:25 info configuration.deprecation: mapred.output.value.class is deprecated. instead, use mapreduce.job.output.value.class
14/04/09 10:44:25 info configuration.deprecation: mapreduce.map.class is deprecated. instead, use mapreduce.job.map.class
14/04/09 10:44:25 info configuration.deprecation: mapred.job.name is deprecated. instead, use mapreduce.job.name
14/04/09 10:44:25 info configuration.deprecation: mapreduce.inputformat.class is deprecated. instead, use mapreduce.job.inputformat.class
14/04/09 10:44:25 info configuration.deprecation: mapred.output.dir is deprecated. instead, use mapreduce.output.fileoutputformat.outputdir
14/04/09 10:44:25 info configuration.deprecation: mapreduce.outputformat.class is deprecated. instead, use mapreduce.job.outputformat.class
14/04/09 10:44:25 info configuration.deprecation: mapred.cache.files.timestamps is deprecated. instead, use mapreduce.job.cache.files.timestamps
14/04/09 10:44:25 info configuration.deprecation: mapred.output.key.class is deprecated. instead, use mapreduce.job.output.key.class
14/04/09 10:44:25 info configuration.deprecation: mapred.working.dir is deprecated. instead, use mapreduce.job.working.dir
14/04/09 10:44:25 info mapreduce.jobsubmitter: submitting tokens for job: job_1396936838233_0002
14/04/09 10:44:25 info impl.yarnclientimpl: submitted application application_1396936838233_0002 to resourcemanager at master.hadoop/192.168.6.77:8032
14/04/09 10:44:25 info mapreduce.job: the url to track the job: http://master.hadoop:8088/proxy/application_1396936838233_0002/
14/04/09 10:44:25 info mapreduce.job: running job: job_1396936838233_0002
14/04/09 10:44:33 info mapreduce.job: job job_1396936838233_0002 running in uber mode : false
14/04/09 10:44:33 info mapreduce.job: map 0% reduce 0%
14/04/09 10:44:46 info mapreduce.job: map 67% reduce 0%
14/04/09 10:44:48 info mapreduce.job: map 100% reduce 0%
14/04/09 10:44:49 info mapreduce.job: job job_1396936838233_0002 completed successfully
14/04/09 10:44:49 info mapreduce.job: counters: 27
file system counters
file: number of bytes read=0
file: number of bytes written=271860
file: number of read operations=0
file: number of large read operations=0
file: number of write operations=0
hdfs: number of bytes read=295
hdfs: number of bytes written=44
hdfs: number of read operations=12
hdfs: number of large read operations=0
hdfs: number of write operations=6
job counters
launched map tasks=3
other local map tasks=3
total time spent by all maps in occupied slots (ms)=34047
total time spent by all reduces in occupied slots (ms)=0
map-reduce framework
map input records=3
map output records=3
input split bytes=295
spilled records=0
failed shuffles=0
merged map outputs=0
gc time elapsed (ms)=505
cpu time spent (ms)=5350
physical memory (bytes) snapshot=427388928
virtual memory (bytes) snapshot=3881439232
total committed heap usage (bytes)=171638784
file input format counters
bytes read=0
file output format counters
bytes written=44
14/04/09 10:44:49 info mapreduce.importjobbase: transferred 44 bytes in 26.0401 seconds (1.6897 bytes/sec)
14/04/09 10:44:49 info mapreduce.importjobbase: retrieved 3 records.
14/04/09 10:44:49 info manager.sqlmanager: executing sql statement: select t.* from `demo_blog` as t limit 1
14/04/09 10:44:49 info hive.hiveimport: loading uploaded data into hive
14/04/09 10:44:52 info hive.hiveimport: 14/04/09 10:44:52 info configuration.deprecation: mapred.reduce.tasks is deprecated. instead, use mapreduce.job.reduces
14/04/09 10:44:52 info hive.hiveimport: 14/04/09 10:44:52 info configuration.deprecation: mapred.min.split.size is deprecated. instead, use mapreduce.input.fileinputformat.split.minsize
14/04/09 10:44:52 info hive.hiveimport: 14/04/09 10:44:52 info configuration.deprecation: mapred.reduce.tasks.speculative.execution is deprecated. instead, use mapreduce.reduce.speculative
14/04/09 10:44:52 info hive.hiveimport: 14/04/09 10:44:52 info configuration.deprecation: mapred.min.split.size.per.node is deprecated. instead, use mapreduce.input.fileinputformat.split.minsize.per.node
14/04/09 10:44:52 info hive.hiveimport: 14/04/09 10:44:52 info configuration.deprecation: mapred.input.dir.recursive is deprecated. instead, use mapreduce.input.fileinputformat.input.dir.recursive
14/04/09 10:44:52 info hive.hiveimport: 14/04/09 10:44:52 info configuration.deprecation: mapred.min.split.size.per.rack is deprecated. instead, use mapreduce.input.fileinputformat.split.minsize.per.rack
14/04/09 10:44:52 info hive.hiveimport: 14/04/09 10:44:52 info configuration.deprecation: mapred.max.split.size is deprecated. instead, use mapreduce.input.fileinputformat.split.maxsize
14/04/09 10:44:52 info hive.hiveimport: 14/04/09 10:44:52 info configuration.deprecation: mapred.committer.job.setup.cleanup.needed is deprecated. instead, use mapreduce.job.committer.setup.cleanup.needed
14/04/09 10:44:53 info hive.hiveimport: 14/04/09 10:44:53 warn conf.hiveconf: deprecated: hive.metastore.ds.retry.* no longer has any effect. use hive.hmshandler.retry.* instead
14/04/09 10:44:53 info hive.hiveimport:
14/04/09 10:44:53 info hive.hiveimport: logging initialized using configuration in file:/usr/local/hive-0.13.0-bin/conf/hive-log4j.properties
14/04/09 10:44:53 info hive.hiveimport: slf4j: class path contains multiple slf4j bindings.
14/04/09 10:44:53 info hive.hiveimport: slf4j: found binding in [jar:file:/usr/local/hadoop-2.2.0/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/staticloggerbinder.class]
14/04/09 10:44:53 info hive.hiveimport: slf4j: found binding in [jar:file:/usr/local/hbase-0.98.0-hadoop2/lib/slf4j-log4j12-1.6.4.jar!/org/slf4j/impl/staticloggerbinder.class]
14/04/09 10:44:53 info hive.hiveimport: slf4j: see http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
14/04/09 10:44:53 info hive.hiveimport: slf4j: actual binding is of type [org.slf4j.impl.log4jloggerfactory]
14/04/09 10:44:57 info hive.hiveimport: ok
14/04/09 10:44:57 info hive.hiveimport: time taken: 0.773 seconds
14/04/09 10:44:57 info hive.hiveimport: loading data to table default.demo_blog
14/04/09 10:44:57 info hive.hiveimport: table default.demo_blog stats: [numfiles=4, numrows=0, totalsize=44, rawdatasize=0]
14/04/09 10:44:57 info hive.hiveimport: ok
14/04/09 10:44:57 info hive.hiveimport: time taken: 0.25 seconds
14/04/09 10:44:57 info hive.hiveimport: hive import complete.
14/04/09 10:44:57 info hive.hiveimport: export directory is empty, removing it
hive cli中验证导入的数据：
hive> show tables;
ok
demo_blog
hbase_table_1
hbase_table_2
hbase_table_3
micmiu_blog
micmiu_hx_master
pokes
xflow_dstip
time taken: 0.073 seconds, fetched: 8 row(s)
hive> select * from demo_blog;
ok
1 micmiu.com
2 ctosun.com
3 baby.micmiu.com
time taken: 0.506 seconds, fetched: 3 row(s)
[五]、导入数据到hbase
演示把表 demo_blog 数据导入到hbase ，指定hbase中表名为 demo_sqoop2hbase 的命令：
sqoop import --connect jdbc:mysql://192.168.6.77/test --username root --password micmiu --table demo_blog --hbase-table demo_sqoop2hbase --hbase-create-table --hbase-row-key id --column-family url
执行过程：
$ sqoop import --connect jdbc:mysql://192.168.6.77/test --username root --password micmiu --table demo_blog --hbase-table demo_sqoop2hbase --hbase-create-table --hbase-row-key id --column-family url
warning: /usr/lib/hcatalog does not exist! hcatalog jobs will fail.
please set $hcat_home to the root of your hcatalog installation.
14/04/09 16:23:38 warn tool.basesqooptool: setting your password on the command-line is insecure. consider using -p instead.
14/04/09 16:23:38 info manager.mysqlmanager: preparing to use a mysql streaming resultset.
14/04/09 16:23:38 info tool.codegentool: beginning code generation
14/04/09 16:23:39 info manager.sqlmanager: executing sql statement: select t.* from `demo_blog` as t limit 1
14/04/09 16:23:39 info manager.sqlmanager: executing sql statement: select t.* from `demo_blog` as t limit 1
14/04/09 16:23:39 info orm.compilationmanager: hadoop_mapred_home is /usr/local/hadoop
note: /tmp/sqoop-hadoop/compile/85408c854ee8fba75bbb2458e5e25093/demo_blog.java uses or overrides a deprecated api.
note: recompile with -xlint:deprecation for details.
14/04/09 16:23:40 info orm.compilationmanager: writing jar file: /tmp/sqoop-hadoop/compile/85408c854ee8fba75bbb2458e5e25093/demo_blog.jar
14/04/09 16:23:40 warn manager.mysqlmanager: it looks like you are importing from mysql.
14/04/09 16:23:40 warn manager.mysqlmanager: this transfer can be faster! use the --direct
14/04/09 16:23:40 warn manager.mysqlmanager: option to exercise a mysql-specific fast path.
14/04/09 16:23:40 info manager.mysqlmanager: setting zero datetime behavior to converttonull (mysql)
14/04/09 16:23:40 info mapreduce.importjobbase: beginning import of demo_blog
slf4j: class path contains multiple slf4j bindings.
slf4j: found binding in [jar:file:/usr/local/hadoop-2.2.0/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/staticloggerbinder.class]
slf4j: found binding in [jar:file:/usr/local/hbase-0.98.0-hadoop2/lib/slf4j-log4j12-1.6.4.jar!/org/slf4j/impl/staticloggerbinder.class]
slf4j: see http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
slf4j: actual binding is of type [org.slf4j.impl.log4jloggerfactory]
14/04/09 16:23:40 info configuration.deprecation: mapred.jar is deprecated. instead, use mapreduce.job.jar
14/04/09 16:23:40 info configuration.deprecation: mapred.map.tasks is deprecated. instead, use mapreduce.job.maps
14/04/09 16:23:41 info zookeeper.zookeeper: client environment:zookeeper.version=3.4.5-1392090, built on 09/30/2012 17:52 gmt
14/04/09 16:23:41 info zookeeper.zookeeper: client environment:host.name=master.hadoop
14/04/09 16:23:41 info zookeeper.zookeeper: client environment:java.version=1.6.0_20
14/04/09 16:23:41 info zookeeper.zookeeper: client environment:java.vendor=sun microsystems inc.
14/04/09 16:23:41 info zookeeper.zookeeper: client environment:java.home=/java/jdk1.6.0_20/jre
14/04/09 16:23:41 info zookeeper.zookeeper: client environment:java.class.path=/usr/local/hadoop/etc/hadoop: .......
14/04/09 16:23:41 info zookeeper.zookeeper: client environment:java.library.path=/usr/local/hadoop/lib/native
14/04/09 16:23:41 info zookeeper.zookeeper: client environment:java.io.tmpdir=/tmp
14/04/09 16:23:41 info zookeeper.zookeeper: client environment:java.compiler=
14/04/09 16:23:41 info zookeeper.zookeeper: client environment:os.name=linux
14/04/09 16:23:41 info zookeeper.zookeeper: client environment:os.arch=amd64
14/04/09 16:23:41 info zookeeper.zookeeper: client environment:os.version=2.6.32-71.el6.x86_64
14/04/09 16:23:41 info zookeeper.zookeeper: client environment:user.name=hadoop
14/04/09 16:23:41 info zookeeper.zookeeper: client environment:user.home=/home/hadoop
14/04/09 16:23:41 info zookeeper.zookeeper: client environment:user.dir=/home/hadoop
14/04/09 16:23:41 info zookeeper.zookeeper: initiating client connection, connectstring=slave6.hadoop:2181,slave5.hadoop:2181,slave7.hadoop:2181 sessiontimeout=90000 watcher=hconnection-0x57c8b24d, quorum=slave6.hadoop:2181,slave5.hadoop:2181,slave7.hadoop:2181, baseznode=/hbase
14/04/09 16:23:41 info zookeeper.clientcnxn: opening socket connection to server slave5.hadoop/192.168.8.205:2181. will not attempt to authenticate using sasl (unable to locate a login configuration)
14/04/09 16:23:41 info zookeeper.recoverablezookeeper: process identifier=hconnection-0x57c8b24d connecting to zookeeper ensemble=slave6.hadoop:2181,slave5.hadoop:2181,slave7.hadoop:2181
14/04/09 16:23:41 info zookeeper.clientcnxn: socket connection established to slave5.hadoop/192.168.8.205:2181, initiating session
14/04/09 16:23:41 info zookeeper.clientcnxn: session establishment complete on server slave5.hadoop/192.168.8.205:2181, sessionid = 0x453fecb6c50009, negotiated timeout = 90000
14/04/09 16:23:41 info configuration.deprecation: hadoop.native.lib is deprecated. instead, use io.native.lib.available
14/04/09 16:23:41 info zookeeper.zookeeper: initiating client connection, connectstring=slave6.hadoop:2181,slave5.hadoop:2181,slave7.hadoop:2181 sessiontimeout=90000 watcher=catalogtracker-on-hconnection-0x57c8b24d, quorum=slave6.hadoop:2181,slave5.hadoop:2181,slave7.hadoop:2181, baseznode=/hbase
14/04/09 16:23:41 info zookeeper.clientcnxn: opening socket connection to server slave7.hadoop/192.168.8.207:2181. will not attempt to authenticate using sasl (unable to locate a login configuration)
14/04/09 16:23:41 info zookeeper.recoverablezookeeper: process identifier=catalogtracker-on-hconnection-0x57c8b24d connecting to zookeeper ensemble=slave6.hadoop:2181,slave5.hadoop:2181,slave7.hadoop:2181
14/04/09 16:23:41 info zookeeper.clientcnxn: socket connection established to slave7.hadoop/192.168.8.207:2181, initiating session
14/04/09 16:23:41 info zookeeper.clientcnxn: session establishment complete on server slave7.hadoop/192.168.8.207:2181, sessionid = 0x2453fecb6f50008, negotiated timeout = 90000
14/04/09 16:23:41 info zookeeper.zookeeper: session: 0x2453fecb6f50008 closed
14/04/09 16:23:41 info zookeeper.clientcnxn: eventthread shut down
14/04/09 16:23:41 info mapreduce.hbaseimportjob: creating missing hbase table demo_sqoop2hbase
14/04/09 16:23:42 info zookeeper.zookeeper: initiating client connection, connectstring=slave6.hadoop:2181,slave5.hadoop:2181,slave7.hadoop:2181 sessiontimeout=90000 watcher=catalogtracker-on-hconnection-0x57c8b24d, quorum=slave6.hadoop:2181,slave5.hadoop:2181,slave7.hadoop:2181, baseznode=/hbase
14/04/09 16:23:42 info zookeeper.recoverablezookeeper: process identifier=catalogtracker-on-hconnection-0x57c8b24d connecting to zookeeper ensemble=slave6.hadoop:2181,slave5.hadoop:2181,slave7.hadoop:2181
14/04/09 16:23:42 info zookeeper.clientcnxn: opening socket connection to server slave7.hadoop/192.168.8.207:2181. will not attempt to authenticate using sasl (unable to locate a login configuration)
14/04/09 16:23:42 info zookeeper.clientcnxn: socket connection established to slave7.hadoop/192.168.8.207:2181, initiating session
14/04/09 16:23:42 info zookeeper.clientcnxn: session establishment complete on server slave7.hadoop/192.168.8.207:2181, sessionid = 0x2453fecb6f50009, negotiated timeout = 90000
14/04/09 16:23:42 info zookeeper.zookeeper: session: 0x2453fecb6f50009 closed
14/04/09 16:23:42 info zookeeper.clientcnxn: eventthread shut down
14/04/09 16:23:42 info client.rmproxy: connecting to resourcemanager at master.hadoop/192.168.6.77:8032
14/04/09 16:23:47 info db.datadrivendbinputformat: boundingvalsquery: select min(`id`), max(`id`) from `demo_blog`
14/04/09 16:23:47 info mapreduce.jobsubmitter: number of splits:3
14/04/09 16:23:47 info configuration.deprecation: mapred.job.classpath.files is deprecated. instead, use mapreduce.job.classpath.files
14/04/09 16:23:47 info configuration.deprecation: user.name is deprecated. instead, use mapreduce.job.user.name
14/04/09 16:23:47 info configuration.deprecation: mapred.cache.files.filesizes is deprecated. instead, use mapreduce.job.cache.files.filesizes
14/04/09 16:23:47 info configuration.deprecation: mapred.cache.files is deprecated. instead, use mapreduce.job.cache.files
14/04/09 16:23:47 info configuration.deprecation: mapred.reduce.tasks is deprecated. instead, use mapreduce.job.reduces
14/04/09 16:23:47 info configuration.deprecation: mapred.output.value.class is deprecated. instead, use mapreduce.job.output.value.class
14/04/09 16:23:47 info configuration.deprecation: mapreduce.map.class is deprecated. instead, use mapreduce.job.map.class
14/04/09 16:23:47 info configuration.deprecation: mapred.job.name is deprecated. instead, use mapreduce.job.name
14/04/09 16:23:47 info configuration.deprecation: mapreduce.inputformat.class is deprecated. instead, use mapreduce.job.inputformat.class
14/04/09 16:23:47 info configuration.deprecation: mapreduce.outputformat.class is deprecated. instead, use mapreduce.job.outputformat.class
14/04/09 16:23:47 info configuration.deprecation: mapred.cache.files.timestamps is deprecated. instead, use mapreduce.job.cache.files.timestamps
14/04/09 16:23:47 info configuration.deprecation: mapred.output.key.class is deprecated. instead, use mapreduce.job.output.key.class
14/04/09 16:23:47 info configuration.deprecation: mapred.working.dir is deprecated. instead, use mapreduce.job.working.dir
14/04/09 16:23:47 info mapreduce.jobsubmitter: submitting tokens for job: job_1396936838233_0005
14/04/09 16:23:47 info impl.yarnclientimpl: submitted application application_1396936838233_0005 to resourcemanager at master.hadoop/192.168.6.77:8032
14/04/09 16:23:47 info mapreduce.job: the url to track the job: http://master.hadoop:8088/proxy/application_1396936838233_0005/
14/04/09 16:23:47 info mapreduce.job: running job: job_1396936838233_0005
14/04/09 16:23:55 info mapreduce.job: job job_1396936838233_0005 running in uber mode : false
14/04/09 16:23:55 info mapreduce.job: map 0% reduce 0%
14/04/09 16:24:05 info mapreduce.job: map 33% reduce 0%
14/04/09 16:24:12 info mapreduce.job: map 100% reduce 0%
14/04/09 16:24:12 info mapreduce.job: job job_1396936838233_0005 completed successfully
14/04/09 16:24:12 info mapreduce.job: counters: 27
file system counters
file: number of bytes read=0
file: number of bytes written=354636
file: number of read operations=0
file: number of large read operations=0
file: number of write operations=0
hdfs: number of bytes read=295
hdfs: number of bytes written=0
hdfs: number of read operations=3
hdfs: number of large read operations=0
hdfs: number of write operations=0
job counters
launched map tasks=3
other local map tasks=3
total time spent by all maps in occupied slots (ms)=35297
total time spent by all reduces in occupied slots (ms)=0
map-reduce framework
map input records=3
map output records=3
input split bytes=295
spilled records=0
failed shuffles=0
merged map outputs=0
gc time elapsed (ms)=381
cpu time spent (ms)=11050
physical memory (bytes) snapshot=543367168
virtual memory (bytes) snapshot=3918925824
total committed heap usage (bytes)=156958720
file input format counters
bytes read=0
file output format counters
bytes written=0
14/04/09 16:24:12 info mapreduce.importjobbase: transferred 0 bytes in 29.7126 seconds (0 bytes/sec)
14/04/09 16:24:12 info mapreduce.importjobbase: retrieved 3 records.
hbase shell中验证导入的数据：
hbase(main):009:0> list
table
demo_sqoop2hbase
table_02
table_03
test_table
xyz
5 row(s) in 0.0310 seconds
=> ["demo_sqoop2hbase", "table_02", "table_03", "test_table", "xyz"]
hbase(main):010:0> scan "demo_sqoop2hbase"
row column+cell
1 column=url:blog, timestamp=1397031850700, value=micmiu.com
2 column=url:blog, timestamp=1397031844106, value=ctosun.com
3 column=url:blog, timestamp=1397031849888, value=baby.micmiu.com
3 row(s) in 0.0730 seconds
hbase(main):011:0> describe "demo_sqoop2hbase"
description enabled
'demo_sqoop2hbase', {name => 'url', data_block_encoding => 'none', bl true
oomfilter => 'row', replication_scope => '0', versions => '1', compre
ssion => 'none', min_versions => '0', ttl => '2147483647', keep_delet
ed_cells => 'false', blocksize => '65536', in_memory => 'false', bloc
kcache => 'true'}
1 row(s) in 0.0580 seconds
hbase(main):012:0>
验证导入成功。
本文到此已经把mysql中的数据迁移到 hdfs、hive、hbase的三种基本情况演示结束。
参考：
- http://sqoop.apache.org/docs/1.4.4/sqoopuserguide.html
—————– ?eof?@michael sun?—————–
【说明】：
本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:)！