Google 會運用 AI 技術將內容翻譯成你偏好的語言，但可能會出錯。

查詢雲端硬碟外部資料表

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

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

您可以從永久外部資料表或執行查詢時建立的暫時外部資料表查詢雲端硬碟資料。

限制

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

必要的角色

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

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

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

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

所需權限

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

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

雲端硬碟權限

如要查詢雲端硬碟中的外部資料，您至少必須具備連結至外部資料表的雲端硬碟檔案View存取權。

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 是要提交至臨時資料表的查詢。

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

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.

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

錯誤字串：Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials

如要解決這項錯誤，請按照下列步驟操作：

確認您具備連結至外部資料表的雲端硬碟檔案檢視權限。
如果 bq 指令列工具版本為 2.1.12 以下，請使用 --enable-gdrive-access 旗標。
確認您或執行查詢的服務帳戶已具備必要角色，可查詢 Google 雲端硬碟外部資料表。

後續步驟

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