创建 Google 云端硬盘外部表

本文档介绍如何基于 Google 云端硬盘中存储的数据创建外部表。

BigQuery 支持基于个人云端硬盘文件和共享文件的外部表。如需详细了解云端硬盘，请参阅云端硬盘培训和帮助。

您可以基于 Google 云端硬盘中具有以下格式的文件创建外部表：

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

准备工作

在创建外部表之前，请收集一些信息并确保您有权创建该表。

检索云端硬盘 URI

如需为 Google 云端硬盘数据源创建外部表，您必须提供云端硬盘 URI。您可以直接从云端硬盘数据的网址中检索云端硬盘 URI：

URI 格式

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

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

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

进行身份验证并启用云端硬盘访问权限

访问云端硬盘中托管的数据需要额外的 OAuth 范围。如需向 BigQuery 进行身份验证并启用云端硬盘访问权限，请执行以下操作：

控制台

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

gcloud

In the Google Cloud console, activate Cloud Shell.

Activate Cloud Shell

At the bottom of the Google Cloud console, a Cloud Shell session starts and displays a command-line prompt. Cloud Shell is a shell environment with the Google Cloud CLI already installed and with values already set for your current project. It can take a few seconds for the session to initialize.
输入以下命令，确保您拥有最新版本的 Google Cloud CLI。
```
gcloud components update
```
输入以下命令，进行 Google 云端硬盘身份验证。
```
gcloud auth login --enable-gdrive-access
```

API

除了 BigQuery 的范围之外，还需请求适当的云端硬盘 OAuth 范围：

运行 gcloud auth login --enable-gdrive-access 命令进行登录。
运行 gcloud auth print-access-token 命令，获取具有云端硬盘范围的 OAuth 访问令牌以供 API 使用。

Python

创建 OAuth 客户端 ID。
执行以下操作，在本地环境中设置具有所需范围的应用默认凭据 (ADC)：
1. 安装 Google Cloud CLI，然后通过运行以下命令对其进行初始化：
```
gcloud init
```
2. 为您的 Google 账号创建本地身份验证凭据：
```
gcloud auth application-default login \
    --client-id-file=CLIENT_ID_FILE \
    --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform
```
  将 CLIENT_ID_FILE 替换为包含您的 OAuth 客户端 ID 的文件。
  
  如需了解详情，请参阅使用 gcloud CLI 提供的用户凭据。

Java

创建 OAuth 客户端 ID。
执行以下操作，在本地环境中设置具有所需范围的应用默认凭据 (ADC)：
1. 安装 Google Cloud CLI，然后通过运行以下命令对其进行初始化：
```
gcloud init
```
2. 为您的 Google 账号创建本地身份验证凭据：
```
gcloud auth application-default login \
    --client-id-file=CLIENT_ID_FILE \
    --scopes=https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/cloud-platform
```
  将 CLIENT_ID_FILE 替换为包含您的 OAuth 客户端 ID 的文件。
  
  如需了解详情，请参阅使用 gcloud CLI 提供的用户凭据。

所需的角色

如需创建外部表，您需要拥有 bigquery.tables.create BigQuery Identity and Access Management (IAM) 权限。

以下每个预定义的 Identity and Access Management 角色都具有此权限：

BigQuery Data Editor (roles/bigquery.dataEditor)
BigQuery Data Owner (roles/bigquery.dataOwner)
BigQuery Admin (roles/bigquery.admin)

如果您不是这些角色中的主账号，请让您的管理员授予您访问权限或为您创建外部表。

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

创建外部表

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

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

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

控制台

在 Google Cloud 控制台中，打开 BigQuery 页面。

转到 BigQuery

在浏览器面板中，展开您的项目并选择数据集。
展开操作选项，然后点击打开。
在详情面板中，点击创建表 。
在创建表页面的来源部分，执行以下操作：
- 在基于以下数据创建表部分，选择云端硬盘。
- 在选择云端硬盘 URI 字段中，输入 Google 云端硬盘 URI。请注意，Google 云端硬盘 URI 不支持使用通配符。
- 对于文件格式，请选择数据格式。Google 云端硬盘数据的有效格式包括：
  - 逗号分隔值 (CSV)
  - 以换行符分隔的 JSON
  - Avro
  - 表格
  注意：如果在 Google Cloud 控制台中选择 Avro 或 Datastore 备份，则系统会隐藏自动选择选项，因为这些文件类型属于自描述类型。
（可选）如果选择“Google 表格”，请在工作表范围（可选）框中指定要查询的工作表和单元格范围。您可以指定工作表名称，也可以指定 sheet_name!top_left_cell_id:bottom_right_cell_id 作为单元格范围，例如“Sheet1!A1:B20”。如果未指定工作表范围，则系统会使用文件中的第一个工作表。
在创建表页面的目标部分，执行以下操作：
- 对于数据集名称，请选择相应的数据集，然后在表名称字段中输入您要在 BigQuery 中创建的表的名称。
- 验证表类型设置为外部表。
在架构部分中，输入架构定义。
- 对于 JSON 或 CSV 文件，您可以勾选自动检测选项，以启用架构自动检测功能。自动检测功能不适用于 Datastore 导出文件、Firestore 导出文件和 Avro 文件。对于这些文件类型，系统将自动从自描述源数据中检索其架构信息。
- 通过以下方式，手动输入架构信息：
  - 启用以文本形式修改，并以 JSON 数组格式输入表架构。注意：您可以在 bq 命令行工具中输入以下命令，以 JSON 格式查看现有表架构：bq show --format=prettyjson DATASET.TABLE。
  - 使用添加字段手动输入架构。
点击创建表。
如有必要，选择您的账号，然后点击允许，以授予 BigQuery 客户端工具对 Google 云端硬盘的访问权限。

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

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

bq

您可以在 bq 命令行工具中使用 bq mk 命令创建表。使用 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

from google.cloud import bigquery
import google.auth

credentials, project = google.auth.default()

# 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]
options = external_config.google_sheets_options
assert options is not None
options.skip_leading_rows = 1  # Optionally skip header row.
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)

results = client.query_and_wait(sql)  # Make an API request.

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

Java

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 {

      GoogleCredentials credentials =
          ServiceAccountCredentials.getApplicationDefault();

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

查询外部表

如需了解详情，请参阅查询云端硬盘数据。

`_FILE_NAME` 伪列

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

_FILE_NAME 列名为预留名称，也就是说，您不能在任何表中使用该名称创建列。

创建 Google 云端硬盘外部表

准备工作

检索云端硬盘 URI

进行身份验证并启用云端硬盘访问权限

控制台

gcloud

API

Python

Java

所需的角色

创建外部表

控制台

bq

API

Python

Java

查询外部表

_FILE_NAME 伪列

`_FILE_NAME` 伪列