处理 Big SQL 扩展数据类型和复杂数据类型

在分布式文件系统(HDFS 和 GPFS)中处理大量数据

Big SQL 是 InfoSphere® BigInsights™ 中引入的一种 SQL 界面,可提供许多有用的扩展数据类型。通常,数据类型定义了所表示的值的属性集,这些属性描述了处理值的方式。Big SQL 支持一组丰富的数据类型,包括 Apache Hive 不支持的扩展数据类型。利用 Big SQL 支持的数据类型,可以更容易地表示和处理半结构化数据类型。使用本文中包含的代码样例和查询,了解如何使用 Big SQL 的简单和嵌套式复杂数据类型,以及如何在应用程序中创建和实现这些类型。作为一个新增的争议点,了解如何使用 Serializer Deserializer (SerDe) 处理 JSON 数据。

Neha Tomar, 软件开发人员, IBM

Neha TomarNeha Tomar 是一名软件开发人员,效力于 IBM 印度实验室的 Big SQL 团队。在此之前,她在 DB2 Install QA 团队工作了两年多。她获得了印度阿拉哈巴德市印度国际信息学院 (IIIT) 的硕士学位。她在数据库技术方面拥有约四年的经验。



2014 年 1 月 02 日

Big SQL 简介

InfoSphere BigInsights Quick Start Edition

InfoSphere BigInsights Quick Start Edition 是 InfoSphere BigInsights(IBM 的基于 Hadoop 的产品)的一个免费的可下载版本。使用 Quick Start Edition,您可以尝试使用 IBM 开发的特性来提高开源 Hadoop 的价值,比如 Big SQL、文本分析和 BigSheets。引导式学习可让您的体验尽可能地顺畅,包括按部就班、自订进度的教程和视频,可帮助您开始让 Hadoop 为您所用。没有时间或数据限制,您可以自行安排时间,在大量数据上试验。请 观看视频学习教程 (PDF)即刻下载 BigInsights Quick Start Edition

用于 InfoSphere BigInsights、Big SQL 的全新 SQL 界面使得用户可以对 HDFS 和 Apache HBase 中存储的数据进行 SQL 访问。它还可以跨分布式环境提供创建和填充表的能力,并通过执行 SQL 查询从分布式数据中提取信息。Big SQL 支持许多数据类型。

原始数据类型:

  • Tinyint
  • Smallint
  • IntInteger
  • Bigint
  • FloatReal
  • Double
  • Decimal(precision, scale)Numeric(precision, scale)
  • String
  • Varchar(length)Char(length)
  • Boolean
  • Timestamp
  • Binary(length)

复杂数据类型:

  • array
  • struct

Big SQL 扩展数据类型

除了原始和复杂数据类型外,Big SQL 还支持扩展数据类型,这种类型不适合 Apache Hive。以下各节将介绍这些扩展数据类型。

十进制或数值数据类型

这种数据类型可用于具有精度和范围的十进制值。缩短数据文件中存储的值,使之适合指定的精度和范围。在 Apache Hive 中,通常使用 string 数据类型保存这些值。在需要限制精度和范围时,可使用 decimal/numeric 数据类型进行替代。

语法:decimal(precision, scale)

以下示例展示了类型 decimal(10,4) DISCOUNT 一栏,其中 10 和 4 分别是精度和范围。

创建十进制数据类型的表格

		[localhost][biadmin] 1> create table PRODUCT (PROD_ID int, DISCOUNT decimal(10,4))
[localhost][biadmin] 2> row format delimited
[localhost][biadmin] 3> fields terminated by ',';
0 rows affected (total: 0.20s)

实际数据类型

real 数据类型与 float 数据类型相同,可用于单精度的浮点值。其行为也与 float 数据类型相同。

语法:real

创建实际数据类型的表格

		[localhost][biadmin] 1> create table PRODUCT(PROD_ID int, DISCOUNT real)
[localhost][biadmin] 2> row format delimited
[localhost][biadmin] 3> fields terminated by ',';
0 rows affected (total: 0.26s)

