שליחת שאילתות על יומן שינויים של Bigtable ב-BigQuery

בדף הזה מפורטות הנחיות ודוגמאות לשאילתות שיעזרו לכם לעבד יומן שינויים של Bigtable ב-BigQuery.

הדף הזה מיועד למשתמשים שהשלימו את הפעולות הבאות:

המדריך הזה מניח שיש לכם ידע מסוים ב-BigQuery. מידע נוסף זמין במדריך למתחילים שמסביר איך לטעון נתונים ולשאול שאילתות.

פתיחת הטבלה של יומן השינויים

  1. במסוף Google Cloud , עוברים לדף BigQuery.

    כניסה ל-BigQuery

  2. בחלונית Explorer מרחיבים את הפרויקט.

  3. להרחיב את מערך הנתונים.

  4. לוחצים על הטבלה עם הסיומת: _changelog.

פורמט הטבלה

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

שאילתות בסיסיות

בדוגמאות שבקטע הזה נעשה שימוש בטבלת Bigtable למעקב אחרי מכירות בכרטיסי אשראי. בטבלה יש קבוצת עמודות אחת (cf) והעמודות הבאות:

  • מפתח שורה בפורמט credit card number#transaction timestamp
  • מוֹכר
  • סכום
  • קטגוריה
  • תאריך העסקה

שאילתה של עמודה אחת

מסננים את התוצאות לעמודה אחת ולמשפחת עמודות אחת באמצעות פסקה WHERE 

SELECT row_key, column_family, column, value, timestamp,
FROM your_dataset.your_table
WHERE
  mod_type="SET_CELL"
  AND column_family="cf"
  AND column="merchant"
LIMIT 1000

ניתוח ערכים

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

SELECT row_key, column_family, column, value, CAST(value AS NUMERIC) AS amount
FROM your_dataset.your_table
WHERE
  mod_type="SET_CELL"
  AND column_family="cf"
  AND column="amount"
LIMIT 1000

ביצוע צבירות

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

SELECT SUM(CAST(value AS NUMERIC)) as total_amount
FROM your_dataset.your_table
WHERE
  mod_type="SET_CELL"
  AND column_family="cf"
  AND column="amount"

הצגת הנתונים בטבלת צירים

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

SELECT * FROM (
  SELECT row_key, timestamp, column, value
  FROM your_dataset.your_table
)
PIVOT (
  MAX(value)
  FOR column in ("merchant", "amount", "category", "transaction_date")
)

שינוי ציר עם קבוצת עמודות דינמית

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

DECLARE cols STRING;
SET cols = (
  SELECT CONCAT('("', STRING_AGG(DISTINCT column, '", "'), '")'),
  FROM your_dataset.your_table
);

EXECUTE IMMEDIATE format("""
SELECT * FROM (
  SELECT row_key, timestamp, column, value
  FROM your_dataset.your_table
)
PIVOT (
  MAX(value)
  FOR column in %s
)""", cols);

נתוני JSON

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

SELECT
  row_key,
  JSON_VALUE(value, "$.category") as category,
  CAST(JSON_VALUE(value, "$.amount") AS NUMERIC) as amount
FROM your_dataset.your_table
LIMIT 1000

שאילתות צבירה עם JSON

אפשר לבצע שאילתות צבירה עם ערכי JSON.

SELECT
  JSON_VALUE(value, "$.category") as category,
  SUM(CAST(JSON_VALUE(value, "$.amount") AS NUMERIC)) as total_amount
FROM your_dataset.your_table
GROUP BY category

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