שאילתות מתמשכות של תצוגות מהותיות

כדי ליצור תצוגה מהותית מתמשכת של טבלת Bigtable, מריצים שאילתת SQL שמגדירה את התצוגה המהותית המתמשכת.

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

תצוגות חומריות רציפות משתמשות בתחביר SQL מוגבל. בדוגמה הבאה אפשר לראות איך ליצור שאילתת SQL של תצוגה חומרית מתמשכת:

SELECT
  expression AS alias [, ...]
FROM from_item
[ WHERE bool_expression ]
GROUP BY expression [, ...];

from_item:
    {
      table_name [ as_alias ]
      | field_path
      }

as_alias:
    [ AS ] alias

אם רוצים ליצור שאילתת SQL של תצוגה חומרית מתמשכת כאינדקס משני אסינכרוני, משתמשים בפסקה ORDER BY:

SELECT
  expression AS alias [, ...]
FROM from_item
[ WHERE bool_expression ]
ORDER BY expression [, ...];

from_item:
    {
      table_name [ as_alias ]
      | field_path
      }

as_alias:
    [ AS ] alias

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

מגבלות על שאילתות

הכללים הבאים חלים על שאילתת SQL שמשמשת ליצירת תצוגה מהותית רציפה:

• חייב להיות משפט SELECT.
• חייב להיות סעיף GROUP BY או, בשאילתות אסינכרוניות של אינדקס משני, סעיף ORDER BY, אבל לא את שניהם.
• חובה להשתמש רק בפונקציות צבירה נתמכות.
• יכולות להיות כמה צבירות בכל קבוצה.

צבירות נתמכות

אפשר להשתמש בפונקציות הצבירה הבאות בשאילתת SQL שמגדירה תצוגה חומרית רציפה:

• COUNT
• SUM
• MIN
• MAX
• HLL_COUNT.INIT
• HLL_COUNT.MERGE
• HLL_COUNT.MERGE_PARTIAL
• ANY_VALUE
• BIT_AND
• BIT_OR
• BIT_XOR
• AVG

אם SELECT COUNT(*) אתם צריכים להגדיר מפתח שורה, כמו בדוגמה הבאה:

SELECT
  '*' AS _key,
  COUNT(*) AS count
FROM
  foo
GROUP BY
  _key;

תכונות SQL שלא נתמכות

אי אפשר להשתמש בתכונות ה-SQL הבאות:

• כל תכונה שלא נתמכת ב-GoogleSQL for Bigtable
• ARRAY
• ARRAY_AGG
• ARRAY_CONCAT_AGG
• COUNT_IF
• ‫CURRENT_TIME ורוב הפונקציות הלא דטרמיניסטיות האחרות, עם חריגים.
• ‫DATE, DATETIME כעמודות פלט (שימוש ב-TIMESTAMP או אחסון של מחרוזת).
• DESC מיון בפלט
• DISTINCT, כמו ב-SUM(*DISTINCT* value))
• LIMIT/OFFSET
• SELECT *
• ‫OVER clause כדי ליצור צבירה של חלונות
• STRUCT

בנוסף, אי אפשר להשתמש בסעיפים GROUP BY או ORDER BY כסעיפים משנה או ליצור עמודות מיפוי. מידע על מגבלות נוספות זמין במאמר מגבלות.

הימנעות משורות שמוחרגות

שורות קלט לא נכללות בתצוגה חומרית מתמשכת במקרים הבאים:

• נבחרו יותר מ-1MiB של נתונים מהשורה. לדוגמה, אם השאילתה היא SELECT apple AS apples , SUM(banana) AS sum_bananas FROM my_table GROUP BY apples, כל שורה שמכילה יותר מ-‎1MiB של נתונים בעמודות apple ו-banana לא תיכלל בתצוגה הרציפה של נתונים חומריים.
• יותר מ-1MiB של נתונים מופקים מהשורה. הבעיה הזו יכולה להתרחש כשמשתמשים בשאילתות כמו SELECT REPEAT(apple, 1000) או כשמשתמשים בקבועים גדולים.
• הפלט מכיל נתונים בכמות גדולה פי 10 או יותר מהכמות שנבחרה.
• השאילתה לא תואמת לנתונים שלכם. הפעולות האלה כוללות ניסיון לחלק באפס, הצפת מספר שלם או ציפייה לפורמט של מפתח שורה שלא נמצא בכל מפתח שורה.

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

פרטי שאילתה

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

הצהרת SELECT

