Spécifier des colonnes ObjectRef dans les schémas de table

Les colonnes ObjectRef vous permettent d'intégrer des données non structurées dans une table BigQuery standard en stockant les métadonnées et les informations de connexion des objets dans Cloud Storage. Par exemple, dans une table de produits, vous pouvez stocker chaque image de produit sur la même ligne que les autres informations sur le produit.

Ce document explique comment définir un schéma de table avec des colonnes STRUCT qui utilisent le format ObjectRef. Pour en savoir plus sur l'utilisation des données multimodales, consultez Analyser des données multimodales et le tutoriel Analyser des données multimodales avec SQL.

Prérequis

Pour renseigner et mettre à jour les valeurs ObjectRef dans une table standard, celle-ci doit comporter une colonne STRING contenant des informations d'URI pour les objets Cloud Storage associés.

Vous devez disposer d'un bucket Cloud Storage contenant les mêmes objets que ceux identifiés dans les données d'URI de la table standard cible.

Maintien des valeurs ObjectRef

Toutes les tables d'objets que vous créez comportent une colonne ref qui contient une valeur ObjectRef pour l'objet donné. Si vous disposez d'une table d'objets, vous pouvez la joindre à votre table standard sur la colonne d'URI d'objet pour renseigner et mettre à jour les valeurs ObjectRef. Cette méthode est plus efficace, car elle évite de récupérer à nouveau les métadonnées de Cloud Storage pour créer une valeur ObjectRef.

De même, si vous disposez déjà d'un ensemble de données Storage Insights pour les métadonnées d'objet, vous pouvez utiliser la colonne ref.uri ou selfLink pour joindre la table standard à l'ensemble de données Storage Insights afin de renseigner et de mettre à jour les valeurs ObjectRef. Les valeurs ObjectRef créées dans les ensembles de données Storage Insights n'ont pas d'autorisateur. Pour interroger ces objets, vous devez disposer d'un accès direct à l'objet ou ajouter un autorisateur à ObjectRef pour utiliser l'accès délégué.

Si vous ne disposez pas d'une table d'objets ou d'un ensemble de données Storage Insights, vous pouvez utiliser la fonction OBJ.MAKE_REF pour renseigner et mettre à jour les valeurs ObjectRef en récupérant les métadonnées d'objet directement depuis Cloud Storage. Cette approche peut être moins évolutive, car elle nécessite de récupérer les métadonnées des objets depuis Cloud Storage.

Créer une colonne ObjectRef

Vous pouvez stocker des valeurs ObjectRef dans des colonnes STRUCT qui utilisent le format ObjectRef, qui est STRUCT<uri STRING, version STRING, authorizer STRING, details JSON>.

Pour créer et remplir une colonne ObjectRef dans une table standard, sélectionnez l'une des options suivantes :

fonctions SQL

Créez et renseignez une colonne ObjectRef en fonction du résultat de la fonction OBJ.MAKE_REF :

  1. Dans la console Cloud de Confiance , accédez à la page BigQuery.

    Accéder à BigQuery

  2. Dans l'éditeur de requête, saisissez l'instruction suivante :

    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;

    Remplacez les éléments suivants :

    • PROJECT_ID : ID de votre projet. Vous pouvez ignorer cet argument si vous créez la table dans votre projet actuel.
    • DATASET_ID : ID de l'ensemble de données que vous créez.
    • TABLE_NAME : nom de la table standard que vous recréez.
    • CONNECTION_ID : valeur STRING contenant une connexion aux ressources Cloud que le service peut utiliser pour accéder aux objets dans Cloud Storage, au format location.connection_id. Exemple : us-west1.myconnection. Vous pouvez obtenir l'ID de connexion en affichant les détails de la connexion dans la console Cloud de Confiance et en copiant la valeur de la dernière section de l'ID de connexion complet affiché dans ID de connexion. Exemple : projects/myproject/locations/connection_location/connections/myconnection.

      Vous devez attribuer le rôle Utilisateur d'objets Storage (roles/storage.objectUser) au compte de service de la connexion sur tout bucket Cloud Storage où vous l'utilisez pour accéder aux objets.

      La connexion doit se trouver dans le même projet et la même région que la requête dans laquelle vous appelez la fonction.

  3. Cliquez sur Exécuter.

Pour en savoir plus sur l'exécution des requêtes, consultez Exécuter une requête interactive.

Table d'objets

