Open10

[BigQuery] UDF から任意の API を叩きたい

BigQuery
kitta65kitta65

BigQuery で集計をするとき、よそに保存したデータに対してもシュッとクエリを発行できると嬉しいな、と思ったりする

kitta65kitta65

外部のデータをクエリする手段として federated queries や external tables が存在しているのは知っているけれど、現状だとそれぞれの対象は次の通り

上記以外を BigQuery から簡単にクエリできるようにするにはどういう方法がある？という話

kitta65kitta65

王道なのは CDC を活用して継続的にデータを取り込んだり、 embulk でまとめて load したりだと思う。
もう少し横着ができないかなと思ってたどり着いたのが stack overflow のこの議論。

I'm wondering if it would be possible to make a api call to the google maps geocoding api within a UDF in BigQuery?

You can now call remote API using remote functions:

BigQuery の remote functions なら任意の API を叩けるぞと。

kitta65kitta65

最近できた Python UDF でも同じことができそうな雰囲気。 cloud resource connection だけちゃんと準備すれば。

If you create a Python UDF without using a Cloud resource connection, the function is executed in an environment that blocks network access. If your UDF accesses online services, you must create the UDF with a Cloud resource connection. If you don't, the UDF is blocked from accessing the network until an internal connection timeout is reached.

kitta65kitta65

せっかくだから Python UDF の方を実験してみる。
datastore のデータを Python UDF 経由で取得してみよう。
まずは terraform で必要なリソースを準備。

terraform {
  required_version = ">= 1.10.0, < 2.0.0"
  required_providers {
    google = {
      source  = "hashicorp/google"
      version = ">= 6.0.0, < 7.0.0"
    }
  }
}

data "google_project" "project" {
  #　CLOUDSDK_CORE_PROJECT 環境変数から project を特定することにして、省略
}

# 実験用の dataset を作っておく
resource "google_bigquery_dataset" "test" {
  dataset_id = "test"
}

# cloud resource connection を作成する
resource "google_bigquery_connection" "test" {
  connection_id = "test_connection"
  location      = "US"
  cloud_resource {}
}

# cloud resource connection の service account に datastore の閲覧権限を与える
resource "google_project_iam_member" "test" {
  project = data.google_project.project.project_id
  role    = "roles/datastore.viewer"
  member  = "serviceAccount:${google_bigquery_connection.test.cloud_resource[0].service_account_id}"
}

# datastore に database を作成
resource "google_firestore_database" "test" {
  name        = "test-datastore"
  location_id = "nam5"
  type        = "DATASTORE_MODE"
}
kitta65kitta65

作成した datastore の database で create entity

適当にデータをつっこんでおく

kitta65kitta65

Python UDF を作成

create or replace function test.read_datastore()
returns array<string>
language python
with connection us.test_connection
options (
  runtime_version = "python-3.11",
  entry_point = "main",
  packages = ["google-cloud-datastore >=2.21.0, < 3.0.0"]
)
as r'''
from google.cloud import datastore
import json

def main():
    # project に文字列の project id を明示する必要がありそう
    # 省略すると project number になってしまった
    client = datastore.Client(database="test-datastore", project="XXXXX")
    entities = client.query(kind="test").fetch()
    jsons = [json.dumps(dict(e)) for e in entities]
    return jsons
'''
;
kitta65kitta65

ちょっと時間をおいてクエリを実行してみると datastore から無事にデータを引っこ抜けた

select parse_json(entity).testProperty
from unnest(test.read_datastore()) as entity

kitta65kitta65

今回は UDF から datastore にアクセスしてみたけれど、別に google cloud のサービスに限らず、外部の API も叩けるはず。

kitta65kitta65

便利なケースもありそうだけれど、本格的に使おうとすると気になる点もあるよな。

  • SQL を書き間違えるととんでもない回数 UDF が実行されるかも（そのとき API も叩いていると DoS 攻撃みたいになる）
  • 関数から返すデータ型
    • Python UDF と remote function の間でもちょっと差がある
    • Python UDF から JSON 型を返せなかったり、 remote functions から ARRAY を返せなかったり
  • あまりデータ量が多いケースで試してないから、なんか問題が起きないといいな