טעינת נתונים ושליחת שאילתות לגבי כלי שורת הפקודה של bq ל-BigQuery

1. מבוא

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

מה תלמדו

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

מה תצטרכו

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.

Cloud Shell

תשתמשו ב-Cloud Shell, סביבת שורת פקודה שפועלת ב-Google Cloud.

הפעלת 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].

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

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

מהו מערך נתונים?

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

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

ב-Cloud Shell, משתמשים בפקודה bq mk כדי ליצור קבוצת נתונים בשם bq_load_codelab.

bq mk bq_load_codelab

איך רואים את המאפיינים של מערך נתונים

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

bq show bq_load_codelab

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

Dataset my-project:bq_load_codelab

   Last modified           ACLs          Labels
 ----------------- -------------------- --------
  15 Jun 14:12:49   Owners:
                      projectOwners,
                      your-email@example.com
                    Writers:
                      projectWriters
                    Readers:
                      projectReaders

4. יצירת קובץ הנתונים

אפשר לטעון נתונים ל-BigQuery מכמה פורמטים של נתונים, כולל JSON עם תווי שורה להפרדה, Avro ו-CSV. לצורך פשטות, נשתמש ב-CSV.

יצירת קובץ CSV

ב-Cloud Shell, יוצרים קובץ CSV ריק.

touch customer_transactions.csv

פותחים את קובץ ה-CSV בעורך קוד ב-Cloud Shell על ידי הפעלת הפקודה cloudshell edit. הפקודה הזו תפתח חלון דפדפן חדש עם עורך קוד ועם חלונית Cloud Shell.

cloudshell edit customer_transactions.csv

בעורך הקוד, מזינים ערכים מופרדים בפסיקים לטעינה ב-BigQuery.

ID,Zipcode,Timestamp,Amount,Feedback,SKU
c123,78757,2018-02-14 17:01:39Z,1.20,4.7,he4rt5
c456,10012,2018-03-14 15:09:26Z,53.60,3.1,ppiieee
c123,78741,2018-04-01 05:59:47Z,5.98,2.0,ch0c0

שומרים את קובץ ה-CSV בלחיצה על קובץ > עריכה.

5. טען נתונים

משתמשים בפקודה bq load כדי לטעון את קובץ ה-CSV לטבלה ב-BigQuery.

bq load \
    --source_format=CSV \
    --skip_leading_rows=1 \
    bq_load_codelab.customer_transactions \
    ./customer_transactions.csv \
    id:string,zip:string,ttime:timestamp,amount:numeric,fdbk:float,sku:string

השתמשתם באפשרויות הבאות:

  • ‫--source_format=CSV משתמש בפורמט הנתונים CSV כשמנתח את קובץ הנתונים.
  • ‫--skip_leading_rows=1 מדלג על השורה הראשונה בקובץ ה-CSV כי זו שורת כותרת.
  • ההגדרה Bq_load_codelab.customer_transactions—the first positional argument— קובעת לאיזו טבלה יועמסו הנתונים.
  • ‫./customer_transactions.csv – הארגומנט השני תלוי המיקום – מגדיר איזה קובץ לטעון. בנוסף לקבצים מקומיים, הפקודה bq load יכולה לטעון קבצים מ-Cloud Storage עם gs://my_bucket/path/to/file URIs.
  • סכימה, שאפשר להגדיר בקובץ סכימת JSON או כרשימה מופרדת בפסיקים. (השתמשתם ברשימה מופרדת בפסיקים כדי לפשט את התהליך).

השתמשתם בסכימה הבאה בטבלה customer_transactions:

  • ‫Id:string: מזהה לקוח
  • Zip:string: מיקוד בארה"ב
  • Ttime:timestamp: התאריך והשעה שבהם התרחשה העסקה
  • ‫Amount:numeric: סכום העסקה (עמודה מספרית מאחסנת נתונים בפורמט עשרוני, וזה שימושי לערכים כספיים).
  • Fdbk:float: הדירוג מסקר משוב על העסקה
  • Sku:string: מזהה של הפריט שנרכש

איך בודקים את פרטי הטבלה

כדי לוודא שהטבלה נטענה, מציגים את המאפיינים שלה.

bq show bq_load_codelab.customer_transactions

פלט:

Table my-project:bq_load_codelab.customer_transactions

   Last modified          Schema          Total Rows   Total Bytes
 ----------------- --------------------- ------------ -------------
  15 Jun 15:13:55   |- id: string         3            159
                    |- zip: string
                    |- ttime: timestamp
                    |- amount: numeric
                    |- fdbk: float
                    |- sku: string

6. הפעלת שאילתה על הנתונים

אחרי שהנתונים נטענים, אפשר להריץ עליהם שאילתות באמצעות ממשק האינטרנט של BigQuery, הפקודה bq או ה-API. השאילתות יכולות לצרף את הנתונים שלכם לכל מערך נתונים (או למערכי נתונים, כל עוד הם נמצאים באותו מיקום) שיש לכם הרשאת קריאה לגביו.

מריצים שאילתת SQL סטנדרטי שמצטרפת למערך הנתונים שלכם עם מערך הנתונים של מספרי המיקוד בארה"ב ומסכמת את העסקאות לפי מדינה בארה"ב. משתמשים בפקודה bq query כדי להריץ את השאילתה.

bq query --nouse_legacy_sql '
SELECT SUM(c.amount) AS amount_total, z.state_code AS state_code
FROM `bq_load_codelab.customer_transactions` c
JOIN `bigquery-public-data.utility_us.zipcode_area` z
ON c.zip = z.zipcode
GROUP BY state_code
'

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

Waiting on bqjob_r26...05a15b38_1 ... (1s) Current status: DONE   
+--------------+------------+
| amount_total | state_code |
+--------------+------------+
|         53.6 | NY         |
|         7.18 | TX         |
+--------------+------------+

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

#standardSQL
SELECT
  /* Total of all transactions in the state. */
  SUM(c.amount) AS amount_total,

  /* State corresponding to the transaction's zipcode. */
  z.state_code AS state_code

/* Query the table you just constructed.
 * Note: If you omit the project from the table ID,
 *       the dataset is read from your project. */
FROM `bq_load_codelab.customer_transactions` c

/* Join the table to the zipcode public dataset. */
JOIN `bigquery-public-data.utility_us.zipcode_area` z

/* Find the state corresponding to the transaction's zipcode. */
ON c.zip = z.zipcode

/* Group over all transactions by state. */
GROUP BY state_code

7. הסרת המשאבים

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

bq rm -r bq_load_codelab

8. מעולה!

העליתם טבלה ל-BigQuery והרצתם עליה שאילתה.

מה כיסיתם

  • שימוש בכלי שורת הפקודה bq כדי לקיים אינטראקציה עם BigQuery.
  • צירוף הנתונים שלכם למערך נתונים ציבורי באמצעות שאילתה ב-BigQuery.

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

למידע נוסף על: