バイセル Tech Blog

バイセル Tech Blogは株式会社BuySell Technologiesのエンジニア達が知見・発見を共有する技術ブログです。

バイセル Tech Blog

全社員がSQLを書けるようBQの権限やデータソースを整理して運用している話（後編）

こちらは バイセルテクノロジーズ Advent Calendar 2022 の22日目の記事です。 前日の記事は 杉田さんの「手動送金地獄をシステム化して作業時間を50%削減した話」でした。

こんにちは！ 株式会社バイセルテクノロジーズのテクノロジー戦略本部データサイエンス部の高谷です。

本記事は全社員がSQLを書けるようBQの権限やデータソースを整理して運用している話の後半です。

はじめに

前編のざっくりおさらいです。

  • Bizメンバー（バイセルのコーポレート部門やビジネス部門）がSQLを書ける環境を構築した話
  • Bizメンバーの各部代表者としてアンバサダーがいる
  • バイセルグループはバイセルとリユース事業の他グループ会社の数社で構成、データ基盤としてもバイセル本体だけではなくグループ全体で構築
  • データ基盤はGCPプロジェクトのBigQueryを利用

バイセルのデータ基盤

バイセルでは以下イメージでデータ基盤を構築しています。

  • バイセルグループ全体のデータ基盤
  • クレンジング層（※）
  • 会社ごと

※クレンジング層はデータウェアハウスとデータマートの中間のようなイメージで、個人情報の除外やBizメンバーが利用しやすい形に加工された層

クレンジング層は全体のデータ基盤が持つデータをViewで参照する形となっています。

データの鮮度をニアリアルタイムにという観点からですが、この辺りも権限設定に関わります。

最終的な設定内容

Bizメンバーや開発部などをグループ分けし、プロジェクト/データセット単位で必要な権限を整理した結果が以下です。

GROUP PJ オーナー BigQuery
データ編集者 		BigQuery
データ閲覧者 		BigQuery
ジョブユーザー
運用 全体のデータ基盤 ●
クレンジング層 ●
会社ごとのPJ ●
開発部 全体のデータ基盤 ●
クレンジング層 ●(※) ●
会社ごとのPJ ● ● ●
アンバサダー 全体のデータ基盤 ●
クレンジング層 ●(※) ●
会社ごとのPJ ● ● ●
Bizメンバー 全体のデータ基盤 ●
クレンジング層 ●(※) ●
会社ごとのPJ ● ●

（※）必要なデータセットに対して付与

また権限のみで対応しきれないところを「承認済みデータセット」という機能を利用しています。

上記を含めた、アンバサダーでアクセスした場合の権限設定の全体像が以下です。

実際にやったこと

権限調査

GCPやBigQueryで用意されている権限の種類が豊富で適切な権限の整理が必要でした。

結果、以下を付与する形としたため整理の流れを説明します。

権限 出来ること 対象
BigQuery データ編集者 Viewの作成 アンバサダー
BigQuery データ閲覧者 データの閲覧 Bizメンバー、アンバサダー
BigQuery ジョブユーザー SQLの実行 Bizメンバー、アンバサダー

新しく作る or 既存のものを使うか検討

GCPには既存で用意されている権限とは別に用途に合わせてカスタムロールというものが作成できます。

本調査の中でカスタムロールを作成する必要があるか議論しましたが、「なるべくシンプルな形としておきたい」という理由から既存の権限を利用する形となりました。

どのような権限があるか調査

公式の記事を参照し、どのような権限があるかを確認しました。

ここで少し混乱したポイントとしては「権限は様々な粒度ごとに設定できる」という点です。

BigQueryはざっくり以下のような構成となっています。

この「Project」「dataset」「View」「table」や「カラム」に対して権限を設定することが可能で、

「プロジェクトに対してのみ付与できる権限」や「データセット配下のみに付与できる権限」など、一口にBigQueryといっても付与できる権限の粒度が多数に分かれている状態です。

