שימוש ב-BigQuery עם Python

1. סקירה כללית

‫BigQuery הוא מחסן נתונים לצורכי ניתוח מנוהל במלואו של Google, בקנה מידה של פטה-בייט ובעלות נמוכה. ‫BigQuery הוא NoOps – אין תשתית לניהול ולא צריך מנהל מסד נתונים – כך שאתם יכולים להתמקד בניתוח נתונים כדי למצוא תובנות משמעותיות, להשתמש ב-SQL מוכר ולנצל את היתרונות של המודל שלנו של תשלום לפי שימוש.

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

מה תלמדו

• איך משתמשים ב-Cloud Shell?
• איך מפעילים את BigQuery API
• איך מאמתים בקשות API
• איך מתקינים את ספריית הלקוח של Python
• איך שואלים שאילתות לגבי יצירות של שייקספיר
• איך שולחים שאילתות לקבוצת הנתונים של GitHub
• איך משנים את הגדרות השמירה במטמון ומציגים נתונים סטטיסטיים

מה תצטרכו

• פרויקט ב-Google Cloud
• דפדפן, כמו Chrome או Firefox
• היכרות עם השימוש ב-Python

סקר

איך תשתמשו במדריך הזה?

רק לקרוא לקרוא ולבצע את התרגילים

איך היית מדרג את חוויית השימוש שלך ב-Python?

מתחילים בינוני מומחה

איזה דירוג מתאים לדעתך לחוויית השימוש שלך בשירותי Google Cloud?

מתחילים ביניים מומחים

2. הגדרה ודרישות

הגדרת סביבה בקצב אישי

1. נכנסים ל-מסוף Google Cloud ויוצרים פרויקט חדש או משתמשים בפרויקט קיים. אם עדיין אין לכם חשבון Gmail או Google Workspace, אתם צריכים ליצור חשבון.

• שם הפרויקט הוא השם המוצג למשתתפים בפרויקט. זו מחרוזת של תווים שלא נמצאת בשימוש ב-Google APIs, ואפשר לעדכן אותה בכל שלב.
• מזהה הפרויקט חייב להיות ייחודי לכל הפרויקטים ב-Google Cloud, והוא קבוע (אי אפשר לשנות אותו אחרי שהוא מוגדר). מסוף Cloud יוצר באופן אוטומטי מחרוזת ייחודית. בדרך כלל לא צריך לדעת מה היא. ברוב סדנאות ה-Codelab, תצטרכו להפנות למזהה הפרויקט (ובדרך כלל הוא מזוהה כ-PROJECT_ID), אז אם אתם לא אוהבים אותו, תוכלו ליצור מזהה אקראי אחר, או לנסות ליצור מזהה משלכם ולראות אם הוא זמין. אחרי שהפרויקט נוצר, הוא 'קפוא'.
• יש ערך שלישי, מספר פרויקט, שמשמש חלק מממשקי ה-API. מידע נוסף על שלושת הערכים האלה מופיע במאמרי העזרה.

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

הערה: אם אתם משתמשים בחשבון Gmail, אתם יכולים להשאיר את מיקום ברירת המחדל כללא ארגון. אם אתם משתמשים בחשבון Google Workspace, אתם צריכים לבחור מיקום שמתאים לארגון שלכם.

2. בשלב הבא, תצטרכו להפעיל את החיוב במסוף Cloud כדי להשתמש במשאבי Cloud או בממשקי API. העלות של התרגול הזה לא אמורה להיות גבוהה, ואולי לא תהיה עלות בכלל. כדי לכבות את המשאבים ולא לחייב אתכם מעבר למה שמוסבר במדריך הזה, צריך לפעול לפי ההוראות לניקוי שמופיעות בסוף ה-Codelab. משתמשים חדשים ב-Google Cloud זכאים לתוכנית תקופת ניסיון בחינם בשווי 300$.

מפעילים את Cloud Shell

אפשר להפעיל את Google Cloud מרחוק מהמחשב הנייד, אבל ב-Codelab הזה נשתמש ב-Google Cloud Shell, סביבת שורת פקודה שפועלת בענן.

הפעלת Cloud Shell

1. ב-Cloud Console, לוחצים על Activate Cloud Shell .

אם זו הפעם הראשונה שאתם מפעילים את Cloud Shell, יוצג לכם מסך ביניים (מתחת לקו הקיפול) עם תיאור של הכלי. במקרה כזה, לוחצים על המשך (והמסך הזה לא יוצג לכם יותר). כך נראה המסך החד-פעמי:

יחלפו כמה רגעים עד שההקצאה והחיבור ל-Cloud Shell יושלמו.

