Google Apps ScriptでBigQueryを実行してみる

BigQueryでSQLを実行した結果をシートに吐き出したいみたいなことは割とよくありますよね。実際に最近扱った案件でも何回かありましたんで、自分への備忘がてら記事に残すとする。

GASでBigQueryを実行する

Google Apps ScriptでBigQueryを実行する方法はググれば山のように出てきます。わざわざここで書く必要はない気もするけど、念の為。

①GAS画面の左側ツールバー「サービス」からBigQueryを追加
なんてことはないですね。これを登録するだけでBigQueryが使えるようになります。

②バージョンは最新のものを選ぶ

Google Apps Scriptの設定

汎用性を高めるためにクラスにしてみた。
たぶんコピペで割とどこでも使えるはず。以下、GASコード。

/**
 * BigQueryAPIの実行
 * @constructor
 *  - project_code : GCPのプロジェクト名
 *  - query : 実行するSQL
 *  - result : BigQueryAPIのレスポンス結果（オブジェクト）
 */
class Bigquery {
  constructor (sql) {
    this.project_code = 'gcp_project_code';
    this.query = sql;
    this.result = this.execQuery();
  }

  /**
   * BigQueryAPIの実行
   * @return {object} - BigQueryAPIの結果（Object）
   */

  //location入れる
  execQuery () {
    const resource = {
      query : this.query,
      timeoutMs: 1200000,
      useLegacySql: false,
      location: 'US'
    };
    return BigQuery.Jobs.query(resource, this.project_code);
  }

  /**
   * BigQueryの実行結果を配列に変換
   * @return {array} - 二次元配列
   */
  getQueryResultArray() {
    const resultCount = this.result.getTotalRows();
    const resultValues = new Array(parseInt(resultCount));
    const tableRows = this.result.getRows();

    for (let i = 0; i < resultCount; i++) {
      let cols = tableRows[i].getF();
      resultValues[i] = new Array(cols.length);
      for (let j = 0; j < cols.length; j++) {
        resultValues[i][j] = cols[j].getV();
      }
    }
    console.log('BigQueryData:', resultValues)
    return resultValues;
  }
}

このクラスの使い方

①SQLの作成（たとえばこんな感じ）

const sql = `
  select name, phone_number, address
  from \`data.user_info\`
  where date between '2023-03-01' AND '2023-03-31'
`

②Classの呼び出し

//引数に①で定義したSQLを渡す
const bigquery = new Bigquery(sql);

③クエリの実行結果を取得

//key[result]で結果を取得
const sqlResult = bigquery.result;
console.log(sqlResult);

使い方は以上。
あとはGAS上で煮るなり焼くなり好きにできます。シートに書き出すも良し、CSVファイルにしてどっかに保存するも良し、メールなりSlackなりで通知するも良し。

GAS上でSQL実行する分には割とシンプルにできて便利でしたね。
（まぁ。SQLを書くのが慣れないとむずいんですがね。。。がんばろ。）