はじめに

firestore-bigquery-exportにはfs-bq-schema-viewsスクリプトというツールが付属で存在し、これを利用してJSON型で格納されるデータをフィールドにばらしたviewを作成する事ができる。これによりクエリが容易になるメリットがある。

今回は、そんなfs-bq-schema-viewsスクリプトを利用して、BigQueryのエミュレーターでviewを作成するという事をやってみた際に遭遇したエラーと対応策を備忘録として残しておく。

※BigQueryのエミュレーターはBigQuery Emulatorを利用した。

fs-bq-schema-viewsスクリプトでBigQueryのエミュレーターにビューを作成した際にエラーになった

fs-bq-schema-viewsスクリプトにはendpointを設定できるようなCLIオプションはないので、自前でコードを書き換えてAPIの向き先をローカルのエミュレーターに向ける必要がある。

まず、fs-bq-schema-viewsスクリプトを利用する際には、yarn add -D @firebaseextensions/fs-bq-schema-viewsで追加するので、スクリプト自体はnode_modulesの以下に存在している（ビルドされる前のTypeScriptのコードだとここ）。

node_modules/@firebaseextensions/fs-bq-schema-views/lib/index.js

そのコードを読んでいくと、fs-bq-schema-views/lib/schema.jsでBigQueryのクライアントをnewしている事が分かる（ビルドされる前のTypeScriptのコードだとここ）。

class FirestoreBigQuerySchemaViewFactory {
    constructor() {
        this.bq = new bigquery.BigQuery();
    }
    ...
}

BigQueryをnewする際には、apiEndpointオプションを設定する事で任意のエンドポイントを設定できるので、以下のように修正し、ローカルのBigQueryエミュレーターにAPIが投げられるようにする。

class FirestoreBigQuerySchemaViewFactory {
    constructor() {
        this.bq = new bigquery.BigQuery({ apiEndpoint: "http://localhost:9050" });
    }
    ...
}

ここまで設定したら後は以下のように"@firebaseextensions/fs-bq-schema-views"コマンド実行すれば、ビューがBigQueryのエミュレーターに作成される、と思っていたが以下のリクエストを実行する際にエラーになってしまった…。

CREATE FUNCTION IF NOT EXISTS `{projectId}.firestore_export.firestoreGeopoint`(json STRING) RETURNS GEOGRAPHY AS (\n  ST_GEOGPOINT(\n    SAFE_CAST(JSON_EXTRACT(json, '$._longitude') AS NUMERIC),\n    SAFE_CAST(JSON_EXTRACT(json, '$._latitude') AS NUMERIC)\n  )\n);

ログ全体は以下。

$ npx @firebaseextensions/fs-bq-schema-views \
>   --non-interactive \
>   --project={projectId} \
>   --dataset=firestore_export \
>   --table-name-prefix=users \
>   --schema-files=./gen-schema-view/test_schema.json
{"severity":"WARNING","message":"Warning, FIREBASE_CONFIG and GCLOUD_PROJECT environment variables are missing. Initializing firebase-admin will fail"}
{"errors":[{"message":"failed to create function spec: failed to format function expression: st_geogpoint function is unimplemented","reason":"jobInternalError"}],"response":{"configuration":{"jobType":"QUERY","query":{"priority":"INTERACTIVE","query":"CREATE FUNCTION IF NOT EXISTS `{projectId}.firestore_export.firestoreGeopoint`(json STRING) RETURNS GEOGRAPHY AS (\n  ST_GEOGPOINT(\n    SAFE_CAST(JSON_EXTRACT(json, '$._longitude') AS NUMERIC),\n    SAFE_CAST(JSON_EXTRACT(json, '$._latitude') AS NUMERIC)\n  )\n);","useLegacySql":false}},"jobReference":{"jobId":"73febe91-439b-4e83-8c06-420a9b130974","projectId":"{projectId}"},"kind":"bigquery#job","selfLink":"http://0.0.0.0:9050/bigquery/v2/projects/{projectId}/jobs/73febe91-439b-4e83-8c06-420a9b130974","statistics":{"creationTime":"1681370422","endTime":"1681370422","query":{"statementType":"SELECT"},"startTime":"1681370422"},"status":{"errorResult":{"message":"failed to create function spec: failed to format function expression: st_geogpoint function is unimplemented","reason":"jobInternalError"},"errors":[{"message":"failed to create function spec: failed to format function expression: st_geogpoint function is unimplemented","reason":"jobInternalError"}],"state":"DONE"}},"message":"failed to create function spec: failed to format function expression: st_geogpoint function is unimplemented"}
failed to create function spec: failed to format function expression: st_geogpoint function is unimplemented

