arrow_back

在 BigQuery 和 Cloud SQL 中使用 SQL 簡介

加入 登录

在 BigQuery 和 Cloud SQL 中使用 SQL 簡介

1 个小时 15 分钟 免费

GSP281

Google Cloud 自修研究室標誌

總覽

SQL (結構化查詢語言) 是資料作業的標準語言,可用於從結構化資料集中獲得問題解答和取得深入分析。這種語言常用於資料庫管理,可執行的工作包括將交易記錄寫入關聯資料庫和進行 PB 規模資料分析。

本研究室將介紹 SQL,讓您準備好執行數據資料學主題方面的許多 Google Cloud 技能重點加強研究室和任務。本研究室分為兩個部分:前半部分將說明基本的 SQL 查詢關鍵字,並讓您使用內含倫敦共享單車資訊的公開資料集,在 BigQuery 控制台執行這些關鍵字。

在後半部分,您將瞭解如何把倫敦共享單車資料集的一部分資料匯出成 CSV 檔案,並上傳至 Cloud SQL。之後,您將學習如何使用 Cloud SQL 建立和管理資料庫和資料表,並在最後實際練習透過其他 SQL 關鍵字來控管和編輯資料。

課程內容

在本研究室中,您將瞭解以下內容:

  • 區分資料庫、資料表和專案。
  • 使用 SELECTFROMWHERE 關鍵字執行簡單的查詢。
  • 認識 BigQuery 控制台的不同元件和階層架構。
  • 將資料庫和資料表載入 BigQuery。
  • 對資料表執行簡單的查詢。
  • 瞭解關鍵字 COUNTGROUP BYASORDER BY
  • 執行和鏈結上述指令,從資料集提取有意義的資料。
  • 將一部分資料匯出成 CSV 檔案,並將該檔案儲存至新的 Cloud Storage 值區。
  • 建立新的 Cloud SQL 執行個體,並將匯出的 CSV 檔案載入為新的資料表。
  • 在 Cloud SQL 中執行 CREATE DATABASECREATE TABLEDELETEINSERT INTOUNION 查詢。

事前準備

務必注意:請先登出個人或企業的 Gmail 帳戶,再開始執行本研究室。

這是入門等級的研究室,無論是只有些許 SQL 使用經驗或完全沒有經驗都不成問題。建議您先熟悉 Cloud Storage 和 Cloud Shell 服務,但這並非強制要求。本研究室將說明以 SQL 讀取和撰寫查詢的基本概念,並讓您在 BigQuery 和 Cloud SQL 中加以應用。

開始本研究室前,請評估您對 SQL 的熟悉程度。以下為較具挑戰性的研究室,可讓您在更進階的用途中應用相關知識。

準備好後,請向下捲動,並按照下列步驟設定研究室環境。

設定和需求

點選「Start Lab」(開始研究室) 按鈕前的須知事項

請詳閱以下操作說明。研究室活動會計時,而且中途無法暫停。點選「Start Lab」(開始研究室) 後就會開始計時,讓您瞭解有多少時間可以使用 Google Cloud 資源。

您將在真正的雲端環境中完成實作研究室活動,而不是在模擬或示範環境。為達此目的,我們會提供新的暫時憑證,讓您用來在研究室活動期間登入及存取 Google Cloud。

如要完成這個研究室活動,請先確認:

  • 您可以使用標準的網際網路瀏覽器 (Chrome 瀏覽器為佳)。
注意:請使用無痕模式或私密瀏覽視窗執行此研究室。這可以防止個人帳戶和學生帳戶之間發生任何衝突,避免個人帳戶產生額外費用。
  • 是時候完成研究室活動了!別忘了,活動一開始將無法暫停。
注意:如果您已有個人 Google Cloud 帳戶或專案,請勿將其用於本研究室,以免您的帳戶產生額外費用。

