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 les valeurs ObjectRef lorsque vous devez intégrer des données non structurées dans un tableau 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 produit en ajoutant une colonne contenant des valeurs ObjectRef. Vous pouvez stocker les valeurs ObjectRef dans des colonnes STRUCT qui utilisent le format ObjectRef, qui est 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 qui explique comment utiliser les données ObjectRef, consultez Analyser des données multimodales avec SQL. Pour savoir comment utiliser des données multimodales dans Python, consultez Analyser des données multimodales dans Python avec BigQuery DataFrames.

Prérequis

Pour remplir et mettre à jour les valeurs ObjectRef dans une table standard, celle-ci doit comporter une colonne STRING contenant des informations sur les URI des 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 URI de la table standard cible. Si vous souhaitez conserver les valeurs ObjectRef dans un tableau standard à l'aide d'une table d'objets, vous devez également disposer d'une table d'objets qui représente les objets de ce bucket.

Maintien des valeurs ObjectRef

Vous pouvez utiliser une table d'objets pour insérer et mettre à jour des valeurs ObjectRef dans une table standard. Si vous êtes sur la liste d'autorisation pour l'aperçu, toutes les tables d'objets que vous créez comportent une colonne ref contenant une valeur ObjectRef pour l'objet concerné. Vous pouvez utiliser l'URI de l'objet pour joindre la table standard à la table d'objets afin de remplir et de mettre à jour les valeurs ObjectRef. Nous recommandons cette approche pour l'évolutivité, car elle évite d'avoir à récupérer les métadonnées des objets depuis Cloud Storage.

Si vous ne souhaitez pas créer de table d'objets, vous pouvez utiliser les fonctions OBJ.FETCH_METADATA et OBJ.MAKE_REF pour remplir et mettre à jour les valeurs ObjectRef en récupérant les métadonnées des objets directement depuis Cloud Storage. Cette approche peut être moins évolutive, car elle nécessite la récupération des métadonnées des objets depuis Cloud Storage.

Créer une colonne ObjectRef

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

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 console Google Cloud , 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.

fonctions SQL

Créez et renseignez une colonne ObjectRef en fonction des résultats des fonctions OBJ.FETCH_METADATA et OBJ.MAKE_REF :

  1. Dans la console Google Cloud , 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.FETCH_METADATA(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 console Google Cloud et en copiant la valeur de la dernière section de l'ID de connexion complet affiché dans ID de connexion. Par 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 tous les 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.

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 forme de 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 conserver 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 préserver l'ordre des objets si nécessaire. Vous pouvez utiliser les métadonnées d'objet, comme le chemin d'URI et le nom de fichier de l'objet, pour mapper les valeurs ObjectRef qui représentent les blocs d'objet à une valeur ObjectRef qui représente 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 ref de table d'objet :

  1. Dans la console Google Cloud , 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 console Google Cloud , 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.FETCH_METADATA(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 console Google Cloud et en copiant la valeur de la dernière section de l'ID de connexion complet affiché dans ID de connexion. Par 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 tous les 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