エラーの原因

まず、以下のリクエストが何者か？だが、これはGCPのBigQueryでは永続関数として登録されているものを作ろうとしているクエリになる。

CREATE FUNCTION IF NOT EXISTS `{projectId}.firestore_export.firestoreGeopoint`(json STRING) RETURNS GEOGRAPHY AS (\n  ST_GEOGPOINT(\n    SAFE_CAST(JSON_EXTRACT(json, '$._longitude') AS NUMERIC),\n    SAFE_CAST(JSON_EXTRACT(json, '$._latitude') AS NUMERIC)\n  )\n);

GCP上で確認できる永続関数の情報は以下。

永続関数、ルーティンとはロジックを再利用してデータを独自の方法で処理するためのもので、例えば、上記の例だと組み込みのST_GEOGPOINT関数を使ってJSON型のデータの中の_longitudeと_latitudeのキーの値をGEOGRAPHY値に変換する、という関数になる。

こういう関数を用意しておく事で、viewを構築するクエリで毎回ルーティンに登録しているものを利用できるので、viewのクエリを簡潔に実装できるようになる。

で、今回のエラーは、ルーティンを作成しようとしたが、組み込み関数のst_geogpointが未実装であるために起きたエラーである事が分かる（failed to format function expression: st_geogpoint function is unimplemented）。

ではどうするか？

2つの対応策があると思う。

ライブラリ（ツール）のコードを読み、エラーが出ないように暫定的に修正する
そもそもfs-bq-schema-viewsスクリプトを利用しないで自前でviewを作成する

それぞれ以下で見ていく。

ライブラリ（ツール）のコードを読み、エラーが出ないように暫定的に修正する

これは、yarn add -D @firebaseextensions/fs-bq-schema-viewsでnode_modulesにダウンロードされたJavaScriptを自分で書き換えて、エラーが出ないようにするというもの。ライブラリのコードをいじるのはあまり普段はやらないが、意図的に変えるのはなしではないかなと思っている。

今回の場合、コードを読むと以下のコード（for (let i = 0; i < udfNames.length; i++) {...}）でルーティン（UDF:User Defined Function）を作成している事が分かる（元のTypeScriptのコードだとここ）。

node_modules/@firebaseextensions/fs-bq-schema-views/lib/schema.js

