הרצת שאילתות במערך הנתונים של Wikipedia ב-BigQuery

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

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

ב-Codelab הזה, חוקרים את מערך הנתונים של Wikipedia באמצעות BigQuery.

מה תלמדו

  • איך משתמשים ב-BigQuery
  • איך לטעון מערך נתונים מהעולם האמיתי ל-BigQuery
  • איך לכתוב שאילתה כדי לקבל תובנות לגבי מערך נתונים גדול

למה תזדקק?

סקר

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

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

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

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

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

הפעלת BigQuery

אם עדיין אין לכם חשבון Google, עליכם ליצור חשבון.

  1. נכנסים אל מסוף Google Cloud ועוברים אל BigQuery. אפשר גם לפתוח ישירות את ממשק המשתמש של BigQuery באינטרנט על ידי הזנת כתובת ה-URL הבאה בדפדפן.
https://console.cloud.google.com/bigquery
  1. מאשרים את התנאים וההגבלות.
  2. כדי להשתמש ב-BigQuery צריך ליצור פרויקט. יוצרים את הפרויקט החדש לפי ההנחיות.

בוחרים שם לפרויקט ורושמים את מזהה הפרויקט. 1884405a64ce5765.png

מזהה הפרויקט הוא שם ייחודי בכל הפרויקטים ב-Google Cloud. בהמשך ב-Codelab הזה, היא תיקרא PROJECT_ID.

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

את מערך הנתונים של Wikipedia טוענים בקטע הבא.

3. יצירת מערך נתונים

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

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

4a5983b4dc299705.png

  1. מזינים את lab בתור מזהה מערך הנתונים:

a592b5b9be20fdec.png

  1. לוחצים על Create dataset כדי ליצור מערך נתונים ריק.

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

הפעלת Cloud Shell

  1. במסוף Cloud, לוחצים על Activate Cloud Shell 4292cbf4971c9786.png.

bce75f34b2c53987.png

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

70f315d7b402b476.png

ההקצאה וההתחברות ל-Cloud Shell נמשכת כמה דקות.