המכונה הווירטואלית הזו כוללת את כל הכלים שדרושים למפתחים. יש בה ספריית בית בנפח מתמיד של 5GB והיא פועלת ב-Google Cloud, מה שמשפר מאוד את הביצועים והאימות של הרשת. אפשר לבצע את רוב העבודה ב-codelab הזה, אם לא את כולה, באמצעות דפדפן או Chromebook.

אחרי שמתחברים ל-Cloud Shell, אפשר לראות שהאימות כבר בוצע והפרויקט כבר הוגדר לפי מזהה הפרויקט.

2. מריצים את הפקודה הבאה ב-Cloud Shell כדי לוודא שאתם מאומתים:

gcloud auth list

פלט הפקודה

 Credentialed Accounts
ACTIVE  ACCOUNT
*       <my_account>@<my_domain.com>

To set the active account, run:
    $ gcloud config set account `ACCOUNT`

הערה: כלי שורת הפקודה gcloud הוא כלי שורת הפקודה המאוחד והעוצמתי ב-Google Cloud. הוא מותקן מראש ב-Cloud Shell. תשימו לב שהוא תומך בהשלמה אוטומטית של כרטיסיות. למידע נוסף, ראו סקירה כללית של כלי שורת הפקודה של Google Cloud.

3. מריצים את הפקודה הבאה ב-Cloud Shell כדי לוודא שפקודת gcloud מכירה את הפרויקט:

gcloud config list project

פלט הפקודה

[core]
project = <PROJECT_ID>

אם לא, אפשר להגדיר אותו באמצעות הפקודה הזו:

gcloud config set project <PROJECT_ID>

פלט הפקודה

Updated property [core/project].

3. הפעלת ה-API

‫BigQuery API אמור להיות מופעל כברירת מחדל בכל פרויקט ב-Google Cloud. כדי לבדוק אם זה נכון, מריצים את הפקודה הבאה ב-Cloud Shell: אתם אמורים להופיע ברשימה של BigQuery:

gcloud services list

האפשרות BigQuery אמורה להופיע ברשימה:

NAME                              TITLE
bigquery.googleapis.com           BigQuery API

...

אם BigQuery API לא מופעל, אפשר להשתמש בפקודה הבאה ב-Cloud Shell כדי להפעיל אותו:

gcloud services enable bigquery.googleapis.com

הערה: אם מתרחשת שגיאה, צריך לחזור לשלב הקודם ולבדוק את ההגדרה.

4. אימות בקשות API

כדי לשלוח בקשות ל-BigQuery API, צריך להשתמש בחשבון שירות. חשבון שירות שייך לפרויקט שלכם, וספריית הלקוח של Google Cloud Python משתמשת בו כדי לשלוח בקשות ל-BigQuery API. בדומה לכל חשבון משתמש אחר, חשבון שירות מיוצג על ידי כתובת אימייל. בקטע הזה תשתמשו ב-Cloud SDK כדי ליצור חשבון שירות, ואז תיצרו פרטי כניסה שיידרשו לכם כדי לבצע אימות בתור חשבון השירות.

קודם כל, מגדירים את משתנה הסביבה PROJECT_ID:

export PROJECT_ID=$(gcloud config get-value core/project)

בשלב הבא, יוצרים חשבון שירות חדש כדי לגשת ל-BigQuery API באמצעות הפקודה:

gcloud iam service-accounts create my-bigquery-sa \
  --display-name "my bigquery service account"

בשלב הבא, יוצרים פרטי כניסה שקוד Python ישתמש בהם כדי להתחבר בתור חשבון השירות החדש. יוצרים את פרטי הכניסה ושומרים אותם כקובץ JSON‏ ~/key.json באמצעות הפקודה הבאה:

gcloud iam service-accounts keys create ~/key.json \
  --iam-account my-bigquery-sa@${PROJECT_ID}.iam.gserviceaccount.com

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

export GOOGLE_APPLICATION_CREDENTIALS=~/key.json

מידע נוסף על אימות BigQuery API

5. הגדרת בקרת גישה

