Analizza gli arricchimenti degli oggetti in BigQuery

L'esecuzione di query sugli enrichment degli oggetti con SQL in BigQuery ti consente di cercare, filtrare e analizzare i metadati di milioni di oggetti archiviati senza dover ispezionare ogni oggetto singolarmente.

Quando configuri i set di dati di Storage Insights, Cloud Storage esporta automaticamente l'inventario e i metadati degli oggetti, inclusi gli enrichment degli oggetti generati dai modelli di AI, direttamente in BigQuery. Questa pagina spiega come Cloud Storage struttura gli enrichment degli oggetti in BigQuery. Fornisce anche ricette SQL per trovare le etichette, rilevare i volti, identificare i contenuti espliciti, estrarre i riquadri di delimitazione e classificare i documenti PDF.

Ottenere i ruoli richiesti

Per ottenere le autorizzazioni necessarie per eseguire query sugli enrichment degli oggetti in BigQuery, chiedi all'amministratore di concederti i seguenti ruoli IAM nel progetto:

Per saperne di più sulla concessione dei ruoli, consulta Gestisci l'accesso a progetti, cartelle e organizzazioni.

Questi ruoli predefiniti contengono le autorizzazioni necessarie per eseguire query sugli enrichment degli oggetti in BigQuery. Per vedere quali sono esattamente le autorizzazioni richieste, espandi la sezione Autorizzazioni obbligatorie:

Autorizzazioni obbligatorie

Per eseguire query sugli enrichment degli oggetti in BigQuery sono necessarie le seguenti autorizzazioni:

  • bigquery.jobs.create
  • bigquery.tables.getData

Potresti anche ottenere queste autorizzazioni con ruoli personalizzati o altri ruoli predefiniti.

Come gli enrichment degli oggetti si integrano con Storage Insights

Quando configuri un set di dati di Storage Insights per il tuo progetto, Cloud Storage esporta periodicamente l'inventario dei bucket e i contesti degli oggetti in un set di dati BigQuery.

In BigQuery, i dati vengono visualizzati nella object_contexts_catalog_view visualizzazione. Questa visualizzazione fornisce un catalogo unificato degli oggetti archiviati. Ogni riga contiene gli attributi dell'oggetto (project, bucket, name, generation) e un array di record contexts ripetuto:

  • contexts.key: l'identificatore di contesto univoco. Gli enrichment di machine learning generati da Cloud Storage iniziano sempre con il google:annotation: prefisso (ad esempio, google:annotation:label-detector-v1 o google:annotation:face-detector-v1).
  • contexts.value: lo stato di esecuzione del modello per l'enrichment (ad esempio, SUCCESS o FAILURE).
  • contexts.extendedData: una colonna JSON contenente il payload di enrichment del modello, ad esempio etichette rilevate, punti di riferimento del volto, vertici del poligono e percorsi delle categorie.

Per la struttura completa della tabella e le descrizioni delle colonne, consulta lo schema del catalogo dei contesti degli oggetti.

Comprendere i pattern di query: UNNEST rispetto a EXISTS

Un oggetto può avere più contesti e ogni modello può generare più rilevamenti (ad esempio, più etichette o volti rilevati). Quando esegui query sulla visualizzazione object_contexts_catalog_view, in genere utilizzi uno dei seguenti pattern SQL:

  • Riga per rilevamento (UNNEST): utilizza l'operatore UNNEST sull'array di record contexts ripetuto e sull'array JSON interno del modello (utilizzando la funzione JSON_QUERY_ARRAY). L'annidamento appiattisce gli enrichment ripetuti in modo che ogni funzionalità rilevata produca la propria riga. Utilizza questo approccio quando devi ispezionare le singole etichette, estrarre le coordinate del riquadro di delimitazione o calcolare le statistiche tra i rilevamenti.
  • Riga per oggetto (EXISTS): utilizza una sottoquery EXISTS correlata per verificare se un oggetto contiene un'etichetta, un output del modello o una soglia specifici. Questo approccio valuta i contesti ripetuti senza annidarli nella query esterna. Restituisce esattamente una riga per oggetto, indipendentemente dal numero di rilevamenti corrispondenti.

Esempi di query

Gli esempi SQL seguenti mostrano come eseguire query e filtrare gli enrichment degli oggetti per i flussi di lavoro comuni di analisi e gestione dei dati.

In ogni query, sostituisci quanto segue:

  • PROJECT_ID: l'ID del tuo Cloud de Confiance progetto.
  • DATASET_ID: il nome del set di dati BigQuery in cui Storage Insights esporta i dati.

Trovare le immagini contenenti un'etichetta specifica

Per cercare le immagini valutate dal modello label-detector, puoi restituire ogni etichetta corrispondente come riga singola o restituire immagini uniche che contengono l'etichetta.

Restituire le etichette corrispondenti (appiattisce le righe)

La query seguente appiattisce gli array contexts e labelAnnotations per restituire una riga separata per ogni etichetta corrispondente trovata in un'immagine:

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;

