デベロッパー

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

AdobeStock_276669603_resized.jpg

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

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

Language: SQL

  -- 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 で提供されるシンプルな構文を使って、テーブル作成後に名前を変更し、データ パイプラインにおける次回のテーブル イテレーションに備えることができます。

Language: SQL

  -- 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 制約を発表します。

Language: SQL

  -- 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 のリリースにより、列名リスト フォーマット内にビューを作成したときにそれを実現できるようになりました。

Language: SQL

  -- 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 によって、走行の始めと終わりなどの要素を抽出できます。出発地と目的地のペアを識別する場合、これらの関数によってその作業が非常に簡単になります。

Language: SQL

  -- 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