Timestreamが備えるビルトイン関数で時系列データの欠損値を自動補完してみた

CX事業本部@大阪の岩田です。時系列データベースTimestreamのビルトイン関数について調べてみたので内容をご紹介します。

Timestreamのクエリで利用可能なビルトイン関数

時系列データベースであるTimestreamのクエリ言語には、時系列データを扱うために特化した関数がビルトインされており、これらの関数を利用することで効率的に時系列データを分析することが可能です。

時系列処理のための関数は大きくviewsとfunctionsに分類されます。それぞれの詳細を見ていきましょう。

Timeseries views

まずはTimeseries viewsです

• CREATE_TIME_SERIES
• UNNEST

という2つの関数が提供されており、これらの関数を利用することで生データの型とtimeseries型を相互に変換することが可能です。実例を見て頂いた方が分かりやすいと思うので、実際にSQLを実行しながら結果を確認してみましょう。

なお以後のクエリはTimestreamが用意しているサンプルデータ「DevOps」を利用して作成したテーブルに対して実行していきます。データベース名は sample-db 、テーブル名はDevOpsで作成しています。

CREATE_TIME_SERIES

生データを timeseries型に変換する関数です。 関数のシグネチャはCREATE_TIME_SERIES(time, measure_value::<data_type>)で、第一引数に時刻を、第2引数に生データの測定値を指定します。試しにサンプルテーブルからホストごとのCPU使用率をtimeseries型で取得してみましょう。

CREATE_TIME_SERIES(time, measure_value::double) as cpu_utilizationと指定することでcpu使用率の計測値をtimeseries型に変換します。

SELECT
	region,
    az,
    hostname,
    CREATE_TIME_SERIES(time, measure_value::double) as cpu_utilization
FROM
	"sample-db"."DevOps" 
WHERE
	measure_name= 'cpu_utilization'
GROUP BY
	region,
    az,
    hostname

実行結果です。

実行結果からView 8 more row(s)のリンクをクリックすると timeseries型データの詳細が確認できます。

UNNEST

続いてUNNEST関数です。こちらの関数はAthenaを使った分析などでも良く利用するのではないでしょうか？timeseries型のデータをフラットに展開することが可能です。関数のシグネチャはUNNEST(timeseries) AS <alias_name> (time_alias, value_alias)です。展開後のテーブル、カラムそれぞれにエイリアスを付けることができます。試しに先程CREATE_TIME_SERIES関数を使ってtimeseries型に変換したデータをさらに展開してみましょう。

WITH time_series_cpu_utilization AS(
  SELECT
      region,
      az,
      hostname,
      CREATE_TIME_SERIES(time, measure_value::double) as cpu_utilization
  FROM
      "sample-db"."DevOps" 
  WHERE
      measure_name= 'cpu_utilization'
  GROUP BY
      region,
      az,
      hostname
)
SELECT
		region,
    az,
    hostname,
    avg(t.cpu_util) AS cpu_avg
FROM
	time_series_cpu_utilization
CROSS JOIN
	UNNEST(cpu_utilization) AS t (time, cpu_util)
GROUP BY
    region,
    az,
    hostname	

実行結果です。

timeseries型のデータが展開されていることが分かります。※この例だと最初からCREATE_TIME_SERIESせずに単純にSELECTすれば良いので、あまりいい例では無いですが...

Time series functions

続いてfunctionsについて見ていきましょう。Timestreamは以下の関数を提供しており、これらの関数を利用することで欠損値を補完することが可能です。

• interpolate_linear
  • 線形補間で欠損値を補完
• interpolate_spline_cubic
  • 3次スプラインで欠損値を補完
• interpolate_locf
  • 最後にサンプリングされた値で欠損値を補完
• interpolate_fill
  • 定数で欠損値を補完

例えば、5分毎にIoT機器からTimestreamにデータを連携するシステムがあったとして、10:00 ~ 11:00の間はうまくデータ連携できていなかったとします。この場合、10:00 ~ 11:00の生データは欠損することになりますが、これらの関数を利用することで10:00 ~ 11:00のデータを補完することが可能です。

各関数のシグネチャと戻り値の型は以下の通りです。シグネチャによって戻り値の型がtimeseries型もしくはdouble型となります。分析のユースケースに応じてシグネチャを使い分けましょう。

シグネチャ	戻り値の型
interpolate_linear(timeseries, array[timestamp])	timeseries
interpolate_linear(timeseries, timestamp)	double
interpolate_spline_cubic(timeseries, array[timestamp])	timeseries
interpolate_spline_cubic(timeseries, timestamp)	double
interpolate_locf(timeseries, array[timestamp])	timeseries
interpolate_locf(timeseries, timestamp)	double
interpolate_fill(timeseries, array[timestamp], double)	timeseries
interpolate_fill(timeseries, timestamp, double)	double

先程CREATE_TIME_SERIESを紹介した時のSQLをベースに実際に欠損値の補完を試してみます。なお、各関数が欠損値を補完するアルゴリズムの詳細や、メリット/デメリットについては良く理解できていないので、詳細な説明は割愛させて頂きますm(_ _)m

interpolate_xxx系の関数を利用し、生データには存在しない2020-10-14 06:00時点の計測値をSELECTしてみましょう

SELECT
	region,
    az,
    hostname,
    CREATE_TIME_SERIES(time, measure_value::double) as cpu_utilization,
    interpolate_linear(CREATE_TIME_SERIES(time, measure_value::double) ,'2020-10-14 06:00:00.000000000') as linear,
    interpolate_spline_cubic(CREATE_TIME_SERIES(time, measure_value::double) ,'2020-10-14 06:00:00.000000000') as cubic,
    interpolate_locf(CREATE_TIME_SERIES(time, measure_value::double) ,'2020-10-14 06:00:00.000000000') as locf,
    interpolate_fill(CREATE_TIME_SERIES(time, measure_value::double) ,'2020-10-14 06:00:00.000000000', 10.0) as fill
FROM
	"sample-db"."DevOps" 
WHERE
	measure_name= 'cpu_utilization'
GROUP BY
	region,
    az,
    hostname

実行結果です。

指定した関数のアルゴリズムに応じて欠損値が補完されていることが分かります。hostnameがhost-5xvZPのデータを例に各関数で補完された値の詳細を見ていきましょう。

• interpolate_linear
  • 補完された値は44.0917814522025でした
  • ざっくりですが2020-10-14 6:00前後の計測値の平均に近い値になっていることが分かります
• interpolate_spline_cubic
  • 補完された値は-910.7790603241914でした。
  • 3次スプラインがどういうものか理解できていないので、ここの説明は省略しますm(_ _)m
• interpolate_locf
  • 補完された値は44.357071437282734
  • 2020-10-14 6:00の直前、2020-10-14 5:37:26の計測値で補完されていることが分かります
• interpolate_fill
  • 補完された値は10.0でした
  • interpolate_fillの引数で指定した定数で補完されていることが分かります

便利ですね！

まとめ

Timestreamのビルトイン関数についてご紹介しました。時系列データベースというコンセプト通り、時系列データを扱うために便利な関数が色々と提供されていることが分かります。これまで別のデータベースにデータを溜め込みつつ、アプリケーション側で時系列処理を行っていたようなケースではデータベースをTimestreamに移行することで分析が捗りそうです。まだTimestreamを試していない方は是非ともお試し下さい！！