BigQueryのローカル環境築構

00-index.md

BigQueryのローカル環境築構

• TL;DR
• レポジトリ
• モチベーション
• 技術
• やり方
• BiqQueryエミュレーターの設定
• GCSエミュレーターの設定（未完）
• SQL実行環境を作る
  • 共通
  • Node.jsのSQL実行環境を作る
  • zxのSQL実行環境を作る
• 参考

TL;DR

情報が少なくて断念したので参考レベル。とりあえずNode.jsからBigQueryを叩けるところまで。公式の例がPythonやGOとBigQuery CLIのみであり、現在ベータなことに留意。

エミュレーターに対しCLIや各言語の公式クライアントを使える。メインの使い方としてBigQueryに対する基本的なSQLクエリを投げることはできる。bqコマンドでスキーマを抜いたり、データセットやテーブルの定義も可能。しかしload系の処理はbqからもNode Clientからも不可能だった。seedは--data-from-yamlでコンテナ起動時にyamlファイルを指定する方法が代わりに提供されている。

GCSとの連携はGCSエミュレーターの方でGoを前提としており、他の言語ではサポートが薄い。加えて、自分のインフラの理解度やDocker力が低く、うまくいかなかった。bqコマンドやgsutilコマンドでローカルのBiqQueryエミュレーターがGCSエミュレーターに接続する部分で認証エラーや証明書エラー(x509: certificate signed by unknown authority)が起きてできなかった。ここで断念。

レポジトリ

learn-db/bq at main · x7ddf74479jn5/learn-db

モチベーション

学習用や研修用で使う想定。個人の学習用途ならBiqQueryサンドボックスで十分カバーできGCP無料範囲内で済む。BiqQueryサンドボックスの方が標準的なやり方でドキュメント通りにしていれば変な詰まり方をしないだろう。簡単さで選ぶならBiqQueryサンドボックスを使った方がベターなのだが、ローカルのエミュレーター環境を構築したい理由に、クラウドリソースの管理をできるだけしたくないというのが本音としてある。

技術

• goccy/bigquery-emulator: BigQuery emulator server implemented in Go
• fsouza/fake-gcs-server: Google Cloud Storage emulator & testing library.

やり方

bigquery-emulatorは起動時にローカルのyamlファイルから初期データをロードできる。 だが、ローカルのcsvやjson形式には対応しておらず、bqコマンドでGCSからロードする方法しかない。DockerでBigQueryとGCSのエミュレーターを立ち上げ、接続する。 エミュレーター開発者がDocker Imageを配布しているためありがたく使っていこう。

BiqQueryエミュレーターの設定

docker-comose.yaml

version: '3.9'
services:
  bq:
    image: ghcr.io/goccy/bigquery-emulator:latest
    container_name: bq-emulator
    ports:
      - 9050:9050
    working_dir: /work
    volumes:
      - ./bq/testdata.yaml:/work/testdata.yaml
    command: |
      --project=test --data-from-yaml=./testdata.yaml

testdata.yaml

projects:
  - id: test
    datasets:
      - id: dataset1
        tables:
          - id: table_a
            columns:
              - name: id
                type: INTEGER
              - name: name
                type: STRING
              - name: createdAt
                type: TIMESTAMP
            data:
              - id: 1
                name: alice
                createdAt: '2022-10-21T00:00:00'
              - id: 2
                name: bob
                createdAt: '2022-10-21T00:00:00'

この状態でコンテナーを起動したらbqコマンドで操作可能になる。

docker compose up -d

コマンドの向き先をローカルエミュレーターに指定して実行する。--apiと--project_idフラグは必須なので毎回付ける必要がある。

bq --api http://0.0.0.0:9050 --project_id=test ls

CSVやJSONでデータをインポートできる。

bq --api http://0.0.0.0:9050 --project_id=test load --source_format=CSV --autodetect {dataset} gs://{bucket}/{filepath}.csv

注意点としては、一般的なJSON形式ではなく以下のようなJSONL (JSON Lines) 形式で記述しなければならない。

{ "id": 1, "name": "foo" }
{ "id": 2, "name": "bar" }
{ "id": 3, "name": "baz" }

