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

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

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

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

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

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

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

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

4a5983b4dc299705.png

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

a592b5b9be20fdec.png

  1. לוחצים על יצירת קבוצת נתונים כדי ליצור קבוצת נתונים ריקה.

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

הפעלת Cloud Shell

  1. ב-Cloud Console, לוחצים על 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 מתוך ערכת הנתונים של צפיות בדפים של ויקימדיה זמינים ב-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

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

742cd54fbf17085.png

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

397a9c25480735cc.png

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

  1. לוחצים על Compose new query (יצירת שאילתה חדשה):

cc28282a25c9746e.png

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

e881286d275ab4ec.png

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

9abb7c4039961f5b.png

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

a119b65f2ca49e41.png

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

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

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

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

מידע נוסף