varchar/char 数据类型

这种数据类型适用于可变长度的字符串。缩短数据文件中存储的值,使之适合指定的长度。在 Apache Hive 中,可将这些值视为 string 类型值。在需要限制内容长度时,可使用 varchar/char 数据类型进行替代,因为该数据类型可以指定列中各值的大小限制。

语法:varchar(length)char(length)

创建 varchar 数据类型的表格

		[localhost][biadmin] 1> create table product(prod_id int, prod_name varchar(15))
[localhost][biadmin] 2> row format delimited
[localhost][biadmin] 3> fields terminated by ',';
0 rows affected (total: 0.12s)

Big SQL 复杂数据类型

Big SQL 支持复杂数据类型 arraystruct。这些类型可用于表示具有复杂结构的数据。虽然这些数据类型并没有在 InfoSphere BigInsights 2.1 信息中心中记录,但我还是要在这里介绍一下,以提供有关 Big SQL 支持的复杂数据类型的洞察。

  • array 数据类型用于定义同类物体的数据结构。Big SQL array 数据类型可在简单格式和嵌套格式中使用。array 类型列中的元素可使用索引 —phone[1] 进行访问,例如,每个 SQL 标准从 1 开始。
  • struct 是数据类型 Big SQL 的一种内置数据类型,它包含一系列的属性,每个属性都可以是原始、复杂或嵌套数据类型。自 InfoSphere BigInsights 2.1 起,Big SQL 支持使用数据类型 struct 作为表格中的列类型,或是作为 array 列的子类型。struct 类型列中的元素可以使用圆点 (.) 符号进行访问,例如 address.city

了解 Big SQL 的复杂数据类型

现在来看一个具体的示例,更详细地了解 Big SQL 复杂数据类型。要想使用 Big SQL,需要配置并启动 Big SQL 服务器,这是 InfoSphere BigInsights 的一个组件。请访问 InfoSphere BigInsights 2.1 信息中心(参见 参考资料),了解有关安装和配置 Big SQL 的详细信息。在完成安装和配置之后,就可以使用 $BIGSQL_HOME/bin 目录中的实用程序 bigsql 来启动 Big SQL。

启动 Big SQL 服务器

		biadmin@bdvm177:~> cd $BIGSQL_HOME/bin
biadmin@bdvm177:/opt/ibm/biginsights/bigsql/bin> ./bigsql start
BigSQL running, pid 18598.

使用命令行客户端工具 JSqsh 连接到 Big SQL 服务器,该工具可安装在服务器上。JSqsh 是一个通用的 JDBC 客户端工具,可用于使用 JDBC 驱动程序的任何数据源。脚本 jsqsh 位于 $BIGSQL_HOME/bin 目录中。如欲了解有关的更多信息,请参阅 InfoSphere BigInsights 2.1 信息中心(参见 参考资料)。

使用 JDBC 客户端工具 jsqsh 连接到 Big SQL 服务器

biadmin@bdvm177:/opt/ibm/biginsights/bigsql/bin> ./jsqsh -Ubiadmin -Ppassw0rd -dbigsql
Current schema context is 'default'
JSqsh Release 1.5-ibm, Copyright (C) 2007-2013, Scott C. Gray
Type \help for available help topics. Using JLine.
[localhost][biadmin] 1>

包含复杂数据类型列的表格

例如,想象一下使用在线门户网站 www.xyzshopping.com 的购物车的情形。创建一个名为 SHOPPING 数据库,其中包含两个表格:CUSTOMER_DETAILS 和 ORDER_DETAILS,前者包含具有该门户帐户的客户详细信息,后者包含客户订单信息。参照表 1 和 表 2,了解这些表格的结构。

表 1. 表 CUSTOMER_DETAILS 的结构
列名称列数据类型
CUSTOMER_IDvarchar(15)
FIRST_NAMEvarchar(15)
LAST_NAMEvarchar(20)
ADDRESSstruct<LINE1:varchar(15), LINE2: varchar(15), POSTAL_CODE:integer, CITY: varchar(20), STATE: varchar(20), COUNTRY: varchar (20), EMAILID: varchar(25)>
CONTACT_NUMBERarray<integer>