fbe3a0674c982259.png

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

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

  1. מריצים את הפקודה הבאה ב-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`
  1. מריצים את הפקודה הבאה ב-Cloud Shell כדי לוודא שהפקודה ב-gcloud יודעת על הפרויקט שלכם:
gcloud config list project

פלט הפקודה

[core]
project = <PROJECT_ID>

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

gcloud config set project <PROJECT_ID>

פלט הפקודה

Updated property [core/project].

טעינת נתונים לתוך BigQuery

לנוחיותך, חלק מהנתונים מ-10 באפריל 2019 ממערך הנתונים של צפיות בדף ב-Wikimedia זמינים ב-Google Cloud Storage בכתובת gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz. קובץ הנתונים הוא קובץ CSV עם קידוד GZip. אפשר לטעון את הקובץ הזה ישירות באמצעות כלי שורת הפקודה bq. כחלק מפקודת הטעינה, צריך גם לתאר את הסכימה של הקובץ.

bq load \
  --source_format CSV \
  --field_delimiter " " \
  --allow_jagged_rows \
  --quote "" \
  --max_bad_records 3 \
  $GOOGLE_CLOUD_PROJECT:lab.pageviews_20190410_140000 \
  gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz \
  wiki,title,requests:integer,zero:integer

השתמשת בכמה אפשרויות מתקדמות כדי לטעון את הקובץ של צפיות בדפים:

  • צריך להגדיר את --source_format CSV כדי לציין שצריך לנתח את הקובץ כקובץ CSV. השלב הזה הוא אופציונלי כי CSV הוא פורמט ברירת המחדל.
  • צריך להגדיר את הערך --field_delimiter " " כדי לציין שרווח יחיד משמש להפרדה בין שדות.
  • צריך להגדיר את --allow_jagged_rows כך שיכלול את השורות עם מספר קטן יותר של עמודות, ומתעלמים מהשגיאות בזמן טעינת קובץ ה-CSV.
  • כדי לציין שהמחרוזות לא מוקפות במחרוזות, צריך להגדיר את --quote "".
  • אפשר להגדיר את --max_bad_records 3 כך להתעלם מ-3 שגיאות לכל היותר במהלך ניתוח קובץ ה-CSV.

מידע נוסף על שורת הפקודה bq זמין במסמכי התיעוד.

5. תצוגה מקדימה של מערך הנתונים

במסוף BigQuery, פותחים את אחת מהטבלאות שטענתם.

  1. מרחיבים את הפרויקט.
  2. מרחיבים את מערך הנתונים.
  3. בוחרים את הטבלה. 99f875c838ed9a58.png

אפשר לראות את סכימת הטבלה בכרטיסייה Schema. 4. כדי לראות כמה נתונים יש בטבלה, עוברים לכרטיסייה פרטים:

742cd54fbf17085.png

  1. פותחים את הכרטיסייה תצוגה מקדימה כדי להציג מבחר שורות מהטבלה.

397a9c25480735cc.png

6. כתיבת שאילתה

  1. לוחצים על כתיבת שאילתה חדשה:

cc28282a25c9746e.png

פעולה זו מציגה את עורך השאילתות:

e881286d275ab4ec.png

  1. כדי למצוא את המספר הכולל של צפיות ב-Wikimedia בין השעות 14:00 ו-15:00 ב-10 באפריל 2019, אפשר לכתוב את השאילתה הבאה:
SELECT SUM(requests)
FROM `lab.pageviews_20190410_140000`
  1. לוחצים על Run:

9abb7c4039961f5b.png

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

a119b65f2ca49e41.png

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

7. שאילתות מתקדמות יותר

איך מוצאים צפיות בדפים בוויקיפדיה

מערך הנתונים של Wikimedia מכיל צפיות בדפים של כל הפרויקטים של Wikimedia (כולל ויקיפדיה, Wiktionary, Wikibooks ווויקיפדיה). צמצום השאילתה לדפי ויקיפדיה באנגלית בלבד על ידי הוספת הצהרת WHERE:

SELECT SUM(requests), wiki
FROM `lab.pageviews_20190410_140000`
WHERE wiki = "en"
GROUP BY wiki

d6c6c7901c314da7.png

שימו לב: שליחת שאילתות לגבי עמודה נוספת, wiki, עלתה מ-124MB ל-204MB.

BigQuery תומך ברבים מסעיפי ה-SQL המוכרים, כמו CONTAINS, GROUP BY, ORDER BY ומספר פונקציות צבירה. בנוסף, ניתן להשתמש בביטויים רגולריים כדי להריץ שאילתות על שדות טקסט! כדאי לנסות אחת מהאפשרויות:

SELECT title, SUM(requests) requests
FROM `lab.pageviews_20190410_140000`
WHERE
  wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

שאילתות במספר טבלאות

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

  1. קודם כל, יוצרים טבלה שנייה לצורך שליחת שאילתות על ידי טעינת הצפיות בדפים מהשעה הבאה בטבלה חדשה:
bq load \
  --source_format CSV \
  --field_delimiter " " \
  --quote "" \
  $GOOGLE_CLOUD_PROJECT:lab.pageviews_20190410_150000 \
  gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-150000.gz \
  wiki,title,requests:integer,zero:integer
  1. בעורך השאילתות, שולחים שאילתה על שתי הטבלאות שטענתם על ידי שליחת שאילתות על טבלאות עם "pageviews_2019" כקידומת:
SELECT title, SUM(requests) requests
FROM `lab.pageviews_2019*`
WHERE
  wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

אפשר לסנן את הטבלאות בצורה סלקטיבית יותר באמצעות העמודה המדומה _TABLE_יפה. השאילתה הזו מוגבלת לטבלאות המתאימות ל-10 באפריל.

SELECT title, SUM(requests) requests
FROM `lab.pageviews_2019*`
WHERE
  _TABLE_SUFFIX BETWEEN '0410' AND '0410_9999999'
  AND wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

8. מנקה

אפשר גם למחוק את מערך הנתונים שיצרתם באמצעות הפקודה bq rm. משתמשים בדגל -r כדי להסיר את הטבלאות שהוא מכיל.

bq rm -r lab

9. מעולה!

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

מידע נוסף