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

所要時間2分

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

解決方法

一般的な問題として以下が考えられます。

正しい JSON SerDe を使用する

Athena は、次の 2 つの JSON SerDes のいずれかを使用して、JSON データを処理します。

The native Apache Hive/HCatalog JsonSerDe (org.apache.hive.hcatalog.data.JsonSerDe)
OpenX SerDe (org.openx.data.jsonserde.JsonSerDe)

どちらの 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

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

次の 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" }

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

次の例の 2 行目にある「age」のデータ型が正しくありません。列の値は「eleven」ではなく「11」にする必要があります。これが原因で、HIVE_BAD_DATA: Error parsing field value 'eleven' for field 1: For input string: "eleven" というエラーメッセージが表示されます。

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