Google utilise la technologie IA pour traduire le contenu dans votre langue préférée. Les traductions générées par IA peuvent contenir des erreurs.

Analyser les enrichissements d'objets dans BigQuery

L'interrogation des enrichissements d'objets avec SQL dans BigQuery vous permet de rechercher, de filtrer et d'analyser des métadonnées sur des millions d'objets stockés sans avoir à inspecter chaque objet individuellement.

Lorsque vous configurez des ensembles de données Storage Insights, Cloud Storage exporte automatiquement votre inventaire d'objets et vos métadonnées, y compris les enrichissements d'objets générés par des modèles d'IA, directement dans BigQuery. Cette page explique comment Cloud Storage structure les enrichissements d'objets dans BigQuery. Elle fournit également des recettes SQL pour trouver des libellés, détecter des visages, identifier du contenu explicite, extraire des cadres de délimitation et catégoriser des documents PDF.

Obtenir les rôles requis

Pour obtenir les autorisations nécessaires pour interroger les enrichissements d'objets dans BigQuery, demandez à votre administrateur de vous accorder les rôles IAM suivants sur votre projet :

Utilisateur de job BigQuery (roles/bigquery.jobUser)
Lecteur de données BigQuery (roles/bigquery.dataViewer)

Pour en savoir plus sur l'attribution de rôles, consultez Gérer l'accès aux projets, aux dossiers et aux organisations.

Ces rôles prédéfinis contiennent les autorisations requises pour interroger les enrichissements d'objets dans BigQuery. Pour connaître les autorisations exactes requises, développez la section Autorisations requises :

Autorisations requises

Les autorisations suivantes sont requises pour interroger les enrichissements d'objets dans BigQuery :

bigquery.jobs.create
bigquery.tables.getData

Vous pouvez également obtenir ces autorisations avec des rôles personnalisés ou d'autres rôles prédéfinis.

Intégration des enrichissements d'objets à Storage Insights

Lorsque vous configurez un ensemble de données Storage Insights pour votre projet, Cloud Storage exporte régulièrement votre inventaire de buckets et vos objets contextes dans un ensemble de données BigQuery.

Dans BigQuery, vos données s'affichent dans la object_contexts_catalog_view vue. Cette vue fournit un catalogue unifié de vos objets stockés. Chaque ligne contient des attributs d'objet (project, bucket, name, generation) et un tableau d'enregistrements contexts répété :

contexts.key: identifiant unique du contexte. Les enrichissements de machine learning générés par Cloud Storage commencent toujours par le google:annotation: préfixe (par exemple, google:annotation:label-detector-v1 ou google:annotation:face-detector-v1).
contexts.value: état d'exécution du modèle pour l'enrichissement (par exemple, SUCCESS ou FAILURE).
contexts.extendedData: colonne JSON contenant la charge utile d'enrichissement du modèle, telle que les libellés détectés, les points de repère du visage, les sommets de polygones et les chemins d'accès aux catégories.

Pour obtenir la structure complète du tableau et les descriptions des colonnes, consultez le schéma du catalogue des contextes d'objets.

Comprendre les schémas de requêtes : UNNEST par rapport à EXISTS

Un objet peut avoir plusieurs contextes, et chaque modèle peut générer plusieurs détections (par exemple, plusieurs libellés ou visages détectés). Lorsque vous interrogez la vue object_contexts_catalog_view, vous utilisez généralement l'un des schémas SQL suivants :

