使用Sqoop从MySQL导入数据到Hive和HBase

来源：互联网发布：淘宝客如何建站编辑：程序博客网时间：2024/06/11 21:13

使用Sqoop从MySQL导入数据到Hive和HBase
- 基础环境
- Sqool和Hive、HBase简介
  - Sqoop
  - Hive
  - HBase
- 测试Sqoop
- 使用Sqoop从MySQL导入数据到Hive
  - 使用复杂SQL
  - 调整Hive数据类型
  - 不断更新
- 使用Sqoop从MySQL导入数据到HBase
  - 使用复杂SQL
  - 不断更新
  - Hive使用HBase数据
- 关于Sqoop2
- 综上所述
- 其他感受

基础环境

sqoop：sqoop-1.4.5+cdh5.3.6+78，hive：hive-0.13.1+cdh5.3.6+397，hbase：hbase-0.98.6+cdh5.3.6+115

Sqool和Hive、HBase简介

Sqoop

Sqoop是一个用来将Hadoop和关系型数据库中的数据相互转移的开源工具，可以将一个关系型数据库（例如： MySQL ,Oracle ,Postgres等）中的数据导进到Hadoop的HDFS中，也可以将HDFS的数据导进到关系型数据库中。

Hive

不想用程序语言开发MapReduce的朋友比如DB们，熟悉SQL的朋友可以使用Hive开离线的进行数据处理与分析工作。
Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供简单的sql查询功能，可以将sql语句转换为MapReduce任务进行运行。
注意Hive现在适合在离线下进行数据的操作，就是说不适合在挂在真实的生产环境中进行实时的在线查询或操作，因为一个字“慢”。
Hive起源于FaceBook，在Hadoop中扮演数据仓库的角色。建立在Hadoop集群的最顶层，对存储在Hadoop群上的数据提供类SQL的接口进行操作。你可以用 HiveQL进行select、join，等等操作。
如果你有数据仓库的需求并且你擅长写SQL并且不想写MapReduce jobs就可以用Hive代替。

Hive的内置数据类型可以分为两大类：(1)、基础数据类型；(2)、复杂数据类型。其中，基础数据类型包括：TINYINT、SMALLINT、INT、BIGINT、BOOLEAN、FLOAT、DOUBLE、STRING、BINARY、TIMESTAMP、DECIMAL、CHAR、VARCHAR、DATE。
下面的表格列出这些基础类型所占的字节以及从什么版本开始支持这些类型。

#测试MySQL连接
[hdfs@node196 bin]$ sqoop list-databases--connect jdbc:mysql://192.168.180.11/angel --username anqi –P
#检验SQL语句
[hdfs@node196 bin]$ sqoopeval--connect jdbc:mysql://192.168.180.11/angel --username anqi --password anqi_mima \
--query"SELECT xi.*, jing.name,wang.latitude,wang.longitude \
FROM xi ,jing, wang \
WHERE xi.id=jing.foreignId AND wang.id=xi.id AND xi.date>='2015-09-01' AND xi.date<='2015-10-01'"

以上Sqoop语句执行过后，可以确认Sqoop运行正常，Sqoop连接MySQL正常。

使用Sqoop从MySQL导入数据到Hive

使用复杂SQL

#从MySQL导入数据到Hive
[hdfs@node196 bin]$ sqoopeval--connect jdbc:mysql://192.168.180.11/angel --username anqi --password anqi_mima \
--query"SELECT xi.*, jing.name,wang.latitude,wang.longitude \
FROM xi ,jing, wang \
WHERE xi.id=jing.foreignId AND wang.id=xi.id AND xi.date>='2015-09-01' AND xi.date<='2015-10-01' \
AND \$CONDITIONS" \
--split-by date --hive-import-m5 \
--target-dir/user/hive/warehouse/anqi_wang \
--hive-table anqi_wang

