GSP281

סקירה כללית

SQL (ראשי תיבות של Structured Query Language) היא שפה סטנדרטית לפעולות בנתונים, שבעזרתה אפשר לשאול שאלות ולהפיק תובנות ממערכי נתונים מובְנים. משתמשים בה בדרך כלל בניהול מסדי נתונים כדי לבצע משימות כמו כתיבת רשומות של טרנזקציות במסדי נתונים רלציוניים וניתוח נתונים בקנה מידה של פטה-בייט.

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

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

מה תלמדו

בשיעור ה-Lab הזה תלמדו איך:

• להבדיל בין מסדי נתונים לבין טבלאות ופרויקטים.
• להשתמש במילות המפתח SELECT, ‏FROM ו-WHERE כדי ליצור שאילתות פשוטות.
• לזהות את החלקים השונים וההיררכיות במסוף BigQuery.
• לטעון טבלאות ומסדי נתונים ב-BigQuery.
• להריץ שאילתות פשוטות על טבלאות.
• להשתמש במילות המפתח COUNT‏, GROUP BY, ‏AS ו-ORDER BY.
• להריץ ולשרשר את הפקודות האלה כדי לחלץ נתונים משמעותיים ממערכים.
• לייצא קבוצות של נתונים לקובץ CSV ולשמור את הקובץ בקטגוריה חדשה של Cloud Storage.
• ליצור מכונה חדשה של Cloud SQL ולטעון את קובץ ה-CSV שייצאתם כטבלה חדשה.
• להריץ את השאילתות CREATE DATABASE, ‏CREATE TABLE, ‏DELETE, ‏INSERT INTO ו-UNION ב-Cloud SQL.

דרישות מוקדמות

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

זהו שיעור Lab ברמת מבוא. אנחנו יוצאים מנקודת הנחה שיש לכם מעט מאוד ניסיון ב-SQL, או שאין לכם ניסיון בכלל. מומלץ להכיר את Cloud Storage ואת Cloud Shell, אבל לא חובה. בשיעור ה-Lab הזה תלמדו את העקרונות הבסיסיים של קריאה וכתיבה של שאילתות ב-SQL, ותיישמו אותם באמצעות BigQuery ו-Cloud SQL.

לפני שתתחילו את שיעור ה-Lab הזה, כדאי שתעצרו ותחשבו מה רמת הידע והניסיון שלכם ב-SQL. השיעורים הבאים יותר מאתגרים ויאפשרו לכם להשתמש בידע שלכם בתרחישים יותר מתקדמים:

• נתוני מזג אוויר ב-BigQuery
• ניתוח נתונים של שיעור ילודה באמצעות Vertex AI ו-BigQuery

כשתהיו מוכנים, גללו מטה והשלימו את השלבים שבהמשך כדי להגדיר את סביבת שיעור ה-Lab.

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

לפני שלוחצים על הלחצן Start Lab (התחלת המעבדה)

עליכם לקרוא את ההוראות האלו. המעבדות מוגבלות בזמן ואי אפשר להשהות אותן. הטיימר מתחיל כשלוחצים על Start Lab ומראה את משך הזמן שבו תוכלו להשתמש במשאבים ב-Google Cloud.

במעבדה המעשית הזו של Qwiklabs, תוכלו לבצע את פעילויות המעבדה בעצמכם בסביבת ענן אמיתית, ולא בהדמיה או בסביבה להדגמה. לשם כך, יינתנו לכם פרטי כניסה זמניים שאיתם תיכנסו ותיגשו אל Google Cloud במשך הפעילות של המעבדה.

מה צריך?

כדי להשלים את המעבדה, תצטרכו:

• גישה לדפדפן אינטרנט סטנדרטי (מומלץ להשתמש בדפדפן Chrome).
• זמן להשלמת המעבדה.

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

הערה: אם משתמשים במכשיר עם Chrome OS, צריך לפתוח חלון אנונימי כדי להריץ את המעבדה הזו.

איך מתחילים שיעור מעשי ונכנסים אל Google Cloud Console

1. לוחצים על הלחצן Start Lab (התחלת השיעור המעשי). אם זה שיעור בתשלום, יקפוץ חלון שבו בוחרים אמצעי תשלום. מימין יש חלונית עם פרטי כניסה זמניים שמולאו מראש. צריך להשתמש בפרטים האלו בשיעור המעשי הזה.

   