Ligne par détection (UNNEST) : utilisez l'opérateur UNNEST sur le tableau d'enregistrements contexts répété et le tableau JSON interne du modèle (à l'aide de la fonction JSON_QUERY_ARRAY). L'annulation de l'imbrication aplatit les enrichissements répétés afin que chaque caractéristique détectée génère sa propre ligne. Utilisez cette approche lorsque vous devez inspecter des libellés individuels, extraire des coordonnées de cadre de délimitation ou calculer des statistiques sur les détections.
Ligne par objet (EXISTS) : utilisez une sous-requête EXISTS corrélée pour tester si un objet contient un libellé, une sortie du modèle ou un seuil spécifique. Cette approche évalue les contextes répétés sans les annuler dans la requête externe. Elle renvoie exactement une ligne par objet, quel que soit le nombre de détections correspondantes.

Exemples de requêtes

Les exemples SQL suivants montrent comment interroger et filtrer les enrichissements d'objets pour les workflows courants de curation et d'analyse des données.

Dans chaque requête, remplacez les éléments suivants :

PROJECT_ID : ID de votre Google Cloud projet.
DATASET_ID: nom de l'ensemble de données BigQuery dans lequel Storage Insights exporte vos données.

Rechercher des images contenant un libellé spécifique

Pour rechercher des images évaluées par le modèle label-detector, vous pouvez renvoyer chaque libellé correspondant sous forme de ligne individuelle ou renvoyer des images uniques contenant le libellé.

Renvoyer les libellés correspondants (aplatit les lignes)

La requête suivante aplatit les tableaux contexts et labelAnnotations pour renvoyer une ligne distincte pour chaque libellé correspondant trouvé dans une image :

SELECT
  t.project,
  t.bucket,
  t.name,
  t.generation,
  STRING(label.description) AS label_description,
  FLOAT64(label.score) AS confidence_score
FROM
  `PROJECT_ID.DATASET_ID.object_contexts_catalog_view` AS t,
  UNNEST(t.contexts) AS context,
  UNNEST(JSON_QUERY_ARRAY(context.extendedData.labelAnnotationGroup.labelAnnotations)) AS label
WHERE
  context.key = 'google:annotation:label-detector-v1'
  AND LOWER(STRING(label.description)) = 'car'
  AND FLOAT64(label.score) > 0.75
LIMIT 10;

Renvoyer des images uniques (évite les lignes en double)

Si une image contient plusieurs libellés correspondants, l'annulation de l'imbrication génère plusieurs lignes pour cet objet. Pour renvoyer exactement une ligne par image, vérifiez l'existence du libellé dans une sous-requête EXISTS :

SELECT
  project,
  bucket,
  name,
  generation
FROM
  `PROJECT_ID.DATASET_ID.object_contexts_catalog_view` AS t
WHERE
  EXISTS(
    SELECT 1
    FROM
      UNNEST(t.contexts) AS context,
      UNNEST(JSON_QUERY_ARRAY(context.extendedData.labelAnnotationGroup.labelAnnotations)) AS label
    WHERE
      context.key = 'google:annotation:label-detector-v1'
      AND LOWER(STRING(label.description)) = 'car'
      AND FLOAT64(label.score) > 0.75
  )
LIMIT 10;

Rechercher des photos de portrait et des selfies avec un sujet souriant (détection des visages)

Vous pouvez combiner plusieurs attributs faciaux, tels que les expressions émotionnelles, les angles d'orientation de la tête et les dimensions du cadre de délimitation, pour trouver des types de photos spécifiques.

La requête suivante utilise une expression de table commune (CTE) pour calculer la couverture du cadre de délimitation du visage. Elle filtre les portraits souriants et de face où le sujet occupe au moins 60% de l'image :

WITH
  face_stats AS (
    SELECT
      t.project,
      t.bucket,
      t.name,
      t.generation,
      -- Extract the joy likelihood score (0.0 to 1.0)
      FLOAT64(face.joyProbability) AS joy_probability,
      -- Extract head pose orientation angles (degrees)
      FLOAT64(face.rollAngle) AS roll_angle,
      FLOAT64(face.panAngle) AS pan_angle,
      FLOAT64(face.tiltAngle) AS tilt_angle,
      -- Calculate the bounding box area relative to total image area (0.0 to 1.0)
      (
        SELECT AS STRUCT
          (MAX(FLOAT64(v.x)) - MIN(FLOAT64(v.x)))
          * (MAX(FLOAT64(v.y)) - MIN(FLOAT64(v.y))) AS coverage
        FROM
          UNNEST(JSON_QUERY_ARRAY(face.boundingPoly.normalizedVertices)) AS v
      ).coverage AS face_coverage_percentage
    FROM
      `PROJECT_ID.DATASET_ID.object_contexts_catalog_view` AS t,
      UNNEST(t.contexts) AS context,
      UNNEST(
        JSON_QUERY_ARRAY(
          context.extendedData.faceAnnotationGroup.faceAnnotations)) AS face
    WHERE
      context.key = 'google:annotation:face-detector-v1'
  )
SELECT
  project,
  bucket,
  name,
  generation,
  joy_probability,
  face_coverage_percentage
FROM
  face_stats
WHERE
  joy_probability > 0.5
  AND roll_angle BETWEEN -10 AND 10
  AND pan_angle BETWEEN -10 AND 10
  AND tilt_angle BETWEEN -10 AND 10
  AND face_coverage_percentage >= 0.60
LIMIT 10;

Cette requête applique les filtres suivants :

joy_probability > 0.5 : sélectionne les visages évalués comme exprimant le bonheur ou le sourire.
BETWEEN -10 AND 10: limite les angles roll, pan et tilt afin que le visage soit orienté directement vers l'appareil photo.
face_coverage_percentage >= 0.60 : utilise des sommets de polygones normalisés (0.0 à 1.0) pour filtrer les visages qui occupent 60% ou plus du cadre.

Rechercher des images comportant des indicateurs de sécurité du contenu

Le modèle content-safety évalue les images pour détecter les contenus réservés aux adultes et les contenus violents. Le modèle attribue à chaque catégorie une évaluation de probabilité, telle que VERY_UNLIKELY, UNLIKELY, POSSIBLE, LIKELY, ou VERY_LIKELY.

La requête suivante renvoie des images uniques que le modèle signale avec un niveau de confiance élevé comme étant du contenu réservé aux adultes :

SELECT
  project,
  bucket,
  name,
  generation
FROM
  `PROJECT_ID.DATASET_ID.object_contexts_catalog_view` AS t
WHERE
  EXISTS(
    SELECT 1
    FROM
      UNNEST(t.contexts) AS context
    WHERE
      context.key = 'google:annotation:content-safety-v1'
      AND STRING(context.extendedData.contentSafetyAnnotation.adultContentLikelihood) = 'VERY_LIKELY'
  )
LIMIT 10;

Pour filtrer les contenus violents ou différentes sensibilités, remplacez le adultContentLikelihood champ par le violentContentLikelihood champ ou recherchez l'évaluation 'LIKELY'.

Compter les images contenant un libellé spécifique

Pour compter le nombre total d'images uniques contenant un libellé spécifique sans double comptage des images qui correspondent à plusieurs libellés, utilisez COUNT(*) avec une sous-requête EXISTS :

SELECT
  COUNT(*) AS image_count
FROM
  `PROJECT_ID.DATASET_ID.object_contexts_catalog_view` AS t
WHERE
  EXISTS(
    SELECT 1
    FROM
      UNNEST(t.contexts) AS context,
      UNNEST(
        JSON_QUERY_ARRAY(
          context.extendedData.labelAnnotationGroup.labelAnnotations)) AS label
    WHERE
      context.key = 'google:annotation:label-detector-v1'
      AND LOWER(STRING(label.description)) = 'ristretto'
      AND FLOAT64(label.score) > 0.5
  );

Extraire les coordonnées du cadre de délimitation pour les objets détectés

Le modèle object-detector détecte les objets physiques dans une image et fournit des polygones de délimitation normalisés (boundingPoly) indiquant où l'objet apparaît dans le cadre.

La requête suivante extrait les objets détectés nommés Drink et crée des colonnes de coordonnées de cadre de délimitation de premier niveau (x_min, y_min, x_max, y_max) :

SELECT
  t.project,
  t.bucket,
  t.name,
  t.generation,
  STRING(object.name) AS object_name,
  FLOAT64(object.score) AS confidence_score,
  -- Calculate bounding box boundaries from normalized vertices
  (
    SELECT AS STRUCT
      MIN(FLOAT64(v.x)) AS x_min,
      MIN(FLOAT64(v.y)) AS y_min,
      MAX(FLOAT64(v.x)) AS x_max,
      MAX(FLOAT64(v.y)) AS y_max
    FROM
      UNNEST(JSON_QUERY_ARRAY(object.boundingPoly.normalizedVertices)) AS v
  ).*
FROM
  `PROJECT_ID.DATASET_ID.object_contexts_catalog_view` AS t,
  UNNEST(t.contexts) AS context,
  UNNEST(
    JSON_QUERY_ARRAY(
      context.extendedData.objectAnnotationGroup.objectAnnotations)) AS object
WHERE
  context.key = 'google:annotation:object-detector-v1'
  AND LOWER(STRING(object.name)) = 'drink'
  AND FLOAT64(object.score) > 0.5
LIMIT 10;

La sous-requête calcule les coordonnées normalisées minimales et maximales pour chaque polygone, et l'expression .* décompresse les champs de coordonnées x_min, y_min, x_max et y_max directement dans l'ensemble de résultats.

Faire correspondre des objets sur plusieurs modèles

Étant donné que Cloud Storage associe les enrichissements de chaque modèle en tant que contextes indépendants sur le même objet, vous pouvez joindre plusieurs résultats de modèle pour trouver du contenu très spécifique.

La requête suivante annule l'imbrication de contexts deux fois pour trouver les images que le modèle label-detector classe comme Ristretto et qui contiennent un objet Drink localisé identifié par le modèle object-detector :

SELECT
  t.project,
  t.bucket,
  t.name,
  t.generation,
  STRING(object.name) AS object_name,
  FLOAT64(object.score) AS object_score,
  STRING(label.description) AS label_description,
  FLOAT64(label.score) AS label_score,
  (
    SELECT AS STRUCT
      MIN(FLOAT64(v.x)) AS x_min,
      MIN(FLOAT64(v.y)) AS y_min,
      MAX(FLOAT64(v.x)) AS x_max,
      MAX(FLOAT64(v.y)) AS y_max
    FROM
      UNNEST(JSON_QUERY_ARRAY(object.boundingPoly.normalizedVertices)) AS v
  ).*
FROM
  `PROJECT_ID.DATASET_ID.object_contexts_catalog_view` AS t,
  -- Match the label detection context
  UNNEST(t.contexts) AS label_ctx,
  UNNEST(
    JSON_QUERY_ARRAY(
      label_ctx.extendedData.labelAnnotationGroup.labelAnnotations)) AS label,
  -- Match the object detection context on the same object
  UNNEST(t.contexts) AS object_ctx,
  UNNEST(
    JSON_QUERY_ARRAY(
      object_ctx.extendedData.objectAnnotationGroup.objectAnnotations)) AS object
WHERE
  label_ctx.key = 'google:annotation:label-detector-v1'
  AND LOWER(STRING(label.description)) = 'ristretto'
  AND FLOAT64(label.score) > 0.5
  AND object_ctx.key = 'google:annotation:object-detector-v1'
  AND LOWER(STRING(object.name)) = 'drink'
  AND FLOAT64(object.score) > 0.5
LIMIT 10;

Filtrer les documents PDF par catégorie de classification

Le modèle pdf-classification attribue des catégories hiérarchiques (telles que /Finance/Accounting & Auditing ou /Law & Government/Legal Services) aux documents PDF stockés.

La requête suivante recherche les objets PDF classés dans la branche /Finance avec des scores de confiance supérieurs à 0,5 :

SELECT
  t.project,
  t.bucket,
  t.name,
  t.generation,
  STRING(category.label) AS category_name,
  FLOAT64(category.score) AS confidence_score
FROM
  `PROJECT_ID.DATASET_ID.object_contexts_catalog_view` AS t,
  UNNEST(t.contexts) AS context,
  UNNEST(
    JSON_QUERY_ARRAY(
      context.extendedData.pdfClassificationCategoryList.categories)) AS category
WHERE
  context.key = 'google:annotation:pdf-classification-v1'
  AND STARTS_WITH(STRING(category.label), '/Finance')
  AND FLOAT64(category.score) > 0.5
LIMIT 10;

Pour obtenir la liste complète des catégories de taxonomie compatibles, consultez la section Catégories de classification des PDF categories.

Agréger les documents PDF par catégorie de classification

Pour analyser la distribution des documents dans votre archive, agrégez et comptez vos objets PDF par catégorie :

SELECT
  STRING(category.label) AS category_name,
  COUNT(*) AS document_count
FROM
  `PROJECT_ID.DATASET_ID.object_contexts_catalog_view` AS t,
  UNNEST(t.contexts) AS context,
  UNNEST(
    JSON_QUERY_ARRAY(
      context.extendedData.pdfClassificationCategoryList.categories)) AS category
WHERE
  context.key = 'google:annotation:pdf-classification-v1'
GROUP BY
  category_name
ORDER BY
  document_count DESC
LIMIT 10;

Agir sur les résultats de la requête

L'interrogation de vos métadonnées est souvent la première étape de la curation automatisée des données, de l'audit de conformité ou de l'application des règles. Une fois que vous avez identifié des ensembles d'objets spécifiques dans BigQuery, vous pouvez transmettre ces résultats à des workflows en aval, comme suit :

Curer des ensembles de données de machine learning : exportez les résultats de votre requête vers une nouvelle table BigQuery ou un fichier CSV pour créer des ensembles d'entraînement et de validation ciblés pour les modèles personnalisés.
Mettre en quarantaine ou supprimer les objets qui ne respectent pas les règles : transmettez les noms d'objets et les emplacements de buckets identifiés par les requêtes de sécurité aux opérations de stockage par lot pour les déplacer vers des buckets de quarantaine restreints ou les supprimer en bloc.
Auditer la distribution du contenu et optimiser les coûts : agrégez les classifications, les dimensions et les catégories d'objets pour comprendre la distribution du stockage dans votre organisation et créer des règles de gestion du cycle de vie ciblées.