OpenX JSON SerDe - Amazon Athena
注意事项和限制可选属性示例：广告数据示例：反序列化嵌套 JSON

OpenX JSON SerDe

与 Hive JSON SerDe 一样，您可以使用 OpenX JSON 来处理 JSON 数据。这些数据还表示为用新行分隔的 JSON 编码文本的单行字符串。与 Hive JSON SerDe 一样，OpenX JSON SerDe 不允许 map 或 struct 键名称中出现重复的键。

注意事项和限制

  • 使用 OpenX JSON SerDe 时，结果的数量及其值可能不确定。结果可能包含多于或少于预期的行，或者如果底层数据中没有空值，则结果可能包含意外的空值。要解决此问题，请使用 Hive JSON SerDe 或将数据重写为其他文件格式类型。

  • SerDe 期望每个 JSON 文档都位于单行文本中，并且不使用行终止字符分隔记录中的字段。如果 JSON 文本采用美观的打印格式，当您在创建表后尝试对其进行查询时，可能会收到类似以下内容的错误消息：HIVE_CURSOR_ERROR: Row is not a valid JSON Object（HIVE_CURSOR_ERROR：行不是有效的 JSON 对象）或 HIVE_CURSOR_ERROR: JsonParseException: Unexpected end-of-input: expected close marker for OBJECT（HIVE_CURSOR_ERROR：JsonParseException：意外的输入结束：对象的预期关闭标记）。

    有关更多信息，请参阅 GitHub 上 OpenX SerDe 文档中的 JSON 数据文件。

可选属性

与 Hive JSON SerDe 不同，OpenX JSON SerDe 还具有以下可选的 SerDe 属性，非常适合用于解决数据中的不一致问题。

ignore.malformed.json

可选。设置为 TRUE 时，可让您跳过格式错误的 JSON 语法。默认为 FALSE。

dots.in.keys

可选。默认为 FALSE。设置为 TRUE 时，允许 SerDe 使用下划线替换键名称中的点。例如，如果 JSON 数据集包含名为 "a.b" 的键，您可以在 Athena 中使用此属性来定义列名 "a_b"。预设情况下（没有此 SerDe），Athena 不允许在列名中使用点。

case.insensitive

可选。默认为 TRUE。设置为 TRUE 时，SerDe 将所有大写列转换为小写。

要在数据中使用区分大小写的键名，请使用 WITH SERDEPROPERTIES ("case.insensitive"= FALSE;)。然后，对于每个非全部小写的键，请使用以下语法提供从列名到属性名的映射：

ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ("case.insensitive" = "FALSE", "mapping.userid" = "userId")

如果您有两个键（例如 URL 和 Url），并且二者在小写时是相同的，则可能会发生与以下内容类似的错误：

HIVE_CURSOR_ERROR: Row is not a valid JSON Object - JSONException: Duplicate key "url"（HIVE_CURSOR_ERROR：行不是有效的 JSON 对象 - JSONException：重复的键“url”）

要纠正此错误，请将 case.insensitive 属性设置为 FALSE，并将键映射到不同的名称，如以下示例所示：

ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ("case.insensitive" = "FALSE", "mapping.url1" = "URL", "mapping.url2" = "Url")
映射

可选。将列名映射到与列名不同的 JSON 键。当 JSON 数据包含作为关键字的键时，mapping 参数很有用。例如，如果您有名为 timestamp 的 JSON 键，请使用以下语法将该键映射到名为 ts 的列：

ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ("mapping.ts" = "timestamp")
将带有冒号的嵌套字段名称映射到与 Hive 兼容的名称

如果字段名称的 struct 中包含冒号，则可以使用 mapping 属性将该字段映射到与 Hive 兼容的名称。例如，假设您的列类型定义包含 my:struct:field:string，则可以通过在 WITH SERDEPROPERTIES 中加入以下条目来将定义映射到 my_struct_field:string：

("mapping.my_struct_field" = "my:struct:field")

以下示例显示了对应的 CREATE TABLE 语句。

CREATE EXTERNAL TABLE colon_nested_field (
item struct<my_struct_field:string>)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ("mapping.my_struct_field" = "my:struct:field")

示例：广告数据

以下示例 DDL 语句使用 OpenX JSON SerDe 基于 Hive JSON SerDe 示例中使用的相同示例在线广告数据创建表。在 LOCATION 子句中，将 myregion 替换为您运行 Athena 的区域的标识符。

CREATE EXTERNAL TABLE impressions (
    requestbegintime string,
    adid string,
    impressionId string,
    referrer string,
    useragent string,
    usercookie string,
    ip string,
    number string,
    processid string,
    browsercokie string,
    requestendtime string,
    timers struct<
       modellookup:string, 
       requesttime:string>,
    threadid string, 
    hostname string,
    sessionid string
)   PARTITIONED BY (dt string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://amzn-s3-demo-bucket.elasticmapreduce/samples/hive-ads/tables/impressions';

示例：反序列化嵌套 JSON

您可以使用 JSON SerDes 来解析更复杂的 JSON 编码数据。这要求使用 CREATE TABLE 语句，而这些语句使用 struct 和 array 元素来表示嵌套结构。

以下示例根据具有嵌套结构的 JSON 数据创建 Athena 表。该示例具有以下结构：

{
"DocId": "AWS",
"User": {
        "Id": 1234,
        "Username": "carlos_salazar", 
        "Name": "Carlos",
"ShippingAddress": {
"Address1": "123 Main St.",
"Address2": null,
"City": "Anytown",
"State": "CA"
   },
"Orders": [
   {
     "ItemId": 6789,
     "OrderDate": "11/11/2022" 
   },
   {
     "ItemId": 4352,
     "OrderDate": "12/12/2022"
   }
  ]
 }
}

请记住，OpenX SerDe 希望每个 JSON 记录都在一行文本上。当存储在 Amazon S3 中时，前面示例中的所有数据都应该在一行上，如下所示：

{"DocId":"AWS","User":{"Id":1234,"Username":"carlos_salazar","Name":"Carlos","ShippingAddress" ...

以下 CREATE TABLE 语句将 OpenX-JSONSerde 与 struct 和 array 集合数据类型结合使用来为示例数据建立对象组。

CREATE external TABLE complex_json (
   docid string,
   `user` struct<
               id:INT,
               username:string,
               name:string,
               shippingaddress:struct<
                                      address1:string,
                                      address2:string,
                                      city:string,
                                      state:string
                                      >,
               orders:array<
                            struct<
                                 itemid:INT,
                                  orderdate:string
                                  >
                              >
               >
   )
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://amzn-s3-demo-bucket/myjsondata/';

要查询表，请使用类似以下示例的 SELECT 语句。

SELECT 
 user.name as Name, 
 user.shippingaddress.address1 as Address, 
 user.shippingaddress.city as City, 
 o.itemid as Item_ID, o.orderdate as Order_date
FROM complex_json, UNNEST(user.orders) as temp_table (o)

要访问结构中的数据字段，示例查询使用点表示法（例如，user.name）。要访问结构数组中的数据（如 orders 字段），可以使用 UNNEST 函数。UNNEST 函数将数组扁平化为临时表（在本例中称为 o）。这使您可以像处理结构一样使用点表示法来访问未嵌套的数组元素（例如，o.itemid）。名称 temp_table 在示例中用于说明目的，通常缩写为 t。

下表显示了查询结果。

# 名称 地址 城市 Item_ID Order_date
1 Carlos 123 Main St. Anytown 6789 2022 年 11 月 11 日
2 Carlos 123 Main St. Anytown 4352 2022 年 12 月 12 日