TimescaleDB
概要
OSS の時系列データベース
SQL を喋る (Postgres の拡張)
https://docs.timescale.com/v1.3/main
TimescaleDB と PostgreSQL DB は単一の PostgresSQL サーバーに同居できる
インタフェース言語として SQL を利用する
テーブル、インデックス、トリガー ような標準的なデータベースオブジェクトを保持している
主要な概念
概要
PostgreSQL の拡張なので、PostgreSQL インスタンス上で動作する
信頼性やセキュリティ、様々なサードパーティ製ツール等の PostgreSQL のメリットを享受できる
Hypertables
全期間内の全データが保持されているように見える抽象的なテーブル
TimescaleDB とのユーザのすべてのやりとりは hypertable との仮想的なやりとりになる
カラム名とタイプ、少なくとも1つの時間を指定するカラム、そして追加のパーティショニングキー (option) で定義される
単一の TimescaleDB にて、異なるスキーマを保持した hypertable を複数保持できる
作成のためには、CREATE TABLE と SELECT create_hypertable() を利用する
Chunks
TimescaleDB は、内部的に hypertable を複数の chunks に分割する
単一の chunk は、特定の期間及びパーティションキーの範囲に対応する
クエリプランナーは、クエリを解決するのにアクセスする必要のある chunk 数を最小化するように動く
各 chunk の実態は、データベーステーブルになる
データモデル
narrow-table モデルと wide-table モデルの2つをサポートしているとのこと。
サンプルデータとして、1000 の IoT デバイスが様々な環境からデータを取得しているパターンを考える。
識別子 device_id, timestamp
メタデータ location_id, dev_type, firmware_version, customer_id
デバイスメトリクス cpu_1m_avg, free_mem, used_mem, net_rssi, net_loss, battery
センサーメトリクス temperature, humidity, pressure, CO, NO2, PM10
table:sample
timestamp device_id cpu_1m_avg free_mem temperature location_id dev_type
2017-01-01 01:02:00 abc123 80 500MB 72 335 field
2017-01-01 01:02:23 def456 90 400MB 64 335 roof
2017-01-01 01:02:30 ghi789 120 0MB 56 77 roof
2017-01-01 01:03:12 abc123 80 500MB 72 335 field
2017-01-01 01:03:35 def456 95 350MB 64 335 roof
2017-01-01 01:03:42 ghi789 100 100MB 56 77 roof
narrow-table model
ほとんどの時系列データベースはデータを以下の方法で表現している。
各メトリクスを別々のエンティティとして表現する
メトリクスを、時間 と 値 のペアの連なりで保持する
メタデータの値は tag-set として表現し、メトリクスと関連づける
各々の metric/tag-set ペアが、個別の time series として表現される。
この表現方法だと、サンプルデータ (の前半) は以下のような 9 つの異なる time series となり、各々はユニークなタグのセットで定義される。name がメトリクス名であり、それにメタデータが付与されている。
code:json
1. {name: cpu_1m_avg, device_id: abc123, location_id: 335, dev_type: field}
2. {name: cpu_1m_avg, device_id: def456, location_id: 335, dev_type: roof}
3. {name: cpu_1m_avg, device_id: ghi789, location_id: 77, dev_type: roof}
4. {name: free_mem, device_id: abc123, location_id: 335, dev_type: field}
5. {name: free_mem, device_id: def456, location_id: 335, dev_type: roof}
6. {name: free_mem, device_id: ghi789, location_id: 77, dev_type: roof}
7. {name: temperature, device_id: abc123, location_id: 335, dev_type: field}
8. {name: temperature, device_id: def456, location_id: 335, dev_type: roof}
9. {name: temperature, device_id: ghi789, location_id: 77, dev_type: roof}
この表現方法だと、各タグの カーディナリティ の組み合わせ分レコードが必要になる。カーディナリティ が高くなりすぎると問題が起こるため、時系列データベースによってはデバイスタイプの種類を制限したり、1データベース内に保持可能なデバイス数を制限したりする。
narrow モデルは、メトリクス毎にタイムスタンプを保持する必要があるため、同一タイムスタンプで保持するメトリクス数が多いとパフォーマンスが落ちるし、ストレージも圧迫する。また、関係する異なるメトリクスを取得しようとすると、メトリクス毎にJOINが必要になるため、クエリが複雑になる。複数のメトリクスを一緒に取得する場合は、wide table フォーマットでそれらを保持した方が良い。
wide-table model
異なるメトリクス同士を一度に取得するのに JOIN が必要ない方式。複数のメトリクスに対してタイムスタンプが1つしか保持されない。タイムスタンプを識別子とした、典型的な RDB のテーブルと一緒。
関連データとの JOIN
RDB と似たような JOIN をサポートもしている。追加のメタデータを別テーブルに保持しておき、それと JOIN する。例えば、location_id と追加のメタデータをマッピングした location テーブルを作成する。
table:location
location_id name latitude longitude zip_code region
42 Grand Central Terminal 40.7527° N 73.9772° W 10017 NYC
77 Lobby 7 42.3593° N 71.0935° W 02139 Massachusetts
クエリー時に、元のテーブルとこの location テーブルを JOIN することで、location テーブル上のメタデータ、例えば zip_code などから絞り込んでクエリーすることができる。
このような外部テーブルを非正規化して1つの巨大なテーブルを作ることもできるが、そうするとデータが肥大化するし、管理が難しくなる。
試してみる
立ち上げ
code:sh
docker run -d --name timescaledb -p 5432:5432 -e POSTGRES_PASSWORD=password timescale/timescaledb:latest-pg11
# コンテナ内のクライアントソフトを利用してアクセスできる
docker exec -it timescaledb psql -U postgres
code:sql
-- データベースの作成
CREATE database tutorial;
-- データベースにアクセス
\c tutorial
-- TimescaleDB で拡張する
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
hypertable の作成
code:sql
CREATE TABLE conditions (
time TIMESTAMPTZ NOT NULL,
location TEXT NOT NULL,
temperature DOUBLE PRECISION NULL
);
-- カラムを追加するとき
ALTER TABLE conditions
ADD COLUMN humidity DOUBLE PRECISION NULL;
-- hypertable を作成する
SELECT create_hypertable('conditions', 'time');
-- データの挿入
INSERT INTO conditions(time, location, temperature, humidity)
VALUES (NOW(), 'office', 70.0, 50.0);
INSERT INTO conditions
VALUES
(NOW(), 'office', 70.0, 50.0),
(NOW(), 'basement', 66.5, 60.0),
(NOW(), 'garage', 77.0, 65.2);
-- データの取得
tutorial=# SELECT * FROM conditions LIMIT 100;
time | location | temperature | humidity
-------------------------------+----------+-------------+----------
2019-05-11 05:36:09.472717+00 | office | 70 | 50
2019-05-11 05:36:17.084085+00 | office | 70 | 50
2019-05-11 05:36:17.084085+00 | basement | 66.5 | 60
2019-05-11 05:36:17.084085+00 | garage | 77 | 65.2
(4 rows)
-- 過去三時間において、各場所における15分毎の情報を、時間及び温度で並び替えして出力する
SELECT time_bucket('15 minutes', time) AS fifteen_min,
location, COUNT(*),
MAX(temperature) AS max_temp,
MAX(humidity) AS max_hum
FROM conditions
WHERE time > NOW() - interval '3 hours'
GROUP BY fifteen_min, location
ORDER BY fifteen_min DESC, max_temp DESC;
特殊なデータの取得方法がいくつかある。
https://docs.timescale.com/v1.3/using-timescaledb/reading-data#advanced-analytics