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 目录

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

示例程序：

$ 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

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

2.3 创建 hive 表

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

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

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 增量导入

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 生成的

3. 参考文章

• Sqoop中文手册