שימוש ב-BigQuery עם Python

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

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

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

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

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

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

הפעלת Cloud Shell

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

הפעלת Cloud Shell

1. במסוף Cloud, לוחצים על 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 יש כמה תפקידים מוגדרים מראש (user, dataOwner, dataViewer וכו') שאפשר להקצות לחשבון השירות שיצרתם בשלב הקודם. מידע נוסף על בקרת גישה זמין במסמכי 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

אתם אמורים לראות רשימה של הודעות התחייבות והאירועים שלהן:

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 Attribution 2.0 גנרי.