使用PXF HDFS连接器读取JSON格式数据的数据。本节描述了如何使用PXF访问HDFS中的JSON数据,包括如何创建和查询引用HDFS中JSON文件的外部表。

先决条件

在尝试从HDFS读取或向HDFS写入数据之前,请确保已满足PXF Hadoop先决条件

使用JSON数据

JSON是基于文本的数据交换格式。JSON数据通常存储在带有 .json后缀的文件中。

.json文件将包含对象的集合。JSON对象是无序键/值对的集合。值可以是字符串(string)、数字(number)、true、false、null或对象(object)或数组(array)。您可以定义嵌套的JSON对象和数组。

样本JSON数据文件内容:

  1. {
  2. "created_at":"MonSep3004:04:53+00002013",
  3. "id_str":"384529256681725952",
  4. "user": {
  5. "id":31424214,
  6. "location":"COLUMBUS"
  7. },
  8. "coordinates":{
  9. "type":"Point",
  10. "values":[
  11. 13,
  12. 99
  13. ]
  14. }
  15. }

在上述的示例中,user是一个由名为idlocation的字段组成的对象。要将user对象中的嵌套字段指定为Greenplum数据库外部表列,请使用.映射:

  1. user.id
  2. user.location

coordinates是一个由名为type字段和名为values的整数数组组成的对象。使用[]来指定特定values数组中特定的元素作为Greenplum数据库外部表的列:

  1. coordinates.values[0]
  2. coordinates.values[1]

有关JSON语法的详细信息,请参阅JSON 简介

JSON到Greenplum数据库数据类型映射

要在Greenplum数据库中表示JSON数据,请将使用基本数据类型的值映射到相同类型的Greenplum数据库列。JSON支持复杂的数据类型包括投影和数组。 使用N级投影将嵌套对象和数组的成员映射到基本数据类型。

下表总结了JSON数据的外部映射规则。

表1. JSON映射

JSON 数据类型PXF/Greenplum Data Type
基本类型(integer, float, string, boolean, null)使用相应的Greenplum数据库内置数据类型;请参阅Greenplum数据库数据类型.
数组(Array)使用[]括号标识基本数据类型数组特定成员的索引。
对象(Object)使用.点号指定基本类型的每一层投影(嵌套)。

JSON数据读取模式

PXF支持两种数据读取模式。默认模式是每行一条完成的JSON记录。PXF还支持对跨多行的JSON记录进行操作的读取模式。

在接下来的示例中,您将使用两种模式对样本数据集进行操作。样本数据集的模式定义具有以下成员名称和数据类型的对象:

  • “created_at” - text
  • “id_str” - text
  • “user” - object
    • “id” - integer
    • “location” - text
  • “coordinates” - object (可选)
    • “type” - text
    • “values” - array
      • [0] - integer
      • [1] - integer

每行单条JSON记录数据如下:

  1. {"created_at":"FriJun0722:45:03+00002013","id_str":"343136551322136576","user":{
  2. "id":395504494,"location":"NearCornwall"},"coordinates":{"type":"Point","values"
  3. : [ 6, 50 ]}},
  4. {"created_at":"FriJun0722:45:02+00002013","id_str":"343136547115253761","user":{
  5. "id":26643566,"location":"Austin,Texas"}, "coordinates": null},
  6. {"created_at":"FriJun0722:45:02+00002013","id_str":"343136547136233472","user":{
  7. "id":287819058,"location":""}, "coordinates": null}

这是用于多行JSON数据的数据集:

  1. {
  2. "root":[
  3. {
  4. "record_obj":{
  5. "created_at":"MonSep3004:04:53+00002013",
  6. "id_str":"384529256681725952",
  7. "user":{
  8. "id":31424214,
  9. "location":"COLUMBUS"
  10. },
  11. "coordinates":null
  12. },
  13. "record_obj":{
  14. "created_at":"MonSep3004:04:54+00002013",
  15. "id_str":"384529260872228864",
  16. "user":{
  17. "id":67600981,
  18. "location":"KryberWorld"
  19. },
  20. "coordinates":{
  21. "type":"Point",
  22. "values":[
  23. 8,
  24. 52
  25. ]
  26. }
  27. }
  28. }
  29. ]
  30. }

在下一节中您将为示例数据集创建JSON文件,并将其添加到HDFS中。

