為什麼嘗試在 Amazon Athena 讀取 JSON 資料時會出現錯誤?

2 分的閱讀內容
0

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

解決方法

請檢查下列常見問題:

使用正確的 JSON SerDe

Athena 使用以下兩個 JSON SerDe 之一處理 JSON 資料:

如果您不確定您使用的是哪個 SerDe,請兩個 SerDe 版本都試一下。如果您使用 OpenX 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 記錄的格式正確:

{ "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"
}

這些記錄的格式也不正確:

{ "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 檔案時,檔案必須以「.json」結尾,後面接著壓縮格式的副檔名,例如「.gz」。例如,這是 gzip 檔案格式正確的副檔名:「myfile.json.gz」。

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

Athena 預設情況下不區分大小寫。如果您的欄名稱只有大小寫上的差異 (例如「Column」和「column」),Athena 就會產生錯誤 (「HIVE_CURSOR_ERROR: 列不是有效的 JSON 物件 - JSONException: 重複的索引鍵」),您的資料在 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 SerDE 表中的所有列都是 JSON 格式

若要查看 Athena 表中是否有無效的 JSON 列或檔案名稱,請執行下列動作:

1.    使用輸入檔案中不存在的分隔符建立表格。請執行類似下列內容的命令:

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

2.    執行類似下列的查詢,以傳回無效 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;

相關資訊

讀取 JSON 資料的最佳做法

對 JSON 相關錯誤進行疑難排解

AWS 官方
AWS 官方已更新 2 年前