ניתוח העשרת אובייקטים ב-BigQuery

כששולחים שאילתות על העשרת אובייקטים באמצעות SQL ב-BigQuery, אפשר לחפש, לסנן ולנתח מטא-נתונים במיליוני אובייקטים מאוחסנים בלי לבדוק כל אובייקט בנפרד.

כשמגדירים מערכי נתונים של Storage Insights, מערכת Cloud Storage מייצאת באופן אוטומטי את מלאי האובייקטים והמטא-נתונים שלהם, כולל העשרות של אובייקטים שנוצרו על ידי מודלים של AI, ישירות אל BigQuery. בדף הזה מוסבר איך Cloud Storage מבנה את העשרת האובייקטים ב-BigQuery. בנוסף, הוא מספק מתכוני SQL למציאת תוויות, זיהוי פנים, זיהוי תוכן בוטה, חילוץ תיבות תוחמות וסיווג מסמכי PDF.

קבלת התפקידים הנדרשים

כדי לקבל את ההרשאות שדרושות להפעלת שאילתות על העשרת אובייקטים ב-BigQuery, אתם צריכים לבקש מהאדמין להקצות לכם את תפקידי ה-IAM הבאים בפרויקט:

להסבר על מתן תפקידים, ראו איך מנהלים את הגישה ברמת הפרויקט, התיקייה והארגון.

התפקידים המוגדרים מראש האלה מכילים את ההרשאות שנדרשות לשליחת שאילתות להעשרת אובייקטים ב-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 שמכילה את מטען הייעודי (payload) של ההעשרה של המודל, כמו תוויות שזוהו, ציוני דרך של פנים, קודקודי פוליגון ונתיבי קטגוריות.

מבנה הטבלה המלא ותיאורי העמודות מפורטים בסכימת הקטלוג של הקשרי אובייקטים.

הסבר על מבני שאילתות: UNNEST לעומת EXISTS

לאובייקט יכולים להיות כמה הקשרים, וכל מודל יכול להפיק כמה זיהויים (כמו כמה תוויות או פנים שזוהו). כששולחים שאילתה לתצוגה object_contexts_catalog_view, בדרך כלל משתמשים באחת מתבניות ה-SQL הבאות:

  • שורה לכל זיהוי (UNNEST): משתמשים באופרטור UNNEST במערך הרשומות contexts החוזר ובמערך ה-JSON הפנימי של המודל (באמצעות הפונקציה JSON_QUERY_ARRAY). ביטול ההטמעה משטח העשרות חוזרות, כך שכל תכונה שזוהתה יוצרת שורה משלה. הגישה הזו מתאימה כשצריך לבדוק תוויות ספציפיות, לחלץ קואורדינטות של תיבות תוחמות או לחשב נתונים סטטיסטיים של כל הזיהויים.
  • ‫שורה לכל אובייקט (EXISTS): משתמשים בשאילתת משנה (subquery) מקושרת של EXISTS כדי לבדוק אם אובייקט מכיל תווית, פלט מהמודל או סף מסוימים. בגישה הזו, ההקשרים החוזרים נבדקים בלי לבטל את ההטמעה שלהם בשאילתה החיצונית. היא מחזירה בדיוק שורה אחת לכל אובייקט, בלי קשר למספר הזיהויים התואמים.

שאילתות לדוגמה

הדוגמאות הבאות ב-SQL מראות איך לשלוח שאילתות ולסנן העשרת אובייקטים בתהליכי עבודה נפוצים של ניתוח נתונים וניהול תוכן.

בכל שאילתה, מחליפים את מה שכתוב בשדות הבאים:

  • ‫PROJECT_ID: מזהה הפרויקט ב- Cloud de Confiance .
  • ‫DATASET_ID: השם של מערך הנתונים ב-BigQuery שאליו Storage Insights מייצא את הנתונים.

חיפוש תמונות שמכילות תווית ספציפית

כדי לחפש תמונות שהמודל 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, ושמכילות אובייקט Drink מקומי שזוהה על ידי המודל 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;

סינון מסמכי PDF לפי קטגוריית סיווג

מודל pdf-classification מקצה קטגוריות היררכיות (כמו /Finance/Accounting & Auditing או /Law & Government/Legal Services) למסמכי PDF מאוחסנים.

השאילתה הבאה מוצאת אובייקטים מסוג PDF שמסווגים בענף /Finance עם ציוני מהימנות גבוהים מ-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;

הרשימה המלאה של קטגוריות הטקסונומיה הנתמכות מופיעה במאמר קטגוריות סיווג של קובצי 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 חדשים כדי ליצור קבוצות אימון וקבוצות נתונים לתיקוף ממוקדות למודלים בהתאמה אישית.
  • העברה להסגר או הסרה של אובייקטים שמפירים את המדיניות: מזינים את שמות האובייקטים ואת מיקומי הקטגוריות שזוהו על ידי שאילתות הבטיחות אל פעולות רבות על פריטי אחסון כדי להעביר אותם לקטגוריות מוגבלות של הסגר או למחוק אותם בכמות גדולה.
  • בודקים את הפצת התוכן ומבצעים אופטימיזציה של העלויות: אפשר לצבור סיווגים, מידות וקטגוריות של אובייקטים כדי להבין את חלוקת האחסון בארגון וליצור כללים ממוקדים לניהול מחזור החיים.

המאמרים הבאים