应对没完没了的报表开发 – esProc for Reporting | 润乾 - 高性价比报表工具 | 高性能大数据计算

技术架构

与报表工具/Java应用无缝结合

esProc纯Java开发

轻量级
嵌入式，无独立服务器
JVM，JDK1.8及以上
VM/Container/Android

易于开发调试的网格编程

简化报表复杂SQL

报表目标查询销售额占总额一半的大客户及订单情况

SQL

SELECT CUSTOMER, AMOUNT, SUM_AMOUNT
	FROM (SELECT CUSTOMER, AMOUNT,
		SUM(AMOUNT) OVER(ORDER BY AMOUNT DESC) SUM_AMOUNT
			FROM (SELECT CUSTOMER, SUM(AMOUNT) AMOUNT
	   			FROM ORDERS GROUP BY CUSTOMER))
	   			WHERE 2 * SUM_AMOUNT < (SELECT SUM(AMOUNT) TOTAL FROM ORDERS)

SPL

	A	B
1	=db.query("select customer,amount from orders order by amount desc")
2	=A1.sum(amount)/2	=0
3	=A1.pselect((B1+=amount)>=A2)	return A1.to(A3)

更复杂的SQL

报表目标统计每天新用户的次日留存情况

SQL

WITH first_login AS ( 
    SELECT userid, MIN(TRUNC(ts)) AS first_login_date  FROM login_data GROUP BY userid),
next_day_login AS (
    SELECT DISTINCT(fl.userid), fl.first_login_date, TRUNC(ld.ts) AS next_day_login_date
    FROM first_login fl LEFT JOIN login_data ld ON fl.userid = ld.userid WHERE TRUNC(ld.ts) = fl.first_login_date + 1),
day_new_users AS (
    SELECT first_login_date,COUNT(*) AS new_user_num FROM first_login GROUP BY first_login_date),
next_new_users AS (
    SELECT next_day_login_date, COUNT(*) AS next_user_num FROM next_day_login GROUP BY next_day_login_date),
all_date AS (
    SELECT DISTINCT(TRUNC(ts)) AS login_date FROM login_data)
SELECT all_date.login_date+1 AS dt,dn. new_user_num,nn. next_user_num,
      (CASE  WHEN nn. next_day_login_date IS NULL THEN 0  ELSE nn.next_user_num END)/dn.new_user_num AS ret_rate
FROM all_date JOIN day_new_users dn ON all_date.login_date=dn.first_login_date
    LEFT JOIN next_new_users nn ON dn.first_login_date+1=nn. next_day_login_date
ORDER BY all_date.login_date;

SPL

	A
1	=file(“login_data.csv”).import@tc()
2	=A1.group(userid;fst=date(ts):fst_login,~.(date(ts)).pos(fst+1)>0:w_sec_login)
3	=A2.groups(fst_login+1:dt;count(w_sec_login)/count(1):ret_rate)

丰富格式报表

制作复杂报表比BIRT/JasperReport更简单

多数据源报表

原生数据源接口直接访问，无须事先定义映射关系，保持数据源特性
轻量级，避免沉重的逻辑数仓

多源混算应用 — 实时报表

历史冷数据由AP库计算后读取
交易热数据实时从TP库读取
混合计算实现全数据实时报表

跨库移植

一句SQL，到处运行，换数据库不改报表

跨库移植-转换实例

SQL:

SELECT EID, NAME, BIRTHDAY, ADDMONTHS(BIRTHDAY,10) DAY10 FROM EMP

⇩ esProc转换 ⇩

ORACLE:

SELECT EID, NAME, BIRTHDAY, BIRTHDAY+NUMTOYMINTERVAL(10,'MONTH') DAY10 FROM EMP

SQLSVR:

SELECT EID, NAME, BIRTHDAY, DATEADD(MM,10,BIRTHDAY) DAY10 FROM EMP

DB2:

SELECT EID, NAME, BIRTHDAY, BIRTHDAY+10 MONTHS DAY10 FROM EMP

MYSQL:

SELECT EID, NAME, BIRTHDAY, BIRTHDAY+INTERVAL 10 MONTH DAY10 FROM EMP

POSTGRES:

SELECT EID, NAME, BIRTHDAY, BIRTHDAY+interval '10 months' DAY10 FROM EMP

TERADATA:

SELECT EID, NAME, BIRTHDAY, ADD_MONTHS(BIRTHDAY, 10) DAY10 FROM EMP

文件数据源

没有数据库也能用SQL

$select * from Orders.csv where Client like '%bro%’ ;

$select o.OrderId,o.Client,e.Name e.Dept,e.EId from Orders.txt o
left join Employee.txt e on o.SellerId=e.Eid;

$select * from {file("Orders.txt").import@t(;":")}
where Amount>=100 and Client like 'bro' or OrderDate is null;

$select * from Orders.xlsx
where Amount>=100 and Client like 'bro' or OrderDate is null

$select * from {json(file("data.json").read())}
where Amount>=100 and Client like 'bro' or OrderDate is null

$select * from { httpfile("http://127.0.0.1:6868/Orders.csv").import@tc() }
where Amount>=100 and Client like 'bro' or OrderDate is null

报表微服务

报表数据源以微服务形式提交，易于扩展移植
esProc脚本解释执行，天然支持热切换
适应多变的报表业务

存储过程替代

存储过程迁移到库外，不依赖于数据库，天然可移植
降低应用间耦合性
无须编译存储过程权限，提升安全性和可靠性

消除数据库中间表

中间表转储到文件存储和计算，减轻数据库负担
树形结构的文件系统易于管理，降低应用间耦合

多线程并行取数

多线程并行取数，同构异构库均可，提高查询效率

单表并行取数：

	A	B
1	fork to(12)	=connect("oracle")
2		=B1.query@x("SELECT * FROM CUSTOMER WHERE MOD(C_CUSTKEY,?)=?", n, A1-1)
3	=A1.conj()

多表（多库）并行取数：

	A	B
1	SELECT * FROM SUPPLIER
2	SELECT * FROM PART
3	SELECT * FROM CUSTOMER
4	SELECT * FROM PARTSUPP
5	SELECT * FROM ORDERS
6	fork [A1:A5]	=connect("oracle")
7		=B6.query@x(A6)

简易的大数据与并行计算

内存

外存

大数据

	A
1	=file(sales.txt).import@t()
2	=A1.select(od>=20240101)
3	=A2.groups(area,emp;sum(amount):amount)

	A
1	=file(sales.txt).cursor@t()
2	=A1.select(od>=20240101)
3	=A2.groups(area,emp;sum(amount):amount)

并行计算

	A
1	=file(sales.txt).import@tm()
2	=A1.select(od>=20240101)
3	=A2.groups(area,emp;sum(amount):amount)

	A
1	=file(sales.txt).cursor@tm()
2	=A1.select(od>=20240101)
3	=A2.groups(area,emp;sum(amount):amount)

加个选项就能并行，快速提升计算性能

本地文件数据缓存

文件系统IO更快
二进制格式无须解析
高性能支持：压缩、列存、索引、…

高性能算法

遍历技术

延迟游标
聚合理解
有序游标
遍历复用
预过滤遍历

高效关联

外键指针化
外键序号化
有序归并
附表
单边分堆连接

高速存储

有序压缩存储
列式存储
层次序号式定位
索引及缓存
倍增分段并行

提高报表计算性能

esProc for Reporting