将样本JSON数据加载到HDFS

PXF HDFS连接器读取存储在HDFS本地的JSON文件。在使用Greenplum数据库查询JSON格式数据之前,该数据必须存在于HDFS中。

将上面的单行JSON记录样本数据集复制并黏贴到名为singleline.json的文件中。同样的,将多行JSON记录数据集复制并黏贴到名为multiline.json的文件中。

注意: 确保JSON文件中没有空白行。

将您刚创建的文件复制到HDFS中。如果您在上一个练习中没有创建/data/pxf_examples目录,请创建它。例如:

  1. $ hdfs dfs -mkdir /data/pxf_examples
  2. $ hdfs dfs -put singleline.json /data/pxf_examples
  3. $ hdfs dfs -put multiline.json /data/pxf_examples

数据加载到HDFS后,您可以使用Greenplum数据库和PXF查询和分析JSON数据。

创建外部表

使用hdfs:json配置文件从HDFS中读取JSON格式的文件。以下语法创建一个引用此类文件的Greenplum数据库可读外部表:

  1. CREATE EXTERNAL TABLE <table_name>
  2. ( <column_name> <data_type> [, ...] | LIKE <other_table> )
  3. LOCATION ('pxf://<path-to-hdfs-file>?PROFILE=hdfs:json[&SERVER=<server_name>][&<custom-option>=<value>[...]]')
  4. FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');

CREATE EXTERNAL TABLE命令中使用的特定关键字和值见下表中描述。

关键字
<path‑to‑hdfs‑file>HDFS数据存储中目录或文件的绝对路径
PROFILEPROFILE关键字必须指定为hdfs:json
SERVER=<server_name>PXF用于访问数据的命名服务器配置。可选的; 如果未指定,PXF将使用default服务器。
<custom‑option><custom-option>描述见下方
FORMAT ‘CUSTOM’hdfs:json配置文件中使用 FORMAT ‘CUSTOM’CUSTOM FORMAT 要求您指定为 (FORMATTER=’pxfwritable_import’)

PXF支持单行和多行JSON记录。当您想读取多行JSON记录时,必须提供IDENTIFIER 和值。 使用这个标识JSON记录对象中第一个字段的成员名称:

选项关键字  语法、  示例  描述
IDENTIFIER&IDENTIFIER=<value>
&IDENTIFIER=created_at
仅在访问多行JSON记录时,才必须在LOCATION字符串中指定IDENTIFIER关键字和值。 使用这个值标识JSON记录对象中第一个字段的成员名称。

示例:读取单行记录的JSON文件

使用以下CREATE EXTERNAL TABLESQL命令来创建可读的外部表,该表引用每条记录单行JSON数据文件并使用PXF默认服务器。

  1. CREATE EXTERNAL TABLE singleline_json_tbl(
  2. created_at TEXT,
  3. id_str TEXT,
  4. "user.id" INTEGER,
  5. "user.location" TEXT,
  6. "coordinates.values[0]" INTEGER,
  7. "coordinates.values[1]" INTEGER
  8. )
  9. LOCATION('pxf://data/pxf_examples/singleline.json?PROFILE=hdfs:json')
  10. FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');

注意使用.投影访问usercoordinates对象中的嵌套字段。还要注意使用[]来访问coordinates.values[]数组中的特定元素。

查询外部表中的JSON数据:

  1. SELECT * FROM singleline_json_tbl;

示例:读取多行记录的JSON文件

从多行JSON记录文件中创建可读外部表的SQL命令和上面单行JSON记录的非常类似。当您要读取多行JSON记录时,您必须额外指定LOCATION子句的IDENTIFIER关键字和值。例如:

  1. CREATE EXTERNAL TABLE multiline_json_tbl(
  2. created_at TEXT,
  3. id_str TEXT,
  4. "user.id" INTEGER,
  5. "user.location" TEXT,
  6. "coordinates.values[0]" INTEGER,
  7. "coordinates.values[1]" INTEGER
  8. )
  9. LOCATION('pxf://data/pxf_examples/multiline.json?PROFILE=hdfs:json&IDENTIFIER=created_at')
  10. FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');

created_at标识示例数据模式中JSON记录record_obj中第一个字段的成员名称。

查询外部表中的JSON数据:

  1. SELECT * FROM multiline_json_tbl;