PLAIDEngineer Blog
エンジニア積極採用中

動的にSQLを生成する複雑なロジックに立ち向かい、開発スピードを向上させた話

Takahiro Hayashi

Posted on

はじめに

こんにちは。プレイドのKARTE Messageチームでエンジニアをしている林(Twitter)です。

この記事では、動的にSQLを生成する機能の実装を無事故で改善し、その後の開発スピードを向上させたことについてお話しします。

KARTE Messageについて

KARTE Messageは、メール・アプリプッシュ・LINEなどのチャネルからエンドユーザーにパーソナライズした配信を効率化したMAツールです。

メール配信サービスの運用について紹介している記事 があるので、こちらもぜひ併せてご覧ください。

配信対象抽出機能について

KARTE Messageでは、SQLが書けないマーケターでもGUIベースでメールやLINEの配信対象を抽出できるための機能を提供しています。

配信対象はデータ統合・利活用プラットフォームであるKARTE Datahub内のテーブルまたはクエリをベースにしており、「リスト」と呼びます。

リストには配信対象のメールアドレスやユーザーID、クーポンコード等のコンテンツに埋め込むデータが入っています。

たとえば、会員マスタのうちGOLDランクの会員にだけメールを送りたい場合は、以下のような操作で対象を絞り込むことができます。ある対象を除外する、パーソナライズした値をコンテンツに埋め込む、施策内で1ユーザーに対してx回まで配信する、といったこともこの機能で実現しています。

※ データはすべてダミーです。

merge_list.webp

配信対象抽出の裏側では、クライアントの設定した値から動的にSQLを生成しています。

先ほどのGOLD会員の例では、以下のようなSQLを生成します。

WITH result AS (
	WITH master_{list_id} AS (
		-- "【デモ】マスターデータ_メール"のクエリ
		SELECT * FROM demo_master_mail
	),
	subset_{list_id} AS (
		-- "gold会員"のクエリ
		SELECT * FROM demo_master_mail WHERE rank = 'gold'
	),
	merge_list AS (
		WITH master AS (
			SELECT * FROM master_{list_id}
		),
		subset AS (
			SELECT user_id FROM subset_{list_id}
		)
		SELECT
			master.user_id AS user_id,
			master.email AS target
		FROM master
		LEFT JOIN subset ON master.user_id = subset.user_id
		WHERE subset.user_id IS NOT NULL
	)
	-- (中略)
)
SELECT * FROM result;

このSQLは配信する時とクライアントが対象ユーザーをプレビューする時にKARTE Datahubのテーブルに対してクエリします。

対象抽出のデータ結合に用いるキーはユーザーIDか配信先 (メールアドレスやLINE ID) を使っており、ユーザーIDのカラムがある場合はユーザーIDで結合します。

クライアントの持っているデータによってはユーザーIDカラムのないデータもあるので、その場合は配信先のカラムで結合します。

また、一部のユーザーIDがnullになっていることもあります。

改善前の課題

たとえば以下のようなケースだと、メールアドレスが一致していても一部でuser_idがnullになっている場合はパーソナライズする値が落ちてしまいます。

user_id_null.png

このようなケースを含め、配信対象抽出機能を改善する前は以下のような課題がありました。

  • データに関する課題
    • targetやuser_idという一般的な名前が予約語になっており、クライアントの設定次第でカラム名が衝突する
    • user_idがnullableなので、対象抽出やパーソナライズする値を結合する際にレコードが落ちる場合がある
  • ロジックに関する課題
    • 配信時のクエリとプレビュー時のクエリを返すロジックの共通化ができておらず、出力に差分が生じてしまう
    • コードが複雑で処理の分割がうまくできておらず、キャッチアップコストが高い

配信対象抽出機能はKARTE Messageの中でもコアになる機能であることや、今後も頻繁にアップデートがかかることが想定される機能でした。この状態のまま運用していくと予期せぬバグやインシデントを起こしかねないので、機能全体に手をいれることにしました。

やったこと

以下2つのアプローチを取って行いました。

  1. リストの元データを view table にし、クエリするときは view table を参照する
  2. 既存の仕様からデータモデルを再定義し、コードをまるっと作り替える

1つ目に関しては、view tableにすることでクライアントのデータに一定の前提を設けられるので、データに関する課題を解決することができると考えました。

2つ目に関しては、仕様が明確だったこと、既存のコードに対してテストが不十分だったことなどから、改めてデータモデルから作り替えた方がメンテナブルになるだろうと考えました。

