在表架构中指定 ObjectRef 列

本文档介绍了如何定义包含可存储 ObjectRef 值的列的 BigQuery 标准表架构。

ObjectRef 值可为 Cloud Storage 中的对象提供元数据和连接信息。当您需要将非结构化数据集成到标准表格中时，请使用 ObjectRef 值。例如，在产品表中，您可以通过添加包含 ObjectRef 值的列，将商品图片存储在与其他商品信息在同一行中。您可以在使用 ObjectRef 格式（即 STRUCT<uri STRING, version STRING, authorizer STRING, details JSON>）的 STRUCT 列中存储 ObjectRef 值。

如需详细了解如何处理多模态数据，请参阅分析多模态数据。如需查看介绍如何处理 ObjectRef 数据的教程，请参阅使用 SQL 分析多模态数据。如需了解如何在 Python 中处理多模态数据，请参阅使用 BigQuery DataFrames 在 Python 中分析多模态数据。

前提条件

如需在标准表中填充和更新 ObjectRef 值，该表必须包含一个 STRING 列，其中包含相关 Cloud Storage 对象的 URI 信息。

您必须有一个 Cloud Storage 存储桶，其中包含目标标准表的 URI 数据中标识的相同对象。如果您想使用对象表在标准表中维护 ObjectRef 值，则还必须有一个表示该存储桶中对象的对象表。

维持 ObjectRef 值

您可以使用对象表在标准表中填充和更新 ObjectRef 值。如果您已列入预览功能的许可名单，则您创建的任何对象表都会包含一个 ref 列，其中包含给定对象的 ObjectRef 值。您可以使用对象 URI 将标准表联接到对象表，以填充和更新 ObjectRef 值。我们建议采用这种方法来实现可伸缩性，因为这样可以避免从 Cloud Storage 检索对象元数据。

如果您不想创建对象表，可以使用 OBJ.FETCH_METADATA 和 OBJ.MAKE_REF 函数直接从 Cloud Storage 提取对象元数据，以填充和更新 ObjectRef 值。此方法的伸缩可能较差，因为它需要从 Cloud Storage 检索对象元数据。

创建 ObjectRef 列

如需在标准表中创建和填充 ObjectRef 列，请选择以下选项之一：

对象表

根据对象表 ref 列中的数据创建并填充 ObjectRef 列：

  1. 在 Google Cloud 控制台中，前往 BigQuery 页面。

    转到 BigQuery

  2. 在查询编辑器中，输入以下语句：

    CREATE OR REPLACE TABLE PROJECT_ID.DATASET_ID.TABLE_NAME
AS
SELECT TABLE_NAME.*, OBJECT_TABLE.ref AS objectrefcolumn
FROM DATASET_ID.TABLE_NAME
INNER JOIN DATASET_ID.OBJECT_TABLE
ON OBJECT_TABLE.uri = TABLE_NAME.uri;

    替换以下内容：

    • PROJECT_ID：您的项目 ID。 如果您是在当前项目中创建表，则可以跳过此参数。
    • DATASET_ID：您要创建的数据集的 ID。
    • TABLE_NAME：您要重新创建的标准表的名称。
    • OBJECT_TABLE：包含要集成到标准表中的对象数据的对象表的名称。

  3. 点击 运行。

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

SQL 函数

根据 OBJ.FETCH_METADATA 和 OBJ.MAKE_REF 函数的输出创建并填充 ObjectRef 列：

  1. 在 Google Cloud 控制台中，前往 BigQuery 页面。

    转到 BigQuery

  2. 在查询编辑器中，输入以下语句：

    CREATE OR REPLACE TABLE PROJECT_ID.DATASET_ID.TABLE_NAME
