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 サブクエリを使用して、オブジェクトに特定のラベル、モデル出力、しきい値が含まれているかどうかをテストします。このアプローチでは、外部クエリでネスト解除せずに繰り返されるコンテキストを評価します。一致する検出結果の数に関係なく、オブジェクトごとに 1 行が返されます。

クエリの例

次の 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;

一意の画像を返す（重複する行を回避する）

画像に一致するラベルが複数含まれている場合、ネスト解除すると、そのオブジェクトに対して複数の行が生成されます。画像ごとに 1 行だけを返すには、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' 評価を確認します。

特定のラベルを含む画像の数を数える

複数のラベルに一致する画像を重複カウントせずに、特定のラベルを含む一意の画像の総数をカウントするには、COUNT(*) と 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
  );

検出されたオブジェクトの境界ボックスの座標を抽出する

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 を 2 回ネスト解除して、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 ファイルにエクスポートして、カスタムモデルのターゲットトレーニングセットと検証セットを構築します。
ポリシーに違反するオブジェクトを隔離または削除する: セーフティクエリで特定されたオブジェクト名とバケットのロケーションを Storage バッチオペレーションにフィードして、制限付きの隔離バケットに移動するか、一括削除します。
コンテンツ配信を監査して費用を最適化する: オブジェクトの分類、ディメンション、カテゴリを集計して、組織全体のストレージ分布を把握し、ターゲットライフサイクル管理ルールを作成します。