3
1

More than 1 year has passed since last update.

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

Last updated at Posted at 2021-12-03

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);

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

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

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

drop table Streaming_Table;

で、その際のリクエスト結果。特に変わったことはなく正常終了している。
image.png
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文は先ほどと同じなので割愛します。)
image.png

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

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

{
  "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);

成功しました!
image.png

ではこの時のリクエスト内容をみてみます。
image.png
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/

3
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
3
1