- 介绍
- part 1 ： 无表查询
- part 2 ： 查询 json
- part 3 ： 查询 sql / mongo
- part 4 ： 变量
- 使用 sql 查表结构
- 在页面上查询表结构
- 补充
- 常见问题
- 相关页面
介绍
sybn dao 是一个基于异构数据查询 java 工具 (效果有类似于apache.calcite)，支持用 sql 联合查询 mongo, solr, elastic, Hbase, redis, List, Stream, json, excel, csv, jdbc(mysql, presto, gbase, hive2), 等数据源。
这里准备了专门的 web 版的 adhoc 查询环境 (类似于 hadoop 的 HUE)，用于测试不同数据库的 sql 查询效果。
所有测试 sql 仅作为抛砖引玉，大家可以按自己的兴趣尝试修改和测试。
注: 本工具对于sql规范的支持说明： 点此查看
part 1 ： 无表查询
select now() as now, date_format(now(),'%Y-%m-%d') as today;
-
说明
在 java 内存中直接执行 udf 函数，并反返回结果。 这个操作是直接执行在 java 中的， 不依赖任何数据库环境。
支持大部分比较常用的udf函数(函数名默认不区分大小写)， 比如: concat, timestampDiff, str_to_date 等。 使用 show functions 可以查看支持的函数列表。
顺便兼容 java 风格的时间格式化 ，比如： date_format(now(),’yy-MM-dd’)。
- 提示
点击右侧查询按钮执行 sql ， 点击底部的 table 可以将返回的 json 数据转换为表格。
- 测试页效果图
part 2 ： 查询 json
select a, sum(b) as s
from [{a:1,b:1},{a:2,b:2},{a:3,b:3},{a:1,b:4}]
group by a
having s > 0
order by a;
-
说明
支持直接查询 json 数据， 语法是直接将 json 放在 from 后面, 目前只支持查询对象数组。
可以对 json 执行各种 sql 操作， 包括但不限于： where, group by, having, order by, skip, limit 等。
- 提示
点击右侧查询按钮执行 sql ， 点击底部的 chars 可以将返回的 json 数据转换为图表。
- 测试页效果图
part 3 ： 查询 sql / mongo
- 查看所有的表及其存储引擎
show table status
可以看到 mongo_demo_table 表的存储引擎是 sybnMongo
- 在一条 sql 里联合查询 mysql 和 mongodb 两个数据源
-- 查询 sql, 2 == '2' 所以可以获取4行
select 'select sql' as action, name, type, create_time from sql_demo_table where type in (1, '2')
union all
-- 查询 mongo, 2 != '2' 所以只能得到2行
select 'select mongo' as action, name, type, create_time from mongo_demo_table where type in (1, '2')
union all
-- group by 子查询
select 'group by' as action, name, min(type) as type, max(create_time) as create_time from (
select * from sql_demo_table
union all
select * from mongo_demo_table
) group by name
-
说明
可以直接把 mongo 库中的表当成 mysql 表进行 sql 查询。
限制： mongo 只支持少部分 udf 函数。 mongo 比较操作时，区分字段类型。
因为测试服务器内存比较小， 暂时不提供 solr, elastic, hbase 的在线测试， 但是执行效果与 mongo 类似。
- 提示
mongo 表支持使用 a.b > 1 查询，语法与原生 mongo 一致。
- 测试页效果图
part 4 ： 变量
-- 先将字符串转时间并赋值给变量, 然后再查询
set @t := str_to_date("2019-03-01", '%Y-%m-%d');
select * from sql_demo_table where create_time < @t and type in (
select type from mongo_demo_table where create_time > @t
);
-
说明
可以设置变量， 方便后续sql重复引用。
兼容 := 和 = 赋值, 两者没有区别.
暂不支持 select 赋值, 仅允许 set 赋值.
- 测试页效果图
- 扩展
支持 @a@list 变量, 用于给 in 查询传值.
-- 先将字符串转时间并赋值给变量, 然后再查询
set @type@list := (select type from mongo_demo_table where create_time > @t);
select * from sql_demo_table where create_time < @t and type in (@type@list);
使用 sql 查表结构
-
以下命令支持查询任意数据源 mongo, solr, elastic, Hbase, mysql, gbase, presto
对于 mongo, hbase 等数据库执行 show create table 时会用第一条数据模拟.
在页面上查询表结构
补充
测试页面的实现类是 SqlDdlDaoMultipleImpl，在 java 中可以使用如下代码执行本页的所有sql。 （详情点击 quick-start 链接）
// 构造 dao (所有 dao 都是线程安全的)
SqlDdlDao dao1 = new DbutilDaoImpl("jdbc:mysql://账户:密码@192.168.4.31:3306,192.168.4.32:3306/test"); // sql
SqlDdlDao dao2 = new MongoDaoImpl("mongodb://账户:密码@192.168.4.31:27017,192.168.4.32:27017/test"); // mongo
SqlDdlDaoMultipleImpl multipleDao = new SqlDdlDaoMultipleImpl();
multipleDao.addAllTableSource(dao1);
multipleDao.addAllTableSource(dao2);
//执行 sql 查询
String sql = "select now() as now, date_format(now(),'%Y-%m-%d') as today";
multipleDao.sqlFindListMap(sql)
注意： SqlDdlDaoMultipleImpl 将所有的数据载入到内存中查询。如果查询的数据量较大，可以使用 SqlDdlStreamDao 或者 SqlDdlDaoStreamAsyncImpl 进行流式计算。
常见问题
- 不支持比较符左侧使用函数 ``` – 不支持如下 sql select * from table where date_format(create_time,’%Y-%m-%d’) = ‘2022-12-14’
– 推荐方案: 遵从 mysql 常规优化建议, 去除左侧比较符 select * from table where create_time betweent ‘2022-12-14 00:00:00’ and ‘2022-12-14 23:59:59’
– 推荐方案: mongo, solr, es 等数据类型敏感的数据库可使用 toDate select * from table where create_time betweent toDate(‘2022-12-14 00:00:00’) and toDate(‘2022-12-14 23:59:59’)
– 加表别名, 跳过验证, 直接发送 sql 给 jdbc 强制执行. select * from table t where date_format(create_time,’%Y-%m-%d’) = ‘2022-12-14’ ```
