查询外部表中的 Cloud Storage 数据

本文档介绍了如何查询存储在 Cloud Storage 外部表中的数据。

准备工作

确保您拥有 Cloud Storage 外部表。

所需的角色

如需查询 Cloud Storage 外部表，请确保您具有以下角色：

  • BigQuery Data Viewer (roles/bigquery.dataViewer)
  • BigQuery User (roles/bigquery.user)
  • Storage Object Viewer (roles/storage.objectViewer)

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

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

所需权限

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

查询永久外部表

创建 Cloud Storage 外部表后，您可以使用 GoogleSQL 语法查询该表，就如查询标准 BigQuery 表一样。例如 SELECT field1, field2 FROM mydataset.my_cloud_storage_table;。

查询临时外部表

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

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

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

使用临时外部表时，并不会在您的某个 BigQuery 数据集中创建表。由于该表不会永久存储在数据集内，因此无法与他人共享。

您可以使用 bq 命令行工具、API 或客户端库来创建和查询链接到外部数据源的临时表。

bq

您可以使用带有 --external_table_definition 标志的 bq query 命令查询链接到外部数据源的临时表。使用 bq 命令行工具查询链接到外部数据源的临时表时，您可以使用以下项来标识表的架构：

（可选）提供 --location 标志并将其值设置为您的位置。

如需使用表定义文件查询链接到外部数据源的临时表，请输入以下命令。

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

替换以下内容：

  • LOCATION：您所在位置的名称。--location 是可选标志。例如，如果您在东京区域使用 BigQuery，可将该标志的值设置为 asia-northeast1。您可以使用 .bigqueryrc 文件设置该位置的默认值。
  • 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=BUCKET_PATH \
'QUERY'

替换以下内容：

  • LOCATION：您所在位置的名称。--location 是可选标志。例如，如果您在东京区域使用 BigQuery，可将该标志的值设置为 asia-northeast1。您可以使用 .bigqueryrc 文件设置该位置的默认值。
  • TABLE：您要创建的临时表的名称。
  • SCHEMA：内嵌架构定义，格式为 field:data_type,field:data_type。
  • SOURCE_FORMAT：外部数据源的格式。例如 CSV。

  • BUCKET_PATH：包含表数据的 Cloud Storage 存储桶的路径，格式为 gs://bucket_name/[folder_name/]file_pattern。

    您可以通过在 file_pattern 中指定一个星号 (*) 通配符从存储桶中选择多个文件。例如 gs://mybucket/file00*.parquet。如需了解详情，请参阅 Cloud Storage URI 的通配符支持。

    您可以通过提供多个路径来为 uris 选项指定多个存储桶。

    以下示例展示了有效的 uris 值：

    • gs://bucket/path1/myfile.csv
    • gs://bucket/path1/*.parquet
    • gs://bucket/path1/file1*、gs://bucket1/path1/*

    如果指定以多个文件为目标的 uris 值，则所有这些文件都必须共享一个兼容的架构。

    如需详细了解如何在 BigQuery 中使用 Cloud Storage URI，请参阅 Cloud Storage 资源路径。

  • QUERY：您要提交到临时表的查询。

例如，以下命令使用以下架构定义创建并查询名为 sales 的临时表，该表链接到存储在 Cloud Storage 中的 CSV 文件：Region:STRING,Quarter:STRING,Total_sales:INTEGER。

bq query \
--external_table_definition=sales::Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=gs://mybucket/sales.csv \
'SELECT
  Region,
  Total_sales
FROM
  sales'

如需使用 JSON 架构文件查询链接到外部数据源的临时表，请输入以下命令。

bq --location=LOCATION query \
--external_table_definition=SCHEMA_FILE@SOURCE_FORMAT=BUCKET_PATH \
'QUERY'

