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

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

## はじめに
ストリーミングインサート関連の小ネタです。

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

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

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

## 再現
今回のケースは特にETL/ELTツールなど使わずとも再現できまずが、今回は自分が使い慣れてる自社の [CData BigQuery Driver](https://www.cdata.com/jp/drivers/bigquery/jdbc/ "タイトル")で確認していきます。
※なお、ストリーミングインサートの確認なので BigQuery の [InsertAllメソッド](https://cloud.google.com/bigquery/docs/reference/rest/v2/tabledata/insertAll) を使います。CData BigQuery Driver では [Insertmode オプション](https://cdn.cdata.com/help/DBG/jp/jdbc/RSBGoogleBigQuery_p_InsertMode.htm) を Streaming に設定することで、InsertAllメソッドへのリクエストを行うようになります。

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

```sql
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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/378300/14977e19-6649-1580-ba27-2f6096ee7962.png)

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

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

```sql
drop table Streaming_Table;
```
で、その際のリクエスト結果。特に変わったことはなく正常終了している。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/378300/d3bc1007-7e0f-f3d8-6137-3f603d6123a0.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>
```

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

CREATE文実行時のリクエスト内容ですが、こちらについてもリクエストとレスポンスどちらも大丈夫ですね。（CREATE文は先ほどと同じなので割愛します。）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/378300/f5f29652-30e5-a88d-631e-7e4cdd332aa0.png)

では最後に「テーブル削除 → テーブル作成 → <font color="Red"> ストリーミングインサート</font>」のレコード追加を行うと・・・
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/378300/f0e2c250-e747-6824-d8eb-e0f1beeae9ad.png)

テーブルが存在しないという404エラーが返ってきました～
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/378300/c2c12ddb-c69e-142d-6d4a-5543cf1a64af.png)

```json
{
  "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回目ジョブイメージでテーブル削除＆テーブル作成を行います。

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

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

```

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

```sql
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 文を実行します。

```sql
insert into Query_Table_1 (StoreId,ProductId,ProductName,StockAmount) values(1,1,'AAA Item',50);
```
成功しました！
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/378300/cab27442-dedb-8e62-d12a-12dcdb47a600.png)

ではこの時のリクエスト内容をみてみます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/378300/c27cafe0-fd19-7c08-b585-77ffa1e6429c.png)
`https://www.googleapis.com/bigquery/v2/projects/dataflow-cdata/queries` にアクセスし、リクエストBodyの中にInsert文が直接指定されています。

```json
{
    "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/