Sqoop导入关系数据库到Hive

Sqoop 是 apache 下用于 RDBMS 和 HDFS 互相导数据的工具。本文以 mysql 数据库为例，实现关系数据库导入到 hdfs 和 hive。

1. 安装 Sqoop

使用 rpm 安装即可。

yum install sqoop sqoop-metastore -y

安装完之后需要下载 mysql jar 包到 sqoop 的 lib 目录。

这里使用 hive 的 metastore 的 mysql 数据库作为关系数据库，以 TBLS 表为例，该表结构和数据如下：

mysql> select * from TBLS limit 3;
+------+-----------+-----+----------------+-----+--------+------+---------+----------------+------------------+-------------------+
|TBL_ID|CREATE_TIME|DB_ID|LAST_ACCESS_TIME|OWNER|RETENTI | SD_ID| TBL_NAME| TBL_TYPE       |VIEW_EXPANDED_TEXT| VIEW_ORIGINAL_TEXT|
+------+-----------+-----+----------------+-----+--------+------+---------+----------------+------------------+-------------------+
|    34|1406784308 |    8|               0|root |       0|    45| test1   | EXTERNAL_TABLE | NULL             | NULL              |
|    40|1406797005 |    9|               0|root |       0|    52| test2   | EXTERNAL_TABLE | NULL             | NULL              |
|    42|1407122307 |    7|               0|root |       0|    59| test3   | EXTERNAL_TABLE | NULL             | NULL              |
+------+-----------+-----+----------------+-----+--------+------+---------+----------------+------------------+-------------------+

2. 使用

2.1 命令说明

查看 sqoop 命令说明：

$ sqoop help
usage: sqoop COMMAND [ARGS]

Available commands:
  codegen            Generate code to interact with database records
  create-hive-table  Import a table definition into Hive
  eval               Evaluate a SQL statement and display the results
  export             Export an HDFS directory to a database table
  help               List available commands
  import             Import a table from a database to HDFS
  import-all-tables  Import tables from a database to HDFS
  list-databases     List available databases on a server
  list-tables        List available tables in a database
  version            Display version information

See 'sqoop help COMMAND' for information on a specific command.

你也可以查看某一个命令的使用说明：

$ sqoop import --help
$ sqoop help import

你也可以使用别名来代替 sqoop (toolname)：

$ sqoop-import

sqoop import 的一个示例如下：

$ sqoop import --connect jdbc:mysql://192.168.56.121:3306/metastore --username hiveuser --password redhat --table TBLS

你还可以使用 --options-file 来传入一个文件，使用这种方式可以重用一些配置参数：

$ sqoop --options-file /users/homer/work/import.txt --table TEST

/users/homer/work/import.txt 文件内容如下：

import
--connect
jdbc:mysql://192.168.56.121:3306/metastore
--username
hiveuser
--password
redhat

2.2 导入数据到 hdfs

使用 sqoop-import 命令可以从关系数据库导入数据到 hdfs。

$ sqoop import --connect jdbc:mysql://192.168.56.121:3306/metastore --username hiveuser --password redhat --table TBLS --target-dir /user/hive/result

注意：

  • mysql jdbc url 请使用 ip 地址
  • 如果重复执行，会提示目录已经存在，可以手动删除
  • 如果不指定 --target-dir，导入到用户家目录下的 TBLS 目录

你还可以指定其他的参数：

