下面我们结合 HDFS,介绍 Sqoop 从关系型数据库的导入和导出
一、MySQL里的数据通过Sqoop importHDFS
它的功能是将数据从关系型数据库导入
HDFS 中,其流程图如下所示。
我们来分析一下 Sqoop 数据导入流程,首先用户输入一个Sqoop
import命令,Sqoop 会从关系型数据库中获取元数据信息,
比如要操作数据库表的 schema是什么样子,这个表有哪些字段,这些字段都是什么数据类型等。
它获取这些信息之后,会将输入命令转化为基于
Map的 MapReduce作业。
这样 MapReduce作业中有很多 Map 任务,每个 Map 任务从数据库中读取一片数据,这样多个 Map 任务实现并发的拷贝,把整个数据快速的拷贝到 HDFS 上。
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
场景:
sqoop import --connect jdbc:postgresql://...../..... --username .... --query "select * from retail_tb_order_qiulp_test where status = 'TRADE_FINISHED'
or status = 'TRADE_CLOSED' or status = 'TRADE_CLOSED_BY_TAOBAO' and \$CONDITIONS" --hive-import -m 6 --hive-table custom_analyse_db.retail_tb_order_main --hive-overwrite --hive-partition-key part --hive-partition-value qiulp --target-dir /user/qiulp/test1
--split-by tid
注意红色字体部分, retail_tb_order_qiulp_test表原有1000条数据,导入hive后成了6000条,是map的倍数关系。
如下图,一条数据在hive中有6条相同的数据:
理解
1.split-by 根据不同的参数类型有不同的切分方法,如int型,Sqoop会取最大和最小split-by字段值,然后根据传入的num-mappers来 确定划分几个区域。比如select max(split_by),min(split-by) from得到的max(split-by)和min(split-by)分别为1000和1,而num-mappers(-m)为2的话,则会分成两个区域
(1,500)和(501-1000),同时也会分成2个sql给2个map去进行导入操作,分别为select XXX from table where split-by>=1 and split-by<500和select XXX from table where split-by>=501 and split-by<=1000.最后每个map各自获取各自SQL中的数据进行导入工作。
2.当split-by不是int型时出现如上场景中的问题。目前想到的解决办法是:将-m 设置称1,split-by不设置,即只有一个map运行,缺点是不能并行map录入数据。(注意,当-m 设置的值大于1时,split-by必须设置字段)
3.split-by即便是int型,若不是连续有规律递增的话,各个map分配的数据是不均衡的,可能会有些map很忙,有些map几乎没有数据处理的情况
-----------------------------------------------------------------------------------------------------------------------
Sqoop Import HDFS(带着官网)
具体,自己去尝试做吧!
在这之前,先启动hadoop集群,sbin/start-all.sh。这里不多赘述。
同时,开启MySQL数据库。这里,不多赘述。
同时,因为后续的sqoop运行啊,会产生一些日志等,我这里先新建一个目录,用来专门存放它。在哪个目录下运行后续的sqoop操作,就在哪个目录下新建就好。(因为,已经配置了环境变量,在任何路径下都是可以运行的)
[hadoop@djt002 sqoop]$ pwd /usr/local/sqoop [hadoop@djt002 sqoop]$ ll total 4 drwxr-xr-x. 9 hadoop hadoop 4096 Apr 27 2015 sqoop-1.4.6 [hadoop@djt002 sqoop]$ mkdir sqoopRunCreate [hadoop@djt002 sqoop]$ ll total 8 drwxr-xr-x. 9 hadoop hadoop 4096 Apr 27 2015 sqoop-1.4.6 drwxrwxr-x. 2 hadoop hadoop 4096 Mar 17 23:33 sqoopRunCreate [hadoop@djt002 sqoop]$ cd sqoopRunCreate/ [hadoop@djt002 sqoopRunCreate]$ pwd /usr/local/sqoop/sqoopRunCreate [hadoop@djt002 sqoopRunCreate]$
比如,以后我就在这个目录下运行操作sqoop,/usr/local/sqoop/sqoopRunCreate。
Sqoop Import 应用场景——密码访问
(1)明码访问
[hadoop@djt002 sqoopRunCreate]$ sqoop list-databases \ > --connect jdbc:mysql://192.168.80.200/ \ > --username hive \ > --password hive
(2)交互式密码访问
[hadoop@djt002 sqoopRunCreate]$ sqoop list-databases \ > --connect jdbc:mysql://192.168.80.200/ \ > --username hive \ > -P Enter password: (输入hive)
(3)文件授权密码访问
因为,官网上是这么给的,在家目录,且需赋予400权限。所以
[hadoop@djt002 ~]$ pwd /home/hadoop [hadoop@djt002 ~]$ echo -n "hive" > .password [hadoop@djt002 ~]$ ls -a . .bash_history .cache djt flume .gnote .gvfs .local .nautilus .pulse Videos .xsession-errors .. .bash_logout .config Documents .gconf .gnupg .hivehistory .mozilla .password .pulse-cookie .vim .xsession-errors.old .abrt .bash_profile .dbus Downloads .gconfd .gstreamer-0.10 .ICEauthority Music Pictures .ssh .viminfo anagram.jar .bashrc Desktop .esd_auth .gnome2 .gtk-bookmarks .imsettings.log .mysql_history Public Templates .Xauthority [hadoop@djt002 ~]$ more .passwordhive [hadoop@djt002 ~]$
[hadoop@djt002 ~]$ chmod 400 .password
[hadoop@djt002 sqoopRunCreate]$ sqoop list-databases \ > --connect jdbc:mysql://192.168.80.200/ \ > --username hive \ > --password-file /home/hadoop/.password java.io.IOException: The provided password file /home/hadoop/.password does not exist!
[hadoop@djt002 local]$ $HADOOP_HOME/bin/hadoop dfs -put /home/hadoop/.password /user/hadoop
[hadoop@djt002 local]$ $HADOOP_HOME/bin/hadoop dfs -chmod 400 /user/hadoop/.password
[hadoop@djt002 ~]$ rm .password rm: remove write-protected regular file `.password' y
[hadoop@djt002 sqoopRunCreate]$ sqoop list-databases \ > --connect jdbc:mysql://192.168.80.200/ \ > --username hive \ > --password-file /user/hadoop/.password
Sqoop Import 应用场景——导入全表
(1)不指定目录 (则默认是在/user/hadoop/下)
我这里啊,给大家尝试另一个软件。(为什么,要这样带大家使用,是为了你们的多适应和多自学能力)(别嫌麻烦!)
SQLyog之MySQL客户端的下载、安装和使用
这里,我们选择在hive这个数据库里,创建新的表,命名为
如果,面对 SQLyog不能正常显示中文数据的情况:在SQLyog下输入SET character_set_results = gb2312(或 gbk),执行,重新启动SQLyog,显示应该也可以看到你所插入的中文数据了。
SQLyog软件里无法插入中文(即由默认的latin1改成UTF8编码格式)
注意,我的数据表是djt-user。我这里改名啦!
[hadoop@djt002 sqoopRunCreate]$ sqoop import \ > --connect jdbc:mysql://192.168.80.200/hive \ > --username hive \ > --password-file /user/hadoop/.password \ > --table djt-user
SET character_set_database=utf8; SET character_set_server=utf8; SHOW VARIABLES LIKE 'character%';
[hadoop@djt002 ~]$ $HADOOP_HOME/bin/hadoop fs -rmr /user/hadoop/djt-user
[hadoop@djt002 sqoopRunCreate]$ sqoop import --connect jdbc:mysql://192.168.80.200/hive --username hive --password-file /user/hadoop/.password --table djt-user
[hadoop@djt002 sqoopRunCreate]$ sqoop import --connect jdbc:mysql://192.168.80.200/hive --username hive --password-file /user/hadoop/.password --table djt-user
Warning: /usr/local/sqoop/sqoop-1.4.6/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /usr/local/sqoop/sqoop-1.4.6/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
Warning: /usr/local/sqoop/sqoop-1.4.6/../zookeeper does not exist! Accumulo imports will fail.
Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation.
17/03/18 04:17:10 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/local/hadoop/hadoop-2.6.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/hbase-1.2.3/lib/slf4j-log4j12-1.7.5.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]
17/03/18 04:17:14 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
17/03/18 04:17:14 INFO tool.CodeGenTool: Beginning code generation
17/03/18 04:17:15 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `djt-user` AS t LIMIT 1
17/03/18 04:17:15 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `djt-user` AS t LIMIT 1
17/03/18 04:17:15 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr/local/hadoop/hadoop-2.6.0
Note: /tmp/sqoop-hadoop/compile/38104c9fe28c7f43fdb42c26826dbf91/djt_user.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
17/03/18 04:17:21 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoop/compile/38104c9fe28c7f43fdb42c26826dbf91/djt-user.jar
17/03/18 04:17:21 WARN manager.MySQLManager: It looks like you are importing from mysql.
17/03/18 04:17:21 WARN manager.MySQLManager: This transfer can be faster! Use the --direct
17/03/18 04:17:21 WARN manager.MySQLManager: option to exercise a MySQL-specific fast path.
17/03/18 04:17:21 INFO manager.MySQLManager: Setting zero DATETIME behavior to convertToNull (mysql)
17/03/18 04:17:21 INFO mapreduce.ImportJobBase: Beginning import of djt-user
17/03/18 04:17:21 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar
17/03/18 04:17:21 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps
17/03/18 04:17:22 INFO client.RMProxy: Connecting to ResourceManager at /0.0.0.0:8032
17/03/18 04:17:30 INFO db.DBInputFormat: Using read commited transaction isolation
17/03/18 04:17:30 INFO db.DataDrivenDBInputFormat: BoundingValsQuery: SELECT MIN(`id`), MAX(`id`) FROM `djt-user`
17/03/18 04:17:31 INFO mapreduce.JobSubmitter: number of splits:3
17/03/18 04:17:32 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1489767532299_0002
17/03/18 04:17:33 INFO impl.YarnClientImpl: Submitted application application_1489767532299_0002
17/03/18 04:17:33 INFO mapreduce.Job: The url to track the job: http://djt002:8088/proxy/application_1489767532299_0002/
17/03/18 04:17:33 INFO mapreduce.Job: Running job: job_1489767532299_0002
17/03/18 04:18:03 INFO mapreduce.Job: Job job_1489767532299_0002 running in uber mode : false
17/03/18 04:18:03 INFO mapreduce.Job: map 0% reduce 0%
17/03/18 04:19:09 INFO mapreduce.Job: map 67% reduce 0%
17/03/18 04:19:12 INFO mapreduce.Job: map 100% reduce 0%
17/03/18 04:19:13 INFO mapreduce.Job: Job job_1489767532299_0002 completed successfully
17/03/18 04:19:13 INFO mapreduce.Job: Counters: 30
File System Counters
FILE: Number of bytes read=0
FILE: Number of bytes written=370638
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=105
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)=174022
Total time spent by all reduces in occupied slots (ms)=0
Total time spent by all map tasks (ms)=174022
Total vcore-seconds taken by all map tasks=174022
Total megabyte-seconds taken by all map tasks=178198528
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)=5172
CPU time spent (ms)=9510
Physical memory (bytes) snapshot=362741760
Virtual memory (bytes) snapshot=2535641088
Total committed heap usage (bytes)=181862400
File Input Format Counters
Bytes Read=0
File Output Format Counters
Bytes Written=105
17/03/18 04:19:13 INFO mapreduce.ImportJobBase: Transferred 105 bytes in 111.9157 seconds (0.9382 bytes/sec)
17/03/18 04:19:13 INFO mapreduce.ImportJobBase: Retrieved 3 records.
[hadoop@djt002 sqoopRunCreate]$
[hadoop@djt002 ~]$ $HADOOP_HOME/bin/hadoop fs -cat /user/hadoop/djt-user/part-m-* 1,王菲,female,36,歌手 2,谢霆锋,male,30,歌手 3,周杰伦,male,33,导演 [hadoop@djt002 ~]$
总结
不指定目录 sqoop import \ --connect 'jdbc:mysql://192.168.128.200/hive \ --username hive \ --password-file /user/hadoop/.password \ --table djt_user
不指定目录 (推荐这种) sqoop import \ --connect 'jdbc:mysql://192.168.128.200/hiveuseUnicode=true&characterEncoding=utf-8' \ --username hive \ --password-file /user/hadoop/.password \ --table djt_user
即,djt_user是在MySQL里,通过Sqoop工具,导入到HDFS里,这里是在/user/hadoop/djt_user。
(2)指定目录
任意可以指定的。
[hadoop@djt002 ~]$ $HADOOP_HOME/bin/hadoop fs -cat /sqoop/test/djt_user/part-m-* 1,王菲,female,36,歌手 2,谢霆锋,male,30,歌手 3,周杰伦,male,33,导演 [hadoop@djt002 ~]$
这里,为统一标准和规范化,用数据表djt_user。
(3)目录已存在
[hadoop@djt002 sqoopRunCreate]$ sqoop import \ > --connect jdbc:mysql://192.168.80.200/hive \ > --username hive \ > --password-file /user/hadoop/.password \ > --table djt_user \ > --target-dir /sqoop/test/djt_user \ > --delete-target-dir
Warning: /usr/local/sqoop/sqoop-1.4.6/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /usr/local/sqoop/sqoop-1.4.6/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
Warning: /usr/local/sqoop/sqoop-1.4.6/../zookeeper does not exist! Accumulo imports will fail.
Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation.
17/03/18 04:43:40 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/local/hadoop/hadoop-2.6.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/hbase-1.2.3/lib/slf4j-log4j12-1.7.5.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]
17/03/18 04:43:45 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
17/03/18 04:43:45 INFO tool.CodeGenTool: Beginning code generation
17/03/18 04:43:46 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `djt_user` AS t LIMIT 1
17/03/18 04:43:46 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `djt_user` AS t LIMIT 1
17/03/18 04:43:46 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr/local/hadoop/hadoop-2.6.0
Note: /tmp/sqoop-hadoop/compile/1fae17dd362476d95608e216756efa34/djt_user.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
17/03/18 04:43:52 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-hadoop/compile/1fae17dd362476d95608e216756efa34/djt_user.jar
17/03/18 04:43:52 INFO tool.ImportTool: Destination directory /sqoop/test/djt_user deleted.
17/03/18 04:43:52 WARN manager.MySQLManager: It looks like you are importing from mysql.
17/03/18 04:43:52 WARN manager.MySQLManager: This transfer can be faster! Use the --direct
17/03/18 04:43:52 WARN manager.MySQLManager: option to exercise a MySQL-specific fast path.
17/03/18 04:43:52 INFO manager.MySQLManager: Setting zero DATETIME behavior to convertToNull (mysql)
17/03/18 04:43:52 INFO mapreduce.ImportJobBase: Beginning import of djt_user
17/03/18 04:43:52 INFO Configuration.deprecation: mapred.jar is deprecated. Instead, use mapreduce.job.jar
17/03/18 04:43:52 INFO Configuration.deprecation: mapred.map.tasks is deprecated. Instead, use mapreduce.job.maps
17/03/18 04:43:53 INFO client.RMProxy: Connecting to ResourceManager at /0.0.0.0:8032
17/03/18 04:44:02 INFO db.DBInputFormat: Using read commited transaction isolation
17/03/18 04:44:02 INFO db.DataDrivenDBInputFormat: BoundingValsQuery: SELECT MIN(`id`), MAX(`id`) FROM `djt_user`
17/03/18 04:44:02 INFO mapreduce.JobSubmitter: number of splits:3
17/03/18 04:44:03 INFO mapreduce.JobSubmitter: Submitting tokens for job: job_1489767532299_0005
17/03/18 04:44:03 INFO impl.YarnClientImpl: Submitted application application_1489767532299_0005
17/03/18 04:44:03 INFO mapreduce.Job: The url to track the job: http://djt002:8088/proxy/application_1489767532299_0005/
17/03/18 04:44:03 INFO mapreduce.Job: Running job: job_1489767532299_0005
17/03/18 04:44:23 INFO mapreduce.Job: Job job_1489767532299_0005 running in uber mode : false
17/03/18 04:44:23 INFO mapreduce.Job: map 0% reduce 0%
17/03/18 04:45:21 INFO mapreduce.Job: map 67% reduce 0%
17/03/18 04:45:23 INFO mapreduce.Job: map 100% reduce 0%
17/03/18 04:45:23 INFO mapreduce.Job: Job job_1489767532299_0005 completed successfully
17/03/18 04:45:24 INFO mapreduce.Job: Counters: 30
File System Counters
FILE: Number of bytes read=0
FILE: Number of bytes written=370635
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=80
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)=163316
Total time spent by all reduces in occupied slots (ms)=0
Total time spent by all map tasks (ms)=163316
Total vcore-seconds taken by all map tasks=163316
Total megabyte-seconds taken by all map tasks=167235584
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)=3240
CPU time spent (ms)=8480
Physical memory (bytes) snapshot=356696064
Virtual memory (bytes) snapshot=2535596032
Total committed heap usage (bytes)=181862400
File Input Format Counters
Bytes Read=0
File Output Format Counters
Bytes Written=80
17/03/18 04:45:24 INFO mapreduce.ImportJobBase: Transferred 80 bytes in 91.6189 seconds (0.8732 bytes/sec)
17/03/18 04:45:24 INFO mapreduce.ImportJobBase: Retrieved 3 records.
[hadoop@djt002 sqoopRunCreate]$
Sqoop Import 应用场景——控制并行度
(1)控制并行度
默认是4个,当然我这里数据量小,指定1个就行了。
[hadoop@djt002 ~]$ $HADOOP_HOME/bin/hadoop fs -rm /sqoop/test/djt_user/part-m-*
[hadoop@djt002 sqoopRunCreate]$ sqoop import \ > --connect 'jdbc:mysql://192.168.80.200/hiveuseUnicode=true&characterEncoding=utf-8' \ > --username hive \ > --password-file /user/hadoop/.password \ > --table djt_user \ > --target-dir /sqoop/test/djt_user \ > --delete-target-dir \ > -m 1
在这里,可能会遇到这个问题。
Sqoop异常解决ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: No columns to generate for ClassWriter问题
[hadoop@djt002 ~]$ $HADOOP_HOME/bin/hadoop fs -cat /sqoop/test/djt_user/part-m-*
Sqoop Import 应用场景——控制字段分隔符
(1)控制字段分隔符
注意,默认的控制分段分隔符是逗号,我们这里自定义。
[hadoop@djt002 sqoopRunCreate]$ sqoop import \ > --connect 'jdbc:mysql://192.168.80.200/hiveuseUnicode=true&characterEncoding=utf-8' \ > --username hive \ > --password-file /user/hadoop/.password \ > --table djt_user \ > --target-dir /sqoop/test/djt_user \ > --delete-target-dir \ > -m 1 \ > --fields-terminated-by "@"
这里,djt_user是在MySQL里,通过Sqoop工具,导入到HDFS里,是在/sqoop/test/djt_user。
[hadoop@djt002 ~]$ $HADOOP_HOME/bin/hadoop fs -cat /sqoop/test/djt_user/part-m-* 1@王菲@female@36@歌手 2@谢霆锋@male@30@歌手 3@周杰伦@male@33@导演 [hadoop@djt002 ~]$
(2)手动增量导入
我们加入,4、5和6。
[hadoop@djt002 sqoopRunCreate]$ sqoop import \ > --connect 'jdbc:mysql://192.168.80.200/hiveuseUnicode=true&characterEncoding=utf-8' \ > --username hive \ > --password-file /user/hadoop/.password \ > --table djt_user \ > --target-dir /sqoop/test/djt_user \ > -m 1 \ > --fields-terminated-by "@" \ > --append \ > --check-column 'id' \ > --incremental append \ > --last-value 3
这里,djt_user是在MySQL里,通过Sqoop工具,导入到HDFS里,是在/sqoop/test/djt_user。
[hadoop@djt002 ~]$ $HADOOP_HOME/bin/hadoop fs -cat /sqoop/test/djt_user/part-m-* 1@王菲@female@36@歌手 2@谢霆锋@male@30@歌手 3@周杰伦@male@33@导演 4@王力宏@male@40@演员 5@张三@male@39@无业游民 6@李四@female@18@学生 [hadoop@djt002 ~]$
(3)自动增量导入
[hadoop@djt002 sqoopRunCreate]$ sqoop job \ > --create job_import_djt_user \ > -- import \ > --connect 'jdbc:mysql://192.168.80.200/hiveuseUnicode=true&characterEncoding=utf-8' \ > --username hive \ > --password-file /user/hadoop/.password \ > --table djt_user \ > --target-dir /sqoop/test/djt_user \ > -m 1 \ > --fields-terminated-by "@" \ > --append \ > --check-column 'id' \ > --incremental append \ > --last-value 6
[hadoop@djt002 sqoopRunCreate]$ sqoop job --exec job_import_djt_user
这里,djt_user是在MySQL里,通过Sqoop工具,导入到HDFS里,是在/sqoop/test/djt_user。
删除某个job
[hadoop@djt002 sqoopRunCreate]$ sqoop job --delete job_import_djt_user
查看当前可用的job
[hadoop@djt002 sqoopRunCreate]$ sqoop job --list
查看某个具体job的信息
[hadoop@djt002 sqoopRunCreate]$ sqoop job --show job_import_djt_user
[hadoop@djt002 sqoopRunCreate]$ sqoop job --show
Warning: /usr/local/sqoop/sqoop-1.4.6/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /usr/local/sqoop/sqoop-1.4.6/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
Warning: /usr/local/sqoop/sqoop-1.4.6/../zookeeper does not exist! Accumulo imports will fail.
Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation.
17/03/18 06:50:58 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6
Missing argument for option: show
[hadoop@djt002 sqoopRunCreate]$ clear
[hadoop@djt002 sqoopRunCreate]$ sqoop job --show job_import_djt_user
Warning: /usr/local/sqoop/sqoop-1.4.6/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /usr/local/sqoop/sqoop-1.4.6/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
Warning: /usr/local/sqoop/sqoop-1.4.6/../zookeeper does not exist! Accumulo imports will fail.
Please set $ZOOKEEPER_HOME to the root of your Zookeeper installation.
17/03/18 06:51:47 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/local/hadoop/hadoop-2.6.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/hbase-1.2.3/lib/slf4j-log4j12-1.7.5.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]
Job: job_import_djt_user
Tool: import
Options:
----------------------------
verbose = false
incremental.last.value = 10
db.connect.string = jdbc:mysql://192.168.80.200/hiveuseUnicode=true&characterEncoding=utf-8
codegen.output.delimiters.escape = 0
codegen.output.delimiters.enclose.required = false
codegen.input.delimiters.field = 0
hbase.create.table = false
hdfs.append.dir = true
db.table = djt_user
codegen.input.delimiters.escape = 0
import.fetch.size = null
accumulo.create.table = false
codegen.input.delimiters.enclose.required = false
db.username = hive
reset.onemapper = false
codegen.output.delimiters.record = 10
import.max.inline.lob.size = 16777216
hbase.bulk.load.enabled = false
hcatalog.create.table = false
db.clear.staging.table = false
incremental.col = id
codegen.input.delimiters.record = 0
db.password.file = /user/hadoop/.password
enable.compression = false
hive.overwrite.table = false
hive.import = false
codegen.input.delimiters.enclose = 0
accumulo.batch.size = 10240000
hive.drop.delims = false
codegen.output.delimiters.enclose = 0
hdfs.delete-target.dir = false
codegen.output.dir = .
codegen.auto.compile.dir = true
relaxed.isolation = false
mapreduce.num.mappers = 1
accumulo.max.latency = 5000
import.direct.split.size = 0
codegen.output.delimiters.field = 64
export.new.update = UpdateOnly
incremental.mode = AppendRows
hdfs.file.format = TextFile
codegen.compile.dir = /tmp/sqoop-hadoop/compile/d81bf23cb3eb8eb11e7064a16df0b92b
direct.import = false
hdfs.target.dir = /sqoop/test/djt_user
hive.fail.table.exists = false
db.batch = false
[hadoop@djt002 sqoopRunCreate]$
Sqoop Import 应用场景——启动压缩
启动压缩
默认是gzip压缩,具体去看官网
[hadoop@djt002 sqoopRunCreate]$ sqoop import \ > --connect 'jdbc:mysql://192.168.80.200/hiveuseUnicode=true&characterEncoding=utf-8' \ > --username hive \ > --password-file /user/hadoop/.password \ > -table djt_user \ > --target-dir /sqoop/test/djt_user \ > --delete-target-dir \ > -m 1 \ > --fields-terminated-by "@" \ > -z
这里,djt_user是在MySQL里,通过Sqoop工具,导入到HDFS里,是在/sqoop/test/djt_user。
[hadoop@djt002 ~]$ $HADOOP_HOME/bin/hadoop fs -cat /sqoop/test/djt_user/part-m-*
Sqoop Import 应用场景——导入空值处理
(1)导入空值处理
先,不加空值处理,看是怎样的。
[hadoop@djt002 sqoopRunCreate]$ sqoop import \ > --connect 'jdbc:mysql://192.168.80.200/hiveuseUnicode=true&characterEncoding=utf-8' \ > --username hive \ > --password-file /user/hadoop/.password \ > -table djt_user \ > --target-dir /sqoop/test/djt_user \ > --delete-target-dir \ > -m 1 \ > --fields-terminated-by "@" >
这里,djt_user是在MySQL里,通过Sqoop工具,导入到HDFS里,是在/sqoop/test/djt_user。
[hadoop@djt002 ~]$ $HADOOP_HOME/bin/hadoop fs -cat /sqoop/test/djt_user/part-m-*
所以,一般需要对null进行转换,即需对空值进行处理。比如年龄那列,要么给他假如是18岁定死,要么就是0等。
[hadoop@djt002 sqoopRunCreate]$ sqoop import \ > --connect 'jdbc:mysql://192.168.80.200/hiveuseUnicode=true&characterEncoding=utf-8' \ > --username hive \ > --password-file /user/hadoop/.password \ > -table djt_user \ > --target-dir /sqoop/test/djt_user \ > --delete-target-dir \ > -m 1 \ > --fields-terminated-by "@" \ > --null-non-string "###" \ > --null-string "###"
这里,djt_user是在MySQL里,通过Sqoop工具,导入到HDFS里,是在/sqoop/test/djt_user。
我这里,将空值null转换成###,这个,大家可以根据自己的需要,可以转换成其它的,不多赘述。自行去举一反三。
[hadoop@djt002 ~]$ $HADOOP_HOME/bin/hadoop fs -cat /sqoop/test/djt_user/part-m-*
下面呢,这个场景,比如,如下,我不需全部的字段导出,非空值的某部分字段呢,该如何操作啊
Sqoop Import 应用场景——导入部分数据
(1)使用–columns
即,指定某个或某些字段导入
比如,我这里,指定只导入id和name,当然,你可以去指定更多,我这里只是个参考和带入门的引子实例罢了。
[hadoop@djt002 sqoopRunCreate]$ sqoop import \ > --connect 'jdbc:mysql://192.168.80.200/hiveuseUnicode=true&characterEncoding=utf-8' \ > --username hive \ > --password-file /user/hadoop/.password \ > -table djt_user \ > --columns id,name \ > --target-dir /sqoop/test/djt_user \ > --delete-target-dir \ > -m 1 \ > --fields-terminated-by "@" \ > --null-non-string "###" \ > --null-string "###"
这里,djt_user是在MySQL里,通过Sqoop工具,导入到HDFS里,是在/sqoop/test/djt_user。
[hadoop@djt002 ~]$ $HADOOP_HOME/bin/hadoop fs -cat /sqoop/test/djt_user/part-m-* 1@王菲 2@谢霆锋 3@周杰伦 4@王力宏 5@张三 6@李四 7@王五 8@王六 9@小王 10@小林 [hadoop@djt002 ~]$
(2)使用–where
刚是导入指定的字段,也可以用筛选来导入达到目的。
比如,我这里,只想导入sex=female的。
[hadoop@djt002 sqoopRunCreate]$ sqoop import \ > --connect 'jdbc:mysql://192.168.80.200/hiveuseUnicode=true&characterEncoding=utf-8' \ > --username hive \ > --password-file /user/hadoop/.password \ > --table djt_user \ > --where "sex='female'" \ > --target-dir /sqoop/test/djt_user \ > --delete-target-dir \ > -m 1 \ > --fields-terminated-by "@" \ > --null-non-string "###" \ > --null-string "###"
这里,djt_user是在MySQL里,通过Sqoop工具,导入到HDFS里,是在/sqoop/test/djt_user。
[hadoop@djt002 ~]$ $HADOOP_HOME/bin/hadoop fs -cat /sqoop/test/djt_user/part-m-* 1@王菲@female@36@歌手 6@李四@female@18@学生 9@小王@female@24@hadoop运维 10@小林@female@30@### [hadoop@djt002 ~]$
(3)使用–query
比如,导入比较复杂更实用。
[hadoop@djt002 ~]$ $HADOOP_HOME/bin/hadoop fs -cat /sqoop/test/djt_user/part-m-* 2@谢霆锋@male@30@歌手 6@李四@female@18@学生 9@小王@female@24@hadoop运维 10@小林@female@30@### [hadoop@djt002 ~]$
注意
若,从MySQL数据库导入数据到HDFS里,出现中断情况了怎么办
答:好比MapReduce作业丢失一样,有个容错机制。但是这里,我们不用担心,任务中断导致数据重复插入,这个不需担心。
它这里呢,要么就是全部导入才成功,要么就是一条都导不进不成功。
即,Sqoop Import HDFS 里没有“脏数据”的情况发生。
MySQL里的数据通过Sqoop importHDFS(作为扩展)
下面我们看一下 Sqoop 如何使用命令行来导入数据的,其命令行语法如下所示。
sqoop import \
--connect jdbc:mysql://192.168.80.128:3306/db_hadoop \
--username sqoop \
--password sqoop \
--table user \
--target-dir /junior/sqoop/ \ //可选,不指定目录,数据默认导入到/user下
--where "sex='female'" \ //可选
--as-sequencefile \ //可选,不指定格式,数据格式默认为 Text 文本格式
--num-mappers 10 \ //可选,这个数值不宜太大
--null-string '\\N' \ //可选
--null-non-string '\\N' \ //可选
--connect:指定 JDBC URL。
--username/password:mysql 数据库的用户名。
--table:要读取的数据库表。
--target-dir:将数据导入到指定的 HDFS 目录下,文件名称如果不指定的话,会默认数据库的表名称。
--where:过滤从数据库中要导入的数据。
--as-sequencefile:指定数据导入数据格式。
--num-mappers:指定 Map 任务的并发度。
--null-string,--null-non-string:同时使用可以将数据库中的空字段转化为'\N',因为数据库中字段为 null,会占用很大的空间。
下面我们介绍几种 Sqoop 数据导入的特殊应用(作为扩展)
1、Sqoop 每次导入数据的时候,不需要把以往的所有数据重新导入 HDFS,只需要把新增的数据导入 HDFS 即可,下面我们来看看如何导入新增数据。
sqoop import \ --connect jdbc:mysql://192.168.80.128:3306/db_hadoop \--username sqoop \ --password sqoop \ --table user \ --incremental append \//代表只导入增量数据--check-column id \//以主键id作为判断条件--last-value 999//导入id大于999的新增数据
上述三个组合使用,可以实现数据的增量导入。
2、Sqoop 数据导入过程中,直接输入明码存在安全隐患,我们可以通过下面两种方式规避这种风险。
1)-P:sqoop 命令行最后使用 -P,此时提示用户输入密码,而且用户输入的密码是看不见的,起到安全保护作用。密码输入正确后,才会执行 sqoop 命令。
sqoop import \ --connect jdbc:mysql://192.168.80.128:3306/db_hadoop \ --username sqoop \ --table user \ -P
2)--password-file:指定一个密码保存文件,读取密码。我们可以将这个文件设置为只有自己可读的文件,防止密码泄露。
sqoop import \ --connect jdbc:mysql://192.168.80.128:3306/db_hadoop \ --username sqoop \ --table user \ --password-file my-sqoop-password
二、通过Sqoop ExportHDFS里的数据到MySQL
它的功能是将数据从 HDFS 导入关系型数据库表中,其流程图如下所示。
我们来分析一下 Sqoop 数据导出流程,首先用户输入一个 Sqoop export 命令,它会获取关系型数据库的 schema,
建立 Hadoop 字段与数据库表字段的映射关系。 然后会将输入命令转化为基于 Map 的 MapReduce作业,
这样 MapReduce作业中有很多 Map 任务,它们并行的从 HDFS 读取数据,并将整个数据拷贝到数据库中。
大家,必须要去看官网!
Sqoop Export 应用场景——直接导出
直接导出
请去看我下面的这篇博客,对你有好处。我不多赘述。
SQLyog普通版与SQLyog企业版对比分析
CREATE TABLE djt_user_copy SELECT * FROM djt_user WHERE 1=2;
[hadoop@djt002 sqoopRunCreate]$ sqoop export \ > --connect 'jdbc:mysql://192.168.80.200/hiveuseUnicode=true&characterEncoding=utf-8' \ > --username hive \ > --password-file /user/hadoop/.password \ > --table djt_user_copy \ > --export-dir /sqoop/test/djt_user \ > --input-fields-terminated-by "@"
这里,HDFS里,是在/sqoop/test/djt_user,通过Sqoop工具,导出到djt_user_copy是在MySQL里。
因为啊,之前,/sqoop/test/djt_user的数据如下
Sqoop Export 应用场景——指定map数
指定map数
Map Task默认是4个
[hadoop@djt002 sqoopRunCreate]$ sqoop export \ > --connect 'jdbc:mysql://192.168.80.200/hiveuseUnicode=true&characterEncoding=utf-8' \ > --username hive \ > --password-file /user/hadoop/.password \ > --table djt_user_copy \ > --export-dir /sqoop/test/djt_user \ > --input-fields-terminated-by "@" \ > -m 1
这里,HDFS里,是在/sqoop/test/djt_user,通过Sqoop工具,导出到djt_user_copy是在MySQL里。
Sqoop Export 应用场景——插入和更新
插入和更新
[hadoop@djt002 sqoopRunCreate]$ sqoop export \ > --connect 'jdbc:mysql://192.168.80.200/hiveuseUnicode=true&characterEncoding=utf-8' \ > --username hive \ > --password-file /user/hadoop/.password \ > --table djt_user_copy \ > --export-dir /sqoop/test/djt_user \ > --input-fields-terminated-by "@" \ > -m 1 \ > --update-key id \ > --update-mode allowinsert
这里,HDFS里,是在/sqoop/test/djt_user,通过Sqoop工具,导出到djt_user_copy是在MySQL里。
Sqoop Export 应用场景——事务处理
事务处理
比如,从HDFS里导出到MySQL。这个时候可能会出现意外情况,如出现中断,则会出现“脏数据”重复情况。
则提供了这个事务处理。
即HDFS -> 先导出到 中间表(成功才可以,后续导出) -> MySQL
我这里是, /sqoop/test/djt_user(在HDFS里) -> djt_user_copy_tmp (在MySQL里) -> djt_user_copy (在MySQL里)
这里,HDFS里,是在/sqoop/test/djt_user,通过Sqoop工具,导出到djt_user_copy是在MySQL里。
注意这个中间表,需要创建djt_user_copy_tmp
[hadoop@djt002 sqoopRunCreate]$ sqoop export \ > --connect 'jdbc:mysql://192.168.80.200/hiveuseUnicode=true&characterEncoding=utf-8' \ > --username hive \ > --password-file /user/hadoop/.password \ > --table djt_user_copy \ > --staging-table djt_user_copy_tmp \ > --clear-staging-table \ > --export-dir /sqoop/test/djt_user \ > -input-fields-terminated-by "@"
这里,HDFS里,是在/sqoop/test/djt_user,通过Sqoop工具,先导出到中间表djt_user_copy_tmp是在MySQL里,再继续导出到djt_user_copy是在MySQL里。
因为,此刻HDFS里的
再次做测试,假设,我现在,把MySQL里的djt_user数据导入到HDFS里的/sqoop/test/djt_user。
[hadoop@djt002 sqoopRunCreate]$ sqoop import --connect 'jdbc:mysql://192.168.80.200/hiveuseUnicode=true&characterEncoding=utf-8' --username hive --password-file /user/hadoop/.password -table djt_user --target-dir /sqoop/test/djt_user --delete-target-dir -m 1 --fields-terminated-by "@" --null-non-string "###" --null-string "###"
[hadoop@djt002 ~]$ $HADOOP_HOME/bin/hadoop fs -cat /sqoop/test/djt_user/part-m-* 1@王菲@female@36@歌手 2@谢霆锋@male@30@歌手 3@周杰伦@male@33@导演 4@王力宏@male@40@演员 5@张三@male@39@无业游民 6@李四@female@18@学生 7@王五@male@34@Java开发工程师 8@王六@male@45@hadoop工程师 9@小王@female@24@hadoop运维 10@小林@female@30@### [hadoop@djt002 ~]$
然后,接着,我们把HDFS里的/sqoop/test/djt_user 导出到 MySQL里的djt_user_copydjt_user_copy。
说白了,就是再次做了一下Sqoop Export 应用场景——事务处理。(自己好好理清思路去)
即HDFS -> 先导出到 中间表(成功才可以,后续导出) -> MySQL
我这里是, /sqoop/test/djt_user(在HDFS里) -> djt_user_copy_tmp (在MySQL里) -> djt_user_copydjt_user_copy(在MySQL里)
[hadoop@djt002 sqoopRunCreate]$ sqoop export \ > --connect 'jdbc:mysql://192.168.80.200/hiveuseUnicode=true&characterEncoding=utf-8' \ > --username hive \ > --password-file /user/hadoop/.password \ > --table djt_user_copy \ > --staging-table djt_user_copy_tmp \ > --clear-staging-table \ > --export-dir /sqoop/test/djt_user \ > -input-fields-terminated-by "@"
这里,HDFS里,是在/sqoop/test/djt_user,通过Sqoop工具,先导出到中间表djt_user_copy_tmp是在MySQL里,再继续导出到djt_user_copy是在MySQL里。
因为,HDFS里的
得到,
Sqoop Export HDFS 应用场景——字段不对应问题
字段不对应问题
因为,在Sqoop import时,我们有选择性的导入某个字段或某些字段对吧,那么,同样,对于Sqoop export也是一样!
[hadoop@djt002 sqoopRunCreate]$ sqoop import --connect 'jdbc:mysql://192.168.80.200/hiveuseUnicode=true&characterEncoding=utf-8' --username hive --password-file /user/hadoop/.password -table djt_user --columns name,sex,age,profile --target-dir /sqoop/test/djt_user --delete-target-dir -m 1 --fields-terminated-by "@" --null-non-string "###" --null-string "###"
比如,HDFS里(的/sqoop/test/djt_user/)有4列, 数据库里(的djt_user_copy)有5列(因为会多出自增键)。那么,如何来处理这个棘手问题呢
这样来处理,
照样sqoop export里也有-columns name,sex,age,profile \
我的这里,自增键呢/
通过Sqoop ExportHDFS里的数据到MySQL(作为扩展)
下面我们看一下 Sqoop 如何使用命令行来导出数据的,其命令行语法如下所示。
sqoop export \ --connect jdbc:mysql://192.168.80.128:3306/db_hadoop \ --username sqoop \ --password sqoop \ --table user \ --export-dir user
--connect:指定 JDBC URL。
--username/password:mysql 数据库的用户名和密码。
--table:要导入的数据库表。
--export-dir:数据在 HDFS 上的存放目录。
下面我们介绍几种 Sqoop 数据导出的特殊应用(作为扩展)
1、Sqoop export 将数据导入数据库,一般情况下是一条一条导入的,这样导入的效率非常低。这时我们可以使用 Sqoop export 的批量导入提高效率,其具体语法如下。
sqoop export \
--Dsqoop.export.records.per.statement=10 \
--connect jdbc:mysql://192.168.80.128:3306/db_hadoop \
--username sqoop \
--password sqoop \
--table user \
--export-dir user \
--batch
--Dsqoop.export.records.per.statement:指定每次导入10条数据,--batch:指定是批量导入。
2、在实际应用中还存在这样一个问题,比如导入数据的时候,Map Task 执行失败,
那么该 Map 任务会转移到另外一个节点执行重新运行,这时候之前导入的数据又要重新导入一份,造成数据重复导入。
因为 Map Task 没有回滚策略,一旦运行失败,已经导入数据库中的数据就无法恢复。
Sqoop export 提供了一种机制能保证原子性, 使用--staging-table 选项指定临时导入的表。
Sqoop export 导出数据的时候会分为两步:
第一步,将数据导入数据库中的临时表,如果导入期间 Map Task 失败,会删除临时表数据重新导入;
第二步,确认所有 Map Task 任务成功后,会将临时表名称为指定的表名称。
sqoop export \ --connect jdbc:mysql://192.168.80.128:3306/db_hadoop \ --username sqoop \ --password sqoop \ --table user \ --staging-table staging_user
3、在 Sqoop 导出数据过程中,如果我们想更新已有数据,可以采取以下两种方式。
1)通过 --update-key id 更新已有数据。
sqoop export \ --connect jdbc:mysql://192.168.80.128:3306/db_hadoop \ --username sqoop \ --password sqoop \ --table user \ --update-key id
2) 使用 --update-key id和--update-mode allowinsert 两个选项的情况下,如果数据已经存在,则更新数据,如果数据不存在,则插入新数据记录。
sqoop export \ --connect jdbc:mysql://192.168.80.128.:3306/db_hadoop \ --username sqoop \ --password sqoop \ --table user \ --update-key id \ --update-mode allowinsert
4、如果 HDFS 中的数据量比较大,很多字段并不需要,我们可以使用 --columns 来指定插入某几列数据。
sqoop export \ --connect jdbc:mysql://192.168.80.128:3306/db_hadoop \ --username sqoop \ --password sqoop \ --table user \ --column username,sex
5、当导入的字段数据不存在或者为null的时候,我们使用--input-null-string和--input-null-non-string 来处理。
sqoop export \ --connect jdbc:mysql://129.168.80.128:3306/db_hadoop \ --username sqoop \ --password sqoop \ --table user \ --input-null-string '\\N' \ --input-null-non-string '\\N'
推荐博客
http://sqoop.apache.org/docs/1.4.6/SqoopUserGuide.html(sqoop官网文档)
1. 概述
本文档主要对SQOOP的使用进行了说明,参考内容主要来自于Cloudera SQOOP的官方文档。为了用中文更清楚明白地描述各参数的使用含义,本文档几乎所有参数使用说明都经过了我的实际验证而得到。
2. codegen
将关系数据库表映射为一个java文件、java class类、以及相关的jar包,作用主要是两方面:
1、 将数据库表映射为一个Java文件,在该Java文件中对应有表的各个字段。
2、 生成的Jar和class文件在metastore功能使用时会用到。
基础语句:
sqoop codegen –connect jdbc:mysql://localhost:3306/hive –username root –password 123456 –table TBLS2
参数 | 说明 |
–bindir <dir> | 指定生成的java文件、编译成的class文件及将生成文件打包为JAR的JAR包文件输出路径 |
–class-name <name> | 设定生成的Java文件指定的名称 |
–outdir <dir> | 生成的java文件存放路径 |
–package-name<name> | 包名,如cn.cnnic,则会生成cn和cnnic两级目录,生成的文件(如java文件)就存放在cnnic目录里 |
–input-null-non-string<null-str> | 在生成的java文件中,可以将null字符串设为想要设定的值(比如空字符串’’) |
–input-null-string<null-str> | 同上,设定时,最好与上面的属性一起设置,且设置同样的值(比如空字符串等等)。 |
–map-column-java<arg> | 数据库字段在生成的java文件中会映射为各种属性,且默认的数据类型与数据库类型保持对应,比如数据库中某字段的类型为bigint,则在Java文件中的数据类型为long型,通过这个属性,可以改变数据库字段在java中映射的数据类型,格式如:–map-column-java DB_ID=String,id=Integer |
–null-non-string<null-str> | 在生成的java文件中,比如TBL_ID==null”null”:””,通过这个属性设置可以将null字符串设置为其它值如ddd,TBL_ID==null”ddd”:”” |
–null-string<null-str> | 同上,使用的时候最好和上面的属性一起用,且设置为相同的值 |
–table <table-name> | 对应关系数据库的表名,生成的java文件中的各属性与该表的各字段一一对应。 |
3. create-hive-table
生成与关系数据库表的表结构对应的HIVE表
基础语句:
sqoop create-hive-table –connect jdbc:mysql://localhost:3306/hive -username root -password 123456 –table TBLS –hive-table h_tbls2
参数 | 说明 |
–hive-home <dir> | Hive的安装目录,可以通过该参数覆盖掉默认的hive目录 |
–hive-overwrite | 覆盖掉在hive表中已经存在的数据 |
–create-hive-table | 默认是false,如果目标表已经存在了,那么创建任务会失败 |
–hive-table | 后面接要创建的hive表 |
–table | 指定关系数据库表名 |
4. eva l
可以快速地使用SQL语句对关系数据库进行操作,这可以使得在使用import这种工具进行数据导入的时候,可以预先了解相关的SQL语句是否正确,并能将结果显示在控制台。
查询示例:
sqoop eva l –connect jdbc:mysql://localhost:3306/hive -username root -password 123456 -query “SELECT * FROM tbls LIMIT 10″
数据插入示例:
sqoop eva l –connect jdbc:mysql://localhost:3306/hive -username root -password 123456 -e “INSERT INTO TBLS2
VALUES(100,1375170308,1,0,’hadoop’,0,1,’guest’,’MANAGED_TABLE’,’abc’,’ddd’)”
-e、-query这两个参数经过测试,比如后面分别接查询和插入SQL语句,皆可运行无误,如上。
5. export
从hdfs中导数据到关系数据库中
sqoop export –connect jdbc:mysql://localhost:3306/hive –username root –password
123456 –table TBLS2 –export-dir sqoop/test
参数 | 说明 |
–direct | 快速模式,利用了数据库的导入工具,如mysql的mysqlimport,可以比jdbc连接的方式更为高效的将数据导入到关系数据库中。 |
–export-dir <dir> | 存放数据的HDFS的源目录 |
-m,–num-mappers <n> | 启动N个map来并行导入数据,默认是4个,最好不要将数字设置为高于集群的最大Map数 |
–table <table-name> | 要导入到的关系数据库表 |
–update-key <col-name> | 后面接条件列名,通过该参数,可以将关系数据库中已经存在的数据进行更新操作,类似于关系数据库中的update操作 |
–update-mode <mode> | 更新模式,有两个值updateonly和默认的allowinsert,该参数只能是在关系数据表里不存在要导入的记录时才能使用,比如要导入的hdfs中有一条id=1的记录,如果在表里已经有一条记录id=2,那么更新会失败。 |
–input-null-string <null-string> | 可选参数,如果没有指定,则字符串null将被使用 |
–input-null-non-string <null-string> | 可选参数,如果没有指定,则字符串null将被使用 |
–staging-table <staging-table-name> | 该参数是用来保证在数据导入关系数据库表的过程中事务安全性的,因为在导入的过程中可能会有多个事务,那么一个事务失败会影响到其它事务,比如导入的数据会出现错误或出现重复的记录等等情况,那么通过该参数可以避免这种情况。创建一个与导入目标表同样的数据结构,保留该表为空在运行数据导入前,所有事务会将结果先存放在该表中,然后最后由该表通过一次事务将结果写入到目标表中。 |
–clear-staging-table | 如果该staging-table非空,则通过该参数可以在运行导入前清除staging-table里的数据。 |
–batch | 该模式用于执行基本语句(暂时还不太清楚含义) |
6. import
将数据库表的数据导入到hive中,如果在hive中没有对应的表,则自动生成与数据库表名相同的表。
sqoop import –connect jdbc:mysql://localhost:3306/hive –username root –password
123456 –table user –split-by id –hive-import
–split-by指定数据库表中的主键字段名,在这里为id。
参数 | 说明 |
–append | 将数据追加到hdfs中已经存在的dataset中。使用该参数,sqoop将把数据先导入到一个临时目录中,然后重新给文件命名到一个正式的目录中,以避免和该目录中已存在的文件重名。 |
–as-avrodatafile | 将数据导入到一个Avro数据文件中 |
–as-sequencefile | 将数据导入到一个sequence文件中 |
–as-textfile | 将数据导入到一个普通文本文件中,生成该文本文件后,可以在hive中通过sql语句查询出结果。 |
–boundary-query <statement> |
边界查询,也就是在导入前先通过SQL查询得到一个结果集,然后导入的数据就是该结果集内的数据,格式如:–boundary-query ‘select id,creationdate from person where id = 3’,表示导入的数据为id=3的记录,或者select min(<split-by>), max(<split-by>) from <table name>,注意查询的字段中不能有数据类型为字符串的字段,否则会报错:java.sql.SQLException: Invalid value
for
getLong() 目前问题原因还未知
|
–columns<col,col,col…> | 指定要导入的字段值,格式如:–columns id,username |
–direct | 直接导入模式,使用的是关系数据库自带的导入导出工具。官网上是说这样导入会更快 |
–direct-split-size | 在使用上面direct直接导入的基础上,对导入的流按字节数分块,特别是使用直连模式从PostgreSQL导入数据的时候,可以将一个到达设定大小的文件分为几个独立的文件。 |
–inline-lob-limit | 设定大对象数据类型的最大值 |
-m,–num-mappers | 启动N个map来并行导入数据,默认是4个,最好不要将数字设置为高于集群的节点数 |
–query,-e<statement> |
从查询结果中导入数据,该参数使用时必须指定–target-dir、–hive-table,在查询语句中一定要有where条件且在where条件中需要包含$CONDITIONS,示例:–query ‘select * from person where $CONDITIONS ‘ –target-dir
/user/hive/warehouse/person –hive-table person |
–split-by<column-name> | 表的列名,用来切分工作单元,一般后面跟主键ID |
–table <table-name> | 关系数据库表名,数据从该表中获取 |
–target-dir <dir> | 指定hdfs路径 |
–warehouse-dir <dir> | 与–target-dir不能同时使用,指定数据导入的存放目录,适用于hdfs导入,不适合导入hive目录 |
–where | 从关系数据库导入数据时的查询条件,示例:–where ‘id = 2′ |
-z,–compress | 压缩参数,默认情况下数据是没被压缩的,通过该参数可以使用gzip压缩算法对数据进行压缩,适用于SequenceFile, text文本文件, 和Avro文件 |
–compression-codec | Hadoop压缩编码,默认是gzip |
–null-string <null-string> | 可选参数,如果没有指定,则字符串null将被使用 |
–null-non-string<null-string> | 可选参数,如果没有指定,则字符串null将被使用 |
增量导入
参数 | 说明 |
–check-column (col) | 用来作为判断的列名,如id |
–incremental (mode) | append:追加,比如对大于last-value指定的值之后的记录进行追加导入。lastmodified:最后的修改时间,追加last-value指定的日期之后的记录 |
–last-value (value) | 指定自从上次导入后列的最大值(大于该指定的值),也可以自己设定某一值 |
对incremental参数,如果是以日期作为追加导入的依据,则使用lastmodified,否则就使用append值。
7. import-all-tables
将数据库里的所有表导入到HDFS中,每个表在hdfs中都对应一个独立的目录。
sqoop import-all-tables –connect jdbc:mysql://localhost:3306/test
sqoop import-all-tables –connect jdbc:mysql://localhost:3306/test –hive-import
参数 | 说明 |
–as-avrodatafile | 同import参数 |
–as-sequencefile | 同import参数 |
–as-textfile | 同import参数 |
–direct | 同import参数 |
–direct-split-size <n> | 同import参数 |
–inline-lob-limit <n> | 同import参数 |
-m,–num-mappers <n> | 同import参数 |
–warehouse-dir <dir> | 同import参数 |
-z,–compress | 同import参数 |
–compression-codec | 同import参数 |
8. job
用来生成一个sqoop的任务,生成后,该任务并不执行,除非使用命令执行该任务。
sqoop job
参数 | 说明 |
–create <job-id> |
生成一个job,示例如:sqoop job –create myjob — import –connectjdbc:mysql://localhost:3306/test –table
person
|
–delete <job-id> | 删除一个jobsqoop job –delete myjob |
–exec <job-id> | 执行一个jobsqoop job –exec myjob |
–help | 显示帮助说明 |
–list | 显示所有的jobsqoop job –list |
–meta-connect <jdbc-uri> | 用来连接metastore服务,示例如:–meta-connect jdbc:hsqldb:hsql://localhost:16000/sqoop |
–show <job-id> | 显示一个job的各种参数sqoop job –show myjob |
–verbose | 打印命令运行时的详细信息 |
9. list-databases
打印出关系数据库所有的数据库名
sqoop list-databases –connect jdbc:mysql://localhost:3306/ -username root -password 123456
10. list-tables
打印出关系数据库某一数据库的所有表名
sqoop list-tables –connect jdbc:mysql://localhost:3306/zihou -username root -password 123456
11. merge
将HDFS中不同目录下面的数据合在一起,并存放在指定的目录中,示例如:
sqoop merge –new-data /test/p1/person –onto /test/p2/person –target-dir /test/merged –jar-file /opt/data/sqoop/person/Person.jar –class-name Person –merge-key id
其中,–class-name所指定的class名是对应于Person.jar中的Person类,而Person.jar是通过Codegen生成的
参数 | 说明 |
–new-data <path> | Hdfs中存放数据的一个目录,该目录中的数据是希望在合并后能优先保留的,原则上一般是存放越新数据的目录就对应这个参数。 |
–onto <path> | Hdfs中存放数据的一个目录,该目录中的数据是希望在合并后能被更新数据替换掉的,原则上一般是存放越旧数据的目录就对应这个参数。 |
–merge-key <col> | 合并键,一般是主键ID |
–jar-file <file> | 合并时引入的jar包,该jar包是通过Codegen工具生成的jar包 |
–class-name <class> | 对应的表名或对象名,该class类是包含在jar包中的。 |
–target-dir <path> | 合并后的数据在HDFS里的存放目录 |
12. metastore
记录sqoop job的元数据信息,如果不启动metastore实例,则默认的元数据存储目录为:~/.sqoop,如果要更改存储目录,可以在配置文件sqoop-site.xml中进行更改。
metastore实例启动:sqoop metastore
参数 | 说明 |
–shutdown | 关闭一个运行的metastore实例 |
13. version
显示sqoop版本信息
语句:sqoop version
14. help
打印sqoop帮助信息
语句:sqoop help
15. 公共参数
Hive参数
参数 | 说明 |
–hive-delims-replacement <arg> | 用自定义的字符串替换掉数据中的\n, \r, and \01等字符 |
–hive-drop-import-delims | 在导入数据到hive中时,去掉数据中\n,\r和\01这样的字符 |
–map-column-hive <arg> | 生成hive表时,可以更改生成字段的数据类型,格式如:–map-column-hiveTBL_ID=String,LAST_ACCESS_TIME=string |
–hive-partition-key | 创建分区,后面直接跟分区名即可,创建完毕后,通过describe 表名可以看到分区名,默认为string型 |
–hive-partition-value<v> | 该值是在导入数据到hive中时,与–hive-partition-key设定的key对应的value值。 |
–hive-home <dir> | Hive的安装目录,可以通过该参数覆盖掉默认的hive目录 |
–hive-import | 将数据从关系数据库中导入到hive表中 |
–hive-overwrite | 覆盖掉在hive表中已经存在的数据 |
–create-hive-table | 默认是false,如果目标表已经存在了,那么创建任务会失败 |
–hive-table | 后面接要创建的hive表 |
–table | 指定关系数据库表名 |
数据库连接参数
参数 | 说明 |
–connect <jdbc-uri> | Jdcb连接url,示例如:–connect jdbc:mysql://localhost:3306/hive |
–connection-manager <class-name> | 指定要使用的连接管理类 |
–driver <class-name> | 数据库驱动类 |
–hadoop-home <dir> | Hadoop根目录 |
–help | 打印帮助信息 |
-P | 从控制端读取密码 |
–password <password> | Jdbc url中的数据库连接密码 |
–username <username> | Jdbc url中的数据库连接用户名 |
–verbose | 在控制台打印出详细信息 |
–connection-param-file <filename> | 一个记录着数据库连接参数的文件 |
文件输出参数
用于import场景。
示例如:
sqoop import –connect jdbc:mysql://localhost:3306/test –username root –P –table person –split-by id –check-column id –incremental append –last-value 1 –enclosed-by ‘\”‘
–escaped-by \# –fields-terminated-by .
参数 | 说明 |
–enclosed-by <char> | 给字段值前后加上指定的字符,比如双引号,示例:–enclosed-by ‘\”‘,显示例子:”3″,”jimsss”,”dd@dd.com” |
–escaped-by <char> | 给双引号作转义处理,如字段值为”测试”,经过–escaped-by \\处理后,在hdfs中的显示值为:\”测试\”,对单引号无效 |
–fields-terminated-by <char> | 设定每个字段是以什么符号作为结束的,默认是逗号,也可以改为其它符号,如句号.,示例如:–fields-terminated-by. |
–lines-terminated-by <char> | 设定每条记录行之间的分隔符,默认是换行,但也可以设定自己所需要的字符串,示例如:–lines-terminated-by ‘#’ 以#号分隔 |
–mysql-delimiters | Mysql默认的分隔符设置,字段之间以,隔开,行之间以换行\n隔开,默认转义符号是\,字段值以单引号’包含起来。 |
–optionally-enclosed-by <char> |
enclosed-by是强制给每个字段值前后都加上指定的符号,而–optionally-enclosed-by只是给带有双引号或单引号的字段值加上指定的符号,故叫可选的。示例如:–optionally-enclosed-by ‘$’
显示结果: $”hehe”,测试$ |
文件输入参数
对数据格式的解析,用于export场景,与文件输出参数相对应。
示例如:
sqoop export –connect jdbc:mysql://localhost:3306/test –username root –password
123456 –table person2 –export-dir /user/hadoop/person –staging-table person3
–clear-staging-table –input-fields-terminated-by ‘,’
在hdfs中存在某一格式的数据,在将这样的数据导入到关系数据库中时,必须要按照该格式来解析出相应的字段值,比如在hdfs中有这样格式的数据:
3,jimsss,dd@dd.com,1,2013-08-07 16:00:48.0,”hehe”,测试
上面的各字段是以逗号分隔的,那么在解析时,必须要以逗号来解析出各字段值,如:
–input-fields-terminated-by ‘,’
参数 | 说明 |
–input-enclosed-by <char> | 对字段值前后有指定的字符,比如双引号的值进行解析:–input-enclosed-by ‘\”‘,数据例子:”3″,”jimsss”,”dd@dd.com” |
–input-escaped-by <char> | 对含有转义双引号的字段值作转义处理,如字段值为\”测试\”,经过–input-escaped-by \\处理后,解析得到的值为:”测试”,对单引号无效。 |
–input-fields-terminated-by <char> | 以字段间的分隔符来解析得到各字段值,示例如:– input-fields-terminated-by, |
–input-lines-terminated-by <char> | 以每条记录行之间的分隔符来解析得到字段值,示例如:–input-lines-terminated-by ‘#’ 以#号分隔 |
–input-optionally-enclosed-by <char> | 与–input-enclosed-by功能相似,与–input-enclosed-by的区别参见输出参数中对–optionally-enclosed-by的描述 |
sqoop import --connect jdbc:mysql://localhost/db --username foo --table TEST
2.账号密码sqoop import --connect jdbc:mysql://database.example.com/employees \
--username aaron --password 12345
3.驱动sqoop import --driver com.microsoft.jdbc.sqlserver.SQLServerDriver \
--connect <connect-string> ...
4.写sql语句导入的方式 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
如果是顺序导入的话,可以只开一个线程sqoop import \
--query 'SELECT a.*, b.* FROM a JOIN b on (a.id == b.id) WHERE $CONDITIONS' \
-m 1 --target-dir /user/foo/joinresults
sqoop import --connnect <connect-str> --table foo --warehouse-dir /shared \
或者 sqoop import --connnect <connect-str> --table foo --target-dir /dest \
9.传递参数给快速导入的工具,使用--开头,下面这句命令传递给mysql默认的字符集是latin1。sqoop import --connect jdbc:mysql://server.foo.com/db --table bar \
--direct -- --default-character-set=latin1
sqoop import ... --null-string '\\N' --null-non-string '\\N'
<property>
<name>property.name</name>
<value>property.value</value>
</property>
如果不在这里面配置的话,就需要像这样写命令 sqoop import -D property.name=property.value ...
#指定列
$ sqoop import --connect jdbc:mysql://db.foo.com/corp --table EMPLOYEES \
--columns "employee_id,first_name,last_name,job_title"
#使用8个线程
$ sqoop import --connect jdbc:mysql://db.foo.com/corp --table EMPLOYEES \
-m 8
#快速模式
$ sqoop import --connect jdbc:mysql://db.foo.com/corp --table EMPLOYEES \
--direct
#使用sequencefile作为存储方式
$ sqoop import --connect jdbc:mysql://db.foo.com/corp --table EMPLOYEES \
--class-name com.foocorp.Employee --as-sequencefile
#分隔符
$ sqoop import --connect jdbc:mysql://db.foo.com/corp --table EMPLOYEES \
--fields-terminated-by '\t' --lines-terminated-by '\n' \
--optionally-enclosed-by '\"'
#导入到hive
$ sqoop import --connect jdbc:mysql://db.foo.com/corp --table EMPLOYEES \
--hive-import
#条件过滤
$ sqoop import --connect jdbc:mysql://db.foo.com/corp --table EMPLOYEES \
--where "start_date > '2010-01-01'"
#用dept_id作为分个字段
$ sqoop import --connect jdbc:mysql://db.foo.com/corp --table EMPLOYEES \
--split-by dept_id
#追加导入
$ sqoop import --connect jdbc:mysql://db.foo.com/somedb --table sometable \
--where "id > 100000" --target-dir /incremental_dataset --append
sqoop import-all-tables --connect jdbc:mysql://db.foo.com/corp
sqoop-export --table foo --update-key id --export-dir /path/to/data --connect …
UPDATE foo SET msg='this is a test', bar=42 WHERE id=0;
UPDATE foo SET msg='some more data', bar=100 WHERE id=1;
...
$ sqoop export --connect jdbc:mysql://db.example.com/foo --table bar \
--export-dir /results/bar_data
$ sqoop export --connect jdbc:mysql://db.example.com/foo --table bar \
--export-dir /results/bar_data --validate
$ sqoop export --connect jdbc:mysql://db.example.com/foo --call barproc \
--export-dir /results/bar_data