管理画面からBigQueryを使ってサクセスする

ハッピーホリデー！id:cockscombです！！この記事ははてなエンジニアAdvent Calendarの8日目のエントリです。

サービス開発をしていると、限られた管理者のみがアクセスできる画面、つまり「管理画面」を作る必要に迫られます。管理画面では、データベースに登録されたさまざまな情報を検索、閲覧、編集できます。もちろんデータの扱いはプライバシーポリシーに則って、厳密に権限管理をしたり、操作ログを残したりします。

管理画面から情報を検索する

管理画面で、データベース上の情報を検索するという部分について考えます。例えば、特定のメールアドレスで登録している利用者を探したいケースがあるとします。

SELECT
    *,
FROM
    users
WHERE
    mail = ?

MySQLなどの典型的なRDBMSでは、このようなSQLを発行するでしょう。mailカラムにインデックスがあれば（ふつうはあるでしょう）、すぐに結果が返ってくると思います。

ところがあるとき、Gmailのエイリアス機能を使ってユーザー登録しているが、エイリアスがどのようなものか不明である、あるユーザーを検索したい、となったらどうでしょう。元々のアドレスはexample@gmail.comで、ユーザー登録の時にexample+test@gmail.comでユーザー登録したかもしれないし、e.x.a.m.p.l.e@gmail.comでユーザー登録したかもしれない、というような場合です。予め正規化しておけばいいわけですが、残念ながら正規化ずみのカラムはありません。

SELECT
  *,
FROM
  users
WHERE
  ENDS_WITH(mail, "@gmail.com")
  AND REGEXP_REPLACE(REGEXP_EXTRACT(mail, "^([^@]+)@"), "(\\.|\\+.*$)", "") = "example"

がんばってSQLを書くと、このような感じでしょうか。言うまでもなくインデックスは効きそうにありません。

BigQueryを使う

いろいろ考えてみると、管理画面からデータを検索するのには、BigQueryのようなものが向いていると気づきます。予め都合のいいインデックスを網羅的に作っておくのは難しく、効率的ではありません。最近では、データ分析用にBigQuery上にデータが置かれていることも多く、それが再利用可能な場合もあります。先ほどのGmailのエイリアスを探すSQLはBigQueryなら問題なく処理できます。

さらにBigQueryの列レベルのセキュリティによるアクセス制限の機能を使えば、きめ細やかなアクセス制限も可能です。

ということで、サーバサイドはGoで開発していたので、cloud.google.com/go/bigqueryを使って簡単にBigQueryと連携できます。例えばクエリを実行するのは次の通りで、返ってきたジョブから実際の値を読み取ります。

import (
    "context"

    "cloud.google.com/go/bigquery"
)

type bigQueryRepo struct {
    client *bigquery.Client
}

func (r *bigQueryRepo) runQuery(ctx context.Context, query string, parameters []bigquery.QueryParameter) (*bigquery.Job, error) {
    q := r.client.Query(query)
    q.Parameters = parameters
    job, err := q.Run(ctx)
    if err != nil {
        return nil, err
    }
    status, err := job.Wait(ctx)
    if err != nil {
        return nil, err
    }
    if err := status.Err(); err != nil {
        return nil, err
    }
    return job, nil
}

ページネーションは少し癖があります。ジョブからデータを読み取るときに、次のページを指すトークンを得られます。ただしこのトークンは同一のジョブに対してのみ有効です。したがって、次のページを取得する際にはジョブID（とロケーション）を使って実行済みのジョブを取得して、そこからトークンを使って次のページを読み出す、というふうにします。

検索の条件をクライアントから渡したい

最後に、管理画面から検索条件を受け取ることを考えます。管理画面のフロントエンドとサーバサイドはGraphQLでやり取りしているので、GraphQLのスキーマとして考えます。

type UserConnection {}

input SearchUsersCondition {
  mailExactlyMatch: String
  mailAliasMatch: String
}

