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文は先ほどと同じなので割愛します。)
では最後に「テーブル削除 → テーブル作成 → ストリーミングインサート」のレコード追加を行うと・・・
{
"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/