【レンタル機器データの可視化】レンタル機器データを可視化してみた~データ準備編~

【レンタル機器データの可視化】レンタル機器データを可視化してみた~データ準備編~

Clock Icon2024.09.03

データ事業本部のsutoです。

BIチームでは、Amazon Athenaのテーブルに格納されたレンタル機器データの可視化をテーマにブログリレーを行っています。

https://dev.classmethod.jp/articles/bi-blog-relay-rental-start/

今回は、レンタル機器データからAmazon S3に出力されたJSONデータをAmazon Athenaを使ってBIツールの可視化用テーブルを作成する手順を説明します。

Amazon Athenaとは

Amazon Athenaは、AWSが提供するサーバーレスのクエリサービスです。従来のデータウェアハウスソリューションとは異なり、Athenaではデータの保存場所であるデータレイクに対してSQL形式のクエリを実行することができます。料金も実際にクエリを実行したデータ量に基づいているため、試行的なデータ検索や加工などのアドホックなクエリ実行にとくに適しています。

基礎的な使い方はGetting Startのページをご参照ください。

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/getting-started.html

また、さらにサービスの概要を知りたい方は以下の入門をご参照いただければと思います。

https://dev.classmethod.jp/articles/introduction-2024-amazon-athena

本シナリオにおけるデータの流れ

今回のシナリオにおけるレンタル機器のデータの流れは以下の図のイメージとなります。
レンタル機器のログをストリーミングデータとしてS3バケットへ出力し、基幹システムからS3バケットへ連携済みのデータと組み合わせてAmazon Athenaでデータ加工を行い、加工後のデータテーブルを使って各BIツールで可視化を実施する流れとなります。

今回は赤枠で囲んだ部分の解説となります。

基本の構成_athenaブログ向け

可視化用テーブル作成

今回は、レンタル機器から取得されるデータはJSONデータであり、Amazon S3へ連携されている前提となります。
このJSONファイルをAthenaから読み込みテーブルを作成、さらにあらかじめcsvとしてS3に保存している基幹データ側のデータと組み合わせて加工し、可視化用のテーブルを作成する流れとなります。(データソースはS3上にcsvファイルとして保存するようにします。)

JSONファイル読み込み

まずはS3にある、以下のようなJSONファイル(一部抜粋)があります。

device.json

  //レンタル開始時
{
    "time": "2022-03-29 07:50:42",
    "device_id": "AA01-xxxx-5",
    "total_hours": "1047"
}
//レンタル終了時
{
    "time": "2022-03-29 10:50:42",
    "device_id": "AA01-xxxx-5",
    "total_hours": "1050"
}

まずjsonファイルをもとにCREATE TABLE文で加工前テーブルを作成します。

CREATE EXTERNAL TABLE IF NOT EXISTS device_json (
  time timestamp,
  device_id string,
  total_hours int
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ('ignore.malformed.json' = 'true' )
LOCATION 's3://xxxxxx/xxxx/json/';

これをもとに必要データの整理をしつつ、機器からの送信データのテーブルを作成したテーブルデータが以下となります。

スクリーンショット 2024-08-19 224121

また、上記のデバイスデータの他に「基幹システム管理データ」と「店舗リスト」のテーブルデータも準備します。

スクリーンショット 2024-08-30 182821

スクリーンショット 2024-08-30 182801

これらから作成した以下のデータマートが、今回のブログリレーの各BIツールで読み込むデータソースとなります。

CREATE TABLE bi_blog_relay2_mart 
WITH (
      external_location = 's3://xxxxxxxx/blog-relay-input-data/mart/',
      format = 'TEXTFILE')
AS
select 
    core.store_id AS "店舗ID",
    shop.store_name AS "店舗名",
    shop.latitude AS "緯度",
    shop.longitude AS "経度",
    core.device_id AS "機器ID",
    core.device_name AS "機器名",
    core.model AS "機種",
    core.device_number AS "機器管理番号",
    core.start_time AS "管理開始日時",
    core.exchange_times AS "交換基準(時間)",
    device.total_uptime AS "累計稼働(時間)",
    core.image AS "画像"
from bi_blog_relay2_core AS core
LEFT OUTER JOIN bi_blog_relay2_shop AS shop
    ON 
        core.store_id = shop.store_id
LEFT OUTER JOIN bi_blog_relay2_device AS device
    ON 
        core.device_number = device.device_number        
;

スクリーンショット 2024-08-30 183214

最後に

レンタル機器データの可視化におけるデータ準備についてご紹介しました。
Amazon Athenaは、昔からJSONファイルからテーブル作成が可能でしたためにログ分析などに使われているサービスです。
すでにAWSのDWHサービスであるRedshiftもSUPER型で読み込むことで同じようにJSONデータを取り込むことが可能になっていますので、興味がある方はそちらもぜひご参考にしてみてください。

参考リンク

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/parsing-JSON.html

https://dev.classmethod.jp/articles/visualize_json_from_redshift_with_tableau/

Share this article

facebook logohatena logotwitter logo

© Classmethod, Inc. All rights reserved.