查询云端硬盘数据

查询云端硬盘数据

本页面介绍如何使用 BigQuery 查询存储在云端硬盘中的数据。

BigQuery 支持查询个人 Google 云端硬盘文件和共享文件。如需详细了解 Google 云端硬盘，请参阅 G Suite 学习中心。

您可以查询 Google 云端硬盘中以下格式的文件：

• 逗号分隔值 (CSV)
• 以换行符分隔的 JSON
• Avro
• 表格

如需查询 Google 云端硬盘外部数据源，请提供数据的 Google 云端硬盘 URI 路径，并创建一个引用该数据源的外部表。您可以使用永久表或临时表引用该 Google 云端硬盘数据源。

限制

BigQuery 查询可能会使表格过载，从而导致Resources exceeded during query execution: Google Sheets service overloaded. 等错误。请考虑简化电子表格；例如，尽量减少公式的使用。

检索云端硬盘 URI

如需为 Google 云端硬盘数据源创建外部表，您必须提供 Google 云端硬盘 URI。如需检索 Google 云端硬盘 URI，请参阅共享指向该文件的链接。

URI 格式

• https://docs.google.com/spreadsheets/d/FILE_ID

  或

• https://drive.google.com/open?id=FILE_ID

其中 FILE_ID 是 Google 云端硬盘文件的字母数字 ID。

启用云端硬盘访问权限

无论是在定义联合来源时还是在查询执行期间，访问 Google 云端硬盘中托管的数据都需要额外的 OAuth 范围。 尽管系统在默认情况下并未启用此访问权限，但您可以通过以下机制使用 Cloud Console、bq 命令行工具或 API 启用它：

控制台

在 Cloud Console 中创建永久表时，请按照基于 Web 的身份验证步骤进行操作。出现提示时，请点击允许，以授予 BigQuery 客户端工具对 Google 云端硬盘的访问权限。

gcloud

启用 Google 云端硬盘访问权限的方法如下：

1. 输入以下命令，确保您拥有最新版本的 Google Cloud CLI。

   gcloud components update
   

2. 输入以下命令，进行 Google 云端硬盘身份验证。

   gcloud auth login --enable-gdrive-access
   

注意：如果您使用 Cloud Shell 访问云端硬盘数据，则无需更新 Google Cloud CLI 或向云端硬盘验证身份

API

如果您使用的是 BigQuery API，则除了 BigQuery 的范围之外，还应请求 Google 云端硬盘的 OAuth 范围。

Python

尝试此示例之前，请按照《BigQuery 快速入门：使用客户端库》中的 Python 设置说明进行操作。如需了解详情，请参阅 BigQuery Python API 参考文档。

在 GitHub 上查看 反馈

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)

Java

试用此示例之前，请按照《BigQuery 快速入门：使用客户端库》中的 Java 设置说明进行操作。如需了解详情，请参阅 BigQuery Java API 参考文档。

在 GitHub 上查看 反馈

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();

永久外部表与临时外部表

您可以使用永久表或临时表在 BigQuery 中查询外部数据源。永久表是在数据集中创建的表，该表链接到外部数据源。由于该表是永久性的，因此您可以使用访问权限控制与其他同样有权访问底层外部数据源的人员共享该表，还可以随时查询该表。

使用临时表查询外部数据源时，您需要提交一个命令，该命令必须包含查询并创建一个链接到外部数据源的非永久表。使用临时表时，不会在任何 BigQuery 数据集内创建表。由于该表并非永久存储在数据集内，因此无法与他人共享。使用临时表查询外部数据源适用于对外部数据进行一次性临时查询，或执行提取、转换和加载 (ETL) 过程。

使用永久外部表查询云端硬盘数据

所需的权限和范围

当您使用永久表在云端硬盘中查询外部数据时，您需要拥有相关权限，才能在项目级别或更高级别运行查询作业，创建指向外部数据的表以及访问表数据。如果外部数据存储在 Google 云端硬盘中，您还需要有权访问链接到外部表的 Google 云端硬盘文件。

BigQuery 权限

如需在 BigQuery 中创建和查询外部表，您至少需要具备以下权限。

• bigquery.tables.create
• bigquery.tables.getData
• bigquery.jobs.create

以下预定义的 IAM 角色同时具有 bigquery.tables.create 和 bigquery.tables.getData 权限：

• bigquery.dataEditor
• bigquery.dataOwner
• bigquery.admin

以下预定义的 IAM 角色包含 bigquery.jobs.create 权限：

• bigquery.user
• bigquery.jobUser
• bigquery.admin

