GAS: BigQuery へ データセットをロードする

GAS: BigQuery へ データセットをロードする

March 4, 2021
Google Apps Script(GAS)
Google Apps Script(GAS), BigQuery

やりたいこと #

  • GAS から BigQuery へデータセットをロードする
  • CSV によるロード
  • JSONL によるロード

CSV によるロード #

  • 基本的にはリファレンスにあるサンプルコード通りに書けばよい
  • テーブルを作成しながらロードするのでテーブル定義もコードに含まれる
  • テーブル作成有無を気にしたくなかったのでサンプルコードに対して「テーブルが存在すれば削除して再作成してからロード」を追加している
function loadCSVIntoBigQuery() {
  const csv = '1,2,abc,2021-03-01\n3,4,efg,2021-03-02';

  const projectId = '<YOUR PROJECT ID IS HERE>';
  const datasetId = '<YOUR DATASET ID IS HERE>';
  const tableId = '<TABLE NAME TO BE CREATED>';
  try {
    BigQuery.Tables.remove(projectId, datasetId, tableId);
  } catch(e) {
    console.log(e);
  }
  BigQuery.Tables.insert({
    tableReference: { projectId, datasetId, tableId },
    schema: {
      fields: [
        {name: 'col1', type: 'INTEGER'},
        {name: 'col2', type: 'INTEGER'},
        {name: 'col3', type: 'STRING'},
        {name: 'col4', type: 'DATE'},
      ]
    }
  }, projectId, datasetId);

  const data = Utilities.newBlob(csv);
  BigQuery.Jobs.insert({
    configuration: {
      load: {
        destinationTable: { projectId, datasetId, tableId }
      }
    }
  }, projectId, data);
}

JSONL によるロード #

CSV によるロードでは以下の課題がある。

  • ARRAY 型や STRUCT 型のカラムを持つテーブルへロードできない
  • CSV 変換を真面目に実装するとちょっと面倒くさい

JavaScript ネイティブで使える JSON.stringify() で変換して JSONL でロードできればこれらの課題は解決できる。

  • map() , JSON.stringify() , join('\n') を駆使して JSONL 形式に変換する
  • ARRAY 型カラムを作成するには mode: 'REPEATED' を指定する
  • configuration:load に sourceFormat: 'NEWLINE_DELIMITED_JSON' を指定する
function loadJSONLIntoBigQuery() {
  const jsonl = [
    {col1: 1, col2: 2, col3: "abc", col4: "2021-03-01", col5: [5, 6, 7]},
    {col1: 3, col2: 4, col3: "efg", col4: "2021-03-02", col5: [8, 9, 0]},
  ].map(o => JSON.stringify(o)).join('\n');

  const projectId = '<YOUR PROJECT ID IS HERE>';
  const datasetId = '<YOUR DATASET ID IS HERE>';
  const tableId = '<TABLE NAME TO BE CREATED>';
  try {
    BigQuery.Tables.remove(projectId, datasetId, tableId);
  } catch(e) {
    console.log(e);
  }
  BigQuery.Tables.insert({
    tableReference: { projectId, datasetId, tableId },
    schema: {
      fields: [
        {name: 'col1', type: 'INTEGER'},
        {name: 'col2', type: 'INTEGER'},
        {name: 'col3', type: 'STRING'},
        {name: 'col4', type: 'DATE'},
        {name: 'col5', type: 'INTEGER', mode: 'REPEATED'},
      ]
    }
  }, projectId, datasetId);

  const data = Utilities.newBlob(jsonl);
  BigQuery.Jobs.insert({
    configuration: {
      load: {
        destinationTable: { projectId, datasetId, tableId },
        sourceFormat: 'NEWLINE_DELIMITED_JSON'
      }
    }
  }, projectId, data);
}