Restituire immagini uniche (evita le righe duplicate)

Se un'immagine contiene più etichette corrispondenti, l'annidamento produce più righe per l'oggetto. Per restituire esattamente una riga per immagine, verifica l'esistenza dell'etichetta in una sottoquery 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;

Trovare foto di ritratti e selfie con un soggetto sorridente (riconoscimento facciale)

Puoi combinare più attributi facciali, come espressioni emotive, angoli di orientamento della testa e dimensioni del riquadro di delimitazione, per trovare tipi specifici di foto.

La query seguente utilizza un'espressione di tabella comune (CTE) per calcolare la copertura del riquadro di delimitazione del volto. Filtra i ritratti sorridenti e rivolti in avanti in cui il soggetto occupa almeno il 60% dell'immagine:

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;

Questa query applica i seguenti filtri:

  • joy_probability > 0.5: seleziona i volti valutati come espressione di felicità o sorriso.
  • BETWEEN -10 AND 10: limita gli angoli roll, pan e tilt in modo che il volto sia orientato direttamente verso la fotocamera.
  • face_coverage_percentage >= 0.60: utilizza i vertici del poligono normalizzati (0.0 a 1.0) per filtrare i volti che occupano il 60% o più dell'inquadratura.

Trovare le immagini con flag di sicurezza dei contenuti

Il modello content-safety valuta le immagini per i contenuti per adulti e violenti. Il modello assegna a ogni categoria una valutazione di probabilità, ad esempio VERY_UNLIKELY, UNLIKELY, POSSIBLE, LIKELY, o VERY_LIKELY.

La query seguente restituisce immagini uniche che il modello contrassegna con un'alta affidabilità come contenuti per adulti:

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;

Per filtrare i contenuti violenti o le diverse sensibilità, sostituisci il adultContentLikelihood campo con il violentContentLikelihood campo o controlla la valutazione 'LIKELY' rating.

Contare le immagini contenenti un'etichetta specifica

Per contare il numero totale di immagini uniche contenenti un'etichetta specifica senza doppio conteggio delle immagini che corrispondono a più etichette, utilizza COUNT(*) con una sottoquery 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
  );

Estrarre le coordinate del riquadro di delimitazione per gli oggetti rilevati

Il modello object-detector rileva gli oggetti fisici in un'immagine e fornisce poligoni di delimitazione normalizzati (boundingPoly) che indicano dove l'oggetto appare nell'inquadratura.

La query seguente estrae gli oggetti rilevati denominati Drink e crea colonne di coordinate del riquadro di delimitazione di primo livello (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 sottoquery calcola le coordinate normalizzate minime e massime per ogni poligono e l'espressione .* decomprime i campi delle coordinate x_min, y_min, x_max e y_max direttamente nel set di risultati.

Corrispondere agli oggetti in più modelli

Poiché Cloud Storage associa gli enrichment di ogni modello come contesti indipendenti sullo stesso oggetto, puoi unire più risultati del modello per trovare contenuti molto specifici.

La query seguente annida contexts due volte per trovare le immagini che il modello label-detector classifica come Ristretto e che contengono un oggetto Drink localizzato identificato dal modello 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;

Filtrare i documenti PDF per categoria di classificazione

Il modello pdf-classification assegna categorie gerarchiche (ad esempio /Finance/Accounting & Auditing o /Law & Government/Legal Services) ai documenti PDF archiviati.

La query seguente trova gli oggetti PDF classificati nel ramo /Finance con punteggi di affidabilità superiori a 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;

Per l'elenco completo delle categorie di tassonomia supportate, consulta Categorie di classificazione PDF.

Aggregare i documenti PDF per categoria di classificazione

Per analizzare la distribuzione dei documenti nell'archivio, aggrega e conta gli oggetti PDF per categoria:

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;

Intervenire sui risultati delle query

L'esecuzione di query sui metadati è spesso il primo passo nella gestione automatizzata dei dati, nell'audit di conformità o nell'applicazione delle policy. Dopo aver identificato set specifici di oggetti in BigQuery, puoi inserire questi risultati nei flussi di lavoro a valle, come segue:

  • Gestire i set di dati di machine learning: esporta i risultati delle query in una nuova tabella BigQuery o in un file CSV per creare set di addestramento e set di validazione mirati per i modelli personalizzati.
  • Mettere in quarantena o rimuovere gli oggetti che violano le policy: inserisci i nomi degli oggetti e le posizioni dei bucket identificati dalle query di sicurezza nelle operazioni batch di archiviazione per spostarli in bucket di quarantena con restrizioni o eliminarli in blocco.
  • Eseguire l'audit della distribuzione dei contenuti e ottimizzare i costi: aggrega le classificazioni, le dimensioni e le categorie degli oggetti per comprendere la distribuzione dello spazio di archiviazione nella tua organizzazione e creare regole di gestione del ciclo di vita mirate.

Passaggi successivi