此外，如果用户具有 bigquery.datasets.create 权限，则当该用户创建数据集时，系统会为其授予该数据集的 bigquery.dataOwner 访问权限。具有 bigquery.dataOwner 访问权限的用户可以在数据集中创建外部表，但若要查询数据，用户仍需具备 bigquery.jobs.create 权限。

如需详细了解 BigQuery 中的 IAM 角色和权限，请参阅预定义的角色和权限。

Google 云端硬盘权限

如需查询 Google 云端硬盘中的外部数据，您必须至少对链接到外部表的 Google 云端硬盘文件具有 View 访问权限。

Compute Engine 实例的范围

创建 Compute Engine 实例时，您可以为该实例指定一个范围列表。这些范围用于控制该实例对 Google Cloud 产品（包括 Google 云端硬盘）的访问权限。在虚拟机上运行的应用会使用服务帐号来调用 Google Cloud API。

如果您将某个 Compute Engine 实例设置为以服务帐号身份运行，并且该服务帐号可以访问一个链接到 Google 云端硬盘数据源的外部表，则您必须为该实例添加 Google 云端硬盘 OAuth 范围 (https://www.googleapis.com/auth/drive.readonly)。

如需了解如何将范围应用于 Compute Engine 实例，请参阅更改实例的服务帐号和访问权限范围。如需详细了解 Compute Engine 服务帐号，请参阅服务帐号。

创建和查询永久外部表

您可以通过以下方式创建链接到外部数据源的永久表：

• 使用 Cloud Console
• 使用 bq 命令行工具的 mk 命令
• 使用 tables.insert API 方法时创建 ExternalDataConfiguration
• 使用客户端库

如需使用永久表查询外部数据源，需要在 BigQuery 数据集中创建一个链接到外部数据源的表。数据不会存储在 BigQuery 表中。由于该表是永久表，因此您可以使用访问权限控制与其他同样有权访问底层外部数据源的人员共享该表。

创建永久外部表时，您可以通过以下方式指定架构：

• 提供显式表架构。
• 使用架构自动检测功能。

如需创建外部表，请执行以下操作：

控制台

1. 在 Cloud Console 中，打开 BigQuery 页面。

转到 BigQuery

1. 在浏览器面板中，展开您的项目并选择数据集。

2. 展开 操作选项，然后点击打开。

3. 在详情面板中，点击创建表 。

4. 在创建表页面的来源部分，执行以下操作：

   • 在基于以下数据创建表部分，选择云端硬盘。

   • 在选择云端硬盘 URI 字段中，输入 Google 云端硬盘 URI。请注意，Google 云端硬盘 URI 不支持使用通配符。

   • 对于文件格式，请选择数据格式。Google 云端硬盘数据的有效格式包括：

     • 逗号分隔值 (CSV)
     • 以换行符分隔的 JSON
     • Avro
     • 表格
     注意：如果在 Cloud Console 中选择 Avro 或 Datastore 备份，则系统会隐藏自动选择选项，因为这些文件类型属于自描述类型。

5. （可选）如果选择“Google 表格”，请在工作表范围（可选）框中指定要查询的工作表和单元格范围。您可以指定工作表名称，也可以指定 sheet_name!top_left_cell_id:bottom_right_cell_id 作为单元格范围，例如“Sheet1!A1:B20”。如果未指定工作表范围，则系统会使用文件中的第一个工作表。

6. 在创建表页面的目标部分，执行以下操作：

   • 对于数据集名称，请选择相应的数据集，然后在表名称字段中输入您要在 BigQuery 中创建的表的名称。

     

   • 验证表类型设置为外部表。

7. 在架构部分中，输入架构定义。

   • 对于 JSON 或 CSV 文件，您可以勾选自动检测选项，以启用架构自动检测功能。自动检测功能不适用于 Datastore 导出文件、Firestore 导出文件和 Avro 文件。对于这些文件类型，系统将自动从自描述源数据中检索其架构信息。
   • 通过以下方式，手动输入架构信息：
     • 启用以文本形式修改，并以 JSON 数组格式输入表架构。注意：您可以在 bq 命令行工具中输入以下命令，以 JSON 格式查看现有表架构：bq show --format=prettyjson DATASET.TABLE。
     • 使用添加字段手动输入架构。

8. 点击创建表。

9. 如有必要，选择您的帐号，然后点击允许，以授予 BigQuery 客户端工具对 Google 云端硬盘的访问权限。

接着，您可以对该表运行查询，就像对原生 BigQuery 表运行查询一样，但需遵守外部数据源的限制。

查询完成后，您可以将结果下载为 CSV 或 JSON 格式、将结果保存为表，或将结果保存到 Google 表格。详情请参阅下载、保存和导出数据。

bq

您可以使用 bq mk 命令在 bq 命令行工具中创建表。使用 bq 命令行工具创建链接到外部数据源的表时，您可以使用以下命令标识表的架构：

• 表定义文件（存储在本地机器上）
• 内嵌架构定义
• JSON 架构文件（存储在本地机器上）

如需使用表定义文件创建链接到 Google 云端硬盘数据源的永久表，请输入以下命令。

bq mk \
--external_table_definition=DEFINITION_FILE \
DATASET.TABLE

其中：

• DEFINITION_FILE 是本地机器上表定义文件的路径。
• DATASET 是包含该表的数据集的名称。
• TABLE 是您要创建的表的名称。

例如，以下命令会使用名为 mytable_def 的表定义文件创建名为 mytable 的永久表。

bq mk --external_table_definition=/tmp/mytable_def mydataset.mytable

如需使用内嵌架构定义创建链接到外部数据源的永久表，请输入以下命令。

bq mk \
--external_table_definition=SCHEMA@SOURCE_FORMAT=DRIVE_URI \
DATASET.TABLE

其中：

• SCHEMA 是架构定义，格式为 FIELD:DATA_TYPE,FIELD:DATA_TYPE。
• SOURCE_FORMAT 是 CSV、NEWLINE_DELIMITED_JSON、AVRO 或 GOOGLE_SHEETS。
• DRIVE_URI 是您的 Google 云端硬盘 URI。
• DATASET 是包含表的数据集的名称。
• TABLE 是您要创建的表的名称。

例如，以下命令会使用架构定义 Region:STRING,Quarter:STRING,Total_sales:INTEGER 创建名为 sales 的永久表，该表链接到存储在 Google 云端硬盘中的 Google 表格文件。

bq mk \
--external_table_definition=Region:STRING,Quarter:STRING,Total_sales:INTEGER@GOOGLE_SHEETS=https://drive.google.com/open?id=1234_AbCD12abCd \
mydataset.sales

如需使用 JSON 架构文件创建链接到外部数据源的永久表，请输入以下命令。

bq mk \
--external_table_definition=SCHEMA_FILE@SOURCE_FORMAT=DRIVE_URI \
DATASET.TABLE

其中：

• SCHEMA_FILE 是本地机器上 JSON 架构文件的路径。
• SOURCE_FORMAT 是 CSV、NEWLINE_DELIMITED_JSON、AVRO 或 GOOGLE_SHEETS。
• DRIVE_URI 是您的 Google 云端硬盘 URI。
• DATASET 是包含表的数据集的名称。
• TABLE 是您要创建的表的名称。

例如，以下命令使用 /tmp/sales_schema.json 架构文件创建名为 sales 的表，该表链接到存储在 Google 云端硬盘中的 CSV 文件。

bq mk \
--external_table_definition=/tmp/sales_schema.json@CSV=https://drive.google.com/open?id=1234_AbCD12abCd \
mydataset.sales

创建永久表后，可以对该表运行查询，就像对原生 BigQuery 表运行查询一样，但要受到外部数据源的限制。

查询完成后，您可以将结果下载为 CSV 或 JSON 格式、将结果保存为表，或将结果保存到 Google 表格。详情请参阅下载、保存和导出数据。

API

使用 tables.insert API 方法时创建 ExternalDataConfiguration。指定 schema 属性或将 autodetect 属性设置为 true，为受支持的数据源启用架构自动检测功能。

Python

尝试此示例之前，请按照《BigQuery 快速入门：使用客户端库》中的 Python 设置说明进行操作。如需了解详情，请参阅 BigQuery Python API 参考文档。

在 GitHub 上查看 反馈

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)