Big SQL 还支持 arraystruct 数据类型的嵌套。表 ORDER_DETAILS 包含五个列,列 ITEMS 是嵌套列。该列包含一个嵌套在 array 数据类型中的 struct 数据类型。

表 2. 表 ORDER_DETAILS 的结构
列名称列数据类型
ORDER_IDvarchar(15)
SHIPPING_ADDRESSstruct<LINE1: varchar (15), LINE2: varchar (15), POSTAL_CODE: integer, CITY: varchar (20), STATE: varchar (20), COUNTRY: varchar (20), EMAILID: varchar (25), CONTACT_NUMBER: integer>
ITEMSarray<struct<ITEM_ID: varchar(20), QUANTITY:integer, PRICE:integer>>
ORDER_DATEtimestamp
CUSTOMER_IDvarchar(15)

使用 Big SQL 创建表 CUSTOMER_DETAILS 和 ORDER_DETAILS

Big SQL 提供了 create table 命令,为基础存储机制 HDFS 和 Apache HBase 创建了一个表。在本例中,HDFS 文件已被用作基础存储。字段分隔符(表中的列)和集合(由复杂数据类型定义)可在使用 create table 命令创建表格时指定。默认情况使用的是 ASCII 控制代码中的控制字符(用插入符号 ^ 表示,例如:^A, ^B),使用该字符作为只有前三个值可以用另一个符号覆盖的数据值的分隔符。Control A (^A) 是字段的默认分隔符,而 Control B (^B) 是集合类型 arraystruct 的默认分隔符。对于嵌套的数据类型,分隔符符号由嵌套的等级决定。

创建数据库和表

		[localhost][biadmin] 1> create database SHOPPING;
0 rows affected (total: 0.60s)
[localhost][biadmin] 1> use SHOPPING;
ok. (total: 0.37s)

[localhost][biadmin] 1> create table CUSTOMER_DETAILS
[localhost][biadmin] 2> (
[localhost][biadmin] 3> CUSTOMER_ID varchar(15),
[localhost][biadmin] 4> FIRST_NAME varchar(15),
[localhost][biadmin] 5> LAST_NAME varchar(20),
[localhost][biadmin] 6> ADDRESS struct<
[localhost][biadmin] 7>    LINE1:varchar(15), 
[localhost][biadmin] 8>    LINE2:varchar(15), 
[localhost][biadmin] 9>    POSTAL_CODE:integer, 
[localhost][biadmin] 10>   CITY:varchar(20), 
[localhost][biadmin] 11>   STATE:varchar(20), 
[localhost][biadmin] 12>   COUNTRY:varchar(20), 
[localhost][biadmin] 13>   EMAILID:varchar(25)>,
[localhost][biadmin] 14> CONTACT_NUMBER array<integer>
[localhost][biadmin] 15> )
[localhost][biadmin] 16> row format delimited
[localhost][biadmin] 17> fields terminated by ','
[localhost][biadmin] 18> collection items terminated by '|'
[localhost][biadmin] 19> lines terminated by '\n'
[localhost][biadmin] 20> ;
0 rows affected (total: 0.82s)

