Especifica columnas ObjectRef en esquemas de tablas

Las columnas ObjectRef te permiten integrar datos no estructurados en una tabla estándar de BigQuery almacenando metadatos y la información de conexión para los objetos en Cloud Storage. Por ejemplo, en una tabla de productos, puedes almacenar cada imagen de producto en la misma fila que el resto de la información del producto.

En este documento, se describe cómo definir un esquema de tabla con columnas STRUCT que usan el formato ObjectRef. Para obtener más información sobre cómo trabajar con datos multimodales, consulta Analiza datos multimodales y el instructivo Analiza datos multimodales con SQL.

Requisitos previos

Para propagar y actualizar los valores de ObjectRef en una tabla estándar, la tabla debe tener una columna STRING que contenga información del URI para los objetos de Cloud Storage relacionados.

Debes tener un bucket de Cloud Storage que contenga los mismos objetos que se identifican en los datos del URI de la tabla estándar de destino.

Mantén los valores de ObjectRef

Todas las tablas de objetos que crees tendrán una columna ref que contendrá un valor ObjectRef para el objeto determinado. Si tienes una tabla de objetos existente, puedes unirla con tu tabla estándar en la columna de URI del objeto para propagar y actualizar los valores de ObjectRef. Esto es más eficiente porque evita volver a recuperar los metadatos de Cloud Storage para crear un valor ObjectRef nuevo.

Del mismo modo, si ya tienes un conjunto de datos de Storage Insights para los metadatos de objetos, puedes usar la columna ref.uri o selfLink para unir la tabla estándar con el conjunto de datos de Storage Insights para propagar y actualizar los valores de ObjectRef. Los valores ObjectRef creados en los conjuntos de datos de Storage Insights no tienen un autorizador. Para consultar estos objetos, debes tener acceso directo al objeto o agregar un autorizador al ObjectRef para usar el acceso delegado.

Si no tienes una tabla de objetos o un conjunto de datos de Storage Insights existentes, puedes usar la función OBJ.MAKE_REF para propagar y actualizar los valores de ObjectRef recuperando los metadatos de los objetos directamente de Cloud Storage. Este enfoque puede ser menos escalable, ya que requiere la recuperación de metadatos de objetos de Cloud Storage.

Crea una columna ObjectRef

Puedes almacenar valores ObjectRef en columnas STRUCT que usen el formato ObjectRef, que es STRUCT<uri STRING, version STRING, authorizer STRING, details JSON>.

Nota: En los ejemplos de este documento, se usa la declaración CREATE OR REPLACE TABLE para crear y propagar una columna ObjectRef en una sola operación, pero también puedes usar la declaración ALTER TABLE ADD COLUMN para agregar una columna STRUCT a una tabla existente y, luego, usar la declaración UPDATE para propagar esa columna en una operación independiente.

Para crear y propagar una columna ObjectRef en una tabla estándar, elige una de las siguientes opciones:

funciones SQL

Crea y propaga una columna ObjectRef según el resultado de la función OBJ.MAKE_REF:

1. En la consola de Google Cloud , ve a la página BigQuery.

   Ir a BigQuery

2. En el editor de consultas, escribe la siguiente oración:

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

   Reemplaza lo siguiente:

   • PROJECT_ID: Es el ID del proyecto. Puedes omitir este argumento si creas la tabla en tu proyecto actual.
   • DATASET_ID: Es el ID del conjunto de datos que crearás.
   • TABLE_NAME: Es el nombre de la tabla estándar que volverás a crear.
   • CONNECTION_ID: Es un valor STRING que contiene una conexión a recursos de Cloud que el servicio puede usar para acceder a los objetos en Cloud Storage, en el formato location.connection_id. Por ejemplo, us-west1.myconnection. Para obtener el ID de conexión, consulta los detalles de la conexión en la consola de Google Cloud y copia el valor de la última sección del ID de conexión completamente calificado que se muestra en ID de conexión. Por ejemplo: projects/myproject/locations/connection_location/connections/myconnection.

     Debes otorgar el rol de Usuario de objetos de almacenamiento (roles/storage.objectUser) a la cuenta de servicio de la conexión en cualquier bucket de Cloud Storage en el que lo uses para acceder a objetos.

     La conexión debe estar en el mismo proyecto y región que la consulta en la que llamas a la función.

3. Haz clic en Ejecutar.

Para obtener más información sobre cómo ejecutar consultas, visita Ejecuta una consulta interactiva.

Tabla de objetos

Crea y propaga una columna ObjectRef basada en los datos de una columna ref de la tabla de objetos:

