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)
The 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: フィールド 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 ファイルを使用するとき、ファイルは「.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;