הצהרת הבחירה מגדירה את העמודות והצבירות שמשמשות בתצוגה המגולמת הרציפה. ההצהרה צריכה להשתמש בסעיף GROUP BY כדי לצבור נתונים בשורות או בסעיף ORDER BY כדי ליצור אינדקס משני אסינכרוני.

אין תמיכה ב-SELECT *, אבל יש תמיכה ב-SELECT COUNT(*).

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

זו דוגמה לשאילתת צבירה GROUP BY רגילה ב-SQL:

SELECT
  myfamily["node"] AS node,
  myfamily["type"] AS type,
  COUNT(clicks) AS clicks_per_key
FROM
  mytable
GROUP BY
  node,
  type

מפתחות שורות ונתונים לא מצטברים

אפשר לציין _key כמפתח השורה לתצוגה חומרית רציפה. אם לא, העמודות בסעיף GROUP BY יוצרות את המפתח בתצוגה.

מפתחות השורות מוגדרים על ידי עמודה _key

אפשר לציין עמודה _key כשמגדירים תצוגה חומרית רציפה. (העמודה הזו שונה מהעמודה _key שמתקבלת כשמריצים שאילתת SQL בטבלת Bigtable). אם מציינים _key, הכללים הבאים חלים:

• חובה לקבץ לפי _key, ואי אפשר לקבץ לפי שום דבר אחר חוץ מ_timestamp (אופציונלי). מידע נוסף זמין במאמר בנושא חותמות זמן.
• העמודה _key חייבת להיות מסוג BYTES.

ציון של _key שימושי אם אתם מתכננים לקרוא את התצוגה באמצעות ReadRows ולא באמצעות SQL, כי הוא מאפשר לכם לשלוט בפורמט של מפתח השורה. לעומת זאת, שאילתת SQL לתצוגה עם _key מוגדרת עשויה לדרוש פענוח מפורש של _key במקום להחזיר רק עמודות מפתח מובְנות.

מפתחות השורות מוגדרים על ידי פסקה GROUP BY או ORDER BY

אם לא מציינים _key, העמודות הלא מצטברות ברשימת SELECT הופכות למפתח השורה בתצוגה. אפשר לתת לעמודות המפתח כל שם שנתמך על ידי מוסכמות SQL. כדאי להשתמש בגישה הזו אם אתם מתכננים להשתמש ב-SQL כדי להריץ שאילתות על התצוגה במקום על בקשת ReadRows.

עמודות פלט לא מצטברות ברשימה SELECT צריכות להיכלל בפסוקית GROUP BY. הסדר שבו העמודות נכתבות בסעיף GROUP BY הוא הסדר שבו הנתונים מאוחסנים במפתח השורה של התצוגה המגולמת הרציפה. לדוגמה, GROUP BY a, b, c הוא באופן מרומז ORDER BY a ASC, b ASC, c ASC.

אם משתמשים בסעיף ORDER BY במקום בסעיף GROUP BY כדי ליצור אינדקס משני אסינכרוני, העמודות ברשימה SELECT שכלולות בסעיף ORDER BY הופכות למפתח השורה בתצוגה. הסדר שבו העמודות נכתבות בפסוקית ORDER BY הוא הסדר שבו הנתונים מאוחסנים במפתח השורה של התצוגה המהותית הרציפה. לדוגמה, ORDER BY a, b, c מאחסן את הנתונים עם מפתחות שורות שממוינים לפי a ASC, ואז לפי b ASC, ואז לפי c ASC.

חשוב: העמודות שיוצרות את מפתח השורה בתצוגה חומרית רציפה לא יכולות להיות NULL. ערך NULL בעמודת מפתח גורם להחרגת הנתונים בשורה התואמת בטבלת המקור מהתצוגה.

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

נתונים מצטברים

עמודות מצטברות בשאילתה מגדירות את החישובים שיוצרים את הנתונים בתצוגה המגובשת המתמשכת.

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

דוגמה:

SELECT
  fam["baz"] AS baz,
  SUM(fam["foo"]) AS sum_foo,
  SUM(fam["bar"]) AS sum_bar
FROM
  TABLE

GROUP BY
  baz;

המאפיינים של פלט השאילתה:

• הפלט של כל baz מופיע בשורה נפרדת בסדר baz ASC.
• אם ל-baz מסוים יש לפחות foo אחד, אז הערך של sum_foo בשורת הפלט הוא ערך שאינו NULL.
• אם ל-baz מסוים יש לפחות bar אחד, אז הערך של sum_bar בשורת הפלט הוא ערך שאינו NULL.
• אם ל-baz מסוים אין ערך באחת מהעמודות, הוא לא ייכלל בתוצאות.

אם תריצו שאילתה על התצוגה עם SELECT *, התוצאה תיראה בערך כך:

baz	sum_foo	sum_bar
baz1	sum_foo1	sum_bar1
baz2	sum_foo2	sum_bar2

חותמות זמן

חותמת הזמן שמוגדרת כברירת מחדל לתא פלט בתצוגה חומרית רציפה היא 0 (1970-01-01 00:00:00Z). אפשר לראות את זה כשקוראים את התצוגה באמצעות ReadRows, ולא כשמבצעים שאילתה באמצעות SQL.

כדי להשתמש בחותמת זמן אחרת בפלט, אפשר להוסיף עמודה מסוג TIMESTAMP לרשימת SELECT של השאילתה ולתת לה את השם _timestamp. אם שולחים שאילתה לתצוגה המגובשת הרציפה באמצעות ReadRows,‏ _timestamp הופך לחותמת הזמן של התאים האחרים בשורה.

חותמת הזמן לא יכולה להיות NULL, היא חייבת להיות גדולה מ-0 או שווה ל-0, והיא חייבת להיות כפולה של 1,000 (דיוק של אלפית השנייה). ‫Bigtable לא תומך בחותמות זמן של תאים שמוקדמות לראשית זמן יוניקס (Unix epoch) ‏(‎1970-01-01T00:00:00Z).

דוגמה: דגימה מחדש של נתונים מצטברים לפי יום. השאילתה משתמשת בפונקציה UNPACK.

SELECT
  _key,
  TIMESTAMP_TRUNC(_timestamp, DAY) AS _timestamp,
  SUM(sum_family["sum_column"]) AS sum_column,
  SUM(sum_family["foo"]) AS second_sum_column
FROM
  UNPACK(
  SELECT
    *
  FROM
    my_table(with_history => TRUE))
GROUP BY
  1,
  2

אם ל-SUM מסוים יש קלט לא ריק ביום מסוים, שורת הפלט מכילה ערך מצטבר עם חותמת זמן שתואמת ליום שקוצר.

אם מריצים שאילתה על התצוגה עם SELECT *, התוצאה תיראה כך:

_key	_timestamp	sum_column	second_sum_column
1	2024-05-01 00:00:00Z	23	99
2	2024-05-02 00:00:00Z	45	201
3	2024-05-03 00:00:00Z	NULL	56
4	2024-05-04 00:00:00Z	8	NULL

שאילתות לא דטרמיניסטיות

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

פונקציית מאפיין האשכול

הפונקציה CLUSTER_ATTRIBUTE() מאפשרת גישה לתקציר הנתונים (metadata) באופן פרוגרמטי לגבי אשכול Bigtable שמריץ את השאילתה של התצוגה הרציפה המגובה בחומר. המאפיין cluster_id מחזיר את מזהה המחרוזת של האשכול שמבצע את העיבוד של התצוגה.

הערה: CLUSTER_ATTRIBUTE הוא מאפיין של אשכול העיבוד, ולא מאפיין של הנתונים שמאוחסנים בטבלת המקור. מכיוון שעיבוד רציף של תצוגות חומריות פועל באופן עצמאי בכל אשכול במופע, CLUSTER_ATTRIBUTE('cluster_id') מוערך כמזהה של האשכול הספציפי הזה במהלך תחזוקה של תצוגה מקומית.

בידוד תצוגה לאשכול יחיד

כדי לבודד את החישוב והאחסון של תצוגה חומרית מתמשכת לאשכול יחיד, כמו אשכול ייעודי לניתוח נתונים או ליצירת דוחות, משווים את CLUSTER_ATTRIBUTE('cluster_id') למחרוזת קבועה שמכילה את מזהה האשכול:

SELECT
  metrics['sensor_id'] AS sensor_id,
  COUNT(1) AS reading_count
FROM `TABLE_ID`
WHERE CLUSTER_ATTRIBUTE('cluster_id') = 'CLUSTER_ID'
GROUP BY 1

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

• ‫TABLE_ID: המזהה הייחודי של טבלת Bigtable.
• ‫CLUSTER_ID: המזהה של האשכול שבו רוצים ליצור את התצוגה החומרית.

השאילתה הזו מתנהגת באופן הבא במופע שלכם:

• אשכול ייעודי: באשכול שתואם לתנאי CLUSTER_ID, התנאי WHERE שווה ל-true. ‫Bigtable צובר את כל הנתונים מטבלת המקור, בלי קשר לאיזה אשכול קלט או שכפל אותם, ומממש את הנתונים האלה בעותק של התצוגה באשכול הזה.
• אשכולות אחרים: בכל האשכולות האחרים במופע, הערך של CLUSTER_ATTRIBUTE('cluster_id') הוא מזהה האשכול המתאים, ולכן התנאי WHERE הוא false. האשכולות האלה לא מבצעים חישובים ולא מאחסנים שורות עבור התצוגה הרציפה החומרית, וכך נחסכים משאבי מחשוב ואחסון.