1. En la consola de Google Cloud , ve a la página BigQuery.

   Ir a BigQuery

2. En el editor de consultas, escribe la siguiente oración:

   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;

   Reemplaza lo siguiente:

   • PROJECT_ID: Es el ID del proyecto. Puedes omitir este argumento si creas la tabla en tu proyecto actual.
   • DATASET_ID: Es el ID del conjunto de datos que crearás.
   • TABLE_NAME: Es el nombre de la tabla estándar que volverás a crear.
   • OBJECT_TABLE: Es el nombre de la tabla de objetos que contiene los datos de objetos que deseas integrar en la tabla estándar.

3. Haz clic en Ejecutar.

Para obtener más información sobre cómo ejecutar consultas, visita Ejecuta una consulta interactiva.

Crea una columna ARRAY<ObjectRef>

Puedes crear una columna ARRAY<STRUCT<uri STRING, version STRING, authorizer STRING, details JSON>> para que contenga arrays de valores ObjectRef. Por ejemplo, puedes dividir un video en imágenes separadas y, luego, almacenarlas como un array de valores ObjectRef.

Puedes usar la función ARRAY_AGG para agregar arrays de valores ObjectRef, incluido el uso de la cláusula ORDER BY para conservar el orden de los objetos si es necesario. Puedes usar el operador UNNEST para analizar un array de valores ObjectRef en valores ObjectRef individuales, incluido el uso de la cláusula WITH OFFSET para conservar el orden de los objetos si es necesario. Puedes usar los metadatos de los objetos, como la ruta del URI y el nombre de archivo del objeto, para asignar valores ObjectRef que representen fragmentos de objetos a un valor ObjectRef que represente el objeto original.

Para ver un ejemplo de cómo trabajar con arrays de valores ObjectRef, consulta la sección Procesa datos multimodales ordenados con valores ARRAY<ObjectRef> del instructivo Analiza datos multimodales con SQL.

Actualiza una columna ObjectRef

Para actualizar una columna ObjectRef en una tabla estándar, elige una de las siguientes opciones:

Tabla de objetos

Actualiza una columna ObjectRef con datos de una columna ref de tabla de objetos:

1. En la consola de Google Cloud , ve a la página BigQuery.

   Ir a BigQuery

2. En el editor de consultas, escribe la siguiente oración:

   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 != "";

   Reemplaza lo siguiente:

   • PROJECT_ID: Es el ID del proyecto. Puedes omitir este argumento si creas la tabla en tu proyecto actual.
   • DATASET_ID: Es el ID del conjunto de datos que crearás.
   • TABLE_NAME: Es el nombre de la tabla estándar que volverás a crear.
   • OBJECT_TABLE: Es el nombre de la tabla de objetos que contiene los mismos datos de objetos que la columna ObjectRef de la tabla estándar.

3. Haz clic en Ejecutar.

Para obtener más información sobre cómo ejecutar consultas, visita Ejecuta una consulta interactiva.

funciones SQL

Actualiza una columna ObjectRef con el resultado de las funciones OBJ.FETCH_METADATA y OBJ.MAKE_REF:

1. En la consola de Google Cloud , ve a la página BigQuery.

   Ir a BigQuery

2. En el editor de consultas, escribe la siguiente oración:

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

   Reemplaza lo siguiente:

   • PROJECT_ID: Es el ID del proyecto. Puedes omitir este argumento si creas la tabla en tu proyecto actual.
   • DATASET_ID: Es el ID del conjunto de datos que crearás.
   • TABLE_NAME: Es el nombre de la tabla estándar que volverás a crear.
   • CONNECTION_ID: Es un valor STRING que contiene una conexión a recursos de Cloud que el servicio puede usar para acceder a los objetos en Cloud Storage, en el formato location.connection_id. Por ejemplo, us-west1.myconnection. Para obtener el ID de conexión, consulta los detalles de la conexión en la consola de Google Cloud y copia el valor de la última sección del ID de conexión completamente calificado que se muestra en ID de conexión. Por ejemplo: projects/myproject/locations/connection_location/connections/myconnection.

     Debes otorgar el rol de Usuario de objetos de almacenamiento (roles/storage.objectUser) a la cuenta de servicio de la conexión en cualquier bucket de Cloud Storage en el que lo uses para acceder a objetos.

     La conexión debe estar en el mismo proyecto y región que la consulta en la que llamas a la función.

3. Haz clic en Ejecutar.

Para obtener más información sobre cómo ejecutar consultas, visita Ejecuta una consulta interactiva.

¿Qué sigue?

• Analizar datos multimodales.
• Analiza datos multimodales con SQL.