2. מעתיקים את שם המשתמש ואז לוחצים על Open Google Console (פתיחת Google Console). יופעלו משאבים במעבדה, ואז ייפתח הדף Sign in (כניסה) בכרטיסייה נפרדת.

   

   טיפ: כדאי לפתוח את הכרטיסיות בחלונות נפרדים, אחד לצד השני.

3. בדף Sign in (כניסה), מדביקים את שם המשתמש שהעתקתם מהחלונית Connection Details (פרטי התחברות). אחר כך מעתיקים ומדביקים את הסיסמה.

   חשוב: צריך להשתמש בפרטי הכניסה מהחלונית Connection Details. אין להשתמש בפרטי הכניסה של Qwiklabs. אם יש לכם חשבון Google Cloud משלכם, אל תשתמשו בו בשיעור המעשי הזה (כך תימנעו מחיוב בתשלום).

4. לוחצים כדי לעבור את הדפים הבאים:

   • הסכמה לתנאים ולהגבלות.
   • אין להוסיף אפשרויות שחזור או אימות דו-שלבי (כי החשבון הזה זמני).
   • אין להירשם לתקופות ניסיון בחינם.

לאחר כמה דקות, Cloud Console ייפתח בכרטיסייה הזו.

משימה 1: העקרונות הבסיסיים של SQL

טבלאות ומסדי נתונים

כמו שציינו קודם, בעזרת SQL אתם יכולים לחלץ מידע מ"מערכי נתונים מובְנים". למערכים כאלה יש עיצוב וכללים ברורים, והרבה פעמים הם מחולקים לטבלאות: נתונים שמסודרים בשורות ובעמודות.

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

הדוגמה הבאה היא של מערך נתונים מובְנים – טבלה פשוטה:

משתמש	מחיר	נשלחה
שמעון	35$	כן
בני	50$	לא

אם יש לכם ניסיון ב-Google Sheet, הטבלה הזו אמורה להיראות לכם די מוכרת. לטבלה יש עמודות (USER,‏ PRICE ו-SHIPPED) ושתי שורות עם ערכים שמולאו בכל עמודה.

מסד נתונים הוא בעצם אוסף של טבלה אחת או יותר. SQL היא כלי לניהול מסדי נתונים מובְנים, אבל הרבה פעמים (וגם בשיעור ה-Lab הזה) מריצים שאילתות על טבלה אחת או מספר טבלאות מחוברות – לא על מסדי נתונים שלמים.

SELECT ו-FROM

‏SQL היא שפה פונטית, כך שלפני שמריצים שאילתות כדאי לחשוב על השאלה שרוצים לשאול ועל תשובה שרוצים לקבל מהנתונים (אלא אם סתם מתנסים בה בשביל הכיף).

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

מילות המפתח החיוניות ביותר הן SELECT ו-FROM:

• משתמשים ב-SELECT כדי לציין אילו שדות רוצים לשלוף ממערך הנתונים.
• משתמשים ב-FROM כדי לציין מאיזו טבלה או מאילו טבלאות רוצים לשלוף את הנתונים.

ניעזר בדוגמה כדי להבין. נניח שיש לכם את הטבלה הבאה, example_table, עם העמודות USER, ‏PRICE ו-SHIPPED.

ונניח שאתם רוצים לשלוף ממנה רק את הנתונים שבעמודה USER. כדי לעשות זאת, תוכלו להריץ את השאילתה הבאה עם מילות המפתח SELECT ו-FROM:

SELECT USER FROM example_table

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

אפשר לבחור גם מספר עמודות באמצעות מילת המפתח SELECT של SQL. נניח שאנחנו רוצים לשלוף את הנתונים מעמודות USER ו-SHIPPED. כדי לעשות את זה, נשנה את השאילתה הקודמת ונוסיף עוד שם של עמודה למילת המפתח SELECT (חשוב להפריד בין השמות באמצעות פסיקים!):

‎SELECT USER, SHIPPED FROM example_table‎

אם נריץ את הפקודה הזו, נשלוף את הנתונים מהעמודות USER ו-SHIPPED:

וזהו! למדתם שתי מילות מפתח בסיסיות ב-SQL! עכשיו ניכנס קצת יותר בעובי הקורה.

WHERE

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