סינון נתונים לפי מקור האשכול

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

SELECT
  metrics['sensor_id'] AS sensor_id,
  COUNT(1) AS reading_count
FROM `TABLE_ID`
WHERE metrics['origin_cluster'] = CLUSTER_ATTRIBUTE('cluster_id')
GROUP BY 1

בשילתא הזו, metrics['origin_cluster'] הוא מאפיין של הנתונים שמאוחסנים בשורה, ואילו CLUSTER_ATTRIBUTE('cluster_id') הוא מאפיין של האשכול שמעבד את התצוגה. כל רפליקה של תצוגה חומרית רציפה באשכול מסוים צוברת רק את השורות שבהן הערך המאוחסן של origin_cluster תואם למזהה של אותו אשכול.

אם יכול להיות שחלק מהשורות לא יכללו אשכול מקור, אפשר להשתמש ב-IF_NULL כדי להקצות אשכול חלופי:

SELECT
  metrics['sensor_id'] AS sensor_id,
  COUNT(1) AS reading_count
FROM `TABLE_ID`
WHERE IF_NULL(metrics['origin_cluster'], 'DEFAULT_CLUSTER_ID') = CLUSTER_ATTRIBUTE('cluster_id')
GROUP BY 1

מאפייני השאילתה

לשאילתות שמשתמשות ב-CLUSTER_ATTRIBUTE יש את המאפיינים הבאים:

• מכיוון ש-sensor_id נמצא בסעיף GROUP BY, הוא הופך לחלק ממפתח השורה בתצוגה המהותית הרציפה.
• כדי להשתמש בפונקציה הלא דטרמיניסטית הזו, צריך להגדיר את הדגל ignore_warnings ל-true כשיוצרים את התצוגה. מידע נוסף זמין במאמר בנושא יצירת תצוגה חומרית מתמשכת.

כשמריצים שאילתה על התצוגה עם SELECT *, התוצאה דומה לתוצאה הבאה:

sensor_id	reading_count
sensor-1234	25
sensor-5678	68

מגבלות

השימוש בפונקציה הלא דטרמיניסטית CLUSTER_ATTRIBUTE מחייב הקפדה על דרישות הניתוב:

• אין תמיכה בניתוב בין כמה אשכולות: מכיוון שהפלט של הפונקציה תלוי באשכול הפיזי שמבצע את השאילתה, ניתוב בין כמה אשכולות יפיק תוצאות לא דטרמיניסטיות.
• שימוש בניתוב לאשכול יחיד: כשמבצעים שאילתה בתצוגה חומרית רציפה שמשתמשת ב-CLUSTER_ATTRIBUTE, צריך להשתמש בפרופיל אפליקציה שהוגדר לניתוב לאשכול יחיד.
• הפניה לאשכול הייעודי: אם מבודדים תצוגה לאשכול ספציפי, כמו WHERE CLUSTER_ATTRIBUTE('cluster_id') = 'my-reporting-cluster', צריך להפנות את פרופיל האפליקציה ישירות לאשכול הזה. שאילתה של אשכול אחר מחזירה תוצאות ריקות כי התצוגה לא מאוכלסת באשכולות אחרים.

קידוד

אם אתם שולחים שאילתה לתצוגה חומרית רציפה באמצעות SQL, אתם לא צריכים לדעת איך הערכים המצטברים מקודדים כי SQL חושף את התוצאות כעמודות מוקלדות.

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

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

סוג	קידוד
BOOL	ערך של בייט אחד, 1 = true, ‏ 0 = false
BYTES	ללא קידוד
‫INT64 (או INT, ‏ SMALLINT, ‏ INTEGER, ‏ BIGINT, ‏ TINYINT, ‏ BYTEINT)	‫64 ביט big-endian
FLOAT64	‫64 ביט IEEE 754, לא כולל NaN ו-‎+/-inf
מחרוזת	UTF-8
TIME/TIMESTAMP	מספר שלם ב-64 ביט שמייצג את מספר המיקרו-שניות מאז ראשית זמן יוניקס (Unix epoch) (עקבי עם GoogleSQL)

מידע נוסף זמין במאמר קידוד בהפניה ל-Data API.

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

• יצירה וניהול של תצוגות חומריות רציפות
• מסמכי עזר בנושא GoogleSQL ל-Bigtable