2023.03.02

Google BigQuery で実行できる SQL と実行できない SQL

BigQuery
Google BigQuery
SQL

BigQueryは、データを分析する際によく使用されるデータウェアハウス（DWH）で、様々な業界の企業やマーケティング会社が使用しているサービスです。

具体的には、Webサイトの解析でよく使われるGoogle アナリティクスとBigQueryを連携させて、サイトに訪問したユーザーがどのようなアクションを取る傾向があるのか解析をして、その結果どのようなコンテンツが求められているのか？などのインサイトを見つけ出すような活用ができます。

他には、ECサイトの顧客の購買履歴を分析して、同じような趣味嗜好のユーザーにおすすめのサジェストを表示するためのデータ分析基盤をBigQueryで構築して運用する事例もあります。BigQueryをフル活用することで、データをシンプルに 1 つの場所に格納でき、データ管理、分析、集計、BI ツール（tableauやLooker）も含めて環境が一元化され、データ分析チームの作業効率の向上を実現できます。

そんな BigQuery を使い倒すために本記事では、 BigQuery がサポートする標準SQL（Google Standard SQL）について、どのようなSQL があるのか、何ができるのか、何ができないのかをご紹介します。

標準SQLサポート状況（2022.07時点）

BigQuery は、標準SQL（Google Standard SQL）とレガシーSQL と呼ばれる2 つのSQL 言語をサポートしています。Google Cloud （GCP）はBigQuery に保存されているデータに対しては、業界標準に準拠し、拡張機能をもつ標準SQL を利用することを推奨しています。

このため、これからBigQuery を利用される方は、標準SQL でできることをまず押さえる必要があります。

標準SQL がサポートするSQL 構文（ステートメント）は、下記のように分類できます。細かい点は次項から見ていきますが、2022年7月現在の最新情報では、基本的なSQL は網羅されており、実際のところ、「BigQuery では、このSQL が実行できない」ということはほとんどない状況です。

①	データクエリ言語（DQL）	BigQuery データ分析のための主要SQLを提供する
②	データ定義言語（DDL）	テーブル等のデータベースオブジェクトを作成、変更する
③	データ操作言語（DML）	テーブルのデータを更新、挿入、削除する
④	データ制御言語（DCL）	BigQuery のシステムリソース（アクセス権等）を制御する
⑤	手続き型言語	1つのリクエストで複数のSQLステートメントを実行できる
⑥	トランザクション制御言語（TCL）	データ変更のためのトランザクションを管理できる（手続き型言語の一部の位置づけで、2022.07現在、Preview 版となっている）
⑦	その他	データのエクスポートなどの追加機能を提供する

標準SQL で何ができるのか、何ができないのか

分類された標準SQL の各SQL について、概要を解説します。

①データクエリ言語（DQL）
一般的なデータベースでは多くの場合、DML（本項③）にまとめられてしまうSELECT ですが、BigQuery ではDQLとして解説されています。
https://cloud.google.com/bigquery/docs/reference/standard-sql/introduction

SELECT	SELECT	テーブルからデータを検索、取得する
	FROM	行を取得する1つまたは複数のテーブルを指定する
	JOIN	複数のテーブルを結合する
	WHERE	SELECT の結果に対し、検索条件を設定する
	GROUP BY	指定したカラムの値をもとにグループ化する
	HAVING	GROUP BY の結果に対し、検索条件を設定する
	ORDER BY	昇順や降順に並び変える
	LIMIT	取得データの行数上限を設定する
	その他	UNION、INTERSECT、EXCEPT、WITH、WINDOW等も利用可能

②データ定義言語（DDL）
一般的なDDL（CREATE、ALTER、DROP）が利用可能で、BigQuery 固有のリソース制御も用意されています。

他データベースと比較すると、CREATE INDEX 文が見当たりませんが、2022年４月にPreview 版として、検索インデックス機能がリリースされました。（DDLマニュアルには未反映）

Pinpoint unique elements with BigQuery search features | Google Cloud Blog
https://cloud.google.com/blog/products/data-analytics/pinpoint-unique-elements-with-bigquery-search-features
検索インデックスを作成する | BigQuery | Google Cloud
https://cloud.google.com/bigquery/docs/search-index?hl=ja

CREATE	SCHEMA	スキーマ、データセットを作成する
	TABLE	テーブルを作成する
	VIEW　、MATERIALIZED VIEW	ビュー、マテリアライズドビューを作成する
	FUNCTION　、PROCEDURE	ユーザー定義関数（UDF）、プロシージャを作成する
	その他	その他、行レベルのアクセス制御作成等がある
ALTER	SCHEMA	スキーマ、データセットの変更、オプションを追加する
	TABLE	テーブルの変更、オプションを追加する
	VIEW　、MATERIALIZED VIEW	ビュー、マテリアライズドビューの変更、オプションを追加する
	その他	その他、列のデータ型の変更等がある
