Objektkontext-Anreicherungen in BigQuery analysieren

Wenn Sie Objektkontext-Anreicherungen mit SQL in BigQuery abfragen, können Sie Metadaten in Millionen von gespeicherten Objekten suchen, filtern und analysieren, ohne jedes Objekt einzeln prüfen zu müssen.

Wenn Sie Storage Insights-Datasets konfigurieren, exportiert Cloud Storage automatisch Ihr Objektinventar und Ihre Metadaten, einschließlich der von KI-Modellen generierten Objektkontext-Anreicherungen, direkt nach BigQuery. Auf dieser Seite wird erläutert, wie Cloud Storage Objektkontext-Anreicherungen in BigQuery strukturiert. Außerdem finden Sie hier SQL-Rezepte zum Suchen von Labels, Erkennen von Gesichtern, Identifizieren von anstößigen Inhalten, Extrahieren von Begrenzungsrahmen und Kategorisieren von PDF-Dokumenten.

Erforderliche Rollen abrufen

Bitten Sie Ihren Administrator, Ihnen die folgenden IAM-Rollen für Ihr Projekt zuzuweisen, um die Berechtigungen zu erhalten, die Sie zum Abfragen von Objektkontext-Anreicherungen in BigQuery benötigen:

Weitere Informationen zum Zuweisen von Rollen finden Sie unter Zugriff auf Projekte, Ordner und Organisationen verwalten.

Diese vordefinierten Rollen enthalten die Berechtigungen, die zum Abfragen von Objektkontext-Anreicherungen in BigQuery erforderlich sind. Maximieren Sie den Abschnitt Erforderliche Berechtigungen , um die notwendigen Berechtigungen anzuzeigen, die erforderlich sind:

Erforderliche Berechtigungen

Die folgenden Berechtigungen sind erforderlich, um Objektkontext-Anreicherungen in BigQuery abzufragen:

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

Sie können diese Berechtigungen gegebenenfalls auch mit benutzerdefinierten Rollen oder anderen vordefinierten Rollen erhalten.

Integration von Objektkontext-Anreicherungen in Storage Insights

Wenn Sie ein Storage Insights-Dataset für Ihr Projekt konfigurieren, exportiert Cloud Storage regelmäßig Ihr Bucket-Inventar und Ihre Objekt kontexte in ein BigQuery-Dataset.

In BigQuery werden Ihre Daten in der object_contexts_catalog_view Ansicht angezeigt. Diese Ansicht bietet einen einheitlichen Katalog Ihrer gespeicherten Objekte. Jede Zeile enthält Objektattribute (project, bucket, name, generation) und ein wiederholtes contexts-Datensatzarray:

  • contexts.key: Die eindeutige Kontext-ID. Von Cloud Storage generierte Machine-Learning Anreicherungen beginnen immer mit dem google:annotation: Präfix (z. B. google:annotation:label-detector-v1 oder google:annotation:face-detector-v1).
  • contexts.value: Der Status der Modellausführung für die Anreicherung (z. B. SUCCESS oder FAILURE).
  • contexts.extendedData: Eine JSON-Spalte mit der Anreicherungspayload des Modells, z. B. erkannte Labels, Gesichtsmerkmale, Polygonecken und Kategoriepfade.

Die vollständige Tabellenstruktur und Spaltenbeschreibungen finden Sie im Schema des Objektkontextkatalogs.

Abfragemuster: UNNEST im Vergleich zu EXISTS