[localhost][biadmin] 1> create table ORDER_DETAILS
[localhost][biadmin] 2> (
[localhost][biadmin] 3> ORDER_ID varchar(15),
[localhost][biadmin] 4> SHIPPING_ADDRESS struct<
[localhost][biadmin] 5>    LINE1:varchar(15), 
[localhost][biadmin] 6>    LINE2:varchar(15), 
[localhost][biadmin] 7>    POSTAL_CODE:integer, 
[localhost][biadmin] 8>    CITY:varchar(20), 
[localhost][biadmin] 9>    STATE:varchar(20),
[localhost][biadmin] 10>   COUNTRY:varchar(20), 
[localhost][biadmin] 11>   EMAILID:varchar(25), 
[localhost][biadmin] 12>   CONTACT_NUMBER:integer>,
[localhost][biadmin] 13> ITEMS array<
[localhost][biadmin] 14>   struct<
[localhost][biadmin] 15>       ITEM_ID:varchar(20), 
[localhost][biadmin] 16>	QUANTITY:integer, 
[localhost][biadmin] 17>	PRICE:integer>>,
[localhost][biadmin] 18> ORDER_DATE timestamp,
[localhost][biadmin] 19> CUSTOMER_ID varchar(15)
[localhost][biadmin] 20> )
[localhost][biadmin] 21> row format delimited
[localhost][biadmin] 22> fields terminated by ','
[localhost][biadmin] 23> collection items terminated by '|'
[localhost][biadmin] 24> lines terminated by '\n'
[localhost][biadmin] 25> ;
0 rows affected (total: 2.79s)

现在,我们已经创建了两个表(数据库 SHOPPING 下的 CUSTOMER_DETAILS 和 ORDER_DETAILS),已经转备好将数据加载到这些表中。

数据库 SHOPPING 中的清单表

		[localhost][biadmin] 1> \show tables -s SHOPPING -e
+-----------+-------------+------------------+------------+
| TABLE_CAT | TABLE_SCHEM | TABLE_NAME       | TABLE_TYPE |
+-----------+-------------+------------------+------------+
| [NULL]    | shopping    | customer_details | TABLE      |
| [NULL]    | shopping    | order_details    | TABLE      |
+-----------+-------------+------------------+------------+

使用 struct 数据类型列填充这些表

Big SQL 使用 load 实用程序(类似于 Apache Hive 实用程序)来填充这些表。load 实用程序没有对加载的数据执行任何转换;数据文件只是复制并移动到相应的表位置。有关的语法细节,请参阅 InfoSphere BigInsights 信息中心(参见 参考资料)。

创建本例中的表时,管线符号 (|) 和逗号 (,) 已经分别用作集合和字段分隔符。因此,表中各种字段的值需要使用逗号进行分隔,如果表中包含一些集合数据类型列(array 或 struct),那么集合项必须使用管线符号进行分隔。在给定的示例中,STRUCT 类型列 CUSTOMER_DETAILS.ADDRESS 的值可以按以下方式,指定为使用管线符号分隔各个 STRUCT 属性的位置:

CMH Road|Indira Nagar|560008|Bangalore|Karnataka|India|neha@in.ibm.com

CUSTOMER_DETAILS 表的样例行

		CUST001,Neha,Tomar,CMH Road|Indira Nagar|560008|Bangalore|Karnataka|India
|neha@in.ibm.com,998891234

现在,表可以使用 load 实用程序进行填充。

填充表 CUSTOMER_DETAILS

		[localhost][biadmin] 1> load hive data local inpath 
[localhost][biadmin] 2> '/home/neha/test_data/shopping/CUSTOMER_DETAILS.dat' 
[localhost][biadmin] 3> overwrite into table CUSTOMER_DETAILS;
ok. (total: 5.54s)

[localhost][biadmin] 1> select * from CUSTOMER_DETAILS;
+------------+------------+-----------+--------------------+----------------------------+
| customer_i | first_name | last_name | address            | contact_number             |
| d          |            |           |                    |                            |
+------------+------------+-----------+--------------------+----------------------------+
| CUST001    | Neha       | Tomar     | {CMH Road,.......} | [998891234, 998000000]     |
| CUST002    | Mrudula    | Madiraju  | {HSR Layout,.... } | [988881212, 804448008]     |
| CUST003    | Madhavi    | Shankar   | {Shanthi Colony,..}| [977777777]                |
| CUST004    | Ravindra   | Bajpai    | {80 Ft Road,...}   | [988822222, 922221111..}   |
+------------+------------+-----------+--------------------+----------------------------+
4 rows in results(first row: 0.11s; total: 0.12s)

