协助报表开发之 MongoDB join mysql
集算器 SPL 语言支持处理多样性数据源，通过 SPL 对 MongoDB 集合与 MySql 表进行 join 关联，不仅简化了对 MongoDB 数据的操作，而且有利于与其它报表工具的方便集成。若想了解更多，请前往乾学院：协助报表开发之 MongoDB join mysql!
处理多样性数据源是报表开发的常见问题，而常见的 JasperReport 等报表工具本身却难以应付，比如展现MongoDB和mysql的混合运算。虽然JasperReport/Birt有virtual data source或table join等功能，但这些功能只在商业版或高端版本出现，在免费版中实现难度很大。而且功能也有较大局限，对连接后的数据无法进行类似SQL的结构化计算。
集算器 SPL 语言面向结构化处理的强计算引擎，支持多样性数据源，集成简单，可以协助不同报表工具方便地实现此类需求，下面用例子说明
emp是MongoDB的集合，cities是 mysql的table，emp中的字段CityID逻辑上相当于外键，指向cities的CityID字段，cities有CityID和CityName这两个字段。现在需要按时间段查询出emp中的员工，并将CityID显示为CityName。部分源数据如下：
Collection emp| EID | Dept | CityID | Name | Gender | Salary | Birthday |
| 10 | R&D | 199 | Ryan | M | 13000 | 1976-03-12 |
| 100 | Sales | Jacob | M | 5000 | 1978-02-12 | |
| 101 | Sales | 56 | Michael | M | 6500 | 1984-03-29 |
| 102 | Sales | 46 | Christian | M | 12000 | 1972-07-25 |
| 103 | Marketing | 34 | Madison | F | 5000 | 1976-07-11 |
| 104 | Marketing | 6 | Sarah | F | 8000 | 1982-11-17 |
| 105 | Marketing | 4 | Tyler | M | 6500 | 1978-04-08 |
| 106 | Marketing | 6 | Emily | F | 7000 | 1975-12-05 |
| 107 | Marketing | 2 | Madison | F | 5000 | 1981-09-29 |
| CityID | CityName | Population | StateId |
| 1 | New York | 8084316 | 32 |
| 2 | Los Angeles | 3798981 | 5 |
| 3 | Chicago | 2886251 | 13 |
| 4 | Houston | 2009834 | 43 |
| 5 | Philadelphia | 1492231 | 38 |
| 6 | Phoenix | 1371960 | 3 |
| 7 | San Diego | 1259532 | 5 |
使用集算器SPL的代码如下：
A |
|
1 |
=mongo_open("mongodb://localhost:27017/test?user=test&password=test") |
2 |
=mongo_shell(A1,"emp.find({'$and':[{'Birthday':{'$gte':'"+string(begin)+"'}},{'Birthday':{'$lte':'" +string(end)+"'}}]},{_id:0})").fetch() |
3 |
=mongo_close(A1) |
4 |
=myDB1.query("select * from cities") |
5 |
=A2.switch(CityID,A4: CityID) |
6 |
=A5.new(EID,Dept,CityID.CityName:CityName,Name,Gender) |
7 |
result A6 |
A1: 连接 MongoDB，连接字格式为 mongo://ip:port/db?arg=value&…
A2: 使用find函数从集合emp中查询出某时间段的记录，取出 _id 之外的所有字段，形成游标。其中查询条件中的 begin 和 end 是来自报表的外部参数，分别表示Birthday的起始时间和终止时间。SPL游标能够分批读取和处理数据，可以避免数据量过大，以防内存溢出。因为数据量不大，所以这里可以直接 fetch 出游标的所有记录，结果如下：
A3: 关闭 MongoDB。
A4: 执行SQL查询，从mysql数据源取数。其中myDB1是数据源名称，配置界面如下：
函数query使用SQL语句进行检索查询，结果如下：
A5: 将A2中的CityID字段替换成A4中对应的记录，其效果类似于左连接。替换后的A2如下（A2与A5指向同一个二维表）：
点击CityID中蓝色的超链接，可以看到对应的记录：
有时需要进行内连接，则应当在函数swtich中使用选项@i，代码即：A2.switch@i(CityID,A4)，结果将会如下：
A6: 从连接的结果中取出需要的字段，并用函数new组成二维表。其中CityID.CityName:CityName表示从A5取出CityID字段对应的记录中的CityName字段，并重命名为CityName（报表工具无法识别CityID.CityName这样的字段名）。
从上述代码可以看出，用switch替换字段后，表之间的关联关系就可以用对象的方式来访问，这种方式直观简单，进行多表多层关联时会体现得更明显。计算结果如下：
到此为止，报表所需要的数据都计算出来了。
A7: 将 A6 中的二维表返回报表工具。
SPL对外提供JDBC接口，报表工具会将SPL识别为普通数据库，集成方案请参考相关文档。
接下来以JasperReport为例设计报表，表样如下：
需要定义两个报表参数Pbegin、Pend，分别对应SPL中的两个参数。预览后可以看到报表结果：
报表调用SPL的方法和调用存储过程一样，此例中可以将本脚本保存为mongodbJoin2.dfx，在JasperReport的SQL设计器中可以用mongodbJoin2 $P{pbegin},$P{pend}来调用。
简言之，SPL 从 MongoDB 获取集合数据，从 Mysql 查询表记录后，将它们通过 switch 函数进行替换字段，再通过函数 new 按需求将数据组成序表，供其它的报表工具展示。通过 SPL 对多样性数据源支持，按需要进行数据合并，这样简化了对 MongoDB 数据的操作，有利于与其它报表工具的方便集成。
