WEB業界のあるあるが詰まった「わかりみかるた」で遊んでみた!

f:id:goudacheese:20211018151609p:plain
こんにちは。コピーライターの合田ピエール陽太郎と申します。

先日、株式会社PLAIDさんのKARTE Blocksのリニューアルプロモーションとして、
WEB業界のあるあるが詰まった「わかりみかるた」の企画制作のお手伝いをしました。 かるたの実物も制作したので、どうせならと社内で遊んでみることにしました。

blocks.karte.io

わかりみかるたとは

わかりみかるたは、WEB業界にいるものなら、誰もが「わかりみ!」と叫びたくなる
あるあるが詰まったボードゲームです。読み上げられた読み札の絵札を探して遊びます。

参加メンバー

f:id:goudacheese:20211018152408p:plain

合田「それではわかりみかるたをはじめたいと思います」
一同「よーし!」
f:id:goudacheese:20211018152607j:plain

「よしなにっていいましたよね」

f:id:goudacheese:20211018152650p:plain

高久「え、どこ」
宮野「はいっ」
大久保「速い」

f:id:goudacheese:20211018152714p:plain

宮野「よくありますよね!」
野口「よしなにって言われると、逆になにやっていいかわかんなくなっちゃいます」
宮野「ときに、自由って不自由ですよね」

「いまさらだけど、最初の……」

f:id:goudacheese:20211018155757p:plain

元木「はいっ、あっ!」
船越「これは、ぼくですね」

f:id:goudacheese:20211018160109p:plain

元木「船越くんは新卒だから、未経験のわかりみが多そうですね」
船越「かるたの中だけの世界なので、リアルの世界で、わかりみ体験できるの楽しみです」

「猫のバナ……」

f:id:goudacheese:20211018160253p:plain

野口「はいっ」
大久保「速いっ」
野口「ずっと狙ってましたから」

f:id:goudacheese:20211018160155p:plain

大久保「猫は強いですもんね」
野口「渾身のビジュアルも、猫の眠そうな顔には勝てない…」
大久保「でも、いいね数は多いけど、サイトに遷移しないこともありますよね…」
野口「むずかしいですね」

「ローンチ直後に……」

f:id:goudacheese:20211018161025p:plain

高久「はいっ」 野口「絶好調ですね」

f:id:goudacheese:20211018161110p:plain

高久「公開直前もよくありますよね」
野口「細かい部分ばかり確認してて、肝心のキービジュアルが仮のままだったことがありました。慌てて変えて無事公開できましたけど」
高久「あの集中力が自在にコントロールできたらいいんねすけどね」

みんなにも読み札をつくってもらいました。

高久

f:id:goudacheese:20211018161138p:plain

元木「だいたいのことはできるんです」
高久「そうなんですね」
元木「たとえば、一週間でサクラダファミリアを建てろ!と言われてもできません、時間があればつくれるじゃないですか」
高久「300年…」

元木

f:id:handlename:20211021115722p:plain

船越「え!」
元木「いや、やったことないですよ」
船越「これぐらい堂々できるんですね!でも、なんでですかね?」
元木「彼は定時であがりたかったんだと思います」
一同「なるほどー」

野口

f:id:goudacheese:20211018161721p:plain

高久「本番用のデザインつくった後に、この量の赤入れきたらしんどいですね…」
野口「現場では合意できてたけど、上長の確認で全部ひっくり返ったことがあります」
高久「辛いやつだ…」

大久保

f:id:goudacheese:20211018161741p:plain

大久保「高橋さんにきいてほしいと言われて、声をかけたら違う高橋さんで」
野口「高橋さんだけのSlackチャンネルありますもんね」
元木「高橋は日本に140万人いるみたいです」
大久保「そのうち佐藤チャンネルもできそうだ…」

船越

f:id:goudacheese:20211018161757p:plain

元木「夢があっていいですね」
船越「どんなに大変でも猫がいたら落ち着きそうです」
元木「キーボードの上に乗って、勝手にデプロイしちゃうかも…」
船越「そ、それは」

宮野

f:id:goudacheese:20211018161818p:plain

宮野「やっぱり締め切り効果ですかね」
野口「お尻に火がつくと驚くぐらい早く終わります」
宮野「火事場の馬鹿力ですね」
野口「あの集中力を日常でもつかいたいです」

職能はちがうけど、仲良く遊べました。

かるたをして遊ぶだけでなく、自分たちでつくって遊ぶこともできました。
わかりみかるたのサイトには、いろんなかるたがまだまだあります。オリジナルのかるたもつくることも!
いまなら、リアルかるたがもらえるプレゼントキャンペーン実施中です。ぜひ遊んでみてください。
blocks.karte.io