Ein Objekt kann mehrere Kontexte haben und jedes Modell kann mehrere Erkennungen ausgeben (z. B. mehrere Labels oder erkannte Gesichter). Wenn Sie die Ansicht object_contexts_catalog_view abfragen, verwenden Sie in der Regel eines der folgenden SQL-Muster:

  • Zeile pro Erkennung (UNNEST): Verwenden Sie den Operator UNNEST für das wiederholte contexts-Datensatzarray und das innere JSON-Array des Modells (mit der Funktion JSON_QUERY_ARRAY). Durch das Entnesten werden wiederholte Anreicherungen vereinfacht, sodass jedes erkannte Feature eine eigene Zeile erzeugt. Verwenden Sie diesen Ansatz, wenn Sie einzelne Labels prüfen, Begrenzungsrahmenkoordinaten extrahieren oder Statistiken für Erkennungen berechnen müssen.
  • Zeile pro Objekt (EXISTS): Verwenden Sie eine korrelierte EXISTS-Unterabfrage, um zu prüfen, ob ein Objekt ein bestimmtes Label, eine bestimmte Modellausgabe oder einen bestimmten Schwellenwert enthält. Bei diesem Ansatz werden wiederholte Kontexte ausgewertet, ohne sie in der äußeren Abfrage zu entnesten. Unabhängig davon, wie viele Erkennungen übereinstimmen, wird genau eine Zeile pro Objekt zurückgegeben.

Beispielabfragen

Die folgenden SQL-Beispiele zeigen, wie Sie Objektkontext-Anreicherungen für gängige Workflows zur Datenaufbereitung und -analyse abfragen und filtern.

Ersetzen Sie in jeder Abfrage Folgendes:

  • PROJECT_ID: Die ID Ihres Google Cloud Projekts.
  • DATASET_ID: Der Name des BigQuery-Datasets, in das Storage Insights Ihre Daten exportiert.

Bilder mit einem bestimmten Label suchen

Wenn Sie nach Bildern suchen möchten, die vom Modell label-detector ausgewertet wurden, können Sie entweder jedes übereinstimmende Label als einzelne Zeile zurückgeben oder eindeutige Bilder zurückgeben, die das Label enthalten.

Übereinstimmende Labels zurückgeben (Zeilen vereinfachen)

Die folgende Abfrage vereinfacht die Arrays contexts und labelAnnotations, um für jedes übereinstimmende Label, das in einem Bild gefunden wurde, eine separate Zeile zurückzugeben:

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;

Eindeutige Bilder zurückgeben (doppelte Zeilen vermeiden)

Wenn ein Bild mehrere übereinstimmende Labels enthält, werden durch das Entnesten mehrere Zeilen für dieses Objekt erstellt. Wenn Sie genau eine Zeile pro Bild zurückgeben möchten, prüfen Sie in einer EXISTS-Unterabfrage, ob das Label vorhanden ist:

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;

Porträts und Selfies mit einer lächelnden Person suchen (Gesichtserkennung)

Sie können mehrere Gesichtsattribute wie Gesichtsausdrücke, Winkel der Kopforientierung und Abmessungen des Begrenzungsrahmens kombinieren, um bestimmte Arten von Fotos zu finden.

Die folgende Abfrage verwendet einen allgemeinen Tabellenausdruck (Common Table Expression, CTE), um die Abdeckung des Begrenzungsrahmens für das Gesicht zu berechnen. Sie filtert nach lächelnden Porträts mit Blick nach vorn, bei denen die Person mindestens 60% des Bildes einnimmt:

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;

Diese Abfrage wendet die folgenden Filter an:

  • joy_probability > 0.5: Wählt Gesichter aus, die als glücklich oder lächelnd bewertet wurden.
  • BETWEEN -10 AND 10: Beschränkt die Winkel für roll, pan und tilt, sodass das Gesicht direkt zur Kamera ausgerichtet ist.
  • face_coverage_percentage >= 0.60: Filtert mit normalisierten Polygonecken (0.0 bis 1.0) nach Gesichtern, die mindestens 60% des Rahmens einnehmen.

Bilder mit Flags zur Inhaltschutzprüfung suchen

Das Modell content-safety bewertet Bilder auf Inhalte für Erwachsene und gewaltverherrlichende Inhalte. Das Modell weist jeder Kategorie eine Wahrscheinlichkeitsbewertung zu, z. B. VERY_UNLIKELY, UNLIKELY, POSSIBLE, LIKELY oder VERY_LIKELY.

Die folgende Abfrage gibt eindeutige Bilder zurück, die vom Modell mit hoher Wahrscheinlichkeit als Inhalte für Erwachsene gekennzeichnet wurden:

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;