注意：
由于使用Sqoop从MySQL导入数据到Hive需要指定target-dir，因此导入的是普通表而不能为外部表。

以下简要列举了Sqoop的执行过程:

MySQL(bigint) --> Hive(bigint) 
MySQL(tinyint) --> Hive(tinyint) 
MySQL(int) --> Hive(int) 
MySQL(double) --> Hive(double) 
MySQL(bit) --> Hive(boolean) 
MySQL(varchar) --> Hive(string) 
MySQL(decimal) --> Hive(double) 
MySQL(date/timestamp) --> Hive(string)

可以看出MySQL的decimal类型变成了Hive中的double类型。此时需要在导入时通过--map-column-hive 作出映射关系指定，如下所示：

[hdfs@node196 bin]$ sqoopimport \
--connect jdbc:mysql://192.168.184.12/angel --username anqi --password anqi_mima \
--query"SELECT * FROM xi WHERE date>='2015-09-16' AND date<='2015-10-01' \
AND \$CONDITIONS" \
--split-by date --hive-import-m5 \
--map-column-hive cost="DECIMAL",date="DATE" \
--target-dir/user/hive/warehouse/xi \
--hive-table xi

以上命令可以执行成功，然而Hive列类型设置为DECIMAL时，从Mysql[decimal(12,2)]-->Hive[decimal]会导致导入后小数丢失。

注意：
对于cost="DECIMAL(10,2)"这样指定精确度的映射语句的执行，在Sqoop1.4.5中执行失败。这是Sqoop1.4.5的一个BUG，详情见：https://issues.apache.org/jira/browse/SQOOP-2103，它在1.4.7版本中修复。

不断更新

将上面Sqoop语句执行两次，在执行第二次时会出现错误：

#从MySQL导入数据到HBase 
[hdfs@node196 bin]$ sqoop import \ 
--connect jdbc:mysql://192.168.184.12/angel --username anqi --password anqi_mima \ 
--query "SELECT * FROM xi WHERE 1=1 \ 
    AND \$CONDITIONS" \ 
--hbase-table hxi --hbase-create-table \ 
--hbase-row-key id --split-by date -m 7 \ 
--column-family aitanjupt

上面SQL语句较简单。经检验，更复杂的SQL语句，Sqoop支持得很好，导入正常。

不断更新

以上指定了HBase的Rowkey后，再次执行从MySQL导入数据到HBase的Sqoop语句，基于相同的Rowkey值，HBase内相应的行会进行更新替换。

Hive使用HBase数据

CREATE EXTERNAL TABLE default.angel(
id BIGINT,
username STRING,
password STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.hbase.HBaseSerDe'
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping"=":key, angel:username, angel:password")
TBLPROPERTIES("hbase.table.name"="hxi");

关于Hive使用存储在HBase中的数据，更多详细信息可以查看《使用Hive或Impala执行SQL语句，对存储在HBase中的数据操作》一文。

关于Sqoop2

架构上，Sqoop1使用MapOnly作业进行Hadoop（HDFS/HBase/Hive）同关系数据库进行数据的导入导出，用户使用命令行方式与之交互，数据传输和数据格式紧密耦合；易用性欠佳，Connector数据格式支持有限，安全性不好，对Connector的限制过死。Sqoop2则建立了集中化的服务，负责管理完整的MapReduce作业，提供多种用户交互方式（CLI/WebUI/RESTAPI），具有权限管理机制，具有规范化的Connector，使得它更加易用，更加安全，更加专注。

综上所述

使用Sqoop从MySQL导入数据到HBase要比导入到Hive方便，使用Hive对HBase数据操作时，也无decimal精度相关BUG，并且可以很好的支持更新。因此建议使用Sqoop从MySQL导入数据到HBase，而非直接Hive。

经过测试，使用Sqoop从MySQL导入数据到HBase，100万条需花费7~12分钟。impala对于hbase的查询效率也没有对hdfs效率高。

阅读全文

0 0