BigQuery SQL 語法基本操作教學_part 1

BigQuery CRUD 操作介紹

BigQuery 的 CRUD（Create、Read、Update、Delete）操作都可以透過 SQL 指令完成。

• Create：SQL INSERT statement
• Read：SQL SELETE statement
• Update：SQL UPDATE/MERGE，雖然 BigQuery 支援 update，但這畢竟是資料分析的 solution，建議還是不要經常做。
• Delete：SQL DELETE

BigQuery CRUD 的 SQL 基本操作教學

因為是資料分析的工具，故會用最多的就是「Read」功能，現行 BigQuery 支援 SQL:2011 的語法。這次的範例我們用 BigQuery 的紐約市的自行車租借公開資料。一開始我們可以檢視要分析的資料，在 BigQuery 的左下表列（圖一）會看到 BigQuery 的公開資料 bigquery-public-data，這些都是公開可以分析的資料。另外補充說明其資料庫結構。

• 第一層是 project name，在這裡 project ID 就是 bigquery-public-data
• 第二層是 dataset name ，在這裡 dataset name 就是 new_york_citibike（圖二）
• 第三層是 table name，在這裡 table name 就是 citibike_stations 及citibike_trips（圖二）

接下來我們檢視一下我們所要分析的資料，在 BigQuery 的每一個 table 中，我們都可以看到 schema、Details、Preview 這三項資料（圖二至四）。

• schema：這一段就是 BigQuery 這一個 table 的 schema，就跟你是使用一般的資料庫一樣的結構。
• Details：這一部分對你來說比較重要的資訊，這個 table 的大小與資料的總比數。
• Preview：顧名思義可以預覽這個 table 的內容資料。

首先我們先對這一個citibike_trips table做簡單的查詢，語法如下。
這裡要特別提一下 from之後的資料來源。BQ需要指定完整資料來源路徑，之前有提到BQ的資料庫有三層結構。所以完整路經的名稱規則為
ProjectID.DataSetName.TableName
這個範例中project ID 有 這個符號是因為BQ無法辨認含有 - 符號的名稱。所以需要用單引號把project ID框起來.

select
  gender, tripduration

from

  `bigquery-public-data`.new_york_citibike.citibike_trips

  limit 5

如上圖，我們select gender / tripduration這兩個欄位，並show出5筆資料。在這邊我們可以看到。BQ在您輸入SQL語法時就會幫你驗證語法的正確性，若語法不正確BQ就會show出紅色的警告標示而不會是如上圖的綠色的驗證成功的圖示。同時，在右手邊你可以看到這一次資料處理量。BQ的收費標準是以“資料儲存量”與“資料處理量”作為收費標準。也可以看到這一次的查詢BQ使用了多少時間處理，這一次處理759MB的資料花了 0.5秒的時間.

使用過一般傳統的RDBMS的朋友都知道，join table是經常要做的事但這個動作會讓資料庫效能低下。由於BQ的特性，我們強烈建議您若要將RDBMS的資料匯入到BQ中請將您的RDBMS資料做denormalized form。

第二個例子，將欄位做別名。範例如下

select

  gender, tripduration as rental_duration

from

  `bigquery-public-data`.new_york_citibike.citibike_trips

  limit 5

這時我們會看到底下的欄位名稱已經被更改

當然我們也可以對欄位做計算，但這個計算的欄位若不給它別名。哪BQ就會自動給出一個別名。範例如下

select

  gender, tripduration/60

from

  `bigquery-public-data`.new_york_citibike.citibike_trips

  limit 5

綜合上面的兩個例子，語法如下

select

  gender, tripduration/60 as duration_minutes

from

  `bigquery-public-data`.new_york_citibike.citibike_trips

  limit 5

使用 “where”語法，可以包含Boolean條件來做filter，範例如下

select

  gender, tripduration/60 as duration_minutes

from

  `bigquery-public-data`.new_york_citibike.citibike_trips

where tripduration < 600

  limit 5

這個範例我們只要 tripduration小於600秒的，我們也可以在增加一些filter的選項。
範例如下

select

  gender, tripduration/60 as duration_minutes

from

  `bigquery-public-data`.new_york_citibike.citibike_trips