如何開始研究室及登入 Google Cloud 控制台

  1. 按一下「Start Lab」(開始研究室) 按鈕。如果研究室會產生費用,畫面中會出現選擇付款方式的彈出式視窗。左側的「Lab Details」(研究室詳細資料) 面板會顯示下列項目:

    • 「Open Google Console」(開啟 Google 控制台) 按鈕
    • 剩餘時間
    • 必須在這個研究室中使用的暫時憑證
    • 完成這個研究室所需的其他資訊 (如有)
  2. 按一下「Open Google Console」(開啟 Google 控制台)。接著,研究室會啟動相關資源並開啟另一個分頁,當中會顯示「Sign in」(登入) 頁面。

    提示:您可以在不同的視窗中並排開啟分頁。

    注意事項:如果頁面中顯示了「Choose an account」(選擇帳戶) 對話方塊,請按一下「Use Another Account」(使用其他帳戶)
  3. 如有必要,請複製「Lab Details」(研究室詳細資料) 面板中的使用者名稱,然後貼到「Sign in」(登入) 對話方塊。按一下「Next」(下一步)

  4. 複製「Lab Details」(研究室詳細資料) 面板中的密碼,然後貼到「Welcome」(歡迎使用) 對話方塊。按一下「Next」(下一步)

    重要注意事項:請務必使用左側面板中的憑證,而非 Google Cloud 技能重點加強的憑證。 注意事項:如果使用自己的 Google Cloud 帳戶來進行這個研究室,可能會產生額外費用。
  5. 按過後續的所有頁面:

    • 接受條款及細則。
    • 由於這是臨時帳戶,請勿新增救援選項或雙重驗證機制。
    • 請勿申請免費試用。

Cloud 控制台稍後會在這個分頁中開啟。

注意事項:按一下畫面左上方的導覽選單,即可在選單中查看 Google Cloud 產品與服務的清單。「導覽選單」圖示

工作 1:SQL 基本概念

資料庫和資料表

如前所述,SQL 可讓您從「結構化資料集」取得資訊。結構化資料集具有明確的規則和格式,且經常會整理成資料表,也就是以列和欄的格式呈現資料。

非結構化資料的例子包括圖片檔。非結構化資料無法使用 SQL 執行作業,也無法儲存在 BigQuery 資料集或資料表中 (至少不能以原生方式儲存)。舉例來說,如要使用圖片資料,就需要透過 Cloud Vision 等服務,或是直接透過其 API

以下為結構化資料集的例子,也就是簡單的資料表:

User

Price

Shipped

Sean

$35

Yes

Rocky

$50

No

如果曾使用過 Google 試算表,應該不會對上述格式感到陌生。我們可以看到,這個資料表有「User」、「Price」和「Shipped」等資料欄,還有兩列已填入資料欄值的資料。

基本上,資料庫就是「一或多個資料表的集合」。雖然 SQL 屬於結構化資料庫管理工具,但在許多情況下 (和本研究室中),您只會在一或數個彙整的資料表中執行查詢,而非查詢整個資料庫。

SELECT 和 FROM

SQL 具有語音性質,若能在執行查詢前先釐清想透過查詢資料回答的問題,向來都很有幫助 (除非只是想隨意探索)。

SQL 擁有預先定義的關鍵字,可用來將問題翻譯成類似英文的 SQL 語法,讓資料庫引擎傳回想要的答案。

最常用的關鍵字是 SELECTFROM

  • 如要指定從資料集提取哪些欄位,請使用 SELECT
  • 如要指定從哪一個或哪些資料表提取資料,請使用 FROM

以下例子或許能協助理解。假設我們擁有下列 example_table 資料表,表中包含「USER」、「PRICE」和「SHIPPED」等欄。

範例資料表

如果只要從「USER」欄提取資料,使用 SELECTFROM 執行下列查詢即可:

SELECT USER FROM example_table

若執行上述指令,就會選取 example_tableUSER 欄的所有名稱。

您也可以使用 SQL 的 SELECT 關鍵字選取多個欄。如要提取「USER」和「SHIPPED」欄的資料,請修改上一個查詢,將另一個資料欄值新增至 SELECT 查詢 (務必使用逗號分隔!):

SELECT USER, SHIPPED FROM example_table

執行上述指令即可從記憶體中擷取 USERSHIPPED 的資料:

範例資料表

就這樣,您學會了兩個基本的 SQL 關鍵字!接下來可以嘗試更有趣的操作。

WHERE

WHERE 關鍵字是另一種 SQL 指令,可以在資料表中篩選特定資料欄值。舉例來說,若要從 example_table 提取包裹已出貨的使用者名稱,可以在查詢中加上 WHERE,如下:

SELECT USER FROM example_table WHERE SHIPPED='YES'

執行上述指令後,系統就會從記憶體傳回所有包裹已出貨的使用者:

範例資料表

現在您已對 SQL 核心關鍵字擁有基本的瞭解,就能在 BigQuery 控制台執行這些類型的查詢以應用所學。

測試您的瞭解程度

