查询云端硬盘数据

本文档介绍如何查询存储在 Google 云端硬盘外部表中的数据。

BigQuery 支持查询个人 Google 云端硬盘文件和共享文件。如需详细了解 Google 云端硬盘，请参阅 Google 云端硬盘培训和帮助。

您可以从永久外部表或运行查询时创建的临时外部表中查询云端硬盘数据。

所需的角色

如需查询 Google 云端硬盘外部表，请确保您具有以下角色：

• BigQuery Data Viewer (roles/bigquery.dataViewer)
• BigQuery User (roles/bigquery.user)

根据您的权限，您可以自行授予这些角色给自己，或者让管理员授予给您。如需详细了解如何授予角色，请参阅查看可针对资源授予的角色。

如需查看查询外部表所需的确切 BigQuery 权限，请展开所需权限部分：

所需权限

• bigquery.jobs.create
• bigquery.readsessions.create（仅当您使用 BigQuery Storage Read API 读取数据时才需要）
• bigquery.tables.get
• bigquery.tables.getData

您也可以使用自定义角色或其他预定义角色来获取这些权限。

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 服务账号，请参阅服务账号。

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

创建云端硬盘外部表后，您可以使用 GoogleSQL 语法进行查询，就像标准 BigQuery 表一样。例如 SELECT field1, field2 FROM mydataset.my_drive_table;。

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

使用临时表查询外部数据源适用于对外部数据进行一次性临时查询，或执行提取、转换和加载 (ETL) 过程。

要在不创建永久表的情况下查询外部数据源，请为临时表提供表定义，然后在命令或调用中使用该表定义来查询临时表。您可以通过以下任一方式提供表定义：

• 表定义文件
• 内嵌架构定义
• JSON 架构文件

系统会使用表定义文件或提供的架构来创建临时外部表，然后对临时外部表运行查询。

使用临时外部表时，并不会在您的某个 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 是您的 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 参考文档。

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

限制

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

后续步骤

• 了解如何在 BigQuery 中使用 SQL。
• 了解外部表。
• 了解 BigQuery 配额。