カヤックでは、さまざまな職種の人とわいわいしながら働くエンジニアも募集しています!

Redshift Federated Query for RDS/Aurora MySQL をつかったType-2 Slowly Changing Dimensionの実装

こんにちは。技術部の自称データエンジニアの池田です。

Redshift Federated Query for RDS/Aurora MySQL(Federated Query for MySQL)がめでたくGAになりました。 Federated Query for MySQLを使うと、RedshiftからAurora MySQLにクエリを発行し、その結果をRedshift上で利用することができます。 今回は、この機能を使ったType-2 Slowly Changing Dimension(SCD2) の実装の話をします。

aws.amazon.com

TL;DR

  • Change Data Capture(CDC)を実装・運用するほどじゃないけど、State Sourcingなテーブルの変更履歴を追跡したいときには、SCD2を使うと嬉しいです。
  • Federated Query for MySQL機能が非常に便利です。簡単に設定の仕方を説明しました。
  • 対象のソースデータベースがMySQL互換で、Redshift上に変更履歴を構成する場合はSCD2とFederated Query for MySQLを活用するとお手軽です

背景

プロダクトやサービスで使用するデータベースの多くはアプリケーションの都合上、StateSourcingなデータマネジメントを採用したテーブルが主体となることが多いでしょう。
StateSourcingとは、起こった事象によって変化させた状態を保存するやり方です。例えば、ユーザーの名前やプロフィール画像などを変更した場合は、基本的にテーブルのデータを上書きし、最新のものだけデータベースに保存します。
しかし、StateSourcingなテーブルはあとから変更前のデータを振り返ることがとても困難です。カスタマーサポートやデータ分析観点ではデータがどのように変更されていったのか?変更履歴の追跡が非常に重要になってきます。
Amazon Relational Database Service(RDS)を使用している場合、変更履歴を追跡する一番簡単な方法は毎日1回S3へスナップショットをExportすることです 毎日1回S3へのスナップショットをExportで変更履歴を追跡する場合の悩みごととして以下があります。

  • データの変更頻度が1日よりも高い場合は、1日1回のスナップショットでは細かい変化が追跡できない。
  • データの変更頻度がとても低い場合、変化のないデータも毎日出力・保存されるので容量の無駄がある
  • データのどの部分が変更されたのか?という情報を追跡するのが、それなりに大変である。

そこで、これらの悩みが気になる場合は、Change Data Capture(CDC)と呼ばれるプロセスと技術を用いることになります。 例えば、Aurora MySQLでCDCを実現する場合は、Binlogを有効にし、Amazon Database Migration Service(DMS)等を使用して、行の変更履歴をS3へ貯めることになるでしょう。 ところが、実際のCDCの実装・運用では以下のような悩みに出会います。

  • CDCは構築・運用が大変
    • DMSを使えば簡単そうだが、DMSがよくわからない
    • DMSを使わない場合は、Binlog等を処理する必要がある。
  • パフォーマンス上の問題でAurora MySQLのBinlogを有効にしたくない
  • etc...

一般的にCDCの構築・運用は高コストと言われており、気軽に導入は難しいでしょう。 そんなときに、選択肢の一つに入るのが Type-2 Slowly Changing Dimension(SCD2) による変更履歴の構築です。

Type-2 Slowly Changing Dimension(SCD2) とは?

SCD2はData Warehouseのデータマネジメント手法の1つで、ソースデータベースのデータを確認した際に、自然キーや代理キーごとにデータの変更を確認し変化があったら行を追加するというものです。

https://en.wikipedia.org/wiki/Slowly_changing_dimension#Type_2:_add_new_row

内容は単純ですが、この手法で変更履歴を構築することはとても強力です。 SCD2の実装は、データベースの操作的にコストがかかることが多いので、(日次よりは頻繁だが)それほど高頻度に変化しないデータの変更履歴追跡に向いています。 ミリ秒単位でデータが変化し続けるものや、絶対に変更前をロストしたくない場合はCDCを選択することをおすすめします。

Redshift Federated Query for RDS/Aurora MySQL を使ったSCD2の実装

準備

Redshiftを採用しており、アプリケーションが書き込むデータベースにMySQL互換データベースを使用している場合には、この度めでたくGAになったRedshift Federated Query for RDS/Aurora MySQLがとても便利です。 利用に際しては、以下の準備が必要です。

  • RedshiftからMySQLへのネットワーク接続ができる状態 (Security GroupやVPCの設定で疎通できる状態)
  • MySQL上にRedshiftが接続に使うユーザーが存在し、システムテーブルと読みとり対象のテーブルにSELECTできる状態 (面倒であれば、 GRANT SELECT ON *.* TO 'redshift'@'%' のように与える)
  • AWS Secrets ManagerにMySQLへの接続情報を格納し、その情報にアクセスできるIAMロールをRedshiftクラスターにアタッチしておく