挙げたポイントに留意しつつ、この時点で必要な権限を以下まで絞込みました。

  • BigQuery データ編集者
  • BigQuery データ閲覧者
  • BigQuery ジョブユーザー
  • BigQuery メタデータ閲覧者

実際に権限を変更してみて挙動を確認

部内メンバーや一部先行で協力してもらっていたBizメンバーの権限を変更し、想像しうる挙動か否かを確認しました。

結果、3つの権限を付与する形で進めることとなりました。

  • BigQuery データ編集者
  • BigQuery データ閲覧者
  • BigQuery ジョブユーザー

先の調査で含めていた「BigQuery メタデータ閲覧者」は、データセットやデータセット内のテーブルやViewの一覧表示に必要と考えピックアップしていましたが挙動確認時に「BigQuery データ閲覧者」のみでこと足りることが分かったため除外しています。

データセット設計＆権限設計

前提として以下となります。

  • データセット

    • 会社ごとのプロジェクト（バイセル、グループ会社A…）内のデータセット

  • 権限

    • バイセルグループのデータ基盤全体におけるアンバサダーとBizメンバーの権限

データセット設計

部署ごとに作成しています。

└── バイセル
    ├── Biz部署_1
    ├── Biz部署_2
    └── Biz部署_3

部署変更が発生した際に増減するリスクがありますが、ある程度のBizメンバーが自由に利用できるかつ運用も煩雑にならない粒度としてこの形式を選択しています。

権限設計

アンバサダーとBizメンバーの権限は下表のイメージで付与する形で検討していましたが、ある課題が発覚しました。

GROUP PJ BigQuery
データ編集者 		BigQuery
データ閲覧者 		BigQuery
ジョブユーザー
アンバサダー 全体のデータ基盤 ●
クレンジング層 ●(※) ●
会社ごとのPJ ● ● ●
Bizメンバー 全体のデータ基盤 ●
クレンジング層 ●(※) ●
会社ごとのPJ ● ●

(※)データセットごと

最上層にも権限を付与しなければならない

データセットやプロジェクトの権限のみで対応しようとすると

全体のデータ基盤やクレンジング層の全てのデータに閲覧権限を付与しなければならず個人情報が閲覧できてしまう

という問題が発生していました。

下層→上層プロジェクトへのデータ参照がViewであることから、最上層のデータセットにまで閲覧権限を付与しなければならず

最上層は個人情報を含んだデータを保持しているため再検討が必要になりました。

改善内容

上記の課題を解決するべく、いくつか案を検討しましたが結果的に3で対応しました。

  1. テーブルの粒度で権限を付与
  2. カラムの粒度で権限を付与
  3. 承認済みデータセットを利用する

1,2に関しては権限のみで対応可能ですが、粒度が細かすぎる上に数も膨大でこの時点で対応するのは現実的ではないと判断しました。

承認済みデータセットとは

承認済みデータセットを使用すると、指定したデータセット内のすべてのビューが、2 番目のデータセット内のデータにアクセスすることを承認できます。

公式ドキュメントから引用した内容です。

つまり「参照元のデータセットで参照先のデータセットを承認したらその上層には権限が必要ない」という機能です。

参照先データセットの数だけ登録が必要ですが権限付与はBizメンバーが参照するデータセットのみでよい状態になりました。

承認方法もシンプルでコンソール上であれば参照元のデータセットに参照先のデータセットを登録するだけでOKです。

また、承認はデータセットだけではなくViewや関数などにも設定できますが、今回はデータセットの単位としています。

以上のことから、権限と承認済みデータセット機能を組み合わせることで進めることとなりました。

Googleグループの作成

先の権限設計の際にBizメンバーへの権限付与方法として「Googleグループ単位の付与にする」という方針が決まっていました。

グループは以下2つを作成しています。

  • Bizメンバー
  • アンバサダー

Bizメンバーにはアンバサダーも含まれるため重複しますが、データ基盤を参照する際は影響なく問題ないと判断しています。

必要な権限付与

ここまでで必要な準備が整ったので権限を付与していきます。

コマンドとコンソールを利用して進めました。