Créez et remplissez une colonne ObjectRef en fonction des données d'une colonne de table d'objets ref :

  1. Dans la console Cloud de Confiance , accédez à la page BigQuery.

    Accéder à BigQuery

  2. Dans l'éditeur de requête, saisissez l'instruction suivante :

    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;

    Remplacez les éléments suivants :

    • PROJECT_ID : ID de votre projet. Vous pouvez ignorer cet argument si vous créez la table dans votre projet actuel.
    • DATASET_ID : ID de l'ensemble de données que vous créez.
    • TABLE_NAME : nom de la table standard que vous recréez.
    • OBJECT_TABLE : nom de la table d'objets contenant les données d'objet que vous souhaitez intégrer à la table standard.

  3. Cliquez sur Exécuter.

Pour en savoir plus sur l'exécution des requêtes, consultez Exécuter une requête interactive.

Créer une colonne ARRAY<ObjectRef>

Vous pouvez créer une colonne ARRAY<STRUCT<uri STRING, version STRING, authorizer STRING, details JSON>> pour contenir des tableaux de valeurs ObjectRef. Par exemple, vous pouvez diviser une vidéo en images distinctes, puis stocker ces images sous la forme d'un tableau de valeurs ObjectRef.

Vous pouvez utiliser la fonction ARRAY_AGG pour agréger des tableaux de valeurs ObjectRef, y compris en utilisant la clause ORDER BY pour préserver l'ordre des objets si nécessaire. Vous pouvez utiliser l'opérateur UNNEST pour analyser un tableau de valeurs ObjectRef en valeurs ObjectRef individuelles, y compris en utilisant la clause WITH OFFSET pour conserver l'ordre des objets si nécessaire. Vous pouvez utiliser les métadonnées d'objet, telles que le chemin d'URI et le nom de fichier de l'objet, pour mapper les valeurs ObjectRef représentant des fragments d'objet à une valeur ObjectRef représentant l'objet d'origine.

Pour voir un exemple d'utilisation de tableaux de valeurs ObjectRef, consultez la section Traiter des données multimodales ordonnées à l'aide de valeurs ARRAY<ObjectRef> du tutoriel Analyser des données multimodales avec SQL.

Mettre à jour une colonne ObjectRef

Pour mettre à jour une colonne ObjectRef dans une table standard, sélectionnez l'une des options suivantes :

Table d'objets

Mettez à jour une colonne ObjectRef à l'aide des données d'une colonne de table d'objets ref :

  1. Dans la console Cloud de Confiance , accédez à la page BigQuery.

    Accéder à BigQuery

  2. Dans l'éditeur de requête, saisissez l'instruction suivante :

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

    Remplacez les éléments suivants :

    • PROJECT_ID : ID de votre projet. Vous pouvez ignorer cet argument si vous créez la table dans votre projet actuel.
    • DATASET_ID : ID de l'ensemble de données que vous créez.
    • TABLE_NAME : nom de la table standard que vous recréez.
    • OBJECT_TABLE : nom de la table d'objets contenant les mêmes données d'objet que la colonne ObjectRef de la table standard.

  3. Cliquez sur Exécuter.

Pour en savoir plus sur l'exécution des requêtes, consultez Exécuter une requête interactive.

fonctions SQL

Mettez à jour une colonne ObjectRef à l'aide du résultat des fonctions OBJ.FETCH_METADATA et OBJ.MAKE_REF :

  1. Dans la console Cloud de Confiance , accédez à la page BigQuery.

    Accéder à BigQuery

  2. Dans l'éditeur de requête, saisissez l'instruction suivante :

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

    Remplacez les éléments suivants :

    • PROJECT_ID : ID de votre projet. Vous pouvez ignorer cet argument si vous créez la table dans votre projet actuel.
    • DATASET_ID : ID de l'ensemble de données que vous créez.
    • TABLE_NAME : nom de la table standard que vous recréez.
    • CONNECTION_ID : valeur STRING contenant une connexion aux ressources Cloud que le service peut utiliser pour accéder aux objets dans Cloud Storage, au format location.connection_id. Exemple : us-west1.myconnection. Vous pouvez obtenir l'ID de connexion en affichant les détails de la connexion dans la console Cloud de Confiance et en copiant la valeur de la dernière section de l'ID de connexion complet affiché dans ID de connexion. Exemple : projects/myproject/locations/connection_location/connections/myconnection.

      Vous devez attribuer le rôle Utilisateur d'objets Storage (roles/storage.objectUser) au compte de service de la connexion sur tout bucket Cloud Storage où vous l'utilisez pour accéder aux objets.

      La connexion doit se trouver dans le même projet et la même région que la requête dans laquelle vous appelez la fonction.

  3. Cliquez sur Exécuter.

Pour en savoir plus sur l'exécution des requêtes, consultez Exécuter une requête interactive.

Étapes suivantes