schemaの確認

bq --api http://0.0.0.0:9050 --project_id=test show --schema dataset1.table_a | jq

[
  {
    "name": "id",
    "type": "INTEGER"
  },
  {
    "name": "name",
    "type": "STRING"
  },
  {
    "name": "createdAt",
    "type": "TIMESTAMP"
  }
]

GCSエミュレーターの設定（未完）

/dataにオブジェクトが保存されるようになっており、バケット名をディレクトリ名にし/data/bucket-nameとなるようにボリュームをマウントする。これをしないと保存はできるが、参照処理が不可能。

GCSエミュレーターの疎通確認

curl http://0.0.0.0:4443/storage/v1/b | jq

バケット作成

例えば images バケットを作成したい場合、以下のように /data/images のようにコンテナ上にディレクトリを作ることで可能に。

volumes:
  - ${PWD}/gcs-data:/data/${BUCKERT_NAME}

# バケットのリスト取得
curl http://0.0.0.0:4443/storage/v1/b | jq
# バケット詳細取得
curl http://0.0.0.0:4443/storage/v1/b/example-bucket/o | jq

GOの環境であればSTORAGE_EMULATOR_HOST="localhost:4443"を環境変数で設定し、localhostに向き先を変えられる。

これでgsutilコマンドが使えるはず……。公式では以下のコマンドでバケットの内容が取れるのだが、なぜか動かず。

gsutil -o 'Credentials:gs_json_host=0.0.0.0' -o 'Credentials:gs_json_port=4443' -o 'Boto:https_validate_certificates=False' ls gs://sample-bucket/

INFO 0529 18:38:05.970480 retry_util.py] Retrying request, attempt #4...

SQL実行環境を作る

共通

Seed

learn-db/seed.ts at main · x7ddf74479jn5/learn-db · GitHub

手元のcsvファイルを読み込ませたい。方法としては以下の2通り。

1. docker-compose.yamlのエミュレーターの設定で--data-from-yamlにデータファイルを指定
   • csvをyamlに変換するのが手間
   • 小さいデータセットで試す用途
   • コンテナを立ち上げるだけでエミュが自動にやってくれる
2. seedスクリプトをNode.jsで走らせる
   • loadのAPIが使いたいがなぜかエラーで使えない

(1)でがんばる。

ツール

• js-yaml - npm
• csv-parse - npm

csv-parse → zod → js-yamlのツールチェインを通してシードファイルを出力する。zodに食わせるのは、csvファイルのパース結果とdatasetのスキーマ（BigQueryのスキーマ形式）。seedスクリプトを実行すれば、db/datasets/data.yamlに書き出される。

問題点

• js-yamlパッケージはBigQueryのスキーマに互換性がない。JSのDateはDATE型、DATETIME型に変換できず、常にTIMESTAMP型になる。文字列として扱いスキーマで指定する形になるが、日付部分が次のような形式はエラーを引き起こす。
  • ゼロパディングされていない： "2020-6-6"
  • 区切り文字がハイフン： "2020/06/06"
  • 空文字列： ""
• csv-parseのパーサーはfalseを文字列型、空値をundefinedに解釈するオプションが存在しない。
• シードファイルを反映させるためにコンテナを再起動させなければならないためデバッグがつらい。

zodでスキーマを組み立てを使い、変換層でパースしてつなぐ。zodにはcoerceやtransformといったパース前後でデータを柔軟に変換できるAPIが存在する。これらとrefineを組み合わせることで、かなり柔軟なバリデーションとデータ変形が可能になる。最低限動けばいいので妥協している。

learn-db/model.ts at main · x7ddf74479jn5/learn-db · GitHub

// "2023-6-6 17:54:20" → "2023-06-06 17:54:20"
// "" → undefined
const dateTimeSchema = z
  .string()
  .refine((val) => {
    // HACK: passthrough empty string
    if (val === "") return true;
    const result = z.coerce.date().safeParse(val);
    return result.success;
  })
  .transform((val) =>
    val === ""
      ? undefined
      : new Date(val)
          .toLocaleDateString("ja-JP", {
            year: "numeric",
            month: "2-digit",
            day: "2-digit",
            hour: "2-digit",
            minute: "2-digit",
            second: "2-digit",
          })
          .replaceAll("/", "-")
  );

