שליחת שאילתות על נתונים ב-BigQuery מתוך JupyterLab

בדף הזה מוסבר איך להריץ שאילתות על נתונים שמאוחסנים ב-BigQuery מתוך ממשק JupyterLab של מופע מחברות מנוהלות של Vertex AI Workbench.

שיטות לשליחת שאילתות לגבי נתוני BigQuery בקובצי מחברת (IPYNB)

כדי להריץ שאילתות על נתונים ב-BigQuery מתוך קובץ מחברת JupyterLab, אפשר להשתמש בפקודה %%bigquery magic ובספריית הלקוח של BigQuery ל-Python.

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

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

פתיחת JupyterLab

  1. נכנסים לדף Managed notebooks במסוף Google Cloud .

    מעבר לתיקיות מנוהלות

  2. לצד השם של מכונת המחברות המנוהלות, לוחצים על Open JupyterLab.

    מופע המחברת המנוהלת ייפתח ב-JupyterLab.

עיון במשאבים של BigQuery

השילוב עם BigQuery מספק חלונית לעיון במשאבי BigQuery שיש לכם גישה אליהם.

  1. בתפריט הניווט של JupyterLab, לוחצים על BigQuery BigQuery in Notebooks.

    בחלונית BigQuery מופיעים הפרויקטים ומערכי הנתונים הזמינים, שבהם אפשר לבצע משימות כמו:

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

    הערה: בתיאור הסיכום של טבלה, לוחצים על הכרטיסייה תצוגה מקדימה כדי לראות תצוגה מקדימה של נתוני הטבלה. בתמונה הבאה מוצגת תצוגה מקדימה של הטבלה international_top_terms שנמצאת במערך הנתונים google_trends בפרויקט bigquery-public-data:

    רשימה בינלאומית של המונחים המובילים.

שאילתות על נתונים באמצעות פקודת הקסם ‎ %%bigquery

בקטע הזה, תכתבו SQL ישירות בתאי מחברת ותקראו נתונים מ-BigQuery לתוך מחברת Python.

פקודות Magic שמשתמשות בתו אחוז אחד או שניים (% או %%) מאפשרות לכם להשתמש בתחביר מינימלי כדי ליצור אינטראקציה עם BigQuery בתוך המחברת. ספריית הלקוח של BigQuery ל-Python מותקנת באופן אוטומטי במופע של מחברות מנוהלות. מאחורי הקלעים, פקודת ה-magic‏ %%bigquery משתמשת בספריית הלקוח של BigQuery ל-Python כדי להריץ את השאילתה שצוינה, להמיר את התוצאות ל-DataFrame של pandas, לשמור את התוצאות במשתנה (אופציונלי) ואז להציג את התוצאות.

הערה: החל מגרסה 1.26.0 של חבילת google-cloud-bigquery Python, נעשה שימוש ב-BigQuery Storage API כברירת מחדל להורדת תוצאות מפקודות ה-magic של %%bigquery.

  1. כדי לפתוח קובץ notebook, בוחרים באפשרות קובץ > חדש > Notebook (מחברת).

  2. בתיבת הדו-שיח Select Kernel, בוחרים באפשרות Python (Local) ולוחצים על Select.

    קובץ ה-IPYNB החדש ייפתח.

  3. כדי לקבל את מספר האזורים לפי מדינה במערך הנתונים international_top_terms מזינים את ההצהרה הבאה:

    %%bigquery
SELECT
  country_code,
  country_name,
  COUNT(DISTINCT region_code) AS num_regions
FROM
  `bigquery-public-data.google_trends.international_top_terms`
WHERE
  refresh_date = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)
GROUP BY
  country_code,
  country_name
ORDER BY
  num_regions DESC;

  4. לוחצים על  הרצת התא.

    הפלט אמור להיראות כך:

    Query complete after 0.07s: 100%|██████████| 4/4 [00:00<00:00, 1440.60query/s]
