Aiven で PostgreSQL インタフェースの時系列データベースである TimescaleDB を試してみる
ウィスキー、シガー、パイプをこよなく愛する大栗です。
相変わらずクラウドデータプラットフォームの 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 を使って時間を追うごとに変化するデータを高速で効率的に扱うことが可能になっています。
また時系列データの分析を行いやすくするための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 です。
解答したファイルがカレントディレクトリにあるとして\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 版も触ってみようかなと思っています。
![[Fivetran] Aiven for Apache Kafkaを接続してストリーミングデータをDWHに同期してみた](https://devio2023-media.developers.io/wp-content/uploads/2020/04/fivetran-logo_1200x630.png){kind=logo}
