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

Ce document explique comment définir un schéma de table standard BigQuery avec des colonnes pouvant stocker des valeurs ObjectRef.

Les valeurs ObjectRef fournissent des métadonnées et des informations de connexion pour les objets dans Cloud Storage. Utilisez des valeurs ObjectRef lorsque vous devez intégrer des données non structurées dans une table standard. Par exemple, dans une table de produits, vous pouvez stocker des images de produits sur la même ligne que le reste des informations sur le produit en ajoutant une colonne contenant des valeurs ObjectRef. Vous pouvez stocker des valeurs ObjectRef dans des colonnes STRUCT qui utilisent le ObjectRef format, à savoir STRUCT<uri STRING, version STRING, authorizer STRING, details JSON>.

Pour en savoir plus sur l'utilisation des données multimodales, consultez Analyser des données multimodales. Pour suivre un tutoriel expliquant comment utiliser les données ObjectRef, consultez Analyser des données multimodales avec SQL.

Prérequis

Pour remplir 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.

Gérer les valeurs ObjectRef

Toutes les tables d'objets que vous créez comportent une colonne ref contenant une valeur ObjectRef pour l'objet donné. Si vous disposez d'une table d'objets existante, vous pouvez la joindre à votre table standard sur la colonne d'URI d'objet pour remplir 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 remplir et de mettre à jour les valeurs ObjectRef. Les valeurs ObjectRef créées dans les ensembles de données Storage Insights ne comportent pas d'autorisation. Pour interroger ces objets, vous devez soit avoir un accès direct à l'objet soit ajouter une autorisation à l'élément 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 existant, vous pouvez utiliser la OBJ.MAKE_REF fonction pour remplir et mettre à jour les valeurs ObjectRef en récupérant directement les métadonnées d'objet à partir de Cloud Storage. Cette approche peut être moins évolutive, car elle nécessite la récupération des métadonnées d'objet à partir de Cloud Storage.

Créer une colonne ObjectRef

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

fonctions SQL

Créez et remplissez une colonne ObjectRef en fonction de la sortie de la fonction OBJ.MAKE_REF :

  1. Dans la Cloud de Confiance console, 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 à une ressource 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 Cloud de Confiance console 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 n'importe quel 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 ref de table d'objets :

  1. Dans la Cloud de Confiance console, 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 ObjectRef valeurs. Par exemple, vous pouvez diviser une vidéo en images distinctes, puis stocker ces images sous forme de tableau de valeurs ObjectRef.

Vous pouvez utiliser la ARRAY_AGG fonction pour agréger des tableaux de ObjectRef valeurs, y compris en utilisant la ORDER BY clause pour conserver l’ordre des objets si nécessaire. Vous pouvez utiliser l' UNNEST opérateur 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 des métadonnées d'objet, telles que le chemin d'URI et le nom de fichier de l'objet, pour mapper les valeurs ObjectRef qui représentent des blocs d'objet à une valeur ObjectRef qui représente l'objet d'origine.

Pour voir un exemple d'utilisation des 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 ref de table d'objets :

  1. Dans la Cloud de Confiance console, 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 de la sortie des fonctions OBJ.FETCH_METADATA et OBJ.MAKE_REF :

  1. Dans la Cloud de Confiance console, 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 à une ressource 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 Cloud de Confiance console 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 n'importe quel 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.

Étape suivante