lightdb周边-聚合查询参考手册

案例合集

聚合查询案例合集资料-快捷链接

前言

聚合查询主要有两个模块组成，一个是Java服务作为接入层；另外一个是LightDB数据库作为引擎层。

系统架构

HTTP聚合

聚合查询支持SQL语句中直接调用HTTP服务来获取JSON数据集。

安装http插件

安装HTTP插件，ssh连接在LightDB部署服务器并切换到LightDB部署用户中。

su - lightdb

这里lightdb是笔者部署LightDB的Linux用户名。使用ltsql postgres命令进入LightDB SQL客户端。进入控制台后，执行如下命令来完成http插件安装：

create extension http;

结果输出CREATE EXTENSION表示创建成功。控制台中验证命令如下：

select status from http_get('http://www.light-pg.com/');;

结果不是提示ERROR: function http_get(unknown) does not exist则表示可以正常使用http插件。

SQL简写支持

想要在Mybatis的SQL文件中使用简写语法${http.服务别名}来远程调用以及自动展开远程调用json结果集为二维表，需要配置服务信息。服务配置表如下：

配置表es_http_service

• url
  HTTP服务地址，如：http://ip:port/order/getOrder，支持域名
• method
  固定为post
• alias
  服务别名，如getExpressInfo，不能包含字符 . -
• ret_key
  服务返回要抽取的json节点基准路径,如key1.key2 默认为 # 表示从根层抽取数据
• ret_type
  ret_key指定的节点数据类型, jsonObject 或者 jsonArray ；如果数据是[]则是 jsonArray 否则就是 jsonObject

配置表es_http_extract

• service_alias 服务别名，同es_http_servie.alias的值
• data_name 字段key名, es_http_service.ret_key基准路径下的key
• data_type 字段类型, data_name 对应数据的类型，支持类型 text integer bigint numeric boolean date time timestamp jsonb

用户可以使用LightDB客户端ltsql、dbeaver等工具连接上LightDB后，使用insert SQL语句进行配置插入。
dbeaver下载连接见http://www.light-pg.com/downloadCate.html

mybatis mapper.xml文件中SQL简写示例如下：

<select id="getExpressInfo" resultType="map">
    select * from ${http.getExpressInfo}
</select>

上述示例中，将调用配置的别名为getExpressInfo的服务并返回json结果。

<select id="getExpressInfo" resultType="map">
    select * from ${http.getExpressInfo} expressInfo
</select>

上述示例中，将调用配置的别名为getExpressInfo的服务并返回json结果，结果展开为二维表后为表取别名expressInfo，可作用于select子句中的字段或者where子句中的字段。

T3聚合

聚合查询支持SQL语句中直接调用JRES T3服务来获取JSON数据集。

安装T3插件

安装T3插件，ssh连接在LightDB部署服务器并切换到LightDB部署用户中。

su - lightdb

这里lightdb是笔者部署LightDB的Linux用户名。
使用ltsql postgres命令进入LightDB SQL客户端。进入控制台后，执行如下命令来完成http插件安装：

create extension t3sdk;

结果输出CREATE EXTENSION表示创建成功。控制台中验证命令如下：

select * from t3sdk_send('127.0.0.1:18081', '', '', '', 'getOrder', '{}');

结果不是提示ERROR: function t3sdk_send(unknown, unknown, unknown, unknown, unknown, unknown) does not exist ，则表示可以正常使用t3插件。

SQL简写支持

想要在Mybatis的SQL文件中使用简写语法${t3.服务别名.功能号}来远程调用以及自动展开远程调用json结果集为二维表，需要配置服务信息。服务配置表如下：

配置表es_t3_service

• service_name
  应用名，JRES微服务的app.name
• service_alias
  服务别名，如orderApp，不能包含字符 . -
• service_group
  应用分组，JRES微服务的app.group
• service_version
  应用版本号，JRES微服务的app.version
• function_id
  JRES微服务中@CloudFunction中的functionId指定的值。
• function_alias
  功能号别名，如getOrder，不能包含字符 . -
• interface_name
  JRES微服务中@CloudService所标注的接口的全路径，如：com.hundsun.demo.order.api.OrderService
• ret_key
  服务返回要抽取的json节点基准路径,如key1.key2 默认为 # 表示从根层抽取数据
• ret_type
  ret_key指定的节点数据类型, jsonObject 或者 jsonArray ；如果数据是[]则是 jsonArray 否则就是 jsonObject

配置表es_t3_extract

• service_alias 服务别名，同配置表es_t3_service.service_alias的值
• function_alias 功能号别名，同配置表es_t3_service.function_alias的值
• data_name 字段key名, es_t3_service.ret_key基准路径下的key
• data_type 字段类型, data_name 对应数据的类型，支持类型 text integer bigint numeric boolean date time timestamp jsonb

用户可以使用LightDB客户端ltsql、dbeaver等工具连接上LightDB后，使用insert SQL语句进行配置插入。dbeaver下载连接见http://www.light-pg.com/downloadCate.html

mybatis mapper.xml文件中SQL简写示例如下：

<select id="getOrder" resultType="map">
    select * from ${t3.orderApp.getOrder}
</select>

上述示例中，将调用配置的别名为getExpressInfo的服务并返回json结果。

