28
12

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

HRBrainAdvent Calendar 2023

Day 10

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

Last updated at Posted at 2023-12-09

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

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

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

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

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

目次

共通の値を書いておく:writing_hand:

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

  return {
    jobDetailsTemplate,
  };
};

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

BigQueryにクエリを投げる:writing_hand:

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;
};

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

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

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

使用例:writing_hand:

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にしておく必要があります。
image.png

まとめ:writing_hand:

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

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

自分も使います:relaxed:

おわり。

参考URL

28
12
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
28
12

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?