958 小米6 小米MIX

時序數據庫分析 - TimescaleDB時序數據庫介紹

標簽

PostgreSQL , TimescaleDB , 時間序列 , 物聯網 , IoT

背景

隨著物聯網的發展，時序數據庫的需求越來越多，比如水文監控、工廠的設備監控、國家安全相關的數據監控、通訊監控、金融行業指標數據、傳感器數據等。

在互聯網行業中，也有著非常多的時序數據，例如用戶訪問網站的行為軌跡，應用程序產生的日誌數據等等。

時序數據有幾個特點

1. 基本上都是插入，沒有更新的需求。

2. 數據基本上都有時間屬性，隨著時間的推移不斷產生新的數據，舊的數據不需要保存太久。

業務方對時序數據通常有幾個查詢需求

1. 獲取最新狀態，查詢最近的數據（例如傳感器最新的狀態）

2. 展示區間統計，指定時間範圍，查詢統計信息，例如平均值，最大值，最小值，計數等。。。

3. 獲取異常數據，根據指定條件，篩選異常數據

時序數據庫應該具備的特點

1. 壓縮能力

通常用得上時序數據庫的業務，傳感器產生的數據量都是非常龐大的，數據壓縮可以降低存儲成本。

2. 自動rotate

時序數據通常對曆史數據的保留時間間隔是有規定的，例如一個線上時序數據業務，可能隻需要保留最近1周的數據。

為了方便使用，時序數據庫必須有數據自動rotate的能力。

3. 支持分片，水平擴展

因為涉及的傳感器可能很多，單個節點可能比較容易成為瓶頸，所以時序數據庫應該具備水平擴展的能力，例如分表應該支持水平分區。

4. 自動擴展分區，

業務對時序數據的查詢，往往都會帶上對時間區間進行過濾，因此時序數據通常在分區時，一定會有一個時間分區的概念。時序數據庫務必能夠支持自動擴展分區，減少用戶的管理量，不需要人為的幹預自動擴展分區。例如1月份月末，自動創建2月份的分區。

5. 插入性能

時序數據，插入是一個強需求。對於插入性能要求較高。

6. 分區可刪除

分區可以被刪除，例如保留1個月的數據，1個月以前的分區都可以刪除掉。

7. 易用性(SQL接口)

SQL是目前最通用的數據庫訪問語言，如果時序數據庫能支持SQL是最好的。

8. 類型豐富

物聯網的終端各異，會有越來越多的非標準類型的支持需求。例如采集圖像的傳感器，數據庫中至少要能夠存取圖像的特征值。而對於其他垂直行業也是如此，為了最大程度的詮釋業務，必須要有精準的數據類型來支撐。

9. 索引接口

支持索引，毫無疑問是為了加速查詢而引入的。

10. 高效分析能力

時序數據，除了單條的查詢，更多的是報表分析或者其他的分析類需求。這對時序數據庫的統計能力也是一個挑戰。

11. 其他特色

11.1 支持豐富的數據類型，數組、範圍類型、JSON類型、K-V類型、GIS類型、圖類型等。滿足更多的工業化需求，例如傳感器的位置信息、傳感器上傳的數據值的範圍，批量以數組或JSON的形式上傳，傳感器甚至可能上傳圖片特征值，便於圖片的分析。（例如國家安全相關），軌跡數據的上層則帶有GIS屬性。

這個世界需要的是支持類型豐富的時序數據庫，而不是僅僅支持簡單類型的時序數據庫。

11.2 支持豐富的索引接口，因為類型豐富了，普通的B-TREE索引可能無法滿足快速的檢索需求，需要更多的索引來支持數組、JSON、GIS、圖特征值、K-V、範圍類型等。 (例如PostgreSQL的gin, gist, sp-gist, brin, rum, bloom, hash索引接口)

這兩點可以繼承PostgreSQL數據庫的已有功能，已完全滿足。

TimescaleDB介紹

TimescaleDB是基於PostgreSQL數據庫打造的一款時序數據庫，插件化的形式，隨著PostgreSQL的版本升級而升級，不會因為另立分支帶來麻煩。

TimescaleDB架構

數據自動按時間和空間分片（chunk）。

TimescaleDB具備以下特點

1. 基於時序優化

2. 自動分片（按時間、空間自動分片(chunk)）

3. 全SQL接口

4. 支持垂直於橫向擴展

5. 支持時間維度、空間維度自動分區。空間維度指屬性字段（例如傳感器ID，用戶ID等）

6. 支持多個SERVER，多個CHUNK的並行查詢。分區在TimescaleDB中被稱為chunk。

7. 自動調整CHUNK的大小

8. 內部寫優化（批量提交、內存索引、事務支持、數據倒灌）。

內存索引，因為chunk size比較適中，所以索引基本上都不會被交換出去，寫性能比較好。

數據倒灌，因為有些傳感器的數據可能寫入延遲，導致需要寫以前的chunk，timescaleDB允許這樣的事情發生(可配置)。

9. 複雜查詢優化（根據查詢條件自動選擇chunk，最近值獲取優化(最小化的掃描,類似遞歸收斂)，limit子句pushdown到不同的server,chunks，並行的聚合操作）

《時序數據合並場景加速分析和實現 - 複合索引，窗口分組查詢加速，變態遞歸加速》

10. 利用已有的PostgreSQL特性（支持GIS，JOIN等），方便的管理（流複製、PITR）

