BigQuery 服務

您可以在 Apps Script 中使用 BigQuery 服務的 Google BigQuery API。這個 API 可讓使用者管理 BigQuery 專案、上傳新資料及執行查詢。

注意： 這項進階服務必須先啟用才能使用。

參考資料

如要進一步瞭解這項服務，請參閱 BigQuery API 的參考說明文件。與 Apps Script 中的所有進階服務一樣，BigQuery 服務使用的物件、方法和參數都與公開 API 相同。詳情請參閱「如何判斷方法簽章」。

如要回報問題及尋求其他支援，請參閱 Google Cloud 支援指南。

程式碼範例

下列程式碼範例使用 API 的第 2 版。

執行查詢

這個範例會查詢每日 Google 熱門搜尋字詞清單。

advanced/bigquery.gs

前往 GitHub 查看

/**
 * Runs a BigQuery query and logs the results in a spreadsheet.
 */
function runQuery() {
  // Replace this value with the project ID listed in the Google
  // Cloud Platform project.
  const projectId = "XXXXXXXX";

  const request = {
    // TODO (developer) - Replace query with yours
    query:
      "SELECT refresh_date AS Day, term AS Top_Term, rank " +
      "FROM `bigquery-public-data.google_trends.top_terms` " +
      "WHERE rank = 1 " +
      "AND refresh_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 2 WEEK) " +
      "GROUP BY Day, Top_Term, rank " +
      "ORDER BY Day DESC;",
    useLegacySql: false,
  };
  let queryResults = BigQuery.Jobs.query(request, projectId);
  const jobId = queryResults.jobReference.jobId;

  // Check on status of the Query Job.
  let sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
  }

  // Get all the rows of results.
  let rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
      pageToken: queryResults.pageToken,
    });
    rows = rows.concat(queryResults.rows);
  }

  if (!rows) {
    console.log("No rows returned.");
    return;
  }
  const spreadsheet = SpreadsheetApp.create("BigQuery Results");
  const sheet = spreadsheet.getActiveSheet();

  // Append the headers.
  const headers = queryResults.schema.fields.map((field) => field.name);
  sheet.appendRow(headers);

  // Append the results.
  const data = new Array(rows.length);
  for (let i = 0; i < rows.length; i++) {
    const cols = rows[i].f;
    data[i] = new Array(cols.length);
    for (let j = 0; j < cols.length; j++) {
      data[i][j] = cols[j].v;
    }
  }
  sheet.getRange(2, 1, rows.length, headers.length).setValues(data);

  console.log("Results spreadsheet created: %s", spreadsheet.getUrl());
}

載入 CSV 資料

這個範例會建立新資料表，並將 Google 雲端硬碟中的 CSV 檔案載入其中。

advanced/bigquery.gs

前往 GitHub 查看

/**
 * Loads a CSV into BigQuery
 */
function loadCsv() {
  // Replace this value with the project ID listed in the Google
  // Cloud Platform project.
  const projectId = "XXXXXXXX";
  // Create a dataset in the BigQuery UI (https://bigquery.cloud.google.com)
  // and enter its ID below.
  const datasetId = "YYYYYYYY";
  // Sample CSV file of Google Trends data conforming to the schema below.
  // https://docs.google.com/file/d/0BwzA1Orbvy5WMXFLaTR1Z1p2UDg/edit
  const csvFileId = "0BwzA1Orbvy5WMXFLaTR1Z1p2UDg";

  // Create the table.
  const tableId = `pets_${new Date().getTime()}`;
  let table = {
    tableReference: {
      projectId: projectId,
      datasetId: datasetId,
      tableId: tableId,
    },
    schema: {
      fields: [
        { name: "week", type: "STRING" },
        { name: "cat", type: "INTEGER" },
        { name: "dog", type: "INTEGER" },
        { name: "bird", type: "INTEGER" },
      ],
    },
  };
  try {
    table = BigQuery.Tables.insert(table, projectId, datasetId);
    console.log("Table created: %s", table.id);
  } catch (err) {
    console.log("unable to create table");
  }
  // Load CSV data from Drive and convert to the correct format for upload.
  const file = DriveApp.getFileById(csvFileId);
  const data = file.getBlob().setContentType("application/octet-stream");

  // Create the data upload job.
  const job = {
    configuration: {
      load: {
        destinationTable: {
          projectId: projectId,
          datasetId: datasetId,
          tableId: tableId,
        },
        skipLeadingRows: 1,
      },
    },
  };
  try {
    const jobResult = BigQuery.Jobs.insert(job, projectId, data);
    console.log(`Load job started. Status: ${jobResult.status.state}`);
  } catch (err) {
    console.log("unable to insert job");
  }
}