从HDFS从读取JSON数据

使用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数据文件内容：

  {
    "created_at":"MonSep3004:04:53+00002013",
    "id_str":"384529256681725952",
    "user": {
      "id":31424214,
      "location":"COLUMBUS"
    },
    "coordinates":{
      "type":"Point",
      "values":[
         13,
         99
      ]
    }
  }

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

user.id
user.location

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

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

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

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

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

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

表1. JSON映射

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记录数据如下：

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

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

{
  "root":[
    {
      "record_obj":{
        "created_at":"MonSep3004:04:53+00002013",
        "id_str":"384529256681725952",
        "user":{
          "id":31424214,
          "location":"COLUMBUS"
        },
        "coordinates":null
      },
      "record_obj":{
        "created_at":"MonSep3004:04:54+00002013",
        "id_str":"384529260872228864",
        "user":{
          "id":67600981,
          "location":"KryberWorld"
        },
        "coordinates":{
          "type":"Point",
          "values":[
             8,
             52
          ]
        }
      }
    }
  ]
}

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

将样本JSON数据加载到HDFS

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

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

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

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

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

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

创建外部表

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

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

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

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

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

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

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

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

查询外部表中的JSON数据:

SELECT * FROM singleline_json_tbl;

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

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

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

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

查询外部表中的JSON数据：

SELECT * FROM multiline_json_tbl;