参数 说明    
  --append 将数据追加到hdfs中已经存在的dataset中。使用该参数，sqoop将把数据先导入到一个临时目录中，然后重新给文件命名到一个正式的目录中，以避免和该目录中已存在的文件重名。  
  --as-avrodatafile 将数据导入到一个Avro数据文件中  
  --as-sequencefile 将数据导入到一个sequence文件中  
  --as-textfile 将数据导入到一个普通文本文件中，生成该文本文件后，可以在hive中通过sql语句查询出结果。  
  --boundary-query <statement> 边界查询，也就是在导入前先通过SQL查询得到一个结果集，然后导入的数据就是该结果集内的数据，格式如：--boundary-query 'select id,no from t where id = 3'，表示导入的数据为id=3的记录，或者 select min(<split-by>), max(<split-by>) from <table name>，注意查询的字段中不能有数据类型为字符串的字段，否则会报错  
  --columns<col,col> 指定要导入的字段值，格式如：--columns id,username  
  --direct 直接导入模式，使用的是关系数据库自带的导入导出工具。官网上是说这样导入会更快  
  --direct-split-size 在使用上面direct直接导入的基础上，对导入的流按字节数分块，特别是使用直连模式从PostgreSQL导入数据的时候，可以将一个到达设定大小的文件分为几个独立的文件。  
  --inline-lob-limit 设定大对象数据类型的最大值  
  -m,--num-mappers 启动N个map来并行导入数据，默认是4个，最好不要将数字设置为高于集群的节点数  
  --query，-e <sql> 从查询结果中导入数据，该参数使用时必须指定–target-dir、–hive-table，在查询语句中一定要有where条件且在where条件中需要包含 \$CONDITIONS，示例：--query 'select * from t where \$CONDITIONS ' --target-dir /tmp/t –hive-table t  
  --split-by <column> 表的列名，用来切分工作单元，一般后面跟主键ID  
  --table <table-name> 关系数据库表名，数据从该表中获取  
  --delete-target-dir 删除目标目录  
  --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将被使用  

示例程序：

$ sqoop import --connect jdbc:mysql://192.168.56.121:3306/metastore --username hiveuser --password redhat --table TBLS --columns "tbl_id,create_time" --where "tbl_id > 1" --target-dir /user/hive/result

使用 sql 语句

参照上表，使用 sql 语句查询时，需要指定 $CONDITIONS

$ sqoop import --connect jdbc:mysql://192.168.56.121:3306/metastore --username hiveuser --password redhat --query 'SELECT * from TBLS where \$CONDITIONS ' --split-by tbl_id -m 4 --target-dir /user/hive/result

上面命令通过 -m 1 控制并发的 map 数。

使用 direct 模式：

$ sqoop import --connect jdbc:mysql://192.168.56.121:3306/metastore --username hiveuser --password redhat --table TBLS --delete-target-dir --direct --default-character-set UTF-8 --target-dir /user/hive/result

指定文件输出格式：

$ sqoop import --connect jdbc:mysql://192.168.56.121:3306/metastore --username hiveuser --password redhat --table TBLS --fields-terminated-by "\t" --lines-terminated-by "\n" --delete-target-dir  --target-dir /user/hive/result

这时候查看 hdfs 中数据(观察分隔符是否为制表符)：

$ hadoop fs -ls result
Found 5 items
-rw-r--r--   3 root hadoop          0 2014-08-04 16:07 result/_SUCCESS
-rw-r--r--   3 root hadoop         69 2014-08-04 16:07 result/part-m-00000
-rw-r--r--   3 root hadoop          0 2014-08-04 16:07 result/part-m-00001
-rw-r--r--   3 root hadoop        142 2014-08-04 16:07 result/part-m-00002
-rw-r--r--   3 root hadoop         62 2014-08-04 16:07 result/part-m-00003

$ hadoop fs -cat result/part-m-00000
34	1406784308	8	0	root	0	45	test1	EXTERNAL_TABLE	null	null	null

$ hadoop fs -cat result/part-m-00002
40	1406797005	9	0	root	0	52	test2	EXTERNAL_TABLE	null	null	null
42	1407122307	7	0	root	0	59	test3	EXTERNAL_TABLE	null	null	null

指定空字符串：

$ sqoop import --connect jdbc:mysql://192.168.56.121:3306/metastore --username hiveuser --password redhat --table TBLS --fields-terminated-by "\t" --lines-terminated-by "\n" --delete-target-dir --null-string '\\N' --null-non-string '\\N' --target-dir /user/hive/result

如果需要指定压缩：

$ sqoop import --connect jdbc:mysql://192.168.56.121:3306/metastore --username hiveuser --password redhat --table TBLS --fields-terminated-by "\t" --lines-terminated-by "\n" --delete-target-dir --null-string '\\N' --null-non-string '\\N' --compression-codec "com.hadoop.compression.lzo.LzopCodec" --target-dir /user/hive/result

