Analyser les enrichissements d'objets dans BigQuery

Interroger les enrichissements d'objets avec SQL dans BigQuery vous permet de rechercher, de filtrer et d'analyser les métadonnées de millions d'objets stockés sans inspecter chaque objet individuellement.

Lorsque vous configurez des ensembles de données Storage Insights, Cloud Storage exporte automatiquement l'inventaire et les métadonnées de vos objets, 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. Il fournit également des recettes SQL pour rechercher des étiquettes, 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 :

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

Vous devez disposer des autorisations suivantes 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 l'inventaire de vos buckets et les contextes d'objets dans un ensemble de données BigQuery.

Dans BigQuery, vos données s'affichent dans la vue object_contexts_catalog_view. 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 préfixe google:annotation: (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 étiquettes détectées, les repères faciaux, les sommets de polygones et les chemins de catégorie.

Pour connaître la structure complète de la table et les descriptions des colonnes, consultez le schéma du catalogue de contextes d'objets.

Comprendre les schémas de requête : UNNEST et EXISTS

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

  • Une ligne par détection (UNNEST) : utilisez l'opérateur UNNEST sur le tableau d'enregistrements répétés contexts et le tableau JSON interne du modèle (à l'aide de la fonction JSON_QUERY_ARRAY). La suppression de l'imbrication aplatit les enrichissements répétés afin que chaque caractéristique détectée produise sa propre ligne. Utilisez cette approche lorsque vous devez inspecter des étiquettes individuelles, 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 une étiquette, une sortie de modèle ou un seuil spécifiques. 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 de données.

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

  • PROJECT_ID : ID de votre projet Cloud de Confiance .
  • DATASET_ID : nom de l'ensemble de données BigQuery vers 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 étiquette correspondante sous forme de ligne individuelle ou renvoyer des images uniques contenant l'étiquette.

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 étiquette correspondante trouvée 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 étiquettes correspondantes, l'annulation de l'imbrication produit 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 portraits et des selfies avec un sujet souriant (détection de 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 de face et souriants 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 de la joie ou souriant.
  • BETWEEN -10 AND 10 : limite les angles roll, pan et tilt de sorte 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 au moins 60% de l'image.

Rechercher des images présentant des avertissements 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 les différents niveaux de sensibilité, remplacez le champ adultContentLikelihood par le champ violentContentLikelihood ou vérifiez la note 'LIKELY'.

Compter les images contenant une étiquette spécifique

Pour compter le nombre total d'images uniques contenant une étiquette spécifique sans compter deux fois les images correspondant à plusieurs étiquettes, 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 l'emplacement de l'objet dans l'image.

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.

Mettre en correspondance des objets dans 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 acceptées, consultez Catégories de classification PDF.

Regrouper 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. Après avoir identifié des ensembles d'objets spécifiques dans BigQuery, vous pouvez intégrer ces résultats dans des workflows en aval, comme suit :

  • Organiser des ensembles de données de machine learning : exportez les résultats de vos requêtes 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 ne respectant 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 de manière groupée.
  • Auditer la distribution du contenu et optimiser les coûts : regroupez 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.

Étapes suivantes