查询 Amazon S3 数据

本文档介绍了如何查询存储在 Amazon Simple Storage Service (Amazon S3) BigLake 表中的数据。

准备工作

确保您拥有 Amazon S3 BigLake 表

所需的角色

如需查询 Amazon S3 BigLake 表,请确保 BigQuery API 的调用方具有以下角色:

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

调用方可以是您的账号或 Amazon S3 连接服务账号。根据您的权限,您可以自行授予这些角色给自己,或者让管理员授予给您。如需详细了解如何授予角色,请参阅查看可针对资源授予的角色

如需查看查询 Amazon S3 BigLake 表所需的确切权限,请展开所需权限部分:

所需权限

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

查询 Amazon S3 BigLake 表

创建 Amazon S3 BigLake 表后,您可以使用 GoogleSQL 语法查询表,就如查询标准 BigQuery 表一样。

缓存的查询结果存储在 BigQuery 临时表中。如需查询临时 BigLake 表,请参阅查询临时 BigLake 表。如需详细了解 BigQuery Omni 限制和配额,请参阅限制配额

在 BigQuery Omni 区域中创建预留时,请使用企业版。如需了解如何使用版本创建预留,请参阅创建预留

在 BigLake Amazon S3 表上运行查询:

SQL

如需查询表,请执行以下操作:

  1. 在 Google Cloud 控制台中,进入 BigQuery 页面。

    转到 BigQuery

  2. 在查询编辑器中,输入以下语句:

    SELECT * FROM DATASET_NAME.TABLE_NAME;

    替换以下内容:

    • DATASET_NAME:您创建的数据集名称
    • TABLE_NAME您创建的表的名称

    • 点击 运行

如需详细了解如何运行查询,请参阅运行交互式查询

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.DatasetId;
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.TableId;
import com.google.cloud.bigquery.TableInfo;
import com.google.cloud.bigquery.TableResult;

// Sample to queries an external data source aws s3 using a permanent table
public class QueryExternalTableAws {

  public static void main(String[] args) throws InterruptedException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    String externalTableName = "MY_EXTERNAL_TABLE_NAME";
    // Query to find states starting with 'W'
    String query =
        String.format(
            "SELECT * FROM s%.%s.%s WHERE name LIKE 'W%%'",
            projectId, datasetName, externalTableName);
    queryExternalTableAws(query);
  }

  public static void queryExternalTableAws(String query) throws InterruptedException {
    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();

      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 aws external permanent table performed successfully.");
    } catch (BigQueryException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

查询临时表

BigQuery 会创建临时表来存储查询结果。如需从临时表中检索查询结果,您可以使用 Google Cloud 控制台或 BigQuery API

从下列选项中选择一项:

控制台

查询引用外部云数据的 BigLake 表时,您可以查看 Google Cloud 控制台中显示的查询结果。

API

如需使用 API 查询 BigLake 表,请按以下步骤操作:

  1. 创建 Job 对象
  2. 调用 jobs.insert 方法以异步运行查询,或调用 jobs.query 方法以同步运行查询,同时传入Job 对象。
  3. 通过传递给定作业引用来读取具有 jobs.getQueryResults 的行,并通过传递查询结果的给定表引用来读取 tabledata.list 方法。

查询 _FILE_NAME 伪列

基于外部数据源的表提供名为 _FILE_NAME 的伪列。此列包含相应行所属文件的完全限定路径。此列仅可用于引用存储在 Cloud StorageGoogle 云端硬盘Amazon S3Azure 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 伪列构造查询谓词时,会应用使用伪列查询注入时间分区表的类似建议。

后续步骤