Amazon Athena で JSON データの読み取ろうとするとエラーが発生するのはなぜですか?

所要時間2分

Amazon Athena で JSON データを読み取ろうとすると、NULL または正しくないデータのエラーが発生します。

解決策

以下の一般的な問題を確認してください:

正しい JSON SerDe を使用する

Athena は次の JSON SerDes のいずれかを使用して 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

注: pretty-print の JSON ファイルに対して Athena クエリを実行する場合は、Amazon Ion Hive SerDe を使用できます。Amazon Ion SerDe では、データの各行が 1 行内にあることを想定されていません。この機能を使用することで、印刷用に整形された形式の (pretty-print の) JSON データセットをクエリしたり、行のフィールドを改行文字で分割したりできます。

レコードごとに 1 行を使用する

次の JSON レコードは正しくフォーマットされており、3 つの 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 データには、1 行に複数のレコードがあり、形式が正しくありません。

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

各列で正しいデータ型を使用する

次の例の 2 行目にある「age」のデータ型が正しくありません。列の値は「eleven」ではなく「11」にする必要があります。これが原因で、次のエラーメッセージが表示されます: HIVE_BAD_DATA: フィールド 1 のフィールド値「11」を解析中にエラーが発生しました: 入力文字列: 「11」。

{"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 ではデフォルトで大文字と小文字は区別されません。「列」と「列」のように、大文字と小文字が異なる列名がある場合、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;