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

2 分的閱讀內容
0

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

解決方法

請檢查下列常見問題:

使用正確的 JSON SerDe

Athena 會使用下列兩個 JSON SerDes 之一來處理 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: 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 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 年前