[BigQuery] Node.jsでスプレッドシートをデータソースとする外部テーブルを作成する

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2661777/89741be5-eb56-ddb2-378d-01db9795d65a.png" width="60%">

社内データがたくさんのスプレッドシートに分散している…というのは、あるあるな状況ではないでしょうか。例えば、期間Aのデータはスプレッドシート１にあり、期間Bのデータはスプレッドシート２にあり、期間Cのデータは…。そのように分散したデータをBigQueryに集約して、SQLで抽出、結合して、CMSやBIツールなどのアプリケーションに渡せたらできたら便利ですね。

ということで、今回はNode.jsのクライアントライブラリを使用して、BigQueryに外部テーブルを作成する方法についての記事です。

# そもそも外部テーブルとは
公式ドキュメントからの引用です。

>外部テーブルは、標準の BigQuery テーブルのように機能するテーブルです。
[外部テーブル  |  BigQuery  |  Google Cloud](https://cloud.google.com/bigquery/docs/external-tables?hl=ja)

つまり外部テーブルを作成すると、スプレッドシートのデータをあたかもBigQueryのテーブルのように扱えるようになります。BigQuery側ではSQLが書けるため、複数のスプレッドシートを結合したり、必要なデータを抽出する操作がぐっとやりやすくなりますね。GASで配列操作をしたり、IMPORTRANGEとVLOOKUPで複雑怪奇な参照をするよりスッキリ！


> テーブル スキーマを含むテーブル メタデータは BigQuery ストレージに保存されていますが、データ自体は外部ソースに存在します。
[外部テーブル  |  BigQuery  |  Google Cloud](https://cloud.google.com/bigquery/docs/external-tables?hl=ja)


あたかもBigQueryのテーブルのように振る舞う、ということがミソで、データ自体はスプレッドシートに存在しています。つまり、スプレッドシートを編集すると、BigQueryの外部テーブルのレコードも同じ値になります。常に最新のスプレッドシートの値がBigQuery側で取得できるという点ではとても便利ですが、スプレッドシート側の編集内容によっては予期せぬ値が入ってくる可能性もあるので、そこのところは注意が必要です。

# 方法
さてBigQueryで外部テーブルを作成するには以下の３つの方法があります。

* ブラウザでGCPのコンソールから操作する
* bqコマンド
* クライアントライブラリ

コンソールからでもテーブル作成はできますが、扱うデータ自体がいくつものスプレッドシートに分割されているケースなど、スクリプトからBigQueryを操作できたらなにかと楽ですよね。

参考：bqコマンドを使用する場合
[GoogleスプレッドシートをデータソースとするBigQuery外部テーブルをCLIで作成する](https://dev.classmethod.jp/articles/202109-google-spreadsheet-bigquery-external-table-with-cli/)

# 前提として
* BigQueryにプロジェクトとデータセットが既に作成されている
* GCPのサービスアカウントが設定済み
* 認証用のjsonがダウンロードされている

# 認証
```Javascript
// Import the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery({
    projectId: 'foo',
    keyFilename: 'bar.json'
});
```

projectIdとkeyFilename(プライベートキー)は環境にあわせて変更してください。


参考：サービスアカウント作成について
[Node.jsでBigQueryを操作する](https://qiita.com/scent_y/items/fa73dd045ddbc375f731)



# テーブル作成
```Javascript
async function createTable(id, sheetname) {
  const datasetId = "my_dataset"; //データセットID
  const tableId = sheetname;
  const options = {
      "externalDataConfiguration": {
          "autodetect": true,
          "sourceFormat": "GOOGLE_SHEETS",
          "sourceUris": [
            `https://docs.google.com/spreadsheets/d/${id}`
          ],
          "googleSheetsOptions":{
            "range":`${sheetname}!A:E`, //読み込むシートの範囲
            "skipLeadingRows":1 //ヘッダー行はスキップ
          },
          "schema":{
            "fields":[
              {"name":"name",         "type":"STRING", "mode":"NULLABLE"},
              {"name":"age",         "type":"STRING", "mode":"NULLABLE"},
              {"name":"weight",      "type":"STRING", "mode":"NULLABLE"}
            ]
          }
      }
  }

  // Create a new table in the dataset
  const [table] = await bigquery
    .dataset(datasetId)
    .createTable(tableId, options);

  console.log(`Table ${table.id} created.`);
}

id = 'XXXXXXXXXXXXXX';   //スプレッドシートのID
sheetname = 'hogehoge';  //シート名
createTable(id, sheetname);
```

# ポイント
基本的には通常のテーブル作成と同じです。外部テーブルを扱う場合、externalDataConfigurationとしてデータソースとするスプレッドシートの情報をBigQueryに渡しています。

#### sourceFormat
GOOGLE_SHEETS

#### sourceUris
読み込むスプレッドシートのURI

#### googleSheetsOptions.range
読み込む範囲

#### googleSheetsOptions.skipLeadingRows
ヘッダー行のスキップ
1行目はヘッダーで、name、age、weightなどの項目が並んでいる場合など、
実際には2行からが取得したいデータになるので1行目はスキップする。

#### schema.fields
スプレッドシートをデータソースとする場合、元データを人間が編集している可能性があるため、すべて文字列としてスキーマを定義しています。

参考：公式ドキュメント
[スキーマを使用してテーブルを作成する](https://cloud.google.com/bigquery/docs/samples/bigquery-create-table?hl=ja)
[REST Resource: tables ](https://cloud.google.com/bigquery/docs/reference/rest/v2/tables#externaldataconfiguration) *ExternalDataConfiguration