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的公开资料，我们使用的公开资料为纽约市的自行车租赁资料。

一开始我们可以检视我们所要分析的资料，在BＱ的左下表列(如图一），你会看到BQ的公开资料 bigquery-public-data，这些都是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 * 整个资料库会让你的费用爆增。除了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语法应用。