GASを使ってスプレッドシート上のデータからBigQuery上にテーブルを作成しよう！

はじめに

はじめまして、日本情報通信の上野です。

本記事はNI+C TeamGCP Advent Calendar 2023 21日目の記事となります。
私は、エンジニアとしてよくスプレッドシートでテーブル定義書を作成しているのですが、テーブルの仕様変更があったときにテーブルを作り直すのは大変ですよね。なので、スプレッドシート上のテーブル定義書をもとにBigQuery上にテーブルを作成するGASを作成しました。これで、テーブル定義書を修正してGASを実行するだけでBigQueryのテーブルも作り直すことができます。それではさっそく、スプレッドシート上のデータからBigQueryのテーブルを作成する手順を解説していきたいと思います。

スプレッドシートの作成

では、今回作成したスプレッドシートとGASを見ていきたいと思います。
スプレッドシートのテーブル定義書のレイアウトは下記の画像となっております。

A列はBigQueryのテーブルのカラム名を定義しています。
B列はカラムのデータ型を定義しています。今回は、入力ミスを防ぐためにプルダウンでの選択式にしました。
C列はデータのモードを定義しています。チェックボックス形式で「REQUIRED」と「NULLABLE」を切り替えるようにしました。「REPEATED」については、今回は未実装です。
D列はテーブルのカラムの説明欄の定義をしています。
E列はパーティションの定義をしています。
F列はクラスタリングの定義をしています。

GASの作成

では、実際にスプレッドシートの内容をもとにBigQuery上でテーブルを作成するGASのコードを解説していきたいと思います。

まずはBigQueryの参照先とテーブルのスキーマの設定を行う部分を作成していきます。今回は、シート名をテーブル名に設定しています。プロジェクト名とデータセット名は自分で編集する必要があります。

 var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var sheetName = sheet.getName();
var values = sheet.getRange('A2:D' + sheet.getLastRow()).getValues();
var projectId = "projectId ";
var datasetId = "datasetId ";
var tableId = sheetName;

  var tableSchema = {
    "fields": []
  };

  for (var i = 0; i < values.length; i++) {
    tableSchema.fields.push({
      "name": values[i][0],
      "type": values[i][1],
      "mode": values[i][2],
      "description": values[i][3]
    });    
  }

パーティション部分のGASは下記の通りになります。パーティションは今回「1日ごと」に設定しております。

  var partitioning = sheet.getRange('E2').getValue();
  var partitioningField;
  
  if (partitioning != "") {
    partitioningField = partitioning;
  }
  if (partitioningField) {
    tableResource["timePartitioning"] = {
      "type": "DAY",
      "field": partitioningField
    };
  }

クラスタリング部分のGASは下記の通りになります。スプレッドシート側で最大4つまで指定することができ、上から優先順位が高くなるように設定しています。
  var clustering = sheet.getRange('F2:F5').getValues();
  var clusteringFields = [];
  
  for(i = 0; i < clustering.length; i++){
    if(clustering[i] != ""){
      clusteringFields.push(clustering[i]);
    }  
  }
  const validClusteringFields = clusteringFields.filter(Boolean);
  if (validClusteringFields.length > 0) {
    tableResource["clustering"] = {
      "fields": validClusteringFields
    };
  }

最後にBigQuery APIでテーブルを作成していきましょう。サービス部分にBigQuery APIを追加する必要もあるのでお気を付けください。

テーブルを作成する前に、同じデータセット内にすでに同じ名前のテーブルがある場合にそのテーブルを削除する処理も入れておくと、テーブルの作り直しが必要な際に作業が楽になります。

  try {
    // Get table resource
    var data = BigQuery.Tables.get(projectId, datasetId, tableId);
    // If table exists, delete it
    if (data) {
      BigQuery.Tables.remove(projectId, datasetId, tableId);
    }
  }
  catch (error) {
    Logger.log('Table does not exist');
  }

  try {
    var table = BigQuery.Tables.insert(tableResource, projectId, datasetId);
    Logger.log('Table created: %s', table.id);
  } catch (err) {
    Logger.log('Error creating table: %s', err);
  }

実行結果

実際に関数を実行して作成したテーブルが下記の画像です。

パーティションやクラスタリングも設定されています。

まとめ

今まではBigQueryのテーブルの仕様変更が行われるたびにテーブルを作り直すのが手間でしたが、テーブル定義書の必要な個所を修正するだけで作り直すことができるので作業がかなり楽になったと思います。
今回作成したスプレッドシートとGASは、パーティションが「1日ごと」固定になっているなどの改善余地がありますので、今後さらに細かい設定もできるように改良したいと思います。