# TODO(developer): Set dataset_id to the ID of the dataset to fetch.
# dataset_id = "your-project.your_dataset"

# Configure the external data source.
dataset = client.get_dataset(dataset_id)
table_id = "us_states"
schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
table = bigquery.Table(dataset.table(table_id), schema=schema)
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.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.external_data_configuration = external_config

# Create a permanent table linked to the Sheets file.
table = client.create_table(table)  # Make an API request.

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

query_job = client.query(sql)  # 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 参考文档。

在 GitHub 上查看 反馈

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.TableId;
import com.google.cloud.bigquery.TableInfo;
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 permanent table
public class QueryExternalSheetsPerm {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    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.%s WHERE name LIKE 'W%%'", datasetName, tableName);
    queryExternalSheetsPerm(datasetName, tableName, sourceUri, schema, query);
  }

  public static void queryExternalSheetsPerm(
      String datasetName, 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();

      TableId tableId = TableId.of(datasetName, tableName);
      // Create a permanent table linked to the Sheets file.
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, sheetsOptions).setSchema(schema).build();
      bigquery.create(TableInfo.of(tableId, externalTable));

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

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

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

使用临时表查询云端硬盘数据

如需在不创建永久表的情况下查询外部数据源，请运行命令以便进行以下组合：

• 将表定义文件与查询组合
• 将内嵌架构定义与查询组合
• 将 JSON 架构定义文件与查询组合