この準備ができましたら、Redshift上で以下のような外部スキーマを定義します。

CREATE EXTERNAL SCHEMA federated_for_mysql
FROM MYSQL
DATABASE 'main'
URI 'app.cluster-ro-xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com'
IAM_ROLE 'arn:aws:iam::000000000000:role/redshift-federated'
SECRET_ARN 'arn:aws:secretsmanager:ap-northeast-1:000000000000:secret:prod/rds/redshift-federated-XXXXXX';

ここで指定するIAMロールは、SECRET_ARNで指定するSecrets ManagerにおいてあるMySQLへの接続情報にアクセスできる必要があります。

基本的な使い方

仮に、MySQL側に以下のようなテーブルが有るとします。

CREATE TABLE `item` (
    `id` BIGINT UNSIGNED NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `updated_at` TIMESTAMP NOT NULL,
    `created_at` TIMESTAMP NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `updated_at` (`updated_at`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4;

外部スキーマを定義により、このテーブルはRedshift上から以下のSQLで参照できます。

SELECT *
FROM federated_for_mysql.item
WHERE updated_at >= convert_timezone('JST', getdate()) - interval '1 day';

このクエリのExplainを確認すると、以下のような内容になります。

XN MySQL Query Scan competition  (cost=0.00..341.04 rows=***** width=*****)
  ->  Remote MySQL Seq Scan federated_for_mysql.item  (cost=0.00..269.24 rows=****** width=******)
        Filter: (updated_at >= '2021-09-29 16:30:20'::timestamp without time zone)

どうやら、Remote MySQL Seq Scanのステップでupdated_atのFilterがかかるようですね。 このクエリは、MySQL側のIndexの恩恵を受けられそうです。

SCD2の実装

先程のitemテーブル変更履歴をRedshiftに構築してみましょう。 まず、保存先のテーブルを用意します。

CREATE TABLE item_history (
    id BIGINT,
    name VARCHAR(191),
    updated_at TIMESTAMP,
    created_at TIMESTAMP,
    scd2_id char(64)
)
DISTKEY(scd2_id)
SORTKEY(scd2_id);

以降、Redshift上のitem_historyテーブルに変更履歴をためていくためには以下のクエリを実行します。

CREATE TEMP TABLE updated_item
AS
SELECT *, UPPER(SHA2(id || ';' || updated_at, 256))::char(64) as scd2_id
FROM federated_for_mysql.item
WHERE updated_at >= convert_timezone('JST', getdate()) - interval '1 day';

DELETE FROM updated_item
USING item_history
WHERE updated_item.scd2_id = item_history.scd2_id;

INSERT INTO item_history
SELECT * FROM updated_item;

このようにすることで、SQLを実行したタイミングで過去1日以内の変更を取得して、まだitem_historyに入っていない変更だけを入れることができます。 上記のSQLをお好きな間隔で定期的に実行することで、その間隔ごとの変更を検出・記録した変更履歴が構築できます。

DBT snapshot

実際にSCD2を実装・運用する場合だと、ソースデータベースのスキーマ変更に追従する等の作業が必要になります。 よりお手軽にしたい場合はDBTというツールのsnapshotという機能を使うことをおすすめします。

docs.getdbt.com

DBTというツールは、SQLを使ったデータ変換を実現するためのSQLビルダー/ランナーです。 細かいDBTのセットアップ等は省きますが、以下のようなsqlファイルを作成すれば、面倒なマイグレーション等はDBTに任せることができます。

{% snapshot item_history %}

{{
    config(
      unique_key='id',
      strategy='timestamp',
      updated_at='updated_at',
      invalidate_hard_deletes=False,
    )
}}

SELECT * 
FROM federated_for_mysql.item
WHERE updated_at >= convert_timezone('JST', getdate()) - interval '1 day'

{% endsnapshot %}

興味があれば、導入を検討してみてください。

おわりに

この記事では、 Redshift Federated Query for RDS/Aurora MySQL の素晴らしさを共有するために、この機能を使ったType-2 Slowly Changing Dimension の実装例について話しました。 この機能はSCD2以外にも役に立つことが沢山あり、とても素晴らしい機能だと感じました。 例えば、運用中のプロダクトあるあるなアンチパターン JSONがTextで入ったカラム も、SUPER型を併用することでデータ分析上では格段に取り扱いやすくなります。 Redshiftを用いており、アプリケーションが書き込むDBがMySQLの場合は試してみることをおすすめします。

カヤックではデータパイプラインの整備に興味のあるエンジニアも募集しています

中途採用も募集しています