Google은 AI 기술을 사용하여 콘텐츠를 사용자의 기본 언어로 번역합니다. AI 번역에는 오류가 있을 수 있습니다.

BigQuery에서 객체 보강 분석

BigQuery에서 SQL로 객체 보강을 쿼리하면 각 객체를 개별적으로 검사하지 않고도 수백만 개의 저장된 객체에서 메타데이터를 검색, 필터링, 분석할 수 있습니다.

Storage Insights 데이터 세트를 구성하면 Cloud Storage 는 AI 모델에서 생성된 객체 보강을 포함한 객체 인벤토리 및 메타데이터를 BigQuery로 직접 내보냅니다. 이 페이지에서는 Cloud Storage가 BigQuery에서 객체 보강을 구성하는 방법을 설명합니다. 또한 라벨 찾기, 얼굴 감지, 선정적인 콘텐츠 식별, 경계 상자 추출, PDF 문서 분류를 위한 SQL 레시피도 제공합니다.

필요한 역할 가져오기

BigQuery에서 객체 보강을 쿼리하는 데 필요한 권한을 얻으려면 관리자에게 프로젝트에 대한 다음 IAM 역할을 부여해 달라고 요청합니다.

BigQuery 작업 사용자 (roles/bigquery.jobUser)
BigQuery 데이터 뷰어 (roles/bigquery.dataViewer)

역할 부여에 대한 자세한 내용은 프로젝트, 폴더, 조직에 대한 액세스 관리를 참조하세요.

이러한 사전 정의된 역할에는 BigQuery에서 객체 보강을 쿼리하는 데 필요한 권한이 포함되어 있습니다. 필요한 정확한 권한을 보려면 필수 권한 섹션을 펼치세요.

필수 권한

BigQuery에서 객체 보강을 쿼리하려면 다음 권한이 필요합니다.

bigquery.jobs.create
bigquery.tables.getData

커스텀 역할이나 다른 사전 정의된 역할을 사용하여 이 권한 을 부여받을 수도 있습니다.

객체 보강이 Storage Insights와 통합되는 방식

프로젝트의 Storage Insights 데이터 세트를 구성하면 Cloud Storage는 버킷 인벤토리 및 객체 컨텍스트를 BigQuery 데이터 세트로 주기적으로 내보냅니다.

BigQuery에서 데이터는 object_contexts_catalog_view 뷰에 표시됩니다. 이 뷰는 저장된 객체의 통합 카탈로그를 제공합니다. 각 행에는 객체 속성(project, bucket, name, generation)과 반복되는 contexts 레코드 배열이 포함됩니다.

contexts.key: 고유한 컨텍스트 식별자입니다. Cloud Storage에서 생성된 머신러닝 보강은 항상 google:annotation: 프리픽스 (예: google:annotation:label-detector-v1 또는 google:annotation:face-detector-v1)로 시작합니다.
contexts.value: 보강의 모델 실행 상태입니다 (예: SUCCESS 또는 FAILURE).
contexts.extendedData: 감지된 라벨, 얼굴 랜드마크, 다각형 꼭짓점, 카테고리 경로와 같은 모델의 보강 페이로드가 포함된 JSON 열입니다.

전체 테이블 구조 및 열 설명은 객체 컨텍스트 카탈로그 스키마를 참고하세요.

쿼리 패턴 이해: UNNEST와 EXISTS

객체에는 여러 컨텍스트가 있을 수 있으며 각 모델은 여러 감지 (예: 여러 라벨 또는 감지된 얼굴)를 출력할 수 있습니다. object_contexts_catalog_view 뷰를 쿼리할 때는 일반적으로 다음 SQL 패턴 중 하나를 사용합니다.

감지당 행 (UNNEST): 반복되는 contexts 레코드 배열과 모델의 내부 JSON 배열에 UNNEST 연산자를 사용합니다 (JSON_QUERY_ARRAY 함수 사용). 언네스팅은 감지된 각 특성이 자체 행을 생성하도록 반복되는 보강을 평면화합니다. 개별 라벨을 검사하거나, 경계 상자 좌표를 추출하거나, 감지 전반에서 통계를 계산해야 하는 경우 이 방법을 사용합니다.
객체당 행 (EXISTS): 상관된 EXISTS 하위 쿼리를 사용하여 객체에 특정 라벨, 모델 출력 또는 임계값이 포함되어 있는지 테스트합니다. 이 방법은 외부 쿼리에서 언네스팅하지 않고 반복되는 컨텍스트를 평가합니다. 일치하는 감지 수와 관계없이 객체당 정확히 하나의 행을 반환합니다.

쿼리 예시

다음 SQL 예는 일반적인 데이터 큐레이션 및 분석 워크플로를 위해 객체 보강을 쿼리하고 필터링하는 방법을 보여줍니다.

각 쿼리에서 다음을 바꿉니다.

PROJECT_ID: 프로젝트 Google Cloud 의 ID입니다.
DATASET_ID: Storage Insights가 데이터를 내보내는 BigQuery 데이터 세트의 이름입니다.

특정 라벨이 포함된 이미지 찾기

label-detector 모델에서 평가한 이미지를 검색하려면 일치하는 각 라벨을 개별 행으로 반환하거나 라벨이 포함된 고유한 이미지를 반환하면 됩니다.

일치하는 라벨 반환 (행 평면화)