‎SELECT USER FROM example_table WHERE SHIPPED='YES'‎

אם נריץ את הפקודה הזו, נשלוף מהעמודה USER רק את הנתונים של מי שהחבילה שלו נשלחה:

עכשיו, אחרי שהבנתם את מילות המפתח הבסיסיות של SQL, תוכלו ליישם את מה שלמדתם ולהריץ את שאילתות מהסוג הזה במסוף BigQuery.

בחינת ההבנה

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

משימה 2: התנסות במסוף BigQuery

הפרדיגמה של BigQuery

‏BigQuery הוא מחסן נתונים (data warehouse) מנוהל בקנה מידה של פטה-בייט שפועל ב-Google Cloud. אנליסטים ומדעני נתונים יכולים להריץ בקלות שאילתות כדי לסנן מערכי נתונים גדולים, לאחד תוצאות ולבצע פעולות מורכבות, בלי לדאוג להגדרה ולניהול של שרתים. הגישה אליו מתבצעת דרך כלי שורת הפקודה (שמותקן מראש ב-cloudshell) או דרך מסוף אינטרנט. בשני המקומות אפשר לנהל נתונים ולהריץ עליהם שאילתות בפרויקטים ב-Google Cloud.

בשיעור ה-Lab הזה נשתמש במסוף האינטרנט כדי להריץ שאילתות SQL.

פתיחת המסוף של BigQuery

ב-Google Cloud Console, בוחרים בתפריט הניווט > BigQuery:

תיפתח ההודעה Welcome to BigQuery in the Cloud Console (ברוך בואך אל BigQuery ב-Cloud Console). בהודעה הזו יש קישור למדריך למתחילים ולהערות המוצר.

לוחצים על סיום.

המסוף של BigQuery ייפתח.

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

החלונית שנמצאת בצד שמאל של המסוף היא חלונית הניווט. מלבד היסטוריית השאילתות, השאילתות השמורות והיסטוריית המשימות, ישנה הכרטיסייה Explorer.

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

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

העלאת נתונים והרצת שאילתות

בחלק הזה נשלוף נתונים ציבוריים לפרויקט כדי שתוכלו לתרגל הרצת פקודות SQL ב-BigQuery.

1. לוחצים על ‎+ ADD.

2. בוחרים באפשרות Star a project by name.

3. נותנים לפרויקט את השם bigquery-public-data.

4. לוחצים על STAR.

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

5. עכשיו יש לכם גישה לנתונים האלה:

• פרויקט ב-Google Cloud ‏‏← bigquery-public-data
• מערך נתונים ← london_bicycles

6. לוחצים על מערך הנתונים london bicycles כדי להציג את הטבלאות שבמערך

• טבלה ← cycle_hire
• טבלה ← cycle_stations

בשיעור ה-Lab הזה נשתמש בנתונים מהטבלה cycle_hire. פותחים את הטבלה cycle_hire ולוחצים על הכרטיסייה Preview. הדף שנפתח אמור להיראות כך:

אתם מוזמנים לעיין בעמודות ובערכים שבשורות. עכשיו אתם מוכנים להריץ כמה שאילתות SQL על הטבלה cycle_hire.

הרצת השאילתות SELECT, ‏FROM ו-WHERE ב-BigQuery

עכשיו אמורה להיות לכם הבנה בסיסית במילות המפתח לשאילתות SQL ובפרדיגמת הנתונים של BigQuery, ויש לכם גם נתונים שאתם יכולים להתנסות בהם. באמצעות השירות הזה תוכלו להריץ פקודות SQL.

אם תסתכלו בפינה הימנית התחתונה במסוף, תראו שיש 24,369,201 שורות של נתונים, או נסיעות באופניים שיתופיים בלונדון בין השנים 2015 ל-2017 (מספר לא מבוטל של נסיעות!)

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

1. מעתיקים את הפקודה הבאה ומדביקים אותה בעורך השאילתות:

SELECT end_station_name FROM `bigquery-public-data.london_bicycles.cycle_hire`;‎

2. לוחצים על Run.

אחרי כ-20 שניות אמורות להופיע 24,369,201 שורות רק עם העמודה שבודדנו באמצעות השאילתה, end_station_name.

עכשיו ננסה לבדוק כמה נסיעות באופניים נמשכו 20 דקות או יותר.