Downloading: 100%|██████████| 41/41 [00:02<00:00, 20.21rows/s]
country_code      country_name    num_regions
0   TR  Turkey         81
1   TH  Thailand       77
2   VN  Vietnam        63
3   JP  Japan          47
4   RO  Romania        42
5   NG  Nigeria        37
6   IN  India          36
7   ID  Indonesia      34
8   CO  Colombia       33
9   MX  Mexico         32
10  BR  Brazil         27
11  EG  Egypt          27
12  UA  Ukraine        27
13  CH  Switzerland    26
14  AR  Argentina      24
15  FR  France         22
16  SE  Sweden         21
17  HU  Hungary        20
18  IT  Italy          20
19  PT  Portugal       20
20  NO  Norway         19
21  FI  Finland        18
22  NZ  New Zealand    17
23  PH  Philippines    17
...

  5. בתא הבא (מתחת לפלט מהתא הקודם), מזינים את הפקודה הבאה כדי להריץ את אותה שאילתה, אבל הפעם שומרים את התוצאות ב-DataFrame חדש של pandas בשם regions_by_country. מזינים את השם הזה באמצעות ארגומנט עם פקודת הקסם %%bigquery.

    %%bigquery regions_by_country
SELECT
  country_code,
  country_name,
  COUNT(DISTINCT region_code) AS num_regions
FROM
  `bigquery-public-data.google_trends.international_top_terms`
WHERE
  refresh_date = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)
GROUP BY
  country_code, country_name
ORDER BY
  num_regions DESC;

    הערה: מידע נוסף על הארגומנטים הזמינים לפקודה %%bigquery זמין במאמרי העזרה בנושא פקודות קסם בספריית הלקוח.

  6. לוחצים על  הרצת התא.

  7. בתא הבא, מזינים את הפקודה הבאה כדי לראות את השורות הראשונות של תוצאות השאילתה שקראתם:

    regions_by_country.head()

  8. לוחצים על  הרצת התא.

    ה-DataFrame של pandas‏, regions_by_country, מוכן לשרטוט.

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

בקטע הזה תשתמשו בספריית הלקוח של BigQuery ל-Python ישירות כדי לקרוא נתונים למחברת Python.

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

הערה: אפשר להשתמש במספר ספריות של Python לניתוח נתונים, ל-data wrangling ולהמחשה חזותית, כמו numpy,‏ pandas,‏ matplotlib ועוד רבות אחרות. חלק מהספריות האלה מבוססות על אובייקט DataFrame.

  1. בתא הבא, מזינים את קוד Python הבא כדי לייבא את ספריית הלקוח BigQuery ל-Python ולאתחל לקוח:

    from google.cloud import bigquery

client = bigquery.Client()

    לקוח BigQuery משמש לשליחה ולקבלה של הודעות מ-BigQuery API.

  2. לוחצים על  הרצת התא.

  3. בתא הבא, מזינים את הקוד הבא כדי לאחזר את אחוז המונחים המובילים היומיים בארה"ב top_terms שחופפים לאורך זמן לפי מספר הימים שעברו. הרעיון הוא לבדוק את המונחים המובילים בכל יום ולראות איזה אחוז מהם חופף למונחים המובילים מהיום הקודם, מהיום שלפני יומיים, מהיום שלפני 3 ימים וכן הלאה (לכל זוג תאריכים בטווח של חודש בערך).

    sql = """
WITH
  TopTermsByDate AS (
    SELECT DISTINCT refresh_date AS date, term
    FROM `bigquery-public-data.google_trends.top_terms`
  ),
  DistinctDates AS (
    SELECT DISTINCT date
    FROM TopTermsByDate
  )
SELECT
  DATE_DIFF(Dates2.date, Date1Terms.date, DAY)
    AS days_apart,
  COUNT(DISTINCT (Dates2.date || Date1Terms.date))
    AS num_date_pairs,
  COUNT(Date1Terms.term) AS num_date1_terms,
  SUM(IF(Date2Terms.term IS NOT NULL, 1, 0))
    AS overlap_terms,
  SAFE_DIVIDE(
    SUM(IF(Date2Terms.term IS NOT NULL, 1, 0)),
    COUNT(Date1Terms.term)
    ) AS pct_overlap_terms
FROM
  TopTermsByDate AS Date1Terms
CROSS JOIN
  DistinctDates AS Dates2
LEFT JOIN
  TopTermsByDate AS Date2Terms
  ON
    Dates2.date = Date2Terms.date
    AND Date1Terms.term = Date2Terms.term
WHERE
  Date1Terms.date <= Dates2.date
GROUP BY
  days_apart

ORDER BY
  days_apart;
"""
pct_overlap_terms_by_days_apart = client.query(sql).to_dataframe()

