嘗試在 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 相關錯誤進行疑難排解