3. מוחקים את השאילתה מהעורך ומריצים את השאילתה הבאה באמצעות מילת המפתח WHERE.

SELECT * FROM `bigquery-public-data.london_bicycles.cycle_hire` WHERE duration>=1200;‎

הרצת השאילתה הזו תימשך כדקה.

הפקודה SELECT *‎ מחזירה את כל ערכי העמודות מהטבלה. משך הנסיעה הוא בשניות, ולכן השתמשנו בערך 1,200 (מכפלה של 60 ב-20).

אם תסתכלו בפינה הימנית התחתונה, תראו שיש עכשיו 7,334,890 תוצאות. אם נחלק את המספר הזה בסך כל השורות (7,334,890 חלקי 24,369,201), נראה שכ-30% מהנסיעות באופניים שיתופיים בלונדון נמשכו 20 דקות או יותר (לזה אנחנו קוראים נסיעות ארוכות!)

בחינת ההבנה

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

משימה 3: מילות מפתח נוספות של SQL: ‏GROUP BY, ‏COUNT, ‏AS ו-ORDER BY

GROUP BY

בעזרת מילת המפתח GROUP BY נוכל לקבץ את התוצאות משורות שחולקות קריטריונים נפוצים (למשל ערך מסוים בעמודה), ולהציג את כל הערכים הייחודיים שנמצאו לפי אותם קריטריונים.

זו מילת מפתח שימושית לבירור מידע קטגורי מטבלאות.

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

SELECT start_station_name FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name;‎

2. לחצו על Run.

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

בלי GROUP BY, השאילתה הייתה מחזירה את כל 24,369,201 השורות. אבל מכיוון שהשתמשנו ב-GROUP BY, מופיעים רק הערכים הייחודיים שנמצאו בטבלה. אתם יכולים לראות זאת בעצמכם בפינה הימנית התחתונה. תוכלו לראות שיש 880 שורות. כלומר יש 880 נקודות התחלה שונות לאופניים שיתופיים בלונדון.

COUNT

הפונקציה COUNT()‎ מחזירה את מספר השורות שחולקות אותם קריטריונים (למשל אותו ערך בעמודה). זו פונקציה שימושית מאוד לשילוב עם GROUP BY.

נוכל להוסיף את הפונקציה COUNT לשאילתה הקודמת, כדי לברר כמה נסיעות התחילו בכל אחת מנקודות ההתחלה.

• מוחקים את השאילתה בעורך, מעתיקים ומדביקים את הפקודה הבאה ולוחצים על RUN:

SELECT start_station_name, COUNT(*) FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name;‎

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

AS

מילת מפתח נוספת של SQL היא AS, שיוצרת כינוי לטבלה או לעמודה. כינוי הוא שם חדש שניתן לעמודה או לטבלה שמוחזרת, לפי מה שמציינים ב-AS.

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

SELECT start_station_name, COUNT(*) AS num_starts FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name;‎

2. לחצו על Run.

השם של העמודה הימנית בתוצאות ישתנה מ-COUNT(*)‎ ל-num_starts.

כמו שאתם רואים, העמודה COUNT(*)‎ בטבלה שהוחזרה נקראת עכשיו num_starts – הכינוי שנתנו לה. מילת המפתח הזו שימושית במיוחד אם מערכי הנתונים גדולים – השמות של עמודות או טבלאות נפוצים יותר ממה שאתם חושבים!

ORDER BY

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

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

כל אחת מהפקודות הבאות היא שאילתה נפרדת. בכל אחת מהפקודות:

1. מוחקים את השאילתה בעורך.
2. מעתיקים את הפקודה ומדביקים אותה בעורך השאילתות.
3. לוחצים על Run. בודקים את התוצאות.

SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY start_station_name;‎ SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY num;‎ SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY num DESC;‎

התוצאות של השאילתה האחרונה מוינו לפי מספר הנסיעות שהתחילו בכל אחת מתחנות ההתחלה.

נוכל לראות שבתחנה "Belgrove Street, King's Cross" התחילו הכי הרבה נסיעות. עם זאת, אם תחלקו את המספר הזה בסך כל השורות (234,458 חלקי 24,369,201), תראו שפחות מ-1% מהנסיעות התחילו בתחנה הזו.

בחינת ההבנה

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

משימה 4: שימוש ב-Cloud SQL

