hadoop中的关系型数据库

文档时间 各软件版本 Hadoop Hbase Sqoop 2.2.0 0.98.1 1.4.4 2014/6/30

前言 .................................................................................................................. 4 一. Hbase........................................................................................................... 5

1. Hbase的目的 ........................................................................................... 5 2. Hbase的表设计 ........................................................................................ 5

1> 行健（Row Key） ............................................................................. 5 2> 列族（Column-Family） .................................................................... 6 3> 存储单元（Cell） .............................................................................. 6 4> 时间戳（version） ............................................................................ 6 3. Hbase的表查询 ........................................................................................ 6

1> 单行查询（get） .............................................................................. 6 2> 多行查询（scan） ............................................................................ 7 4. Hbase的效率 ........................................................................................... 7 5. Hbase使用示例 ........................................................................................ 8

1> 需求分析 ......................................................................................... 8 2> 数据库设计 ...................................................................................... 8 3> 数据样例 ......................................................................................... 9 4> 查询代码样例 ................................................................................. 10

二. 以RDBMS为数据源 ..................................................................................... 11

1. 实现原理 ............................................................................................... 11 2. 编程接口 ............................................................................................... 11 3. 具体实现 ............................................................................................... 12

1> 建立连接 ....................................................................................... 12 2> 定义表对应的bean ......................................................................... 12 3> 读取数据 ....................................................................................... 13

4> 写入数据 ....................................................................................... 14 5> 完整代码示例 ................................................................................. 14 4. 注意事项 ............................................................................................... 15 三. Sqoop ........................................................................................................ 17

1. Sqoop的作用 ......................................................................................... 17 2. 数据库导入HDFS ................................................................................... 17

1> 命令行方式 .................................................................................... 17 2> 代码方式 ....................................................................................... 18 3. HDFS导入数据库 ................................................................................... 18

1> 命令行方式 .................................................................................... 18 2> 代码方式 ....................................................................................... 19 4. 数据库导入Hbase .................................................................................. 19

1> 命令行方式 .................................................................................... 19 2> 代码方式 ....................................................................................... 20 5. Hbase导入数据库 .................................................................................. 20

前言

MapReduce技术推出后，曾遭到关系数据库研究者的挑剔和批评，认为MapReduce不具备有类似于关系数据库中的结构化数据存储和处理能力。为此，Google和MapReduce社区进行了很多努力。

一方面，他们设计了类似于关系数据中结构化数据表的技术（Google的BigTable，Hadoop的HBase）提供一些粗粒度的结构化数据存储和处理能力。

另一方面，为了增强与关系数据库的集成能力，Hadoop MapReduce提供了相应的访问关系数据库库的编程接口。

另外，我们还可以通过Sqoop，在关系型数据库和HDFS或Hbase间转换数据。

一. Hbase

1. Hbase的目的

Hbase的设计类似于关系数据中结构化数据表的技术，它介于nosql和RDBMS之间，提供一些粗粒度的结构化数据存储和处理能力，主要用来存储非结构化和半结构化的松散数据。

2. Hbase的表设计

HBase以表的形式存储数据，表有行和列组成，列划分为若干个列族(column-family)。Hbase中的列族类似于RDBMS中的列（column），必须在数据插入前，即设计表结构时设计好。一行的每个列族中，均可容纳多个Cell，每一个Cell，都是一个K-V对。

一个Hbase表结构如下图所示： Table1 行 Row row key1 列族 column-family1 column-family2 Cell1(k1-v1)，Cell2(k2-v2) Cell3(k3-v3)，Cell4(k4-v4) … … … … 其中，Cell是一个虚拟的概念，我们在使用中，通过row key+ column-family+key，在确定获取唯一的值（value）。

1> 行健（Row Key）

row key是用来检索记录的主键，用于访问hbase table中的行，row key是hbase中唯一可以用来创建索引的地方，row key可以是任意字符串。

2> 列族（Column-Family）

hbase表中的每个列，都归属与某个列族。列族是表的chema的一部分(而列不是)，必须在使用表之前定义。列名都以列族作为前缀。

3> 存储单元（Cell）

Cell是一个逻辑上的存储单元，由{row key, column, version} 唯一确定。cell中的数据是没有类型的，是一个K-V对，全部是字节码形式存贮。

4> 时间戳（version）

HBase中通过row和columns确定的为一个存贮单元称为cell。每个 cell都保存着同一份数据的多个版本。版本通过时间戳来索引。时间戳可以由hbase在数据写入时自动赋值（通常缺省如此做），此时时间戳是精确到毫秒的当前系统时间。时间戳也可以由客户显式赋值。用户在通过行健、列族和列键查询数据时也不需要指定具体的时间戳，而有系统返回最近时间戳的数据。

