דוגמאות לשאילתות GoogleSQL ל-Bigtable

בדף הזה מוצגות דוגמאות לדפוסי שאילתות SQL עבור שאילתות Bigtable נפוצות ומתקדמות. אפשר להריץ שאילתות GoogleSQL בעורך השאילתות של Bigtable Studio. אפשר גם להריץ שאילתות באמצעות ספריית הלקוח של Bigtable ל-Java.

לפני שקוראים את הדף הזה, כדאי לקרוא את הסקירה הכללית על GoogleSQL ל-Bigtable.

בדוגמאות שבדף הזה נעשה שימוש במזהים ובערכים דומים לאלה שמופיעים בנתונים לדוגמאות.

דפוסי שאילתות SQL נפוצים ב-Bigtable

אלו הן דוגמאות לשאילתות נפוצות לגבי נתונים ב-Bigtable. במאמרים דוגמאות לקריאה ושימוש במסננים אפשר לראות דוגמאות לשאילתות דומות שמבצעות קריאה ל-Bigtable Data API. דוגמאות לשאילתות על מפתחות שורות מובְנים מופיעות במאמר שאילתות על מפתחות שורות מובְנים.

אחזור הגרסה האחרונה של כל העמודות עבור מפתח שורה נתון.

  SELECT * FROM myTable WHERE _key = 'r1'

אחזור כל הגרסאות של כל העמודות עבור מפתח שורה נתון.

  SELECT * FROM myTable(with_history => TRUE) WHERE _key = 'r1'

שליפה של הגרסה האחרונה של עמודה מסוימת ממשפחת עמודות מסוימת עבור מפתח שורה נתון.

  SELECT stats_summary['os_build'] AS os
  FROM analytics
  WHERE _key = 'phone#4c410523#20190501'

אחזור של מפתחות שורות והגרסה האחרונה של כמה עמודות לטווח נתון של מפתחות שורות.

  SELECT
    _key,
    stats_summary['os_build'] AS os,
    stats_summary['user_agent'] AS agent
  FROM analytics
  WHERE _key >= 'phone#4c410523#20190501' AND _key < 'phone#4c410523#201906201'

אחזור כל הגרסאות של כל העמודות עבור כמה טווחי מפתחות שורות, עד 10 שורות.

  SELECT *
  FROM analytics(with_history => TRUE)
  WHERE
    (_key >= 'phone#4c410523#20190501' AND _key < 'phone#4c410523#201906201')
    OR (_key >= 'phone#5c10102#20190501' AND _key < 'phone#5c10102#20190601')
  LIMIT 10

אחזור של כל הגרסאות של כל העמודות עבור כמה מפתחות שורות.

  SELECT *
  FROM analytics(with_history => TRUE)
  WHERE _key = 'phone#4c410523#20190501' OR _key = 'phone#4c410523#20190502'

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

  SELECT *
  FROM analytics(with_history => TRUE)
  WHERE _key IS IN ('phone#4c410523#20190501', 'phone#4c410523#20190502')

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

  SELECT stats_summary
  FROM analytics
  WHERE _key LIKE 'phone#%'

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

  SELECT _key, cell_plan FROM analytics(with_history => TRUE, latest_n => 3)

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

  SELECT *
  FROM myTable(with_history => TRUE)
  WHERE REGEXP_CONTAINS(_key, '.*#20190501$')

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

  SELECT *
  FROM myTable
  WHERE _key LIKE 'user12%' AND counterFamily['counter'] > 123

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

  SELECT *
  FROM analytics
  WHERE _key LIKE 'com.mysite%' AND session['referrer'] = './home'

מסווגת שורה נתונה על סמך הערך של עמודה נתונה. השאילתה הזו דומה לשימוש במסנן מותנה ליצירת שאילתות ב-Bigtable Data API.

  SELECT
    *,
    CASE cell_plan['data_plan']
      WHEN '10gb' THEN 'passed-filter'
      ELSE 'filtered-out'
      END
      AS label
  FROM analytics

שליפת מפתח השורה והמסווגים של העמודות במשפחת עמודות ספציפית עבור טווח מפתחות שורות ספציפי. ב-SQL, משפחות של עמודות מיוצגות על ידי סוג הנתונים map, שבו כל מסווג עמודה וערך ממופים כצמד מפתח/ערך. שאילתת ה-SQL הזו דומה לשימוש במסנן של ערך strip ב-Bigtable Data API.

  SELECT _key, MAP_KEYS(cell_plan) AS keys
  FROM analytics
  WHERE _key >= 'phone#4c410523#20190501' AND _key < 'phone#4c410523#201906201'

