BigQueryだけでRedashから個人情報見えなくする方法解説

f:id:tkgwy:20190131220648j:plain

データエンジニアの瀧川です。

Redash使ってますか！？

Redashが出てここ数年、加速度的に導入事例が聞かれるようになり、もうスタートアップのデファクトスタンダードとも言えるのではないでしょうか。

その導入事例の中でよく聞かれるのが、 全社的に Redashを公開して、だれでもデータにアクセスできるようにしたよという話です。

確かにRedashは、クエリの蓄積・共有が容易ですし、グラフやダッシュボード機能がそのまま営業活動に使えたりと利点はたくさんあります。

ただ、ここで問題になるのが、個人情報を始めとする 一般ユーザが見ることのできないデータ をどのように制御するかだと思います。

本記事では、弊社で使っている BigQueryだけ で、個人情報をマスキングしたデータを用意し、権限設定でデータへのアクセスを制御する方法を紹介しようと思います！

目標
方法
まとめ

目標

例として、 production データセットに users テーブルが存在し、カラムとして (id int64, email: string, created_at) を持つとします。(以下のような感じですね！)

f:id:tkgwy:20190131200920p:plain

そのときに email カラムがマスキングされたテーブルだけ参照できる common-bigquery サービスアカウントを作成できればゴールとします。

(Redashへは、作成したサービスアカウントを登録すれば、個人情報にアクセスできない)

方法

以下の手順で実施していきます。

元テーブルからマスクテーブルへのコピーするクエリを作成
クエリを定期実行
サービスアカウントにアクセス権を付与
データセットの許可リストにサービスアカウントを追加

元テーブルからマスクテーブルへのコピーするクエリを作成

「マスキングしたいカラム以外をselect」ってどのように書いてますか？

単純にやるなら以下ですよね。

select id, created_at from production.users

しかし、BigQueryには replace や except というステートメントが存在して、「hogehogeなカラムだけを除く、置換する」という表現をすることができます！これを使って上のSQLを書き直すと以下のようになります。

select * except(email) from production.users

Standard SQL Query Syntax | BigQuery | Google Cloud

上記のステートメントと、UDF(User Defined Function)を使って、 production.users から email のドメイン以外をマスキングしてselectするクエリが以下になります！ (UDFは複雑に見えますが、emailであれば@マークでsplitしてローカル部だけハッシュ化しているだけで、集計作業なんかでドメインを残したほうがいい場合のクエリですね)

#standardSQL
create temp function mask(str string, typ string) as (
case typ
  # emailはローカル部だけマスクし、ドメイン情報は残す
  when 'email' then (
    select format('%s@%s', to_hex(sha256(local)), domain) from (
      select split(str, '@')[safe_offset(0)] as local
           , split(str, '@')[safe_offset(1)] as domain
    )
  )
  else to_hex(sha256(str))
end
);
select * replace (mask(email, 'email') as email) from production.users