表 ORDER_DETAILS 包含一个嵌套列 ITEMS,该列有一个在 array 类型列中定义的 struct 数据类型。表字段使用逗号分隔,而集合字段使用管线符号分隔。要想分隔 struct 属性 ITEM_ID、QUANTITY 和 PRICE,可以使用分隔符 Control character C (^C) 定义表数据的第三季分隔符。

ORDER_DETAILS 表的样例行

		ORD001,CMH Road|Indira Nagar|560008|Bangalore|Karnataka|India|neha@in.ibm.com
|998000000,ITEM001^C2^C110|ITEM010^C4^C500,2013-07-29 15:35:40.356700,CUST002

表可以使用 load 实用程序进行填充。

填充表 ORDER_DETAILS

		[localhost][biadmin] 1> load hive data local inpath 
[localhost][biadmin] 2> '/home/neha/test_data/shopping/ORDER_DETAILS.dat' 
[localhost][biadmin] 3> overwrite into table ORDER_DETAILS;
ok. (total: 4.97s)

[localhost][biadmin] 1> select * from ORDER_DETAILS;
+----------+-----------------+---------------------+------------------------+------------+
| order_id | shipping_address| items               | order_date             | customer_id|
|          |                 |                     |                        |            |
+----------+-----------------+---------------------+------------------------+------------|
| ORD001   | {CMH Road....}  | [{ITEM001, 2, ..}..]| 2013-07-29 15:35:40.356| CUST002    |
| ORD002   | {80 Ft Road...} | [{ITEM111, 1, ..}..]| 2013-08-01 11:10:00.004| CUST001    |
+----------+-----------------+---------------------+------------------------+------------+
2 rows in results(first row: 8.27s; total: 8.28s)

样例查询

使用这些示例创建适合于您的具体情况的查询。

提取发送的 ORDER_ID 和 CITY 的查询

		[localhost][biadmin] 1> select ORDER_ID, SHIPPING_ADDRESS.CITY as SHIPPING_CITY 
[localhost][biadmin] 2> from ORDER_DETAILS;
+----------+---------------+
| order_id | SHIPPING_CITY |
+----------+---------------+
| ORD001   | Bangalore     |
| ORD002   | Bangalore     |
+----------+---------------+
2 rows in results(first row: 0.30s; total: 0.31s)

提取 CUSTOMER_ID 和第一个 CONTACT_NUMBER 的查询

		[localhost][biadmin] 1> select CUSTOMER_ID,CONTACT_NUMBER[1] as CONTACT_NUMBER 
[localhost][biadmin] 2> from CUSTOMER_DETAILS;
+-------------+----------------+
| customer_id | CONTACT_NUMBER |
+-------------+----------------+
| CUST001     |      998891234 |
| CUST002     |      988881212 |
| CUST003     |      977777777 |
| CUST004     |      988822222 |
+-------------+----------------+
4 rows in results(first row: 0.8s; total: 0.8s)

备注:自 InfoSphere BigInsights 2.1 起,不再支持嵌套的 structstruct 中的 struct)。此外,也不再支持提取 struct 类型列(嵌套)中的 array 的特定元素。


处理复杂数据类型的 JSON SerDe

与 Apache Hive 类似,Big SQL 也支持使用自定义的 SerDe 来处理不同的数据格式。要使用自定义的 SerDe 块来创建表,可以使用 create table 语句的子句 ROW FORMAT SERDE 'serde.class.name'。用 JSON 格式表示复杂数据结构之所以如此简单,是因为用于 JSON 数据的约定与编程语言类似。使用 JSON 数据格式还有其他几项优势,包括属于轻量级并且易于解析。

请注意,用户可以根据特定需求,通过实现 SerDe "org.apache.hadoop.hive.serde2.SerDe" 的 Apache Hive 界面来定义自定义的 SerDe。要想使用 Big SQL,则需要在 Big SQL 类路径中包含自定义的 SerDe Java 类。参阅 InfoSphere BigInsights 信息中心,了解有关的详细信息(参见 参考资料)。