系统会使用表定义文件或提供的架构来创建临时外部表，然后对临时外部表运行查询。bq 命令行工具和 API 支持使用临时表查询外部数据源。

使用临时外部表时，并不会在您的某个 BigQuery 数据集中创建表。由于该表不会永久存储在数据集内，因此无法与他人共享。使用临时表查询外部数据源适用于对外部数据进行一次性临时查询，或执行提取、转换和加载 (ETL) 过程。

所需权限

当您使用临时表在 Google 云端硬盘中查询外部数据时，您需要拥有相关权限才能在项目级或更高级层运行查询作业，并且需要对指向该外部数据的表所属数据集的访问权限。在 Google 云端硬盘中查询数据时，您还需要拥有对包含您数据的 Google 云端硬盘文件的访问权限。

BigQuery 权限

如需使用临时表在 BigQuery 中查询外部表，您至少需要拥有以下权限。

• bigquery.tables.getData
• bigquery.jobs.create

以下预定义的 IAM 角色具有 bigquery.tables.getData 权限：

• bigquery.dataEditor
• bigquery.dataOwner
• bigquery.admin

以下预定义的 IAM 角色具有 bigquery.jobs.create 权限：

• bigquery.user
• bigquery.jobUser
• bigquery.admin

此外，如果用户具有 bigquery.datasets.create 权限，则当该用户创建数据集时，系统会为其授予该数据集的 bigquery.dataOwner 访问权限。具有 bigquery.dataOwner 访问权限的用户可以在数据集中创建和访问外部表，但若要查询数据，用户仍需具备 bigquery.jobs.create 权限。

如需详细了解 BigQuery 中的 IAM 角色和权限，请参阅预定义的角色和权限。

Google 云端硬盘权限

如需查询 Google 云端硬盘中的外部数据，您必须至少对链接到外部表的 Google 云端硬盘文件具有 View 访问权限。

创建和查询临时表

您可以使用 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 是您的 Google 云端硬盘 URI。
• QUERY 是您要提交到临时表的查询。

例如，以下命令使用架构定义 Region:STRING,Quarter:STRING,Total_sales:INTEGER 创建并查询名为 sales 的临时表，该表链接到存储在 Google 云端硬盘中的 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 是您的 Google 云端硬盘 URI。
• QUERY 是您要提交到临时表的查询。

例如，以下命令使用 /tmp/sales_schema.json 架构文件创建并查询名为 sales 的临时表，该表链接到存储在 Google 云端硬盘中的 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 参考文档。

在 GitHub 上查看 反馈

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 参考文档。

在 GitHub 上查看 反馈

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

_FILE_NAME 伪列

基于外部数据源的表可提供名为 _FILE_NAME 的伪列。此列包含相应行所属文件的完全限定路径，且仅可用于引用存储在 Cloud Storage 和 Google 云端硬盘中的外部数据的表。

_FILE_NAME 列名为预留名称，也就是说，您不能在任何表中使用该名称创建列。如需选择 _FILE_NAME 的值，您必须使用别名。以下查询示例演示了如何通过将别名 fn 分配给伪列来选择 _FILE_NAME。

bq query \
--project_id=project_id \
--use_legacy_sql=false \
'SELECT
   name,
   _FILE_NAME AS fn
 FROM
   `dataset.table_name`
 WHERE
   name contains "Alex"' 

其中：

• project_id 是有效的项目 ID（如果使用 Cloud Shell 或在 Google Cloud CLI 中设置默认项目，则不需要此标志）
• dataset 是存储永久外部表的数据集名称
• table_name 是永久外部表的名称

如果查询在 _FILE_NAME 伪列上具有过滤条件谓词，则 BigQuery 会尝试跳过不满足过滤条件的读取文件。使用 _FILE_NAME 伪列构建查询谓词时，会应用使用伪列查询提取时间分区表的类似建议。