转载

为 JSON 数据启用 IBM BigInsights Big SQL，第 1 部分

简介

为了便于讲解，我们使用了下面这个扁平化的 JSON 数据样本，它实际上是收集的 JSON 格式的博客数据：

{"IsAdult":0,"PostSize":10118,"Crawled":"2012-02-15 08:33:27","Country":"","SubjectHtml":"Are you ready for <Keyword>IBM Watson<//Keyword>?","Language":"English","Inserted":"2012-02-15 08:36:56","Tags":"","Type":"blog","FeedInfo": "{/"Title/":/"Fern Halper's data makes the world go 'round/",/"Id/":/"16813173/",/"ExtKey/": /"e67d93756d1f9b5b5056bba4b6a7f78c/",/"Url/": /"http://fbhalper.wordpress.com//"}","Published":"2012-02-13 14:02:57","Url": "http://fbhalper.wordpress.com/2012/02/13/are-you-ready-for-ibm-watson/"},

{"IsAdult":0,"PostSize":10628,"Crawled":"2012-03-24 02:14:18","Country":"" ,"SubjectHtml":"<Keyword>IBM Watson<//Keyword> /u2013 what better use of analytics than fighting cancer ?","Language":"English","Inserted":"2012-03-24 02:18:02","Tags":"","Type": "blog","FeedInfo" :"{/"Title/": /"/",/"Id/":/"27788189/",/"ExtKey/": /"2fe4441ca88258772333b0209fb36ad8/",/"Url/": /"http://andvijaysays.wordpress.com//"}","Published":"2012-03-22 19:31:31","Url": "http://andvijaysays.wordpress.com/2012/03/22/ ibm-watson-what-better-use-of-analytics-than-fighting-cancer/"},

{"IsAdult":0,"PostSize":15625,"Crawled":"2012-03-07 02:47:36","Country":"","SubjectHtml": "<Keyword>IBM Watson<//Keyword> Going to Work At Citigroup on Wall Street/u2013Congress Didn/u2019t See Big Data As A Tool (Hadoop Framework) When They Had Their Chance/u2026For Consumers The Attack of the Killer Algorithms/u2013Chapter 22","Language":"English","Inserted":"2012-03-07 02:54:03","Tags":"","Type": "blog","FeedInfo":"{/"Title/":/"Medical Quack/",/"Id/":/"23369637/",/"ExtKey/": /"d4abe748d2aeb92e207c94c18b87cf8c/",/"Url/": /"http://ducknetweb.blogspot.com//"}","Published": "2012-03-07 02:24:00","Url": "http://ducknetweb.blogspot.com/2012/03/ibm-watson-going-to-work-at-citigroup.html"},

回页首

表模式

与提供的扁平化 JSON 数据样本中一样，我们需要计划我们的表模式来存储 JSON 数据，并使用 Big SQL 查询来分析。下面给出了我们将定义为 Big SQL 表的列和相应的列数据类型的详细信息。

表 1. 表模式

列名称	数据类型
Country	STRING
FeedInfo	STRING
Language	STRING
Published	STRING
SubjectHtml	STRING
Tags	STRING
Type	STRING
URL	STRING

回页首

定义 Big SQL 表

我们使用 Hadoop 关键字和 CREATE TABLE 语句来定义 Big SQL 表。要存储和处理 JSON 数据，需要在 ROW FORMAT SERDE 子句的表定义中指定 BigInsights Big SQL 所提供的 JSON SerDe 类。JSON SerDe 类位于 BigInsights 提供的 JAR 文件中：$BIGSQL_HOME/userlib/bigsql-serdes-*.jar。

我们可以有两种类型的 Big SQL 表：一个托管 Big SQL 表和一个外部 Big SQL 表。以下示例展示了如何使用上述表模式来定义 Big SQL 表。

清单 1. 托管 Big SQL 表示例

CREATE HADOOP TABLE blogs_table_managed ( Country STRING,
  FeedInfo STRING, Language STRING, Published STRING, SubjectHtml STRING,  Tags STRING, Type STRING, Url STRING )
  ROW FORMAT SERDE 'com.ibm.biginsights.bigsql.serde.JSONSerDe' ;

清单 2. 外部托管 Big SQL 表示例

CREATE EXTERNAL HADOOP TABLE blogs_table_external ( Country STRING,
  FeedInfo STRING, Language STRING, Published STRING,  SubjectHtml STRING, Tags STRING, Type STRING, Url STRING )  ROW FORMAT SERDE 'com.ibm.biginsights.bigsql.serde.JSONSerDe' LOCATION '/user/bigsql/shared_data/blogs_data/' ;

回页首

将 JSON 数据加载到 Big SQL 表中

将 JSON 数据加载到 Big SQL 表中很简单。我们只需将 JSON 数据文件放在 HDFS 路径中的适当位置。假设我们已在用户本地文件系统中的 blogs_data.json 文件中拥有所需的 JSON 数据。在 BigInsights 3.x 中，创建的托管 Big SQL 表的 HDFS 路径为：

/biginsights/hive/warehouse/ BIUserName .db/blogs_table_managed

在 IBM BigInsights 4.x 中，上面创建的托管 Big SQL 表的 HDFS 路径为：

/apps/hive/warehouse/ bigsql .db/blogs_table_managed

对于托管 Big SQL 表：使用 HDFS 命令将 blogs_data.json 文件从本地文件系统复制到合适的 HDFS 路径。

对于外部 Big SQL 表：将 blogs_data.json 从本地文件系统复制到 CREATE HADOOP TABLE 语句中指定的 HDFS 路径，并确保拥有 HDFS 路径的正确的访问权限。

回页首

使用 JSQSH 命令行来处理 Big SQL

要连接到 Big SQL 服务并执行所有上述活动，我们可以使用 BigInsights 中包含的 JSQSH 命令行接口。对于 BigInsights 3.x，JSQSH 位于 /opt/ibm/biginsights/jsqsh/bin/jsqsh。但是对于 BigInsights 4.x，JSQSH 位于 /usr/ibmpacks/common-utils/jsqsh/2.14/bin/jsqsh。

以 bigsql 用户身份登录到集群，并运行 JSQSH 工具来设置连接：

Full-Path /jsqsh – setup
键入 (C)onnection Wizard 并按下 Enter
选择 bigsql 连接编号并按下 Enter
选择该编号来更新每个连接 URL 变量
将 Autoconnect 设置为 True
选择 (T)est 来测试 Big SQL 连接
单击 Save 和 Quit ，这会让您仍然留在 JSQSH 命令提示符中，直到您使用 quit 命令退出

回页首

使用 JSQSH 命令行创建 Big SQL 托管表

在我们设置了需要的连接细节并将连接的 autoconnect 属性设置为 true 后，现在从 jsqsh 的完整路径来执行它，以便连接到 Big SQL。现在我们会看到如下所示的提示符。

清单 3. Big SQL 托管表 JSON 输出

Automatically connecting with connection "bigsql."  Run with --setup to disable autoconnect if necessary.
  JSqsh Release 3.0, Copyright (C) 2007-2015, Scott C. Gray
  Type /help for available help topics. Using JLine.
  [big sql server][bigsql] 1> CREATE HADOOP TABLE  blogs_table_managed ( Country STRING,
  [big sql server][bigsql] 2> FeedInfo STRING,
  [big sql server][bigsql] 3> Language STRING,
  [big sql server][bigsql] 4> Published STRING,
  [big sql server][bigsql] 5> SubjectHtml STRING,
  [big sql server][bigsql] 6> Tags STRING,
  [big sql server][bigsql] 7> Type STRING,
  [big sql server][bigsql] 8> Url STRING )
  [big sql server][bigsql] 9> ROW FORMAT SERDE ' com.ibm.biginsights.bigsql.serde.JSONSerDe';
0 rows affected (total: 3.41s)

要将 JSON 数据加载到 Big SQL 托管表中，可以使用下面所示的 HDFS 命令将 blogs_data.json 文件从本地文件系统复制到 HDFS 路径。

清单 4. BigInsights 3.x HDFS put 命令

hdfs dfs -put blogs_data.json /biginsights/hive/warehouse/bigsql.db/blogs_table_managed
 hdfs dfs -ls /biginsights/hive/warehouse/bigsql.db/blogs_table_managed
   Found 1 items
   -rw-r--r-- 1 bigsql biadmin 11699 2015-05-07 04:04   /biginsights/hive/warehouse/bigsql.db/blogs_table_managed/blogs_data.json

清单 5. BigInsights 4.x HDFS put 命令

hdfs dfs -put blogs_data.json /apps/hive/warehouse/bigsql.db/blogs_table_managed
  hdfs dfs -ls /apps/hive/warehouse/bigsql.db/blogs_table_managed
  Found 1 items
   -rw-r--r-- 1 bigsql hadoop 11699 2015-05-07 04:04 /apps/hive/warehouse/bigsql.db  /blogs_table_managed/blogs_data.json

我们已将需要的 JSON 数据放入 Big SQL 托管表的合适的 HDFS 路径中，所以我们可以开始从 JSQSH 命令行处理这些 JSON 数据了，如下所示。

清单 6. Big SQL 托管表 JSQSH 输出上的 SQL 查询

[big sql server][bigsql] 1> select count(*) from blogs_table_managed;
  +----+
  | 1 |
  +----+
  | 20 |
  +----+
  1 row in results(first row: 1.60s; total: 1.61s)
  [big sql server][bigsql] 1>  select language from blogs_table_managed where language like 'Chinese%' ;
 +------------------+
  | LANGUAGE |
  +------------------+
  | Chinese - Simple |
 +------------------+
  1 row in results(first row: 2.73s; total: 2.73s)
  [big sql server][bigsql] 1>  select published from blogs_table_managed where language = 'German' ;
 +---------------------+
  | PUBLISHED |
 +---------------------+
  | 2012-03-06 16:12:31 |
 +---------------------+
  1 row in results(first row: 0.63s; total: 0.64s)
  [big sql server][bigsql] 1>  select language , count(*) as blogs from blogs_table_managed group by language;
 +------------------+-------+
  | LANGUAGE | BLOGS |
 +------------------+-------+
  | Chinese - Simple | 1 |
  | English | 15 |
  | German | 1 |
  | Portuguese | 1 |
  | Russian | 1 |
  | Slovak | 1 |
  +------------------+-------+
 6 rows in results(first row: 0.81s; total: 0.82s)

回页首

丢弃 Big SQL 托管表

丢弃 Big SQL 托管表也会删除存储在相应 HDFS 路径上的底层 JSON 数据文件。我们可以在 HDFS 路径上看到底层 JSON 数据文件，如下所示。

清单 7. Big SQL 托管表的 BigInsights 3.x 底层 JSON 数据文件

hdfs dfs -ls /biginsights/hive/warehouse/bigsql.db/blogs_table_managed
 Found 1 items
  -rw-r--r-- 1 bigsql biadmin 11699 2015-05-07 04:04 /biginsights/hive/warehouse/bigsql.db /blogs_table_managed/blogs_data.json
 [big sql server][bigsql] 1> drop table blogs_table_managed;
  0 rows affected (total: 3.50s)

丢弃表后，Big SQL 默认情况下也会删除底层数据。

hdfs dfs -ls /biginsights/hive/warehouse/bigsql.db/blogs_table_managed
  ls: `/biginsights/hive/warehouse/bigsql.db/blogs_table_managed': No such file or directory

对于 BigInsights 4.x，丢弃上述 Big SQL 托管表也会得到这个结果。

回页首

使用 JSQSH 命令行创建 Big SQL 外部表

创建 Big SQL 外部表类似于创建 Big SQL 托管表，但我们需要在 CREATE HADOOP TABLE 语句中指定 LOCATION 子句来表明将用来存放表数据文件的 HDFS 路径。

清单 8. Big SQL 外部表 JSQSH 输出

[big sql server][bigsql] 1> CREATE EXTERNAL HADOOP TABLE blogs_table_external ( Country STRING,
  [big sql server][bigsql] 2> FeedInfo STRING,
  [big sql server][bigsql] 3> Language STRING,
  [big sql server][bigsql] 4> Published STRING,
  [big sql server][bigsql] 5> SubjectHtml STRING,
  [big sql server][bigsql] 6> Tags STRING,
  [big sql server][bigsql] 7> Type STRING,
   [big sql server][bigsql] 8> Url STRING )
  [big sql server][bigsql] 9> ROW FORMAT SERDE 'com.ibm.biginsights.bigsql.serde.JSONSerDe'
 [big sql server][bigsql] 10> LOCATION '/user/bigsql/shared_data/blogs_data/' ;
  0 rows affected (total: 0.45s)

LOCATION 子句指定的路径如果不存在，则会在 HDFS 中创建该路径。

回页首

将 JSON 数据加载到 Big SQL 外部表中

使用 hdfs 命令将 blogs_data.json 文件从本地文件系统复制到 CREATE HADOOP TABLE 语句中指定的 HDFS 路径，如下所示。

清单 9. BigInsights 3.x 和 4.x HDFS put 命令

hdfs dfs -put blogs_data.json /user/bigsql/shared_data/blogs_data/
  hdfs dfs -ls /user/bigsql/shared_data/blogs_data/
  Found 1 items
  -rw-r--r-- 1 bigsql bigsql-group-name 11699 2015-05-07 04:04 /user/bigsql/shared_data
 /blogs_data/blogs_data.json

回页首

从 Big SQL 外部表查询 JSON 数据

从 Big SQL 外部表查询数据的过程与查询 Big SQL 托管表相同。在我们将所需的 JSON 数据文件放入 HDFS 路径中后，就可以开始从连接到 Big SQL 服务的 JSQSH 命令行对数据文件中存在的 JSON 数据执行 SELECT 语句，如下所示。

清单 10. Big SQL 外部表 JSQSH 输出上的 SQL 查询

[big sql server][bigsql] 1> select count(*) from blogs_table_external;
  +----+
  | 1 |
 +----+
  | 20 |
  +----+
  1 row in results(first row: 1.60s; total: 1.61s)
  [big sql server][bigsql] 1>  select language from blogs_table_external where language like 'Chinese%' ;
 +------------------+
  | LANGUAGE |
  +------------------+
  | Chinese - Simple |
  +------------------+
  1 row in results(first row: 2.73s; total: 2.73s)
  [big sql server][bigsql] 1>  select published from blogs_table_external where language = 'German' ;
 +---------------------+
  | PUBLISHED |
 +---------------------+
  | 2012-03-06 16:12:31 |
 +---------------------+
  1 row in results(first row: 0.63s; total: 0.64s)
  [big sql server][bigsql] 1>  select language , count(*) as blogs from blogs_table_external group by language;
 +------------------+-------+
  | LANGUAGE | BLOGS |
 +------------------+-------+
  | Chinese - Simple | 1 |
  | English | 15 |
  | German | 1 |
  | Portuguese | 1 |
  | Russian | 1 |
  | Slovak | 1 |
  +------------------+-------+
 6 rows in results(first row: 0.81s; total: 0.82s)

回页首

丢弃 Big SQL 外部表

丢弃 Big SQL 外部表默认情况下不会删除存储在 HDFS 路径中的底层数据文件。

清单 11. IBM BigInsights 3.x 和 4.x 中的 Big SQL 外部表的 JSON 底层数据文件

[big sql server][bigsql] 1> drop table blogs_table_external;
  0 rows affected (total: 0.77s)
 hdfs dfs -ls /user/bigsql/shared_data/blogs_data/
  Found 1 items
  -rw-r--r-- 1 bigsql biadmin 11699 2015-05-11 00:28 /user/bigsql/shared_data/blogs_data
 /blogs_data.json

甚至在丢弃外部表后，底层数据文件仍然存在于 HDFS 路径中，而且可以进一步使用。

回页首

结合使用第三方 JSON SerDe 和 Big SQL

对于 Big SQL，我们还可以使用第三方提供的 JSON SerDe 来使用 Big SQL 查询处理 JSON 格式数据。下载所需的 JSON SerDe JAR 文件，并将它放在每个 Big SQL 节点上的 $BIGSQL_HOME/userlib 和 $HIVE_HOME/lib 中，停止并重新启动 Big SQL 和 Hive 服务，让第三方 JSON SerDe Jar 生效。

现在我们可以使用 CREATE HADOOP TABLE 语句中的 ROW FORMAT SERDE 子句指定的 JSON SerDe JAR 文件中的 JSON SerDe 类来创建 Big SQL 表。加载 JSON 数据文件和使用 Big SQL 查询 JSON 数据的剩余步骤保持不变。

要使用第三方 JSON SerDe ，可以下载可用的 JSON SerDe JAR 文件的最新的二进制版本。在撰写本文时，SerDe JAR 文件的最新可用版本为 json-serde-1.3-jar-with-dependencies.jar。

将下载的 JSON SerDe JAR 文件放在 $BIGSQL_HOME/userlib 和 $HIVE_HOME/lib 中。停止并重新启动 Big SQL 和 Hive 服务，让类路径获取放入的 JSON SerDe JAR。

清单 12. 结合使用第三方 JSON SerDe 和 Big SQL：找到 JSON SerDe 类并在 CREATE HADOOP TABLE 语句中使用它

jar -tvf json-serde-1.3-jar-with-dependencies.jar | grep 'JsonSerDe.class'
                      15022 Mon Sep 08 10:56:52 PDT 2014 org/openx/data/jsonserde/JsonSerDe.class
                     The CREATE HADOOP table statement with this JsonSerDe class looks as below:
                     CREATE HADOOP TABLE blogs_table_managed  ( Country STRING, FeedInfo STRING, Language STRING, Published STRING,  SubjectHtml STRING, Tags STRING, Type STRING, Url STRING )
 ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' ;

加载 JSON 数据和使用 Big SQL 进行处理的剩余步骤与上面描述的用于 Big SQL 表的步骤相同。

回页首

结束语

本系列第 1 部分是初学者的一个起点，一些初学者可能希望了解如何创建 Big SQL Hadoop 表，以便使用 IBM BigInsights JSON SerDe 和第三方提供的 JSON SerDe 来处理扁平化的 JSON 数据。本教程介绍了如何使用 JSQSH 命令行，动手练习存储、加载和使用 Big SQL 查询分析扁平化的 JSON 数据。文中还介绍了创建不同类型的 Big SQL 表，存储扁平化的 JSON 数据，使用 Big SQL 查询进行分析，以及丢弃 Big SQL 表的默认后果。本系列第 2 部分包含使用 BigInsights Big SQL 处理嵌套的 JSON 数据的步骤。

正文到此结束