データ分析

時系列データ分析を簡素化する新しい BigQuery 機能のご紹介

2024年4月3日

Google Cloud Japan Team

※この投稿は米国時間 2024 年 3 月 22 日に、Google Cloud blog に投稿されたものの抄訳です。

BigQuery での時系列分析を簡素化する新しい SQL 機能のプレビュー版をお客様にご案内できることを嬉しく思います。これらの新機能により、最も一般的な 2 つの時系列オペレーション（ウィンドウ処理とギャップ充填）を実行するクエリの作成が簡素化されます。さらに、RANGE データ型とサポート関数の提供も開始します。RANGE 型は連続した時間枠を表すため、値の時間ベースの状態を記録するのに役立ちます。これらの機能を組み合わせれば、BigQuery のデータに対する時系列クエリを簡単に作成できるようになります。

時系列分析とデータウェアハウス

時系列データは、多くの企業にとって重要であり、増加し続けているデータクラスです。デバッグログ、指標、イベントログなどの運用データは本質的に時間指向であり、貴重な情報が多く含まれています。さらに、製造、科学研究などの分野で一般的なコネクテッドデバイスは、分析を要する重要な指標を継続的に生成します。これらのデータはすべて同じパターンに従います。データは特定の時点の値として報告され、そのデータから意味を導き出すには、時間の経過とともにどのように変化するのかを理解する必要があります。

従来、時系列分析の問題を抱えるユーザーはドメイン固有のソリューションに依存していたため、データサイロへのアクセスが困難でした。また、多くの場合、独自のクエリ言語が使用されているため、使用するにはさらに専門知識が必要です。時系列データを他のデータと合わせて分析しようとしている組織は、データウェアハウスにデータをエクスポートする前に、1 つのストア内のデータをクリーンアップして正規化するパイプラインに投資する必要があります。そのため、分析情報を取得するまでに時間がかかり、データのメリットを活用できるユーザーが、すぐにデータを得られなくなる可能性があります。BigQuery SQL に時系列分析機能を追加すれば、この貴重なデータへのアクセスが民主化されます。組織は、BigQuery のスケーラブルなサーバーレスアーキテクチャを活用して、高価なパイプラインを構築して維持することなく、時系列データを他のビジネスデータとともに保存および分析できるようになりました。

時系列ウィンドウ処理とギャップ充填機能のご紹介

多くの時系列クエリの最初のステップは、個々のデータポイントを特定の期間とアライメントの出力ウィンドウにマッピングすることです。たとえば、過去 12 時間にわたって 10 分ごとにサンプリングされた値の平均を知りたいとします。基礎となるデータは任意の時間にサンプリングされることが多いため、ユーザーのクエリには入力時間を出力時間ウィンドウにマッピングするロジックが必要になります。BigQuery の新しい時間バケット化関数は、このマッピングを実行するためのフル機能を提供します。

1 日の中のある期間に記録された大気質指数（AQI）と気温を示す時系列データセットを使用した例を見てみましょう。テーブルの各行は、時系列データの 1 つのサンプルを表しています。このケースでは、郵便番号 60606 の単一の時系列がありますが、テーブルには、すべて郵便番号で表される複数の時系列のデータを簡単に保持できます。

読み込んでいます...

追加の処理を行う前に、各行を 2 時間幅の特定の時間バケットに割り当てます。また、これらのウィンドウ内を集計して、平均 AQI と最高温度を計算します。

読み込んでいます...

出力ウィンドウの幅を定義するために、任意の有効な間隔を使用できることに注意してください。ウィンドウを 00:00:00 以外の時刻に開始したい場合は、ウィンドウを整列するための任意の原点を定義することもできます。

ギャップ充填は、基盤になるデータの時間的なギャップに対処する、時系列分析のもう 1 つの一般的な手順です。指標を生成するサーバーがリセットされ、進行中のデータフローが停止する場合があります。ユーザートラフィックが不足すると、イベントデータに欠落が生じる可能性があります。あるいは、データが本質的にスパースである場合もあります。ユーザーは、他のデータとの結合や、グラフ作成の準備をする前に、これらのギャップを埋める方法を制御する必要があります。それを行うのが、新しくリリースされた GAP_FILL テーブル値関数です。

前の例では、04:00:00 と 05:59:59 の間に元データがないため、出力データでは 02:00 と 06:00 の間にギャップがあります。このデータを他の時系列データと結合する前に、そのギャップを埋めます。以下では、GAP_FILL TVF: "last observation carried forward" (locf) と線形補間によってサポートされるバックフィルの 2 つのモードを示します。TVF は null の挿入もサポートしています。

読み込んでいます...

WITH aggregated_2_hr AS (
  SELECT
    TIMESTAMP_BUCKET(time, INTERVAL 2 HOUR) AS time,
    zip_code,
    CAST(AVG(aqi) AS INT64) AS aqi,
    MAX(temperature) AS max_temperature
  FROM mydataset.environmental_data_hourly
  GROUP BY zip_code, time
  ORDER BY zip_code, time)