ייצוא שאילתות כקובצי CSV

‏Cloud SQL הוא שירות מנוהל של מסד נתונים, שבעזרתו אפשר ליצור, לתחזק ולנהל מסדי נתונים רלציוניים של PostgreSQL ו-MySQL בענן. ב-Cloud SQL אפשר להשתמש בשני פורמטים של נתונים: קובצי SQL מוכנים לשימוש (sql.) וקובצי CSV (‏csv.) כאן תלמדו איך לייצא לקובצי CSV קבוצות של נתונים מהטבלה cycle_hire ולהעלות אותם ל-Cloud Storage בתור מיקום זמני.

נחזור למסוף BigQuery. זו אמורה להיות הפקודה האחרונה שהרצתם:

SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY num DESC;‎

1. בחלק עם תוצאות השאילתה, לוחצים על SAVE RESULTS > ‏CSV(local file). תתחיל הורדה של קובץ CSV עם תוצאות השאילתה. שימו לב איך הקובץ נקרא ואיפה הוא נשמר, כי תצטרכו אותו בקרוב.

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

SELECT end_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY end_station_name ORDER BY num DESC;‎

תוחזר טבלה שמכילה את מספר הנסיעות באופניים שיתופיים שהסתיימו בכל אחת מתחנות הסיום, מהגבוה לנמוך.

3. בחלק עם תוצאות השאילתה, לוחצים על SAVE RESULTS > ‏CSV(local file). תתחיל הורדה של קובץ CSV עם תוצאות השאילתה. שימו לב איך הקובץ נקרא ואיפה הוא נשמר, כי תצטרכו אותו בחלק הבא.

העלאת קובצי CSV ל-Cloud Storage

1. נכנסים למסוף Cloud ויוצרים קטגוריית אחסון שאליה אפשר להעלות את הקבצים שנוצרו.

2. לוחצים על תפריט הניווט > Cloud Storage ‏> Buckets ואז על CREATE BUCKET.

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

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

4. אם מופיעה הודעה, לוחצים על Confirm בתיבת הדו שיח Public access will be prevented.

בדיקה שהמשימה הושלמה

לוחצים על Check my progress למטה כדי לבדוק את ההתקדמות בשיעור ה-Lab. אם הקטגוריה נוצרה, יופיע ציון בדיקה.

יצירת קטגוריה של אחסון בענן

עכשיו אתם אמורים להיות במסוף Cloud ולראות את הקטגוריה החדשה של Cloud Storage שיצרתם.

1. לוחצים על UPLOAD FILES ובוחרים את קובץ ה-CSV שמכיל את הנתונים של start_station_name.

2. עכשיו לוחצים על Open. חוזרים על הפעולות האלה גם לנתונים של end_station_name.

3. לוחצים על סמל שלוש הנקודות שליד הקובץ start_station_name כדי לשנות את השם שלו, ואז לוחצים על Rename. משנים את שם הקובץ ל-start_station_data.csv.

4. לוחצים על סמל שלוש הנקודות שליד הקובץ end_station_name כדי לשנות את השם שלו, ואז לוחצים על Rename. משנים את שם הקובץ ל-end_station_data.csv.

הקבצים start_station_data.csv ו-end_station_data.csv אמורים להופיע ברשימה Objects בדף Bucket details.

בדיקה שהמשימה הושלמה

לוחצים על Check my progress כדי לוודא שהשלמתם את המשימה. אם קובצי ה-CSV הועלו כאובייקטים לקטגוריה, יופיע ציון בדיקה.

העלאת קובצי CSV ל-Cloud Storage

משימה 5: יצירת מכונה של Cloud SQL

פותחים את המסוף ולוחצים על תפריט הניווט >‏ SQL.

1. לוחצים על CREATE INSTANCE >‏ Choose MySQL .

2. כמזהה המכונה, כותבים my-demo.

3. בשדה Password כותבים סיסמה מאובטחת (וזוכרים אותה!).

4. בוחרים את הגרסה MySQL 5.8 למסד הנתונים.

5. ב-Choose a Cloud SQL edition, בוחרים באפשרות Enterprise.

6. ב-Preset, בוחרים באפשרות Development (4 vCPU, 16 GB RAM, 100 GB Storage, Single zone).

