前言
使用聚合查询中间件,多个http、t3、fdw服务的结果聚合,同时利用SQL引擎的能力对结果进行连接、分组、排序、聚合
安装聚合查询
试用版本安装见 lightdb周边-聚合查询试用包指引_星辰bitone的博客-CSDN博客
场景
假设业务应用分为用户域微服务、订单域微服务、产品域微服务,每个微服务拥有单独的数据库。 如下图所示:
在场景用户认购理财产品后,会在订单域里面产生下单记录,产品域中会有用户购买理财产品中的每日收益情况。产品、订单、用户分别提供如下服务:
产品域:/product/getProducts 查询产品收益信息
订单域:/order/getOrders 查询订单信息
用户域:/user/getUser 查询用户信息
聚合查询开发配置
服务配置
聚合查询配置业务域服务,假定业务服务提供了HTTP服务,则配置如下,规则见:lightdb周边-聚合查询参考手册_星辰bitone的博客-CSDN博客
用户服务配置
INSERT INTO es_http_service (url, "method", alias, ret_key, ret_type) VALUES('http://ip:port/user/getUser', 'post', 'getUser', 'result', 'jsonArray');
INSERT INTO es_http_extract(service_alias, data_name, data_type)VALUES('getUser', '"userId"', 'text');
INSERT INTO es_http_extract(service_alias, data_name, data_type) VALUES('getUser', '"userName"', 'text');
订单服务配置
INSERT INTO es_http_service (url, "method", alias, ret_key, ret_type) VALUES(''http://ip:port/order/getOrders', 'post', 'getOrders', 'result', 'jsonArray');
INSERT INTO es_http_extract(service_alias, data_name, data_type) VALUES('getOrders', '"orderId"', 'text');
INSERT INTO es_http_extract(service_alias, data_name, data_type) VALUES('getOrders', '"userId"', 'text');
INSERT INTO es_http_extract(service_alias, data_name, data_type) VALUES('getOrders', '"productId"', 'text');
INSERT INTO es_http_extract(service_alias, data_name, data_type) VALUES('getOrders', '"status"', 'text');
产品服务配置
INSERT INTO es_http_service (url, "method", alias, ret_key, ret_type) VALUES(''http://ip:port//product/getProducts', 'post', 'getProducts', 'result', 'jsonArray');
INSERT INTO es_http_extract(service_alias, data_name, data_type) VALUES('getProducts', '"userId"', 'text');
INSERT INTO es_http_extract(service_alias, data_name, data_type) VALUES('getProducts', '"productId"', 'text');
INSERT INTO es_http_extract(service_alias, data_name, data_type) VALUES('getProducts', '"productName"', 'text');
INSERT INTO es_http_extract(service_alias, data_name, data_type) VALUES('getProducts', '"amountDate"', 'text');
INSERT INTO es_http_extract(service_alias, data_name, data_type) VALUES('getProducts', '"amount"', 'numeric');
mybatiis xml配置
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hundsun.easysearch.TestMapper">
<select id="getUserTradeTrendInfo" resultType="map">
SELECT * FROM
(
SELECT u."userId", u."userName", o."productId", p."productName", p."amountDate", sum(p."amount")
FROM
${http.getProducts} p ,
${http.getOrders} o ,
${http.getUser} u
WHERE
u."userId" = o."userId" and o."status" = 'S' and o."productId" = p."productId"
GROUP BY u."userId", u."userName", o."productId", p."productName", p."amountDate"
) t
ORDER BY t."amountDate" DESC, t."productName" DESC limit #{pageSize} offset #{pagePos}
</select>
</mapper>
上述mybatis xml配置内容放到TestMaper.xml中,并放到聚合查询接入层easysearch.mapper-locations指定的目录下。
[fcperf@hs-10-20-30-193 easysearch]$ cat config/application.properties | grep locations easysearch.mapper-locations=./mapper
[fcperf@hs-10-20-30-193 easysearch]$ ls mapper/
TestMapper.xml
启动聚合查询接入层
[fcperf@hs-10-20-30-193 easysearch]$ ls a
rthas-output config easy-search.jar logs mapper start.sh stdout.log stop.sh
[fcperf@hs-10-20-30-193 easysearch]$ ./start.sh
[fcperf@hs-10-20-30-193 easysearch]$ tail -f logs/easy-search-app.log
查看启动情况,启动成功后可以请求接口getUserTradeTrendInfo,如下图