
Before you begin
- Labs create a Google Cloud project and resources for a fixed time
- Labs have a time limit and no pause feature. If you end the lab, you'll have to restart from the beginning.
- On the top left of your screen, click Start lab to begin
Create a cloud storage bucket
/ 25
Upload CSV files to Cloud Storage
/ 25
Create a Cloud SQL instance
/ 25
Create a database
/ 25
SQL (結構化查詢語言) 是資料作業的標準語言,可用於從結構化資料集中獲得問題解答和取得深入分析結果。這種語言常用於資料庫管理,可執行的工作包括將交易記錄寫入關聯資料庫,以及進行 PB 規模資料分析。
本實驗室分為兩個部分:前半部分將說明基本的 SQL 查詢關鍵字,您會使用內含倫敦共享單車資訊的公開資料集,在 BigQuery 執行這些關鍵字。
在後半部分,您將瞭解如何把倫敦共享單車資料集的一部分資料匯出成 CSV 檔案,並上傳至 Cloud SQL。之後,您將學習如何使用 Cloud SQL 建立和管理資料庫和資料表,並在最後實際練習透過其他 SQL 關鍵字來控管和編輯資料。
本實驗室的學習內容包括:
務必注意:請先登出個人或企業的 Gmail 帳戶,再開始執行本實驗室。
這是入門等級的實驗室,無論是只有些許 SQL 使用經驗或完全沒有經驗都不成問題。建議您先熟悉 Cloud Storage 和 Cloud Shell 服務,但這並非強制要求。本實驗室將說明以 SQL 讀取和撰寫查詢的基本概念,並讓您在 BigQuery 和 Cloud SQL 中加以應用。
開始本實驗室前,請評估您對 SQL 的熟悉程度。以下為較具挑戰性的實驗室,所提供的相關知識可應用於更進階的用途。
準備好後,請向下捲動,並按照下列步驟設定實驗室環境。
請詳閱以下操作說明。研究室活動會計時,而且中途無法暫停。點選「Start Lab」 後就會開始計時,讓您瞭解有多少時間可以使用 Google Cloud 資源。
您將在真正的雲端環境中完成實作研究室活動,而不是在模擬或示範環境。為達此目的,我們會提供新的暫時憑證,讓您用來在研究室活動期間登入及存取 Google Cloud。
如要完成這個研究室活動,請先確認:
按一下「Start Lab」(開始研究室) 按鈕。如果研究室會產生費用,畫面中會出現選擇付款方式的彈出式視窗。左側的「Lab Details」窗格會顯示下列項目:
點選「Open Google Cloud console」;如果使用 Chrome 瀏覽器,也能按一下滑鼠右鍵,然後選取「在無痕式視窗中開啟連結」。
接著,實驗室會啟動相關資源並開啟另一個分頁,當中顯示「登入」頁面。
提示:您可以在不同的視窗中並排開啟分頁。
如有必要,請將下方的 Username 貼到「登入」對話方塊。
您也可以在「Lab Details」窗格找到 Username。
點選「下一步」。
複製下方的 Password,並貼到「歡迎使用」對話方塊。
您也可以在「Lab Details」窗格找到 Password。
點選「下一步」。
按過後續的所有頁面:
Google Cloud 控制台稍後會在這個分頁開啟。
如前所述,SQL 可讓您從「結構化資料集」取得資訊。結構化資料集具有明確的規則和格式,且經常會整理成資料表,也就是以列和欄的格式呈現資料。
非結構化資料的例子包括圖片檔。非結構化資料無法使用 SQL 執行作業,也無法儲存在 BigQuery 資料集或資料表中 (至少不能以原生方式儲存)。舉例來說,如要使用圖片資料,就需要透過 Cloud Vision 等服務,或是直接透過其 API。
以下為結構化資料集的例子,也就是簡單的資料表:
User |
Price |
Shipped |
Sean |
$35 |
Yes |
Rocky |
$50 |
No |
如果您使用過 Google 試算表,應該不會對上述格式感到陌生。資料表含有「User」、「Price」和「Shipped」欄,以及已填入資料欄值的兩個資料列。
從本質來看,資料庫就是「一或多個資料表的集合」。雖然 SQL 屬於結構化資料庫管理工具,但在許多情況下 (和本實驗室中),您只會在一或數個彙整的資料表中執行查詢,而非查詢整個資料庫。
SQL 具有語音性質,在執行查詢前,若能先釐清想透過查詢資料回答什麼問題會很有幫助 (除非只是想隨意探索)。
SQL 擁有預先定義的關鍵字,可用來將問題翻譯成類似英文的 SQL 語法,讓資料庫引擎傳回想要的答案。
最常用的關鍵字是 SELECT
和 FROM
:
SELECT
。FROM
。舉例來說,假設 example_table
資料表含有「USER」、「PRICE」和「SHIPPED」欄,如下所示:
如果只要提取「USER」欄的資料,執行下列使用了 SELECT
和 FROM
的查詢即可:
執行上述指令時,您會選取 example_table
資料表 USER
欄中的所有姓名。
您也可以使用 SQL 的 SELECT
關鍵字選取多個資料欄。如要提取「USER」和「SHIPPED」欄的資料,請修改上一個查詢,將另一個資料欄值新增至 SELECT
查詢 (務必使用逗號分隔!):
執行上述指令即可從記憶體中擷取 USER
和 SHIPPED
的資料:
就這樣,您學會了兩個基本的 SQL 關鍵字!接下來可以嘗試更有趣的操作。
WHERE
關鍵字是另一種 SQL 指令,可以在資料表中篩選特定資料欄值。舉例來說,若要從 example_table
提取包裹已出貨的使用者名稱,可以在查詢中加上 WHERE
,如下:
執行上述指令後,系統就會從記憶體傳回所有包裹已出貨的使用者:
現在您已對 SQL 核心關鍵字擁有基本的瞭解,就能在 BigQuery 控制台執行這些類型的查詢以應用所學。
請回答下列選擇題,加強您對目前所介紹概念的瞭解。盡力回答即可。
BigQuery 是在 Google Cloud 執行的全代管 PB 規模 data warehouse,可供資料分析師和數據資料學家迅速查詢和篩選大型資料集、匯總結果和執行複雜的作業,而不必費心設定和管理伺服器。這套系統的呈現形式為指令列工具 (已預先安裝在 Cloud Shell) 或網路控制台,兩種形式都能用來管理和查詢 Google Cloud 專案中的資料。
在本實驗室中,您將使用網路控制台執行 SQL 查詢。
接著,畫面中會顯示「歡迎使用 Cloud 控制台中的 BigQuery」訊息方塊,當中會列出快速入門導覽課程指南的連結和版本資訊。
BigQuery 控制台會隨即開啟。
我們先花一些時間記下使用者介面上的一些重要功能。控制台右側為查詢的「編輯器」,可讓您撰寫及執行 SQL 指令 (就像先前提及的範例)。下方是「查詢記錄」,會列出先前執行的查詢。
控制台中的左側窗格是導覽選單。除了功能明確的「查詢記錄」、「已儲存的查詢」和「工作記錄」之外,還有「Explorer」專區。
在「Explorer」專區中,最高層級的資源內含 Google Cloud 專案,這些專案就如同您在各 Google Cloud Skills Boost 實驗室中登入使用的臨時 Google Cloud 專案。從控制台和最後一張螢幕截圖可以發現,「Explorer」專區中只有您的專案。如果點選專案名稱旁的箭頭,畫面中不會顯示任何內容。
這是因為專案內未含有資料集或資料表,沒有任何資料可供查詢。我們先前說明過,資料集內含資料表。在 BigQuery 將資料新增至專案時,請留意「專案內含資料集,而資料集內含資料表」。您已充分瞭解「專案 > 資料集 > 資料表」的結構和控制台的細節,現在可以上傳一些可供查詢的資料了。
本節說明如何將一些公開資料提取至專案,以在 BigQuery 中實際執行 SQL 指令。
按一下「+ 新增」。
選擇「依據名稱為專案加上星號」。
將專案名稱設為 bigquery-public-data。
按一下「加上星號」。
請務必留意,您在這個新分頁中操作時,仍然不是使用實驗室專案。您只是將內含資料集和資料表且可公開存取的專案提取至 BigQuery 進行分析,並未「切換」至該項專案。所有工作和服務仍然與您的 Google Cloud Skills Boost 帳戶相連結。查看在控制台頂端附近的專案欄位即可確認這一點:
bigquery-public-data
london_bicycles
cycle_hire
cycle_stations
在這個實驗室中,您會使用 cycle_hire 中的資料。請開啟 cycle_hire
資料表,然後點選「預覽」分頁標籤。您應該會看見類似下方的頁面:
請查看各欄和填入各列的值。現在您可以對 cycle_hire
資料表執行幾個 SQL 查詢。
現在您已具備 SQL 查詢關鍵字和 BigQuery 資料模式的基本概念,也擁有可查詢的資料,請透過這項服務執行幾個 SQL 指令。
控制台右下角會顯示共有 83,434,866 個資料列,也就是 2015 年至 2017 年間倫敦各趟共享單車行程的資料,資料量非常可觀!
請記下第九欄的鍵 end_station_name
,也就是共享單車行程的終點。深入探索之前,請先執行簡單的查詢來單獨查看 end_station_name
欄。
約 20 秒之後,系統應會傳回 83,434,866 個資料列,內含您查詢的那一欄:end_station_name
。
找找看有多少單車行程持續 20 分鐘以上吧!
WHERE
關鍵字執行下列查詢:這項查詢可能需要幾分鐘才能完成執行。
SELECT *
會回傳資料表中的所有資料欄值。時長是以秒計算,因此使用的數值是 1200 (60 * 20)。
右下角顯示傳回了 26,441,016 個資料列。將這個數字除以總數 (26441016/83434866),可以得出約 30% 的倫敦共享單車行程持續 20 分鐘以上,真的很有毅力!
請回答下列選擇題,以加強您對目前所介紹概念的瞭解。盡力回答即可。
GROUP BY
關鍵字會匯總擁有共同條件 (例如資料欄值) 的結果集資料列,並回傳依這類條件找到的所有不重複項目。
這個關鍵字很適合用來找出資料表中的類別資訊。
結果會是不重複的資料欄值清單。
如未使用 GROUP BY
,查詢會傳回全部 83,434,866 個資料列。GROUP BY
會輸出資料表中的不重複資料欄值。查看右下角即可確認這一點,該處會顯示 954 個資料列,代表共有 954 個不同的倫敦共享單車行程起點。
COUNT()
函式會傳回擁有相同條件 (例如資料欄值) 的資料列數量,與 GROUP BY
搭配使用時非常實用。
請將 COUNT
函式新增至上一個查詢,找出從各起點出發的行程數量。
輸出內容會顯示從各起點出發的行程數量。
SQL 也具有 AS
關鍵字,可用來建立資料表或欄的「別名」。「別名」是為傳回的資料表或欄指定的新名稱,使用 AS
即可指定任意名稱。
AS
關鍵字新增至上次執行的查詢,以便瞭解這項操作。請清除編輯器中的查詢,然後複製並貼上以下指令:您會在結果中看到右欄的名稱從 COUNT(*)
變更為 num_starts
。
如您所見,回傳資料表的 COUNT(*)
欄現在已設為別名 num_starts
。處理大型資料集時,這個關鍵字特別實用,畢竟忘記不夠精確的資料表或欄名稱是常有的事!
ORDER BY
關鍵字會按照您指定的條件或資料欄值,以遞增或遞減順序排列查詢傳回的資料。請將這個關鍵字新增至先前的查詢,然後執行下列操作:
下列指令分別為不同的查詢。請針對每個指令執行下列操作:
最後一項查詢的結果會列出起點,並按照從各起點出發的行程數量排序。
可以看到最多人將「Hyde Park Corner, Hyde Park」當做起點。不過,如果將該數除以總數 (671688/83434866),會發現不到 1% 的行程從這個站點出發。
請回答下列選擇題,加強您對目前所介紹概念的瞭解。盡力回答即可。
Cloud SQL 是全代管資料庫服務,可協助您輕鬆設定、維護及管理雲端中的關聯式 PostgreSQL 和 MySQL 資料庫。Cloud SQL 接受兩種格式的資料:傾印檔案 (.sql) 或 CSV 檔案 (.csv)。您將瞭解如何把 cycle_hire
資料表中的一部分資料匯出成 CSV 檔案,並上傳至 Cloud Storage,做為中繼位置。
請回到 BigQuery 控制台,上次執行的指令應如下所示:
在「查詢結果」部分,依序點選「儲存結果」>「CSV (本機檔案)」。系統將會展開下載作業,把這項查詢儲存為 CSV 檔案。請記住下載檔案的位置和名稱,稍後會用到。
清除查詢編輯器,複製下列指令,並在查詢編輯器中執行:
系統將傳回一個資料表,表中包含在各終點站結束的共享單車行程數量,並依行程數量從最多排到最少。
請前往 Cloud 控制台。您會在這裡建立儲存空間值區,並將稍早建立的檔案上傳至該值區。
依序選取「導覽選單」>「Cloud Storage」>「值區」,按一下「建立值區」。
為值區輸入不重複的名稱,並保持其他所有設定的預設狀態,接著按一下「建立」。
如果出現提示訊息,請在「系統會禁止公開存取
」對話方塊中按一下「確認」。
請點選下方的「Check my progress」,確認實驗室進度。若成功建立 bucket,就會看到評估分數。
現在應該會在 Cloud 控制台看到剛剛建立的 Cloud Storage bucket。
依序點選「上傳」>「上傳檔案」,然後選取含有 start_station_name
資料的 CSV 檔案。
然後點選「開啟」。請對 end_station_name
資料重複上述操作。
請在檔案的最右側依序點選三點圖示和「重新命名」,並將 start_station_name
檔案重新命名為 start_station_data.csv
。
請在檔案的最右側依序點選三點圖示和「重新命名」,並將 end_station_name
檔案重新命名為 end_station_data.csv
。
接著,在「值區詳細資料」頁面的「物件」清單中,您應該會看見 start_station_data.csv
和 end_station_data.csv
。
請點選「Check my progress」,確認工作已完成。如果已成功將 CSV 物件上傳到 bucket,就會看到評估分數。
在控制台中,依序選擇「導覽選單」>「SQL」。
依序按一下「建立執行個體」>「選擇 MySQL」。
將執行個體 ID 設為 my-demo。
在「密碼」欄位中輸入安全的密碼並記下來。
資料庫版本請選取「MySQL 8」。
「選擇 Cloud SQL 版本」請選取「Enterprise」。
「版本預設設定」請選取「開發」(4 個 vCPU、16 GB RAM、100 GB 儲存空間、單一可用區)。
將「區域」欄位設為
將「Multi zones (Highly available)」>「主要可用區」欄位設為
點選「建立執行個體」。
請點選下方的「Check my progress」,確認實驗室進度。如果已成功設定 Cloud SQL 執行個體,您會看見評估分數。
您有了運作中的 Cloud SQL 執行個體,現在請使用 Cloud Shell 指令列,在該執行個體中建立資料庫。
按一下控制台右上角的圖示,開啟 Cloud Shell。
執行下列指令,將專案 ID 設為環境變數:
如果出現 [Y/n] 提示訊息,請依序按下 Y 和 ENTER 鍵。
系統會顯示可以在瀏覽器中開啟的連結。在您登入 qwiklabs 帳戶的瀏覽器中開啟這個連結。登入之後,請複製系統顯示的驗證碼。接著,將這組驗證碼貼入 Cloud Shell。
my-demo
替換為相應的名稱:輸出內容應顯示如下:
Cloud SQL 執行個體擁有預先設定的資料庫,但您將自行建立資料庫來儲存倫敦共享單車的資料。
bike
的資料庫:您應該會收到下列輸出內容:
請點選「Check my progress」,確認工作已完成。如果已成功在 Cloud SQL 執行個體中建立資料庫,您會看見評估分數。
這個陳述式使用了 CREATE
關鍵字,但透過 TABLE
子句指定要建立的是資料表,而非資料庫。USE
關鍵字則指定要連結的資料庫。現在您擁有一個名為「london1」的資料表,其中包含「start_station_name」和「num」兩個資料欄。VARCHAR(255)
會指定變數長度字串欄可容納最多 255 個字元,INT
則是指資料欄類型為整數。
這兩項指令應會收到下列輸出內容:
因為您尚未載入資料,所以系統會顯示「empty set」(空資料集)。
請返回 Cloud SQL 控制台。接下來要將 start_station_name
和 end_station_name
CSV 檔案上傳至剛剛建立的 london1 和 london2 資料表。
start_station_data.csv
。按一下「選取」。bike
資料庫,然後輸入 london1
做為資料表名稱。為另一個 CSV 檔案執行相同的操作:
end_station_data.csv
。按一下「選取」。bike
資料庫,然後輸入 london2
做為資料表名稱。您現在應該已成功將兩個 CSV 檔案上傳為 bike
資料庫的資料表。
您應該會收到 955 行輸出內容,每行各有一個不重複的站點名稱。
您應該會收到 959 行輸出內容,同樣是每行各有一個不重複的站點名稱。
以下將再說明幾個 SQL 關鍵字,以便執行資料管理作業。第一個是 DELETE
關鍵字。
執行這兩項指令後,應該會收到以下輸出內容:
刪除的列為 CSV 檔案中的欄標頭。DELETE
關鍵字並不會移除檔案的第一列本身,但會從資料表刪除資料欄名稱 (此案例中為「num」) 含有特定值 (此案例中為「0」) 的「所有資料列」。您執行 SELECT * FROM london1;
和 SELECT * FROM london2;
查詢後,如果捲動至資料表頂端,會發現那些列已不存在。
您也可以使用 INSERT INTO
關鍵字將值插入資料表。
start_station_name
和 num
的值分別設為「test destination」和「1」:INSERT INTO
關鍵字需要指定一個資料表 (london1),新資料列所包含的資料欄則由第一個括弧內的字詞來指定 (本案例中為「start_station_name」和「num」)。「VALUES」子句後方的內容會插入為新資料列的值。
您應該會收到下列輸出內容:
如果執行 SELECT * FROM london1;
查詢,您會看到「london1」資料表底部出現一個新的列。
最後一個要學習的 SQL 關鍵字是 UNION
。這個關鍵字可將二或多個 SELECT
查詢輸出內容合併為結果集。您將會使用 UNION
合併「london1」和「london2」資料表各一部分的資料。
以下鏈結查詢將運用 UNION
運算子從兩個資料表提取特定資料,然後合併這些資料。
第一個 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 關鍵字,您已能查詢大規模的資料集,進而取得系統回傳的資料點並回答特定問題。
在本實驗室中,您學到了 SQL 的基礎知識,以及如何在 BigQuery 和 Cloud SQL 中使用關鍵字執行查詢。您也瞭解了專案、資料庫和資料表背後的核心概念。另外,還練習使用關鍵字操控及編輯資料、學會如何在 BigQuery 讀取資料,並練習對資料表執行查詢。瞭解如何在 Cloud SQL 建立執行個體之後,您試著將一部分資料轉移至資料庫中的資料表。最後,您在 Cloud SQL 鏈結並執行查詢,得出有關倫敦共享單車起點站和終點站的有趣結論。
透過下列 Google Cloud 技能重點加強實驗室,持續學習和練習使用 Cloud SQL 和 BigQuery。
閱讀由 O'Reilly Media, Inc. 出版的《Data Science on the Google Cloud Platform, 2nd Edition》(Google Cloud Platform 的數據資料學,第 2 版),進一步瞭解數據資料學。
協助您瞭解如何充分運用 Google Cloud 的技術。我們的課程會介紹專業技能和最佳做法,讓您可以快速掌握要領並持續進修。我們提供從基本到進階等級的訓練課程,並有隨選、線上和虛擬課程等選項,方便您抽空參加。認證可協助您驗證及證明自己在 Google Cloud 技術方面的技能和專業知識。
使用手冊上次更新日期:2024 年 10 月 16 日
實驗室上次測試日期:2024 年 10 月 16 日
Copyright 2025 Google LLC 保留所有權利。Google 和 Google 標誌是 Google LLC 的商標,其他公司和產品名稱則有可能是其關聯公司的商標。
此内容目前不可用
一旦可用,我们会通过电子邮件告知您
太好了!
一旦可用,我们会通过电子邮件告知您
One lab at a time
Confirm to end all existing labs and start this one