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

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

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

控制台

在 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
    
  4. 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 是您要创建的表的名称。

    如果您的表定义文件包含专用于 Google 表格的配置,则可以跳过前几行并指定定义的工作表范围。

    以下示例使用 /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 列名为预留名称,也就是说,您不能在任何表中使用该名称创建列。