GCP 筆記: Introduction to SQL for BigQuery and Cloud SQL

SQL 基礎

SQL 是用來查詢結構化的資料集的語言，資料集有明確的規則。

資料庫是同時儲存一個或多個資料表的集合。

大多數的情況，SQL 是用來查詢部分資料表，以及資料表間聯結 (join) 的資料。

SELECT 與 FROM

SQL 有預先定義的語法，使用者用制式的語法向資料庫「問問題」。

其中最基礎的就是 SELECT 與 FROM。

• SELECT 是用來提取想要選取的欄位。
• FROM 用來指定想要從哪 (幾) 張資料表中提取資料。

例如「從 example_table 資料表中，提取 USER 欄位的資料」。

SELECT USER FROM example_table

或者「從 example_table 資料表中，提取 USER 及 GENDER 欄位的資料」。

SELECT USER, GENDER FROM example_table

WHERE

WHERE 是用來篩選資料條件的語法，譬如：「從 example_table 資料表中，提取 GENDER 是 MALE 的 USER 資料」

SELECT USER FROM example_table WHERE SHIPPED='YES'

BigQuery 主控台

從導覽列中點選 [BigQuery]，點擊 [新增資料] > [探索公開資料集]，取得公開的資料。

Qwiklab 使用的範例是 London Bicycle Hires 這個資料集，搜尋後，點擊 [查看資料集 (View Dataset)]。此時編輯區會加入名為 bigquery-public-data 的新專案。

展開 bigquery-public-data，尋找 london-bicycles 資料集，選擇 cycle-hires 資料表。點擊編輯區的 [Preview]，可以以表格的方式預覽資料。

接著到編輯器中，查詢自行車還車點的名稱。

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

總共有 2,400 多萬筆的資料。

利用 SELECT * 選取所有欄位，並篩選查詢「超過 20 分鐘行程」的借還紀錄。

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

查詢出來共有 730 多萬筆資料。換句話說，倫敦有 30% 以上的共享單車租用超過 20 分鐘。

GROUP BY

利用 GROUP BY 語法，可以將資料依據欄位的微一值進行分組。

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

上述的例子便能透過 start_station_name 找到倫敦總共有 880 個共享單車的起租點。

COUNT

COUNT() 函數功能可以用來回傳共通特性的計次結果，常與 GROUP BY 一起使用。

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

執行後可以看到起租點的租借次數。

AS

AS 語法是用來製作別名 (alias) 用的。換句話說，就是將欄位名稱重新命名為新的名稱。配合 COUNT() 函數可以這樣執行。

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

執行結果中，原本未定義的欄位名稱，變成 num_starts。

ORDER BY

用來排序查詢結果，有升序 (ASC)、降序 (DESC) 之分。

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;

上述的指令會回傳以下結果：

• Return a table that contains the number of bikeshare rides that begin in each starting station, organized alphabetically by the starting station.
• Return a table that contains the number of bikeshare rides that begin in each starting station, organized numerically from lowest to highest.
• Return a table that contains the number of bikeshare rides that begin in each starting station, organized numerically from highest to lowest.

執行 Cloud SQL

先將最後執行的查詢結果儲存成 CSV 檔備用。接著執行下列指令：

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

將查詢結果儲存成 CSV 檔並下載。

建立 Cloud Storage 貯體，並將下載的 CSV 檔個別命名為 start_station_name.csv 與 end_station_name.csv 後，上傳至貯體。

從導覽選單中，選擇 [SQL]。接著建立新的實體，選擇 [MySQL]，輸入資料庫名稱與 root 的密碼後，建立新的實體。

利用 Cloud Shell 建立資料庫與資料表

gcloud sql connect  qwiklabs-demo --user=root

輸入密碼後，便會進入 MySQL 的指令介面。建立資料庫 bike，接著用 USE 指令選擇資料庫，並 CREATE TABLE 建立新的資料表 london1，加入欄位 start_station_name 與 num。

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

接著建立資料表 london2，加入欄位 end_station_name 與 num。

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

可以嘗試查詢兩個資料表的結果，雖然應該會回傳 Empty。

SELECT * FROM london1;
SELECT * FROM london2;

從主控台中，回到實體的 Overview，點選 IMPORT，從 Cloud Storage 貯體將資料匯入到指定的資料表中。

DELETE

利用 DELETE FROM 語法，將符合條件的資料列刪除。

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

INSERT INTO

利用 INSERT INTO 語法，插入新的資料列。

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

UNION

利用 UNION 可以將兩個搜尋結果合併。

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;