אזהרה: אם לא תבחרו ב-Preset את האפשרות Development, הפרויקט יסומן בשגיאה ושיעור ה-Lab יסתיים.

7. בשדה Multi zones (Highly available) בוחרים באפשרות

8. לוחצים על CREATE INSTANCE.

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

9. לוחצים על המכונה של Cloud SQL. הדף SQL Overview ייפתח.

בדיקה שהמשימה הושלמה

לוחצים על Check my progress למטה כדי לבדוק את ההתקדמות בשיעור ה-Lab. אם נוצרה מכונה של Cloud SQL, יופיע ציון בדיקה.

יצירת מכונה של Cloud SQL

משימה 6: הרצת שאילתות חדשות ב-Cloud SQL

מילת המפתח CREATE (מסדי נתונים וטבלאות)

עכשיו, אחרי שיצרנו מכונה של Cloud SQL, ניצור בתוכה מסד נתונים באמצעות שורת הפקודה של Cloud Shell.

1. לוחצים על הסמל של Cloud Shell בפינה הימנית העליונה של המסוף כדי לפתוח אותו.

2. מריצים את הפקודה הבאה כדי להגדיר את מזהה הפרויקט בתור משתנה סביבה:

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

יצירת מסד נתונים ב-Cloud Shell

1. כדי להגדיר אימות בלי לפתוח דפדפן, מריצים את הפקודה הבאה ב-Cloud Shell.

gcloud auth login --no-launch-browser‎

הפקודה הזאת מספקת קישור לפתיחה בדפדפן. פותחים את הקישור באותו דפדפן שבו נכנסתם לחשבון Qwiklabs. אחרי הכניסה לחשבון תקבלו קוד אימות שצריך להעתיק – הדביקו אותו ב-Cloud Shell.

2. כדי לחבר את המכונה של SQL, מריצים את הפקודה הבאה. אם לא השתמשתם בשם my-demo למכונה, זכרו להחליף אותו בשם שבחרתם:

gcloud sql connect my-demo --user=root --quiet שימו לב: יכול להיות שייקח דקה להתחבר למכונה. אם ההודעה "Operation failed because another operation was already in progress" מופיעה, סימן שהפעולה נכשלה כי מתבצעת פעולה אחרת. תצטרכו לחכות שיצירת המכונה של SQL תסתיים ורק אז לנסות להתחבר אליה שוב.

3. כשתתבקשו, תצטרכו להזין את הסיסמה שבחרתם למכונה.

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

‎Welcome to the MySQL monitor. Commands end with ; or \g.‎ Your MySQL connection id is 494 Server version: 8.0.31-google (Google) Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.‎ Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.‎ mysql>‎

כל מכונה של Cloud SQL כוללת מסדי נתונים שהוגדרו מראש, אבל כאן ניצור מסד נתונים משלנו שבו נאחסן את נתוני האופניים השיתופיים מלונדון.

4. כדי ליצור מסד נתונים בשם bike, מריצים את הפקודה הבאה בשורת הפקודה של MySQL:

CREATE DATABASE bike;‎

הפלט הבא אמור להתקבל:

Query OK, 1 row affected (0.05 sec) mysql>‎

בדיקה שהמשימה הושלמה

לוחצים על Check my progress כדי לוודא שהשלמתם את המשימה. אם נוצר מסד נתונים במכונה של Cloud SQL, יופיע ציון בדיקה.

יצירת מסד נתונים

יצירת טבלה ב-Cloud Shell

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

USE bike;‎ CREATE TABLE london1 (start_station_name VARCHAR(255), num INT);‎

בפקודה הזו השתמשנו במילת המפתח CREATE, אבל הפעם השתמשנו בתנאי TABLE כדי לציין שאנחנו רוצים ליצור טבלה במקום מסד נתונים. במילת המפתח USE מציינים לאיזה מסד נתונים רוצים להתחבר. עכשיו יש לכם טבלה בשם london1 שמכילה שתי עמודות: start_station_name ו-num. החלק ‎VARCHAR(255) מציין את האורך המקסימלי של כל מחרוזת בעמודה – במקרה שלנו, 255 תווים. החלק INT מציין שמדובר בעמודה מסוג מספרים שלמים.

2. כדי ליצור טבלה נוספת בשם london2, מריצים את הפקודה הבאה:

USE bike;‎ CREATE TABLE london2 (end_station_name VARCHAR(255), num INT);‎

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