SELECT *
FROM GAP_FILL(
  TABLE aggregated_2_hr,
  ts_column => 'time',
  bucket_width => INTERVAL 2 HOUR,
  partitioning_columns => ['zip_code'],
  value_columns => [
    ('aqi', 'locf'),
    ('max_temperature', 'linear')
  ]
)
ORDER BY zip_code, time;

+---------------------+----------+-----+-----------------+
|        time         | zip_code | aqi | max_temperature |
+---------------------+----------+-----+-----------------+
| 2020-09-08 00:00:00 |    60606 |  23 |              66 |
| 2020-09-08 02:00:00 |    60606 |  22 |              60 |
| 2020-09-08 04:00:00 |    60606 |  22 |              58 |
| 2020-09-08 06:00:00 |    60606 |  25 |              56 |
+---------------------+----------+-----+-----------------+

GAP_FILL を使用すれば、最初に入力データをバケット化することなく、整列された出力データを生成することもできます。

これらの新しいバケット化オペレーションとギャップ充填オペレーションを組み合わせることで、時系列分析の重要な構成要素が提供されます。ユーザーは、BigQuery のサーバーレスアーキテクチャ、高スループットのストリーミング取り込み、大規模並列処理、組み込み AI を活用して、別のデータサイロに依存することなく、時系列データと他のビジネスデータから貴重な分析情報を引き出すことができるようになりました。

重要なのは、これらの新しい関数が、専門家が使い慣れている標準 SQL 構文およびセマンティクス分析に沿っていることです。新しい言語や新しい計算モデルを学ぶ必要はありません。これらの機能を既存のワークフローに追加するだけで、分析情報の提供を開始できるのです。

RANGE データ型のご紹介

RANGE 型と関数を使用すれば、連続したデータウィンドウの操作が容易になります。たとえば、RANGE<DATE> "[2024-01-01, 2024-02-01)" は、2024-01-01 から始まり 2024-02-01 を除くすべての DATE 値を表します。RANGE にはどちらの端かの制限がなく、それぞれ時間の始まりと終わりを表すこともできます。RANGE は、連続した期間にわたって true または有効な状態を表すのに役立ちます。ユーザーへの割り当て、さまざまな時期の通貨の為替レート、システム設定の値、アルゴリズムのバージョン番号などは、どれも好例です。

また、クエリ時に RANGE を結合または拡張できる機能の提供も開始します。RANGE_SESSIONIZE は、重複または隣接する RANGE を 1 つの行に結合できる TVF です。以下の表は、ユーザーがこの機能を使用して、同じ情報を含む小さなテーブルを作成する方法を示しています。

読み込んでいます...

RANGE_SESSIONIZE は、重複する範囲をすべて検索し、（sensor_id, flow）パーティション内で重複するすべての範囲の集合であるセッション範囲を出力します。最後にセッション範囲ごとにグループ化し、重複する範囲を組み合わせたテーブルを出力します。

読み込んでいます...

SELECT sensor_id, session_range, flow
FROM RANGE_SESSIONIZE(
  (SELECT sensor_id, duration, flow FROM mydataset.sensor_metrics),
  "duration",
  ["sensor_id", "flow"],
  "OVERLAPS")
ORDER BY sensor_id, session_range;

+-----------+--------------------------------------------+------+
| sensor_id |                session_range               | flow | 
+-----------+--------------------------------------------+------+
|         1 | [2020-01-01 12:00:01, 2020-01-01 12:10:46) | 10   |
|         1 | [2020-01-01 12:00:01, 2020-01-01 12:10:46) | 10   |
|         1 | [2020-01-01 12:10:27, 2020-01-01 12:15:56) | 11   |
|         2 | [2020-01-01 12:05:08, 2020-01-01 12:15:42) | 21   |
|         2 | [2020-01-01 12:05:08, 2020-01-01 12:15:42) | 21   |
+-----------+--------------------------------------------+------+

SELECT sensor_id, session_range, flow, SUM(spins)
FROM RANGE_SESSIONIZE(
  TABLE mydataset.sensor_metrics, 
  "duration",
  ["sensor_id", "flow"],
  "OVERLAPS")
GROUP BY sensor_id, session_range, flow
ORDER BY sensor_id, session_range

+-----------+--------------------------------------------+------+-------+
| sensor_id |                session_range               | flow | spins |
+-----------+--------------------------------------------+------+-------+
|         1 | [2020-01-01 12:00:01, 2020-01-01 12:10:46) | 10   |    21 |
|         1 | [2020-01-01 12:10:27, 2020-01-01 12:15:56) | 11   |     4 |
|         2 | [2020-01-01 12:05:08, 2020-01-01 12:15:42) | 21   |    12 |
+-----------+--------------------------------------------+------+-------+

RANGE を使用すれば、さらに多くのことができます。たとえば、RANGE を使用して、タイムスタンプテーブルと RANGE テーブルを JOIN することや、AS OF JOIN の動作をエミュレートすることができます。その他の例については、RANGE と時系列のドキュメントを参照してください。