GPSマルチユニットをQuickSightで可視化した②

SORACOM GPSマルチユニットのデータをQuickSightで可視化する連載の第2回です。前回は、デバイスからAWS S3へのデータ連携設定について詳しく解説しました。今回は、そのS3に蓄積されたJSONデータをQuickSightで効率的に分析・可視化するための準備として、AWS Glueを用いたデータカタログの作成と、Athenaによるデータ確認の方法を解説していきます。

AWS Glueの設定

AWS Glueは、S3に保存されたデータに対してスキーマを定義し、データカタログを作成するサービスです。これにより、AthenaなどのサービスからSQLを使ってデータを容易にクエリできるようになります。

Databasesの作成

1. AWSマネジメントコンソールでGlueサービスを開きます。
2. 左側のメニューから「Data Catalog」>「Databases」を選択します。
3. 「Create database」をクリックします。
4. Name: に soracom-db と入力します。
5. その他の設定はデフォルトのままで構いません。「Create database」をクリックします。

crawlerの作成

次に、作成したデータベースにテーブルを作成するためのクローラーを定義します。クローラーは、指定したS3バケット内のデータを自動的に解析し、スキーマを推測してテーブルを作成します。

1. 作成した soracom-db データベースをクリックし、遷移したページ内にある「Add tables using crawler」をクリックします。
2. Crawler name: に gpsMultiunit と入力します。
3. Data source type: で「S3」を選択します。
4. Specify S3 crawl scope: で「Specified path in my account」を選択し、S3 path: にGPSマルチユニットから送信されたデータが保存されているS3バケットとパスを指定します。(例: s3://your-bucket-name/your-prefix/)
5. Choose an IAM role: で適切なIAMロールを選択します。このロールには、S3バケットへの読み取り権限とGlueへの書き込み権限が必要です。
6. Frequency: は、データの更新頻度に合わせて選択します（今回は「Run on demand」で手動実行とします）。
7. Configure crawler output: セクションで、Target database: に先ほど作成した soracom-db を指定します。
8. その他の設定はデフォルトのままで構いません。「Create crawler」をクリックします。

設定が完了したら、作成した gpsMultiunit クローラーを選択し、「Run crawler」を実行します。クローラーがS3内のJSONファイルを解析し、データカタログにテーブルを作成するまでしばらく待ちます。

AthenaでViewを作成

クローラーが正常に実行されると、「soracom_gpsmultiunit」というテーブルが作成されます。このテーブルのデータ構造は、S3に保存されたJSONファイルをそのまま反映しているため、ネストされた構造になっており、QuickSightで直接扱うには少し手間がかかります。

そこで、QuickSightでのダッシュボード作成を容易にするために、Athenaで2つのVIEWを作成します。

JSONをフラット化したVIEW

このVIEWは、JSONのネストされた構造を平坦化し、各データ項目を個別のカラムとして抽出します。

CREATE OR REPLACE VIEW soracom_gpsmultiunit_allflatt_view AS
SELECT
    credentialsid,
    operatorid,
    destination.provider AS destination_provider,
    destination.service AS destination_service,
    destination.resourceUrl AS destination_resourceUrl,
    destination.payloadsonly AS destination_payloadsOnly,
    destination.sendpayloadsasbinary AS destination_sendPayloadsAsBinary,
    sourceprotocol,
    payloads.lat AS payloads_lat,
    payloads.lon AS payloads_lon,
    payloads.bat AS payloads_bat,
    payloads.rs AS payloads_rs,
    payloads.temp AS payloads_temp,
    payloads.humi AS payloads_humi,
    payloads.x AS payloads_x,
    payloads.y AS payloads_y,
    payloads.z AS payloads_z,
    payloads.type AS payloads_type,
    payloads.binaryparserenabled AS payloads_binaryParserEnabled,
    CAST(from_unixtime(timestamp / 1000) AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Tokyo' AS timestamp) AS jst_timestamp,
    imsi,
    imei
FROM
    soracom_gpsmultiunit;

最新の行だけを表示するVIEW

このVIEWは、フラット化したデータの中から、最新のデータレコードのみを抽出します。ダッシュボードで現在の最新状況を表示する際に利用します。

CREATE OR REPLACE VIEW soracom_gpsmultiunit_latest_view AS
SELECT *
FROM soracom_gpsmultiunit_allflatt_view
ORDER BY jst_timestamp DESC
LIMIT 1;

これらのVIEWを作成することで、QuickSightからより扱いやすい形式でデータにアクセスできるようになります。

次のステップ

これで、SORACOM GPSマルチユニットからS3に保存されたデータを、AWS GlueとAthenaを通じてQuickSightで可視化するための準備が整いました。次回は、これらのVIEWをQuickSightに接続し、実際にダッシュボードを作成する手順を詳しく解説します。