11. 支持自動的按時間保留策略（自動刪除過舊數據）

疑問

1. chunk過多，會不會影響查詢性能？

這點不需要擔心，PostgreSQL 10.0已經優化了

《PostgreSQL 10.0 preview 性能增強 - 分區表子表元信息搜索性能增強》

例子

1. 創建時序表(hypertable)

# Create a schema for a new hypertable  
CREATE TABLE sensor_data (  
"time" timestamp with time zone NOT NULL,  
device_id TEXT NOT NULL,  
location TEXT NULL,  
temperature NUMERIC NULL,  
humidity NUMERIC NULL,  
pm25 NUMERIC  
);  
  
# Create a hypertable from this data  
SELECT create_hypertable  
('sensor_data', 'time', 'device_id', 16);

2. 遷移數據到hyper table

# Migrate data from existing Postgres table into  
# a TimescaleDB hypertable  
INSERT INTO sensor_data (SELECT * FROM old_data);

3. 查詢hyper table

# Query hypertable like any SQL table  
SELECT device_id, AVG(temperature) from sensor_data  
WHERE temperature IS NOT NULL AND humidity > 0.5  
AND time > now() - interval '7 day'  
GROUP BY device_id;

4. 查詢最近異常的數據

# Metrics about resource-constrained devices  
SELECT time, cpu, freemem, battery FROM devops  
WHERE device_id='foo'  
AND cpu > 0.7 AND freemem < 0.2  
ORDER BY time DESC  
LIMIT 100;

5. 計算最近7天，每小時的異常次數

# Calculate total errors by latest firmware versions  
# per hour over the last 7 days  
SELECT date_trunc('hour', time) as hour, firmware,  
COUNT(error_msg) as errno FROM data  
WHERE firmware > 50  
AND time > now() - interval '7 day'  
GROUP BY hour, firmware  
ORDER BY hour DESC, errno DESC;

6. 計算巴士的每小時平均速度

# Find average bus speed in last hour  
# for each NYC borough  
SELECT loc.region, AVG(bus.speed) FROM bus  
INNER JOIN loc ON (bus.bus_id = loc.bus_id)  
WHERE loc.city = 'nyc'  
AND bus.time > now() - interval '1 hour'  
GROUP BY loc.region;

7. 展示最近12小時，每小時的平均值

=#  SELECT date_trunc('hour', time) AS hour, AVG(weight)  
    FROM logs  
    WHERE device_type = 'pressure-sensor' AND customer_id = 440  
      AND time > now() - interval '12 hours'  
    GROUP BY hour;  
  
 hour               | AVG(weight)  
--------------------+--------------  
 2017-01-04 12:00   | 170.0  
 2017-01-04 13:00   | 174.2  
 2017-01-04 14:00   | 174.0  
 2017-01-04 15:00   | 178.6  
 2017-01-04 16:00   | 173.0  
 2017-01-04 17:00   | 169.9  
 2017-01-04 18:00   | 168.1  
 2017-01-04 19:00   | 170.2  
 2017-01-04 20:00   | 167.4  
 2017-01-04 21:00   | 168.6

8. 監控每分鍾過載的設備數量

=#  SELECT date_trunc('minute', time) AS minute, COUNT(device_id)  
    FROM logs  
    WHERE cpu_level > 0.9 AND free_mem < 1024  
      AND time > now() - interval '24 hours'  
    GROUP BY minute  
    ORDER BY COUNT(device_id) DESC LIMIT 25;  
  
 minute             | heavy_load_devices  
--------------------+---------------------  
 2017-01-04 14:59   | 1653  
 2017-01-04 15:01   | 1650  
 2017-01-04 15:00   | 1605  
 2017-01-04 15:02   | 1594  
 2017-01-04 15:03   | 1594  
 2017-01-04 15:04   | 1561  
 2017-01-04 15:06   | 1499  
 2017-01-04 15:05   | 1460  
 2017-01-04 15:08   | 1459

9. 最近7天，按固件版本，輸出每個固件版本的報錯次數

=#  SELECT firmware_version, SUM(error_count) FROM logs  
    WHERE time > now() - interval '7 days'  
    GROUP BY firmware_version  
    ORDER BY SUM(error_count) DESC LIMIT 10;  
  
 firmware_version  | SUM(error_count)  
-------------------+-------------------  
 1.0.10            | 191  
 1.1.0             | 180  
 1.1.1             | 179  
 1.0.8             | 164  
 1.1.3             | 161  
 1.1.2             | 152  
 1.2.1             | 144  
 1.2.0             | 137  
 1.0.7             | 130  
 1.0.5             | 112  
 1.2.2             | 110

10. 某個範圍，每小時，溫度高於90度的設備數量。

=#  SELECT date_trunc('hour', time) AS hour, COUNT(logs.device_id)  
    FROM logs  
    JOIN devices ON logs.device_id = devices.id  
    WHERE logs.temperature > 90 AND devices.location = 'SITE-1'  
    GROUP BY hour;  
  
 hour               | COUNT(logs.device_id)  
--------------------+------------------------  
 2017-01-04 12:00   | 994  
 2017-01-04 13:00   | 905  
 2017-01-04 14:00   | 875  
 2017-01-04 15:00   | 910  
 2017-01-04 16:00   | 905  
 2017-01-04 17:00   | 840  
 2017-01-04 18:00   | 801  
 2017-01-04 19:00   | 813  
 2017-01-04 20:00   | 798