BigQueryにストリーミングインサートしたときに発見した小ネタ #BigQuery

BigQuery Advent Calendar 2021 4日目の記事です！

はじめに

ストリーミングインサート関連の小ネタです。

自社のETL/ELTツール(CDataSync)のテクニカルサポートを行っていた際に、とあるユーザーさんから「 BigQuery へデータを連携しているんだが、BigQuery から "404 not found" でエラーが返ってくるんだけど！」という問合せを受け、調査したときのネタです。内容としてはツール側の話というより、BigQueryへのインサート方法で少し気づきがあったのでその話になります。

ユーザーさんがツールでやっていたことは至ってシンプルです。
毎回データを洗替で連携しようと、ELTツールのジョブを「テーブル作成 → ストリーミングインサート」、少し待ってから「テーブル削除 → テーブル作成 → ストリーミングインサート」という内容で実行していた感じです。

内容からみて、同じような設定かつユースケースであれば、他のETL/ELTツールでも起こり得そうだなと思います。
ではこの事象を再現させながら解決方法を書いていきます。

再現

今回のケースは特にETL/ELTツールなど使わずとも再現できまずが、今回は自分が使い慣れてる自社の CData BigQuery Driverで確認していきます。
※なお、ストリーミングインサートの確認なので BigQuery の InsertAllメソッドを使います。CData BigQuery Driver では Insertmode オプションを Streaming に設定することで、InsertAllメソッドへのリクエストを行うようになります。

最初にテーブル作成＆レコードを追加していきます。

CREATE TABLE Streaming_Table (
   StoreId INT,
   ProductId INT,
   ProductName VARCHAR(100),
   StockAmount INT
);
insert into Streaming_Table (StoreId,ProductId,ProductName,StockAmount) values(1,1,'AAA Item',50);

特に何事もなくレコードが追加されました。

これがいわゆる、ETL/ELTツールからの初回ジョブというやつです。

ではほんの少し置いた後、ETL/ELTツールからの2回目ジョブを意識した「テーブル削除 → テーブル作成 → ストリーミングインサート」を行ってみます。まずはテーブル削除。

drop table Streaming_Table;

で、その際のリクエスト結果。特に変わったことはなく正常終了している。

bqコマンドで見てもテーブルが削除されています。

PS C:\dev\tools\scripts> bq query "SELECT count(*) FROM sync.Streaming_Table"
BigQuery error in query operation: Error processing job 'dataflow-
cdata:bqjob_r3a471484c98c9893_0000017d7fcab27c_1': Not found: Table dataflow-
cdata:sync.Streaming_Table was not found in location US
PS C:\dev\tools\scripts>

ではこの流れの通り、「テーブル削除 → テーブル作成 → ストリーミングインサート」のテーブル作成を続けて行います。

CREATE文実行時のリクエスト内容ですが、こちらについてもリクエストとレスポンスどちらも大丈夫ですね。（CREATE文は先ほどと同じなので割愛します。）

では最後に「テーブル削除 → テーブル作成 → ストリーミングインサート」のレコード追加を行うと・・・

テーブルが存在しないという404エラーが返ってきました～

{
  "error": {
    "code": 404,
    "message": "Table 917044252951:sync.streaming_Table not found.",
    "errors": [
      {
        "message": "Table 917044252951:sync.streaming_Table not found.",
        "domain": "global",
        "reason": "notFound"
      }
    ],
    "status": "NOT_FOUND"
  }
}

以上が再現確認でした。

原因

どうやらBigQuery の insertAll メソッドでレコード追加を行う場合、この辺の構造はよく理解してないですが、再作成直後のテーブルではテーブルが存在しないということになるようです。

例）
https://www.googleapis.com/bigquery/v2/projects/dataflow-cdata/datasets/sync/tables/Query_Table_1/insertAll

解決策

数分置いておけばエラーは出なくなるので待つということでも良いのですが、
もし短いスパンでデータを BigQuery に連携、なお且つ毎回洗替するようなケースの場合は、レコード追加のときにInsertAll ではなくjobs.queryの方を使うことで解決します。
https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query
これだとレコードが確実に反映されるようです。

ではやってみます。
まずは先ほどと同じように初回ジョブイメージでテーブル作成＆レコード追加をしたら、2回目ジョブイメージでテーブル削除＆テーブル作成を行います。

テーブル作成＆レコード追加

CREATE TABLE Query_Table_1 (
   StoreId INT,
   ProductId INT,
   ProductName VARCHAR(100),
   StockAmount INT
);
insert into Query_Table_1 (StoreId,ProductId,ProductName,StockAmount) values(1,1,'AAA Item',50);

テーブル削除＆テーブル作成

drop table Query_Table_1;

CREATE TABLE Query_Table_1 (
   StoreId INT,
   ProductId INT,
   ProductName VARCHAR(100),
   StockAmount INT
);

ここで InsertAll ではなく jobs.query を使うようにします。
※CData Driver ではInsertmode DML に変更するとjobs.queryの方を使うように切り替わります。
それでは Insert 文を実行します。

insert into Query_Table_1 (StoreId,ProductId,ProductName,StockAmount) values(1,1,'AAA Item',50);

成功しました！

ではこの時のリクエスト内容をみてみます。

https://www.googleapis.com/bigquery/v2/projects/dataflow-cdata/queries にアクセスし、リクエストBodyの中にInsert文が直接指定されています。

{
    "kind": "bigquery#queryRequest",
    "query": "INSERT INTO `Query_Table_1` (`StoreId`, `ProductId`, `ProductName`, `StockAmount`) VALUES (1, 1, 'AAA Item', 50)",
    "useLegacySql": "false",
    "priority": "INTERACTIVE",
    "defaultDataset": {
        "datasetId": "sync",
        "projectId": "dataflow-cdata"
    }
}

気になること

InsertAll メソッドでストリーミングインサートを行っている方で、短いスパンでテーブルを毎回削除するようなケースは稀かと思いますが、例えば他の ETL/ELT ツールではどの方法で BigQUery にインサートしているのか、もうちょっと時間があったら調べてみたいかなという感じです。

おわりに

ちょっとした小ネタでしたが、もし同じようなエラーが発生したらインサート方法を確認してみるというのが良いかもです。

ちなみに今回の記事に出てきたツールは下記のものです。
BigQuery JDBC Driver：https://www.cdata.com/jp/drivers/bigquery/
CDataSync：https://www.cdata.com/jp/sync/