Wenn Sie nach gewaltverherrlichenden Inhalten oder unterschiedlichen Sensitivitäten filtern möchten, ersetzen Sie das adultContentLikelihood Feld durch das violentContentLikelihood Feld, oder prüfen Sie auf die 'LIKELY' Bewertung.

Bilder mit einem bestimmten Label zählen

Wenn Sie die Gesamtzahl der eindeutigen Bilder mit einem bestimmten Label zählen möchten, ohne Bilder doppelt zu zählen, die mehreren Labels entsprechen, verwenden Sie COUNT(*) mit einer EXISTS-Unterabfrage:

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
  );

Begrenzungsrahmenkoordinaten für erkannte Objekte extrahieren

Das Modell object-detector erkennt physische Objekte in einem Bild und stellt normalisierte Begrenzungspolygone (boundingPoly) bereit, die angeben, wo sich das Objekt im Rahmen befindet.

Die folgende Abfrage extrahiert erkannte Objekte mit dem Namen Drink und erstellt Begrenzungsrahmenkoordinatenspalten der obersten Ebene (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;

Die Unterabfrage berechnet die minimalen und maximalen normalisierten Koordinaten für jedes Polygon und der Ausdruck .* entpackt die Koordinatenfelder x_min, y_min, x_max und y_max direkt in das Ergebnisset.

Objekte in mehreren Modellen abgleichen

Da Cloud Storage Anreicherungen aus jedem Modell als unabhängige Kontexte für dasselbe Objekt anhängt, können Sie mehrere Modellergebnisse verknüpfen, um sehr spezifische Inhalte zu finden.

Die folgende Abfrage entnestet contexts zweimal, um Bilder zu finden, die vom Modell label-detector als Ristretto klassifiziert wurden und ein lokalisiertes Drink-Objekt enthalten, das vom Modell object-detector identifiziert wurde:

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;

PDF-Dokumente nach Klassifizierungskategorie filtern

Das pdf-classification Modell weist gespeicherten PDF-Dokumenten hierarchische Kategorien zu, z. B. /Finance/Accounting & Auditing oder /Law & Government/Legal Services.

Die folgende Abfrage sucht nach PDF-Objekten, die unter dem Zweig /Finance kategorisiert sind und Konfidenzwerte über 0,5 haben:

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;

Die vollständige Liste der unterstützten Taxonomiekategorien finden Sie unter Kategorien für die PDF-Klassifizierung.

PDF-Dokumente nach Klassifizierungskategorie aggregieren

Wenn Sie die Verteilung von Dokumenten in Ihrem Archiv analysieren möchten, aggregieren und zählen Sie Ihre PDF-Objekte nach Kategorie:

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;

Maßnahmen in Bezug auf Abfrageergebnisse ergreifen

Das Abfragen von Metadaten ist oft der erste Schritt bei der automatisierten Datenaufbereitung, Compliance-Prüfung oder Richtliniendurchsetzung. Nachdem Sie bestimmte Objektgruppen in BigQuery identifiziert haben, können Sie diese Ergebnisse in nachgelagerte Workflows einfließen lassen:

  • Machine-Learning-Datasets aufbereiten: Exportieren Sie Ihre Abfrageergebnisse in eine neue BigQuery-Tabelle oder CSV-Datei, um gezielte Trainings- und Validierungssätze für benutzerdefinierte Modelle zu erstellen.
  • Objekte, die gegen Richtlinien verstoßen, unter Quarantäne stellen oder entfernen: Übergeben Sie die Objektnamen und Bucket-Speicherorte, die von Sicherheitsabfragen identifiziert wurden, an Storage-Batchvorgänge um sie in eingeschränkte Quarantäne-Buckets zu verschieben oder in großen Mengen zu löschen.
  • Inhaltsverteilung prüfen und Kosten optimieren: Aggregieren Sie Objekt klassifizierungen, -abmessungen und -kategorien, um die Speicher verteilung in Ihrer Organisation zu verstehen und gezielte Regeln für die Lebenszyklus verwaltung zu erstellen.

Nächste Schritte