プロジェクト

gclod コマンドを利用

gcloud projects get-iam-policy myproject //IAMの確認 
// プロジェクトへ権限付与
gcloud projects add-iam-policy-binding myproject --member group:bizmenber@XX.com --role roles/bigquery.dataViewer
gcloud projects add-iam-policy-binding myproject --member group:bizmenber@XX.com --role roles/bigquery.dataEditor
gcloud projects add-iam-policy-binding myproject --member group:bizmenber@XX.com --role roles/bigquery.jobUser

データセット

データセットは数が少なかったこともありコンソール上で実施しましたが、bqコマンドやAPIでも実行可能です。

動作検証

付与した権限が意図した挙動か最終確認していきます。 確認の流れとしては以下です。

  1. サービスアカウントの用意
  2. サービスアカウントへの権限付与
  3. サービスアカウントでbqコマンドのSQL実行

今まではコンソール上で権限の確認を実施していたのですが、パターンも多く実ユーザの権限の付け替えは非効率でした。

そのためサービスアカウントを利用しbqコマンドでの確認方法へ変更しています。

サービスアカウントの用意

今回は事前に権限検証用のサービスアカウントが存在していたのでそれを利用しました。

※バイセルグループ全体のデータ基盤　のプロジェクトで作成されたもの

サービスアカウントへの権限付与

検証対象ユーザによって権限が異なるため、1つのユーザの検証が完了次第権限のサービスアカウントの権限を付け替えながら進めていきます。

権限付与はコンソール上で実施しました。

サービスアカウントでbqコマンドのSQL実行

サービスアカウントでSQLを実行します。

Bizメンバーの権限が意図した挙動かどうか確認した際のコマンドをサンプルとして掲載します。

// 利用サービスアカウントの確認
>gcloud config list
[accessibility]
screen_reader = False
[core]
account = test@buysellproject.iam.gserviceaccount.com
disable_usage_reporting = True
project = buysellproject

Your active configuration is: [service-buysellproject]

// クレンジング層のデータにアクセス可能か
>bq query --use_legacy_sql=false SELECT id,name FROM cleansing.test_dataset.master_test
+----+----------------------------+
| id |            name            |
+----+----------------------------+
|  1 |           XXXX             |
+----+----------------------------+

//クレンジング層の対象データセット内を一覧表示できるか
>bq ls cleansing:test_dataset
                    tableId                     Type   Labels   Time Partitioning   Clustered Fields
 --------------------------------------------- ------ -------- ------------------- ------------------
  master_test                                   VIEW
  tran_test                                     VIEW

//バイセルグループ全体のデータ基盤のデータがSELECTがNGか
>bq query --use_legacy_sql=false SELECT id,name FROM buysell_all.test_dataset_1.master_test_1
BigQuery error in query operation: Error processing job 'buysell_all:bqjob_XXXXXXXX_XXXXXXXXXXXXXX': Access Denied: Table buysell_all:test_dataset_1.master_test_1: User does not have permission to
query table buysell_all:test_dataset_1.master_test_1, or perhaps it does
not exist in location asia-northeast1.

//バイセルグループ全体のデータ基盤の対象データセット内の一覧表示がNGか
>bq ls buysell_all:test_dataset_1
BigQuery error in ls operation: Access Denied: Dataset buysell_all:test_dataset_1:
Permission bigquery.tables.list denied on dataset buysell_all:test_dataset_1 (or it
may not exist).

まとめ

今回は実際にバイセルのデータ基盤で採用している権限や、承認済みデータセットの機能、動作検証の流れをお伝えしました。

運用が始まり、Bizメンバーが積極的にSQLを書いて自分でデータを見ていますが、新たな課題も発生しているのでより効率化/最適化できるように改善を進めます。

最後にBuySell Technologiesでデータサイエンティストを募集しています。興味がある方はぜひご応募ください！

herp.careers

明日の バイセルテクノロジーズ Advent Calendar 2022 は 田中さんによる「環境構築をコマンドでまとめてみた」です。