Aiven で PostgreSQL インタフェースの時系列データベースである TimescaleDB を試してみる

Guri / Hajime Oguri

2022.01.26

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

ウィスキー、シガー、パイプをこよなく愛する大栗です。

相変わらずクラウドデータプラットフォームの Aiven を触っています。Aiven for PostgreSQL では、PostgreSQL の拡張として実装されている時系列データベースの TimescaleDB を利用できます。

時系列データベース

時系列データベースとは、その名の通りで時系列で変化するデータを効率的に格納や分析するためのデータベースです。ITシステムの監視やIoTデータ、環境モニタリングなどで利用されます。基本的にデータが発生した瞬間に保存していくので、一般的に時系列データには以下のような特徴があります。

  • 時間中心：レコードには常にタイムスタンプがあります。
  • 追加のみ：データは基本的に追加のみで更新されません。
  • 直近のデータ：新規のデータは通常最近の時間帯に関するもので、古い時間帯に関する更新や埋戻しはほとんど発生しません。

時系列データベースの実装だと有名なものにInfluxData社が開発しているInfluxDBなどが有ります。

TimescaleDB

TimescaleDB はアメリカTimescale社が開発している時系列データベースです。PostgreSQL の拡張としてパッケージングされており、完全なSQLをサポートしています。

PostgreSQL、InfluxDB、MongoDBよりも10~100倍高速なクエリを実現し、1秒あたり数百万データポイントの書き込みが可能な、リレーショナルと時系列を一緒に扱えるデータベースとなっています。

TimescaleDB では、Hypertable と呼ぶ単一のテーブルに見えるものを使います。Hypertable は実際にデータを格納する Chunk を複数まとめた仮想ビューのようなものになっています。この Hypertable を使って時間を追うごとに変化するデータを高速で効率的に扱うことが可能になっています。

Hypertables and chunks

また時系列データの分析を行いやすくするためのHyperfunctionと呼ばれる関数群なども実装されています。

注意すべき点としてライセンスが有ります。基本的な部分である TimescaleDB Open Source は Apache License 2.0 を採用したオープンソースソフトウェアになっています。しかし、一部の機能が拡張された TimescaleDB Communityは独自のTimescale License(以下 TSL)になっています。大まかに TSL を説明すると、ホストされた Database-as-a-Service として TimescaleDB を提供しない限りすべての機能を無料で使用できるライセンスです。

やってみた

Aiven では TimescalDB の拡張がインストール済みなので有効にするだけですぐに使えます。なお、インストールされているライセンスは TimescaleDB Open Source となっているため一部機能が制限されています。

Aiven で PostgreSQL を起動するには、以下のエントリをご参照ください。

まずは、作成した PostgreSQL サービスにログインします。

% psql -h pg-aws-timescale-1-hajime-ff19.aivencloud.com -p 12492 -U avnadmin -d defaultdb
Password for user avnadmin:
psql (14.1, server 13.5)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

TimescaleDB を有効にする新しいデータベースsample1を作成します。

defaultdb=> CREATE DATABASE sample1;
CREATE DATABASE
defaultdb=> \c sample1;
psql (14.1, server 13.5)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
You are now connected to database "sample3" as user "avnadmin".

TimescaleDB 拡張をデータベースに読み込みます。

sample1=> CREATE EXTENSION timescaledb CASCADE;

WARNING:
WELCOME TO
 _____ _                               _     ____________