type Query {
  searchUsers(condition: SearchUsersCondition!): UserConnection! 
}

searchUsersに条件を表すパラメータを渡すということにしてみます。現在のGraphQLでは、パラメータにUnion型は使えないため、適当なInput型を作りました。メールアドレスの完全一致と、エイリアスを解決した上で一致する、というふたつの条件を表現しています。

（GraphQLについてはこちらをどうぞ）

これでうまくいくにはうまくいくのですが、メールアドレスの前方一致や後方一致、あるいはメールアドレス以外の条件を増やしていくと、冗長な感じになりそうです。サーバサイドで対応するSQLを組み立てる必要もあります。

そもそもBigQueryを使っているので、インデックスの有無を気にせず、どんなクエリを投げてもよいはずです。いっそフロントエンドからSQLのWHERE節を直接指定できたらいいのですが、管理画面とはいえちょっと気が引けます。

CEL

ここで、CELという表現を使うことを考えます。CELはCommon Expression Languageの頭文字語で、Googleによって開発される式言語です。

Projects - opensource.google

CELを入力として受け付けるようにすれば、Gmailのエイリアス機能を考慮した条件は次のように書けます。

mail.endsWith("@gmail.com") && regexp_replace(regexp_extract(mail, "^([^@]+)@"), "(\\.|\\+.*$)", "") = "example"

このときregexp_replaceとregexp_extractは独自に定義した関数で、CELの標準にはありません。

GraphQLのスキーマも次のように単純にできます。

type UserConnection {}

type Query {
  searchUsers(condition: String!): UserConnection! 
}

これでフロントエンドからどのような条件でも渡せるようになります。

あとは、CELからSQLのWHERE節をどうやって作るのかが問題です。

cockscomb/cel2sql

CELには都合よくGoの実装があり、CELからASTを作ったりするのはすぐにできそうです。

あとは丁寧に実装します。丁寧に実装したものをこちらに用意しました。

ちょっとややこしいのですが、次のように使います。

import (
    "context"
    "fmt"
    
    "cloud.google.com/go/bigquery"
    "github.com/cockscomb/cel2sql"
    "github.com/cockscomb/cel2sql/bq"
    "github.com/cockscomb/cel2sql/sqltypes"
    "github.com/google/cel-go/cel"
    "github.com/google/cel-go/checker/decls"
)

var client *bigquery.Client = ...
tableMetadata, _ := client.Dataset("dataset").Table("users").Metadata(context.TODO())

env, _ := cel.NewEnv(
    cel.CustomTypeProvider(bq.NewTypeProvider(map[string]bigquery.Schema{
        "Users": tableMetadata.Schema,
    })),
    sqltypes.SQLTypeDeclarations,
    cel.Declarations(
        decls.NewVar("users", decls.NewObjectType("Users")),
    ),
)

// CELからSQLを作る
ast, _ := env.Compile(`users.mail.endsWith("@gmail.com") && regexp_replace(regexp_extract(users.mail, "^([^@]+)@"), "(\\.|\\+.*$)", "") = "example"`)
sqlCondition, _ := cel2sql.Convert(ast)

fmt.Println(sqlCondition) // ENDS_WITH(`users`.`mail`, "@gmail.com") AND REGEXP_REPLACE(REGEXP_EXTRACT(`users`.`mail`, "^([^@]+)@"), "(\\.|\\+.*$)", "") = "example"

セットアップがややこしいのは、CELの型情報をうまく扱うためです。例えば数値と数値の加算をCELで書くとintVar + 1となって、SQLでもintVar + 1にできます。ところが文字列の場合、CELではやはりstringVar + "a"と書けますが、BigQueryの標準SQLではstringVar || "a"となり、演算子が違います。これを解決するために、BigQueryのスキーマ情報から型を取り出したりしています。

他にもBigQueryで使える関数の多くを透過的に扱えるように、定義を渡したりしています。