本頁面由 Cloud Translation API 翻譯而成。

查詢雲端硬碟資料

本文說明如何查詢儲存在 Google 雲端硬碟外部資料表中的資料。

BigQuery 同時支援查詢個人雲端硬碟檔案和共用檔案。如要進一步瞭解雲端硬碟，請參閱「Google 雲端硬碟訓練課程和相關說明」。

您可以透過永久外部資料表或臨時外部資料表查詢雲端硬碟資料，臨時外部資料表會在您執行查詢時建立。

限制

如要瞭解外部資料表的相關限制，請參閱外部資料表限制。

必要的角色

如要查詢雲端硬碟外部資料表，請確認您具備下列角色：

BigQuery 資料檢視者 (roles/bigquery.dataViewer)
BigQuery 使用者 (roles/bigquery.user)

視權限而定，您可以將這些角色授予自己，或請管理員授予您這些角色。如要進一步瞭解如何授予角色，請參閱查看可針對資源授予的角色。

如要查看查詢外部資料表所需的確切 BigQuery 權限，請展開「必要權限」部分：

所需權限

bigquery.jobs.create
bigquery.readsessions.create (只有在使用 BigQuery Storage Read API 讀取資料時才需要)
bigquery.tables.get
bigquery.tables.getData

您或許還可透過自訂角色或其他預先定義的角色取得這些權限。

雲端硬碟權限

您至少必須擁有連結至外部資料表之 Google 雲端硬碟檔案的 View 權限，才能查詢 Google 雲端硬碟中的外部資料。

Compute Engine 執行個體的範圍

建立 Compute Engine 執行個體時，您可以指定執行個體的範圍清單。這個範圍會控管執行個體對 Google Cloud產品 (包含雲端硬碟) 的存取權。在 VM 上執行的應用程式會使用服務帳戶呼叫 Google Cloud API。

