Amazon Athenaを使ってJsonファイルを読み込もうとして数時間ハマった件について

はじめに

Amazon Cognitoのユーザープールから任意の条件のユーザー情報を取得する必要があったので、Athenaを使うことにしました。
Athenaを初めて使うこともあり、セットアップにかなり時間がかかってしまいました。
今回はその際に躓いたところを紹介していきます。

AthenaでJSONを読み込む

今回使用したJSONのデータ構造

Cognitoのユーザープールからデータ取得する際にはCLIを使用しました。
参考：CognitoユーザープールからAWS CLIでユーザーを検索してみる

example.json

{
  "Users": [
      {
          "Username": "22704aa3-fc10-479a-97eb-2af5806bd327",
          "Enabled": true,
          "UserStatus": "FORCE_CHANGE_PASSWORD",
          "UserCreateDate": 1548089817.683,
          "UserLastModifiedDate": 1548089817.683,
          "Attributes": [
              {
                  "Name": "sub",
                  "Value": "22704aa3-fc10-479a-97eb-2af5806bd327"
              },
              {
                  "Name": "email_verified",
                  "Value": "true"
              },
              {
                  "Name": "email",
                  "Value": "[e-mail]"
              }
          ]
      }
  ]
}

上記のJSONデータをよく見るとキーはUsersしかなく、配列になっています。
サンプルとして1ユーザー情報しか入っていないが、実際はUsersに複数のユーザー情報が入っています。

テーブル作成

Athenaでネストや配列を含むデータを読み込みためには以下のように記載することができる。

CREATE TABLE

CREATE EXTERNAL TABLE IF NOT EXISTS mydatabase.mytable(
`Users` array<struct<`UserName`: string,
`UserCreateDate`: string,
`UserLastModifiedDate`: string,
`Enabled`: boolean,
`UserStatus`: string,
`Attributes`: array<struct<`Name`:string, `Value`: string>>
>>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 'JSONファイルが入ったS3バケットURI';

array<>でJSONの配列を表現する。
struct<>でネストされたJSONの値を表現する。今回はUsers配下やAttributes配下がネストされた値となる。
上記のSQL文でテーブルは作成することができました。
*ここでもstructの書き方や型の定義ミスでかなり時間がかかりました。
ネストしていると凡ミスも多いなと。。

これであとはクエリを実行するだけ。

クエリ実行

以下のクエリを実行したところ、、、

query

select user
from mytable
cross join unnest(users) as t(user)

/*`unnest`は配列の1要素を1行にする関数です。
これがないと配列のデータを取り出すことができません。*/

エラー！

error

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

原因

Athenaで使用するJSONは1レコード1行でないといけないようです。
参考：Amazon Athena で JSON データの読み取りを試みるとエラーが発生するのはなぜですか?

それでは、修正して実行してみよう！
まずは、JSONの修正から

example(修正後)

{"Users":[{"Username":"22704aa3-fc10-479a-97eb-2af5806bd327","Enabled":true,"UserStatus":"FORCE_CHANGE_PASSWORD","UserCreateDate":1548089817.683,"UserLastModifiedDate":1548089817.683,"Attributes":[{"Name":"sub","Value":"22704aa3-fc10-479a-97eb-2af5806bd327"},{"Name":"email_verified","Value":"true"},{"Name":"email","Value":"[e-mail]"}]}]}

凄い横長になりましたね。。
*実際は9万行近くあったので、見れたものじゃなかったです。

そして、再度クエリを実行したところ

result

22704aa3-fc10-479a-97eb-2af5806bd327

無事データの取得ができました！！

おわりに

AthenaはS3のログを解析するのによく使われるサービスと机上で勉強した知識だけだったので、
実際に触ってみてかなり勉強になりました。

今回はAthenaを使ってみて躓いた点をブログとしてまとめてみました。
これからAthenaを使う人の参考になれば幸いです。

PS.これが初のブログ執筆です。これからも知見共有的なブログを書いていこうと思います。