デベロッパー

BigQuery のユーザーフレンドリーな SQL とともに快適な夏を

2021年6月8日

https://storage.googleapis.com/gweb-cloudblog-publish/images/AdobeStock_276669603_resized.max-1400x1400.jpg

Google Cloud Japan Team

※この投稿は米国時間 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 によって逆の動作を行います。

lang-sql

読み込んでいます...

-- 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 を使用しています。

lang-sql

読み込んでいます...

柔軟なスキーマ処理

管理者とデータエンジニア向けの新しい SQL では、データパイプラインプロセス用のテーブル名変更と、柔軟な列管理が可能です。

テーブル名変更（一般提供）

データパイプラインプロセスでは、パイプライン実行の次回のイテレーションのための便宜を考慮して、テーブルを作成した後で名前を変更するのが一般的です。これを実現するためには、テーブル作成後に名前を変更できるメカニズムが必要です。今回、SQL を使用してこの名前の変更を行えるようになりました。ALTER TABLE RENAME TO で提供されるシンプルな構文を使って、テーブル作成後に名前を変更し、データパイプラインにおける次回のテーブルイテレーションに備えることができます。

lang-sql

読み込んでいます...

列に対する DROP NOT NULL 制約（一般提供）

BigQuery はこれまで UI、CLI、API で利用可能な多くのツールを提供してきましたが、データベースとのやり取りには SQL を使用したいという管理者も数多く存在します。BigQuery はこのほど、データセットとテーブルのプロビジョニングと管理を可能にする DDL ステートメントをリリースし、プロビジョニングと管理を大幅に簡便化しました。本日、この一連のリリースに続く新たな追加として、列に対する ALTER COLUMN DROP NOT NULL 制約を発表します。

ALTER COLUMN DROP NOT NULL によって、BigQuery の列から NOT NULL 制約を削除できます。

lang-sql

読み込んでいます...

列リストでの CREATE VIEW（一般提供）

BigQuery のお客様の間では、ビジネスロジックを把握するためにビューが広く使用されています。多くの BigQuery ユーザーが、ビジネス要件で、ビューで列にエイリアスを割り当てることを必要としています。BigQuery は、列リスト構文での CREATE VIEW のリリースにより、列名リストフォーマット内にビューを作成したときにそれを実現できるようになりました。

lang-sql

読み込んでいます...

新しい地理空間ツール

ST_POINTN、ST_STARTPOINT、ST_ENDPOINT

現実世界のデータを取り扱うデータ分析関連のお客様にとって、地理空間データはきわめて貴重です。BigQuery は、非常に強力な地理空間関数への対応によって、マーケティングデータの処理、嵐の追跡、自動運転車両の管理を支援します。特に車両または位置追跡データの分析向けに、重要ポイントでの抽出またはフィルタリングを容易にする 3 つの新しい関数を提供します。

たとえば、車両の履歴を扱う場合、ST_POINTN、ST_STARTPOINT、ST_ENDPOINT によって、走行の始めと終わりなどの要素を抽出できます。出発地と目的地のペアを識別する場合、これらの関数によってその作業が非常に簡単になります。

lang-sql

読み込んでいます...

暑い夏の日には冷たい飲み物が最適であるのと同様、BigQuery のこれらの新しいユーザーフレンドリーな SQL 機能は、データ分析ワークフローに最適です。BigQuery について詳しくは Google のウェブサイトをご確認ください。BigQuery サンドボックスを無料ですぐにお試しいただけます。

-Google Cloud プロダクトマネージャー Dan Peng

-Google Cloud プロダクトマネージャー Jagan R. Athreya

投稿先