`

Sqoop导入关系数据库到Hive

 
阅读更多

文章来自:http://blog.javachen.com/2014/08/04/import-data-to-hive-with-sqoop/

 

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里的存放目录

3. 参考文章

分享到:
评论

相关推荐

    Sqoop导Oracle数据到Hive

    ### Sqoop导入Oracle数据到Hive的详细步骤与解析 #### 一、概述 在大数据处理领域中,经常需要将关系型数据库中的数据导入到Hadoop的HDFS中,以便进一步进行数据分析或处理。其中,Sqoop作为一种强大的工具,能够...

    sqoop导入数据到hive中,数据不一致

    4. 分区问题:如果Hive表是分区表,而Sqoop导入时未正确处理分区,可能会导致数据分布不均或者丢失部分数据。确保在导入时指定了正确的分区字段和值。 5. 编码问题:数据源的字符编码与Hive或Hadoop集群的默认编码...

    2、sqoop导入(RMDB-mysql、sybase到HDFS-hive)

    在大数据处理中,Sqoop 提供了方便的数据导入和导出功能,它能够将结构化的数据从传统数据库迁移到 Hadoop 生态系统中的组件,如 Hive。 在本主题中,我们将深入探讨 Sqoop 的导入功能,特别是如何将数据从 MySQL ...

    数据同步Sqoop用法之mysql与Hive导入导出.docx

    Sqoop 提供了灵活的数据导入方式,可以将 MySQL 数据库中的数据导入到 Hive 表中。例如,以下命令可以将 MySQL 数据库中的 people_access_log 表导入到 Hive 表 web.people_access_log 中: sqoop import \ --...

    bdp2hive:生成hive分区表、sqoop导入数据至hive

    生成sqoop脚本组件主要通过传递的数据库信息查询数据库获取表字段与数据集之间的对照关系SQL语句,通过模板拼接成sqoop脚本,上传服务器执行;hive分区表生成组件主要是通过获取数据集及数据集item的code,数据集...

    sqoop导入数据到hdfs路径

    ### Sqoop导入数据到HDFS路径详解 #### 一、Sqoop简介 Sqoop是一款开源工具,主要用于在Hadoop和关系型数据库之间高效地传输大量数据。它利用MapReduce作业来提取、转换和加载(ETL)数据。通过Sqoop,用户可以从...

    hadoop软件1,和hive_3,sqoop_2搭配使用

    例如,我们可以先使用Sqoop将关系数据库中的数据导入到Hadoop的HDFS中,然后通过Hive对这些数据进行清洗、转换和分析。分析完成后,如果需要将结果数据返回到数据库,同样可以利用Sqoop完成导出操作。这种组合应用...

    hadoop_install-sqoop数据导入

    Sqoop导入数据的过程可以概括为几个主要步骤:连接关系数据库、定义数据导入的参数、执行导入命令。首先,Sqoop需要连接到关系数据库,这通常通过指定数据库的JDBC驱动和连接参数来实现。其次,需要定义哪些数据需要...

    23-Sqoop数据导入导出1

    ### Sqoop导入原理 1. **JDBC检查**:在开始导入前,Sqoop通过JDBC连接数据库,获取表结构和列信息,如数据类型,将其映射为Java类型。 2. **代码生成器**: Sqoop根据表信息生成Java类,用于存储从数据库抽取的...

    sqoop 从 hive 导到mysql遇到的问题.docx

    Sqoop 可以用于将数据从关系数据库管理系统导入到 Hive,或者从 Hive 导出数据到关系数据库管理系统。 Sqoop 是一个功能强大且易于使用的数据传输工具,广泛应用于数据仓库、数据挖掘、商业智能、Big Data 等领域。...

    基于Sqoop+Hive+Spark+MySQL+AirFlow+Grafana的工业大数据离线数仓项目

    在大数据项目中,AirFlow能确保数据从收集到分析的整个流程自动化、有条不紊地运行,例如设置定时任务从MySQL抽取数据,用Sqoop导入HDFS,然后启动Hive和Spark作业进行分析。 最后,Grafana是一个强大的可视化工具...

    Sqoop安装与使用

    在将 mysql 数据库中的表导入到 HDFS 中时,需要启动 hadoop,登录 mysql 数据库,查看 hive 数据库中有哪些表,然后使用 sqoop 命令将 hive 数据库中的表导入到 HDFS 中。最后,查看是否导入 HDFS 中。 Sqoop ...

    Sqoop数据库数据导入导出教程PDF

    把关系型数据库的数据导入到Hadoop系统(如HDFS,Hbase和Hive中) 把数据从Hadoop系统里面抽取并导出到关系型数据库中 利用MapReduce加快数据处理速度 ·批处理方式进行数据传输 Sqoop 优势 ·¬高效、可控地利用资源 ...

    Hadoop-Sqoop-Oracle:使用Sqoop在Oracle数据库和HDFS之间进行导入和导出

    Hadoop-Sqoop-Oracle ...当您要将关系数据库中存储的数据导入到HDFS Hadoop引擎时使用。 可以将来自外部系统的数据导入HDFS并以Hive表和HBase表等各种Hadoop文件格式保存。 Sqoop v2仅支持保存到HDFS 在所有与JDBC

    HIVE&&SQOOP

    例如,企业可能需要定期从关系数据库中抽取数据,通过Sqoop导入到Hadoop集群,然后使用Hive进行数据清洗、转换和分析,最后将结果写回数据库或生成报告。 在实际应用中,我们还需要考虑性能优化。对于Hive,这可能...

    yinian_hive_increase_sqoop:sqoop从mysql同步数据到hive

    【标题】"yinian_hive_increase_sqoop:sqoop从mysql同步数据到hive" 描述了一种使用Apache Sqoop工具将数据从MySQL数据库高效地导入到Hadoop的Hive数据仓库的过程。这个过程在大数据处理中至关重要,因为它允许用户...

    SearchEngine-sqoop数据导入

    “import-all-tables”命令用于导入数据库中所有表的数据;而“create-hive-table”命令则可以在导入过程中创建Hive表。用户可以通过指定不同的参数来控制导入行为,如设置分割列、批处理大小和导入查询等。 此外,...

    一个简单的Hive项目,使用了Sqoop、Hadoop、Hive、MySQL,对电商数据进行分析-BI_project.zip

    在这个项目中,MySQL可能扮演数据源的角色,提供原始的电商交易数据,这些数据随后通过Sqoop导入到Hadoop集群进行分析。 项目文件"BI_project-master"可能包含了项目源代码、配置文件、README文档等资源。源代码...

    hive2.1.1_mysql_sqoop.zip

    在Sqoop中,这个JAR文件是必要的,因为它提供了一个接口,使得Sqoop能够连接到MySQL数据库,执行数据的导入导出操作。 在实际应用中,首先,你需要将`mysql-connector-java-5.1.47-bin.jar`添加到Hadoop和Sqoop的类...

    Sqoop数据采集工具简介、安装、使用学习笔记(配合Hive和Hbase)

    最初的设计方案是通过 Sqoop 将数据从 PostgreSQL 导入到 Kafka,再通过存储程序将 Kafka 的数据保存至 Hive 或 HBase 中。然而,在实施过程中遇到了 Sqoop 版本兼容性问题: - **Sqoop1**:适用于命令行模式执行。...

Global site tag (gtag.js) - Google Analytics