本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。
OpenX SerDe
像蜂巢 JSON 一樣 SerDe,您可以使用 OpenX 的 JSON 來處理 JSON 資料。這些資料也可表示為 JSON 編碼文字 (以新行分隔) 的單行字串。像蜂巢 JSON 一樣 SerDe,OpenX 的 JSON SerDe 不允許重複的鍵map
或struct
密鑰名稱。
注意
SerDe 預期每個 JSON 文件都位於單行文字上,且記錄中欄位之間沒有行終止字元。如果 JSON 文本是漂亮的打印格式,您可能會收到一條錯誤消息,如 HIVE_CURSOR_ERROR:行不是有效的 JSON 對象
或 HIVE_CURSOR_ERROR:: 意外 JsonParseException end-of-input:當您嘗試在創建表後查詢對象的預期關閉標記
。如需詳細資訊,請參閱上 GitHub的 OpenX SerDe 文件中的 JSON 資料
可選屬性
與蜂巢 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:列不是有效的 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,根據範例中用於 Hive JSON 的相同範例線上廣告資料 SerDe 來建立資料表。 SerDe在 LOCATION
子句中,請以您執行 Athena 所在之處的區域識別符取代 myregion
。
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://DOC-EXAMPLE-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-JsonSerDestruct
和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://DOC-EXAMPLE-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
下表顯示了查詢結果。
# | 名稱 | Address | City | 項目識別碼 | 訂單日期 |
---|---|---|---|---|---|
1 | Carlos | 123 主要聖彼得堡 | 任何城鎮 | 6789 | 11/11/2022 |
2 | Carlos | 123 主要聖彼得堡 | 任何城鎮 | 4352 | 12/12/2022 |