阿里云PostgreSQL_案例介绍2 | 学习笔记

开发者学堂课程【PostgreSQL云上开发实践:阿里云PostgreSQL_案例介绍2】学习笔记，与课程紧密联系，让用户快速学习知识。

课程地址：https://developer.aliyun.com/learning/course/528/detail/7101

阿里云PostgreSQL_案例介绍2

内容介绍：

一、Case3（特征、相似）

二、Case4（图像、特征、透视）

三、Case5（模拟股票交易系统）

四、Case6（空间应用）

五、Case7（文本情感）

六、Case8（树、多表关联、多值、图搜）

一、Case3（特征、相似）

· 相似

——数组相似

——文本特征值相似

——图片相似

案例：

· 导购系统

- 1亿历史导购文章:数组(商品ID)相似判断

- 实时判定盗文

- 毫秒级

· 新零售-商品相关短文相似查询

- 10亿级短文

- 短文特征值海明码相似识别

- 切分，通过 smlar 插件 overlap 求相似

- 毫秒级

· 图像搜索系统

- 10亿级图片

- 相似图片识别

- 对象识别(doing)

- 毫秒级

云产品方案、效果

· RDS PG

- smlar 插件

· 相似文本、数组

· 海明码切片(转码)相似

- imgsmlr 插件

· 相似图片

图像特征值提取与存储

图像特征值比对

RDS PostgreSQL:

· smlar 插件

- cosine , overlap , tdidf 相似

· imgsmlr 插件

- 图像特征值，图像相似搜

- 图像识别(doing)

· smlar 插件

- 短文海明距离<N，相似性

- 海明码切片+smlar overlap 高速检索

案例-架构设计、代码、实操手册:

· 数组相似

https://github.com/digoal/blog/blob/master/201701/20170116_02.md https://github.com/digoal/blog/blob/master/201701/20170116_03.md

https://github.com/digoal/blog/blob/master/201701/20170116_04.md https://github.com/digoal/blog/blob/master/201701/20170112_02.md

· 海明码相似

https://github.com/digoal/blog/blob/master/201708/20170804_01.md

· 图片相似

https://github.com/digoal/blog/blob/master/201607/20160726_01.md

二、Case4（图像、特征、透视）

· 画像系统

案例：

· 心选

· 生意参谋

· 优酷

· B2B 卖家智能运营

· 菜鸟

· 友盟

- 多值列标签+任意字段组合 圈选

（1、多值列:标签2、索 Adhoc SQL 多列任意组合搜）

· XXXpush

- 业务背景: ToB 实时圈人系统

- 数据来源:实时标签数据

- 数据规模:单表10亿条记录，单个 B-1亿用户，1万个标签字段。

- 数据描述:每个用户的标签数据

- 查询需求:任意标签组合圈人

· 100毫秒级响应

- 并发需求:200+

- DML需求:实时标签分钟级体现到查询中

痛点：（效率低）

· 1万个 TAG ，大宽表。

- 目前没有数据库支持。需要拆分成多表。

· 原方案成本高，收益低。

- 8台，数据延迟天级别，响应时间接近分钟级，并发不到100。

云产品方案、效果：

· RDS PG  

- 阿里云 varbitx 插件  

- 翻转存储 tag,userid_varbit

- 用户 ID 字典化  

· 单台 RDSPG

- 标签数据合并延迟10分钟级

- 查询响应毫秒级

- 支持并发500+

- 裸空间节省80倍算上索引至少240倍节省

案例：

· 数据银行项目

· 20亿+用户，万级标签，大屏展示(100+标签组合卷选透视)

-1、求 COUNT，2000亿(20亿用户，100个标签组合)

USER_IDS ，响应速度2.6秒。

-2、求 USERID 明细，返回500万用户ID位置，692毫秒

-3、求 USERID 明细，返回 BITMAP，500万个 BIT，224毫秒。

案例:

· 超大BIT分段，加速

· bitand，同时包含

· bitor，包含任意

· bitandbitxor，包含1但是不包含2

案例-架构设计、代码、实操手册：

· 阿里云varbitx插件

https://github.com/digoal,blog/blob/master/201712/20171212_01.md

https://github.com/digoal/blog/blob/master/201610/20161021_01.md

https://github.com/digoal/blog/blob/master/201705/20170502_01.md

https://github.com/digoal/blog/blob/master/201706/20170612_01.md

