データエンジニアの @komi_edtr_1230 です。

データチームの業務はデータ分析によってheyのサービスを改善することなのですが、その際にデータ基盤を叩く必要があります。

heyではBigQueryを中心として分析業務を行なっているのですが、このBigQueryのメンテナンスとして悩ましいものがテーブルのスキーマ管理です。

サービス側のDBに何かしらの改修が入ったりビジネス的な要請によりデータウェアハウスに変更が入ることはよくあるのですが、そうした変更に際してどのようにスキーマを管理するかというのは恐らくどこの会社でも悩むポイントだと思います。

heyのデータチームも今までデータセット/テーブルの管理にかなり苦労してきたのですが、今はある程度整った形で運用できるようになってきたので、今回の記事では過去にやってしまったアンチパターンについて紹介します。

現状のスキーマ管理方法

アンチパターンを紹介する前にheyのデータチームはどのようなスキーマ管理方法に着地したかというと、SQLファイル内に直接DDL文を書くというやり方になりました。

具体例として、以下にPayPalによる支払い情報についてのテーブルのSQLファイルを載せます。

CREATE OR REPLACE TABLE
  `${gcp_project}.${bq_warehouse_dataset}.paypals`(
    paypal_id STRING OPTIONS(description="Amazon PayのID"),
    token_id STRING OPTIONS(description="PayPalでの決済時のTokenのID"),
    order_id STRING OPTIONS(description="オーダーID"),
    created_at TIMESTAMP OPTIONS(description=""),
    captured_at TIMESTAMP OPTIONS(description="資金確保のタイミング"),
    transaction_id STRING OPTIONS(description="PayPalでの取引ID"),
    displayed_at TIMESTAMP OPTIONS(description="決済画面の開かれた時刻")
  )
  OPTIONS(
    description="PayPalの決済ログ"
  )
;

INSERT `${gcp_project}.${bq_warehouse_dataset}.paypals`
SELECT
  (以下略)
FROM
  `${gcp_project}.${bq_source_dataset}.paypals`

テーブル定義をSQLファイル内にまとめることにより、ファイル内に

テーブルが差分更新なのか全件更新なのか
各カラムのデータ型や説明
テーブル自体の説明

などテーブル情報について様々な情報をまとめられます。

運用において大切なことは、何か変更が走った際に1つだけ変えればOKという形を整えることで、そうした方が更新する際の心理的負荷が減ります。

それと同時に、1箇所を見ればテーブル情報について全部知れるという状況を作ることによって全体感の把握もやりやすくなります。

アンチパターン1: スキーマ情報をJSONで外部に保存

BigQueryのコマンドであるbqコマンドではテーブルの作成/更新の際に--schema some_file.jsonというオプションを使うことによってそのテーブルのスキーマを更新することができます。

自分たちも最初はこの方式でSQLファイルにはDMLだけを、DDLについてはJSONファイルで外部に書き出すという方式にしてました。

エンジニアとして、ここはあえて疎結合にした方が構成が綺麗だろうと思っていたのです。

また、当時はEmbulkによってサービスDBにあるデータをBigQueryに書き出していたのですが、Embulkのconfigファイルにオプションとして以下のようにスキーマ情報を込めることができたので、これを有効活用しようと思ったというのも理由としてあります。

out:
  type: bigquery
  mode: append
  auth_method: service_account
  json_keyfile: /path/to/json_keyfile.json
  project: your-project-000
  dataset: your_dataset_name
  table: your_table_name
  schema_file: some_file.json  # <- これ

しかし、この方式で運用を続けていくうちに、スキーマ変更のときは複数箇所を見なければいけないことに心理的負荷を感じ始め、同時に変更ミスでテーブルが壊れるというトラブルにも出くわすようになりました。(SQLファイルは更新したがJSONファイルを更新し忘れた、というミスが原因でした)

そうした理由からスキーマ管理についてはSQLファイル内に完結させるというのがベストであろうと判断しました。

アンチパターン2: BigQueryのコンソール上でカラムのdescriptionを直接書く

スキーマ情報をJSONで管理することはマズいと思い、少しずつSQLファイルにDDL文を書いていくように変更を始めました。

ただ、heyでは様々な事業展開をしているためテーブルの数がかなりあり、データ分析依頼に対してDDL文を付記する作業が追いついていませんでした。

そこでデータアナリストの人が一部のテーブルについて直接BigQueryにスキーマを書いてくれたりしていたのですが、ここで悲しい事件が起きます。

というのも、データ連携の際にCREATE OR REPLACE TABLEのような操作を行なっていたがためにデータアナリストの方が書いてくれたdescriptionが次の朝になったら吹き飛んでいたのです。

そのためBigQueryに直接descriptionを書くという方式も避けるようにしました。

アンチパターン3: カラムの型推論に頼りすぎる

そんなこんなでスキーマ管理についてはSQLファイル内で完結させるのが良いだろうという結論に至ったのですが、データエンジニアが実装しているデータ連携の中でスキーマ情報を管理せずただただ流し込んでいるだけのものが一部ありました。

具体的に、スプレッドシート連携についてはBigQuery Connectorを利用しており、カラム名や型などは勝手に推論した上でテーブル定義を作成してくれるので、これに完全に任せていました。

しかし、ある日急にこの連携がエラーを吐くようになります。

中身を見たところ、特にデータソース側には大きな変更が無いのにも関わらずエラーになっていました。

ここの原因調査をしたところ、どうやら型推論でNULLに対してはデフォルトでSTRINGを当てるような操作が行われているようなのですが、もともとはデータソースを上から100件程度見て型推論をしていたところが急にランダムサンプリングによって型推論するような仕様に変わったらしく、それが原因でエラーを吐いていたようです。

これについては完全に型推論に頼りすぎていたのが原因であり、これについても明示的にスキーマ情報を与えることによってエラーを起こさないようになりました。