BigQueryで主キーは設定しないけど主キーの一意性は担保したいあなたに〜プロシージャと解説付き〜

元教師

こんにちは！データサイエンティストの青木和也（https://twitter.com/kaizen_oni）です！

今回の記事では「どうしてもGCPのBigQuery上のテーブルに主キーを設定させてくれない」という状況かつ「主キーにあたるカラムの一意性は担保したい」というわがままな要望をもらった時に、プロシージャを使って上手いこと解決する方法を紹介します！

時間がない人のための3行要約

引数にテーブル名と主キーにあたるカラム名を持つプロシージャを使えば解決
引数の代入にはEXECUTE IMMEDIATEという動的SQLを用いる
動的SQLは変数への代入ができないので一時テーブルを作成して、後から変数に代入する

一意性をチェックするプロシージャを見てみる
プロシージャの各コードで何をしているのか見てみる
まとめ

一意性をチェックするプロシージャを見てみる

以下に一意性をチェックするプロシージャのコードを紹介いたします。

[SCHEMA]の部分には実際に一意性をチェックしたいテーブルが存在するスキーマ名を代入してください。

CREATE OR REPLACE `[SCHEMA].CHECK_COLUMN_UNIQUENESS` (col_name STRING, table_name STRING)

BEGIN

DECLARE totalRowCount   INT64;
DECLARE UniqueRowCount  INT64;

-- 引数のテーブル名を使用して総データ数を計算し、変数totalRowCountに代入する
EXECUTE IMMEDIATE
   FORMAT("""SELECT COUNT(*) FROM [SCHEMA].%s""", table_name)
      INTO totalRowCount;

-- 引数のカラム名とテーブル名を使用してユニークデータ数を計算し、変数UniqueRowCountに代入する
EXECUTE IMMEDIATE
   FORMAT("""SELECT COUNT(*) FROM (SELECT DISTINCT %s FROM [SCHEMA].%s)""", col_name, table_name)
      INTO UniqueRowCount;

if totalRowCount != UniqueRowCount then
    Raise USING MESSAGE = CONCAT('結合キー', col_name, 'に重複が見つかりました。重複レコード数: ', totalRowCount - UniqueRowCount);
else 
    SELECT CONCAT('テーブル', table_name, 'のカラム', col_name, 'は一意です。');
end if;
END;

プロシージャの各コードで何をしているのか見てみる

それではプロシージャの各コード部分で何を行なっているのかを、詳細に解説していきたいと思います。

プロシージャの定義

CREATE OR REPLACE `[SCHEMA].CHECK_COLUMN_UNIQUENESS` (col_name STRING, table_name STRING)

BEGIN

このコードではプロシージャを定義しています。

引数としてはテーブル名のtable_nameと一意性をチェックするカラム名のcol_nameを定義しています。

[SCHEMA]の部分には、BigQuery上でアクセス可能なスキーマ名を入力してください。

またBEGIN句によって、ここから先がプロシージャの中身であることを明示します。

変数の定義

DECLARE totalRowCount   INT64;
DECLARE UniqueRowCount  INT64;

このコードでは総データ数とユニークデータ数を比較するための変数を定義しています。

総データ数を変数に代入

-- 引数のテーブル名を使用して総データ数を計算し、変数totalRowCountに代入する
EXECUTE IMMEDIATE
   FORMAT("""SELECT COUNT(*) FROM [SCHEMA].%s""", table_name)
      INTO totalRowCount;

このコードでは引数table_nameを使用して、テーブルから総データ数を取得し、変数 totalRowCountに代入しています。

EXECUTE IMMEDIATEとは？

EXECUTE IMMEDIATEは文字列と変数の組み合わせの式を受け取り、有効な SQL ステートメントをその場ですぐに生成して即座に実行することができるBigQueryの便利機能です。

今回のクエリでは、文字列の中の%sを変数table_nameで置き換え、そのSQL文をEXECUTE IMMEDIATEで即時実行することによって、テーブル名を変数で柔軟に切り替えて総データ数を取得することを可能にしています。

一意なデータ数を変数に代入

-- 引数のカラム名とテーブル名を使用してユニークデータ数を計算し、変数UniqueRowCountに代入する
EXECUTE IMMEDIATE
   FORMAT("""SELECT COUNT(*) FROM (SELECT DISTINCT %s FROM [SCHEMA].%s)""", col_name, table_name)
      INTO UniqueRowCount;

このコードでは、引数col_nameとtable_nameを使用して、テーブルからカラムcol_nameが一意であるようなデータ数を取得し、変数UniqueRowCountに代入しています。

なお、このコードでは以下の手順にしたがって一意なデータ数を求めています。

テーブルtable_nameからカラムcol_nameについて重複しないようにテーブルを抽出します
抽出したテーブルのデータ数を数えます

なお、COUNT(DISTINCT %s)を利用せずに相関サブクエリを使用している理由は、col_nameに複数のカラム名をコンマ区切りで入れたとしてもそれらのカラムについて一意なデータ数を数えることができるようにするためです。

-- 例: col_name = 'A, B'、table_name = 'TABLE1'とした時
SELECT COUNT(*) FROM ( SELECT DISTINCT A, B FROM [SCHEMA].TABLE1 )

総データ数と一意なデータ数を比較、異なる場合はエラーをあげる

if totalRowCount != UniqueRowCount then
    RAISE USING MESSAGE = CONCAT('結合キー', col_name, 'に重複が見つかりました。重複レコード数: ', totalRowCount - UniqueRowCount);
else 
    SELECT CONCAT('テーブル', table_name, 'のカラム', col_name, 'は一意です。');
end if;
END;

このコードでは、if文で総データ数とユニークデータ数が一致するかをチェックしています。

一致しなかった時はRAISE UNING MESSAGEを使用してエラーメッセージを吐いて、処理を中断しています。

一致した場合は適当なメッセージを出すように設定しています。

最終行の「END;」はプロシージャの始まりを意味する「BEGIN」に対応する句です。