BigQuery SQL語法基本操作 part 1

Big Query CRUD(Create/Read/Update/Delete)的基本操作介紹

BigQuery(以下簡稱BQ)的CRUD操作都可以透過SQL指令來完成

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

因為是資料分析的工具,故會用最多的就是”Read”的功能。現行BigQuery 支援SQL:2011的語法。

以下我們使用一個例子,如下圖。進入到BQ後我們使用BQ的公開資料,我們使用的公開資料是紐約市的自行車租借資料.

一開始我們可以檢視我們所要分析的資料,在BQ的左下表列(如圖一),你會看到BQ的公開資料 bigquery-public-data,這些都是BQ公開可以分析的資料。
這裡說明一下BQ的資料庫結構。
BQ的
第一層是project name,在這裡project ID就是 bigquery-public-data
第二層是 dataset name ,在這裡dataset name 就是 new_york_citibike(如圖二)
第三層是 table name,在這裡table name就是citibike_stations及citibike_trips(如圖二)

圖一
圖二
圖三
圖四

接下來我們檢視一下我們所要分析的資料,在BQ的每一個table中我們 都可以看到schema/Details/Preview這三項資料(如圖二到四)。

schema–這一段就是BQ這一個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語法應用。

發佈留言