view tableにする対応について

先に、viewのクエリを示します。

SELECT
  *,
  cast(
    ifnull(
      cast(`user_id` as string),
      concat('null_embedded_', `email`)
    ) as string
  ) as masspush_user_id,
  cast(`mail` as string) as masspush_target,
FROM
  (
    -- リストの元データのクエリ
    SELECT
      *
    FROM
      demo_master_mail
  )

ポイントは2つです。

  • 予約語はクライアントが設定する可能性が限りなく低い命名にする
  • user_idがnullの時はnull_embedded_{配信先} で埋める

こうすることで、カラム名が重複することや最終的に配信対象が抜け落ちてしまうことが減りました。リストの元データのクエリは数百〜数千行になることもあるので、最終的なクエリをシンプルに保つことができるようにもなりました。

移行する中で難しかったのが、viewのクエリが参照するデータの鮮度を保つことです。

リストの中には、「先月10,000円以上購入した人」や「30分前にカゴ落ちした人」などの相対日時を条件に含むものがあります。これらは絶対日時に変換してクエリにしているので、配信やプレビューが実行される直前でviewのクエリを更新する必要がありました。

また、KARTE DatahubはBigQuery上にテーブルを保持しています。BigQueryのテーブル更新オペレーションは10秒で最大5つまでというクォータがあるので、都度viewを更新するのは現実的ではありません。

そこで、以下のような実装で更新オペレーションを極力減らすことにしました。

export const updateListViewQueryIfBaseSQLModified = async (
	baseSQL: string;
	list: List;
) => {
  const tableName = createTableName(list);
  const cachedQueryHash = await findCachedQueryHash(tableName);
  const newQueryHash = hashQuery(baseSQL);
  // 最新のqueryとcacheしたqueryのhashが一致してれば処理を終える
  if (cachedQueryHash === newQueryHash) {
    return;
  } else {
    // BigQueryに問い合わせてviewのqueryを比較する
    const isEqualBaseSQL = await compareViewToBaseSQL({
      list_id: list.id,
      baseSQL: baseSQL,
    });
    // queryが異なればviewのqueryを更新する
    if (!isEqualBaseSQL) {
      const viewQuery = buildQuery(list, baseSQL);
      await updateView(viewQuery, list.id);
    }
    // table名をkeyに、queryのhashをvalueにしたcacheを保存する
    await cacheQueryHash({ key: tableName, query: baseSQL });
  }
}

まず、BigQueryにリクエストする手前にcacheを挟むようにしました。同じリストを使い回して同時刻に複数個の配信を実行するクライアントは多いです。このようなケースはcacheのおかげでBigQueryへのリクエストを減らすことができました。

次のステップでは、BigQueryに問い合わせて最新のviewのqueryを取得し、比較するようにしました。テーブル取得のクォータは更新に比べて制約が緩いので、更新前に最終チェックをしています。

このようなステップを踏むことで、配信が集中する時間帯でもクォータを超えることなくviewのクエリを最新の状態に保つことができました。

コードの作り替えについて

コードの作り替えはTDDライクに行いました。まず仕様からテストケースを起こし、そのテストケースに従ってテストが通るようにコードを書くようにすることで、仕様の抜け漏れやエッジケースの見落としなどがないかを確認しながら進めることができました。

また、リリースに関してはview table対応とは別で行う、一部のテナントから段階的に行うなど、万が一問題があったときの影響範囲を最小限に抑えながら進めました。

結果

かなり大規模かつ問題が起きた時のリスクが大きい修正でしたが、配信に影響なくすべてリリースすることができました。

またこのリリース後、キャッチアップコストが大幅に減り、開発工数がより正確に見積れるようになりました。新しくチームに入ってきたエンジニアの方が新規に機能拡張をすることになった時も、キャッチアップが大変という感覚は無かったそうです。改善前に比べてテストも充実する形になったので、変更を加える際の影響範囲やエッジケースのバグ取りこぼしも見えやすくなりました。

課題の種類をデータとロジックに分けて、それぞれを解決するアプローチにしたことでシンプルになったと個人的には思っています。

最後に

私が所属するKARTE Messageチームでは、大量配信を支える配信基盤の改善や、より良いMAツールにしていくための機能開発など、まだまだやりたいことがたくさんあります。

少しでもこのような領域に興味を持ってくださった方は採用ページをご覧ください。応募待ってます！