請回答下列選擇題,加強您對目前所介紹概念的瞭解。請按照您的理解盡力回答。

工作 2:探索 BigQuery 控制台

BigQuery 模式

BigQuery 是在 Google Cloud 上執行的全代管 PB 規模資料倉儲系統,可供資料分析師和數據資料學家迅速查詢和篩選大型資料集、匯總結果和執行複雜的作業,而不必費心設定和管理伺服器。這套系統的呈現形式為指令列工具 (已預先安裝在 Cloud Shell) 或網路控制台,兩種形式都能用來管理和查詢 Google Cloud 專案中的資料。

在本研究室中,您將使用網路控制台執行 SQL 查詢。

開啟 BigQuery 控制台

  1. 在 Google Cloud 控制台中,依序選取「導覽選單」>「BigQuery」

接著,畫面中會顯示「歡迎使用 Cloud 控制台中的 BigQuery」訊息方塊,當中會列出快速入門導覽課程指南的連結和版本資訊。

  1. 點選「完成」

BigQuery 控制台會隨即開啟。

讓我們花一些時間記下使用者介面上的一些重要功能。控制台右側為查詢「編輯器」,這是撰寫和執行 SQL 指令 (如前述範例) 的位置。下方是「查詢記錄」,會列出先前執行的查詢。

控制台左側窗格是「導覽面板」。除了明確易懂的「查詢記錄」、「已儲存的查詢」和「工作記錄」以外,還有「Explorer」分頁。

「Explorer」分頁中最高層級的資源包含 Google Cloud 專案,這些專案就像您在每個 Google Cloud 技能重點加強研究室中登入使用的臨時 Google Cloud 專案。您可以在控制台和最後一張螢幕截圖中看到,我們只會將專案存放在「Explorer」分頁。如果點選專案名稱旁的箭頭,畫面上不會顯示任何內容。

這是因為專案內未含有資料集或資料表,沒有任何資料可供查詢。先前我們說明過,資料表內含在資料集中。在 BigQuery 將資料新增至專案時,請注意「資料表會內含在資料集中,而資料集內含在專案中」。現在您已進一步瞭解「專案 > 資料集 > 資料表」的模式和控制台的細節,可以上傳一些可供查詢的資料了。

上傳可供查詢的資料

在本節中,您會將一些公開資料提取至專案,以便在 BigQuery 實際執行 SQL 指令。

  1. 按一下「+ 新增資料」

  2. 依序選取「釘選專案」>「輸入專案名稱」

  3. 將專案名稱設為 bigquery-public-data

  4. 點選「釘選」按鈕。

請特別留意,在這個新分頁中,您仍然是在研究室專案之外操作。您只是將內含資料集和資料表且可公開存取的專案提取至 BigQuery 進行分析,並未「切換」至該專案。所有工作和服務都仍與您的 Google Cloud 技能重點加強帳戶相連結。查看在控制台頂端附近的專案欄位即可確認這一點:

專案欄位顯示 Google Cloud 技能重點加強專案的名稱

  1. 在搜尋列輸入「London Bicycles Hires」(倫敦單車租借),然後按一下「查看資料集」

  2. 現在您可以存取下列資料:

  • Google Cloud 專案 → bigquery-public-data

  • 資料集 → london_bicycles

  1. 按一下「london bicycles」(倫敦單車) 資料集以展開相關聯的資料表

  • 資料表 → cycle_hire
  • 資料表 → cycle_stations

在本研究室中,我們將使用 cycle_hire 的資料。請開啟 cycle_hire 資料表,然後點選「預覽」分頁。頁面應類似於以下畫面:

cycle_hire 資料表,「預覽」分頁

請查看各欄和填入各列的值。現在您可以對 cycle_hire 資料表執行一些 SQL 查詢。

在 BigQuery 中執行 SELECT、FROM 和 WHERE

