デベロッパー
BigQuery のユーザー フレンドリーな SQL とともに快適な夏を
※この投稿は米国時間 2021 年 5 月 25 日に、Google Cloud blog に投稿されたものの抄訳です。
夏をすぐそこに迎え、熱気が高まってきています。しかし今月、BigQuery が、ユーザー フレンドリーな SQL のリリースとともに、冷たい飲み物でいっぱいのクーラー ボックスのような快適さをお届けします。
今回発表する BigQuery のユーザー フレンドリーな SQL のリリースには、3 つのカテゴリがあります。強力な分析機能、柔軟なスキーマ処理、新しい地理空間ツールです。
強力な分析機能
この強力な SQL 分析機能によって、アナリストが BigQuery でデータを整理、フィルタリング、レンダリングする際の柔軟性が、かつてないほど向上します。PIVOT と UNPIVOT を使用して集計データにスプレッドシートのような機能性を実現し、QUALIFY を使用して分析関数内の無関係なデータを除外できます。
このセクションでは、BigQuery の一般公開データセット usa_names を使用した例を通して、これらの新機能の理解を深めます。
PIVOT / UNPIVOT(プレビュー)
データ分析に従事する人々が特に時間を取られる仕事の一つに、データを適切な形にラングリングする作業があります。SQL はデータのラングリングに適していますが、ときにはスプレッドシートで行うようにテーブルのフォーマットを変更し、行と列を相互にピボットしたくなる場合があります。このユースケースに対応するため、BigQuery に PIVOT 演算子と UNPIVOT 演算子を導入します。PIVOT では、値を集計することにより行の一意の値から列を作成し、UNPIVOT ではその逆の動作を行います。
以下の例は、bigquery-public-data.usa_names.usa_1910_current で PIVOT を使用して、性別を列として表し、各年に生まれた男女の数を表示しています。その後、UNPIVOT によって逆の動作を行います。
-- we start with SQL to create a simple table
-- we only include gender, year, and number.
CREATE TABLE
mydataset.sampletable1 AS (
SELECT
Gender,Year,SUM(Number) AS Number
FROM
`bigquery-public-data.usa_names.usa_1910_current`
WHERE
Year >= 2017
GROUP BY
Gender, Year);
-- The resulting table:
--+----------------------------------------+
--| Gender | Year | Number |
--+----------------------------------------+
--| F | 2019 | 1353716 |
--| F | 2017 | 1403989 |
--| F | 2018 | 1380382 |
--| M | 2018 | 1568678 |
--| M | 2019 | 1538056 |
--| M | 2017 | 1604609 |
--+----------------------------------------+
-- use PIVOT to create columns for “female” and “male”
CREATE TABLE
mydataset.Pivoted AS
SELECT
year, male, female
FROM
mydataset.sampletable1
PIVOT( SUM(Number) FOR gender IN ('M' AS male,
'F' AS female))
ORDER BY
year;
-- The resulting pivoted table:
--+----------------------------------------+
--| Year | female | male |
--+----------------------------------------+
--| 2017 | 1403989 | 1604609 |
--| 2018 | 1380382 | 1568678 |
--| 2019 | 1353716 | 1538056 |
--+----------------------------------------+
-- UNPIVOT reverses the row/column rotation of PIVOT.
SELECT
*
FROM
mydataset.Pivoted
UNPIVOT(number FOR gender IN (male AS 'M',
female AS 'F'));
QUALIFY(プレビュー)
SQL の上級ユーザーは、分析関数(別名ウィンドウ関数)の持つ力を知っています。これらの関数は、行のグループの値を計算し、各行について単一の結果を返します。たとえば、総計、小計、移動平均、ランクなどの計算に分析関数を使用します。QUALIFY への対応が発表されたことで、BigQuery のユーザーは、QUALIFY 句を使用して分析関数の結果をフィルタリングできるようになりました。
QUALIFY は、WHERE および HAVING とともに、フィルタリングに使用されるクエリ句のファミリーに属しています。WHERE 句は、クエリで個々の行をフィルタリングするのに使用されます。HAVING 句は、集計関数と GROUP BY 句の後の結果セットで集計行をフィルタリングするのに使用されます。QUALIFY 句は、分析関数の結果のフィルタリングに使用されます。
QUALIFY の有用性を示すために、以下の例では、bigquery-public-data.usa_names.usa_1910_current を使用して過去 10 年間の各年の女性名トップ 3 を返すために、QUALIFY を使用しています。
-- QUALIFY filters the result of the RANK function
SELECT
name,year,SUM(number) AS total,
RANK() OVER (PARTITION BY year
ORDER BY SUM(number) DESC) AS rank
FROM
`bigquery-public-data.usa_names.usa_1910_current`
WHERE
gender = 'F'
AND YEAR >= 2010
GROUP BY 1,2
QUALIFY RANK <= 3
ORDER BY 2,4;
柔軟なスキーマ処理
管理者とデータ エンジニア向けの新しい SQL では、データ パイプライン プロセス用のテーブル名変更と、柔軟な列管理が可能です。
テーブル名変更(一般提供)
データ パイプライン プロセスでは、パイプライン実行の次回のイテレーションのための便宜を考慮して、テーブルを作成した後で名前を変更するのが一般的です。これを実現するためには、テーブル作成後に名前を変更できるメカニズムが必要です。今回、SQL を使用してこの名前の変更を行えるようになりました。ALTER TABLE RENAME TO で提供されるシンプルな構文を使って、テーブル作成後に名前を変更し、データ パイプラインにおける次回のテーブル イテレーションに備えることができます。-- create a sample table “tablename” in “mydataset”.
-- You will rename this table.
CREATE OR REPLACE TABLE dataset.tablename(
col1 STRING,
col2 NUMERIC);
-- if this table “tablename” becomes obsolete
-- perform Table Rename to “obsoletetable”
ALTER TABLE
mydataset.name RENAME TO obsoletetable;
列に対する DROP NOT NULL 制約(一般提供)
BigQuery はこれまで UI、CLI、API で利用可能な多くのツールを提供してきましたが、データベースとのやり取りには SQL を使用したいという管理者も数多く存在します。BigQuery はこのほど、データセットとテーブルのプロビジョニングと管理を可能にする DDL ステートメントをリリースし、プロビジョニングと管理を大幅に簡便化しました。本日、この一連のリリースに続く新たな追加として、列に対する ALTER COLUMN DROP NOT NULL 制約を発表します。
ALTER COLUMN DROP NOT NULL によって、BigQuery の列から NOT NULL 制約を削除できます。
-- create a table to store credit card numbers
-- the business requires this field, so
-- include a NOT NULL constraint
CREATE TABLE
mydataset.customers(credit_card_number STRING NOT NULL);
-- if needs of the business no longer require this field,
-- the customer can allow null entries in this column
-- by dropping the constraint
ALTER TABLE
mydataset.customers
ALTER COLUMN credit_card_number DROP NOT NULL;
列リストでの CREATE VIEW(一般提供)
BigQuery のお客様の間では、ビジネス ロジックを把握するためにビューが広く使用されています。多くの BigQuery ユーザーが、ビジネス要件で、ビューで列にエイリアスを割り当てることを必要としています。BigQuery は、列リスト構文での CREATE VIEW のリリースにより、列名リスト フォーマット内にビューを作成したときにそれを実現できるようになりました。
-- aliases list1 and list2 can be assigned in a list format
CREATE VIEW
myview (list1, list2) AS
SELECT
column_1, column_2
FROM
mydataset.exampletable
新しい地理空間ツール
ST_POINTN、ST_STARTPOINT、ST_ENDPOINT
現実世界のデータを取り扱うデータ分析関連のお客様にとって、地理空間データはきわめて貴重です。BigQuery は、非常に強力な地理空間関数への対応によって、マーケティング データの処理、嵐の追跡、自動運転車両の管理を支援します。特に車両または位置追跡データの分析向けに、重要ポイントでの抽出またはフィルタリングを容易にする 3 つの新しい関数を提供します。
たとえば、車両の履歴を扱う場合、ST_POINTN、ST_STARTPOINT、ST_ENDPOINT によって、走行の始めと終わりなどの要素を抽出できます。出発地と目的地のペアを識別する場合、これらの関数によってその作業が非常に簡単になります。
-- pull the first, second, penultimate and final points
-- from a linestring
WITH linestring as (
SELECT ST_GeogFromText('linestring(1 1, 2 1, 3 2, 3 3)') g
)
SELECT
ST_StartPoint(g) AS first, ST_EndPoint(g) AS last,
ST_PointN(g,2) AS second, ST_PointN(g, -2) as second_to_last
FROM
linestring
+--------------+--------------+--------------+----------------+
| first | last | second | second_to_last |
+--------------+--------------+--------------+----------------+
| POINT(1 1) | POINT(3 3) | POINT(2 1) | POINT(3 2) |
+--------------+--------------+--------------+----------------+
暑い夏の日には冷たい飲み物が最適であるのと同様、BigQuery のこれらの新しいユーザー フレンドリーな SQL 機能は、データ分析ワークフローに最適です。BigQuery について詳しくは Google のウェブサイトをご確認ください。BigQuery サンドボックスを無料ですぐにお試しいただけます。
-Google Cloud プロダクト マネージャー Dan Peng
-Google Cloud プロダクト マネージャー Jagan R. Athreya