為什麼嘗試在 Amazon Athena 讀取 JSON 資料時會發生錯誤？

2 分的閱讀內容

嘗試在 Amazon Athena 讀取 JSON 資料時，收到 NULL 或資料不正確錯誤。

解決方法

請檢查下列常見問題：

使用正確的 JSON SerDe

Athena 使用下列其中一個 JSON SerDe 來處理 JSON：

如果您需要對建立的資料表執行 INSERT INTO 查詢，請使用 Hive JSON SerDe。

如果您使用 OpenX JSON SerDe，則可以忽略格式錯誤的記錄來識別導致錯誤的文件。在下列範例中，將 ignore.malformed.json 設定為 true 時，格式錯誤的記錄會以 NULL 傳回：

CREATE EXTERNAL TABLE json (    id int,
    name string
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( 'ignore.malformed.json' = 'true')
LOCATION 's3://awsexamplebucket/';

執行類似下列內容，查詢新資料表以識別包含格式錯誤之記錄的檔案：

SELECT "$PATH", * FROM your_table where your_column is NULL

**注意：**如果您要針對美化格式 JSON 檔案執行 Athena 查詢，可以使用 Amazon Ion Hive SerDe。Amazon Ion Hive SerDe 不會預期每個資料列在單獨一行。您可以使用此功能來查詢具有美化格式的 JSON 資料集，或使用新行字元分割資料列中的欄位。

每個記錄使用一行

下列 JSON 記錄具有正確的格式，三個 JSON SerDE 全都可以讀取：

{ "id" : 50, "name":"John" }
{ "id" : 51, "name":"Jane" }
{ "id" : 53, "name":"Jill" }

下列 JSON 記錄只能由 Amazon Ion Hive SerDe 讀取：

{  "id" : 50,
  "name":"John"
}
{
  "id" : 51,
  "name":"Jane"
}
{
  "id" : 53,
  "name":"Jill"
}

下列 JSON 資料在單一行內有多個記錄，且格式不正確：

{ "id" : 50, "name":"John" } { "id" : 51, "name":"Jane" } { "id" : 53, "name":"Jill" }

在每個資料欄中使用正確的資料類型

下列範例中的第二行包含 "age" 的不正確資料類型。資料欄值應該是「11」而不是「十一」。這會導致下列錯誤訊息： HIVE_BAD_DATA：剖析欄位 1 的欄位值「十一」時發生錯誤：對於輸入字串：「十一」。

{"name":"Patrick","age":35,"address":"North Street"}
{"name":"Carlos","age":"eleven","address":"Flowers Street"}
{"name":"Fabiana","age":22,"address":"Main Street"}

對壓縮的 JSON 檔案使用正確的副檔名

當您使用壓縮的 JSON 檔案時，該檔案必須以 ".gz" 格式結尾。例如，"myfile.json.gz" 或 "myfile.gz" 才是格式正確的副檔名。

使用不區分大小寫的資料欄或將 case.insensitive 屬性設為 false

依預設，Athena 為不區分大小寫。如果您的資料欄名稱只有大小寫的差異 (例如 "Column" 和 "column")，Athena 就會產生下列錯誤訊息：

"HIVE_CURSOR_ERROR: Row is not a valid JSON Object - JSONException: Duplicate key"

此外，您的資料在 Athena 中無法顯示。為了避免此錯誤，請使用不區分大小寫的資料欄來產生資料。

如果您使用 OpenX SerDe，可以使用區分大小寫的金鑰名稱。為此，請將 case.insensitive SerDe 屬性設為 false，並新增大寫金鑰的對應。例如，要使用如下大寫和小寫資料欄：

{"Username": "bob1234", "username": "bob" }

請使用以下 SerDe 屬性：

CREATE external TABLE casesensitive_json (user_name String,username String)
ROW FORMAT serde 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( 'mapping.user_name' = 'Username','case.insensitive'='false')
LOCATION 's3://awsexamplebucket/';

如果 JSON 檔案有區分大小寫的重複資料欄名稱，請更新檔案以移除重複的資料欄：

{"username": "bob1234", "username": "bob" }

確定 JSON SerDe 資料表中的所有資料列都是 JSON 格式

若要尋找 Athena 資料表中是否有 JSON 資料列或檔案名稱，請執行下列操作：

使用輸入檔案中不存在的分隔符號建立資料表。請執行類似於下列內容的命令：

CREATE EXTERNAL TABLE IF NOT EXISTS json_validator (jsonrow string) ROW FORMAT DELIMITED
FIELDS TERMINATED BY '%'
location 's3://awsexamplebucket/';

執行類似於下列內容的查詢，以傳回無效 JSON 資料列的檔案名稱、資料列詳細資料，以及 Amazon S3 路徑。

WITH testdataset AS (SELECT "$path" s3path,jsonrow,try(json_parse(jsonrow)) isjson FROM json_validator)
SELECT * FROM testdataset WHERE ISJSON IS NULL;