Analizza gli arricchimenti degli oggetti in BigQuery

L'esecuzione di query sugli arricchimenti degli oggetti con SQL in BigQuery ti consente di cercare, filtrare e analizzare i metadati di milioni di oggetti archiviati senza 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 arricchimenti degli oggetti generati dai modelli di AI, direttamente in BigQuery. Questa pagina spiega in che modo Cloud Storage struttura gli arricchimenti degli oggetti in BigQuery. Fornisce inoltre formule SQL per trovare etichette, rilevare volti, identificare contenuti espliciti, estrarre riquadri di delimitazione e categorizzare documenti PDF.

Ottieni i ruoli richiesti

Per ottenere le autorizzazioni necessarie per eseguire query sugli arricchimenti 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 arricchimenti degli oggetti in BigQuery. Per vedere quali sono esattamente le autorizzazioni richieste, espandi la sezione Autorizzazioni obbligatorie:

Autorizzazioni obbligatorie

Per eseguire query sugli arricchimenti 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.

Integrazione degli arricchimenti degli oggetti con Storage Insights

Quando configuri un set di dati 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 vista object_contexts_catalog_view. 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 univoco del contesto. Gli arricchimenti di machine learning generati da Cloud Storage iniziano sempre con il prefisso google:annotation: (ad esempio google:annotation:label-detector-v1 o google:annotation:face-detector-v1).
  • contexts.value: lo stato di esecuzione del modello per l'arricchimento (ad esempio SUCCESS o FAILURE).
  • contexts.extendedData: una colonna JSON contenente il payload di arricchimento del modello, ad esempio etichette rilevate, punti di riferimento del volto, vertici dei poligoni e percorsi delle categorie.

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

Informazioni sui pattern di query: UNNEST ed 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 vista 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). La funzionalità di unnesting appiattisce gli arricchimenti ripetuti in modo che ogni caratteristica rilevata produca la propria riga. Utilizza questo approccio quando devi esaminare singole etichette, estrarre le coordinate riquadro di delimitazione o calcolare le statistiche tra i rilevamenti.
  • Riga per oggetto (EXISTS): utilizza una subquery 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 unnesting nella query esterna. Restituisce esattamente una riga per oggetto, indipendentemente dal numero di rilevamenti corrispondenti.

Esempi di query

Gli esempi SQL riportati di seguito mostrano come eseguire query e filtrare gli arricchimenti degli oggetti per i flussi di lavoro comuni di selezione e analisi dei dati.

In ogni query, sostituisci quanto segue:

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

Trova immagini contenenti un'etichetta specifica

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

Restituisce le etichette corrispondenti (appiattisce le righe)

La seguente query esegue il flattening degli 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;

Restituisci immagini uniche (evita righe duplicate)

Se un'immagine contiene più etichette corrispondenti, l'annullamento del nidificazione produce più righe per quell'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;

Trova 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 seguente query 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 10rollpantilt
  • face_coverage_percentage >= 0.60: utilizza i vertici del poligono normalizzati (da 0.0 a 1.0) per filtrare i volti che occupano almeno il 60% del frame.

Trova immagini con flag di sicurezza dei contenuti

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

La seguente query restituisce immagini uniche che il modello contrassegna con un'alta confidenza 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 con sensibilità diverse, sostituisci il campo adultContentLikelihood con il campo violentContentLikelihood o verifica la classificazione 'LIKELY'.

Conta le immagini contenenti un'etichetta specifica

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

Estrai 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 appare l'oggetto nel frame.

La seguente query 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 .* scompatta i campi delle coordinate x_min, y_min, x_max e y_max direttamente nel set di risultati.

Abbina gli oggetti in più modelli

Poiché Cloud Storage associa gli arricchimenti di ogni modello come contesti indipendenti sullo stesso oggetto, puoi unire i risultati di più modelli per trovare contenuti altamente specifici.

La seguente query esegue l'un-nesting di 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;

Filtra i documenti PDF per categoria di classificazione

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

La seguente query 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 dei PDF.

Aggrega i documenti PDF per categoria di classificazione

Per analizzare la distribuzione dei documenti nel tuo 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;

Passa all'azione sui risultati della query

L'esecuzione di query sui metadati è spesso il primo passaggio della selezione automatizzata dei dati, del controllo della conformità o dell'applicazione delle policy. Dopo aver identificato insiemi specifici di oggetti in BigQuery, puoi inserire i risultati nei workflow downstream, come segue:

  • Crea 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 convalida mirati per i modelli personalizzati.
  • Metti in quarantena o rimuovi gli oggetti che violano le policy: inserisci i nomi degli oggetti e le posizioni dei bucket identificati dalle query di sicurezza in Operazioni batch di archiviazione per spostarli in bucket di quarantena limitati o eliminarli collettivamente.
  • Controlla la distribuzione dei contenuti e ottimizza 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