SELECT * FROM london1;‎ SELECT * FROM london2;‎

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

‎Empty set (0.04 sec)

מכיוון שלא טענתם נתונים, מופיע הכיתוב empty set.

העלאת קובצי CSV לטבלאות

נחזור למסוף Cloud SQL. עכשיו נעלה את קובצי ה-CSV ששמרנו, start_station_name ו-end_station_name, לטבלאות החדשות שיצרנו, london1 ו-london2.

1. בדף של המכונה של Cloud SQL, לוחצים על IMPORT.
2. בשדה הקובץ של Cloud Storage לוחצים על Browse ואז על החץ שליד השם של הקטגוריה, ואחר כך לוחצים על start_station_data.csv. לוחצים על Select.
3. בוחרים בפורמט CSV לקובץ.
4. בוחרים את מסד הנתונים bike וכותבים את שם הטבלה london1.
5. לוחצים על Import.

חוזרים על הפעולות האלה לקובץ ה-CSV הנוסף.

1. בדף של המכונה של Cloud SQL, לוחצים על IMPORT.
2. בשדה הקובץ של Cloud Storage לוחצים על Browse ואז על החץ שליד השם של הקטגוריה ועל end_station_data.csv, ואחר כך לוחצים על Select.
3. בוחרים בפורמט CSV לקובץ.
4. בוחרים את מסד הנתונים bike ומקלידים את שם הטבלה london2.
5. לוחצים על Import.

עכשיו שני קובצי ה-CSV אמורים להופיע בטבלאות שבמסד הנתונים bike.

1. נחזור לסשן של Cloud Shell ונריץ את הפקודה הבאה בהודעת השרת של MySQL, כדי לבדוק מה מכילה הטבלה london1:

SELECT * FROM london1;‎

בפלט אמורות להיות 955 שורות, אחת לכל שם של תחנה.

2. מריצים את הפקודה הבאה כדי לוודא שגם הטבלה london2 מכילה נתונים:

SELECT * FROM london2;‎

בפלט אמורות להיות 959 שורות, אחת לכל שם של תחנה.

מילת המפתח DELETE

עכשיו נכיר עוד כמה מילות מפתח של SQL שיכולות לעזור לנו בניהול נתונים. הראשונה היא DELETE.

• מריצים את הפקודות הבאות בסשן של MySQL, כדי למחוק את השורה הראשונה בטבלאות london1 ו-london2:

DELETE FROM london1 WHERE num=0;‎ DELETE FROM london2 WHERE num=0;‎

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

‎Query OK, 1 row affected (0.04 sec)

השורות שנמחקו היו הכותרות של העמודות בקובצי ה-CSV. למעשה, לא השתמשנו במילת המפתח DELETE בפקודה כדי להסיר את השורה הראשונה בכל קובץ, אלא בכל השורות בטבלה שבהן שם העמודה (num) הכיל ערך ספציפי (0). אם תריצו את השאילתות SELECT * FROM london1;‎ ו-SELECT * FROM london2;‎ ותגללו לראש הטבלה, תראו שהשורות האלה לא קיימות יותר.

מילת המפתח INSERT INTO

אתם יכולים גם להוסיף ערכים לטבלאות באמצעות מילת המפתח INSERT INTO.

• מריצים את הפקודה הבאה ומוסיפים שורה חדשה לטבלה london1: תחנה בשם test destination לעמודה start_station_name ואת המספר 1 לעמודה num.

INSERT INTO london1 (start_station_name, num) VALUES ("test destination", 1);‎

כשמשתמשים במילת המפתח INSERT INTO צריך לציין טבלה (london1). הפקודה תיצור שורה חדשה עם עמודות לפי התנאים בסוגריים הראשונים (במקרה שלנו, start_station_name ו-num). מה שבא אחרי התנאי VALUES יתווסף בתור ערכים לשורה החדשה.

הפלט הבא אמור להתקבל:

‎Query OK, 1 row affected (0.05 sec)

עכשיו, אם נריץ את השאילתה SELECT * FROM london1;‎ נראה שנוספה שורה חדשה בתחתית הטבלה london1.

מילת המפתח UNION