where tripduration >=300 and tripduration < 600 and gender = ‘female’

  limit 5

上面這個範例我們使用了多個條件來filter並用AND。此範例我們要小於600秒並大於等於300秒且是女性的資料。

當然也可以用 and not來排除我們不需要的條件，例如

select

  gender, tripduration/60 as duration_minutes

from

  `bigquery-public-data`.new_york_citibike.citibike_trips

where tripduration < 600 and not gender = ‘female’

  limit 5

上面的範例中，我們filter小於600秒並且不是女性的租借者的資料，另外我們也可以在where條件下再次做filter，範例如下

select

  gender, tripduration/60 as duration_minutes

from

  `bigquery-public-data`.new_york_citibike.citibike_trips

where (tripduration < 600 and not gender = ‘female’) or gender = ‘male’

  limit 5

這格範例中，我們使用or 。filter的資料是(小於600秒而且不是女性的) 或是男性，因為性別欄位中的資料可能有unknow的存在。另外要說明一下where的欄位資料必須是實際存在的欄位而不是別名名稱。以之前的別名範例來說應該如下寫法

select

  gender, tripduration/60 as minutes

from

  `bigquery-public-data`.new_york_citibike.citibike_trips

where (tripduration / 60) < 10

  limit 5

在BQ方案中， select * 整個table 是強烈建議不要做的，因為效能會非常差而且剛剛提過BQ的計費方式是以資料處理量來計費，經常select * 整個資料庫會讓你的費用爆增。在前面的文章(BigQuery—-Google Data Warehouse 解決方案)有簡介過BQ是以column base為基礎的架構，所以在select時請選擇你要分析的欄位。

除了select * 整個資料庫外，我們也可以用except來排除我們不要分析的欄位。這樣就可以把費用在降低。範例如下

select

  * except(short_name, last_reported)

from

  `bigquery-public-data`.new_york_citibike.citibike_stations

where name like ‘%Riverside%’

在上面的範例，我們依然使用了select * ，但我們使用了except排除了我們不要分析的欄位。

我們也可以用replace來替原來的資料做計算，範例如下

select

  * replace(num_bikes_available + 5 as num_bikes_available)

from

  `bigquery-public-data`.new_york_citibike.citibike_stations

上面的範例中，我們只對 num_bikes_available這個欄位做計算，而計算後的欄位可以一樣是同樣的名稱，並不會影響原來的原始資料。

使用WITH做 Subqueries，一般的query，我們可以是這樣的寫法

select * from (

  select

    gender, tripduration /60 as minutes

  from

    `bigquery-public-data`.new_york_citibike.citibike_trips

)

where minutes < 10

limit 5

這個select *的inner query的別名是發生在 select裡面的。若是這個inner query的結果是我們經常要的.哪重複這一個SQL statement 就變得很麻煩.這時我們可以使用WITH來替代。範例如下

with all_trips as(

  select

    gender, tripduration /60 as minutes

  from

    `bigquery-public-data`.new_york_citibike.citibike_trips

)

select * from all_trips

where minutes < 10

limit 5

從這例子我們可以看到，我們把要經常使用的subquery使用WITH來替代，這時候在 with裡的query結果就可以被外部其他query所使用。

最後我們介紹Order by，這應該是經常使用 SQL語法的人最熟悉的了。
範例如下

 select

    gender, tripduration /60 as minutes

  from

    `bigquery-public-data`.new_york_citibike.citibike_trips

where gender = ‘female’

order by minutes DESC

limit 5

在BQ中，rows是不排序的。若使用order by而沒有指定的話，預設是Descending.

以上是一些 BQ CRUD 指令的簡單介紹，針對上面介紹的 SQL 語法大家如有任何問題歡迎留言詢問或直接聯繫我們，下一篇我們將在深入複雜的 SQL 語法應用，還請大家敬請期待！

▋延伸閱讀：

・BigQuery 是什麼？大數據時代一定要認識的最強資料分析工具
・BigQuery串接Google sheet的初階應用以及Data Studio的視覺化功能介紹
・如何透過Firebase與BigQuery來進行分析
・BigQuery –匯入資料 part 1