附：可选的文件参数如下表。

参数 说明
--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只是给带有双引号或单引号的字段值加上指定的符号，故叫可选的

2.3 创建 hive 表

生成与关系数据库表的表结构对应的HIVE表：

$ sqoop create-hive-table --connect jdbc:mysql://192.168.56.121:3306/metastore --username hiveuser --password redhat --table TBLS
参数 说明
--hive-home <dir> Hive的安装目录，可以通过该参数覆盖掉默认的hive目录
--hive-overwrite 覆盖掉在hive表中已经存在的数据
--create-hive-table 默认是false，如果目标表已经存在了，那么创建任务会失败
--hive-table 后面接要创建的hive表
--table 指定关系数据库表名

2.4 导入数据到 hive

执行下面的命令会将 mysql 中的数据导入到 hdfs 中，然后创建一个hive 表，最后再将 hdfs 上的文件移动到 hive 表的目录下面。

$ sqoop import --connect jdbc:mysql://192.168.56.121:3306/metastore --username hiveuser --password redhat --table TBLS --fields-terminated-by "\t" --lines-terminated-by "\n" --hive-import --hive-overwrite --create-hive-table --hive-table dw_srclog.TBLS --delete-target-dir

说明：

  • 可以在 hive 的表名前面指定数据库名称
  • 可以通过 --create-hive-table 创建表，如果表已经存在则会执行失败

接下来可以查看 hive 中的数据：

$ hive -e 'select * from dw_srclog.tbls'
34	1406784308	8	0	root	0	45	test1	EXTERNAL_TABLE	null	null	NULL
40	1406797005	9	0	root	0	52	test2	EXTERNAL_TABLE	null	null	NULL
42	1407122307	7	0	root	0	59	test3	EXTERNAL_TABLE	null	null	NULL

直接查看文件内容：

$ hadoop fs -cat /user/hive/warehouse/dw_srclog.db/tbls/part-m-00000
34140678430880root045go_goodsEXTERNAL_TABLEnullnullnull
40140679700590root052merchantEXTERNAL_TABLEnullnullnull

从上面可见，数据导入到 hive 中之后分隔符为默认分隔符，参考上文你可以通过设置参数指定其他的分隔符。

另外，Sqoop 默认地导入空值（NULL）为 null 字符串，而 hive 使用 \N 去标识空值（NULL），故你在 import 或者 export 时候，需要做相应的处理。在 import 时，使用如下命令：

$ sqoop import  ... --null-string '\\N' --null-non-string '\\N'

在导出时，使用下面命令：

$ sqoop import  ... --input-null-string '' --input-null-non-string ''

一个完整的例子如下：

$ sqoop import --connect jdbc:mysql://192.168.56.121:3306/metastore --username hiveuser --password redhat --table TBLS --fields-terminated-by "\t" --lines-terminated-by "\n" --hive-import --hive-overwrite --create-hive-table --hive-table dw_srclog.TBLS --null-string '\\N' --null-non-string '\\N' --compression-codec "com.hadoop.compression.lzo.LzopCodec"

2.5 增量导入

参数 说明
--check-column (col) 用来作为判断的列名，如id
--incremental (mode) append：追加，比如对大于last-value指定的值之后的记录进行追加导入。lastmodified：最后的修改时间，追加last-value指定的日期之后的记录
--last-value (value) 指定自从上次导入后列的最大值（大于该指定的值），也可以自己设定某一值

2.6 合并 hdfs 文件

将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里的存放目录
时间： 2024-09-18 17:46:38

Sqoop导入关系数据库到Hive的相关文章

sqoop从MySQL导入数据到hive报错 class not found

问题描述 sqoop从MySQL导入数据到hive报错 class not found 解决方案 sqoop 导入oracle 报错 Imported Failed : Attempted to generate class with no columns问题解决办法mysql启动报错 MySQL manager or server PID file could not be found! [FAILED]启动HIVE 服务报错 HWI WAR file not found 解决方案二: 您好,

