The Open-source SPL Boosts MongoDB Computing Ability
Bentil Shadrack
Posted on November 14, 2023
MongoDB is a typical NoSQL database. Its document-oriented structure makes both storage and access convenient and efficient. But the database has rather weak computing ability. Computations on MongoDB data, particularly complex ones, are hard to handle. A data computing engine having powerful computing capability is needed to work with MongoDB to achieve relevant computing tasks.
The open-source esProc SPL is a specialized structured data computation engine. It supplies rich class libraries and all-around, database-independent computational capabilities. SPL has an independent procedural syntax that is particularly good at handling complex computations. It can help MongoDB increase its ability to compute, accomplish grouping & aggregation, joins, subqueries, and all the other computing tasks effortlessly.
Regular queries
It is easy to achieve JOINs MongoDB finds it difficult to handle in SPL:
| A | B | |
|---|---|---|
| 1 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") |
Connect to MongoDB |
| 2 | =mongo_shell(A1,"c1.find()").fetch() |
Fetch data from MongoDB |
| 3 | =mongo_shell(A1,"c2.find()").fetch() |
|
| 4 | =A2.join(user1:user2,A3:user1:user2,output) |
Perform join |
| 5 | >A1.close() |
Close connection to MongoDB |
SPL can reuse the result of handling data of a table that is repeatedly involved in computations:
| A | B | |
|---|---|---|
| 1 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") |
|
| 2 | =mongo_shell(A1,"course.find(,{_id:0})").fetch() |
Fetch data from MongoDB |
| 3 | =A2.group(Sno).((avg = ~.avg(Grade), ~.select(Grade>avg))).conj() |
Get documents where grading level is above average |
| 4 | >A1.close() |
Perform IN conditional query in SPL:
| A | B | |
|---|---|---|
| 1 | =mongo_open("mongodb://localhost:27017/test") |
|
| 2 | =mongo_shell(A1,"orders.find(,{_id:0})") |
Fetch data from MongoDB |
| 3 | =mongo_shell(A1,"employee.find({STATE:'California'},{_id:0})").fetch() |
Select certain employee documents |
| 4 | =A3.(EID).sort() |
Get EID field and sort it |
| 5 | =A2.select(A4.pos@b(SELLERID)).fetch() |
Perform binary search |
| 6 | >A1.close() |
SPL’s technique to turn foreign key values to objects – the object-referencing foreign key – creates efficient foreign key pointers:
| A | B | |
|---|---|---|
| 1 | =mongo_open("mongodb://localhost:27017/local") |
|
| 2 | =mongo_shell(A1,"Progress.find({}, {_id:0})").fetch() |
Fetch Progress data |
| 3 | =A2.groups(courseid; count(userId):popularityCount) |
Group and count by course |
| 4 | =mongo_shell(A1,"Course.find(,{title:1})").fetch() |
Get Course data |
| 5 | =A3.switch(courseid,A4:_id) |
Foreign-key-based join |
| 6 | =A5.new(popularityCount,courseid.title) |
Create result set |
| 7 | =A1.close() |
SPL achieves APPLY algorithm in a simple way:
| A | B | |
|---|---|---|
| 1 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") |
|
| 2 | =mongo_shell(A1,"users.find()").fetch() |
Fetch users data |
| 3 | =mongo_shell(A1,"workouts.find()").fetch() |
Fetch workouts data |
| 4 | =A2.conj(A3.select(A2.workouts.pos(_id)).derive(A2.name)) |
Get matching _id values from the sequence of workouts documents |
| 5 | >A1.close() |
SPL’s way of performing set-oriented calculations – intersection, union, difference, and concatenation:
| A | B | |
|---|---|---|
| 1 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") |
|
| 2 | =mongo_shell(A1,"emp1.find()").fetch() |
|
| 3 | =mongo_shell(A1,"emp2.find()").fetch() |
|
| 4 | =[A2,A3].conj() |
Concatenation of sequences |
| 5 | =[A2,A3].merge@ou() |
Union by whole row comparison |
| 6 | =[A2,A3].merge@ou(_id, NAME) |
Union by key value comparison |
| 7 | =[A2,A3].merge@oi() |
Intersection by whole row comparison |
| 8 | =[A2,A3].merge@oi(_id, NAME) |
Intersection by key value comparison |
| 9 | =[A2,A3].merge@od() |
Difference by whole row comparison |
| 10 | =[A2,A3].merge@od(_id, NAME) |
Difference by key value comparison |
| 11 | >A1.close() |
Get sequence number of a member in a sequence in SPL:
| A | B | |
|---|---|---|
| 1 | =mongo_open("mongodb://localhost:27017/local") |
|
| 2 | =mongo_shell(A1,"users.find({name:'jim'},{name:1,friends:1,_id:0})").fetch() |
|
| 3 | =A2.friends.pos("luke") |
Get sequence numbers of members in sequence friends |
| 4 | =A1.close() |
Perform intersection of multi-member collections in SPL:
| A | B | |
|---|---|---|
| 1 | [Chemical, Biology, Math] | Courses |
| 2 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") |
|
| 3 | =mongo_shell(A2,"student.find()").fetch() |
Fetch student data |
| 4 | =A3.select(Lesson^A1!=[]) |
Get documents where at least one course is selected |
| 5 | =A4.new(_id, Name, ~.Lesson^A1:Lession) |
Get the final result |
| 6 | >A2.close() |
Complex queries Getting TopN in SPL:
| A | B | |
|---|---|---|
| 1 | =mongo_open("mongodb://127.0.0.1:27017/test") |
|
| 2 | =mongo_shell(A1,"last3.find(,{_id:0};{variable:1})").fetch() |
Get last3 data and sort it by variable |
| 3 | for A2;variable =A3.top(3;-timestamp) |
Get the three documents having the latest timestamps |
| 4 | `=@ | B3` |
| 5 | =B4.minp(~.timestamp) |
Get documents with the earliest timestamp |
| 6 | >mongo_close(A1) |
Summarize a nested-structure collection in SPL:
| A | |
|---|---|
| 1 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") |
| 2 | =mongo_shell(A1,"computer.find()").fetch() |
| 3 | =A2.new(_id:ID,income.array().sum():INCOME,output.array().sum():OUTPUT) |
| 4 | >A1.close() |
Combine subdocuments made up of multiple attributes in SPL:
| A | B | C | |
|---|---|---|---|
| 1 | =mongo_open("mongodb://localhost:27017/local") |
||
| 2 | =mongo_shell(A1,"c1.find(,{_id:0};{name:1})").fetch() |
||
| 3 | =create(_id, readUsers) |
Create result table sequence | |
| 4 | `for A2;name =A4.conj(acls.read.users | acls.append.users | acls.edit.users |
| 5 | {% raw %}`=@ | A3.insert(0, A4.name, B4)` | |
| 6 | =A1.close() |
Query nested List subdocument in SPL:
| A | B | |
|---|---|---|
| 1 | =mongo_open("mongodb://localhost:27017/local") |
|
| 2 | =mongo_shell(A1,"Cbettwen.find(,{_id:0})").fetch() |
|
| 3 | =A2.conj((t=~.objList.data.dataList,t.select((s=float(~.split@c1()(1)), s>6154 && s<=6155)))) |
Get eligible strings |
| 4 | =A1.close() |
SPL Cross-sector aggregation:
| A | |
|---|---|
| 1 | =mongo_open("mongodb://localhost:27017/local") |
| 2 | =mongo_shell(A1,"student.find()").fetch() |
| 3 | =A2.group(school) |
| 4 | =A3.new(school:school,~.align@a(5,sub1).(~.len()):sub1,~.align@a(5,sub2).(~.len()):sub2) |
| 5 | =A4.new(school,sub1(5):sub1-5,sub1(4):sub1-4,sub1(3):sub1-3,sub1(2):sub1-2,sub1(1):sub1-1,sub2(5):sub2-5,sub2(4):sub2-4,sub2(3):sub2-3,sub2(2):sub2-2,sub2(1):sub2-1) |
| 6 | =A1.close() |
SPL segment-based grouping:
| A | B | |
|---|---|---|
| 1 | [3000,5000,7500,10000,15000] | Intervals of Sales for segmentation |
| 2 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") |
|
| 3 | =mongo_shell(A2,"sales.find()").fetch() |
|
| 4 | =A3.groups(A1.pseg(~.SALES):Segment;count(1): number) |
Group data and count employees by SALES intervals |
| 5 | >A2.close() |
SPL class-based grouping:
| A | B | |
|---|---|---|
| 1 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") |
|
| 2 | =mongo_shell(A1,"books.find()") |
|
| 3 | =A2.groups(addr,book;count(book):Count) |
Grouping & count |
| 4 | =A3.groups(addr;sum(Count):Total) |
Grouping & sum |
| 5 | =A3.join(addr,A4:addr,Total) |
Join operation |
| 6 | >A1.close() |
Data writing Export data as CSV in SPL:
| A | B | |
|---|---|---|
| 1 | =mongo_open("mongodb://localhost:27017/raqdb") |
|
| 2 | =mongo_shell(A1,"carInfo.find(,{_id:0})") |
|
| 3 | =A2.conj((t=~,cars.car.new(t.id:id,t.cars.name, ~:car))) |
Split each car field value into multiple rows |
| 4 | =file("D:\\data.csv").export@tc(A3) |
Export as CSV |
| 5 | >A1.close() |
SPL database update (from MongoDB to MySQL):
| A | B | |
|---|---|---|
| 1 | =mongo_open("mongodb://localhost:27017/raqdb") |
Connect to MongoDB |
| 2 | =mongo_shell(A1,"course.find(,{_id:0})").fetch() |
|
| 3 | =connect("myDB1") |
Connect to MySQL |
| 4 | =A3.query@x("select * from course2").keys(Sno, Cno) |
|
| 5 | >A3.update(A2:A4,course2,Sno,Cno, Grade; Sno,Cno) |
Update data into MySQL |
| 6 | >A1.close() |
SPL database update (from MySQL to MongoDB):
| A | B | |
|---|---|---|
| 1 | =connect("mysql") |
Connect to MySQL |
| 2 | =A1.query@x("select * from course2") |
Get data of course2 table |
| 3 | =mongo_open("mongodb://localhost:27017/raqdb") |
Connect to MongoDB |
| 4 | =mongo_insert(A3, "course",A2) |
Insert records of MySQL table course2 into the MongoDB collection |
| 5 | >A3.close() |
Mixed computations
SPL enables convenient mixed computation between MongoDB and another data source:
| A | B | |
|---|---|---|
| 1 | =mongo_open("mongodb://localhost:27017/test") |
Connect to MongoDB |
| 2 | =mongo_shell(A1,"emp.find({'$and':[{'Birthday':{'$gte':'"+string(begin)+"'}},{'Birthday':{'$lte':'"+string(end)+"'}}]},{_id:0})").fetch() |
Get records within a specified time interval |
| 3 | =A1.close() |
Close MongoDB connection |
| 4 | =myDB1.query("select * from cities") |
Get data of cities table in MySQL |
| 5 | =A2.switch(CityID,A4:CityID) |
Foreign-key-based join |
| 6 | =A5.new(EID,Dept,CityID.CityName:CityName,Name,Gender) |
Create result set |
| 7 | return A6 |
Return the result set |
SQL support
Besides the native syntax, SPL offers support of SQL92 standard. You can use SQL to query MongoDB. To achieve the above join operation, for instance:
| A | |
|---|---|
| 1 | =mongo_open("mongodb://127.0.0.1:27017/test") |
| 2 | =mongo_shell(A1,"c1.find()").fetch() |
| 3 | =mongo_shell@x(A1,"c2.find()").fetch() |
| 4 | $select s.* from {A2} as s left join {A3} as r on s.user1=r.user1 and s.user2=r.user2 where r.income>0.3 |
Integration into application
SPL provides standard JDBC/ODBC drivers through which SPL can be conveniently integrated into an application or invoked by it. To invoke SPL code through JDBC, for instance:
…
Class.forName("com.esproc.jdbc.InternalDriver");
Connection conn = DriverManager.getConnection("jdbc:esproc:local://");
PrepareStatement st=con.prepareStatement("call splScript(?)"); // splScript is the name of SPL script file
st.setObject(1,"California");
st.execute();
ResultSet rs = st.getResultSet();
…
With all those functionalities, you’ll sure to be impressed by MongoDB’s strikingly boosted computing ability. Try your hand now.
Check GitHub Repo
Don't forget to give us a star ⭐ on the repo
Posted on November 14, 2023
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.