Menganalisis pengayaan objek di BigQuery

Membuat kueri pengayaan objek dengan SQL di BigQuery memungkinkan Anda menelusuri, memfilter, dan menganalisis metadata di jutaan objek tersimpan tanpa memeriksa setiap objek satu per satu.

Saat Anda mengonfigurasi set data Insight Penyimpanan, Cloud Storage akan otomatis mengekspor inventaris dan metadata objek Anda, termasuk pengayaan objek yang dihasilkan oleh model AI, langsung ke BigQuery. Halaman ini menjelaskan cara Cloud Storage menyusun pengayaan objek di BigQuery. Halaman ini juga menyediakan resep SQL untuk menemukan label, mendeteksi wajah, mengidentifikasi konten vulgar, mengekstrak kotak pembatas, dan mengategorikan dokumen PDF.

Mendapatkan peran yang diperlukan

Untuk mendapatkan izin yang diperlukan untuk membuat kueri pengayaan objek di BigQuery, minta administrator Anda untuk memberi peran IAM berikut pada project Anda:

Untuk mengetahui informasi selengkapnya tentang pemberian peran, lihat Mengelola akses ke project, folder, dan organisasi.

Peran bawaan ini berisi izin yang diperlukan untuk membuat kueri pengayaan objek di BigQuery. Untuk melihat izin yang benar-benar diperlukan, perluas bagian Izin yang diperlukan:

Izin yang diperlukan

Izin berikut diperlukan untuk membuat kueri pengayaan objek di BigQuery:

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

Anda mungkin juga bisa mendapatkan izin ini dengan peran khusus atau peran bawaan lainnya.

Cara pengayaan objek terintegrasi dengan Insight Penyimpanan

Saat Anda mengonfigurasi set data Insight Penyimpanan untuk project Anda, Cloud Storage akan secara berkala mengekspor inventaris bucket dan konteks objek Anda ke set data BigQuery.

Di BigQuery, data Anda akan muncul di object_contexts_catalog_view tampilan. Tampilan ini menyediakan katalog terpadu objek tersimpan Anda. Setiap baris berisi atribut objek (project, bucket, name, generation) dan array rekaman contexts berulang:

  • contexts.key: ID konteks unik. Pengayaan machine learning yang dihasilkan oleh Cloud Storage selalu diawali dengan awalan google:annotation: (misalnya, google:annotation:label-detector-v1 atau google:annotation:face-detector-v1).
  • contexts.value: Status eksekusi model untuk pengayaan (seperti SUCCESS atau FAILURE).
  • contexts.extendedData: Kolom JSON yang berisi payload pengayaan model, seperti label yang terdeteksi, landmark wajah, verteks poligon, dan jalur kategori.

Untuk mengetahui struktur tabel dan deskripsi kolom yang lengkap, lihat Skema katalog konteks objek.

Memahami pola kueri: UNNEST versus EXISTS

Objek dapat memiliki beberapa konteks, dan setiap model dapat menghasilkan beberapa deteksi (seperti beberapa label atau wajah yang terdeteksi). Saat membuat kueri tampilan object_contexts_catalog_view, Anda biasanya menggunakan salah satu pola SQL berikut:

  • Baris per deteksi (UNNEST): Gunakan operator UNNEST pada array rekaman contexts berulang dan array JSON dalam model (dengan menggunakan fungsi JSON_QUERY_ARRAY). Unnesting meratakan pengayaan berulang sehingga setiap fitur yang terdeteksi menghasilkan barisnya sendiri. Gunakan pendekatan ini jika Anda perlu memeriksa setiap label, mengekstrak koordinat kotak pembatas, atau menghitung statistik di seluruh deteksi.
  • Baris per objek (EXISTS): Gunakan subkueri EXISTS yang berkorelasi untuk menguji apakah objek berisi label, output model, atau nilai minimum tertentu. Pendekatan ini mengevaluasi konteks berulang tanpa meng-unnest-nya dalam kueri luar. Pendekatan ini menampilkan tepat satu baris per objek, terlepas dari jumlah deteksi yang cocok.

Contoh kueri

Contoh SQL berikut menunjukkan cara membuat kueri dan memfilter pengayaan objek untuk alur kerja analisis dan kurasi data umum.

Di setiap kueri, ganti hal berikut:

  • PROJECT_ID: ID project Cloud de Confiance Anda.
  • DATASET_ID: Nama set data BigQuery tempat Insight Penyimpanan mengekspor data Anda.

Menemukan gambar yang berisi label tertentu

Untuk menelusuri gambar yang dievaluasi oleh model label-detector, Anda dapat menampilkan setiap label yang cocok sebagai baris individual atau menampilkan gambar unik yang berisi label tersebut.

Menampilkan label yang cocok (meratakan baris)

Kueri berikut meratakan array contexts dan labelAnnotations untuk menampilkan baris terpisah untuk setiap label yang cocok yang ditemukan dalam gambar:

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;

Menampilkan gambar unik (menghindari baris duplikat)

Jika gambar berisi beberapa label yang cocok, unnesting akan menghasilkan beberapa baris untuk objek tersebut. Untuk menampilkan tepat satu baris per gambar, periksa keberadaan label dalam subkueri 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;