为了演示 JSON SerDe 的用法,我们将创建一个适用于购物车场景的表 WISHLIST,该表包含 CUSTOMER_ID 和 ITEM_ID 的 array,以便保留由客户想在意愿清单中保存的项组成的列表。参见 参考资料,下载本例中使用的 SerDe 块(可从 GitHub 下载)。

表 3. 表 WISHLIST 的结构
列名称列数据类型
CUSTOMER_IDvarchar(15)
ITEMS_LISTarray<varchar(15)>

使用 Big SQL 创建表 WISHLIST

		[localhost][biadmin] 1> create table WISHLIST
[localhost][biadmin] 2> (
[localhost][biadmin] 3> CUSTOMER_ID varchar(15),
[localhost][biadmin] 4> ITEM_LIST array<varchar(15)>
[localhost][biadmin] 5> )
[localhost][biadmin] 6> row format serde 'org.openx.data.jsonserde.JsonSerDe'
[localhost][biadmin] 7> stored as textfile;
0 rows affected (total: 2.86s)

WISHLIST 表的 JSON 格式的样例行

		  {"CUSTOMER_ID":"CUST001","ITEM_LIST":["ITEM001","ITEM008"]}

使用 JSON 格式数据填充表

		[localhost][biadmin] 1> load hive data local inpath 
[localhost][biadmin] 2> '/home/neha/test_data/shopping/wishlist.json' 
[localhost][biadmin] 3> overwrite into table WISHLIST;
ok. (total: 15.5s)

样例查询

使用这些示例构建您自己的自定义查询。

从表 WISHLIST 中提取数据的查询

		[localhost][biadmin] 1> select * from WISHLIST;
+-------------+-----------------------------+
| customer_id | item_list                   |
+-------------+-----------------------------+
| CUST001     | [ITEM001, ITEM008]          |
| CUST008     | [ITEM022, ITEM089, ITEM005] |
+-------------+-----------------------------+
2 rows in results(first row: 8.27s; total: 8.28s)

[localhost][biadmin] 1> select CUSTOMER_ID,ITEM_LIST[1] as FIRST_ITEM from WISHLIST;
+-------------+--------------+
| customer_id | FIRST_ITEM   |
+-------------+--------------+
| CUST001     | ITEM001      |
| CUST008     | ITEM022      |
+-------------+--------------+
2 rows in results(first row: 8.19s; total: 8.19s)

结束语

InfoSphere BigInsights Big SQL 界面支持您使用扩展数据类型处理分布式文件中存储的大量数据(比如 HDFS 或 GPFS)。通过使用本文的样例代码和样例查询,您可以用 Big SQL 数据类型对自己的数据进行练习。


下载

描述名字大小
样例代码ComplexDataTypeQueries.zip2KB

参考资料

学习

获得产品和技术

讨论

条评论

developerWorks: 登录

标有星(*)号的字段是必填字段。


需要一个 IBM ID?
忘记 IBM ID?


忘记密码?
更改您的密码

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件

 


在您首次登录 developerWorks 时,会为您创建一份个人概要。您的个人概要中的信息(您的姓名、国家/地区,以及公司名称)是公开显示的,而且会随着您发布的任何内容一起显示,除非您选择隐藏您的公司名称。您可以随时更新您的 IBM 帐户。

所有提交的信息确保安全。

选择您的昵称



当您初次登录到 developerWorks 时,将会为您创建一份概要信息,您需要指定一个昵称。您的昵称将和您在 developerWorks 发布的内容显示在一起。

昵称长度在 3 至 31 个字符之间。 您的昵称在 developerWorks 社区中必须是唯一的,并且出于隐私保护的原因,不能是您的电子邮件地址。

标有星(*)号的字段是必填字段。

(昵称长度在 3 至 31 个字符之间)

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件.

 


所有提交的信息确保安全。


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Information Management
ArticleID=959048
ArticleTitle=处理 Big SQL 扩展数据类型和复杂数据类型
publish-date=01022014