[GAS × BigQuery] サクッと入門・クエリの実行🔥

こんにちは。
フロントエンドエンジニアのみつです。

GAS(Google Apps Script)からBigQueryを実行したい時の自分用チートシート。

SQLをBigQueryの管理画面で書いて実行の確認が取れたら、これを使うのみ！！

という関数がなにやら出来たっぽいのでそのメモです。

• GASエディタで、BigQuery APIをONにしている必要があります。
• 基本的に、スプレッドシートに紐づくGASを操作するためのコード（コンテナバインド型）を想定しています。

目次

• 共通の値を書いておく
• BigQueryにクエリを投げる
• クエリ結果を受け取って、データを抽出する
• 使用例
  • sample関数
  • sample関数 実行キャプチャ
• まとめ
• 参考URL

共通の値を書いておく

const getBigQueryVariables = () => {
  const jobDetailsTemplate = {
    status: "",
    statusMessage: "",
    errorMessage: "",
    result: null,
  };

  return {
    jobDetailsTemplate,
  };
};

projectIDとかも共通している値があればこの中に入れちゃうの良さそうかも、と思ったり！

BigQueryにクエリを投げる

jobs.queryを使います。

Runs a BigQuery SQL query synchronously and returns query results if the query completes within a specified timeout.

また、numRetriesで3回までは失敗してもリトライするようにしています。

const customJobQuery = (projectID, sql) => {
  const bigqueryVariables = getBigQueryVariables();
  const jobDetails = bigqueryVariables.jobDetailsTemplate;

  try {
    jobDetails.result = BigQuery.Jobs.query(
      {
        useLegacySql: false,
        query: sql,
      },
      projectID
    );

    let numRetries = 0;
    while (
      (jobDetails.result["jobComplete"] !== true ||
        jobDetails.result["numDmlAffectedRows"] !== 0) &&
      numRetries < 4
    ) {
      Utilities.sleep(500);

      jobDetails.result = BigQuery.Jobs.query(
        {
          useLegacySql: false,
          query: sql,
        },
        projectID
      );

      numRetries += 1;
    }

    if (jobDetails.result["jobComplete"] === true) {
      jobDetails.status = "success";
      jobDetails.statusMessage = "success"; // TODO: Change message to be more descriptive
    }
  } catch (e) {
    jobDetails.status = "error";
    jobDetails.statusMessage = "error";
    jobDetails.errorMessage = e.message;
  }

  return jobDetails;
};

クエリ結果を受け取って、データを抽出する

jobDetailsのresultに、
BigQuery.Jobs.queryの結果が入っているので、取り出して値だけにしてあげるのも一応。

const customJobQueryGetResult = (jobDetails) => {
  const rows = jobDetails.result["rows"].map((row) => {
    return row["f"].map((cell) => cell["v"]);
  });
  return rows;
};

使用例

sample関数

const sample = () => {
  const projectID = "project-id";
  const sql = "SELECT ... FROM ...";

  const jobDetails = customJobQuery(projectID, sql);
  const rows = customJobQueryGetResult(jobDetails);

  console.log(rows);
};

sample関数 実行キャプチャ

※BigQueryAPIをONにしておく必要があります。

まとめ

できるだけペタっと貼ってそのまま使えるような状態を目指しました〜！

projectIDとSQLだけ修正してそのまま既存のプロジェクトにご利用ください

自分も使います

おわり。

参考URL