pct_overlap_terms_by_days_apart.head()

    ה-SQL שבו נעשה שימוש מוצפן במחרוזת Python ואז מועבר אל השיטה query() כדי להריץ שאילתה. השיטה to_dataframe מחכה שהשאילתה תסתיים ומורידה את התוצאות ל-pandas DataFrame באמצעות BigQuery Storage API.

  4. לוחצים על  Run cell.

    השורה הראשונה של תוצאות השאילתה מופיעה מתחת לתא הקוד.

       days_apart   num_date_pairs  num_date1_terms overlap_terms   pct_overlap_terms
 0          0             32               800            800            1.000000
 1          1             31               775            203            0.261935
 2          2             30               750             73            0.097333
 3          3             29               725             31            0.042759
 4          4             28               700             23            0.032857

מידע נוסף על שימוש בספריות לקוח של BigQuery זמין במאמר שימוש בספריות לקוח.

הרצת שאילתות על נתונים באמצעות השילוב של BigQuery במחברות מנוהלות

השילוב עם BigQuery מספק שתי שיטות נוספות להרצת שאילתות על נתונים. השיטות האלה שונות מהשימוש בפקודת הקסם %%bigquery.

  • עורך השאילתות בתוך התא הוא סוג של תא שאפשר להשתמש בו בקובצי המחברת.

  • עורך השאילתות העצמאי נפתח ככרטיסייה נפרדת ב-JupyterLab.

מודעות בגוף התוכן

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

  1. ב-JupyterLab, פותחים קובץ Notebook ‏ (IPYNB) או יוצרים קובץ חדש.

  2. כדי ליצור עורך שאילתות בתוך תא, לוחצים על התא ואז על הלחצן  שילוב עם BigQuery משמאל לתא. או בתא Markdown, מזינים #@BigQuery.

    השילוב עם BigQuery ממיר את התא לתוך עורך שאילתות בתוך התא.

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

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

  5. אפשר ללחוץ על Query and load as DataFrame (שאילתה וטעינה כ-DataFrame) כדי להוסיף באופן אוטומטי תא חדש שמכיל קטע קוד שמייבא את ספריית הלקוח של BigQuery ל-Python, מריץ את השאילתה בתא של מחברת ומאחסן את התוצאות ב-DataFrame של pandas בשם df.

עצמאי

כדי להשתמש בעורך השאילתות העצמאי כדי ליצור שאילתות לנתונים בטבלה ב-BigQuery, מבצעים את השלבים הבאים:

  1. ב-JupyterLab, בחלונית BigQuery in Notebooks, לוחצים לחיצה ימנית על טבלה ובוחרים באפשרות Query table, או לוחצים לחיצה כפולה על טבלה כדי לפתוח תיאור בכרטיסייה נפרדת, ואז לוחצים על הקישור Query table.

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

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

  4. אפשר ללחוץ על העתקת הקוד ל-DataFrame כדי להעתיק קטע קוד שמייבא את ספריית הלקוח של BigQuery ל-Python, מריץ את השאילתה בתא של מחברת ומאחסן את התוצאות ב-DataFrame של pandas בשם df. מדביקים את הקוד בתא של מחברת שרוצים להפעיל פתרונות חכמים.

צפייה בהיסטוריית השאילתות ושימוש חוזר בשאילתות

כדי לראות את היסטוריית השאילתות ככרטיסייה ב-JupyterLab, מבצעים את הפעולות הבאות:

  1. בתפריט הניווט של JupyterLab, לוחצים על BigQuery BigQuery in Notebooks כדי לפתוח את החלונית BigQuery.

  2. בחלונית BigQuery, גוללים למטה ולוחצים על Query history (היסטוריית שאילתות).

    היסטוריית השאילתות מודגשת בחלק התחתון של סרגל הניווט הימני

    רשימת השאילתות תיפתח בכרטיסייה חדשה, שבה תוכלו לבצע משימות כמו:

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

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