הפונקציה UNPACK מאפשרת להמיר נתונים מ-Bigtable לפורמט של סדרת זמן טבלאית, ששימושי לביצוע ניתוח של סדרות זמן. נניח שיש לכם עמודה clicks במשפחת עמודות engagement. השאילתה הבאה משתמשת ב-UNPACK כדי לראות את הביצועים של קמפיינים מסוימים על ידי צבירת הקליקים במשך דקה מהשעה האחרונה.

  SELECT
    FORMAT_TIMESTAMP('%M', _timestamp) AS minute,
    COUNT(clicks) AS total_clicks
  FROM
    UNPACK((
      SELECT engagement['clicks'] as clicks
      FROM metrics(with_history => true, after => TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR))
      WHERE _key = @campaign_id
    ))
  GROUP BY
    minute;

דפוסי שאילתות SQL מתקדמים ב-Bigtable

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

בעזרת השאילתה הבאה, אפשר לאחזר את מפתח השורה ואת הערך האחרון של מאפיין ה-JSON‏ abc במשפחת העמודות session. מידע נוסף זמין במאמר בנושא פונקציות JSON.

  SELECT _key, JSON_VALUE(session['payload'], '$.abc') AS abc FROM analytics

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

  SELECT
    _key AS userid,
    session['total_minutes'] / session['count'] AS avg_session_length
  FROM analytics

באמצעות השאילתה הבאה, אפשר לאחזר את הגרסה האחרונה של כל העמודות עבור קידומת נתונה של מפתח שורה, אם משפחת העמודות session מכילה את referrer, ‏ origin או server כמאפיין עמודה. אפשר גם לכתוב את השאילתה הזו כסדרה של השוואות נפרדות, כמו session['referrer'] IS NOT NULL OR session['origin'] IS NOT NULL. עם זאת, בשאילתות שכוללות מספר גדול של השוואות, מומלץ להשתמש בגישה הבאה.

  SELECT *
  FROM analytics
  WHERE
    _key LIKE 'com.abc%'
    AND ARRAY_INCLUDES_ANY(MAP_KEYS(session), ['referrer', 'origin', 'server'])

באמצעות השאילתה הבאה, אפשר לאחזר את הגרסה האחרונה של כל העמודות עבור קידומת נתונה של מפתח שורה, אם משפחת העמודות session מכילה את referrer, ‏ origin ו-server כמאפייני עמודה. לחלופין, אפשר לכתוב את השאילתה הזו כסדרה של השוואות נפרדות, כמו session['referrer'] IS NOT NULL AND session ['origin'] IS NOT NULL.

  SELECT *
  FROM analytics
  WHERE
    _key LIKE 'com.abc%'
    AND ARRAY_INCLUDES_ALL(MAP_KEYS(session), ['referrer', 'origin', 'server'])

בעזרת השאילתה הבאה, אפשר לאחזר את הגרסה האחרונה של כל העמודות עבור קידומת נתונה של מפתח שורה, אם משפחת העמודות session מכילה את הערכים com.google.search, com.google.maps או com.google.shopping.

  SELECT *
  FROM analytics
  WHERE
    _key LIKE 'com.abc%'
    AND ARRAY_INCLUDES_ANY(
      MAP_VALUES(session),
      ['com.google.search', 'com.google.maps', 'com.google.shopping'])

בעזרת השאילתה הבאה אפשר לאחזר את הגרסה האחרונה של כל העמודות אם זוגות של מפתח/ערך במשפחת העמודות cell_plan כוללים גם את data_plan:unlimited וגם את roaming:North America.

  SELECT *
  FROM analytics
  WHERE
    ARRAY_INCLUDES_ALL(
      CAST(
        MAP_ENTRIES(cell_plan)
        AS ARRAY<STRUCT<key STRING, value STRING>>),
      [('data_plan', 'unlimited'), ('roaming', 'North America')])