<select id="getOrder" resultType="map">
    select * from ${t3.orderApp.getOrder} orderInfo
</select>

上述示例中，将调用配置的别名为orderApp、功能号别名为getOrder的服务并返回json结果，结果展开为二维表后为表取别名orderInfo，可作用于select子句中的字段或者where子句中的字段。

外部表聚合

通过LightDB FDW技术，可以像访问本地表一样访问外部表。

Oracle外部表

oracle_fdw支持像访问本地表一样访问Oracle中的表。

安装Oracle插件，示例如下：
前置条件，需要确保LightDB数据库用户具备Oracle客户端的访问执行权限。下载安装好Oracle客户端后需要配置环境变量，示例如下：

export ORACLE_HOME=/home/lightdb/instantclient_19_10
export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
export TNS_ADMIN=/home/lightdb/instantclient_19_10
export NLS_LANG=american_america.utf-8

上述路径为笔者的环境路径，使用者需要根据自身环境情况使用自身的路径。
注意: 需确保环境变量生效后启动LightDB，如果已经启动LightDB，可以尝试重启。

安装Oracle FDW插件，语句如下：
create extension oracle_fdw;

配置Oracle，示例如下：

create server oradb foreign data wrapper oracle_fdw options (dbserver '//<地址>:<端口>/<SID>');
create user mapping for <LightDB用户名> server oradb options (user '<Oracle用户名>', password '<Oracle密码>');
GRANT USAGE ON FOREIGN DATA WRAPPER oracle_fdw TO CURRENT_USER;
GRANT USAGE ON FOREIGN SERVER oradb TO CURRENT_USER;

创建外部表，示例如下：

CREATE foreign table express_info (
   express_id VARCHAR(64),
   order_id  VARCHAR(64) ,
   express_no VARCHAR(64),
   company VARCHAR(64) ,
   create_datetime  VARCHAR(14),
   status  VARCHAR(1)
) server oradb OPTIONS (schema '<XXXX>', table '<EXPRESS_INFO>' );

注意：上述示例中schema指定的名字和table指定的名字在ORACLE一般为大写。到这里，可以像访问本地表一样访问Oracle表了。

MySQL外部表

mysql_fdw支持像访问本地表一样访问mysql中的表。

安装mysql插件，示例如下：
前置条件，需要确保LightDB数据库用户具备MySQL客户端的访问执行权限。下载安装好MySQL客户端后需要配置环境变量，示例如下：

export MYSQL_HOME=/home/root/mysql/mysql
export LD_LIBRARY_PATH=$MYSQL_HOME/lib:$LD_LIBRARY_PATH

上述路径为笔者的环境路径，使用者需要根据自身环境情况使用自身的路径。
注意: 需确保环境变量生效后启动LightDB，如果已经启动LightDB，可以尝试重启。

安装MySQL FDW插件，语句如下：
create extension mysql_fdw;

配置MySQL，示例如下：

create server mysqldb foreign data wrapper mysql_fdw options (host '<地址>', port '<端口>');
create user mapping for <LightDB用户名> server mysqldb options (username '<MySQL用户名>', password '<MySQL密码>');
GRANT USAGE ON FOREIGN DATA WRAPPER mysql_fdw TO CURRENT_USER;
GRANT USAGE ON FOREIGN SERVER mysqldb TO CURRENT_USER;

创建外部表，示例如下：

CREATE foreign table express_info (
    express_id VARCHAR(64),
    order_id  VARCHAR(64) ,
    express_no VARCHAR(64),
    company VARCHAR(64) ,
    create_datetime  VARCHAR(14),
    status  VARCHAR(1)
    ) server mysqldb OPTIONS (dbname 'easysearch', table_name 'EXPRESS_INFO' );

到这里，可以像访问本地表一样访问Oracle表了。

聚合API

easy-search有三类API暴露，自省HTTP服务，JRES T3服务以及IAR网关的HTTP服务。下面的 <API服务名> 均取值于MyBatis mapper文件的StatementId短名字。举个例子：

<select id="getExpressInfo" resultType="map">
    select * from ${http.getExpressInfo}
</select>

上面的SQL代码片断代表聚合服务getExpressInfo。

自省HTTP接口

easy-search启动后自身会暴露一组HTTP接口服务用于聚合查询，规则如下：

http://ip:port/gateway/easysearch/<API服务名>

ip为easy-search所在服务地址。port为了easy-search配置文件中server.port指定的端口。

JRES T3接口

easy-search启动后自身会暴露一组JRES T3接口服务用于聚合查询，规则如下：
接口名规则：mybatis mapper xml文件中的namespace的值

<mapper namespace="com.hundsun.easysearch.TestMapper">

如上interfaceName是com.hundsun.easysearch.TestMapper

功能号规则：com.hundsun.easysearch.gateway.<API服务名>

IAR网关API

easy-search启动后自身会暴露一组IAR HTPP接口服务用于聚合查询，规则如下：

http://ip:port/<分组>/<服务名>/<版本号>/<API服务名>

分组是app.group指定的值，默认为g；服务名是app.name指定的值，默认是easy-search；版本号是app.version指定的值，默认是v

链接：lightdb聚合查询