使用Sqoop从MySQL导入数据到Hive和HBase 及近期感悟

云计算 waitig 656℃ 百度已收录 0评论

基础环境

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。 

下面的表格列出这些基础类型所占的字节以及从什么版本开始支持这些类型。

  1. #测试MySQL连接
  2. [hdfs@node196
    bin
    ]$ sqoop list-databases--connect
    jdbc
    :mysql://192.168.180.11/angel
    --username anqi –P
  3. #检验SQL语句
  4. [hdfs@node196
    bin
    ]$ sqoopeval--connect
    jdbc
    :mysql://192.168.180.11/angel
    --username anqi --password anqi_mima \
  5. --query"SELECT
    xi.*, jing.name,wang.latitude,wang.longitude \
  6. FROM
    xi ,jing, wang \
  7. 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

<pre linenums="" prettyprinted"="" data-anchor-id="0l4v" style="margin-top: 0px; margin-bottom: 20px; padding: 9.5px; white-space: pre-wrap; word-wrap: break-word; font-size: 13px; font-family: Monaco, Menlo, Consolas, "Courier New", monospace; color: rgb(51,
51, 51); border-radius: 4px; line-height: 20px; word-break: break-all; border: 0px solid rgba(0, 0, 0, 0.14902); box-shadow: rgba(255, 255, 255, 0.0980392) 0px 1px 2px inset, rgba(102, 128, 153, 0.0470588) 45px 0px 0px inset, rgba(102, 128, 153, 0.0470588)
0px 1px 0px; background: none 0px 0px repeat scroll rgba(102, 128, 153, 0.0470588);">

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

注意: 

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

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

  1. MySQL(bigint) --> Hive(bigint)
  2. MySQL(tinyint) --> Hive(tinyint)
  3. MySQL(int) --> Hive(int)
  4. MySQL(double) --> Hive(double)
  5. MySQL(bit) --> Hive(boolean)
  6. MySQL(varchar) --> Hive(string)
  7. MySQL(decimal) --> Hive(double)
  8. MySQL(date/timestamp) --> Hive(string)

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

<pre linenums="" prettyprinted"="" data-anchor-id="n5s6" style="margin-top: 0px; margin-bottom: 20px; padding: 9.5px; white-space: pre-wrap; word-wrap: break-word; font-size: 13px; font-family: Monaco, Menlo, Consolas, "Courier New", monospace; color: rgb(51,
51, 51); border-radius: 4px; line-height: 20px; word-break: break-all; border: 0px solid rgba(0, 0, 0, 0.14902); box-shadow: rgba(255, 255, 255, 0.0980392) 0px 1px 2px inset, rgba(102, 128, 153, 0.0470588) 45px 0px 0px inset, rgba(102, 128, 153, 0.0470588)
0px 1px 0px; background: none 0px 0px repeat scroll rgba(102, 128, 153, 0.0470588);">

  1. [hdfs@node196
    bin
    ]$ sqoopimport
    \
  2. --connect
    jdbc
    :mysql://192.168.184.12/angel
    --username anqi --password anqi_mima \
  3. --query"SELECT
    * FROM xi WHERE date>='2015-09-16' AND date<='2015-10-01' \
  4. AND
    \$CONDITIONS"
    \
  5. --split-by
    date
    --hive-import-m5
    \
  6. --map-column-hive
    cost
    ="DECIMAL",date="DATE"
    \
  7. --target-dir/user/hive/warehouse/xi
    \
  8. --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语句执行两次,在执行第二次时会出现错误:

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

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

不断更新

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

Hive使用HBase数据

<pre linenums="" prettyprinted"="" data-anchor-id="9rqx" style="margin-top: 0px; margin-bottom: 20px; padding: 9.5px; white-space: pre-wrap; word-wrap: break-word; font-size: 13px; font-family: Monaco, Menlo, Consolas, "Courier New", monospace; color: rgb(51,
51, 51); border-radius: 4px; line-height: 20px; word-break: break-all; border: 0px solid rgba(0, 0, 0, 0.14902); box-shadow: rgba(255, 255, 255, 0.0980392) 0px 1px 2px inset, rgba(102, 128, 153, 0.0470588) 45px 0px 0px inset, rgba(102, 128, 153, 0.0470588)
0px 1px 0px; background: none 0px 0px repeat scroll rgba(102, 128, 153, 0.0470588);">

  1. CREATE
    EXTERNAL TABLE
    default.angel(
  2. id
    BIGINT
    ,
  3. username
    STRING
    ,
  4. password
    STRING
    )
  5. ROW
    FORMAT SERDE
    'org.apache.hadoop.hive.hbase.HBaseSerDe'
  6. STORED
    BY
    'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
  7. WITH
    SERDEPROPERTIES
    ("hbase.columns.mapping"=":key,
    angel:username, angel:password"
    )
  8. 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效率高。

本文会不定期更新,若有不对之处请前往原文出处:http://www.cnblogs.com/wgp13x/ 进行指正。

其他感受

在大数据领域浸染的多了,才知道当前社会上的公司对于大数据方向的技术都是什么样的理解。有的公司数据量足够大,但数据并无价值或者价值不够大,我们称之为LU(large but unavailable)类型公司;有的公司数据量不多,但都价值连城,我们称之为SV(small but valuable)类型公司;有的公司数据量很大,数据也都很有价值,我们称之为LV(large and valuable)类型公司。

LU公司市场上有很多,造成数据无价值的因素可能是有数据本身的原因,也有产品的原因,他们也经常想要在当前环境下试下大数据的水,这便要细心甄别。SV公司也有不少,对于他们而言,好的数据分析师比大数据平台更重要,欲要试水大数据,还是要对数据量有清晰的预估。LV公司是最需要搭建大数据平台的,但又往往他们没有技术与欲望去做这件事,这也比较可恨。

简而言之,想做的不一定需要,不做的不一定不需要。


本文由【waitig】发表在等英博客
本文固定链接:使用Sqoop从MySQL导入数据到Hive和HBase 及近期感悟
欢迎关注本站官方公众号,每日都有干货分享!
等英博客官方公众号
点赞 (0)分享 (0)