時序資料庫分析-TimescaleDB時序資料庫介紹

德哥發表於2017-04-10

資料庫

背景

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

在網際網路行業中，也有著非常多的時序資料，例如使用者訪問網站的行為軌跡，應用程式產生的日誌資料等等。

時序資料有幾個特點

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