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 un tableau standard. Par exemple, dans un tableau de produits, vous pouvez stocker les images des 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 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 la section Analyser les données multimodales. Pour obtenir un tutoriel sur l'utilisation des données ObjectRef, consultez Analyser des données multimodales avec SQL. Pour en savoir plus sur l'utilisation des données multimodales dans Python, consultez la page Analyser des données multimodales en Python avec BigQuery DataFrames.

Prérequis

Pour renseigner et mettre à jour les valeurs ObjectRef dans une table standard, la table doit comporter une colonne STRING contenant des informations 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. Si vous souhaitez gérer les valeurs ObjectRef dans une table standard à l'aide d'une table d'objets, vous devez également disposer d'une table d'objets représentant les objets de ce bucket.

Maintenir les valeurs ObjectRef

Vous pouvez utiliser une table d'objets pour renseigner et mettre à jour les valeurs ObjectRef dans une table standard. Si vous figurez sur la liste d'autorisation de l'aperçu, toutes les tables d'objets que vous créez comportent une colonne ref contenant une valeur ObjectRef pour l'objet donné. Vous pouvez utiliser l'URI de l'objet pour joindre la table standard à la table des objets afin de renseigner 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 d'objet à partir de 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 renseigner et mettre à jour les valeurs ObjectRef en extrayant 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 à partir de Cloud Storage.

Créer une colonne ObjectRef

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

Tableau des objets

Créez et remplissez une colonne ObjectRef basée sur les 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 le tableau 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 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 :

    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 le tableau 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. Pour obtenir l'ID de connexion, affichez les détails de la connexion dans la console et copiez la valeur de la dernière section de l'ID de connexion complet affiché dans ID de connexion. Google Cloud 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 que vous 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 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 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 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 qui représentent des segments d'objets sur 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:

Tableau des objets

Mettez à jour une colonne ObjectRef à l'aide 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 :

    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 le tableau 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 le tableau 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. Pour obtenir l'ID de connexion, affichez les détails de la connexion dans la console et copiez la valeur de la dernière section de l'ID de connexion complet affiché dans ID de connexion. Google Cloud 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 que vous 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