339
RDS PostgreSQL\HDB PG 毫秒級海量時空數據透視 典型案例分享
標簽
PostgreSQL , GIS , 時空數據 , 數據透視 , bitmapAnd , bitmapOr , multi-index , 分區 , brin , geohash cluster
背景
隨著移動終端的普及，現在有越來越多的業務數據會包含空間數據，例如手機用戶的FEED信息、物聯網、車聯網、氣象傳感器的數據、動物的溯源數據，一係列跟蹤數據。
這些數據具備這幾個維度的屬性：
1、空間
2、時間
3、業務屬性，例如溫度、濕度、消費額、油耗、等。
數據透視是企業BI、分析師、運營非常關心的，以往的透視可能是時間結合業務維度的，現在會加入空間的透視（例如某個點附近，在某個時間段的透視；或者某個省的數據透視；或者北緯度附近的數據透視等）。
數據實時透視的一個關鍵點是預計算、實時計算、流式計算。下麵有一個案例：
《PostgreSQL\GPDB 毫秒級海量多維數據透視 案例分享》
以上案例的數據中不包含空間維度，本文將介紹包含 "空間、時間、業務" 等多個維度數據透視的數據庫設計和DEMO。
一、場景介紹
我們以一個場景為例，講解時空數據透視。
在滴滴、出租車、公交車、大巴、危化品車輛上安裝了傳感器，這些傳感器的位置是跟著汽車流動的，同時這些傳感器會不斷的生成數據並上報數據。
同時還有一些靜止的傳感器，也會上傳並上報數據。
數據結構有3種，根據不同的上報模式，對應不同的結構。
1、單條上報模式
table
(
sid int, -- 傳感器ID
pos geometry, -- 傳感器位置
ts timestamp, -- 時間
val1 int, -- 傳感器探測到的屬性1值
val2 float, -- 傳感器探測到的屬性2值
val3 text -- 傳感器探測到的屬性3值
......
)
2、批量上報模式，聚合後的明細(一條記錄包含多個VALUE)
table
(
sid int, -- 傳感器ID
pos geometry[], -- 傳感器位置數組
ts timestamp[], -- 時間數組
val1 int[], -- 傳感器探測到的屬性1值數組
val2 float[], -- 傳感器探測到的屬性2值數組
val3 text[] -- 傳感器探測到的屬性3值數組
......
)
3、批量上報模式，JSON打包的明細
table
(
sid int, -- 傳感器ID
info jsonb -- 批量打包數據 {k1: {pos:val, ts:val, val1:val, val2:val, ...}, k2: {}, k3: {}, ....}
)
二、架構設計
數據透視架構設計，分為兩種，一種比較暴力，實時裸算，需要更多的計算能力，但是比較適合無法建模的透視需求。
另一種，對於可以建模的透視，則可以通過預處理的方式，使用更低的成本，提高透視的響應速度。
如下
一、實時架構
實時模式，指數據寫入後，用戶根據需求查詢，統計結果。實時模式的查詢響應速度取決於集群的計算能力，通常為了達到高速響應，需要大量的投入。
實時計算除了提高計算能力，通常還可以通過任意列索引來提高透視的響應速度，例如：
《多字段，任意組合條件查詢(無需建模) - 毫秒級實時圈人 最佳實踐》
二、預處理架構
預處理的方法較多，例如流式計算、T+N調度，lambda調度。
1、流處理
流計算可以選擇PostgreSQL的pipelineDB插件（預計7月份插件化），支持TTL，滑動窗口，估值統計，以及PG內置的聚合統計函數等。性能也非常不錯，單機可以達到100萬行/s的處理速度。
《流計算風雲再起 - PostgreSQL攜PipelineDB力挺IoT》
數據來源實時寫入pipelinedb進行流式計算，流式計算的結果（例如統計維度為天，TTL設置為7天，每天將前天的統計結果寫入報表庫RDS PG或者HDB PG），數據來源的明細數據如果要留底，則可以將其寫入 HDB PG或OSS。
這種架構設計，對於可以建模的透視，可以做到毫秒級的響應。對於無法建模的透視需求（需要使用明細進行實時的計算），同樣可以使用HDB PG的並行計算能力，得到較快速度的響應。
2、T+n 調度
T+n 調度，實際上是一種常見的報表係統的做法，例如在淩晨將明細數據導入到數據庫或者OSS中，根據建模，生成報表。
案例如下：
《PostgreSQL\GPDB 多維數據透視典型案例分享》
3、lambda 調度
T+N調度，隻是將流計算節點更換成HDB PG或者RDS PG，通過FUNCIONT和任務調度的方式，增量的對建模數據進行統計和合並統計結果。
案例如下：
《(流式、lambda、觸發器)實時處理大比拚 - 物聯網(IoT)\金融,時序處理最佳實踐》
三、分區規則設計
分區規則指數據在某一個數據節點內的分區規則，分區規則應考慮到數據的查詢方式，例如經常按時間、空間範圍搜索或查詢，所以我們有兩個分區維度。
PostgreSQL, HDB都支持多級分區，因此可以在這兩個維度上進行多級分區。
[ PARTITION BY partition_type (column)
[ SUBPARTITION BY partition_type (column) ]
[ SUBPARTITION TEMPLATE ( template_spec ) ]
[...]
( partition_spec )
| [ SUBPARTITION BY partition_type (column) ]
[...]
( partition_spec
[ ( subpartition_spec
[(...)]
) ]
)
where partition_type is:
LIST
| RANGE
where partition_specification is:
partition_element [, ...]
and partition_element is:
DEFAULT PARTITION name
| [PARTITION name] VALUES (list_value [,...] )
| [PARTITION name]
START ([datatype] 'start_value') [INCLUSIVE | EXCLUSIVE]
[ END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE] ]
[ EVERY ([datatype] [number | INTERVAL] 'interval_value') ]
| [PARTITION name]
END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE]
[ EVERY ([datatype] [number | INTERVAL] 'interval_value') ]
[ WITH ( partition_storage_parameter=value [, ... ] ) ]
[column_reference_storage_directive [, ...] ]
where subpartition_spec or template_spec is:
subpartition_element [, ...]
and subpartition_element is:
DEFAULT SUBPARTITION name
| [SUBPARTITION name] VALUES (list_value [,...] )
| [SUBPARTITION name]
START ([datatype] 'start_value') [INCLUSIVE | EXCLUSIVE]
[ END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE] ]
[ EVERY ([datatype] [number | INTERVAL] 'interval_value') ]
| [SUBPARTITION name]
END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE]
[ EVERY ([datatype] [number | INTERVAL] 'interval_value') ]
[ WITH ( partition_storage_parameter=value [, ... ] ) ]
[column_reference_storage_directive [, ...] ]
[ TABLESPACE tablespace ]
1、時間範圍，例如每天一個分區。
2、GEOHASH範圍，geohash是經緯坐標的編碼值，代表一個BOX，編碼長度決定了它的精度(BOX的大小)，相鄰的BOX的編碼PREFIX相同。因此使用geohash進行範圍編碼是可行的。
例如用戶需要搜索某個時間段的數據，可以使用分區規則，挑選出對應的分區進行查詢，從而縮小搜索的範圍。
用戶需要搜索某個空間範圍的數據，通過GEOHASH範圍分區，同樣可以挑選出對應的分區進行查詢，從而縮小搜索的範圍。
HDB不僅僅支持geohash同時支持geometry，在geometry上可以建立GiST空間索引，使用空間索引可以支持KNN檢索（精準檢索，與BOX無關）。
四、分布規則設計
分布規則指數據在多個數據節點層麵的分布，不要與分區規則一致。我們可以選擇隨機或者業務相關字段作為分布規則，同時需要考慮數據的傾斜。
關於分區和分布列的選擇，可以參考這篇文檔：
《Greenplum 最佳實踐 - 數據分布黃金法則 - 分布列與分區的選擇》
1、隨機分布，數據將隨機寫入不同的數據節點，保證了數據的均勻性。但是查詢時，需要調用所有數據節點進行查詢。如果是JOIN，還會涉及數據的重分布。
2、業務ID，按業務ID來分布，當按業務ID進行查詢時，隻需要調用對應業務ID所在數據節點，但是請務必考慮到數據傾斜，例如某個業務ID的數據特別多，那麼可能導致分布不均勻。
PS：（一致性HASH解決了分布不均勻的問題。）
五、預計算設計
對於可以建模的透視需求，預計算是可以大幅度提升透視響應時間的手段。
除了業務指標維度，常見的還有時間、空間維度的預計算。
統計有一個相通性，都是基於分組進行，比如時間分組（小時，天，月，年等），空間分組（行政區，box，GEOHASH，GRID等）。
預計算 - 固定時間
例如業務要查詢天、月、年的報表。那麼我們可以按天進行預計算。
使用GROUPING SETS語法進行多維透視。
insert into result
select date(crt_time),col1,col2,col3,...,agg_func1(),agg_func2(),...
from table
where crt_time between ? and ?
group by date(crt_time),GROUPING SETS(col1,col2,col3,...);
預計算 - 滑動時間窗口
在《星際穿越》這部電影中，未來的人類構造了一個五維空間，讓主人公可以在過去的時間中穿越，在過去的時間內任意的滑動。
實際上數據透視，也有類似的滑動需求，可能需要查詢過去任意時間窗口的統計。
例如，查詢 2017-06-27 13:50:00 前後30分鍾的統計。
有兩種方式實現滑動窗口：
1、非預計算，即根據明細直接進行統計。
2、若預計算需要支持滑動窗口，我們需要進行更小粒度的統計，例如窗口大小為1小時，那麼細粒度至少要小於一小時，例如使用半小時的粒度，在查詢滑動窗口時，將細粒度的統計結果進行二次統計得到滑動窗口的統計結果。
pipelineDB的滑動窗口也是這麼來實現的。
https://docs.pipelinedb.com/sliding-windows.html
預計算 - 固定行政區
為了實現空間維度的預計算，我們需要將空間數據進行分組，這個分組也是根據業務對空間數據透視的需求來的。
例子
1、業務需要根據區級行政區進行統計，那麼我們可以根據區級行政區進行預計算，（圖片取自互聯網）。
首先需要有翻譯行政區ID的函數支持，根據經緯度，返回該經緯度屬於哪個行政區的ID。
一個比較土的方法，用一張表來表示行政區的多邊形geometry，通過gist索引，可以快速的返回某個經緯度屬於哪個多邊形。
table (
ID int, -- 行政區ID
bb geometry -- 行政區的多邊形
)
create or replace function get_id(pos geometry) returns int as $$
select id from table where bb && pos limit 1;
$$ language sql strict immutable;
統計
insert into result
select get_id(pos),col1,col2,col3,...,agg_func1(),agg_func2(),...
from table
where crt_time between ? and ?
group by get_id(pos),GROUPING SETS(col1,col2,col3,...);
2、按GEOHASH PREFIX進行統計，例如去5位長度的hash，聚合。（通過PostGIS的ST_GeoHash()函數計算經緯度geohash value。）
insert into result
select ST_GeoHash(pos,5),col1,col2,col3,...,agg_func1(),agg_func2(),...
from table
where crt_time between ? and ?
group by ST_GeoHash(pos,5),GROUPING SETS(col1,col2,col3,...);
GEOHASH精度如下
so one symbol (letters or digits) is base 32 (8 bits) Each first bit is used for high or low window,
then subsequent bits divide the precision by 2.
(so divide by 8 in the best case) but there is an alternance between lat and long precision,
so it ends up dividing by 4 and 8 alternatively.
# km
1 ±2500
2 ±630
3 ±78
4 ±20
5 ±2.4
6 ±0.61
7 ±0.076
8 ±0.019
9 ±0.0024
10 ±0.00060
11 ±0.000074
Note that, as mentioned on the Wiki page, those values come from a location near the equator,
where a degree has nearly the same lenght in X and Y.
For a more accurate information, you should start from the lat and long errors,
and compute the km precision along X-axis based on the latitude of your position.
預計算 - 滑動空間窗口(點輻射)
空間維度的滑動窗口，實現方法和時間維度的滑動窗口類似，例如我們可以對8位編碼的geohash進行統計，然後我們可以按5位geohash編碼進行二次聚合透視。
空間滑動和時間滑動窗口一樣，使用預計算都會有一定的失真。（因為邊界無法做到完全清晰。）
如果用戶在意這樣的失真，可以考慮實時計算。
業務維度的預計算與時間、空間維度類似，就不贅述了。
六、透視設計
透視需求 - 固定時間
select sum,avg,count,min,max,hll,....
from result
where
ts =
and val1 =
and ...;
透視需求 - 滑動窗口
通過預計算實現的滑動，需要進行二次聚合。方法如下：
sum = sum(sum)
count = sum(count)
min = min(min)
max = max(max)
avg = sum(sum)/sum(count)
hll = hll_union_agg(hll)
select sum(sum),sum(count),hll_union_agg(hll),...
from result
where
ts between ? and ?
and val1 =
and ...;
透視需求 - 固定行政區
select sum,avg,count,min,max,hll,....
from result
where
loc_id = ?
and val1 =
and ...;
透視需求 - 滑動空間窗口(點輻射)
sum = sum(sum)
count = sum(count)
min = min(min)
max = max(max)
avg = sum(sum)/sum(count)
hll = hll_union_agg(hll)
select sum(sum), sum(count), hll_union_agg(hll), ...
from result
where
substring(geohash_val, 1, 5) = ?
and val1 =
and ...;
七、結合OSS
實際上，如果用戶不需要查詢明細，或者用戶不需要頻繁的要求低延遲的查詢少量明細的話，明細數據是不需要進入數據庫的，導入OSS即可。
用戶可以使用RDS PG或HDB PG，並發的訪問OSS，從而達到實時透視，或者預計算的目的。
八、流計算，lambda
《流計算風雲再起 - PostgreSQL攜PipelineDB力挺IoT》
流計算的模式，用戶根據數據結構創建流，並對流創建統計視圖，然後激活這個統計視圖，將數據實時寫入數據庫，數據庫對流過的數據進行實時的統計，生成統計結果。
如果統計結果本身也非常龐大，那麼我們可以使用TTL統計視圖，定期將統計結果轉移到HDB即可。
例子
定義流
create stream s1 (
sid int, -- 傳感器ID
pos geometry, -- 傳感器位置
ts timestamp, -- 時間
val1 int, -- 傳感器探測到的屬性1值
val2 float, -- 傳感器探測到的屬性2值
val3 text -- 傳感器探測到的屬性3值
......
);
定義TTL統計視圖，保留1周
CREATE CONTINUOUS VIEW v1 WITH (ttl = '7 day', ttl_column = 'crt_time') AS
select date(crt_time) crt_time,col1,col2,col3,...,agg_func1(),agg_func2(),...
from table
group by date(crt_time),GROUPING SETS(col1,col2,col3,...);
激活統計視圖
ACTIVATE v1;
定期數據轉移
insert into result
select * from v1 where crt_time = '昨天';
數據透視
select * from result ....;
九、小結
1、為什麼不使用時間、空間複合索引？
當用戶需要查詢某個時間區間，某個點附近N公裏的數據時，有什麼快速定位到目標明細數據的方法？
在實際維度建索引、在空間維度建索引，隻能快速的收斂到一個維度，另一維度需要通過HEAP得到TUPLE後進行RECHECK和過濾。
為什麼不建立時間、空間兩個字段的複合索引呢？因為都是連續查詢，所以這類複合索引都需要掃描驅動列的所有索引ENTRY。
複合索引對於驅動列為等值的查詢效率是很高的。
對於時間、空間雙維度數據查詢，建議使用分區的方法，分區是最有效的可以縮小數據範圍的方法。
空間維度的分區，建議使用GEOHASH的range分區。
2、毫秒級任意維度透視的核心，預計算。
3、數據估算類型：HLL。有助於用戶快速的查詢唯一值數量，新增值數量。
4、如果用戶需要對明細VALUE進行聚合，可以使用數組、JSONB等字段存儲聚合明細。
5、阿裏雲 RDS PG、HDB for PG、OSS、流計算插件、雲端ETL調度任務服務 , 為毫秒級的時間、空間任意維度數據透視提供了一個完整的大數據實時計算的解決方案。
參考
https://docs.pipelinedb.com/sliding-windows.html
《PostgreSQL\GPDB 多維數據透視典型案例分享》
《(流式、lambda、觸發器)實時處理大比拚 - 物聯網(IoT)\金融,時序處理最佳實踐》
最後更新：2017-06-29 17:33:04