AS
SELECT TABLE_NAME.*,
OBJ.FETCH_METADATA(OBJ.MAKE_REF(uri, 'CONNECTION_ID')) AS objectrefcolumn
FROM DATASET_ID.TABLE_NAME;

    替换以下内容：

    • PROJECT_ID：您的项目 ID。 如果您是在当前项目中创建表，则可以跳过此参数。
    • DATASET_ID：您要创建的数据集的 ID。
    • TABLE_NAME：您要重新创建的标准表的名称。
    • CONNECTION_ID：一个 STRING 值，其中包含服务可用于访问 Cloud Storage 中对象的云资源连接，格式为 location.connection_id。例如 us-west1.myconnection。您可以通过在 Google Cloud 控制台中查看连接详情，然后复制连接 ID 中显示的完全限定连接 ID 的最后一部分中的值来获取连接 ID。例如 projects/myproject/locations/connection_location/connections/myconnection。

      您必须向连接的服务账号授予 Storage Object User (roles/storage.objectUser) 角色，以便该服务账号能够访问您要使用该连接访问对象的任何 Cloud Storage 存储桶。

      连接必须与您调用函数的查询位于同一项目和区域中。

  3. 点击 运行。

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

创建 ARRAY<ObjectRef> 列

您可以创建一个 ARRAY<STRUCT<uri STRING, version STRING, authorizer STRING, details JSON>> 列来包含 ObjectRef 值数组。例如，您可以将视频分块为单独的图片，然后将这些图片存储为 ObjectRef 值的数组。

您可以使用 ARRAY_AGG 函数对 ObjectRef 值的数组进行汇总，包括使用 ORDER BY 子句（如有必要）来保留对象顺序。您可以使用 UNNEST 运算符将 ObjectRef 值数组解析为各个 ObjectRef 值，包括在必要时使用 WITH OFFSET 子句来保留对象顺序。您可以使用对象元数据（例如 URI 路径和对象文件名）将表示对象分块的 ObjectRef 值映射到表示原始对象的 ObjectRef 值。

如需查看有关如何处理 ObjectRef 值数组的示例，请参阅使用 SQL 分析多模态数据教程的使用 ARRAY<ObjectRef> 值处理有序多模态数据部分。

更新 ObjectRef 列

如需更新标准表中的 ObjectRef 列，请选择以下选项之一：

对象表

使用对象表 ref 列中的数据更新 ObjectRef 列：

  1. 在 Google Cloud 控制台中，前往 BigQuery 页面。

    转到 BigQuery

  2. 在查询编辑器中，输入以下语句：

    UPDATE PROJECT_ID.DATASET_ID.TABLE_NAME
SET objectrefcolumn = (SELECT ref FROM DATASET_ID.OBJECT_TABLE WHERE OBJECT_TABLE.uri = TABLE_NAME.uri)
WHERE uri != "";

    替换以下内容：

    • PROJECT_ID：您的项目 ID。 如果您是在当前项目中创建表，则可以跳过此参数。
    • DATASET_ID：您要创建的数据集的 ID。
    • TABLE_NAME：您要重新创建的标准表的名称。
    • OBJECT_TABLE：包含与标准表 ObjectRef 列相同对象数据的对象表的名称。

  3. 点击 运行。

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

SQL 函数

使用 OBJ.FETCH_METADATA 和 OBJ.MAKE_REF 函数的输出更新 ObjectRef 列：

  1. 在 Google Cloud 控制台中，前往 BigQuery 页面。

    转到 BigQuery

  2. 在查询编辑器中，输入以下语句：

    UPDATE PROJECT_ID.DATASET_ID.TABLE_NAME
SET objectrefcolumn = (SELECT OBJ.FETCH_METADATA(OBJ.MAKE_REF(uri, 'CONNECTION_ID')))
WHERE uri != "";

    替换以下内容：

    • PROJECT_ID：您的项目 ID。 如果您是在当前项目中创建表，则可以跳过此参数。
    • DATASET_ID：您要创建的数据集的 ID。
    • TABLE_NAME：您要重新创建的标准表的名称。
    • CONNECTION_ID：一个 STRING 值，其中包含服务可用于访问 Cloud Storage 中对象的云资源连接，格式为 location.connection_id。例如 us-west1.myconnection。您可以通过在 Google Cloud 控制台中查看连接详情，然后复制连接 ID 中显示的完全限定连接 ID 的最后一部分中的值来获取连接 ID。例如 projects/myproject/locations/connection_location/connections/myconnection。

      您必须向连接的服务账号授予 Storage Object User (roles/storage.objectUser) 角色，以便该服务账号能够访问您要使用该连接访问对象的任何 Cloud Storage 存储桶。

      连接必须与您调用函数的查询位于同一项目和区域中。

  3. 点击 运行。

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

后续步骤