https://github.com/digoal/blog/blob/master/201712/20171223_01.md

三、Case5（模拟股票交易系统）

· SchemaLess

· 同类业务

——轨迹

——时序特征

案例：

· 证券模拟交易系统

- 业务背景:模拟股票交易

- 数据来源: 实时股票数据

- 数据规模: 300亿

- 数据描述:股票交易数据，大宽表。

- 查询需求：查询任意股票任意时间区间的数据，返回60条数据10毫秒以内

- 并发需求:1000+

- DML 需求:准实时写入

痛点：（ IO 放大问题，索引庞大问题）

· 按任意时间滑动查询。

· 写入、查询延迟要求低。

云产品方案、效果：

· RDS PG

- Schemaless 方案( UDF )

- 任意股票任意时间段查询响应时间0.04毫秒

· 同行竞品为10毫秒

- 股票数据写入速度约22万行/s，远超业务需求。

- 以十年的股票数据来计算，约300亿数据。单机可以搞定。

- BRIN时序索引，10亿~1MB。

timescaleDB 插件：

· 自动切片，写入性能无损耗

· merge append (时序窗口查询性能优化)

· 数据维护 API 

菜鸟-实时 FEEDLOG 监测：

· 海量 FEED LOG 实时质量统计(若干台RDS PG)

- avg,min,max, sum,percentile_disc  

- 单 rds pg 指标:15亿 point/分钟

- intarray,aggs_for_arrays, list partition 

案例-架构设计、代码、实操手册:

· 证券案例

https://github.com/digoal/blog/blob/master/201704/20170417_01.md

· 自动切片

https://github.com/digoal/blog/blob/master/201711/20171102_02.md https://github.com/digoal/blog/blob/master/201705/20170511_01.md

https://github.com/digoal/blog/blob/master/201709/20170927_03.md

· 菜鸟-实时 FEEDLOG 检测

https://github.com/digoal/blog/blob/master/201802/20180205_04.md

· timescaleDB 插件

https://github.com/digoal/blog/blob/master/201801/20180129_01.md https://github.com/digoal/blog/blob/master/201704/20170409_05.md

四、Case6（空间应用）

· 电子围栏、LBS、AOI、POI、路网、导航、自动驾驶、路径规划

· GIS 空间数据管理

· 电子围栏(不规则多边形)

- 共享自行车还车点管理

- 公务用车限行管理

- 车辆限行区域管理

- 放牧区域管理

- 菜鸟-包裹快递员分配管理

- 基于实时位置的广告营销

- 智能家居( loT )

- 封印

案例-不规则多边形：

· 不具备空间索引的数据库，编码索引。存在弊端 

· 菜鸟 aoi

-  AOI 库的构建，

- 精准分单

· 共享单车

- 限制还车地点

场景-LBS：

· 探探、微信、陌陌、订餐、POI (附近对象"如加油站"搜索) 

案例-点云、路径规划：

· 菜鸟-自动配送机器人

- pointcloud

· 高德

- 地图、导航

· 淘点点  

- 路径规划

案例-路径拟合：

· Gxxx，路径补全

- 监控盲点路径补全

- pgrouting 路径插件

· Gxxx，人车拟合

- 拟合司机、乘客

- 时间、空间圈选计算

· 兴趣点相关业务(导航、...)

· 导出 POI，导入 PostgreSQL (PostGIS)  

· https:/wiki.openstreetmap.org/wiki/Osmosis/Installation

· http:/wiki.openstreetmap.org/wiki/Osmosis/Detailed_Usages

·https://help.openstreetmap.org/questions/4065/getting-specific-poi-data-and-keeping-them-up-to-date

案例-架构设计、代码、实操手册:

· 电子围栏

https://lgithub.com/digoal/blog/blob/master/201710/20171031_01.md https://aithub.com/disoal/blog/blob/master/201708/20170803_01.md

· 多边形搜索

https://github.com/digoal/blog/blob/master/201710/20171004_01.md https://github.com/digoal/blog/blob/master/201710/20171005_01.md https://github.com/digoal/blog/blob/master/201711/20171107_06.md

· 点云

https://github.com/digoal/blog/blob/master/201705/20170519_02.md https://github.com/digoal/blog/blob/master/201705/20170523_01.md

· 路径规划

https://github.com/digoal/blog/blob/master/201508/20150813_03.md

