创建 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

  1. 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.

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

    gcloud components update
    
  3. 输入以下命令,进行 Google 云端硬盘身份验证。

    gcloud auth login --enable-gdrive-access
    

API

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

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

Python

  1. 创建 OAuth 客户端 ID

  2. 执行以下操作,在本地环境中设置具有所需范围的应用默认凭据 (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

  1. 创建 OAuth 客户端 ID

  2. 执行以下操作,在本地环境中设置具有所需范围的应用默认凭据 (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 角色和权限,请参阅预定义的角色和权限

创建外部表

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

如需创建外部表,请执行以下操作:

控制台

  1. 在 Google Cloud 控制台中,打开 BigQuery 页面。

转到 BigQuery

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

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

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

  4. 创建表页面的来源部分,执行以下操作:

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

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

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

      • 逗号分隔值 (CSV)
      • 以换行符分隔的 JSON
      • Avro
      • 表格
  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 命令行工具中使用 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_FORMATCSVNEWLINE_DELIMITED_JSONAVROGOOGLE_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_FORMATCSVNEWLINE_DELIMITED_JSONAVROGOOGLE_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 StorageGoogle 云端硬盘中的外部数据的表。

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