BigQuery 認可済みの UDF を利用した安全な ID マッチング
はじめに
自社サービスの顧客理解や、効率的な広告配信、パーソナライズした顧客アプローチを行うために、顧客データを収集・統合・分析を行う企業が非常に増えています。
その中の多くの企業は、自社サービスの顧客データだけでの分析を行っているのではなく、その企業・グループ会社が保有する様々なサービスの 1st Party Data（CRM） とのデータ統合や、もしくはパートナー・外部企業が持つ顧客データ（DMP）とのデータ統合を行い、顧客データをリッチにすることで より深い顧客データ分析を行っています。その ID データ統合の手法のことを「ID マッチング/名寄せ/突合」などと呼ぶことが多いです。
このような社内外とのデータ交換・データ共有の取り組みが一般的になってきたものの、実際に行っているプロセスを見ると、下記のような手法が散見されます。みなさんも身に覚え、聞き覚えがないでしょうか？？？
- オンライン ストレージ上で顧客データのファイルを共有
- 自社で利用している顧客データのデータベースのテーブルを直接共有
従来型データ共有の仕組みの課題
このような従来型のデータ共有プロセスは下記のような課題があります。
① 高価で壊れやすいデータ パイプライン
② 不要なデータ複製のコスト
③ データ提供の遅延 / 非同期
④ ガバナンス / セキュリティ / プライバシー上のリスク
まずオンライン ストレージ上でのデータ共有にはデータベースからデータを抽出してファイル保存し、送信するバッチ データ パイプラインが必要です。これらのパイプラインは実行にコストがかかるだけでなく、ソースデータを変更すると破損する可能性があります（上記①）。
これにより、特に大容量のデータセットの場合、データの複数のコピーが発生し不要なコストや処理・コピーによるデータ遅延が発生します（上記②③）。データの遅延はビジネス上の決定が遅れることにも繋がります。
最後にこれらのデータ パイプラインの構築・メンテナンスには手間やコストがかかる上、依頼が臨時で緊急度高く入ることが多いためガバナンス上追跡/トレースできていないこと、また不要なデータ（ID が一致しなかったため本来は共有する必要がないデータ）まで共有してしまっていることが多くあります（上記④）。
つまり従来のデータ共有手法だと、組織の境界を越えて、安全でスピーディでプライバシーが保護されたデータ共有が実現できているとは言えません。では、どのようにすればきちんとしたデータ共有が可能になるのでしょうか？それを実現するのが今回ご紹介する BigQuery 認可済みの UDF（Authorized UDFs ）です！
※ プライバシー データ等機密データが格納された特定の列のみ共有したくない場合は、BigQuery の列レベルのセキュリティによるアクセス制限を利用してください。
BigQuery 認可済みの UDF
UDF とはユーザー定義関数（User Defined Function）のことで、データベースの SQL に標準的に用意されているSUM, COUNT, AVG のような関数をユーザー自身が独自に定義できるものです（参考：標準 SQL ユーザー定義関数）。ある数値を元に計算させたり日時計算したり、別のテーブルからデータを呼び出したり、様々な用途で利用されます。通常の UDF を実行するには、その UDF を実行するユーザー自身が、UDF 内で呼び出すテーブルへのアクセス権が必要になります。
一方、BigQuery の認可済みの UDF は、特定のデータセットへのアクセスを認可された UDF です。UDF とは異なり UDF を呼び出すユーザーにテーブルへのアクセス権がない場合でも実行でき、条件に一致したデータのみ取得することができます。
つまり、上図のように
- 共有先ユーザー/企業には、認可済みの UDF の実行権限のみ付与（認可済みの UDF では必要最低限のデータのみ返却するように定義）
- 共有データが格納されているテーブルへのアクセス権は付与しない
とすることで、共有データが格納されているテーブルをそのままデータ共有に利用することが可能になるため、前述のデータ共有/ID マッチングのプロセスの課題は全てクリアすることができます！では次の章では実際に BigQuery 認可済みの UDF を利用したデータ共有を試しながら説明していきます。
※ 共有する ID は類推しづらい ID にしてください。例えば、ID として 100, 101, 102 … のようなシーケンシャルな値を利用すると、データ共有先ユーザーが「 102 の次は 103 だろう」と容易に類推でき、認可済みの UDF で取得する ID リストに含まれてしまうリスクがありますのでご注意下さい。
BigQuery 認可済みの UDF の例
ではここからは実際にデモを交えながら説明していきます。今回の構成はデータ共有元（provider）側に下記データ/ UDF を用意することにします。
- 非公開データセット（provider_private_dataset）と、顧客データを格納した非公開テーブル（provider_user_table）
- 公開データセット（provider_public_dataset）と、provider_user_tableのデータを呼び出す認可済みの UDF （retrieve_matched_data）
provider_user_table と subscriber_user_table はそれぞれ下記のデータを持っており、データ共有先（subscriber） は データ共有元（provider）のデータに対して uuid をキーにして ID マッチングを行い、自社のデータをよりリッチ（デモグラの付与等）にしたいと考えています。
一方、データ共有元（provider）は、データ共有先（subscriber）に対して自社のユーザー ID（user_id）や個人を特定する情報（名前、メールアドレス）の他、ID マッチングしなかったデータを共有したくないと考えます。そこでデータ共有元（provider）側では、顧客データを格納するテーブル provider_user_table への直接のアクセス権は提供せず、provider_user_table のデータを呼び出す認可済みの UDF （retrieve_matched_data）への実行権限のみ設定するようにします。
認可済みの UDF の作成
下記 SQL により、認可済みの UDF（retrieve_matched_data） を共有データセット（provider_public_dataset） 内に作成します。UDF は uuid の配列を受け取り、非公開テーブルである provider_user_table の中で uuid が一致するデータのみ抽出する SELECT ステートメントが含まれ、結果として配列を返却するようにしています。
#standardSQL
CREATE OR REPLACE FUNCTION provider_public_dataset.retrieve_matched_data(list ARRAY<STRING>) RETURNS ARRAY<STRUCT<uuid STRING, age INTEGER, gender STRING, job STRING, purchase_times INTEGER>> AS ((
SELECT ARRAY_AGG(STRUCT(
uuid,
age,
gender,
job,
purchase_times
))
FROM provider_private_dataset.provider_user_table t
WHERE t.uuid IN UNNEST(list)
));次に、データセット（provider_public_dataset）の設定画面上で「ルーティンを承認」ボタンをクリックし、下図のように、認可する UDF のプロジェクト ID・データセット ID・ルーティン ID を選択し、承認します。ルーティン（routine）とは 永続 UDF やストアドプロシージャのような関数のことを表しています。
実際に呼び出すときは、下記のように呼び出します（例：uuid が aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa に一致するレコードの取得。戻り値が配列のため(この場合の結果は 1 行ですが) UNNEST しています）。
#standardSQL
SELECT * FROM UNNEST ({{PROVIDER_PROJECT_ID}}.provider_public_dataset.retrieve_matched_data(["aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"])
);上記クエリはデータ提供元ユーザであれば認可済みの UDF に対して実行権限があるため実行できますが、データ提供先ユーザは権限がなくこの時点ではエラーになります。
認可済みの UDF の呼び出し権限の付与
データ提供先（subscriber）に対して上記の認可済みの UDF の実行権限を付与していきます。まず認可済みの UDF が格納されているデータセット（provider_public_dataset）に対する bigquery.dataViewer ロールを付与します。（実際は「最小限の権限」の原則に従い bigquery.routines.get のみ持つカスタムロールを作成して付与してください）
BigQuery の管理コンソールで行う場合は、データセットの設定を開き、「共有データセット」ボタンを押し、下記画面で設定します。
この時点で共有先ユーザーは retrieve_matched_data 関数を呼び出す権限を持っていますが、provider_private_dataset のテーブルにアクセスできません。ユーザーが関数を呼び出そうとすると、次のようなエラー メッセージが表示されます。
Access Denied: Table ｛｛PROVIDER_PROJECT_ID｝｝:provider_private_dataset.provider_user_table: User does not have permission to query table ｛｛PROVIDER_PROJECT_ID｝｝:provider_private_dataset.provider_user_table.bq コマンドライン ツールを使用して、次のように show コマンドを実行します。公開したくない方のデータセットの設定を出力しています。
bq show --format=prettyjson provider_private_dataset > dataset.json出力は dataset.json という名前のローカル ファイルに保存されます。 dataset.json を編集して、次の JSON オブジェクトを access 配列に追加します。公開したくない方のデータセットにおいて、retrieve_matched_data のルーティンに対してはアクセス権限を付与しています。
{
"routine": {
"datasetId": "provider_public_dataset",
"projectId": "{｛PROVIDER_PROJECT_ID｝｝",
"routineId": "retrieve_matched_data"
}
}bq コマンドライン ツールを使用して、次のように update コマンドを実行して非公開データセット(provider_private_dataset) の設定を更新します。
bq update --source dataset.json provider_private_dataset成功すると、Dataset '{{PROVIDER_PROJECT_ID}}:provider_private_dataset' successfully updated.というメッセージが返却されます。これでデータ共有先（subscriber）は認可済みの UDF を実行できるようになります。
先に登場したクエリでテストするのも良いですが、今回は当初の目的に従って、認可済みの UDF で取得したデータと、データ共有先（subscriber）が持つデータ(subscriber_user_table)と ID マッチングさせてみます。
下記のクエリでは、このような処理をしています。
- subscriber_user_table に保存されている ID 情報を配列として渡す
- 認可済み UDF 内では provider_user_table の中で ID に一致するレコードの age, gender, job, purchase_times を取得
- subscriber_user_table が持つデータ（uuid, visit_times, first_visit, cv_times）と合わせて表示
#standardSQL
SELECT
t1.uuid,
t2.age,
t2.gender,
t2.job,
t2.purchase_times,
t1.visit_times,
t1.first_visit,
t1.cv_times
FROM `{{SUBSCRIBER_PROJECT_ID}}.subscriber_private_dataset.subscriber_user_table` t1
LEFT OUTER JOIN
(SELECT * FROM
UNNEST ({{PROVIDER_PROJECT_ID}}.provider_public_dataset.retrieve_matched_data(
ARRAY (
SELECT
uuid
FROM `{{SUBSCRIBER_PROJECT_ID}}.subscriber_private_dataset.subscriber_user_table`
)
) ) ) t2
on t1.uuid = t2.uuid;結果は下図のようになります。データ共有元（provider）が持つ、ID マッチングできなかったデータがデータ共有先（subscriber）に渡されていないことがわかります。
まとめ
以上により、BigQuery の認可済みの UDF を利用することで元のテーブルへのアクセス権限を付与することなく、かつ、必要なデータのみフィルタリングした上でデータ共有することができるようになることが理解できたと思います。データ共有・ID マッチングを行うために、データ パイプラインを別途構築したり、また安全性に不安を覚えながら作業されていたり、別途データ共有の仕組み（Data Clean Room [データ クリーンルーム]など）の利用を検討している方も多いと思いますが、BigQuery でも標準機能でセキュアにスピーディに実現できますので、ぜひ利用してみて下さい！
