关于Lindorm Ganos
什么是Lindorm Ganos
Lindorm是阿里云推出的云原生超融合多模数据库，提供了宽表、时序、搜索、文件等多种引擎。目前，Lindorm已经深度融合了由阿里云联合达摩院数据库与存储实验室联合研发的时空数据库引擎Ganos（下文统称为Lindorm Ganos），可以解决海量移动对象存储、管理和查询需求，弥补了各类NoSQL在时空数据处理方面的不足。
Lindorm Ganos的特点
相较于传统在业务侧自主解决时空需求的定制化方案，Lindorm Ganos具有如下特点：
- 一站式原生能力助力开发便捷。Lindorm Ganos拥有原生的空间数据类型，可将车辆轨迹、电子围栏等带空间位置信息的数据直接存储为宽表内的时空对象（点、线、面等），同时Lindorm的流引擎、计算引擎可以无缝与宽表中的时空对象进行空间关联查询，并将复杂计算下推至离数据更近的宽表中，提升计算效率；
- 多层级索引能力助力提速增效。传统的NoSQL数据库往往采用空间填充曲线来进行索引，这种索引方式对于面过滤点有着比较好性能体验，但难以适应类似于点查面或实时性很高的场景。Lindorm Ganos在全面支持空间填充曲线索引外，还实现了内存R树、地理网格等多种索引，用来帮助客户解决更为复杂的业务场景需求。同时传统的NoSQL时空方案针对不同查询需要将数据冗余多份，为每一份数据单独构建符合查询需求的索引，造成了数存储空间的膨胀，Lindorm Ganos在主键索引的基础上支持二级索引，可以为多个时空列建立不同的索引，有效解决了数据冗余问题；
- 广适应兼容能力助力业务迁移。Lindorm Ganos除了一站式解决存、流、算等多模态场景下时空需求外，还兼容传统NoSQL方案Geomesa。对于不想进行业务更改的成熟应用系统，客户可以使用Lindorm Ganos兼容Geomesa版本，实现自建Geomesa至Lindorm Ganos的迁移转换，从而享受云上便捷的管控与弹性能力；对于全新构建的业务系统，可以基于标准SQL语法直接体验Lindorm Ganos多模态时空计算能力，降低存储成本与开发难度；
产品 |
特点 |
Lindorm Ganos |
|
Geomesa |
|
Lindorm Ganos的核心能力
如上图所示，Ganos引擎目前已集成到Lindorm流引擎与Lindorm宽表引擎，并即将集成到Lindorm Spark计算引擎中，可分别应对实时时空计算、海量历史数据查询和复杂时空分析场景。
宽表
宽表引擎原生支持标准几何数据类型（点、线、面、多点、多线、多面、对象集合等）；支持时空主键索引与时空二级索引实现多维度时空查询；支持时空谓词下推提升检索效率；支持构造函数、输出函数、访问函数、空间关系函数、处理函数、量测函数、聚合函数七大类数十个时空算子，并通过各类索引与并行化能力的构建加速时空查询性能。
流
流引擎原生支持标准几何数据类型（点、线、面、多点、多线、多面、对象集合等）；支持内存R树索引；支持时空窗口函数；支持使用宽表中的时空算子。
应用场景
场景描述
在交通物流、网约车等场景中，业务一般会要求车辆按照预先规划路径行驶，确保该路线可以通过固定区域的相关站点，当车辆无故偏离既定区域或路线时，需要产生告警信息。同时，指挥中心需要实时掌握相关时间区间内各区域的车辆的分布情况，以应对可能产生的临时运力配置。
需求分析
在这类场景中，我们将预先设定好的区域或线路称之为地理围栏，由于地理围栏并不是实时变化的，可以使用外表的形式存储到Lindorm宽表中。而车辆的位置信息是实时上传的，一般的策略是先存储到Kafka中，Lindorm流引擎通过订阅Kafka中的位置数据，实时计算车辆位置和地理围栏之间的关系。
因此针对上述车辆行为实时性分析的需求，我们统一的技术路线为：
技术实现
开通Lindorm Ganos服务
Lindorm Ganos时空服务是Lindorm宽表引擎和流引擎的一个组件，服务开通后仅收取Lindorm宽表引擎和流引擎费用。本文介绍通过云原生多模数据库Lindorm控制台开通Ganos时空服务的方法。
前提条件
已创建Lindorm实例并开通宽表引擎，具体操作请参见创建实例。
操作步骤
- 登录Lindorm管理控制台。
- 在页面左上角，选择实例所属的地域。
- 在实例列表页，单击目标实例ID或者目标实例所在行操作列的管理。
- 在左侧导航栏，选择宽表引擎。
- 在Ganos时空服务页签中，单击立即开通。
创建地理围栏表和结果表
首先，通过 lindorm-cli 工具连接到宽表引擎，并创建一张围栏表和两张结果保存表，详细用法见：如何通过Lindorm-cli连接并使用宽表引擎。
地理围栏表
地理围栏表结构如下：
字段名 |
字段类型 |
含义 |
rID |
INT |
区域ID |
rName |
VARCHAR |
区域名称 |
fence |
GEOMETRY |
区域地理围栏 |
创建语句：
CREATE TABLE IF NOT EXISTS regions( rID INT, rName VARCHAR, fence GEOMETRY, PRIMARY KEY (rID));
写入数据：
UPSERT INTO regions(rID,rName,fence) VALUES(1,'Ozone Park',ST_GeomFromText('POLYGON ((-73.8401285612718 40.6811466505837,-73.8395206532515 40.6799618692111,-73.8379425103529 40.6767942092538,-73.8381194669117 40.6766298890589,-73.8378437225533 40.6760781241611,-73.8374957178211 40.6754068237692,-73.8371376045733 40.6746651342727,-73.8368089476441 40.6739611172004,-73.8364142389397 40.6731155892318,-73.8360075280982 40.6722695592153,-73.8354748573921 40.6712018700466,-73.835281176604 40.6708136428008,-73.8333481926936 40.6667731870154,-73.8346496563211 40.6671153078034,-73.834876543007 40.6672116647181,-73.8352894154928 40.6672359792612,-73.8357010882837 40.6672602219222,-73.8366782953458 40.6675864924794,-73.8373007213445 40.6677073598162,-73.8374490872674 40.6677983503997,-73.8383435819539 40.667989026072,-73.8392785101637 40.6682018489106,-73.8400855367502 40.6684674816112,-73.8401362375677 40.6682680250151,-73.8411739918562 40.6685074427113,-73.8421580216392 40.668701447728,-73.8422723788069 40.6687360461845,-73.8422954223773 40.6688139180597,-73.8424358542023 40.6692885569141,-73.8425804896669 40.6692805331373,-73.8427136770531 40.6692782933521,-73.8428858937471 40.6692850755081,-73.8430561383127 40.6693059383046,-73.8432215015442 40.6693404108845,-73.8448103320548 40.6698446229891,-73.8455683986551 40.6700833762166,-73.8459383911981 40.6701789778298,-73.8468689311023 40.6704699773152,-73.8474137815111 40.6706299951777,-73.8478700087405 40.6707794998545,-73.8484776530248 40.6709167386527,-73.8509645368408 40.6717195113711,-73.8516346095603 40.6719303328333,-73.8528380389565 40.6722791897672,-73.8564873929823 40.6733767741885,-73.8571134913068 40.6735750545941,-73.8582515369217 40.6741356106876,-73.8592831792372 40.6744527164362,-73.859592621798 40.674556410171,-73.8602956564666 40.6747718944622,-73.8613186364857 40.6750212688162,-73.861397759328 40.6753365899807,-73.8617123871756 40.6766105959751,-73.8620284308983 40.6778861140327,-73.8622594308922 40.6788167861563,-73.8623458053502 40.6791647861468,-73.8629647069958 40.679112816953,-73.8632864084451 40.6790771970637,-73.8633096424793 40.6791679696637,-73.8633494359304 40.6793516408897,-73.862296261729 40.6796315804947,-73.8615604707299 40.6798399675205,-73.8614232954279 40.6798788134365,-73.8606619873011 40.6800975381459,-73.8605330012606 40.6801344484212,-73.8597746258015 40.6803507121752,-73.8596627184619 40.6803823728658,-73.8592951236568 40.6804863655036,-73.8587857690299 40.6806304636443,-73.8581983610058 40.6794308490694,-73.8577926842888 40.6794557166447,-73.8572107855002 40.6794858643449,-73.8568313704324 40.6795093383044,-73.8565341030587 40.6795277252978,-73.8543933076625 40.6796601136851,-73.8539238916211 40.6796861563043,-73.8526772610296 40.6800794150825,-73.8522341708392 40.6802078909679,-73.8516820550599 40.6803679773976,-73.8509908934889 40.6805683676613,-73.8513177382014 40.6812400157284,-73.851639287721 40.6818862723219,-73.8519786460037 40.6825642112858,-73.8508716277106 40.682259542912,-73.8506047820218 40.6821937826643,-73.8497640746518 40.6819866140518,-73.849216206079 40.6818442105581,-73.8486338397522 40.6816840280915,-73.8478681763194 40.6814868321228,-73.8475784512998 40.6815798682803,-73.8467146430761 40.6818584931919,-73.8458323743807 40.6821064507287,-73.8452904239151 40.6822304448268,-73.8452020026917 40.6822506681482,-73.8451334994619 40.6822665330011,-73.8450583785584 40.6822844513381,-73.8449619067907 40.6823074553189,-73.8445106372463 40.6824150883417,-73.8440891495958 40.6825292990679,-73.8432309037535 40.6827751970579,-73.8423702910891 40.6830184222148,-73.8415139199754 40.6832616809542,-73.8413409197213 40.6833105030829,-73.8411960566322 40.6833513668439,-73.8401285612718 40.6811466505837))')), (2,'South Jamaica',ST_GeomFromText('POLYGON ((-73.7739565946594 40.6998057815896,-73.7744618616977 40.6989951464606,-73.7745937747897 40.6987994981814,-73.7748922447388 40.6983874480614,-73.7751363378374 40.6980504660281,-73.7753284213353 40.6977856671998,-73.7756762612124 40.6973050299834,-73.7757628775039 40.6971828625656,-73.7760874916699 40.6967252309963,-73.7761893233184 40.6965743417206,-73.7765977551772 40.695969114417,-73.7770388206771 40.6953643370194,-73.7772573434463 40.695064042407,-73.7778359341564 40.6943213354032,-73.7786339981454 40.6940137882607,-73.7794408133765 40.6936451096976,-73.7801864611243 40.6930152298108,-73.7802372384712 40.6929723354355,-73.7791983850612 40.6916505689252,-73.7778933737174 40.6899176900325,-73.7779605515926 40.6898948102007,-73.7789124960071 40.6895706472499,-73.7797729507005 40.6892811699879,-73.780781554983 40.6910299429487,-73.7816029657583 40.6906869044051,-73.7824271228163 40.6903443692837,-73.7832662504421 40.6899942927367,-73.7840990401853 40.6896477549682,-73.7847924067515 40.6893615991688,-73.7858038080206 40.6889962194239,-73.7864395063024 40.6887817096396,-73.7864395071795 40.6887817106464,-73.78698344487 40.6886084769705,-73.7876420987132 40.6884348503789,-73.7886791202396 40.6881801840847,-73.7892052990308 40.6880532127044,-73.7896263129537 40.6879390592815,-73.7903846829611 40.6877536137994,-73.7921069937337 40.6870475362773,-73.7924804347787 40.6868896301776,-73.793172207811 40.6865966864091,-73.7939084167813 40.6863046908945,-73.7943708725588 40.6869125819983,-73.7947783741015 40.6874706838947,-73.7952109489994 40.6880946657303,-73.7956148678686 40.688656891263,-73.7966293325787 40.6882214520225,-73.7974605715221 40.6878723023254,-73.7982858827173 40.6875240189383,-73.7991353122315 40.6871819585409,-73.7999504284372 40.6868277194537,-73.7989820914056 40.6848587378247,-73.7994303144039 40.6847319887619,-73.7998611915557 40.6846101428261,-73.8007739486379 40.684349928051,-73.8016857242636 40.6840919562596,-73.8025643093224 40.6838424389356,-73.8034452806282 40.683591857358,-73.8043235107706 40.6833417951066,-73.8051920927228 40.683096058179,-73.8056876957102 40.6829556333581,-73.8057684019137 40.6829323912503,-73.8060620273092 40.6835543609624,-73.8066060873639 40.6846811457838,-73.8070906333246 40.6856303107838,-73.8073362180269 40.68612544699,-73.8078746961921 40.6870983377937,-73.8080734242231 40.6874528242586,-73.8085765517067 40.6883308903005,-73.8087435857251 40.6886258840912,-73.8089241714599 40.688944809204,-73.8091446130455 40.6893310302304,-73.8091842520077 40.6894004789694,-73.8095478753031 40.6900375461245,-73.8097232674079 40.6903463441451,-73.8099560125067 40.6907561126578,-73.8102442624469 40.6912635868165,-73.8106329831089 40.6919473825767,-73.8105421669787 40.6919724587714,-73.8102695800732 40.6920452490003,-73.8097101715963 40.6921993844249,-73.8089021690074 40.6925643489552,-73.8082813237515 40.6921473856993,-73.808036403551 40.6919828948203,-73.8072395722275 40.6914319883901,-73.8064247940488 40.6908738584137,-73.8055584983553 40.6916087049525,-73.8046879304442 40.6923455716394,-73.8038177056676 40.69307963156,-73.8029594004559 40.6938174711306,-73.8021125254978 40.6945444989427,-73.8011961782468 40.6953364890635,-73.8005320421923 40.6958566923754,-73.7998592755535 40.696347080176,-73.7991822688379 40.6968714763248,-73.7987681488739 40.6972595513965,-73.7985464016822 40.6974665328276,-73.7978619344646 40.6979934042774,-73.7975367277742 40.6981989246031,-73.7972237473001 40.6983594578855,-73.796476064564 40.6986930845588,-73.7957217379361 40.6990147351136,-73.7944992088149 40.6993857767211,-73.7935770873488 40.6997564716958,-73.7928435646897 40.7002436835376,-73.7926050671798 40.7004321004764,-73.7924611163779 40.7005537891058,-73.7924610687205 40.7005538247033,-73.7923291703971 40.7006842248951,-73.7923290518274 40.7006843416155,-73.7923289446476 40.7006844764495,-73.7922104398761 40.7008225091069,-73.7922102971705 40.7008226713511,-73.792210166737 40.7008228512079,-73.7921061120282 40.7009673573132,-73.7921059577079 40.7009675733127,-73.7921058265006 40.700967789354,-73.7920169119603 40.7011177261603,-73.7919437208677 40.7012717260558,-73.7918691655687 40.701366136686,-73.7923528213712 40.7020979395267,-73.792391703146 40.7021665296529,-73.7921373244354 40.7022033692417,-73.7918789353982 40.7022217940825,-73.7916199885831 40.7022215720069,-73.791500836588 40.7022231387255,-73.7913819459409 40.7022359407026,-73.7912660328482 40.7022598049504,-73.7911556966063 40.7022940758,-73.7905556693738 40.7025677955624,-73.7899940581221 40.7028714162894,-73.7897759726084 40.7029788636108,-73.7896639148017 40.7030394885209,-73.7891930891331 40.703204385244,-73.7883541395225 40.7034602306484,-73.7880416149939 40.703555596983,-73.7875168996975 40.703715082339,-73.7859797642716 40.7041866163631,-73.7848487620353 40.7045855697378,-73.7844202732172 40.7047624667747,-73.7825658258396 40.7054248638677,-73.7814788740036 40.7054552509169,-73.7803449110796 40.7054124167072,-73.7798182796988 40.7049278979101,-73.7791600984135 40.7043889666645,-73.7784913099873 40.7038332252451,-73.7779197995922 40.7032915358874,-73.7773398987117 40.702850036793,-73.7769103774633 40.7025248853258,-73.7763829125391 40.7020949282884,-73.7758689289492 40.7016551707192,-73.7753688616938 40.7012059830451,-73.774883186405 40.7007477523601,-73.7744123011585 40.7002808768336,-73.7739565946594 40.6998057815896))')), (3,'Homecrest',ST_GeomFromText('POLYGON ((-73.9585927849577 40.610403030935,-73.958280569522 40.608747354782,-73.957724012078 40.6057998337066,-73.9575305759446 40.6048233088302,-73.9572834744956 40.6034696422002,-73.9568300163318 40.6010873490775,-73.9577574592259 40.6009858891051,-73.9573324632827 40.5987309142699,-73.9569271971363 40.5966120763113,-73.9565554049201 40.5946477253432,-73.9560772035243 40.5921014324455,-73.9570033042373 40.5920022562352,-73.9567157927849 40.5904987341702,-73.9566568339178 40.590101652897,-73.9565879051368 40.589884115185,-73.9563920019817 40.5889286758599,-73.9562272157761 40.5879959145243,-73.9561942455716 40.5877618269323,-73.9571256638599 40.5876609610387,-73.9581954315636 40.587544332289,-73.9593473673058 40.5874175372304,-73.9597803021433 40.5873694754772,-73.9603495358524 40.5873062855713,-73.9604071009968 40.5880343517998,-73.9604596836014 40.5887238198211,-73.9605166723842 40.5894529116476,-73.9605723046448 40.5901814271313,-73.9606241032518 40.5908626812079,-73.960679840684 40.5915975821236,-73.9628378585162 40.5913601115127,-73.9637736631535 40.5912572288887,-73.9648348555111 40.5911369691151,-73.9651438519249 40.591101916118,-73.9652882780718 40.5910866103009,-73.9654252379174 40.591071428373,-73.9665195111283 40.5909528734642,-73.9674452747824 40.5908512102937,-73.9678770216766 40.5908037561619,-73.9683709996116 40.5907494666111,-73.9692995928408 40.5906475899197,-73.9702233767037 40.5905401753408,-73.9711510183133 40.5904424440306,-73.9712874885898 40.5911595017495,-73.9714184756329 40.5918385099836,-73.9715553051332 40.5925604575562,-73.9717822018769 40.5937560838577,-73.9718968482308 40.5943525106931,-73.9717650899907 40.5951114114436,-73.9716955494848 40.5954885423192,-73.9715332445073 40.5963687092509,-73.971329510669 40.5963524307184,-73.9714835078879 40.5971715849578,-73.9716303815781 40.5979440169642,-73.9717610527433 40.5986519329472,-73.9719108047708 40.5994230591321,-73.9729111684123 40.5993158042032,-73.9725653370985 40.6017943730231,-73.9724526158846 40.602617054037,-73.9723515208722 40.603348118861,-73.9721774273492 40.6041947115372,-73.9723973547593 40.6056518906467,-73.9725898234115 40.6066715333734,-73.972994339389 40.6088141418022,-73.9719151872532 40.6089359216908,-73.9708953837774 40.6090497192104,-73.9699656611014 40.6091504768933,-73.9690408663949 40.6092531931632,-73.9679216879961 40.6093891895261,-73.9678067633597 40.6094031552795,-73.9676863562192 40.6094177874824,-73.9673997951279 40.6094596075738,-73.9662983547991 40.6095552138141,-73.9652237806769 40.6096744010221,-73.9642964983203 40.609776426175,-73.9647811280894 40.6123309221642,-73.9638533251958 40.6124325187131,-73.9628597396145 40.612549486467,-73.9623794782476 40.6099863745258,-73.9613726831358 40.6100988747116,-73.9604436188154 40.6102015068025,-73.9595206956783 40.6103020528368,-73.9585927849577 40.610403030935))'));
结果保存表
针对地理围栏场景，创建结果保存表fresult，表结构如下：
字段名 |
字段类型 |
含义 |
uID |
INT |
车辆ID |
rName |
VARCHAR |
区域名称 |
rID |
INT |
区域ID |
创建语句：
CREATE TABLE IF NOT EXISTS fresult ( uID VARCHAR, rName VARCHAR, rID INT, PRIMARY KEY (uID));
针对区域统计场景，创建结果保存表cresult，表结构如下：
字段名 |
字段类型 |
含义 |
rName |
VARCHAR |
区域名称 |
carCount |
INT |
车辆数统计 |
CREATE TABLE IF NOT EXISTS cresult( rName VARCHAR, carCount INT, PRIMARY KEY(rName));
接入流数据
这一步通过流引擎客户端连接流引擎并写入流数据。可以通过 Kafka API 写入数据到指定的 topic 中，见通过开源Kafka客户端写入Lindorm流引擎数据，或通过开源Kafka脚本工具连接Lindorm流引擎，并写入数据。此处演示通过脚本工具写入：
创建 Topic
创建 topic logVehicle，创建之前需要确保集群上不存在同名 topic，否则请更换 topic 名称。创建命令如下，请将ld-xxx替换成您的实例 ID：
./bin/kafka-topics.sh --bootstrap-server ld-xxx-proxy-stream.lindorm.rds.aliyuncs.com:30080 --topic logVehicle --create
您会看到创建成功的提示：
Created topic logVehicle
注：这里只创建了一个partition，如果想要创建多个partition，可以通过--partitions参数指定。
写入数据
用户的行程流数据如下，每个字段含义分别为：
字段名 |
含义 |
uID |
用户 ID |
x |
位置 x 坐标 |
y |
位置 y 坐标 |
tripTime |
时间戳 |
写入数据命令如下，请将ld-xxx替换成您的实例 ID：
./bin/kafka-console-producer.sh --bootstrap-server ld-xxx-proxy-stream.lindorm.rds.aliyuncs.com:30080 --topic logVehicle {"uID":"A","x":"-73.84742304","y":"40.67615477","tripTime":"2019-03-12 07:00:00"} {"uID":"B","x":"-73.80586723","y":"40.68456336","tripTime":"2019-03-12 07:01:00"} {"uID":"C","x":"-73.84838537","y":"40.68045710","tripTime":"2019-03-12 07:02:00"} {"uID":"D","x":"-73.85054493","y":"40.67567139","tripTime":"2019-03-12 07:03:00"} {"uID":"E","x":"-73.78196178","y":"40.69291659","tripTime":"2019-03-12 07:02:00"} {"uID":"F","x":"-73.96973146","y":"40.59762887","tripTime":"2019-03-12 07:01:00"} {"uID":"G","x":"-73.78496848","y":"40.69361194","tripTime":"2019-03-12 07:03:00"} {"uID":"H","x":"-73.78778279","y":"40.70284529","tripTime":"2019-03-12 07:02:00"}
查看 Topic 数据
查看数据命令如下，请将ld-xxx替换成您的实例 ID：
./kafka-console-consumer.sh --bootstrap-server ld-xxx-proxy-stream.lindorm.rds.aliyuncs.com:30080 --topic logVehicle --from-beginning
可以看到下列输出：
提交流引擎计算任务
这一步使用 Flink SQL 提交流引擎计算任务，读取 Kafka topic 中的数据并结合宽表数据做计算。
连接到流引擎
请参考下列链接连接到流引擎：使用客户端连接并使用Lindorm流引擎。建议在与流引擎同一个可用区的 ECS 环境下运行，运行前请将 ECS 的 IP 地址添加到访问控制白名单。请将ld-xxx替换成您的实例 ID：
./lindorm-sqlline -url jdbc:streamsql:url=http://ld-xxx-proxy-stream.lindorm.rds.aliyuncs.com:30060
创建计算任务
注意：
- Kafka的连接地址properties.bootstrap.servers，请使用 Lindorm Stream Kafka 私有网络地址；
- 宽表的连接地址seedServer，请使用 Lindorm 宽表 SQL 私有网络地址及 30020端口。
地理围栏计算任务
下面的计算任务将流入 Kafka 的点与 Lindorm 表做地理围栏过滤计算，输出每个点所在的行政区划：
- 首先，加载 ganos 函数模块
- 随后创建数据源表 carData、数据维表regions、数据结果表fresult
- 最后创建流任务，使用 ST_Contains函数过滤数据并写入数据结果表
请将ld-xxx替换成您的实例 ID：
CREATE FJOB fenceFilter ( LOAD MODULE ganos; CREATE TABLE carData(`uID` STRING, `x` DOUBLE, `y` DOUBLE ) WITH ( 'connector'='kafka', 'topic'='logVehicle', 'scan.startup.mode'='earliest-offset', 'properties.bootstrap.servers'='ld-xxx-proxy-stream.lindorm.rds.aliyuncs.com:30080', 'format'='json' ); CREATE TABLE regions ( `rID` INT, `rName` STRING, `fence` GEOMETRY, PRIMARY KEY (`rID`) NOT ENFORCED ) WITH ( 'connector'='lindorm', 'seedServer'='ld-xxx-proxy-lindorm.lindorm.rds.aliyuncs.com:30020', 'userName'='root', 'password'='your_passwd', 'tableName'='regions', 'namespace'='default' ); CREATE TABLE fresult ( `uID` STRING, `rName` STRING, `rID` INT, PRIMARY KEY (`uID`) NOT ENFORCED ) WITH ( 'connector'='lindorm', 'seedServer'='ld-xxx-proxy-lindorm.lindorm.rds.aliyuncs.com:30020', 'userName'='root', 'password'='your_passwd', 'tableName'='fresult', 'namespace'='default' ); INSERT INTO fresult SELECT A.uID, B.rName, B.rID FROM carData AS A JOIN regions AS B ON ST_Contains(B.fence, ST_MakePoint(A.x,A.y)); );
可以通过流引擎控制台查看任务流程：
区域统计计算任务
下面的计算任务构造了一个大小为 5 分钟的滚动窗口，每 5 分钟统计一次窗口内的数据：
- 首先，加载 ganos 函数模块
- 随后创建数据源表 logCarWithTs、数据维表regions、数据结果表cresult
- 最后创建流任务，使用count函数及ST_Contains函数、TUMBLE时间窗口过滤数据并写入数据结果表
请将ld-xxx替换成您的实例 ID：
CREATE FJOB fenceAgg ( LOAD MODULE ganos; CREATE TABLE logCarWithTs( `uID` STRING, `x` DOUBLE, `y` DOUBLE, `tripTime` TIMESTAMP(3), WATERMARK for `tripTime` AS `tripTime` ) WITH ('connector'='kafka', 'topic'='logVehicle', 'scan.startup.mode'='earliest-offset', 'properties.bootstrap.servers'='ld-xxx-proxy-stream.lindorm.rds.aliyuncs.com:30080', 'format'='json'); CREATE TABLE regions( `rID` INT, `rName` STRING, `fence` GEOMETRY, PRIMARY KEY (`rID`) NOT ENFORCED ) WITH ('connector'='lindorm', 'seedServer'='ld-xxx-proxy-lindorm.lindorm.rds.aliyuncs.com:30020', 'userName'='root', 'password'='root', 'tableName'='regions', 'namespace'='default'); CREATE TABLE cresult( `rName` STRING, `carCount` BIGINT NOT NULL, PRIMARY KEY (rName) NOT ENFORCED ) WITH ('connector'='lindorm', 'seedServer'='ld-xxx-proxy-lindorm.lindorm.rds.aliyuncs.com:30020', 'userName'='root', 'password'='root', 'tableName'='cresult', 'namespace'='default'); INSERT INTO cresult SELECT regions.rName, count(*) AS CarCount FROM (SELECT * FROM TABLE(TUMBLE(TABLE logCarWithTs, DESCRIPTOR(tripTime),INTERVAL '5' MINUTES))) L JOIN regions ON ST_Contains(regions.fence,ST_MakePoint(L.x,L.y)) GROUP BY regions.rName; );
可以通过流引擎控制台查看任务流程：
查看结果
在宽表中，通过 SQL 语句查看对应的计算结果：
- 地理围栏计算结果
SELECT * FROM fresult; +-----+---------------+-----+ | uID | rName | rID | +-----+---------------+-----+ | A | Ozone Park | 1 | | B | South Jamaica | 2 | | C | Ozone Park | 1 | | D | Ozone Park | 1 | | E | South Jamaica | 2 | | F | Homecrest | 3 | | G | South Jamaica | 2 | | H | South Jamaica | 2 | +-----+---------------+-----+
- 区域统计计算结果
SELECT * FROM cresult; +---------------+----------+ | rName | carCount | +---------------+----------+ | Homecrest | 1 | | Ozone Park | 3 | | South Jamaica | 4 | +---------------+----------+
总结
Lindorm Ganos实现了Lindorm宽表、流、计算等引擎在时空领域的打通，支持原生时空类型与多种时空算子，支持多种不同的时空索引，不仅可用于传统的周边查询，还面向了历史轨迹的查询分析、地理围栏查询、点面查询等更加复杂的业务需求。相较于传统的Geomesa、ElasticSearch等NoSQL时空方案，Ganos在多种场景下性能均有3-5倍以上的提升，同时兼容适配Flink、Spark引擎进一步降低了客户技术方案的复杂性，结合Lindorm在写入、压缩等方面的优势，Lindorm Ganos在海量时空数据管理分析领域构建出了既能满足客户功能与性能需求，又可大幅度降低存储计算成本的全新方案。