导入-通过sqoop, load数据到hive，sqoop如何知道hive的warehouse

问题描述 通过sqoop, load数据到hive,sqoop如何知道hive的warehouse 我创建了自己的hive-site.xml文件,在里边指定了hive的warehouse,现在的问题是:我通过sqoop,把数据从sqlserv导入到hive的时候,我如何让sqoop知道我用的是我自己的hive-site.xml文件,从而用自己配置的warehouse.我们不希望用默认的hive warehouse. 各位大神帮帮忙啊. 解决方案 这个问题问得好,不太好回答

求助，sqoop从Mysql导入数据到Hive,为何有两行数据换行了？

问题描述 我用的导入语句如下:sqoopimport--connectjdbc:mysql://localhost:3306/report--usernameroot--passwordroot--tableGOO_GOODS--hive-import-m1--hive-overwrite--hive-tablereport.GOO_GOODS--fields-terminated-by','数据也导入到hive表中了,可是其实有两行数据被换行了,如下:(小孔空心(出租车专用))14031016

Sqoop导入到hdfs

   1.注意win下直接复制进linux 改一下--等 sqoop-list-databases --connect jdbc:mysql://122.206.79.212:3306/ --username root -P  先看一下有什么数据库,发现有些数据库,能查询到的数据库才能导入,很奇怪.   2.导入到hdfs sqoop import --connect jdbc:mysql://122.206.79.212:3306/dating --username root --passwo

mysql数据与Hadoop之间导入导出之Sqoop实例

前面介绍了sqoop1.4.6的 如何将mysql数据导入Hadoop之Sqoop安装,下面就介绍两者间的数据互通的简单使用命令. 显示mysql数据库的信息,一般sqoop安装测试用 sqoop list-databases --connect jdbc:mysql://192.168.2.101:3306/ --username root --password root 显示数据库里所有表: sqoop list-tables --connectjdbc:mysql://192.168.2.

sqoop数据迁移（基于Hadoop和关系数据库服务器之间传送数据）

1:sqoop的概述: (1):sqoop是apache旗下一款"Hadoop和关系数据库服务器之间传送数据"的工具.(2):导入数据:MySQL,Oracle导入数据到Hadoop的HDFS.HIVE.HBASE等数据存储系统:(3):导出数据:从Hadoop的文件系统中导出数据到关系数据库 (4):工作机制: 将导入或导出命令翻译成mapreduce程序来实现: 在翻译出的mapreduce中主要是对inputformat和outputformat进行定制: (5):Sqoop的原

《Hadoop实战手册》一1.4 使用Sqoop从MySQL数据库导入数据到HDFS

1.4 使用Sqoop从MySQL数据库导入数据到HDFS Sqoop是Apache基金下的一个项目,是庞大Hadoop生态圈中的一部分.在很多方面Sqoop和distcp很相似(见1.3节).这两个工具都是构建在MapReduce之上的,利用了MapReduce的并行性和容错性.与集群间的数据复制不同,Sqoop设计通过JDBC驱动连接实现Hadoop集群与关系数据库之间的数据复制. 它的功能非常广泛,本节将以网络日志条目为例展示如何使用Sqoop从MySQL数据库导入数据到HDFS. 准备工

sqoop hive oracle-sqoop 从oracle导数据到hive中报错

问题描述 sqoop 从oracle导数据到hive中报错 往hive中导入表,报如下错误,请大家帮忙 [root@amorsay3 bin]# ./sqoop import --hive-import --connect jdbc:oracle:thin:@192.168.13.168:1521:orcl --username HADOOPLEARN --password zhao --table EMP -m 1 --hive-table emp1 Warning: /usr/local/h

[Hadoop]Sqoop 1.4.2中文文档（一）之数据导入

一.Sqoop Help $ sqoop help usage: sqoop COMMAND [ARGS] Available commands: codegen Generate code to interact with database records create-hive-table Import a table definition into Hive eval Evaluate a SQL statement and display the results export Expor