Google 会使用 AI 技术将内容翻译成您偏好的语言。AI 翻译可能包含错误。

在 BigQuery 中分析对象丰富化数据

通过在 BigQuery 中使用 SQL 查询对象丰富化，您可以搜索、过滤和分析数百万个存储对象中的元数据，而无需单独检查每个对象。

配置 Storage Insights 数据集后，Cloud Storage 会自动将您的对象清单和元数据（包括 AI 模型生成的对象丰富化）直接导出到 BigQuery 中。本页面介绍了 Cloud Storage 如何在 BigQuery 中构建对象丰富化。此外，本页面还提供了 SQL 配方，用于查找标签、检测人脸、识别露骨内容、提取边界框以及对 PDF 文档进行分类。

获取所需的角色

如需获得在 BigQuery 中查询对象丰富化所需的权限，请让您的管理员向您授予项目的以下 IAM 角色：

BigQuery Job User (roles/bigquery.jobUser)
BigQuery Data Viewer (roles/bigquery.dataViewer)

如需详细了解如何授予角色，请参阅管理对项目、文件夹和组织的访问权限。

这些预定义角色可提供在 BigQuery 中查询对象丰富化所需的权限。如需查看所需的确切权限，请展开所需权限部分：

所需权限

在 BigQuery 中查询对象丰富化需要以下权限：

bigquery.jobs.create
bigquery.tables.getData

您也可以使用自定义角色或其他预定义角色来获取这些权限。

对象丰富化与存储空间分析的集成方式

为项目配置 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：存储空间分析将数据导出到的 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'评分。

统计包含特定标签的图片数量

如需统计包含特定标签的唯一图片总数，而不会重复统计与多个标签匹配的图片，请将 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 两次，以查找 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 模型会将分层类别（例如 /Finance/Accounting & Auditing 或 /Law & Government/Legal Services）分配给存储的 PDF 文档。

以下查询会查找在 /Finance 分支下分类且置信度分数大于 0.5 的 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 文件，以便为自定义模型构建有针对性的训练集和验证集。
隔离或移除违反政策的对象：将安全查询识别出的对象名称和存储桶位置馈送到存储批量操作中，以便将它们移至受限的隔离存储桶或批量删除它们。
审核内容分发情况并优化费用：汇总对象分类、尺寸和类别，以了解组织内的存储分布情况并创建有针对性的生命周期管理规则。