DROP	SCHEMA	スキーマ、データセットを削除する
	TABLE	テーブルを削除する
	VIEW　、MATERIALIZED VIEW	ビュー、マテリアライズドビューを削除する
	FUNCTION　、PROCEDURE	ユーザー定義関数（UDF）、プロシージャを削除する
	その他	その他、行レベルのアクセス制御削除等がある

DDL公式マニュアル
https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language

③データ操作言語（DML）一般的なDML（CREATE、ALTER、DROP）が利用可能で、MERGE やTRUNCATE も対応しています。

SELECT 文はDML ですが、BigQuery ではDQL として分類しています。また、TRUNCATE TABLE は、従来はDELETEを使う必要がありましたが、2020年10月リリースで新機能として対応しました。BigQuery release notes | Google Cloud
https://cloud.google.com/bigquery/docs/release-notes#October_14_2020ユーザーを笑顔にする BigQuery の使いやすい SQL 新機能 | Google Cloud Blog
https://cloud.google.com/blog/ja/products/bigquery/smile-new-user-friendly-sql-capabilities-bigquery

INSERT	テーブルに新しい行を追加する
DELETE	テーブルから行を削除する
UPDATE	テーブル内の既存の行を更新する
MERGE	複数テーブルのデータをマージし、挿入・削除・更新を同時に行う
TRUNCATE	テーブルメタデータ情報を残し、すべての行を削除する

DML公式マニュアル
https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax

④データ制御言語（DCL）一般的なDCL と同様、アクセス権付与や権限はく奪が利用可能です。

GRANT	ユーザーにデータベースオブジェクトへのアクセス権限ロールを付与する
REVOKE	ユーザーのアクセス権限ロールを削除する

DCL公式マニュアル
https://cloud.google.com/bigquery/docs/reference/standard-sql/data-control-language

⑤手続き型言語・⑥トランザクション制御言語（TCL）

1つのリクエストで複数のSQL を実行可能です。2021年6月にトランザクション管理にも対応しました。（2022.07時点でPreview 版）
https://cloud.google.com/bigquery/docs/release-notes#June_29_2021

DECLARE	指定されたタイプの変数を宣言する
SET	指定された式の値を持つように変数を設定する
TRANSACTION（Preview 版）	トランザクションの開始、コミット、ロールバックを制御する
その他	CASE文、IF文、LOOP文等がサポートされており、各種手続きを構成できる

手続き型言語、TCL 公式マニュアル
https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language

⑦ その他
テーブルからのエクスポートや、テーブルへのローダー機能が利用可能です。

EXPORT DATA	クエリ結果を外部ストレージ（Cloud Strage）にエクスポートする
LOAD DATA（Preview 版）	1つ以上のファイルからテーブルにデータをロードする

標準SQLその他公式マニュアル
https://cloud.google.com/bigquery/docs/reference/standard-sql/other-statements

データクエリ言語のSQL 活用例

本項では、データクエリ言語（DQL）の活用例を、一般的なSQL 利用シナリオと合わせてご紹介します。

テーブルからデータを取り出し、氏名を昇順に並べ替え、上から2件を表示

ID 列とNAME 列をもつ、単純なCUSTOMER テーブルへのクエリを想定します。

ID 列とNAME 列

ORDER BY 句による順序指定、ASC、DESC による昇順、降順は問題なく利用できます。

また、BigQuery に限らず、巨大なテーブルを全件アクセスしてリソースを消費させてしまうことを回避するため、LIMIT 句で取得件数をコントロールします。 IN 句による同一列の複数条件指定

WHERE 句で抽出条件を指定する

一般的なAND、OR、NOT の論理演算子、NULL 演算子をサポートしています。

・条件1 AND 条件2
・条件1 OR 条件2
・NOT 条件
・IS NULL
・IS NOT NULL

関数、演算子、条件 | BigQuery | Google Cloud
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators?hl=ja#logical_operators

LIKE 演算子によるあいまい検索で、例えば下記のように住所テーブルのaddress 列から「東京都」で始まる住所の取得も可能です。

SELECT *
FROM 住所テーブル
WHERE address LIKE '東京都%'

BETWEEN 演算子も利用可能で、例えば下記のようなWHERE 句による期間指定も問題なく使えます。（下記例は、2022年1月から6月までの期間を指定）

WHERE DATE BETWEEN "2022-01-01" AND "2022-06-30"

IN 句による同一列の複数条件指定も利用可能です。（前述のCUSTOMER テーブルを例にします）
クエリ結果

集計関数を用いてレコード総件数の集計や重複を取り除く

頻繁に利用する集計関数ですが、こちらはGoogle Cloud （GCP）公式サイトのサンプルSQL を見てみます。
下記のように、4件のデータ（UNNEST で配列を行に展開しています）に対し、COUNTで集計して4件を取得（count_star）、DISTINCT で重複除外して集計して3件取得（count_dist_x）します。

その他、下記を含め利用頻度の高い集計関数も、GROUP BY 句と組み合わたグルーピングも問題なく使えます。

・SUM　合計
・AVG　平均値
・MAX　最大値
・MIN　最小値

集計関数 | BigQuery | Google Cloud
https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions?hl=ja#count

GROUP BY 句
https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#group_by_clause