Node.jsのSQL実行環境を作る

learn-db/bq/src/node at main · x7ddf74479jn5/learn-db · GitHub

nodejs-bigquery/quickstart.js at master · googleapis/nodejs-bigquery · GitHub

挙動確認

// lib/bq-client.ts

import { BigQuery } from "@google-cloud/bigquery";
import { util } from "@google-cloud/common";
import bqJson from "@google-cloud/bigquery/package.json" assert { type: "json" };

export const createBQClient = () => {
  const options = {
    projectId: "dc_sql",
    apiEndpoint: "http://0.0.0.0:9050",
    baseUrl: "http://0.0.0.0:9050",
    scopes: ["https://www.googleapis.com/auth/bigquery"],
    packageJson: bqJson,
    customEndpoint: true,
  };

  const bigQueryClient = new BigQuery(options);

  // Hack: connecting to a local Datastore server
  // @see https://blog.open.tokyo.jp/2022/12/18/bigquery-test-on-node-js.html
  // bigQueryClient.makeAuthenticatedRequest = util.makeAuthenticatedRequestFactory(options);

  return bigQueryClient;
};

query

// lib/query.ts

import { createBQClient } from "./bq-client.js";

function main(datasetId = "dataset1", tableId = "table_a") {
  const bigquery = createBQClient();
  
  async function query() {
    const query = `SELECT name
      FROM \`${datasetId}.${tableId}\`
      LIMIT 100`;

    const options = {
      query: query,
      location: "US",
    };

    const [job] = await bigquery.createQueryJob(options);
    console.log(`Job ${job.id} started.`);

    const [rows] = await job.getQueryResults();

    console.log("Rows:");
    rows.forEach((row) => console.log(row));
  }

  query();
}

main(...process.argv.slice(2));

pnpm ts-node lib/query.ts

Rows:
{ name: 'alice' }
{ name: 'bob' }

load

// lib/load-csv.ts

function main(datasetId = "dataset1", tableId = "products", filename = `${dir}/dc_sql/products.csv`) {
  const bigquery = createBQClient();

  async function loadLocalFile() {
    const metadata: JobLoadMetadata = {
      sourceFormat: "CSV",
      autodetect: true,
    };

    const [job] = await bigquery.dataset(datasetId).table(tableId).load(filename, metadata);

    console.log(`Job ${job.id} completed.`);

    const errors = job.status?.errors;
    if (errors && errors.length > 0) {
      throw errors;
    }
  }

  loadLocalFile();
}

main(...process.argv.slice(2));

pnpm ts-node lib/load-csv.ts

...
'runtime error: invalid memory address or nil pointer dereference'

GOのエラー文なのでBigQueryエミュレータ内部でエラーが発生したっぽい。情報が少なくて解決不能と判断。

zxのSQL実行環境を作る

learn-db/bq/src/zx at main · x7ddf74479jn5/learn-db · GitHub

自分の環境においてエミュレーターに打てるコマンドで有効なもののうち使いたいものに絞ったbqのラッパーCLIを作る。SQLを実行できればいいのでDDLは除いている。

使いたいbqコマンド一覧と独自コマンド

const BQ_COMMANDS = ["ls", "shell", "head", "show", "query", "version"];
const BQZX_COMMANDS = ["help", "sql", "seed"];

参考

• goccy/bigquery-emulator: BigQuery emulator server implemented in Go
• fsouza/fake-gcs-server: Google Cloud Storage emulator & testing library.
• Node.js で JSON データを BigQuery にインポートする | DevelopersIO
• sizuhiko - Technote - Node.js で BigQuery を使ったコードの自動テストを記述する
• firestore-bigquery-exportでFirestoreとエミュレートしたBigQueryでのデータ連携をローカルでやってみた｜SHIFT Group 技術ブログ
• nodejs-bigquery/quickstart.js at master · googleapis/nodejs-bigquery · GitHub