如果將 Compute Engine 執行個體設為以服務帳戶的形式執行，且這個服務帳戶會存取連結至雲端硬碟資料來源的外部資料表，則您必須為執行個體新增雲端硬碟的 OAuth 範圍 (https://www.googleapis.com/auth/drive.readonly)。

如需為 Compute Engine 執行個體套用範圍的相關資訊，請參閱變更執行個體的服務帳戶與存取權範圍一節。如要進一步瞭解 Compute Engine 服務帳戶，請參閱服務帳戶一文。

使用永久外部資料表查詢雲端硬碟資料

建立雲端硬碟外部資料表後，您可以使用 GoogleSQL 語法查詢，就像查詢標準 BigQuery 資料表一樣。例如：SELECT field1, field2 FROM mydataset.my_drive_table;。

使用臨時資料表查詢雲端硬碟資料

使用臨時資料表查詢外部資料來源，對於一次性、臨時查詢外部資料，或對擷取、轉換和載入 (ETL) 處理程序而言非常有用。

如要查詢外部資料來源，但不想建立永久資料表，請提供臨時資料表的資料表定義，然後在指令或呼叫中使用該資料表定義，查詢臨時資料表。您可以透過下列任一方式提供資料表定義：

系統會使用資料表定義檔或提供的結構定義來建立臨時外部資料表，然後對臨時外部資料表執行查詢。

使用臨時外部資料表時，並不會在某個 BigQuery 資料集中建立資料表。因為資料表不會永久儲存在資料集中，所以無法與其他使用者分享。

建立及查詢臨時資料表

您可以使用 bq 指令列工具、API 或用戶端程式庫，建立和查詢連結到外部資料來源的臨時資料表。

bq

您可以搭配 --external_table_definition 旗標使用 bq query 指令，查詢已連結至外部資料來源的臨時資料表。使用 bq 指令列工具查詢連結至外部資料來源的臨時資料表時，可以透過以下項目識別資料表的結構定義：

資料表定義檔 (儲存在本機)
內嵌結構定義
JSON 結構定義檔 (儲存在本機)

如要使用資料表定義檔查詢已連結至外部資料來源的臨時資料表，請輸入下列指令。

bq --location=LOCATION query \
--external_table_definition=TABLE::DEFINITION_FILE \
'QUERY'

其中：

LOCATION 是您的位置。--location 是選用旗標。
TABLE 是要建立的臨時資料表名稱。
DEFINITION_FILE 是本機電腦上資料表定義檔的路徑。
QUERY 是要提交至臨時資料表的查詢。

舉例來說，下列指令會使用名為 sales_def 的資料表定義檔，建立及查詢名為 sales 的臨時資料表。

bq query \
--external_table_definition=sales::sales_def \
'SELECT
   Region,Total_sales
 FROM
   sales'

如要使用內嵌結構定義來查詢連結至外部資料來源的臨時資料表，請輸入下列指令。

bq --location=LOCATION query \
--external_table_definition=TABLE::SCHEMA@SOURCE_FORMAT=DRIVE_URI \
'QUERY'

其中：

LOCATION 是您的位置。--location 是選用旗標。
TABLE 是要建立的臨時資料表名稱。
SCHEMA 是結構定義，格式為 FIELD:DATA_TYPE,FIELD:DATA_TYPE。
SOURCE_FORMAT 為 CSV、NEWLINE_DELIMITED_JSON、AVRO 或 GOOGLE_SHEETS。
DRIVE_URI 是您的雲端硬碟 URI。
QUERY 是要提交至臨時資料表的查詢。

舉例來說，下列指令會使用 Region:STRING,Quarter:STRING,Total_sales:INTEGER 結構定義，建立和查詢名為 sales 的臨時資料表，且此表會連結至儲存在雲端硬碟中的 CSV 檔案。

bq --location=US query \
--external_table_definition=sales::Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \
'SELECT
   Region,Total_sales
 FROM
   sales'

如要使用 JSON 結構定義檔來查詢連接外部資料來源的臨時資料表，請輸入下列指令。

bq --location=LOCATION query \
--external_table_definition=SCHEMA_FILE@SOURCE_FORMT=DRIVE_URI \
'QUERY'

其中：

LOCATION 是您的位置。--location 是選用旗標。
SCHEMA_FILE 是您本機上的 JSON 結構定義檔路徑。
SOURCE_FILE 為 CSV、NEWLINE_DELIMITED_JSON、AVRO 或 GOOGLE_SHEETS。
DRIVE_URI 是您的雲端硬碟 URI。
QUERY 是要提交至臨時資料表的查詢。

舉例來說，下列指令會使用 /tmp/sales_schema.json 結構定義檔，建立和查詢名為 sales 的臨時資料表，且此表會連結至儲存在雲端硬碟中的 CSV 檔案。

bq query \
--external_table_definition=sales::/tmp/sales_schema.json@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \
'SELECT
   Total_sales
 FROM
   sales'

API

建立查詢工作設定。如需有關呼叫 jobs.query 和 jobs.insert 的資訊，請參閱查詢資料的相關說明。
建立 ExternalDataConfiguration 以指定外部資料來源。

Python

在試行這個範例之前，請先按照 BigQuery 快速入門導覽課程：使用用戶端程式庫中的 Python 設定說明進行操作。詳情請參閱 BigQuery Python API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

from google.cloud import bigquery
import google.auth

# Create credentials with Drive & BigQuery API scopes.
# Both APIs must be enabled for your project before running this code.
credentials, project = google.auth.default(
    scopes=[
        "https://www.googleapis.com/auth/drive",
        "https://www.googleapis.com/auth/bigquery",
    ]
)

# Construct a BigQuery client object.
client = bigquery.Client(credentials=credentials, project=project)

# Configure the external data source and query job.
external_config = bigquery.ExternalConfig("GOOGLE_SHEETS")

# Use a shareable link or grant viewing access to the email address you
# used to authenticate with BigQuery (this example Sheet is public).
sheet_url = (
    "https://docs.google.com/spreadsheets"
    "/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing"
)
external_config.source_uris = [sheet_url]
external_config.schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
external_config.options.skip_leading_rows = 1  # Optionally skip header row.
external_config.options.range = (
    "us-states!A20:B49"  # Optionally set range of the sheet to query from.
)
table_id = "us_states"
job_config = bigquery.QueryJobConfig(table_definitions={table_id: external_config})

# Example query to find states starting with "W".
sql = 'SELECT * FROM `{}` WHERE name LIKE "W%"'.format(table_id)

query_job = client.query(sql, job_config=job_config)  # Make an API request.

# Wait for the query to complete.
w_states = list(query_job)
print(
    "There are {} states with names starting with W in the selected range.".format(
        len(w_states)
    )
)

Java

在試行這個範例之前，請先按照 BigQuery 快速入門導覽課程：使用用戶端程式庫中的 Java 設定說明進行操作。詳情請參閱 BigQuery Java API 參考說明文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

import com.google.auth.oauth2.GoogleCredentials;
import com.google.auth.oauth2.ServiceAccountCredentials;
import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.GoogleSheetsOptions;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableResult;
import com.google.common.collect.ImmutableSet;
import java.io.IOException;

// Sample to queries an external data source using a temporary table
public class QueryExternalSheetsTemp {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String tableName = "MY_TABLE_NAME";
    String sourceUri =
        "https://docs.google.com/spreadsheets/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing";
    Schema schema =
        Schema.of(
            Field.of("name", StandardSQLTypeName.STRING),
            Field.of("post_abbr", StandardSQLTypeName.STRING));
    String query = String.format("SELECT * FROM %s WHERE name LIKE 'W%%'", tableName);
    queryExternalSheetsTemp(tableName, sourceUri, schema, query);
  }

  public static void queryExternalSheetsTemp(
      String tableName, String sourceUri, Schema schema, String query) {
    try {

      // Create credentials with Drive & BigQuery API scopes.
      // Both APIs must be enabled for your project before running this code.
      GoogleCredentials credentials =
          ServiceAccountCredentials.getApplicationDefault()
              .createScoped(
                  ImmutableSet.of(
                      "https://www.googleapis.com/auth/bigquery",
                      "https://www.googleapis.com/auth/drive"));

      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery =
          BigQueryOptions.newBuilder().setCredentials(credentials).build().getService();

      // Skip header row in the file.
      GoogleSheetsOptions sheetsOptions =
          GoogleSheetsOptions.newBuilder()
              .setSkipLeadingRows(1) // Optionally skip header row.
              .setRange("us-states!A20:B49") // Optionally set range of the sheet to query from.
              .build();

      // Configure the external data source and query job.
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, sheetsOptions).setSchema(schema).build();
      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query)
              .addTableDefinition(tableName, externalTable)
              .build();

      // Example query to find states starting with 'W'
      TableResult results = bigquery.query(queryConfig);

      results
          .iterateAll()
          .forEach(row -> row.forEach(val -> System.out.printf("%s,", val.toString())));

      System.out.println("Query on external temporary table performed successfully.");
    } catch (BigQueryException | InterruptedException | IOException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

疑難排解

錯誤字串：Resources exceeded during query execution: Google Sheets service overloaded.

這可能是暫時性錯誤，重新執行查詢即可修正。如果重新執行查詢後仍發生錯誤，請考慮簡化試算表，例如盡量減少使用公式。詳情請參閱外部資料表限制。

後續步驟

瞭解如何在 BigQuery 中使用 SQL。
瞭解外部資料表。
瞭解 BigQuery 配額。