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

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

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

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

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

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

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

אם עדיין לא עשיתם זאת, צרו מכונה של Agent Platform Workbench.

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

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

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

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

פתיחת 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.

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

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

  1. כדי לפתוח קובץ notebook, לוחצים על File > New > 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. ‫לוחצים על  Run cell.

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

    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. ‫לוחצים על  Run cell.

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

    regions_by_country.head()
    
  8. ‫לוחצים על  Run cell.

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

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

בקטע הזה תשתמשו בספריית הלקוח של BigQuery ל-Python ישירות כדי לקרוא נתונים ל-notebook של 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. ‫לוחצים על  Run cell.

  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 ואז מועבר לmethod‏ query() כדי להריץ שאילתה. השיטה to_dataframe ממתינה לסיום השאילתה ומורידה את התוצאות ל-DataFrame של pandas באמצעות 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 ב-Agent Platform Workbench

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

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

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

בתוך התא

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

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

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

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

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

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

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

עצמאי

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

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

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

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

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

איך רואים את היסטוריית השאילתות ומשתמשים בהן שוב

כדי לראות את היסטוריית השאילתות בכרטיסייה ב-JupyterLab:

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

  2. בחלונית BigQuery, גוללים למטה ולוחצים על Query history.

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

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

    • כדי לראות את פרטי השאילתה, כמו מזהה המשימה, מתי השאילתה הופעלה וכמה זמן היא נמשכה, לוחצים על השאילתה.
    • כדי לשנות את השאילתה, להריץ אותה שוב או להעתיק אותה ל-notebook לשימוש עתידי, לוחצים על Open query in editor.

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