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

בדף הזה מוסבר איך לשלוח שאילתות לנתונים שמאוחסנים ב-BigQuery מתוך ממשק JupyterLab של מופע Gemini Enterprise Agent Platform Workbench.

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

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

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

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

לפני שמתחילים

אם עוד לא עשיתם זאת, צרו מופע של Agent Platform Workbench.

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

כדי לוודא שלחשבון השירות של המופע יש את ההרשאות שנדרשות לשליחת שאילתות לנתונים ב-BigQuery, צריך לבקש מהאדמין להקצות לחשבון השירות של המופע את תפקיד ה-IAM ‏Service Usage Consumer ‏ (roles/serviceusage.serviceUsageConsumer) בפרויקט.

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

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

פתיחת JupyterLab

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

    כניסה לדף Instances

  2. לצד השם של מכונת Agent Platform Workbench, לוחצים על Open JupyterLab.

    מופע של Agent Platform Workbench ייפתח ב-JupyterLab.

עיון במשאבי BigQuery

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

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

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

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

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

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

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

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

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

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

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

  2. בתיבת הדו-שיח Select Kernel, בוחרים באפשרות Python 3 ולוחצים על 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 זמין במסמכי התיעוד של client library magics.

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

    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. לוחצים על  הפעלת התא.

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

       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 ב-Agent Platform Workbench

השילוב עם 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, מריץ את השאילתה בתא של Notebook ומאחסן את התוצאות ב-DataFrame של pandas בשם df.

עצמאי

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

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

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

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

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

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

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

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

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

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

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

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

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