RDS PostgreSQLHDB PG 毫秒級海量時空資料樞紐 典型案例分享

摘要： 標籤PostgreSQL , GIS , 時空資料, 資料樞紐, bitmapAnd , bitmapOr , multi-index , 分區 , brin , geohash cluster 幕後 隨著移動終端的普及，現在有越來越多的商務資料會包含空間資料，例如手機用戶的FEED資訊、物聯網、車連網、氣象感應器的資料、動物的溯源資料，一系列追蹤資料。

隨著移動終端的普及，現在有越來越多的商務資料會包含空間資料，例如手機用戶的FEED資訊、物聯網、車連網、氣象感應器的資料、動物的溯源資料，一系列追蹤資料。

這些資料具備這幾個維度屬性：

1、空間

2、時間

3、商務屬性，例如溫度、濕度、消費額、油耗、等。

資料樞紐是企業BI、剖析師、運營非常關心的，以往的樞紐可能是時間結合商務維度，現在會上線空間的樞紐（例如某個點附近，在某個時間段的樞紐；或者某個省的資料樞紐；或者北緯度附近的資料樞紐等）。

資料即時樞紐的一個關鍵點是預計算、即時計算、流式計算。下面有一個案例：

《PostgreSQLGPDB毫秒級海量多維資料樞紐 案例分享》

以上案例的資料中不包含空間維度，本文將介紹包含 "空間、時間、商務" 等多個維度資料樞紐的資料庫設計和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或者HDBPG），資料來源的詳細資料如果要留底，則可以將其寫入 HDB PG或OSS。

這種架構設計，對於可以建模的樞紐，可以做到毫秒級的回應。對於無法建模的樞紐需求（需要使用明細進行即時的計算），同樣可以使用HDB PG的平行計算能力，得到較快速度的回應。

2、T+n 調度

T+n 調度，實際上是一種常見的報表系統的做法，例如在淩晨將詳細資料匯入到資料庫或者OSS中，根據建模，生成報表。

案例如下：

《PostgreSQLGPDB多維資料樞紐典型案例分享》

3、lambda調度

T+N調度，只是將流計算節點更換成HDB PG或者RDSPG，通過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的滑動視窗也是這麼來實現的。

http://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、按GEOHASHPREFIX進行統計，例如去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即可。

用戶可以使用RDS PG或HDBPG，並行的存取OSS，從而達到即時樞紐，或者預計算的目的。

《流計算風雲再起 -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調度任務服務, 為毫秒級的時間、空間任意維度資料樞紐提供了一個完整的巨量資料即時計算的解決方案。

·RDS PostgreSQL

·HDBPostgreSQL

·OSS

http://docs.pipelinedb.com/sliding-windows.html

《PostgreSQLGPDB多維資料樞紐典型案例分享》

《(流式、lambda、觸發程序)即時處理大比拼 - 物聯網(IoT)金融,時序處理最佳實踐》

相關產品：

1. 剖析型資料庫

2. 巨量資料計算服務(MaxCompute)

3. 雲資料庫RDS

4. 雲端服務器ECS