מילת המפתח האחרונה של SQL שנלמד עליה היא UNION. משתמשים במילת המפתח הזו כדי לשלב בין הפלט של מספר שאילתות SELECT לפלט אחד של תוצאות. במקרה שלנו, נשתמש ב-UNION כדי לשלב בין נתוני הטבלאות london1 ו-london2.

בשאילתה הבאה נשלוף נתונים ספציפיים משתי הטבלאות ונשלב אותם בעזרת האופרטור UNION.

• מריצים את הפקודה הבאה בשורת הפקודה של MySQL:

‎SELECT start_station_name AS top_stations, num FROM london1 WHERE ‎num>100000 ‎UNION ‎SELECT end_station_name, num FROM london2 WHERE num>100000 ORDER BY top_stations DESC;‎

בשאילתה הראשונה עם SELECT בחרנו את שתי העמודות מהטבלה london1, ויצרנו לעמודה start_station_name את הכינוי top_stations. השתמשנו במילת המפתח WHERE כדי לשלוף רק שמות של תחנות שבהן התחילו יותר מ-100,000 נסיעות באופניים שיתופיים.

בשאילתה השנייה עם SELECT בחרנו את שתי העמודות מהטבלה london2, והשתמשנו במילת המפתח WHERE כדי לשלוף רק שמות של תחנות שבהן הסתיימו יותר מ-100,000 נסיעות באופניים שיתופיים.

באמצעות מילת המפתח UNION שילבנו בין הנתונים מ-london2 ל-london1 לפלט אחד. מכיוון שאיחדנו בין הנתונים מ-london1 ל-london2, העמודות ייקראו top_stations ו-num.

השתמשנו במילת המפתח ORDER BY כדי לסדר את השורות בטבלה הסופית לפי סדר אלפביתי יורד של הערכים בעמודה top_stations.

הפלט הבא אמור להתקבל:

כמו שאפשר לראות, 13 מתוך 14 תחנות הן הכי פופולריות להתחלה ולסיום של נסיעות. השתמשתם בכמה מילות מפתח בסיסיות של SQL כדי להריץ שאילתה על מערך נתונים, לקבל נתונים ולענות על שאלות ספציפיות.

כל הכבוד!

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

סיום יחידת ה-Quest

זהו שיעור Lab ללימוד עצמי שהוא חלק מיחידות ה-Quest הבאות: ‏ Data Science on Google Cloud, ‏‏Cloud SQL, ‏BigQuery Basics for Data Analysts, ‏NCAA® March Madness®: Bracketology with Google Cloud, ‏Cloud Engineering,‏ Data Catalog Fundamentals ו-Applying BQML's Classification, Regression, and Demand Forecasting for Retail Applications. יחידת Quest היא מסלול לימוד שבנוי מסדרה של שיעורי Lab. כאשר משלימים יחידת Quest מקבלים תג שמציין את ההישג. אתם יכולים להציג את התג או התגים באופן גלוי לכולם, ולקשר אליהם בקורות החיים שלכם באינטרנט או בחשבון במדיה חברתית. רוצים לקבל קרדיט מיידי על השלמת שיעור ה-Lab הזה? הירשמו לאחת מיחידות ה-Quest שמכילות אותו. בקטלוג של Google Skills מפורטת הרשימה המלאה של יחידות ה-Quest.

השלבים הבאים / מידע נוסף

רוצים ללמוד עוד על Cloud SQL ו-BigQuery ולתרגל את השימוש בהם? תוכלו להמשיך לשיעורי ה-Lab הבאים של Google Skills:

• נתוני מזג אוויר ב-BigQuery
• ניתוח נתונים של התאחדות ה-NCAA באמצעות BigQuery
• טעינת נתונים ב-Google Cloud SQL
• Cloud SQL עם Terraform

למידע נוסף על מדעי הנתונים: Data Science on the Google Cloud Platform, 2nd Edition: O'Reilly Media, Inc.‎.

הדרכה והסמכה של Google Cloud

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

המדריך עודכן לאחרונה ידנית ב-6 באוקטובר 2023

שיעור ה-Lab נבדק לאחרונה ב-6 באוקטובר 2023

זכויות יוצרים 2026 Google LLC. כל הזכויות שמורות. Google והלוגו של Google הם סימנים מסחריים רשומים של Google LLC. שמות של חברות ומוצרים אחרים עשויים להיות סימנים מסחריים של החברות, בהתאמה, שאליהן הם משויכים.