בעזרת השאילתה הבאה אפשר לאחזר את הקריאות row key ו-temperature של חיישני מזג אוויר במקרים שבהם הטמפרטורה הייתה מעל 70 מעלות במהלך שבע המדידות האחרונות.

  SELECT
    _key AS sensorid,
    ARRAY_FILTER(
      CAST(
        sensor['temperature']
        AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>),
      e -> CAST(e.value AS FLOAT32) > 70) AS high_temperature
  FROM weather(with_history => TRUE, latest_n => 7)

בסדר הסינון הזמני, latest_n מופיע אחרון, ולכן שאילתה כמו after => X, before => y, latest_n => 3 מחזירה את שלושת הערכים האחרונים שעומדים בתנאים after ו-before. אם התרחיש לדוגמה שלכם מחייב שlatest_n יקבל עדיפות, אתם יכולים לציין את latest_n כמסנן הזמני היחיד, ואז להחיל את שאר המסננים הזמניים באמצעות אופרטורים של שאילתות בהצהרת SELECT, כמו בדוגמה. מידע נוסף מופיע במאמר בנושא מסננים זמניים.

  SELECT
    ARRAY_FILTER(
      CAST(
        address['street']
        AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>),
      e -> e.timestamp > TIMESTAMP('2021-01-04T23:51:00.000Z'))
      AS street_address
  FROM locations(with_history => TRUE, latest_n => 3)

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

  SELECT
    ARRAY_FILTER(
      CAST(
        address['street']
        AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>),
      (e, i) -> i <= 2)
      AS street_address,
    ARRAY_FILTER(
      ARRAY_REVERSE(
        CAST(
          address['state']
          AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>)),
      (e, i) -> i <= 1)
      AS state
  FROM locations(with_history => TRUE)

בעזרת השאילתה הבאה, אפשר לאחזר את כל הגרסאות של כל העמודות אם זוגות של מפתח/ערך במשפחת העמודות של הכתובת כוללים את city:Savannah או את city:Nashville בכל נקודת זמן.

  SELECT *
  FROM locations(with_history => TRUE)
  WHERE
    ARRAY_LENGTH(
      ARRAY_FILTER(
        CAST(
          MAP_ENTRIES(address)
          AS ARRAY<
            STRUCT<
              key STRING,
              value ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>>>),
        e ->
          e.key = 'city'
          AND ARRAY_INCLUDES_ANY(
            ARRAY_TRANSFORM(e.value, k -> k.value), ['Savannah', 'Nashville'])))
    > 0

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

  SELECT *
  FROM locations(with_history => TRUE)
  WHERE
    ARRAY_LENGTH(
      ARRAY_FILTER(
        MAP_ENTRIES(address),
        e ->
          e.key = 'city'
          AND ARRAY_INCLUDES_ANY(
            ARRAY_TRANSFORM(e.value, k -> k.value), ['Savannah', 'Nashville'])))
    > 0

ניתוח SQL עם תפוקה גבוהה באמצעות Data Boost

במהדורת Enterprise Plus, כדי להריץ שאילתות שסורקות כמויות גדולות של נתונים בלי להשפיע על הביצועים של אשכולות הייצור, אפשר להשתמש ב-Data Boost למשימות ניתוח נתונים עם נפח נתונים גבוה. השאילתות האלה מתאימות במיוחד לניתוח בזמן אמת וליצירת תובנות מנתונים היסטוריים או מנתוני סדרות זמן. כדי להריץ את השאילתות האלה, צריך להשתמש בפרופיל אפליקציה שהוגדר לשימוש ב-Data Boost.

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

ניתוח נתונים היסטוריים של סדרות נתונים מבוססות זמן

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

SELECT _key, metrics
FROM myTable(with_history => TRUE, after => TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR))

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

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

SELECT
  _key AS device_id,
  COUNT(events['event_type']) AS total_events
FROM
  UNPACK((
    SELECT events['event_type']
    FROM sensor_data(with_history => TRUE, after => TIMESTAMP('2026-01-01T00:00:00Z'))
  ))
GROUP BY
  Device_id;

סריקות בטווח רחב

השאילתה הבאה מראה איך לאחזר את כל העמודות עבור טווח גדול של מפתחות שורות. השימוש ב-Data Boost מבטיח שהבקשה הזו עם התפוקה הגבוהה לא תנצל משאבי CPU בצמתי האשכול.

SELECT *
FROM analytics
WHERE _key >= 'user#000000' AND _key < 'user#999999'

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