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.eval

可以快速地使用SQL语句对关系数据库进行操作，这可以使得在使用import这种工具进行数据导入的时候，可以预先了解相关的SQL语句是否正确，并能将结果显示在控制台。
查询示例：
sqoop eval --connect jdbc:mysql://localhost:3306/hive --username root --password 123456 --query "SELECT * FROM tbls LIMIT 10"
数据插入示例：
sqoop eval --connect jdbc:mysql://localhost:3306/hive --username root --password 123456 --e "INSERT INTO TBLS2VALUES(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 myjob  — import --connectjdbc:mysql://localhost:3306/test --table person
参数说明
--create <job-id>：生成一个jobeg：sqoop job –-create myjob  — import --connectjdbc:mysql://localhost:3306/test --tableperson
--delete <job-id>：删除一个jobeg:sqoop job –delete myjob
--exec <job-id>：执行一个jobeg:sqoop 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中的数据库连接用户名
–v-erbose：在控制台打印出详细信息
--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","[e-mail]"
--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,[e-mail],1,2013-08-07 16:00:48.0,"hehe",测试
上面的各字段是以逗号分隔的，那么在解析时，必须要以逗号来解析出各字段值，如：
–input-fields-terminated-by ',’
参数说明
--input-enclosed-by <char>对字段值前后有指定的字符，比如双引号的值进行解析：–input-enclosed-by '\"'，数据例子："3","jimsss","[e-mail]"
--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的描述

python调度sqoop示例：

#! /usr/bin/env python# coding:utf-8import os,sysfrom datetime import datetimeMyUSER="XX"                   # 用户名 xxxxxMyPASS="xx"                     # 密码   ******MyHOST="xx"          # 主机MyPORT="3306"                           # 端口   3306MyDB="xx"                             # 数据库名VERSION="50621"                         # 版本(生产环境或者测试环境)：生产环境是prdCONNECTION_URL="\"jdbc:mysql://%s:%s/%s?tinyInt1isBit=false&zeroDateTimeBehavior=convertToNull\"" %(MyHOST,MyPORT,MyDB)table = sys.argv[1]# 第一个参数表名：源表与结果表一样try:    incday = sys.argv[2] # 第二个参数为增量值 except IndexError:    incday = (datetime.now()+datetime.timedelta(-1)).strftime('%Y%m%d') #默认昨天try:    inccol = sys.argv[3]#第三个参数为增量字段except IndexError:    inccol = "create_tm" #默认create_tm    sql = r"SELECT t.* FROM {0} t WHERE DATE_FORMAT(t.{1},'%Y%m%d') = '{2}' AND \$CONDITIONS".format(table,inccol,incday)cmd = r'''sqoop import --connect {0} --username {1} --password {2} \--query "{3}" \--split-by ID --hive-import -m 1 \--target-dir /result/asura_oia_core/ods/{4}/inc_day={5} \--hive-table asura_oia_core.{6} \--hive-partition-key inc_day \--hive-partition-value  {7} \--hive-overwrite \--delete-target-dir --null-string '\\N'   --null-non-string '\\N''''.format(CONNECTION_URL,MyUSER,MyPASS,sql,table,incday,table,incday)#print(cmd)os.system(cmd)