|_   _(_)                             | |    |  _  \ ___ \
  | |  _ _ __ ___   ___  ___  ___ __ _| | ___| | | | |_/ /
  | | | |  _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \
  | | | | | | | | |  __/\__ \ (_| (_| | |  __/ |/ /| |_/ /
  |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
               Running version 2.5.1
For more information on TimescaleDB, please visit the following links:

 1. Getting started: https://docs.timescale.com/timescaledb/latest/getting-started
 2. API reference documentation: https://docs.timescale.com/api/latest
 3. How TimescaleDB is designed: https://docs.timescale.com/timescaledb/latest/overview/core-concepts

Note: Please enable telemetry to help us improve our product by running: ALTER DATABASE "defaultdb" SET timescaledb.telemetry_level = 'basic';

CREATE EXTENSION

ここからは TimescaleDB のドキュメントのGetting startedを参考に進めます。

Hypertable を作成します。普通にCREATE TABLE文でテーブルを作成して、create_hypertable関数を使ってtime列を時間列に設定して Hypertable に変換します。

sample1=> CREATE TABLE IF NOT EXISTS weather_metrics (

   time TIMESTAMP WITHOUT TIME ZONE NOT NULL,
   timezone_shift int NULL,
   city_name text NULL,
   temp_c double PRECISION NULL,
   feels_like_c double PRECISION NULL,
   temp_min_c double PRECISION NULL,
   temp_max_c double PRECISION NULL,
   pressure_hpa double PRECISION NULL,
   humidity_percent double PRECISION NULL,
   wind_speed_ms double PRECISION NULL,
   wind_deg int NULL,
   rain_1h_mm double PRECISION NULL,
   rain_3h_mm double PRECISION NULL,
   snow_1h_mm double PRECISION NULL,
   snow_3h_mm double PRECISION NULL,
   clouds_percent int NULL,
   weather_type_id int NULL
);

sample1=> SELECT create_hypertable('weather_metrics','time');

時系列データを挿入します。データはOpenWeatherMapのもので、1979年1月1日から2021年4月27日までの各都市の気象情報が含まれています。

以下のファイルをダウンロードして解答します。ファイルサイズは圧縮時で 70.9 MB、解凍時で 337.6 MB です。

weather_data.zip

解答したファイルがカレントディレクトリにあるとして\copyコマンドでデータをロードします。

\copy weather_metrics (time, timezone_shift, city_name, temp_c, feels_like_c, temp_min_c, temp_max_c, pressure_hpa, humidity_percent, wind_speed_ms, wind_deg, rain_1h_mm, rain_3h_mm, snow_1h_mm, snow_3h_mm, clouds_percent, weather_type_id) from './weather_data.csv' CSV HEADER;

時系列データにクエリを実行してみましょう。

まずは普通に過去2年間の各都市の平均気温を表示してみます。

sample1=> SELECT city_name, avg(temp_c)
sample1-> FROM weather_metrics
sample1-> WHERE time > now() - INTERVAL '2 years'
sample1-> GROUP BY city_name;
    city_name     |        avg         
------------------+--------------------
 Vienna           | 10.209269094138605
 Pietermaritzburg | 19.348735893702273
 Austin           | 19.150044378698183
 Lisbon           |  16.37932587240041
 Nairobi          |  19.40911903895161
 Stockholm        |  7.276496837979902
 New York         | 11.979102821699977
 San Francisco    | 13.880734437568263
 Princeton        | 11.235531123139346
 Toronto          |  8.201053289297494
(10 rows)

過去5年間の各都市の総降雪量も見てみましょう。

sample1=> SELECT city_name, sum(snow_1h_mm)
sample1-> FROM weather_metrics
sample1-> WHERE time > now() - INTERVAL '5 years'
sample1-> GROUP BY city_name;
    city_name     |        sum         
------------------+--------------------
 Austin           |  617.8499999999998
 Lisbon           |                   
 Nairobi          |                   
 New York         |  771.1599999999984
 Pietermaritzburg |                   
 Princeton        |  685.7499999999976
 San Francisco    |                   
 Stockholm        | 326.66999999999996
 Toronto          |  592.3599999999988
 Vienna           |  831.9299999999997
(10 rows)

ここで TimescaleDB 専用の関数を使って分析してみましょう。time_bucket()を使います。

time_bucket()はdate_truncに似ていますが、任意時間で使用できます。この関数を使って、過去10ヶ月の各都市の15日平均の気温を取得してみます。

sample1=> SELECT time_bucket('15 days', time) as "bucket"
sample1->    ,city_name, avg(temp_c)
sample1->    FROM weather_metrics
sample1->    WHERE time > now() - (10* INTERVAL '1 month')
sample1->    GROUP BY bucket, city_name
sample1->    ORDER BY bucket DESC;
       bucket        |    city_name     |        avg         
---------------------+------------------+--------------------
 2021-04-27 00:00:00 | Lisbon           |           16.32125
 2021-04-27 00:00:00 | Toronto          |  7.467083333333334
 2021-04-27 00:00:00 | Austin           | 22.714583333333334
 2021-04-27 00:00:00 | Pietermaritzburg | 21.053333333333335
 2021-04-27 00:00:00 | New York         | 12.003750000000002
 2021-04-27 00:00:00 | Stockholm        | 3.7720833333333332
 2021-04-27 00:00:00 | Princeton        | 12.638333333333334
 2021-04-27 00:00:00 | Vienna           |  8.724166666666667
 2021-04-27 00:00:00 | Nairobi          | 19.898333333333337
 2021-04-27 00:00:00 | San Francisco    |           11.79625
 2021-04-12 00:00:00 | Pietermaritzburg |  19.21772222222224
 2021-04-12 00:00:00 | Stockholm        | 4.8291184573002734
 2021-04-12 00:00:00 | Nairobi          |  19.23444444444446
 2021-04-12 00:00:00 | Vienna           |   7.61509485094851
 2021-04-12 00:00:00 | New York         |   11.3755376344086
 2021-04-12 00:00:00 | Lisbon           | 16.128189189189182
 2021-04-12 00:00:00 | Toronto          |  7.082105263157897
 2021-04-12 00:00:00 | Austin           |  18.47928388746802
 2021-04-12 00:00:00 | San Francisco    | 12.085027777777782
 2021-04-12 00:00:00 | Princeton        | 10.796771653543304
 2021-03-28 00:00:00 | Toronto          | 7.6546388888888925
 2021-03-28 00:00:00 | Lisbon           | 16.197348066298343
 2021-03-28 00:00:00 | Vienna           |  9.710969529085872
 2021-03-28 00:00:00 | New York         |  10.83059299191374
 2021-03-28 00:00:00 | San Francisco    | 12.629777777777786
 2021-03-28 00:00:00 | Nairobi          | 19.806611111111117
 2021-03-28 00:00:00 | Princeton        |  10.88007853403141
 2021-03-28 00:00:00 | Pietermaritzburg |  20.33491666666667
 2021-03-28 00:00:00 | Stockholm        |  4.997260638297875
 2021-03-28 00:00:00 | Austin           | 18.981123287671235
 2021-03-13 00:00:00 | New York         | 15.265909090909092
 2021-03-13 00:00:00 | Vienna           | 11.006363636363634
 2021-03-13 00:00:00 | Toronto          |  6.322499999999999
 2021-03-13 00:00:00 | Lisbon           | 14.701590909090907
 2021-03-13 00:00:00 | San Francisco    |            12.3775
 2021-03-13 00:00:00 | Austin           | 19.939090909090908
 2021-03-13 00:00:00 | Nairobi          | 21.822954545454543
 2021-03-13 00:00:00 | Stockholm        |  6.579318181818181
 2021-03-13 00:00:00 | Pietermaritzburg | 18.834090909090907
 2021-03-13 00:00:00 | Princeton        | 16.448636363636368
(40 rows)

類似の関数でtime_bucket_gapfill()という関数もあります。こちらは時間枠にデータが存在しなかった場合でもレコードを埋められるそうです。ただし、TimescaleDB Community でしか利用できないので今回は試せていません。

ライセンスが対象でないのでエラーメッセージが表示されます。

sample1=> SELECT time_bucket_gapfill('30 days', time) as bucket,
sample1->    city_name, sum(snow_1h_mm) as sum
sample1->    FROM weather_metrics
sample1->    WHERE time > now() - INTERVAL '1 year' AND time < now()
sample1->    GROUP BY bucket, city_name
sample1->    ORDER BY bucket DESC;
ERROR:  function "time_bucket_gapfill" is not supported under the current "apache" license
HINT:  Upgrade your license to 'timescale' to use this free community feature.

さいごに

このように TimescaleDB は時系列データの効率的なクエリが可能になっています。Apache License 版では、機能が限定的なので、Community 版も触ってみようかなと思っています。

*

関連記事

パートナー探しのために出店していたブース企業に突撃した② #GoogleCloudNext

室井靖成

2023.09.01