在日常的使用当中，我们在大多数情况下实际上是忽略Hbase中时间戳的存在的。

3. Hbase的表查询

Hbase的表查询分为两种：单行数据的get查询；多行数据的scan查询。

1> 单行查询（get）

单行查询适用于获取已确定知道row key的单行数据（可指定具体的列族，也可不指定），注意，类似于RDBMS中一行只有一条数据，Hbase中的一行数据中有多个Cell（K-V对）。

示例代码：

2> 多行查询（scan）

多行查询类似于RDBMS中的单表或多表多条件联合查询，适用于跨行多条件的复杂查询。

Scan查询，目前支持在行健上的范围查询，以及可以在列族、cell中的键和值上分别添加多种形式的过滤器，来达到和RDBMS类似的复杂条件查询效果。Hbase目前提供了多种过滤器，能够满足绝大多数的查询需求。

示例代码：

4. Hbase的效率

Hbase是一个基于HDFS文件系统的数据库，存储时，数据按照Row key的字典序(byte order)排序存储。设计key时，要充分排序存储这个特性，将经常一起读取的行存储放到一起，将极大提升Hbase的查询效率。

注意：

字典序对int排序的结果是

1,10,100,11,12,13,14,15,16,17,18,19,2,20,21,…,9,91,92,93,94,95,96,97,98,99。要保持整形的自然序，行键必须用0作左填充。

警告：

Hbase适用于随机读取，但不适用于频繁的全表扫描和大量IO读取，大范围频繁的数据读取容易造成节点当机，大范围数据读取请考虑使用HDFS。

5. Hbase使用示例

1> 需求分析

如上图所示，我们需要按照地域、开始时间和结束时间三个条件，查询出所需数据。

2> 数据库设计

存储单元设计：按照查询条件，地域信息的最小查询单位是村（10位行政编码），时间信息的最小单位是天，所以我们把 村-每天 对应的统计信息值作为一个存储单元（cell）存储。

行健设计：由于同一地域范围内，地域编码都有同一的前缀，所以我们把统一行政编码作为行健，统一为十位，不足十位的后面补零（例如郑州市为：4101000000）。查询时，当需要查询某一地域范围下数据时，只要查询按0补足和按9补足的范围数据即可（例如查询郑州市范围内的数据，只需要查询4101000000~4101999999），如此即可同时满足查询和存储时的位置相关性需求，提升形成。

列族设计：所有数据使用一个列族，该列族在逻辑上木有实际意义。

列键设计：把时间转化为long型数据做为列键，具有和行健相同的效果，查询时只要使用过滤器指定一个时间范围即可。

列值设计：列值上存储具体的统计数据。 数据库设计如下图所示： Row key 统一行政编码 Column-Family Default key 时间（long） value 统计值 3> 数据样例

4> 查询代码样例

二. 以RDBMS为数据源

1. 实现原理

为了增强与关系数据库的集成能力，Hadoop MapReduce提供了访问关系数据库的编程接口。

MapReduce与RDBMS交互的整体架构如下图所示：

从图中我们可以看到，datanode节点不再连接默认的HDFS，而是改为通过JDBC连接传统的RDBMS。也就是在MapReduce中，把关系型数据库做为默认的输入输出设备，而不再是HDFS。

2. 编程接口

具体到MapReduce框架读/写数据库，有2个主要的程序分别是 DBInputFormat和DBOutputFormat，DBInputFormat 对应的是SQL语句select，而DBOutputFormat 对应的是 Inster/update，使用DBInputFormat和DBOutputForma时候需要实现InputFormat这个抽象类，这个抽象类含有getSplits()和createRecordReader()抽象方

法，在DBInputFormat类中由 protected String getCountQuery() 方法传入结果集的个数，getSplits()方法再确定输入的切分原则，利用SQL中的 LIMIT 和 OFFSET 进行切分获得数据集的范围 ，请参考DBInputFormat源码中public InputSplit[]

getSplits(JobConf job, int chunks) throws IOException的方法，在DBInputFormat源码中createRecordReader()则可以按一定格式读取相应数据。

3. 具体实现 1> 建立连接

DBConfiguration类提供数据库配置和创建连接的接口，通过DBConfiguration的静态工厂方法configureDB，来建立数据库的连接。

