JSONL ファイルをもとに Amazon Athena で CREATE TABLE してみる

こんにちは！よしななです。 今回は、JSONL データを Amazon Athena でCREATE TABLEする機会があったので備忘録としてブログに残します。

目次

• やりたいこと
• 対象読者
• 事前準備
  • AWS へのサインイン
  • データを Amazon S3 に配置する
  • JSONL ファイルのアップロード
• 手順
  • Amazon Athena で JSONL を操作してみる
    • CREATE TABLE の実行
    • テーブルの検索

やりたいこと

• Amazon S3 にアップロードされた 以下の JSONL ファイルをもとに、Amazon Athena 上でCREATE TABLEを実行します。
• 作成したテーブルに対し、SELECT文を使用してGradesカラムをJapanese（国語）、Math（数学）、English（英語）…と分けて出力するところまでを実施します。

• 今回対象となるデータは以下となります。

test_jsonl.jsonl

{"No": 1, "Name": "山田太郎", "Gender": "Male", "Grades": {"Japanese": 82, "Math": 74, "English": 88, "Science": 90, "SocialStudies": 85}}
{"No": 2, "Name": "佐藤花子", "Gender": "Female", "Grades": {"Japanese": 90, "Math": 68, "English": 95, "Science": 87, "SocialStudies": 92}}
{"No": 3, "Name": "鈴木一郎", "Gender": "Male", "Grades": {"Japanese": 75, "Math": 80, "English": 78, "Science": 85, "SocialStudies": 80}}
{"No": 4, "Name": "高橋愛", "Gender": "Female", "Grades": {"Japanese": 88, "Math": 92, "English": 94, "Science": 90, "SocialStudies": 91}}

• 今回対象となるデータの型は以下の通りです。

対象読者

• JSONL ファイルをもとに Amazon Athena 上でCREATE TABLEしたい方

事前準備

AWS マネジメントコンソールへのサインイン

AWS マネジメントコンソールからサインインが必要です。
以下の公式ドキュメントを参考に、サインインを行います。
https://docs.aws.amazon.com/ja_jp/signin/latest/userguide/how-to-sign-in.html

データを Amazon S3 に配置する

まず、JSONL ファイルをアップロードするための S3 バケットを作成します。 ホーム画面上部 → 検索コンソールから S3 を検索 → バケットを作成 をクリックします。
バケット作成画面に遷移するので、ここから S3 バケットを作成します。

設定項目

AWS リージョン：アジアパシフィック (東京) ap-northeast-1 を選択します。
バケット名：任意の名前を入力します。今回はjsonl-testに設定します。
その他の設定はデフォルトの状態にします。

JSONL ファイルのアップロード

作成した S3 バケットに、テーブルのデータソースとなる JSONL ファイルのアップロードを行います。作成したバケットを選択 → アップロードをクリックします。

以下の画面に遷移します。
ファイルのアップロードをクリックするとエクスプローラーが開くので、該当のファイルを選択してアップロードします。こちらでファイルのアップロードが完了となります。

CREATE DATABASE の実行

次に、Amazon Athena 上でCREATE DATABASEを実行します。
ホーム画面上部 → 検索コンソールから Amazon Athena を検索 → クエリエディタ をクリックします。
クエリエディタを開いたら、以下を実行し DATABASE を作成します。

create_database.sql

CREATE DATABASE jsonl_test;

手順

CREATE TABLE の実行

データベースが作成されたので、CREATE TABLEを実行してみます。

create_external_table.sql

CREATE EXTERNAL TABLE IF NOT EXISTS `jsonl_test_table`(
  `No` int,
  `Name` string,
  `Gender` string,
  `Grades` struct <`Japanese`:int,`Math`:int,`English`:int,`Science`:int,`SocialStudies`:int>
)
ROW FORMAT SERDE 
  'org.openx.data.jsonserde.JsonSerDe' 
LOCATION
  's3://jsonl-test/';

CREATE TABLE クエリの構文について

上記のクエリの構文について、以下で解説します。
下記以外にもCREATE TABLEのオプションを指定できますが、JSONL ファイルの読み込みには下記の3項目が設定されていればクエリの実行が可能です。
クエリの作成には、以下の公式ドキュメントページを参考にしています。
https://docs.aws.amazon.com/athena/latest/ug/create-table.html

1.CREATE TABLE

• EXTERNAL
  • Amazon Athena 上でCREATE TABLEする場合、Iceberg テーブルを作成する場合以外でEXTERNALが抜けているとエラーになります。
• IF NOT EXISTS
  • 同じ名前のテーブルが既にデータベースに存在する場合、CREATE TABLEをスキップします。

2. ROW FORMAT SERDE

• 作成するテーブルのデータ形式を指定します。
• テーブル作成元のデータ形式とROW FORMAT SERDEの形式が一致していないとエラーになります。
• 今回は、JSONL ファイルを読み込む必要があるので'org.openx.data.jsonserde.JsonSerDe'を指定します。

3. LOCATION

• データが配置されているバケット名を入力します。
• 今回は、jsonl-test直下にtest_jsonl.jsonlを配置したのでs3://jsonl-test/を入力します。

オブジェクト型について

オブジェクト型のデータを CREATE TABLEするには、struct＜col_name:data_type,...＞と記述します。

出力結果

テーブルが作成されたので、SELECT * FROM "jsonl_test"."jsonl_test_table" limit 10;を実行しテーブルの中身を確認してみます。

問題なくテーブルが出力されました！

テーブルの検索

次に、SELECT文を使用してGradesカラムをJapanese（国語）、Math（数学）、English（英語）…と分けて出力してみたいと思います。

select_from.sql

SELECT
    "No",
    "Name",
    "Gender",
    "Grades"."Japanese" as "国語",
    "Grades"."Math" as "数学",
    "Grades"."English" as "英語"
FROM "jsonl_test"."jsonl_test_table" limit 10;

SELECT FROM クエリの構文について

1. Grades カラムの展開

Gradesカラムを Japanese（国語）、Math（数学）、English（英語）… と分割して出力するには、{カラム名}.{オブジェクトの項目名}で出力が可能です。

出力結果

上記のクエリを実行すると、以下の通りGradesカラムをJapanese（国語）、Math（数学）、English（英語）と分けて出力することができました！

以上で、JSONL ファイルをもとに Amazon Athena でCREATE TABLEしてみる は完了となります。ここまで読んでいただきありがとうございました！