BigQuery Scriptingによって定期バッチをBigQueryだけで完結させる試み

スマートキャンプエンジニアの瀧川です。

みなさん、BigQueryは使っているでしょうか？

BigQueryはデータのインポート、加工、エクスポートを便利にする機能が数多くリリースされており、ファンも多いことと思います。

かくいう私もBigQuery大好きマンとして社内で名を馳せていて、最近だと下の記事で笑顔になった1人です。

ユーザーを笑顔にする BigQuery の使いやすい SQL 新機能 | Google Cloud 公式ブログ

今回は2019年末にリリースされたBigQuery Scriptingに注目して、どんなものなのか、既存のワークフローの中でどう使えるのかを試してみようと思います！

BigQuery Scripting
- 試しにFizzbuzz書いてみた
既存のワークフローの一部を置き換えてみる
- お題: 個人情報の秘匿化
- 実装方針
  - 完成したScript(クエリ)
まとめ

BigQuery Scripting

BigQuery Scriptingとは、2019年末にBeta版が発表された新機能となります。

今までのBigQueryでは単一のクエリを実行することしかできませんでしたが、これにより複数のクエリの実行、変数・条件分岐・ループが利用できるようになりました。

例えば「集計対象カラム一覧テーブルから取得したカラム名を動的に埋め込んだクエリ」みたいなメタクエリも簡単に書くことができます。

試しにFizzbuzz書いてみた

新しい言語を始めるときにみなさんはなにから書き始めるでしょうか？

私はなんだかんだでFizzbuzzを一回は書いている気がします。

(パターンマッチなどあるか、ifは値を返すかとか見てたりしますね)

ということでBigQuery ScriptingでFizzbuzzを書いたのが以下になります。

declare i int64 default 1;
declare result array<string> default [];

create temp function fizzbuzz(num int64) as (
  case
  when mod(num, 15) = 0 then 'FizzBuzz'
  when mod(num, 3) = 0 then 'Fizz'
  when mod(num, 5) = 0 then 'Buzz'
  else cast(num as string)
  end
);

while i <= 100 do
  set result = array_concat(result, [fizzbuzz(i)]);
  set i = i + 1;
end while;

select str from unnest(result) as str

変数宣言(declare)、UDF定義(create temp function)、ループ(while)、代入(set)を使って、他のプログラミング言語とほぼ変わらないロジックで書くことができました。

既存のワークフローの一部を置き換えてみる

今回、BigQuery Scriptingでなにができるかを見るために、実際に弊社で動いているワークフローの一部をBigQuery Scriptingのみで実装してみることにします。

お題: 個人情報の秘匿化

弊社ではアプリケーションのデータ、ログをBigQueryに集約しています。

それをそのまま社内に公開してしまうと、個人情報が含まれるため問題となってしまいます。

そこで今は以下の画像の通り、DigDagとbqコマンドを使ってクエリを投げて秘匿化したテーブルを作成するようにしています。 (以前書いたこちらの記事も参考にしてください BigQueryだけでRedashから個人情報見えなくする方法解説 - SMARTCAMP Engineer Blog)

生成された秘匿化クエリ

/*
独自の設定ファイル
masking:
  dataset: 'raw_dataset'
  tables:
    users:
      - column: first_name
      - column: email
*/
select *
replace(
  to_hex(sha256(cast(first_name as string))) as first_name
  , to_hex(sha256(cast(email as string))) as email
) from `raw_dataset.users`

今回はこれをBigQuery Scriptingで実装し直してみようと思います。

実装方針

秘匿化するテーブル、カラムをどこかで管理する必要がある(上述の設定ファイル相当)ので、今回はSpreadsheetで管理し、それをBigQueryにテーブルとして連携させておくことにします。

あとは秘匿化前のデータセットのINFORMATION_SCHEMA.TABLESから対象テーブルを抽出して、それに合致する秘匿情報を上記Spreadsheetのテーブルから取得して、動的にクエリを構築して実行という流れになるかと思います。

完成したScript(クエリ)

最終的なScriptが以下のようになりました。

多少冗長なところはあるにせよ、既存の処理とほぼ同じようなScriptができました。これがBigQueryで直接動かせるのは楽ですし、BigQuery Schedulingで定期実行すれば管理コストがとても低くなるなと思います。

(1点詰まったのは、ドキュメントではexecute immediateでplaceholderが利用できるっぽかったのですが、syntaxエラーでできませんでした🤔)

実行時間も気になるところだと思いますが、今回サイズがまちまちな100テーブルを対象に実行してみて10分程度でした。

既存のワークフローだと都度APIリクエストをしているので、今回のScriptのほうが早いのではないかと思ってましたが、そこまで変わらない(または遅い)かなと思います(既存ワークフローは他の処理も混じってるので単純な比較はできてないですが)。

declare input_dataset string default 'raw_dataset';
declare output_dataset string default 'masked_dataset';
declare mask_master_table string default 'master.masking_columns';

# temp vars
declare target_table_names array<string>;
declare target_table_name string;
declare i int64 default 0;
declare replace_columns_statement string;

# e.g. ['users', 'profiles', 'corporations', ...]
execute immediate format("""
  select array_agg(table_name)
  from %s.INFORMATION_SCHEMA.TABLES
""", input_dataset) into target_table_names;

while i < array_length(target_table_names) do
  set target_table_name = target_table_names[safe_offset(i)];

  # マスクするカラムからreplace()のパラメータを生成
  # e.g. to_hex(sha256(cast(full_name))) as full_name, to_hex(sha256(cast(email))) as email
  execute immediate format("""
    select string_agg('to_hex(sha256(cast(' || column_name || ' as string))) as ' || column_name, ',')
    from %s
    where table_name = '%s'
  """, mask_master_table, target_table_name) into replace_columns_statement;

  execute immediate format(
    """
      create or replace table %s as
      select * %s from %s
    """
    , output_dataset || '.' || target_table_name
    , ifnull(format('replace(%s)', replace_columns_statement), '')
    , input_dataset || '.' || target_table_name
  );

  set i = i + 1;
end while