替换以下内容：

  • LOCATION：您所在位置的名称。--location 是可选标志。例如，如果您在东京区域使用 BigQuery，可将该标志的值设置为 asia-northeast1。您可以使用 .bigqueryrc 文件设置该位置的默认值。
  • SCHEMA_FILE：本地机器上 JSON 架构文件的路径。
  • SOURCE_FORMAT：外部数据源的格式。例如 CSV。

  • BUCKET_PATH：包含表数据的 Cloud Storage 存储桶的路径，格式为 gs://bucket_name/[folder_name/]file_pattern。

    您可以通过在 file_pattern 中指定一个星号 (*) 通配符从存储桶中选择多个文件。例如 gs://mybucket/file00*.parquet。如需了解详情，请参阅 Cloud Storage URI 的通配符支持。

    您可以通过提供多个路径来为 uris 选项指定多个存储桶。

    以下示例展示了有效的 uris 值：

    • gs://bucket/path1/myfile.csv
    • gs://bucket/path1/*.parquet
    • gs://bucket/path1/file1*、gs://bucket1/path1/*

    如果指定以多个文件为目标的 uris 值，则所有这些文件都必须共享一个兼容的架构。

    如需详细了解如何在 BigQuery 中使用 Cloud Storage URI，请参阅 Cloud Storage 资源路径。

  • QUERY：您要提交到临时表的查询。

例如，以下命令使用 /tmp/sales_schema.json 架构文件创建并查询名为 sales 的临时表，该表链接到存储在 Cloud Storage 中的 CSV 文件。

  bq query \
  --external_table_definition=sales::/tmp/sales_schema.json@CSV=gs://mybucket/sales.csv \
  'SELECT
      Region,
      Total_sales
    FROM
      sales'

API

要使用 API 运行查询，请按以下步骤操作：

  1. 创建一个 Job 对象。
  2. 使用 JobConfiguration 对象填充 Job 对象的 configuration 部分。
  3. 使用 JobConfigurationQuery 对象填充 JobConfiguration 对象的 query 部分。
  4. 使用 ExternalDataConfiguration 对象填充 JobConfigurationQuery 对象的 tableDefinitions 部分。
  5. 调用 jobs.insert 方法以异步运行查询，或调用 jobs.query 方法以同步运行查询，同时传入Job 对象。

Java

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

如需向 BigQuery 进行身份验证，请设置应用默认凭证。如需了解详情，请参阅为客户端库设置身份验证。 

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.CsvOptions;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableResult;

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

  public static void runQueryExternalGCSTemp() {
    // TODO(developer): Replace these variables before running the sample.
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/us-states/us-states.csv";
    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);
    queryExternalGCSTemp(tableName, sourceUri, schema, query);
  }

  public static void queryExternalGCSTemp(
      String tableName, String sourceUri, Schema schema, String query) {
    try {
      // 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.getDefaultInstance().getService();

      // Skip header row in the file.
      CsvOptions csvOptions = CsvOptions.newBuilder().setSkipLeadingRows(1).build();

      // Configure the external data source and query job.
      ExternalTableDefinition externalTable =
          ExternalTableDefinition.newBuilder(sourceUri, csvOptions).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 e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

Node.js

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

如需向 BigQuery 进行身份验证，请设置应用默认凭证。如需了解详情，请参阅为客户端库设置身份验证。 

// Import the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function queryExternalGCSTemp() {
  // Queries an external data source using a temporary table.

  const tableId = 'us_states';

  // Configure the external data source
  const externalDataConfig = {
    sourceFormat: 'CSV',
    sourceUris: ['gs://cloud-samples-data/bigquery/us-states/us-states.csv'],
    // Optionally skip header row.
    csvOptions: {skipLeadingRows: 1},
    schema: {fields: schema},
  };

  // Example query to find states starting with 'W'
  const query = `SELECT post_abbr
  FROM \`${tableId}\`
  WHERE name LIKE 'W%'`;

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    query,
    tableDefinitions: {[tableId]: externalDataConfig},
  };

  // Run the query as a job
  const [job] = await bigquery.createQueryJob(options);
  console.log(`Job ${job.id} started.`);

  // Wait for the query to finish
  const [rows] = await job.getQueryResults();

  // Print the results
  console.log('Rows:');
  console.log(rows);
}

Python

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

如需向 BigQuery 进行身份验证，请设置应用默认凭证。如需了解详情，请参阅为客户端库设置身份验证。 

from google.cloud import bigquery

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

# Configure the external data source and query job.
external_config = bigquery.ExternalConfig("CSV")
external_config.source_uris = [
    "gs://cloud-samples-data/bigquery/us-states/us-states.csv"
]
external_config.schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
external_config.options.skip_leading_rows = 1
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.

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

查询 _FILE_NAME 伪列

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

_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 伪列构造查询谓词时，会应用使用伪列查询注入时间分区表的类似建议。

后续步骤