DBConfiguration.configureDB(

Configuration conf,String driverClass,String url,String userName,String Password) 其中，conf 为当前执行的作业的配置文件，driverClasss为数据库厂商提供的访问其数据库的驱动程序，dbUrl为运行数据库的主机的地址，userName和password分别为数据库提供访问地用户名和相应的访问密码。

2> 定义表对应的bean

Hdoop中自定义的bean，必须要继承Writable接口，并实现write(DataOutput out)和readFields(DataInput in)方法，来让hadoop帮助你序列化这个bean。

同时，为了和数据库交互，还必须要继承DBWritable接口，并实现write(PreparedStatement statement)和readFields(ResultSet resultSet)。

最后还需要实现toString来完成bean的string转化。 示例代码如下：

3> 读取数据

Hadoop框架提供了一个DBInputFormat类，做为从关系型数据库获取数据的接口，DBInputFormat类提供setInput方法来设置要读取的数据库。

setInput(Job job, Class inputClass,

String tableName,String conditions, String orderBy, String... fieldNames)

其中，job为当前准备执行的作业，inputClass为表对应的bean，tableName为表名，conditions为筛选条件（eg. '(updated > 20070101 AND length > 0)'），orderBy为排序规则，fieldNames为要获取的列名。

数据读取示例代码：

4> 写入数据

Hadoop框架提供了一个DBOutputFormat类，做为向关系型数据库写入数据的接口，DBOutputFormat类提供setOutput方法来设置要写入的数据库。

setOutput(Job job, String tableName, String... fieldNames)

其中，job为当前准备执行的作业， tableName为表名， fieldNames为要写入的列名。

数据写入示例代码：

5> 完整代码示例

连接及查询和写入设置：

在map中读取数据

在reduce中写入数据

4. 注意事项

虽然Hadoop允许从数据库中直接读取数据记录作为MapReduce的输入，但处理效率较低，而且大量频繁地从MapReduce程序中查询和读取关系数据库可能会大大增加数据库的访问负载，因此DBInputFormat和DBOutputFormat仅适合操作小量数据记录的计算和应用，不适合数据仓库联机数据分析大量数据的读取处理。

实际上，我们不建议在MapReduce中对任意数据库做大量的操作，效率均不太好，包括Hbase。和MapReduce交互最直接的还是原生的HDFS。

三. Sqoop

1. Sqoop的作用

Sqoop是一个转换工具，用于在关系型数据库与HDFS或Hbase之间进行数据转换。

Sqoop提供的面向java的api，允许我们在代码或MapReduce中调用Sqoop交互数据。

2. 数据库导入HDFS 1> 命令行方式

sqoop import

--connect url //数据库连接地址 --username user //用户名 --password pass //密码 --table name //表名称

--fields-terminated-by '\' //文件中字段间的分隔符 -m number //复制过程使用map作业数量 示例：

sqoop import --connect jdbc:mysql://192.168.0.230/hadoop --username **** --password **** --table student --fields-terminated-by '\' -m 1

文件导出后在hdfs的存储路径为：/user/用户名/表名/part-m-00000

2> 代码方式

3. HDFS导入数据库 1> 命令行方式

sqoop export

--connect url //数据库连接地址 --username user //用户名 --password pass //密码 --table name //表名称 --export-dir url //hdfs文件路径

--fields-terminated-by '\' //文件中字段间的分隔符 示例：

sqoop export --connect jdbc:mysql://192.168.0.230/hadoop --username **** --password **** --table student –-export-dir /user/apple/student/part-m-00000 --fields-terminated-by '\'

2> 代码方式

4. 数据库导入Hbase 1> 命令行方式

sqoop import

--connect url //数据库连接地址 --username user //用户名 --password pass //密码 --table name //表名称

--hbase-table name //hbase表名 --hbase-create-table //表不存在就创建 --column-family family //列族名

--hbase-row-key col //指定那一列做为行健 示例：

sqoop import --connect jdbc:mysql://192.168.0.230/hadoop --username root --password root --table student --hbase-table student --hbase-create-table --column-family default --hbase-row-key id

注意：sqoop会把表中每一列的数据导入到一个Cell中，key为列名，value为列值

示例

关系表中的数据：

导入Hbase后的数据：

2> 代码方式

5. Hbase导入数据库

关于将Hbase的数据导入到mysql里，sqoop并不是直接支持的，一般采用如下3种方法：

将Hbase数据，扁平化成HDFS文件，然后再由sqoop导入。 第二种，将Hbase数据导入Hive表中，然后再导入mysql。

第三种直接使用Hbase的Java API读取表数据，直接向mysql导入，不需要使用sqoop。