Menemukan foto potret dan selfie dengan subjek yang tersenyum (deteksi wajah)

Anda dapat menggabungkan beberapa atribut wajah, seperti ekspresi emosi, sudut orientasi kepala, dan dimensi kotak pembatas, untuk menemukan jenis foto tertentu.

Kueri berikut menggunakan ekspresi tabel umum (CTE) untuk menghitung cakupan kotak pembatas wajah. Kueri ini memfilter potret yang tersenyum dan menghadap ke depan, dengan subjek menempati setidaknya 60% gambar:

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;

Kueri ini menerapkan filter berikut:

  • joy_probability > 0.5: Memilih wajah yang dievaluasi sebagai ekspresi kebahagiaan atau tersenyum.
  • BETWEEN -10 AND 10: Membatasi sudut roll, pan, dan tilt sehingga wajah berorientasi langsung ke kamera.
  • face_coverage_percentage >= 0.60: Menggunakan verteks poligon yang dinormalisasi (0.0 hingga 1.0) untuk memfilter wajah yang menempati 60% atau lebih dari frame.

Menemukan gambar yang memiliki flag keamanan konten

Model content-safety mengevaluasi gambar untuk konten dewasa dan konten kekerasan. Model ini menetapkan rating kemungkinan untuk setiap kategori, seperti VERY_UNLIKELY, UNLIKELY, POSSIBLE, LIKELY, atau VERY_LIKELY.

Kueri berikut menampilkan gambar unik yang diberi flag oleh model dengan tingkat keyakinan tinggi sebagai konten dewasa:

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;

Untuk memfilter konten kekerasan atau sensitivitas yang berbeda, ganti kolom adultContentLikelihood dengan kolom violentContentLikelihood atau periksa rating 'LIKELY'.

Menghitung gambar yang berisi label tertentu

Untuk menghitung jumlah total gambar unik yang berisi label tertentu tanpa menghitung dua kali gambar yang cocok dengan beberapa label, gunakan COUNT(*) dengan subkueri 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
  );

Mengekstrak koordinat kotak pembatas untuk objek yang terdeteksi

Model object-detector mendeteksi objek fisik dalam gambar dan menyediakan poligon pembatas yang dinormalisasi (boundingPoly) yang menunjukkan tempat objek muncul dalam frame.

Kueri berikut mengekstrak objek yang terdeteksi bernama Drink dan membuat kolom koordinat kotak pembatas tingkat atas (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;

Subkueri menghitung koordinat yang dinormalisasi minimum dan maksimum untuk setiap poligon, dan ekspresi .* menguraikan kolom koordinat x_min, y_min, x_max, dan y_max langsung ke dalam kumpulan hasil.

Mencocokkan objek di beberapa model

Karena Cloud Storage melampirkan pengayaan dari setiap model sebagai konteks independen pada objek yang sama, Anda dapat menggabungkan beberapa hasil model untuk menemukan konten yang sangat spesifik.

Kueri berikut meng-unnest contexts dua kali untuk menemukan gambar yang diklasifikasikan oleh model label-detector sebagai Ristretto dan yang berisi objek Drink yang dilokalkan yang diidentifikasi oleh model 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;

Memfilter dokumen PDF menurut kategori klasifikasi

Model pdf-classification menetapkan kategori hierarkis (seperti /Finance/Accounting & Auditing atau /Law & Government/Legal Services) ke dokumen PDF yang disimpan.

Kueri berikut menemukan objek PDF yang dikategorikan di bawah cabang /Finance dengan skor keyakinan lebih besar dari 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;

Untuk mengetahui daftar lengkap kategori taksonomi yang didukung, lihat Kategori klasifikasi PDF kategori.

Mengagregasi dokumen PDF menurut kategori klasifikasi

Untuk menganalisis distribusi dokumen di seluruh arsip Anda, gabungkan dan hitung objek PDF Anda menurut kategori:

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;

Mengambil tindakan pada hasil kueri

Membuat kueri metadata Anda sering kali merupakan langkah pertama dalam kurasi data otomatis, audit kepatuhan, atau penerapan kebijakan. Setelah mengidentifikasi kumpulan objek tertentu di BigQuery, Anda dapat memasukkan hasil tersebut ke dalam alur kerja hilir, sebagai berikut:

  • Mengurasi set data machine learning: Ekspor hasil kueri Anda ke tabel BigQuery atau file CSV baru untuk membuat set pelatihan dan set validasi yang ditargetkan untuk model kustom.
  • Mengarantina atau menghapus objek yang melanggar kebijakan: Masukkan nama objek dan lokasi bucket yang diidentifikasi oleh kueri keamanan ke dalam operasi batch Storage untuk memindahkannya ke bucket karantina yang dibatasi atau menghapusnya secara massal.
  • Mengaudit distribusi konten dan mengoptimalkan biaya: Gabungkan klasifikasi, dimensi, dan kategori objek untuk memahami distribusi penyimpanan di seluruh organisasi Anda dan membuat aturan pengelolaan siklus proses yang ditargetkan.

Langkah berikutnya