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

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

そもそも外部テーブルとは

公式ドキュメントからの引用です。

外部テーブルは、標準の BigQuery テーブルのように機能するテーブルです。
外部テーブル | BigQuery | Google Cloud

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

テーブルスキーマを含むテーブルメタデータは BigQuery ストレージに保存されていますが、データ自体は外部ソースに存在します。
外部テーブル | BigQuery | Google Cloud

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

方法

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

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

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

参考：bqコマンドを使用する場合
GoogleスプレッドシートをデータソースとするBigQuery外部テーブルをCLIで作成する

前提として

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

認証

// 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を操作する

テーブル作成

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

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

参考：公式ドキュメント
スキーマを使用してテーブルを作成する
 REST Resource: tables *ExternalDataConfiguration

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