🎮

改行を含むJSONファイルに対してAthenaを実行したい場合は、hiveのJsonSerDeを使う必要がある

Published

2023-09-13

背景

特定のS3プレフィックスにJSONの設定ファイルを置いて、その設定に基づいて処理を定期実行するシステムを運用しています。

期末の評価用の数字を作るために、設定の中身について集計したいモチベーションが発生した。

その時にJsonSerDeの使い分け方について、学びがあったためメモを残しておきます。

やったこと

以下にサンプルコードを示します。

sql

CREATE EXTERNAL TABLE user_yusuda.topgun_transfer_config (
  `name` string,
  `id` string,
  `target` string,
  `period` int,
  `created_by` string,
  `target_ids` array<string>,
  `urls` array<string>,
  `limit_uu` int
)
-- ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'  -- これだとうまくいかない
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'  -- こっちはうまくいく
WITH SERDEPROPERTIES (
  'serialization.format' = '1'
) LOCATION 's3://bucket/prefix/to/configs/'
TBLPROPERTIES ('has_encrypted_data'='false');

コードはサンプルのJSONをプロンプトに入れる形で、ChatGPTに生成してもらった。

ポイントは、SERDEに'org.openx.data.jsonserde.JsonSerDe'ではなく、 'org.apache.hive.hcatalog.data.JsonSerDe'を使うこと。

openxのSerDeは、改行が含まれるJSONファイルに対応していないようで、以下のエラーが発生する。

plain text

HIVE_CURSOR_ERROR: Row is not a valid JSON Object - JSONException: A JSONObject text must end with '}' at 2 [character 3 line 1]

マルチラインJSONを処理する場合は、hiveのSerDeを使うとうまくいくらしい。

オチ

hiveのSerDeでも、空のファイルが含まれていたりするとエラーが出るらしく、結局Athenaで集計することは諦めて、地道にS3からファイルを取得して集計するスクリプトを使った。

python

import boto3
import json

s3 = boto3.resource('s3')
bucket = s3.Bucket('bucket')

count = 0
for obj in bucket.objects.filter(Prefix='prefix/to/configs/'):
    if obj.key.endswith('.json'):
        content_object = s3.Object(bucket.name, obj.key)
        file_content = content_object.get()['Body'].read().decode('utf-8')
        json_content = json.loads(file_content)
        if 'urls' in json_content and json_content['urls']:
            count += 1

print(count)

これもChatGPTに生成してもらった。

便利な時代や