現在您已具備 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`;
  1. 然後按一下「執行」

大約 20 秒後,系統應會傳回 24369201 列資料,其中含有您查詢的單一資料欄 end_station_name

我們來找找看共有多少持續 20 分鐘或以上的單車行程吧!

  1. 請清除編輯器中的查詢,然後使用 WHERE 關鍵字執行下列查詢:

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

這項查詢可能需要幾分鐘才能完成執行。

SELECT * 會回傳資料表中的所有資料欄值。時間是以秒數計算,因此使用的數值是 1200 (60 * 20)。

您可以在右下角看到系統回傳 7,334,890 個資料列。把這個數字除以總數 (7334890/24369201),可以得出將近 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;
  1. 按一下「執行」

結果會是不重複的資料欄值清單。

若未使用 GROUP BY,這項查詢會完整傳回 24,369,201 個資料列。GROUP BY 會輸出資料表中的不重複資料欄值。查看右下角即可確認這一點。您會看到資料列共 880 個,這表示總共有 880 個不同的倫敦共享單車行程起點。

COUNT

COUNT() 函式會傳回擁有相同條件 (例如資料欄值) 的資料列數量,與 GROUP BY 搭配使用時非常實用。

請將 COUNT 函式新增至上一個查詢,找出從各起點出發的行程數量。

  • 請清除編輯器中的查詢,複製及貼上以下指令,然後按一下「執行」

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;
  1. 按一下「執行」

您會在結果中看到右欄的名稱從 COUNT(*) 變更為 num_starts

如您所見,回傳資料表的 COUNT(*) 欄現在已設為別名 num_starts。處理大型資料集時,這個關鍵字特別實用,畢竟忘記不夠精確的資料表或欄名稱是常有的事!

ORDER BY

ORDER BY 關鍵字會根據指定的條件或資料欄值,以遞增或遞減的順序排列系統傳回的查詢資料。我們將在上一個查詢新增這個關鍵字,以執行下列操作:

  • 讓系統傳回資料表,表中包含從各起點站出發的行程數量,並依起點站的字母順序排列。
  • 讓系統傳回資料表,表中包含從各起點站出發的行程數量,並依數字大小從最低排至最高。
  • 讓系統傳回資料表,表中包含從各起點站出發的行程數量,並依數字大小從最高排至最低。

下列指令分別為不同的查詢。請針對每一項指令執行下列操作:

  1. 清除查詢編輯器。

  2. 將指令複製及貼上到查詢編輯器。

  3. 按一下「執行」。查看結果。

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」(貝爾格羅夫街,國王十字) 出發的行程數量最多。不過,若將這個數量除以總數 (234458/24369201),會發現少於 1% 的行程從這個站點出發。

測試您的瞭解程度

請回答下列選擇題,以加強您對目前所介紹概念的瞭解。請按照您的理解盡力回答。

工作 4:使用 Cloud SQL

將查詢匯出成 CSV 檔案

Cloud SQL 是全代管資料庫服務,可協助您輕鬆設定、維護及管理雲端中的關聯式 PostgreSQL 和 MySQL 資料庫。Cloud SQL 接受兩種格式的資料:傾印檔案 (.sql) 或 CSV 檔案 (.csv)。您將瞭解如何把 cycle_hire 資料表中的一部分資料匯出成 CSV 檔案,並上傳至 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. 在「查詢結果」部分,依序點選「儲存結果」>「CSV (本機檔案)」。系統將會展開下載作業,把這項查詢儲存為 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;

系統將傳回一個資料表,表中包含在各終點站結束的共享單車行程數量,並依行程數量從最多排到最少。

  1. 在「查詢結果」部分,依序點選「儲存結果」>「CSV (本機檔案)」。系統將會展開下載作業,把這項查詢儲存為 CSV 檔案。請記下所下載檔案的位置和名稱,下一節將會用到。

將 CSV 檔案上傳至 Cloud Storage

  1. 請前往 Cloud 控制台。您會在這裡建立儲存空間值區,並將稍早建立的檔案上傳至該值區。

  2. 請依序選取「導覽選單」>「Cloud Storage」>「值區」,然後按一下「建立值區」

注意事項:如果出現提示訊息,請針對「尚未儲存工作」點選「離開」
  1. 請為值區輸入不重複的名稱並保持其餘所有設定的預設狀態,然後按一下「建立」

測試已完成的工作

請點選下方的「Check my progress」(檢查我的進度),確認您的研究室進度。若已成功建立值區,就會看到評估分數。

建立 Cloud Storage 值區。

現在您應該會在 Cloud 控制台看到剛剛建立的 Cloud Storage 值區。

  1. 請按一下「上傳檔案」,然後選取含有 start_station_name 資料的 CSV 檔案。

  2. 然後點選「開啟」。請對 end_station_name 資料重複上述操作。

  3. 請在檔案的最右側依序點選三點圖示和「重新命名」,並將 start_station_name 檔案重新命名為 start_station_data.csv

  4. 請在檔案的最右側依序點選三點圖示和「重新命名」,並將 end_station_name 檔案重新命名為 end_station_data.csv

您應該會在「值區詳細資料」頁面的「物件」清單上,看到 start_station_name.csvend_station_data.csv

測試已完成的工作

請點選「Check my progress」(檢查我的進度) 確認工作已完成。如果已成功將 CSV 物件上傳到值區,就會看到評估分數。

將 CSV 檔案上傳至 Cloud Storage。

工作 5:建立 Cloud SQL 執行個體

在控制台中,依序選擇「導覽選單」>「SQL」

  1. 依序點選「建立執行個體」>「Choose MySQL」(選擇 MySQL)

  2. 將執行個體 ID 設定為 qwiklabs-demo

  3. 在「密碼」欄位輸入安全的密碼 (並記下這組密碼!)。

  4. 選擇資料庫版本「MySQL 5.7」

  5. 將「Multiple zones (Highly available)」(多可用區 (可用性高)) 欄位設為

  6. 按一下「建立執行個體」

執行個體可能需要幾分鐘才能建立完成。建立完成後,執行個體名稱旁會顯示綠色勾號。

  1. 對 Cloud SQL 執行個體按一下。SQL 的「總覽」頁面將會開啟。

測試已完成的工作

如要確認您的研究室進度,請點選下方的「Check my progress」(檢查我的進度)。如果已成功設定 Cloud SQL 執行個體,您就會看見評估分數。

建立 Cloud SQL 執行個體。

工作 6:在 Cloud SQL 新增查詢

CREATE 關鍵字 (資料庫和資料表)

現在您已擁有執行中的 Cloud SQL 執行個體,請使用 Cloud Shell 指令列在其中建立資料庫。請複製下方的 Cloud Shell 連結,然後貼入新的瀏覽器無痕分頁。

https://shell.cloud.google.com/?show=terminal

設定專案 ID:

gcloud config set project [PROJECT_ID]

在 Cloud Shell 中建立資料庫

  1. 請在 Cloud Shell 中執行下列指令,在不開啟瀏覽器的情況下設定驗證功能

gcloud auth login --no-launch-browser

系統會顯示可以在瀏覽器中開啟的連結。在您登入 qwiklabs 帳戶的瀏覽器中開啟這個連結。登入之後,請複製系統顯示的驗證碼。在 Cloud Shell 中貼上這組驗證碼。

設定專案

gcloud config set project [PROJECT_ID]
  1. 請執行下列指令來連線至 SQL 執行個體。如果您使用不同的執行個體名稱,請將 qwiklabs-demo 替換為正確的名稱。

gcloud sql connect qwiklabs-demo --user=root --quiet 注意事項:系統可能需要幾分鐘才能連線至執行個體。
  1. 出現提示訊息時,請輸入您為執行個體設定的根密碼。

輸出內容應類似如下:

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 494 Server version: 5.7.14-google-log (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 執行個體擁有預先設定的資料庫,但您將自行建立資料庫來儲存倫敦共享單車的資料。

  1. 請在 MySQL 伺服器提示中執行下列指令,建立名為 bike 的資料庫:

CREATE DATABASE bike;

您應該會收到下列輸出內容:

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

測試已完成的工作

請點選「Check my progress」(檢查我的進度) 來確認工作已完成。如果已成功在 Cloud SQL 執行個體中建立資料庫,就會看到評估分數。

建立資料庫。

在 Cloud Shell 建立資料表

  1. 請執行下列指令,在 bike 資料庫內建立資料表:

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

這個陳述式使用了 CREATE 關鍵字,但透過 TABLE 子句指定要建立的是資料表,而非資料庫。USE 關鍵字則指定要連結的資料庫。現在您擁有一個名為「london1」的資料表,其中包含「start_station_name」和「num」兩個資料欄。VARCHAR(255) 會指定變數長度字串欄可容納最多 255 個字元,INT 則是指資料欄類型為整數。

  1. 請執行下列指令,建立另一個名為「london2」的資料表:

USE bike; CREATE TABLE london2 (end_station_name VARCHAR(255), num INT);
  1. 請確認空的資料表已建立完成,然後在 MySQL 伺服器提示中執行下列指令:

SELECT * FROM london1; SELECT * FROM london2;

這兩項指令應會收到下列輸出內容:

Empty set (0.04 sec)

因為您尚未載入資料,所以系統會顯示「empty set」(空資料集)。

將 CSV 檔案上傳至資料表

請返回 Cloud SQL 控制台。接下來要將 start_station_nameend_station_name CSV 檔案上傳至剛剛建立的 london1 和 london2 資料表。

  1. 在 Cloud SQL 執行個體頁面,按一下「匯入」
  2. 在 Cloud Storage 檔案欄位按一下「瀏覽」,然後依序點選值區名稱附近的箭頭和 start_station_data.csv。按一下「選取」
  3. 將檔案格式設為「CSV」
  4. 選取 bike 資料庫,然後輸入「london1」做為資料表。
  5. 按一下「匯入」

請為另一個 CSV 檔案執行相同的操作。

  1. 在 Cloud SQL 執行個體頁面,按一下「匯入」
  2. 按一下 Cloud Storage 檔案欄位中的「瀏覽」,然後依序點選值區名稱對面的箭頭和 end_station_data.csv。按一下「選取」
  3. 將檔案格式設為「CSV」
  4. 選取 bike 資料庫,然後輸入「london2」做為資料表。
  5. 按一下「匯入」

現在您應該已將兩個 CSV 檔案上傳為 bike 資料庫的資料表。

  1. 請返回 Cloud Shell 工作階段,然後在 MySQL 伺服器提示中執行下列指令,以查看 london1 的內容:

SELECT * FROM london1;

您應該會收到 881 行輸出內容,每一行分別是不同的站點名稱。

  1. 請執行下列指令,確認資料已填入 london2:

SELECT * FROM london2;

您應該會收到 883 行輸出內容,每一行分別是不同的站點名稱。

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 中插入新的列,並將 start_station_namenum 的值分別設為「test destination」和「1」:

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」欄的值,依字母和遞減的順序排列最終的聯集資料集。

您應該會收到下列輸出內容:

擁有 top_stations 和 num 欄的輸出資料表

如您所見,13/14 的站點都是共乘單車行程的熱門起點站和終點站。運用一些基本的 SQL 關鍵字,您已能查詢大規模的資料集,進而取得系統回傳的資料點並回答特定問題。

恭喜!

在本研究室中,您學到了 SQL 的基礎知識,以及如何在 BigQuery 和 Cloud SQL 中使用關鍵字執行查詢。您也瞭解了專案、資料庫和資料表背後的核心概念。此外,還練習使用關鍵字控管和編輯資料,學會如何將資料集載入 BigQuery,並實際對資料表執行查詢。瞭解如何在 Cloud SQL 建立執行個體後,您便練習將一部分的資料轉移到資料庫中的資料表。最後,您在 Cloud SQL 中鏈結並執行查詢,做出了關於倫敦共享單車起點站和終點站的有趣結論。

完成您的任務

本自修研究室是下列任務的一部分:「Google Cloud 的數據資料學」、「科學資料處理」、「Cloud SQL」、「給資料分析師的 BigQuery 基本概念」、「NCAA® March Madness®:使用 Google Cloud 預測賽程表」、「雲端工程」、「Data Catalog 基礎知識」和「針對零售業應用情況運用 BQML 的分類、迴歸、需求預測功能」。任務是一系列相關聯的研究室,這些研究室構成一個完整的學習路徑。完成任務後即可獲得徽章,以表彰您的成就。您可以公開展示徽章,並連結至線上履歷或社群媒體帳戶。歡迎報名參加任何包含本研究室的任務,立即取得完成學分。請參閱「Google Cloud 技能重點加強目錄」來查看所有可參加的任務。

後續步驟/瞭解詳情

透過下列 Google Cloud 技能重點加強研究室,持續學習和練習使用 Cloud SQL 和 BigQuery。

閱讀《Google Cloud Platform 的數據資料學,第 2 版:O'Reilly Media, Inc.》,進一步瞭解數據資料學。

Google Cloud 教育訓練與認證

協助您瞭解如何充分運用 Google Cloud 的技術。我們的課程會介紹專業技能和最佳做法,讓您可以快速掌握要領並持續進修。我們提供從基本到進階等級的訓練課程,並有隨選、線上和虛擬課程等選項,方便您抽空參加。認證可協助您驗證及證明自己在 Google Cloud 技術方面的技能和專業知識。

使用手冊上次更新日期:2022 年 10 月 3 日

研究室上次測試日期:2022 年 10 月 3 日

Copyright 2022 Google LLC 保留所有權利。Google 和 Google 標誌是 Google LLC 的商標,其他公司和產品名稱則有可能是其關聯公司的商標。