다음 쿼리는 contexts 및 labelAnnotations 배열을 평면화하여 이미지 내에서 발견된 일치하는 각 라벨에 대해 별도의 행을 반환합니다.

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;

고유한 이미지 반환 (중복 행 방지)

이미지에 일치하는 라벨이 여러 개 포함되어 있으면 언네스팅은 해당 객체에 대해 여러 행을 생성합니다. 이미지당 정확히 하나의 행을 반환하려면 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;

웃는 피사체가 있는 인물 사진 및 셀카 사진 찾기 (얼굴 감지)

감정 표현, 머리 방향 각도, 경계 상자 크기와 같은 여러 얼굴 속성을 결합하여 특정 종류의 사진을 찾을 수 있습니다.

다음 쿼리는 공통 테이블 표현식 (CTE)을 사용하여 얼굴 경계 상자 범위를 계산합니다. 피사체가 이미지의 60% 이상을 차지하는 웃는 정면 인물 사진을 필터링합니다.

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;

이 쿼리는 다음 필터를 적용합니다.

joy_probability > 0.5: 행복을 표현하거나 웃는 것으로 평가된 얼굴을 선택합니다.
BETWEEN -10 AND 10: 얼굴이 카메라를 직접 향하도록 roll, pan, tilt 각도를 제한합니다.
face_coverage_percentage >= 0.60: 정규화된 다각형 꼭짓점 (0.0~1.0)을 사용하여 프레임의 60% 이상을 차지하는 얼굴을 필터링합니다.

콘텐츠 안전 플래그가 있는 이미지 찾기

content-safety 모델은 이미지에서 성인용 콘텐츠와 폭력적인 콘텐츠를 평가합니다. 모델은 각 카테고리에 VERY_UNLIKELY, UNLIKELY, POSSIBLE, LIKELY, 또는 VERY_LIKELY와 같은 일치 가능성 평점을 할당합니다.

다음 쿼리는 모델이 성인용 콘텐츠로 높은 신뢰도로 플래그를 지정하는 고유한 이미지를 반환합니다.

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;

폭력적인 콘텐츠 또는 다양한 민감도를 필터링하려면 adultContentLikelihood 필드를 violentContentLikelihood 필드로 바꾸거나 'LIKELY' 평점을 확인합니다.

특정 라벨이 포함된 이미지 수 계산

일치하는 라벨이 여러 개인 이미지를 중복 집계하지 않고 특정 라벨이 포함된 고유한 이미지의 총 개수를 계산하려면 EXISTS 하위 쿼리와 함께 COUNT(*)를 사용합니다.

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

감지된 객체의 경계 상자 좌표 추출

object-detector 모델은 이미지에서 실제 객체를 감지하고 객체가 프레임에 표시되는 위치를 나타내는 정규화된 경계 다각형 (boundingPoly)을 제공합니다.

다음 쿼리는 Drink라는 감지된 객체를 추출하고 최상위 경계 상자 좌표 열 (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;

하위 쿼리는 각 다각형의 최소 및 최대 정규화된 좌표를 계산하고 .* 표현식은 x_min, y_min, x_max, y_max 좌표 필드를 결과 집합으로 직접 압축 해제합니다.

여러 모델에서 객체 일치

Cloud Storage는 각 모델의 보강을 동일한 객체에 독립적인 컨텍스트로 연결하므로 여러 모델 결과를 조인하여 매우 구체적인 콘텐츠를 찾을 수 있습니다.

다음 쿼리는 contexts를 두 번 언네스팅하여 label-detector 모델이 Ristretto로 분류하고 object-detector 모델에서 식별한 현지화된 Drink 객체가 포함된 이미지를 찾습니다.

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 문서 필터링

pdf-classification 모델은 저장된 PDF 문서에 계층적 카테고리 (예: /Finance/Accounting & Auditing 또는 /Law & Government/Legal Services)를 할당합니다.

다음 쿼리는 신뢰도 점수가 0.5보다 큰 /Finance 브랜치 아래에 분류된 PDF 객체를 찾습니다.

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;

지원되는 분류 카테고리의 전체 목록은 PDF 분류 카테고리를 참고하세요.

분류 카테고리별로 PDF 문서 집계

보관 파일 전체에서 문서 배포를 분석하려면 카테고리별로 PDF 객체를 집계하고 계산합니다.

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;

쿼리 결과에 대해 조치 취하기

메타데이터 쿼리는 자동화된 데이터 큐레이션, 규정 준수 감사 또는 정책 시행의 첫 번째 단계인 경우가 많습니다. BigQuery에서 특정 객체 집합을 식별한 후에는 다음과 같이 이러한 결과를 다운스트림 워크플로에 제공할 수 있습니다.

머신러닝 데이터 세트 큐레이션: 쿼리 결과를 새 BigQuery 테이블 또는 CSV 파일로 내보내 커스텀 모델의 타겟 학습 및 검증 세트를 빌드합니다.
정책 위반 객체 격리 또는 삭제: 안전 쿼리에서 식별된 객체 이름과 버킷 위치를 스토리지 일괄 작업 에 제공하여 제한된 격리 버킷으로 이동하거나 일괄 삭제합니다.
콘텐츠 배포 감사 및 비용 최적화: 객체 분류, 측정기준, 카테고리를 집계하여 조직 전체의 스토리지 배포를 파악하고 타겟 수명 주기 관리 규칙을 만듭니다.