各種関数（数学関数、文字列関数、変換関数）

小数点を四捨五入するROUND や、XをY乗するPOWER(X, Y)等の数学関数、文字列結合するCONCAT文字列関数、データ型変換を行うCAST変換関数（数値型を文字列型に変換等）が用意されています。この他にも、データ分析に応用可能な多くの関数がサポートされています。

ROUND関数による四捨五入の例
※後述するWITH句で仮想テーブルを作り、ROUND関数を利用しています.

POWER関数例
1TBのディスクのバイト表記サイズを、MB表記、GB表記にする（1000byte=1KBとする）

CONCAT関数例
POWER関数例

関数 | BigQuery | Google Cloud
https://cloud.google.com/bigquery/docs/reference/standard-sql/aead_encryption_functions?hl=ja

JOIN によるテーブル結合（INNER JOIN、LEFT OUTER JOIN）

テーブル結合は複数の種類がありますが、利用頻度が高くSQL の可読性を高める内部結合（INNER JOIN）と、左外部結合（LEFT OUTER JOIN）のサンプルSQL について、Google Cloud公式サイトから見てみます。

内部結合は、下記サンプルSQL のように、Table A とTable B のデータの紐づきがある、w列2,3 とy列2,3 が結合、抽出されます。紐付きのないTable A のw列1 とTable B のy列4 は結果に含まれない、一般的な内部結合の動きとなります。

内部結合

INNER JOIN例

Rosterテーブル	TeamMascotテーブル

INNER JOIN例

左外部結合は、下記サンプルSQL のように、Table A（左側）を元にTable B（右側）を結合します。
ここでは、Table A に存在しないTable B のy列4 が結合対象外となり、Talbe A にしか存在しないw列1 にはNULL が入ります。こちらも、一般的な左外部結合の動きです。

左外部結合

LEFT OUTER JOIN例

Rosterテーブル	TeamMascotテーブル

INNER JOIN例

JOIN | BigQuery | Google Cloud
https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#join_types

WITH句による複雑なサブクエリの可視化

複雑な分析要件で、サブクエリや結合の度合いが増えていくと、SQL クエリの可読性が非常に悪くなります。WITH 句は、下記のように、実行結果を仮想テーブルとして複数扱うことができます。

WITH A AS (任意のSELECT文),
B AS (任意のSELECT文),
C AS (任意のSELECT文)
SELECT * FROM A …（以降省略）

公式サイトのサンプルでは、下記のように2つのサブクエリの仮想テーブルをWITH 句で宣言し、2つのクエリ結果を結合しています。（この場合、重複ありで、どちらか、もしくは両方に含まれる和集合）

Rosterテーブル	PlayerStatsテーブル

クエリの結合例

サンプルでクエリの結合例がでましたが、BigQuery は、UNION ALL（重複ありの和集合）、UNION DISTINCT（重複なしの和集合）、INTERSECT DISTINCT（積集合）、EXCEPT DISTINCT（差集合、除外）もサポートしています。

WITH 句 | BigQuery | Google Cloud
https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#with_clause

UNION | BigQuery | Google Cloud
https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#union_example

おわりに

今回、各SQL 言語のサポート状況や、SELECT を中心としたデータクエリ言語の観点から、一般的なSQL の使い方と見比べてみましたが、ほとんどのケースが対応可能になっていました。このあたりは、不足している機能があれば、ユーザーニーズに迅速にこたえて新機能をリリースしていく、Google Cloud （GCP）やBigQuery ならではのスピード感だと思います。

一方で、迅速なユーザーニーズ対応を行っているだけに、2022年現在、標準SQL のオプションレベルでPreview 版となっているケースもあり、本番環境利用にはリスク評価を行った上で採用する必要があります。BigQuery の標準SQL は、PostgreSQL やMySQL でのSQL の素養があれば、違和感を感じることなく活用できるのではないでしょうか。

データ分析を推進する上で、BigQuery 固有のSQL 作法につまづくことは今後、さらに減っていくと考えられます。
弊社トップゲートでは、Google Cloud (GCP）利用料3%OFFや支払代行手数料無料、請求書払い可能などGoogle Cloud (GCP）をお得に便利に利用できます。

さらに専門的な知見を活かし、rvice/gcp-consulting.html" target="_blank" rel="noopener">Google Cloud (GCP）に関する技術サポート、コンサルティング

など幅広くあなたのビジネスを加速させるためにサポートをワンストップで対応することが可能です。

Google Workspace（旧G Suite）に関しても、実績に裏付けられた技術力やさまざまな導入支援実績があります。

あなたの状況に最適な利用方法の提案から運用のサポートまでのあなたに寄り添ったサポートを実現します！

Google Cloud (GCP）、またはGoogle Workspace（旧G Suite）の導入をご検討をされている方はお気軽にお問い合わせください。
お問合せはこちら

メール登録者数3万件！TOPGATE MAGAZINE大好評配信中！
Google Cloud（GCP）、Google Workspace（旧G Suite）、TOPGATEの最新情報が満載！
メルマガ登録はこちら