Sqoop(即SQL to Hadoop)开始于2009年,是一个用来将Hadoop(hdfs/hive/hbase)和关系型数据库(如MySQL,Oracle,SQL SERVER,Postgres等)中的数据相互转移的工具,可以将一个关系型数据库中的数据导进到Hadoop的HDFS中,也可以将HDFS的数据导进到关系型数据库中。它通过MapReduce任务来传输数据,并充分利用MapReduce并行特点以批处理的方式加快数据传输,从而提高并发特性和容错。
官网说明:http://sqoop.apache.org/docs/1.4.7/SqoopUserGuide.html
Sqoop命令分四大类:
(1).sqoop import 单表导入
(2).sqoop import-all-tables 批量导入(整个数据库导入)
(3).sqoop export 导出(常用于数量增量更新/同步)
(4).sqoop job 多任务创建(增量导入时的任务创建)(可自动记录上一次导入的最大Key值)
使用参数说明
1.数据导入:sqoop import
可通过 sqoop import --help 命令查看
参数 | 说明 |
---|---|
普通参数 | |
–connect | 指定JDBC连接字符串 |
–connection-manager | 指定连接管理器类名 |
–connection-param-file | 指定连接参数文件 |
–driver | 手动指定要使用的JDBC驱动程序类 |
–hadoop-home | 覆盖 $HADOOP_MAPR ED_HOME_ARG 参数 |
–hadoop-mapred-home
| 覆盖 $HADOOP_MAPR ED_HOME_ARG 参数 |
–help | 打印使用说明 |
–metadata-transaction-isolation-level | 为元数据查询定义事务隔离级别 |
–oracle-escaping-disabled | 禁用Oracle/OraOop连接管理器的转义机制 |
-P | 从控制台读取密码 |
–password | 设置密码验证 |
–password-alias | 凭据提供程序密码别名 |
–password-file | 在密码文件路径上设置验证 |
–relaxed-isolation | 对导入使用read-uncommi隔离 |
–skip-dist-cache | 跳过将jar复制到分布式缓存 |
–temporary-rootdir | 定义导入的临时根目录 |
–throw-on-error | 在作业期间发生错误时,重新抛出RuntimeException |
–username | 设置身份验证的用户名 |
–verbose | 工作时打印更多信息 |
导入的控制参数 | |
–append | 以追加模式导入数据 |
–as-avrodatafile | 将导入数据以avro文件存储 |
–as-parquetfile | 将导入数据以parquet文件存储 |
–as-sequencefile | 将导入数据以SequenceFile文件存储 |
–as-textfile | 以纯文本形式导入数据(默认) |
–autoreset-to-one-mapper | 如果没有拆分键可用,则将映射器的数量重置为一个映射器 |
–boundary-query | 设置边界查询,检索主键的最大值和最小值 |
–columns <col,col,col…> | 指定需要导入的列 |
–compression-codec | 用于导入的压缩编解码器 |
–delete-target-dir | 以删除模式导入数据(如果目标文件存在则删除再导入. 不指定时如果目标路径存在则报错) |
–direct | 使用直接导入快速路径 |
–direct-split-size | 在直接模式导入时,将输入流按“n”字节分割 |
-e,–query | 导入SQL“语句”的结果 |
–fetch-size | 当需要更多行时,设置从数据库中获取的行数’n’,设置内联LOB的最大大小 |
-m,–num-mappers | 使用n个map任务并行导入.默认并行度为4 |
–mapreduce-job-name | 为生成的mapreduce作业设置名称 |
–merge-key | 要用于合并结果的Key列(用于增量导入时重复数据的合并) |
–split-by | 用于分割工作单元的表的列 |
–split-limit | 日期/时间/时间戳和整数类型的拆分列每次拆分的行上限。对于日期或时间戳字段,以秒为单位计算。拆分极限应该大于0 |
–table | 读取的表名(要导入的表) |
–target-dir | 导入的表存放于HDFS中的目标路径 |
–validate | 使用配置的验证器验证副本 |
–validation-failurehandler | ValidationFailureHandler的完全限定类名 |
–validation-threshold | ValidationThreshold的完全限定类名 |
–validator | Validator的完全限定类名 |
–warehouse-dir | 要导入hdfs的父路径 |
–where | 导入时使用WHERE条件过滤 |
-z,–compress | 启用压缩 |
增量导入参数 | |
–check-column | 源列,以检查增量更改 |
–incremental | 定义类型为“append”或“lastmodified”的增量导入 |
–last-value | 增量检查列中最后导入的值 |
输出行格式化参数 | |
–enclosed-by | 设置所需字段的封闭字符 |
–escaped-by | 设置转义字符 |
–fields-terminated-by | 设置字段分隔符 |
–lines-terminated-by | 设置行尾字符 |
–mysql-delimiters | 使用MySQL默认的分隔符集: 字段:, ;行:\n ;转义字符:\ ;字段包围符:’ |
–optionally-enclosed-by | 设置包含字符的字段 |
输入解析参数 | |
–input-enclosed-by | 设置所需的字段罩 |
–input-escaped-by | 设置输入转义字符 |
–input-fields-terminated-by | 设置输入字段分隔符 |
–input-lines-terminated-by | 设置输入行结束字符 |
–input-optionally-enclosed-by | 设置包含字符的字段 |
Hive参数 | |
–create-hive-table | 导入时自动创建Hive表.如果目标hive表存在,则失败 |
–hive-database | 设置导入到hive时要使用的数据库名称 |
–hive-delims-replacement | 用用户定义的字符串替换导入字符串字段中的Hive record \0x01和行分隔符(\n\r) |
–hive-drop-import-delims | 从导入的字符串字段中删除Hive记录\0x01和行分隔符(\n\r) |
–hive-home | 覆盖 $HIVE_HOME 配置参数 |
–hive-import | 将表导入到Hive中(如果没有设置任何分隔符,则使用Hive的默认分隔符) |
–hive-overwrite | 重写Hive表中的现有数据(覆盖导入) |
–hive-partition-key | 设置导入到hive时要使用的分区键 |
–hive-partition-value | 设置导入到hive时要使用的分区值 |
–hive-table | 设置导入到hive时要使用的表名 |
–map-column-hive | 覆盖指定列到hive类型的映射 |
HCatalog参数(Hive元数据导入参数) | |
–hcatalog-database | HCatalog数据库名称(即Hive数据库) |
–hcatalog-home | 覆盖 $HCAT_HOME(即HIVE_HOME) |
–hcatalog-partition-keys | 设置导入到hive时要使用的分区键 |
–hcatalog-partition-values | 设置导入到hive时要使用的分区值 |
–hcatalog-table | HCatalog表名(即Hive表) |
–hive-home | 覆盖 $HIVE_HOME |
–hive-partition-key | 设置导入到hive时要使用的分区键 |
–hive-partition-value | 设置导入到hive时要使用的分区值 |
–map-column-hive | 覆盖指定列到hive类型的映射 |
HCatalog导入特定选项 | |
–create-hcatalog-table | 在导入之前创建HCatalog |
–drop-and-create-hcatalog-table | 在导入之前删除并创建HCatalog |
–hcatalog-storage-stanza | 用于创建表的HCatalog存储节 |
HBase参数 | |
–column-family | 设置导入的目标列族 |
–hbase-bulkload | 启用HBase批量加载 |
–hbase-create-table | 如果指定,创建缺少的HBase表 |
–hbase-row-key | 指定要使用哪个输入列作为行键 |
–hbase-table | 导入到HBase中的表名 |
Accumulo参数 | |
–accumulo-batch-size | 批处理大小(以字节为单位) |
–accumulo-column-family | 设置导入的目标列族 |
–accumulo-create-table | 如果指定,则创建缺少的累加Accumulo表 |
–accumulo-instance | Accumulo实例名 |
–accumulo-max-latency | 最大写延迟(以毫秒为单位) |
–accumulo-password | Accumulo密码 |
–accumulo-row-key | 指定要使用哪个输入列作为行键 |
–accumulo-table | 导入到Accumulo中的表 |
–accumulo-user | Accumulo用户名 |
–accumulo-visibility | 要应用于导入的所有行的可见性令牌 |
–accumulo-zookeepers | 逗号分隔的zookeeper列表(主机:端口号) |
代码生成的参数 | |
–bindir | 编译对象的输出目录 |
–class-name | 设置生成的类名,覆盖 --package-name. 当与–jar-file组合时,设置input类. |
–escape-mapping-column-names | 禁用列名中转义的特殊字符 |
–input-null-non-string | 输入空非字符串表示 |
–input-null-string | 输入空字符串表示 |
–jar-file | 禁用代码生成;使用指定的jar |
–map-column-java | 覆盖特定列到java类型的映射 |
–null-non-string | 空非字符串表示(数值型默认初始化值:0) |
–null-string | 空字符串表示(即String默认初始化值:"") |
–outdir | 生成代码的输出目录 |
–package-name | 将自动生成的类放在这个包中 |
备注:
通用Hadoop命令行参数:(必须预先加入任何特定于工具的参数)
支持的通用选项包括
-conf <configuration file> 指定应用程序配置文件
-D <property=value> 为给定的属性使用值
-fs <local|namenode:port> 指定一个namenode
-jt <local|resourcemanager:port> 指定一个ResourceManager
-files <comma separated list of files> 指定要复制到mapReduce集群的逗号分隔文件
-libjars <comma separated list of jars> 指定要包含在类路径中的逗号分隔的jar文件.
-archives <comma separated list of archives> 指定要在计算机上取消存档的逗号分隔归档.
一般的命令行语法是bin/hadoop命令[genericOptions] [commandOptions]
至少,您必须指定 --connect 和 --table
mysqldump和其他子程序的参数可以在命令行上的“–”后面提供。
示例一:从mysql到hive
sqoop export \
--connect jdbc:mysql://localhost:3306/db_mysql?useUnicode=true&characterEncoding=utf-8 \
--username root \
--password 123456 \
--table tb_mysql \
--hcatalog-database db_hive \
--hcatalog-table tb_hive \
--fields-terminated-by '\t'
示例二:从sqlserver到hive的增量导入
sqoop import \
--connect 'jdbc:sqlserver://1.2.3.4:5678;database=ABC' \
--username root \
--password 123456 \
--table tb_sqlserver \
--check-column LastModTime \
--delete-target-dir \
--incremental lastmodified \
--last-value '2019-01-30 10:29:07.0'
示例三:从oracle到hive
sqoop import \
--connect 'jdbc:oracle:thin:@<HOST>:1521:app' \
--username root \
--password 123456 \
--hive-import \
--hive-table dw_hd.ods_store \
--query 'select * from HD.STORE where $CONDITIONS and RCVTIME < TO_TIMESTAMP('2017-05-30 00:00:00','yyyy-mm-dd hh24:mi:ss.ff')' \
--split-by FLOWNO \
--direct \
--target-dir '/user/root/store' \
--null-string '\\N' \
--null-non-string '\\N' \
--m 1;
注意:在–query中必须加入where $CONDITIONS
2.批量(或数据库)导入:sqoop import-all-tables
可通过 sqoop import-all-tables --help 命令查看
除了 导入控制参数 和sqoop import不一致外,其它参数均相同,此处省略.
参数 | 说明 |
---|---|
普通参数 | (同上) |
输入解析参数 | (同上) |
输出行格式化参数 | (同上) |
Hive参数 | (同上) |
HCatalog参数 | (同上) |
HCatalog导入特定选项 | (同上) |
HBase参数 | (同上) |
Accumulo参数 | (同上) |
代码生成参数 | (同上) |
导入控制参数 | |
–as-avrodatafile | 将数据导入到avro文件中 |
–as-parquetfile | 将数据导入到parquet文件中 |
–as-sequencefile | 将数据导入到SequenceFile中 |
–as-textfile | 以纯文本形式导入数据(默认) |
–autoreset-to-one-mapper | 如果没有拆分键可用,则将映射器的数量重置为一个映射器 |
–compression-codec | 用于导入的压缩编解码器 |
–direct | 使用直接导入快速路径 |
–direct-split-size | 在直接模式导入时,将输入流按“n”字节分割 |
–exclude-tables | 导入所有表时要排除的表 |
–fetch-size | 当需要更多行时,设置从数据库中获取的行数’n’ |
–inline-lob-limit | 设置内联LOB的最大大小 |
-m,–num-mappers | 使用n个map任务并行导入,默认4 |
–mapreduce-job-name | 为生成的mapreduce作业设置名称 |
–warehouse-dir
| 目标表的HDFS路径 |
-z,–compress | 启用压缩 |
注意:导入的时候一定要保证mysql库里面的所有表都有主键.
示例:(MySQL --> Hive 的整库导入)
#将db_mysql库中user_info除外的其他表全部导入
sqoop import-all-tables \
--connect jdbc:mysql://192.192.192.192:3306/db_mysql \
--username root \
--password 123456 \
--exclude-tables user_info \
--hive-import \
--hive-overwrite \
--hive-database db_hive
3.数据导出:sqoop export
可通过 sqoop export --help 命令查看
参数 | 说明 |
---|---|
普通参数 | (同上) |
输入解析参数 | (同上) |
输出行格式化参数 | (同上) |
代码生成参数 | (同上) |
HCatalog参数 | (同上) |
导出控制参数 | |
–batch | 指示以批处理模式执行的底层语句 |
–call | 使用这个存储过程填充表(每行一个调用) |
–clear-staging-table | 清除中间临时表 |
–columns | 指定导出的列,列名之间用逗号隔开 |
-m,–num-mappers | 使用n个map任务并行导出 |
–staging-table | 中间临时表(创建一个与导入目标表同样的数据结构,保留该表为空在运行数据导入前,所有事务会将结果先存放在该表中,然后最后由该表通过一次事务将结果写入到目标表中) |
–table | 需要导出的表 |
–update-key | 按指定的键列更新记录 |
–update-mode | 指定在数据库中发现具有不匹配键的新行时如何执行更新.该模式有两种updateonly(默认)和allowinsert。 |
–validate | 使用配置的验证器验证副本 |
–validation-failurehandler | ValidationFailureHandler(验证器故障处理器)的完全限定类名 |
–validation-threshold | ValidationThreshold(验证器阀值)的完全限定类名 |
–validator | Validator(验证器)的完全限定类名 |
备注:
updateonly:该模式用于更新Hive表与目标表中数据的不一致,即在不一致时,将Hive中的数据同步给目标表(如MySQL,Oracle等的目标表中),这种不一致是指,一条记录中的不一致,比如Hive表和MySQL中都有一个id=1的记录,但是其中一个字段的取值不同,则该模式会将这种差异抹除。对于“你有我无”的记录则“置之不理”。
allowinsert:该模式用于将Hive中有但目标表中无的记录同步到目标表中,但同时也会同步不一致的记录。这种模式可以包含updateonly模式的操作,这也是为什么没有命名为insertonly的原因吧。
示例一:(MySQL --> Hive 的增量同步)
sqoop export \
--connect 'jdbc:mysql://192.192.192.192:3306/db_mysql?useUnicode=true&characterEncoding=UTF-8' \
--username root \
--password '123456' \
--table tb_mysql \
--hcatalog-database db_hive \
--hcatalog-table tb_hive \
--update-key update_time \
--update-mode allowinsert \
--fields-terminated-by '\t'
示例二:(Hive --> MySQL 的全表导出)
sqoop export \
--connect 'jdbc:mysql://192.192.192.192:3306/db_mysql?useUnicode=true&characterEncoding=utf-8' \
--username root \
--password '123456' \
--table tb_mysql \
--hcatalog-database db_hive \
--hcatalog-table tb_hive
4.导入任务创建:sqoop job
可通过 sqoop job --help 命令查看
参数 | 说明 |
---|---|
作业(Job)管理参数 | |
–create | 创建一个新保存的作业 |
–delete | 删除保存的作业 |
–exec | 运行保存的作业 |
–help | 打印使用说明 |
–list | 查看现有Job列表(已创建的所有job) |
–meta-connect | 为元数据指定JDBC连接字符串 |
–show | 显示指定的作业 |
–verbose | 作业执行时打印更多信息 |
示例:
sqoop job \
--create job_rpd \
-- import \
--connect 'jdbc:sqlserver://1.2.3.4:7965;database=ABC' \
--username root\
--password 123456 \
--table tb_sqlserver \
--hive-import \
--hive-drop-import-delims \
--hive-database db_hive\
--hive-table tb_hive\
--incremental append \
--check-column update_time\
--merge-key update_time\
--last-value '2019-01-01 13:34:52.457' \
-m 1;
**执行Job**: sqoop job --exec job_rpd
**查看Job**: sqoop job --list
**删除Job**: sqoop job --delete job_rpd
注意:
(1).“–”和import中间必需有空格,不然会报错.
(2).每次执行任务都会提示输入密码,可通过在conf/sqoop-site.xml文件中配置sqoop.metastore.client.record.password 的值为true即可取消这一提示,直接使用job中指定的密码 。
(3).通过 sqoop job --show job_rpd 查看任务信息时可查看job任务详情,包括每次执行完后last-value的更新值.
示例二:
sqoop job \
--create job_rrelieve \
-- import \
--connect 'jdbc:sqlserver://1.2.3.4:7965;database=ABC' \
--username root \
--password 123456 \
--query 'select cast(id as int) id,c1,c2,c3,c4,c5 from (select * from tb_sqlserver where id > 19000000 and id < 22000000)tmp WHERE $CONDITIONS' \
--hive-import \
--hive-drop-import-delims \
--hive-database db-hive \
--hive-table tb_hive \
--incremental append \
--check-column id \
--merge-key id \
--last-value '19020010' \
--target-dir 'hdfs://nameservice1/user/hive/warehouse/db_hive.db/tb_hive' \
-m 1;
执行:sqoop job --exec job_rrelieve
查看指定任务:sqoop job --show job_rrelieve
删除:sqoop job --delete job_rrelieve