BigQueryスクリプトで複数テーブルの行数比較を自動化する

概要

前回の記事では、BigQueryスクリプトのFOR INループで複数テーブルを一括処理する方法を紹介しました。

今回はこれを応用して、EXECUTE IMMEDIATEのINTO句とUSING句を活用し、複数テーブルの行数比較結果をテーブルに保存するスクリプトを紹介します。
テーブル移行時のデータ検証等に活用できるかと思います。

BigQueryスクリプト

DECLARE dataset_name STRING DEFAULT 'my_dataset';
DECLARE result_table STRING DEFAULT 'my-project.my_dataset.comparison_results';

DECLARE date_suffix STRING;
DECLARE table1_id STRING;
DECLARE table2_id STRING;
DECLARE table1_count INT64;
DECLARE table2_count INT64;
DECLARE is_equal BOOL;

BEGIN
  -- 行数比較結果保存用テーブルの作成
  CREATE TABLE IF NOT EXISTS `my-project.my_dataset.comparison_results` (
    table1 STRING,
    table2 STRING,
    table1_count INT64,
    table2_count INT64,
    is_equal BOOL,
    checked_at TIMESTAMP
  );

  FOR record IN (
    SELECT table_id
    FROM `my-project.my_dataset.__TABLES_SUMMARY__`
    WHERE REGEXP_CONTAINS(table_id, r'^target_table1_\d{8}$')
    ORDER BY table_id
  )
  DO
    SET table1_id = record.table_id;
    SET date_suffix = REGEXP_EXTRACT(table1_id, r'(\d{8})');
    SET table2_id = FORMAT('target_table2_%s', date_suffix);

    -- INTO句で結果を変数に格納
    EXECUTE IMMEDIATE FORMAT("""
      SELECT COUNT(1) FROM `my-project.%s.%s`
    """, dataset_name, table1_id)
    INTO table1_count;

    EXECUTE IMMEDIATE FORMAT("""
      SELECT COUNT(1) FROM `my-project.%s.%s`
    """, dataset_name, table2_id)
    INTO table2_count;

    SET is_equal = table1_count = table2_count;

    -- USING句でパラメータバインド
    EXECUTE IMMEDIATE FORMAT("""
      INSERT INTO %s (table1, table2, table1_count, table2_count, is_equal, checked_at)
      VALUES (@table1, @table2, @table1_count, @table2_count, @is_equal, CURRENT_TIMESTAMP())
    """, result_table)
    USING
      table1_id AS table1,
      table2_id AS table2,
      table1_count AS table1_count,
      table2_count AS table2_count,
      is_equal AS is_equal;

  END FOR;
END;

結果確認のsampleクエリ

比較結果をテーブルに格納しているので、以下のように確認できます。

SELECT *
FROM `my-project.my_dataset.comparison_results`
WHERE is_equal = FALSE
ORDER BY checked_at DESC;

前回からの応用ポイント

INTO句：クエリ結果を変数に格納する

通常のEXECUTE IMMEDIATEはクエリを実行するだけですが、INTO句を使うと結果を変数に格納できます。

-- 結果が変数 table1_count に入る
EXECUTE IMMEDIATE "SELECT COUNT(1) FROM `my-project.my_dataset.my_table`"
INTO table1_count;

これにより取得した値を後続の処理（比較やINSERTなど）で使えるようになります。

USING句：変数の値を安全にSQLへ渡す

INSERT文などで変数の値を使いたい場合、FORMATで直接埋め込む方法もありますが、USING句を使うとより安全に値を渡せます。

EXECUTE IMMEDIATE """
  INSERT INTO my_table (col1, col2)
  VALUES (@param1, @param2)
"""
USING
  value1 AS param1,
  value2 AS param2;

---

前回同様、FOR INループは1テーブルずつ逐次実行されるため対象テーブルが多い場合はご注意いただきたいですが、今回は検証結果をテーブル格納しているので、行数比較レベルのシンプルな検証であれば対象テーブルが-1000程度まではそこまで時間もかからないかな、といった印象です。(実行プロジェクトの混み具合次第ではありますが...)

以上です。
どなたかのお役に立てれば幸いです。

関連資料

• BigQueryスクリプトのFOR INループで複数テーブルを一括処理する - 前回の記事
• Procedural language | BigQuery - EXECUTE IMMEDIATE、INTO、USINGの構文
• FORMAT関数 - String functions | BigQuery
• INFORMATION_SCHEMA の概要 | BigQuery