はじめに
Amazon Athena でテーブル作成や検索がうまくできなかったり、毎回苦行を強いられることに悩んでいましたが(最初からドキュメント読めよ)、公式ドキュメントにめちゃくちゃ丁寧なトラブルシューティングが書かれていたので、ここにまとめたいと思います。
JSON データ読み込みエラーの解決
適切な JSON SerDe の選択
Athena では JSON データの処理に 3 つの SerDe が利用可能です：
Hive JSON SerDe
- 作成したテーブルに対して
INSERT INTOクエリを実行する場合に使用
OpenX JSON SerDe
- 不正な JSON レコードを無視する機能が利用可能
CREATE EXTERNAL TABLE json_table (
id int,
name string
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
'ignore.malformed.json' = 'true' -- 不正な形式のレコードはNULLとして返す
)
LOCATION 's3://your-bucket/json-data/';
Amazon Ion Hive SerDe
- プリティプリント形式の JSON ファイルをクエリする場合に使用
- 改行文字で区切られていない JSON データに対応
# プリティプリント形式のJSON
{
"name": "太郎",
"age": 25,
"hobbies": [
"読書",
"サッカー",
"料理"
],
"address": {
"city": "東京",
"zipcode": "100-0001"
}
}
JSON データフォーマットの問題
正しいフォーマット
{"id": 1, "name": "John"}
{"id": 2, "name": "Jane"}
{"id": 3, "name": "Bob"}
不正なフォーマット
{"id": 1, "name": "John"}{"id": 2, "name": "Jane"}{"id": 3, "name": "Bob"}
解決策: 各 JSON レコードを改行文字で区切る必要があります。
データ型の不整合
以下のような型の不整合エラーが発生することがあります：
HIVE_BAD_DATA: Error parsing field value 'eleven' for field 1: For input string: "eleven"
解決策:
- 数値カラムには数値のみを格納
- 文字列データは適切にクォートで囲む
- スキーマ定義時に適切なデータ型を指定
圧縮ファイルの拡張子
圧縮された JSON ファイルは .gz で終わる必要があります：
- ✅
data.json.gz - ✅
data.gz - ❌
data.json.compressed
重複キーエラーの解決
大文字小文字の問題
HIVE_CURSOR_ERROR: Row is not a valid JSON Object - JSONException: Duplicate key
原因: Athena はデフォルトで大文字小文字を区別しないため、"Column"と"column"が重複キーとして認識されます。
解決策:
CREATE EXTERNAL TABLE case_sensitive_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://your-bucket/data/';
データ内の重複キーの除去
JSON ファイル内の重複キーを事前に除去する必要があります：
// 不正
{"username": "bob1234", "username": "bob"}
// 正常
{"username": "bob1234", "display_name": "bob"}
COUNT クエリの問題
レコード数が正しく返されない問題
症状: 複数の JSON レコードがあるにも関わらず、SELECT COUNT(*)が 1 を返す
原因: レコードが改行文字で区切られていない
解決策:
// 修正前
{"id": 1, "name": "John"}{"id": 2, "name": "Jane"}{"id": 3, "name": "Bob"}
// 修正後
{"id": 1, "name": "John"}
{"id": 2, "name": "Jane"}
{"id": 3, "name": "Bob"}
不正な JSON の特定
不正な JSON レコードを特定するためのクエリ：
-- 1. 区切り文字が含まれないテーブルを作成
CREATE EXTERNAL TABLE json_validator (
jsonrow string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '%'
LOCATION 's3://your-bucket/data/';
-- 2. 不正なJSONを特定
WITH testdataset AS (
SELECT "$path" s3path, jsonrow, try(json_parse(jsonrow)) isjson
FROM json_validator
)
SELECT * FROM testdataset WHERE isjson IS NULL;
S3 ソースファイルの特定
特定の行のソースファイルを確認
-- データを取得
SELECT * FROM my_table WHERE year = 2019;
-- そのデータのS3パスを確認
SELECT "$path" FROM my_table WHERE year = 2019;
特定の S3 ファイルからの行を検索
-- 特定のファイルからの行を取得
SELECT *, "$path"
FROM my_table
WHERE regexp_like("$path", 's3://bucket/path/file-01.csv');
-- ワイルドカード検索
SELECT *, "$path"
FROM my_table
WHERE regexp_like("$path", 'file-1');
JSON 関数の活用
JSON_EXTRACT vs JSON_EXTRACT_SCALAR
JSON_EXTRACT関数は JSON オブジェクトを返しますが、Hive などの JSON データ型をサポートしていないテーブル形式では NOT_SUPPORTED: Unsupported Hive type: json エラーが発生する可能性があります。
JSON_EXTRACT_SCALARはスカラー値（Boolean、Number、String）専用で、複雑な JSON オブジェクトでは出力が空になります。
-- JSON_EXTRACT: JSON型を返す（テーブル作成時にエラーの可能性）
JSON_EXTRACT(data, '$.name')
-- JSON_EXTRACT_SCALAR: 文字列を返す（スカラー値のみ）
JSON_EXTRACT_SCALAR(data, '$.name')
-- 複雑なJSONオブジェクトの場合
JSON_FORMAT(JSON_EXTRACT(data, '$.projects'))
JSON 型の取り扱い
-- データ型を確認
SELECT TYPEOF(JSON_EXTRACT(data, '$.projects')) FROM table;
-- JSON_FORMATでシリアライズ
WITH dataset AS (
SELECT '{"projects": [{"name":"project1"}]}' AS blob
)
SELECT
JSON_EXTRACT_SCALAR(blob, '$.name') AS name,
JSON_FORMAT(JSON_EXTRACT(blob, '$.projects')) AS projects
FROM dataset;
Athena の制約事項と考慮事項
主要な制約事項
- クエリ文字列長: 最大 262,144 バイト
- 行・カラムサイズ: 単一行または列のサイズは 32MB 以下
- テキストファイルの行長: 最大 200MB
- 配列の初期化: 最大 254 個の引数
- パーティション数: CREATE TABLE AS SELECT（CTAS）で最大 100 個
パフォーマンス最適化
JOIN 最適化
-- 大きなテーブルを左側に配置
SELECT *
FROM large_table l
JOIN small_table s ON l.id = s.id;
GROUP BY 最適化
-- カーディナリティの高い列から順番に配置
SELECT country_id, region_id, COUNT(*)
FROM data
GROUP BY country_id, region_id;
-- 冗長な列を避ける
SELECT country_id,
arbitrary(country_name) AS country_name,
COUNT(*) AS city_count
FROM world_cities
GROUP BY country_id;
SELECT 最適化
-- 必要な列のみを選択
SELECT id, name, created_at
FROM table
WHERE date_col >= '2024-01-01'
LIMIT 1000;
リソース制限エラーの回避
Window 関数の最適化:
-- 避ける: 大きなウィンドウ
SELECT *, ROW_NUMBER() OVER (ORDER BY date) FROM large_table;
-- 推奨: パーティション分割
SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY date) FROM large_table;
-- または非ウィンドウ関数を使用
SELECT sensor_id,
arbitrary(location) AS location, -- GROUP BY句に冗長な列を追加しなくても済むように、以下の例のようにarbitrary関数を使用
max_by(battery_status, updated_at) AS battery_status
FROM sensor_readings
GROUP BY sensor_id;
参考リンク
Discussion