...
    initializeSchemaViewResources(datasetId, tableNamePrefix, schemaName, firestoreSchema) {
        return __awaiter(this, void 0, void 0, function* () {
            const rawChangeLogTableName = changeLog(raw(tableNamePrefix));
            const latestRawViewName = latest(raw(tableNamePrefix));
            const changeLogSchemaViewName = changeLog(schema(tableNamePrefix, schemaName));
            const latestSchemaViewName = latest(schema(tableNamePrefix, schemaName));
            const dataset = this.bq.dataset(datasetId);
            const udfNames = Object.keys(udf_1.udfs);
            for (let i = 0; i < udfNames.length; i++) {
                const functionName = udfNames[i];
                const udf = udf_1.udfs[functionName](datasetId);
                yield this.bq.query({
                    query: udf.query,
                });
            }
...

そしてfor文で回しているudfNamesの配列は、udf_1.udfsのキーの配列だが、udf_1.udfs自体は以下のように定義されている（元のTypeScriptのコードだとここ）。

node_modules/@firebaseextensions/fs-bq-schema-views/lib/udf.js

...
exports.udfs = {
    firestoreArray: firestoreArrayFunction,
    firestoreBoolean: firestoreBooleanFunction,
    firestoreNumber: firestoreNumberFunction,
    firestoreTimestamp: firestoreTimestampFunction,
    firestoreGeopoint: firestoreGeopointFunction,
};
...

今回のエラーはfirestoreGeopointのfirestoreGeopointFunctionでST_GEOGPOINT関数を利用してUDFを作成しようとしていた事で起きていた。

...
function firestoreGeopointFunction(datasetId) {
    const definition = firestoreGeopointDefinition(datasetId);
    return {
        query: definition,
        useLegacySql: false,
    };
}
function firestoreGeopointDefinition(datasetId) {
    return sqlFormatter.format(`
    CREATE FUNCTION IF NOT EXISTS \`${process.env.PROJECT_ID}.${datasetId}.firestoreGeopoint\`(json STRING)
    RETURNS GEOGRAPHY AS
    (ST_GEOGPOINT(SAFE_CAST(JSON_EXTRACT(json, '$._longitude') AS NUMERIC), SAFE_CAST(JSON_EXTRACT(json, '$._latitude') AS NUMERIC)));`);
}
...

ので、udf.jsのfirestoreGeopointをコメントアウトすれば、UDFを作成する事自体をしなくなるので、エラーが発生しなくなる。

node_modules/@firebaseextensions/fs-bq-schema-views/lib/udf.js

...
exports.udfs = {
    firestoreArray: firestoreArrayFunction,
    firestoreBoolean: firestoreBooleanFunction,
    firestoreNumber: firestoreNumberFunction,
    firestoreTimestamp: firestoreTimestampFunction
    // firestoreGeopoint: firestoreGeopointFunction,
};
...

コメントアウト後にコマンドを実行すると、以下のようにビューが作成される事が確認できる。

$ npx @firebaseextensions/fs-bq-schema-views \
   --non-interactive \
   --project={projectId} \
   --dataset=firestore_export \
   --table-name-prefix=users \
   --schema-files=./gen-schema-view/test_schema.json
{"severity":"WARNING","message":"Warning, FIREBASE_CONFIG and GCLOUD_PROJECT environment variables are missing. Initializing firebase-admin will fail"}
firestoreArray
firestoreBoolean
firestoreNumber
firestoreTimestamp
BigQuery creating schema view users_schema_test_schema_changelog:
Schema:
{"fields":[{"name":"email","type":"string","extractor":"email"},{"name":"last_name","type":"string","extractor":"last_name"},{"name":"first_name","type":"string","extractor":"first_name"}]}
Query:
SELECT
  document_name,
  document_id,
  timestamp,
  operation,
  JSON_EXTRACT_SCALAR(data, '$.email') AS email,
  JSON_EXTRACT_SCALAR(data, '$.last_name') AS last_name,
  JSON_EXTRACT_SCALAR(data, '$.first_name') AS first_name
FROM
  `{projectId}.firestore_export.users_raw_changelog`
BigQuery created schema view users_schema_test_schema_changelog

BigQuery creating schema view users_schema_test_schema_latest:
Schema:
{"fields":[{"name":"email","type":"string","extractor":"email"},{"name":"last_name","type":"string","extractor":"last_name"},{"name":"first_name","type":"string","extractor":"first_name"}]}
Query:
-- Given a user-defined schema over a raw JSON changelog, returns the
-- schema elements of the latest set of live documents in the collection.
--   timestamp: The Firestore timestamp at which the event took place.
--   operation: One of INSERT, UPDATE, DELETE, IMPORT.
--   event_id: The event that wrote this row.
--   <schema-fields>: This can be one, many, or no typed-columns
--                    corresponding to fields defined in the schema.
SELECT
  document_name,
  document_id,
  timestamp,
  operation,
  email,
  last_name,
  first_name
FROM
  (
    SELECT
      document_name,
      document_id,
      FIRST_VALUE(timestamp) OVER(
        PARTITION BY document_name
        ORDER BY
          timestamp DESC
      ) AS timestamp,
      FIRST_VALUE(operation) OVER(
        PARTITION BY document_name
        ORDER BY
          timestamp DESC
      ) AS operation,
      FIRST_VALUE(operation) OVER(
        PARTITION BY document_name
        ORDER BY
          timestamp DESC
      ) = "DELETE" AS is_deleted,
      FIRST_VALUE(JSON_EXTRACT_SCALAR(data, '$.email')) OVER(
        PARTITION BY document_name
        ORDER BY
          timestamp DESC
      ) AS email,
      FIRST_VALUE(JSON_EXTRACT_SCALAR(data, '$.last_name')) OVER(
        PARTITION BY document_name
        ORDER BY
          timestamp DESC
      ) AS last_name,
      FIRST_VALUE(JSON_EXTRACT_SCALAR(data, '$.first_name')) OVER(
        PARTITION BY document_name
        ORDER BY
          timestamp DESC
      ) AS first_name
    FROM
      `{projectId}.firestore_export.users_raw_latest`
  )
WHERE
  NOT is_deleted
GROUP BY
  document_name,
  document_id,
  timestamp,
  operation,
  email,
  last_name,
  first_name
BigQuery created view users_schema_test_schema_latest
done.

※上記のコードを書き換える方法だが、firestoreGeopointというルーティン（UDF）がなくても問題ないビュースキーマであれば大丈夫だが、firestoreGeopointを必要とするビュースキーマであれば、もちろんこの方法はNG。

そもそもfs-bq-schema-viewsスクリプトを利用しないで自前でviewを作成する

これはスキーマを別ファイルに定義して、そのファイルからbqコマンドでBigQueryエミュレーターにビューを作成する方法。スキーマを自分で書く手間があるが、慣れていればそんなに大変ではないので、この方法が正攻法な気もする。

$ bq --api http://localhost:9050 --project_id={projectId} mk --view "$(< gen-schema-view/local/users_schema_test_schema_changelog.sql)" firestore_export.users_schema_test_schema_changelog
View '{projectId}:firestore_export.users_schema_test_schema_changelog' successfully created.

gen-schema-view/local/users_schema_test_schema_changelog.sql

SELECT
  document_name,
  document_id,
  timestamp,
  operation,
  JSON_EXTRACT_SCALAR(data, '$.email') AS email,
  JSON_EXTRACT_SCALAR(data, '$.last_name') AS last_name,
  JSON_EXTRACT_SCALAR(data, '$.first_name') AS first_name
FROM
  `{projectId}.firestore_export.users_raw_changelog`

上記のようにビューを作成すれば、以下のようにビューに対してクエリが実行できる。

$ bq --api http://localhost:9050 query --project_id={projectId} "SELECT * FROM firestore_export.users_schema_test_schema_changelog"
+-----------------------------------------------------------------------------------------------------+------------------------------+---------------------+-----------+-------------------------------+-----------+------------+
|                                            document_name                                            |         document_id          |      timestamp      | operation |             email             | last_name | first_name |
+-----------------------------------------------------------------------------------------------------+------------------------------+---------------------+-----------+-------------------------------+-----------+------------+
| projects/{projectId}/databases/(default)/documents/users/AtEGzCMD1E7QlHNioTzhL0k6wvtX               | AtEGzCMD1E7QlHNioTzhL0k6wvtX | 2023-04-14 08:39:04 | CREATE    | chicken.peach.833@example.com | 山田      | 華子       |
+-----------------------------------------------------------------------------------------------------+------------------------------+---------------------+-----------+-------------------------------+-----------+------------+