http;//pgrouting.org/

· KNN搜索

https://github.com/digoal/blog/blob/master/201711/20171107_07.md https://github.com/digoal/blog/blob/master/201308/20130806_01.md

· 商旅问题

https://github.com/digoal/blog/blob/master/201704/20170409_01.md

五、Case7（文本情感）

· 实时文本情感分析

——聊天记录

——评论

——XF/FK

——...

案例-实时舆情分析：

· 实时文本情感分析、舆情系统

详细链接：

· 实时文本情感分析、舆情系统

· 导入 javalpython lib

https://help.aliyun.com/document_detail/50594.html

· plpython 开发手册

https://www.postgresql.org/docs/10/static/plpython.html

https://gpdb.docs.pivotal.io/43100/ref_ guide/extensions/pl_ python.html  

· pljava 开发手册

https://gpdb.docs.pivotalio/43100/ref_guide/extensions/pl_ iava.html

· plR 开发手册

https://apdb.docs.pivotal.io/43100/ref_guide/extensions/pl_r.html

· MADlib SQL 机器学习库手册

http://madlib.apache.org/docs/latest/index.html

· R MADlib 对接手册

https://cran.r-proiect.org/web/packages/PivotalR/

· python MADlib 库对接手册

https://pypi.python.org/pypi/pymadlib/0.1.7

六、Case8（树、多表关联、多值、图搜）

· 树形结构

——复杂 JOIN

——递归查询

——图式搜索（graph search）

金融风控、QA系统、好友关系、舆情系统、药品监管（溯源）、串货监管（溯源）

案例-图式搜索、伴随分析:

· xxx小微金融项目

- 业务背景:中xx小微金融项目

- 数据来源:

·爬虫、合作平台(税务、蚂蚁、银行...)

- 数据规模:

· 全网100亿，中xx10亿级

· 大平台、线上线下多份存储

- 数据描述:

· 企业信息、法人信息、爬虫爬到的相关信息、纳税信息、。。。展示图谱，辅助评估贷款风险

- 查询需求:

· 企业多级关系查询、图谱展示

- 并发需求:100+

- DML需求:实时写入 

痛点：

· 树形结构数据，递归查询

· 众多关联企业信息

- 多表JOIN，关联关系复杂

· 输出多级关联企业(类似人脉关系)

· 要求高速响应图式搜索需求

RDS PG：

· ltree 数据类型

· 多表 JOIN  

· 递归查询

· -CTE

· 数组

- key1:{val1,val2,val3..}

- GIN 索引

- 包含、相交

- =any(array)

适用场景：

· 好友关系系统

· 好友推荐系统

· 陌生人交

· 知识图谱

· 风控

案例-架构设计、代码、实操手册：

· 图式搜索案例链接

https://github.com/digoal/blog/blob/master/201708/20170801_01.md  

httns://github.com/digoal/blog/blob/master/201612/20161213_01.md

https://github.com/digoal/blog/blob/master/201801/20180102_04.md

· ltree数类型：

https://github.com/digoal/blog/blob/master/201105/20110527_01.md https://github.com/digoal/blog/blob/master/201709/20170923_01.md

· 递归查询:

https://github.com/digoal/blog/blob/master/201705/20170519_01.md https://github.com/digoal/blog/blob/master/201703/20170324_01.md https://github.com/digoal/blog/blob/master/201612/20161201_01.md https://github.com/digoal/blog/blob/master/201611/20161128_02.md https://github.com/digoal/blog/blob/master/201611/20161128_01.md httos://github.com/digoal/blog/blob/master/201607/20160725_01.md https://github.com/digoal/blog/blob/master/201607/20160723_01.md https://github.com/digoal/blog/blob/master/201604/20160405_01.md httos://github.com/digoal/blog/blob/master/201512/20151221_02.md https://github.com/digoal/blog/blob/master/201210/20121009_01.md https://github.com/digoal/blog/blob/master/201209/20120914_01.md

· JSON.plv8:

https://github.com/ply8/pv8/blob/master/doc/ply8.md#scalar-function-calls

(阿里云 RDS PG 内置 plv8 语言)

· 数组

https://github.com/digoal/blog/hlob/master/201711/20171107_18.md

https://github.com/digoal/blog/blob/master/201711/20171107_19.md

httos://github.com/digoal/blog/blob/master/201711/20171107_20.md