こんにちは、小林寛和(@hiro-koba)と申します。私はデータエンジニアのための勉強会「Data Engineering Study」の共同主催者をやったり、ETL & ワークフローサービス「trocco®︎（トロッコ）」を運営する株式会社primeNumberの取締役CPOを務めているデータエンジニアです。
データエンジニアの皆さんが普段利用されているDWH/ETL/BIツールに関する最新アップデートや、界隈の最新トピックをまとめて配信しております。
お忙しい方や、サクッと最新情報をキャッチアップしたい方向けに、主要なニュースをピックアップして独自にまとめています。
毎月更新でニュースをお届けしていますので、ニュースの新着メール通知をご希望の方はこちらのフォーム（https://forms.gle/ZHUrxtfRZzPXxWZP6）よりご登録ください。
Google BigQueryのニュースまとめ
演算子の追加: PIVOT, UNPIVOT, QUALIFY
以下３つが Previewリリースされました。
- PIVOT演算子
- UNPIVOT 演算子
- QUALIFY句
特に PIVOT / UNPIVOT演算子は注目のアップデートかと思います。
データアナリストの利用者にとって、既存のBIツールを使ったピボットテーブル機能を使う場合、ツールのコンピュータリソースの制約によりデータサイズをしぼる必要がありました。
今回、BigQueryでサポートされたことでテーブル全体での整形処理ができるようになることで、BIツールのパフォーマンスやリソースを意識せずにデータ整形が行えるようになりそうです。
また、同様の処理を書く際に以前はUNION地獄となっていたところを、簡潔に（かつ動的に）記述することができるようになったため、データエンジニアにとっても有用なものになりそうです。
(参考 https://twitter.com/satoluxx/status/1391923522050551811)
使い方について個別に紹介していきます。
PIVOT
集計結果を使用して行を列に変換します。以下のような Produce というテーブルをサンプルとして利用します（公式DocsのSQL例）。
WITH Produce AS (
SELECT 'Kale' as product, 51 as sales, 'Q1' as quarter UNION ALL
SELECT 'Kale', 23, 'Q2' UNION ALL
SELECT 'Kale', 45, 'Q3' UNION ALL
SELECT 'Kale', 3, 'Q4' UNION ALL
SELECT 'Apple', 77, 'Q1' UNION ALL
SELECT 'Apple', 0, 'Q2' UNION ALL
SELECT 'Apple', 25, 'Q3' UNION ALL
SELECT 'Apple', 2, 'Q4')
SELECT * FROM Produce
+---------+-------+---------+
| product | sales | quarter |
+---------+-------+---------+
| Kale | 51 | Q1 |
| Kale | 23 | Q2 |
| Kale | 45 | Q3 |
| Kale | 3 | Q4 |
| Apple | 77 | Q1 |
| Apple | 0 | Q2 |
| Apple | 25 | Q3 |
| Apple | 2 | Q4 |
+---------+-------+---------+PIVOT 演算子を使用すると、quarter 列の行が新しい列（Q1、Q2、Q3、Q4）に変換されます。
SELECT * FROM
(SELECT * FROM Produce)
PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))
+---------+----+----+----+----+
| product | Q1 | Q2 | Q3 | Q4 |
+---------+----+----+----+----+
| Apple | 77 | 0 | 25 | 2 |
| Kale | 51 | 23 | 45 | 3 |
+---------+----+----+----+----+SELECT * FROM
(SELECT sales, quarter FROM Produce)
PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))
+-----+----+----+----+
| Q1 | Q2 | Q3 | Q4 |
+-----+----+----+----+
| 128 | 23 | 70 | 5 |
+-----+----+----+----+UNPIVOT
PIVOTと対になる演算子で、列を行に変換します。
以下のような Produce というテーブルを参照しています（PIVOTで整形した結果データと同じもの）。
WITH Produce AS (
SELECT 'Kale' as product, 51 as Q1, 23 as Q2, 45 as Q3, 3 as Q4 UNION ALL
SELECT 'Apple', 77, 0, 25, 2)
SELECT * FROM Produce
+---------+----+----+----+----+
| product | Q1 | Q2 | Q3 | Q4 |
+---------+----+----+----+----+
| Kale | 51 | 23 | 45 | 3 |
| Apple | 77 | 0 | 25 | 2 |
+---------+----+----+----+----+Q1、Q2、Q3、Q4 列が変換されます。これらの列の値には Sales という新しい列が入力され、これらの列の名前に Quarter という新しい列が入力されます。これは、単一列のピボット解除演算です。
SELECT * FROM Produce
UNPIVOT(sales FOR quarter IN (Q1, Q2, Q3, Q4))
+---------+-------+---------+
| product | sales | quarter |
+---------+-------+---------+
| Kale | 51 | Q1 |
| Kale | 23 | Q2 |
| Kale | 45 | Q3 |
| Kale | 3 | Q4 |
| Apple | 77 | Q1 |
| Apple | 0 | Q2 |
| Apple | 25 | Q3 |
| Apple | 2 | Q4 |
+---------+-------+---------+複数列のピボット解除演算の例はこちら（公式Docsより）。
SELECT * FROM Produce
UNPIVOT(
(first_half_sales, second_half_sales)
FOR semesters
IN ((Q1, Q2) AS 'semester_1', (Q3, Q4) AS 'semester_2'))
+---------+------------------+-------------------+------------+
| product | first_half_sales | second_half_sales | semesters |
+---------+------------------+-------------------+------------+
| Kale | 51 | 23 | semester_1 |
| Kale | 45 | 3 | semester_2 |
| Apple | 77 | 0 | semester_1 |
| Apple | 25 | 2 | semester_2 |
+---------+------------------+-------------------+------------+QUALIFY
QUALIFY句は分析関数の値でさらにフィルタをかけるものです。
これまではSELECT句に記述し、サブクエリ化した上でフィルタする必要ががありましたが、より簡潔に記述できるようになりました。
SQL記述例は以下の通り。
SELECT
item,
RANK() OVER (PARTITION BY category ORDER BY purchases DESC) as rank
FROM Produce
WHERE Produce.category = 'vegetable'
QUALIFY rank <= 3
+---------+------+
| item | rank |
+---------+------+
| kale | 1 |
| lettuce | 2 |
| cabbage | 3 |
+---------+------+PIVOT、UNPIVOTは、ExcelやSpreadsheetsのピボットテーブル機能を利用している人には馴染みのある操作かと思います。
より詳しく知りたい方は以下の参考リンクもご覧ください。
DDLステートメントの追加: テーブルのリネーム
テーブルのrenameがDDLステートメントGA（一般提供）リリースされました。
これまではテーブル名の変更はサポートされておらず、新規テーブル名でテーブルをコピーし、元のテーブルを削除するなどが必要でしたが、以下のSQLの様に記述可能になっています。
ALTER TABLE mydataset.mytable RENAME TO mynewtableただし、一部制約があり、
- 外部テーブル、行レベルポリシーのあるテーブルのリネームは不可
- ストリーミングインサートしているテーブルは一時的に取り込みを止めておく必要あり
などが公式ページにて記載されています。
より詳しく知りたい方は以下の参考リンクもご覧ください。
DDLステートメントの追加: create view view_column_name_list, ALTER COLUMN DROP NOT NULL
DDLのサポートとして以下２つがGA（一般提供）リリースされました。
1. BigQuery のView作成時にカラム名を指定して作成
元はSELECT句の列名を元にカラム名として生成されていましたが、以下の記述例のように指定できるようになりました。SQL記述例（count列を定義して生成します）は以下の通り。
CREATE VIEW mydataset.age_groups(age, count) AS SELECT age, COUNT(*)
FROM mydataset.people
group by age;2. BigQueryテーブルの列から NOT NULL 制約を削除
以下のようなSQL記述が可能になります。
ALTER TABLE mydataset.mytable
ALTER COLUMN mycolumn
DROP NOT NULLより詳しく知りたい方は、以下の参考リンクもご覧ください。
GeoJSON形式データの取り込みと地理関数のサポート
GeoJSON形式のデータロードと、地理関数として以下の３つがGA（一般提供）リリースされました。
- ST_STARTPOINT
- ST_ENDPOINT
- ST_POINTN
GeoJSON形式のデータロードについて
BigQuery GIS は 2018年のGoogle Cloud Nextでパブリックアルファ版として公開されています。
- https://cloud.google.com/blog/products/gcp/bridging-the-gap-between-data-and-insights
- https://cloud.google.com/bigquery/docs/gis
GeoJSON形式とはJSON形式で記述された地理空間表現フォーマットのことで以下のような書き方をします。
{ "type": "Point", "coordinates": [-121,41] }地理関数について
ST_STARTPOINT
LineString の geography の起点をポイントの geography として返します。
SELECT ST_StartPoint(ST_GeogFromText('linestring(1 1, 2 1, 3 2, 3 3)')) first
+--------------+
| first |
+--------------+
| POINT(1 1) |
+--------------+ST_ENDPOINT
LineString の geography の終点をポイントの geography として返します。
SELECT ST_EndPoint(ST_GeogFromText('linestring(1 1, 2 1, 3 2, 3 3)')) last
+--------------+
| last |
+--------------+
| POINT(3 3) |
+--------------+ST_POINTN
LineString の geography の N 番目の点をポイント geography として返します。
WITH linestring AS (
SELECT ST_GeogFromText('linestring(1 1, 2 1, 3 2, 3 3)') g
)
SELECT ST_PointN(g, 1) AS first, ST_PointN(g, -1) 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) |
+--------------+--------------+--------------+----------------+より詳しく知りたい方は以下の参考リンクもご覧ください。
その他のアップデート
その他、以下のアップデートがありました。
- ODBC/JDBC ドライバのアップデート（公式リリースノート、JDBCリリースノート、ODBCリリースノート）
Amazon Redshiftのニュースまとめ
Redshift MLの一般提供開始
SQL文を記述することで機械学習のトレーニングから推測まで実行できる新機能です。
従来はRedshiftからS3へUNLOAD、その後SageMakerへ読み込ませるという手動の流れが必要でしたが、SQLで全ての作業を完結できるようになります(裏側では同じようなアーキテクチャが動いているとのこと)。
例えば、CREATE MODEL文といった拡張されたSQL構文によってMLモデルを作成、それに対してSELECT文で結果を得ることが可能です。
参考1: AWS公式アナウンス
参考2: DevelopersIOの試してみた記事
Snowflakeのニュースまとめ
データガバナンスの機能として「アクセス履歴」がプレビューに
過去365日（1年）以内のSnowflakeオブジェクト（例: テーブル、ビュー、列）のアクセス履歴を参照することができます。（現時点ではプレビュー機能）
注意点こちらの公式ドキュメントにできる/できないの記載があります。
Amazon API Gatewayプライベートエンドポイントのサポート
外部関数としてAmazon API Gatewayプライベートエンドポイントを呼び出すことが可能になりました。
Go Snowflake Driver: AWSでのPUTのサポート
GolangのSnowflake DriverでAWSのPUTコマンドをサポートしました。(プレビュー機能)
これにより、ローカルマシンのファイルをSnowflakeのステージとして使用しているS3へアップロードできるようになりました。
Lookerのニュースまとめ
Lookerモバイルアプリのアップデート
以下のアップデートが入りました。
- Web版と同じく22言語に対応
- アプリを開くたびに認証が必要な「Force mobile authentication」や、生体認証の設定が可能に
- ディープリンクでモバイルアプリへの誘導を可能に
参考: Looker公式リリースノート
GUIからAPIの操作コードを出力する「API Explorer」がリリース
GUI上で操作を行うだけで、APIを操作するための実装コードを好きな言語でアウトプットできる「API explorer」がリリースされました。
現状対応している言語は、LookerでSDKとしてサポートされている以下6つの言語です。
- Python
- TS
- Kotlin
- C#
- Swift
- Go
参考1: Looker公式ドキュメント
参考2: DevelopersIO試してみた記事
Looker Error Catalogが公開
Lookerで表示されるエラーとその対処法をまとめたページが公開されました。
エラーメッセージとその発生箇所から、エラーの原因などを索引できます。
参考: Looker公式リリースノート
Googleデータポータルのニュースまとめ
レポートのプレゼンテーション機能が追加
レポートを全画面表示し、プレゼンテーションを行うことが出来るようになりました。
以下の様に、「三点リーダー」 > 「共有」を選択するとプレゼンテーションモードに切り替わります。
参考: Google公式リリースノート
参考2: unyoo.jpの解説記事
レポートやデータソースの履歴に対し、名前の設定が可能に
変更履歴に対して名前を設定できるようになりました。
新規作成時や、過去のバージョンに対して名前を設定することができます。
参考: Google公式リリースノート
ETLツール「trocco(トロッコ)」のニュースまとめ
対応データソースを拡充
ETL & ワークフローサービス「trocco（トロッコ）」が、以下のデータソースに対応しました。
転送元AWS Cost Explorerに正式対応- 転送元Shopifyに正式対応
- 転送元Salesforce Reportに正式対応
- 転送先Googleスプレッドシートに正式対応
- 転送元PostgreSQLでCDC方式の転送に正式対応
- 転送元Boxに正式対応
- 転送元Facebook Adsクリエイティブに正式対応
- 転送元eセールスマネージャーに正式対応
プログラミングETL機能をリリース
好きな言語でETLを実装できる「プログラミングETL」機能をリリースいたしました。
troccoのデータ転送の最中に、Ruby, Pythonでデータ変換処理などが実行できます。
詳しくは以下のデモ動画をご覧ください。
BigQueryテスト実行機能の追加
設定を作成・編集する際、BigQueryに一時テーブルを作成し、転送をテスト実行する機能が追加されました。
設定変更時に、BigQuery上にお試しで転送してみて、実データで変更内容を確認したい方におすすめです。
ワークフローでBigQueryクエリ実行結果に基づくループ実行に対応
ワークフロー（ジョブ管理機能）において、BigQueryのクエリ結果に基づくループ実行ができるようになりました。
BigQuery上にある任意のリストを利用し、転送設定をループ実行させることが出来ます。
下の例の通り、ループ対象のカスタム変数は複数指定することが可能です。
オンプレエージェント機能が登場
今までtrocco®︎では、クラウド上にあるtrocco®︎実行環境からデータを取ってくる方式のみに対応していました。そのため、オンプレミスなどの外部からアクセス出来ない環境に存在するデータを転送することが出来ませんでした。
今回のリリースにより、オンプレミス環境にtrocco®︎のエージェントをインストールし、オンプレ内からクラウドにデータを転送する方式に対応しました。
現時点で対応しているのは以下の転送経路となります。
- ローカルファイル → BigQuery
- ローカルファイル → Google Cloud Storage
- ローカルファイル → S3
こちらはオプション機能になので、お試しになりたい方はこちらからご連絡ください。
その他のアップデート
今月のtroccoは上記以外にもアップデートがたくさんありました。
詳しくは以下のページをご参照ください。
https://blog.trocco.io/releases/programming-etl
以上、Data Engineering News 2021年5月アップデートまとめでした。
毎月更新でニュースをお届けしておりますので、ニュースの新着メール通知をご希望の方はこちらのフォーム（https://forms.gle/ZHUrxtfRZzPXxWZP6）よりご登録ください。
こんなニュースを知りたい！ご意見・ご要望も著者Twitterアカウントまでお気軽にDM下さい！