ב-BigQuery נעשה שימוש בניהול זהויות והרשאות גישה (IAM) כדי לנהל את הגישה למשאבים. ל-BigQuery יש מספר תפקידים מוגדרים מראש (משתמש, בעל נתונים, צופה בנתונים וכו') שאפשר להקצות לחשבון השירות שיצרתם בשלב הקודם. מידע נוסף על בקרת גישה זמין במסמכי BigQuery.

כדי לשלוח שאילתות למערכי נתונים ציבוריים, צריך לוודא שלחשבון השירות יש לפחות את התפקיד roles/bigquery.user. ב-Cloud Shell, מריצים את הפקודה הבאה כדי להקצות את תפקיד המשתמש לחשבון השירות:

gcloud projects add-iam-policy-binding ${PROJECT_ID} \
  --member "serviceAccount:my-bigquery-sa@${PROJECT_ID}.iam.gserviceaccount.com" \
  --role "roles/bigquery.user"

כדי לוודא שלחשבון השירות יש את תפקיד המשתמש, מריצים את הפקודה הבאה:

gcloud projects get-iam-policy $PROJECT_ID

אתם אמורים לראות את הנתונים הבאים:

bindings:
- members:
  - serviceAccount:my-bigquery-sa@<PROJECT_ID>.iam.gserviceaccount.com
  role: roles/bigquery.user
...

6. התקנת ספריית הלקוח

מתקינים את ספריית הלקוח של BigQuery Python:

pip3 install --user --upgrade google-cloud-bigquery

עכשיו אתם מוכנים לכתוב קוד באמצעות BigQuery API.

7. שליחת שאילתות לגבי יצירות של שייקספיר

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

בנוסף למערכי נתונים ציבוריים, BigQuery מספק מספר מוגבל של טבלאות לדוגמה שאפשר להריץ עליהן שאילתות. הטבלאות האלה נמצאות במערך הנתונים bigquery-public-data:samples. הטבלה shakespeare במערך הנתונים samples מכילה אינדקס מילים של יצירות שייקספיר. הוא מציג את מספר הפעמים שכל מילה מופיעה בכל מאגר.

בשלב הזה תריצו שאילתה על הטבלה shakespeare.

הערה: אפשר לראות את הפרטים של טבלת shakespeare במסוף BigQuery כאן.

קודם, ב-Cloud Shell, יוצרים אפליקציית Python פשוטה שבה ישתמשו כדי להריץ את דוגמאות הקוד של Translation API.

mkdir bigquery-demo
cd bigquery-demo
touch app.py

פותחים את עורך הקוד בפינה השמאלית העליונה של Cloud Shell:

עוברים לקובץ app.py בתיקייה bigquery-demo ומחליפים את הקוד בקוד הבא.

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT corpus AS title, COUNT(word) AS unique_words
    FROM `bigquery-public-data.samples.shakespeare`
    GROUP BY title
    ORDER BY unique_words
    DESC LIMIT 10
"""
results = client.query(query)

for row in results:
    title = row['title']
    unique_words = row['unique_words']
    print(f'{title:<20} | {unique_words}')

כדאי להקדיש דקה או שתיים כדי לבדוק את הקוד ולראות איך מתבצעת השאילתה בטבלה.

חוזרים ל-Cloud Shell ומריצים את האפליקציה:

python3 app.py

תוצג רשימה של מילים והמופעים שלהן:

hamlet               | 5318
kinghenryv           | 5104
cymbeline            | 4875
troilusandcressida   | 4795
kinglear             | 4784
kingrichardiii       | 4713
2kinghenryvi         | 4683
coriolanus           | 4653
2kinghenryiv         | 4605
antonyandcleopatra   | 4582

הערה: אם מופיעה השגיאה PermissionDenied (403), צריך לוודא שפעלתם לפי השלבים של אימות בקשות API.

• בודקים שמשתנה הסביבה של פרטי הכניסה מוגדר: echo $GOOGLE_APPLICATION_CREDENTIALS
• אמור להופיע הנתיב המלא לקובץ של פרטי הכניסה: /home/$USER/key.json
• לאחר מכן, בודקים שהאישורים נוצרו: cat $GOOGLE_APPLICATION_CREDENTIALS
• הפלט אמור להיראות כך: {"type": "service_account","project_id": "PROJECT_ID",...}

אם יש טעויות, צריך לחזור לשלב אימות בקשות API.

8. הרצת שאילתות במערך הנתונים של GitHub

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

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

פתיחת הטבלה github_repos

לוחצים על לחצן התצוגה המקדימה כדי לראות איך הנתונים נראים:

עוברים לקובץ app.py בתיקייה bigquery_demo ומחליפים את הקוד בקוד הבא.

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT subject AS subject, COUNT(*) AS num_duplicates
    FROM bigquery-public-data.github_repos.commits
    GROUP BY subject
    ORDER BY num_duplicates
    DESC LIMIT 10
"""
results = client.query(query)

for row in results:
    subject = row['subject']
    num_duplicates = row['num_duplicates']
    print(f'{subject:<20} | {num_duplicates:>9,}')

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

חוזרים ל-Cloud Shell ומריצים את האפליקציה:

python3 app.py

אמורה להופיע רשימה של הודעות על ביצוע שינויים (commit) והמופעים שלהן:

Update README.md     | 1,685,515
Initial commit       | 1,577,543
update               |   211,017
                     |   155,280
Create README.md     |   153,711
Add files via upload |   152,354
initial commit       |   145,224
first commit         |   110,314
Update index.html    |    91,893
Update README        |    88,862

הערה: מערך הנתונים הזה מתעדכן מעת לעת, לכן התוצאות עשויות להשתנות.

9. שמירה במטמון ונתונים סטטיסטיים

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

בשלב הזה, תשביתו את השמירה במטמון ותציגו נתונים סטטיסטיים על השאילתות.

עוברים לקובץ app.py בתיקייה bigquery_demo ומחליפים את הקוד בקוד הבא.

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT subject AS subject, COUNT(*) AS num_duplicates
    FROM bigquery-public-data.github_repos.commits
    GROUP BY subject
    ORDER BY num_duplicates
    DESC LIMIT 10
"""
job_config = bigquery.job.QueryJobConfig(use_query_cache=False)
results = client.query(query, job_config=job_config)

for row in results:
    subject = row['subject']
    num_duplicates = row['num_duplicates']
    print(f'{subject:<20} | {num_duplicates:>9,}')

print('-'*60)
print(f'Created: {results.created}')
print(f'Ended:   {results.ended}')
print(f'Bytes:   {results.total_bytes_processed:,}')

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

חוזרים ל-Cloud Shell ומריצים את האפליקציה:

python3 app.py

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

Update README.md     | 1,685,515
Initial commit       | 1,577,543
update               |   211,017
                     |   155,280
Create README.md     |   153,711
Add files via upload |   152,354
initial commit       |   145,224
first commit         |   110,314
Update index.html    |    91,893
Update README        |    88,862
------------------------------------------------------------
Created: 2020-04-03 13:30:08.801000+00:00
Ended:   2020-04-03 13:30:15.334000+00:00
Bytes:   2,868,251,894

10. טעינת נתונים ל-BigQuery

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

בשלב הזה, תטענו קובץ JSON שמאוחסן ב-Cloud Storage לטבלה ב-BigQuery. קובץ ה-JSON נמצא במיקום gs://cloud-samples-data/bigquery/us-states/us-states.json

אם אתם רוצים לדעת מה התוכן של קובץ ה-JSON, אתם יכולים להשתמש בכלי gsutil של שורת הפקודה כדי להוריד אותו ב-Cloud Shell:

gsutil cp gs://cloud-samples-data/bigquery/us-states/us-states.json .

אפשר לראות שהוא מכיל את רשימת המדינות בארה"ב, וכל מדינה היא מסמך JSON בשורה נפרדת:

head us-states.json

{"name": "Alabama", "post_abbr": "AL"}
{"name": "Alaska", "post_abbr":  "AK"}
...

כדי לטעון את קובץ ה-JSON הזה ל-BigQuery, עוברים לקובץ app.py בתיקייה bigquery_demo ומחליפים את הקוד בקוד הבא.

from google.cloud import bigquery

client = bigquery.Client()

gcs_uri = 'gs://cloud-samples-data/bigquery/us-states/us-states.json'

dataset = client.create_dataset('us_states_dataset')
table = dataset.table('us_states_table')

job_config = bigquery.job.LoadJobConfig()
job_config.schema = [
    bigquery.SchemaField('name', 'STRING'),
    bigquery.SchemaField('post_abbr', 'STRING'),
]
job_config.source_format = bigquery.SourceFormat.NEWLINE_DELIMITED_JSON

load_job = client.load_table_from_uri(gcs_uri, table, job_config=job_config)

print('JSON file loaded to BigQuery')

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

חוזרים ל-Cloud Shell ומריצים את האפליקציה:

python3 app.py

מערך נתונים וטבלה נוצרים ב-BigQuery.

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

‫11. מעולה!

למדתם איך להשתמש ב-BigQuery עם Python.

פינוי נפח

כדי להימנע מחיובים בחשבון Google Cloud בגלל השימוש במשאבים שנעשה במסגרת מדריך זה:

• במסוף Cloud, נכנסים לדף Manage resources.
• ברשימת הפרויקטים, בוחרים את הפרויקט ולוחצים על מחיקה.
• כדי למחוק את הפרויקט, כותבים את מזהה הפרויקט בתיבת הדו-שיח ולוחצים על Shut down.

מידע נוסף

• ‫Google BigQuery: https://cloud.google.com/bigquery/docs/
• ‫Python ב-Google Cloud: https://cloud.google.com/python/
• ספריות לקוח של Cloud ל-Python: ‏ https://googleapis.github.io/google-cloud-python/

רישיון

העבודה הזו בשימוש במסגרת רישיון Creative Commons שמותנה בייחוס כללי מגרסה 2.0.