SPL Practice: Structured Text computing
Structured Text Computing Requirements
Structured text files are a common data storage method, such as this score.txt file, which records the scores of students in all classes. The first row is the column name, and each subsequent row is the data of a student, separated by tab within each row.
This structured text also has various computational and processing requirements. For example, selecting the girls with the English score of 90 or above, to calculate the total score of each student and sort them in descending order, to calculate the average Chinese language score for each class.
Importing into a database and using SQL to calculate is certainly not a problem, but the process is complicated and there may not always be a database available. Writing Java code is also very tedious, but using SPL is very simple.
Read structured text file
First, let's see how to load the file. The import() function of A1 can directly read the structured text file into an in-memory data table. The @t option represents the first row is field name. For comma separated CSV files, the option @c can be used to read in, and @tc represents reading the CSV text with the first row being column names.
Read score.txt and score.csv respectively in A1/A2. A3/A4 can also read score.txt and score.csv using the T() function, which can automatically load based on the file extension name.
There are other situations when reading text data, such as not having a title row, only reading partial columns, custom separators, and text with quotation marks and carriage returns. For details on these functions, please refer to the official documentation.
A |
|
1 |
=file("score.txt").import@t() |
2 |
=file("score.csv").import@tc() |
3 |
=T("score.txt") |
4 |
=T("score.csv") |
Try filtering
Let's first try the filtering operation, for example, to select girls who score 90 or above in English. A1 uses the T()function to load text data into an in-memory data table, and A2 uses the select() function to filter the data, it can be seen that only girls with scores equal to or above 90 remain in the result. The dot operator in A3 can combine loading data and filtering operation into one statement, with the same calculation result.
If this file is regarded as a table, the corresponding SQL statement for the operation is as follows:
SELECT * FROM score
WHERE English>=90 AND Gender=2
Sometimes it is desired to dynamically pass in the values of the filtering criteria. The default values can be predefined as 80 points and male students, which can be modified during runtime. The select function in A4 can use these two parameter variables.
In A5, use the export()function to write the result table sequence of A2 back into a text file named 'filter. txt'. Similar to the import() function, the @t option indicates that the first row will be written as a title.
A |
|
1 |
=T("score.txt") |
2 |
=A1.select(English>=90 && Gender==2) |
3 |
=T("score.txt").select(English>=90 && Gender==2) |
4 |
=T("score.txt").select(English>=argEng && Gender==argGen) |
5 |
=file("filter.txt").export@t(A2) |
Then try aggregation
Then try some aggregation operations. Calculate the highest and average scores for both girls and boys separately, which is consistent with the SQL aggregation functions.
A |
|
1 |
=T("score.txt") |
2 |
=A1.select(English>=90 && Gender==2) |
3 |
=T("score.txt").select(English>=90 && Gender==2) |
4 |
=T("score.txt").select(English>=argEng && Gender==argGen) |
5 |
=file("filter.txt").export@t(A2) |
SQL:
SELECT max(Math), avg(Math)
FROM score WHERE English>=90 AND Gender=2
SELECT max(Math), avg(Math)
FROM score WHERE English>=90 AND Gender=1
Integrate into Java applications
After the initial calculation attempts, let's take a look at how to integrate these codes into Java applications. SPL offers standard JDBC, which can provide data querying and computing capabilities to Java applications like executing SQL or calling stored procedures.
[JDBCConection].prepareStatement(SPL statement)
[JDBCConection].prepareCall(SPL script)
Import the jar files from the installation directory \ esProc \ lib into the application:
esproc-bin-xxxx.jar
icu4j-60.3.jar
Then copy raqsoftConfig. xml from the installation directory \ esProc \ config to the application's class path. raqsoftConfig. xml is the core configuration file of SPL, and its name cannot be changed.
JDBC executes SPL code
The driver class is InternalDriver, the connection URL is jdbc: esproc: local.
The created JDBC connection can execute SPL code like an SQL query. Load score.txt here to filter students with high scoring English. Use JDBC parameters to dynamically set the minimum score, with 90 set here. Run this Java program and, like other regular JDBC uses, return the result table sequence as a ResultSet object.
Class.forName("com.esproc.jdbc.InternalDriver");
Connection con= DriverManager.getConnection("jdbc:esproc:local://");
PreparedStatement ps = con.prepareStatement("=T(\"score.txt\").select(English >= ? )");
ps.setInt(1, 90);
ResultSet rs = ps.executeQuery();
JDBC calls SPL script
Save the SPL program as a .splx script file, here saved as scoreCalc.splx:
A |
|
1 |
=T("score.txt").select(English>=90) |
2 |
return A1 |
Then put the script file into the main directory of esProc, which is configured as the root directory of D drive in raqsoftConfig.xml.
Then you can call these scripts like using JDBC to call stored procedures:
PreparedStatement ps = con.prepareCall("call scoreCalc()");
ps.execute();
ResultSet rs = ps.getResultSet();
Executing call scoreCalc() calls and executes scoreCalc.splx. Return the result set to ResultSet.
JDBC executes SPL and returns single or multiple values
In addition to returning a table sequence of multiple rows and columns to the JDBC ResultSet object, it can also return a single value of one row and one column, or a sequence of multiple values of multiple rows and one column.
A |
|
1 |
=T("score.txt").sum(Math) |
2 |
return A1 |
A single value with 1 row and 1 column, with the column name fixed as "Field". For example, A2 returns the total math score of 70360.
A |
|
1 |
=T("score.txt").sum(Math) |
2 |
=T("score.txt").count(Math) |
3 |
=T("score.txt").avg(Math) |
4 |
=T("score.txt").min(Math) |
5 |
return [A1, A2, A3, A4] |
Multiple values with multi rows and 1 column, with the column name still being "Field", such as A5 returning the total score, number of people, average score, and lowest score in mathematics.
Using variable
Next, let's continue to look at more computing tasks and implement common SQL operations using SPL.
For the convenience of discussion, we assume that all A1 cells of the code are to load the file, and then assign the table sequence to the score variable. Afterwards, there is no need to write A1 anymore, only calculate on score.
In addition, for simplicity, SPL code no longer uses parameters, and you can modify it to have parameters by yourself.
A |
|
1 |
>score=T("score.txt") |
2 |
=score.select(English>=90 && Gender==2) |
3 |
…… |
Calculate columns
Calculate a new field using multiple field values of the same row, for example, calculate the total score and average score of each student's three subjects.
SPL uses the new() function to define calculated columns, and the expression in the calculated column can use the existing calculated columns, such as calculating total first and then using total to calculate avg.
A |
|
2 |
=score .new( Gender, Name ,(English+Chinese+Math):total ,round(total/3, 2):avg ) |
In the new() function, some original columns can also be selected and mixed with new computed columns or adjust the order of the columns. Here, the Gender is adjusted before the Name.
SQL:
SELECT
Gender, Name
,(English+Chinese+Math) as total
,(English+Chinese+Math)/3 as avg
FROM score
Add calculated columns
If you want to add new calculated columns directly to the existing table sequence, you can use the derive() function. Add the total and average scores of the three subjects here.
A |
|
2 |
=score .derive( (English+Chinese+Math):total ,round(total/3, 2):avg ) |
SQL:
SELECT
*
,(English+Chinese+Math) as total
,(English+Chinese+Math)/3 as avg
FROM score
Sort
Sort all students in descending order of total score, and for students with equal total scores, sort them in ascending order of math scores. SPL uses the sort() function to implement sorting, with a negative sign before the sorting field 'total' indicating descending order and 'Math' indicating ascending order.
A |
|
2 |
=score.derive( (English+Chinese+Math):total ).sort(-total, Math) |
In the results, it is observed that there are multiple students with a total score of 281, with students with lower math scores ranking higher.
Fields of string and date types also support sorting. For example, to sort in reverse order by Name will compare the characters one by one from the first character to the next.
SQL:
SELECT
(English+Chinese+Math) as total
,*
FROM score
ORDER BY
(English+Chinese+Math) DESC
,Math DESC
Group and aggregation
We want to group by class and gender, and calculate the number of people and average English scores. SPL uses the groups() function to implement grouping and aggregation. Define the grouping fields Class and Gender before the semicolon in parameter. When there are multiple grouping fields, separate them with commas; Define the aggregation expression after the semicolon, supporting aggregations such as sum, count, avg, min, max, etc.
A |
|
2 |
=score.groups( Class , Gender ; count(Name):cnt , round(avg(English),2):engAvg ) |
SQL:
SELECT
Class,Gender
, COUNT (Name) AS cnt
, ROUND(AVG (English),2) AS engAvg
FROM score
GROUP BY Class,Gender
Filter after grouping and aggregation
Before grouping, use the select() function to filter students who score 60 or above in mathematics;
After grouping and aggregation, use the select() function to filter groups with an average English score exceeding 70. SPL is defined and calculated step by step, and intermediate steps can be filtered at any time without using WHERE/HAVING to distinguish whether it occurred after the grouping.
A |
|
2 |
=score.select(Math>60) .groups( Class,Gender ;count(Name):cnt, round(avg(English),2):engAvg ).select(engAvg > 70) |
SQL:
SELECT
Class, Gender, COUNT (Name) AS cnt
, ROUND(AVG (English),2) AS engAvg
FROM score
WHERE Math>60
GROUP BY Class,Gender
HAVING ROUND(AVG (English),2) > 70
Remove duplicate values
In SPL, using the id() function can remove duplicate values. After removing duplicate names for one thousand students, we see 598 names.
A |
|
2 |
=score.id(Name) |
SQL:
SELECT DISTINCT Name FROM score
Multiple columns can also be combined to remove duplicates, which means that the values of multiple columns are considered duplicate only when they are all the same. For example, if a thousand students combine Name and Math to remove duplicates, there will be 992 left.
A |
|
2 |
=score.id([Name,Math] ) |
SQL:
SELECT DISTINCT Name,Math FROM score
Count distinct
In SPL, the icount () function is used to implement count distinct, resulting in 598 names after deduplication among 1000 students.
A |
|
2 |
=score.icount(Name) |
SQL:
SELECT COUNT(DISTINCT Name) Name FROM score
icount also supports multi column count distinct, with 992 remaining after deduplication for names and math scores that are all the same.
A |
|
2 |
=score.icount([Name,Math] ) |
SQL:
SELECT
COUNT(*)
FROM (
SELECT DISTINCT Name, Math FROM score
)
Count distinct within groups after grouping
The icount() function can also be used within the groups function, allowing for count distinct within the groups. There are 54 names for 56 people in Class 2 and 53 names for 55 people in Class 3.
A |
|
2 |
=score.groups( Class ; icount(Name):nameCnt , count(Name):cnt ) |
SQL:
SELECT
Class
, COUNT(distinct Name) AS nameCnt
, COUNT(Name) AS cnt
FROM score
GROUP BY Class
TOP-N
It is also common to calculate the top few records, and the top() function gets the top N records.The first parameter is a negative number, indicating reverse order. -5 will get the top 5 records with the highest total. If it is a positive number of 5, it will get the bottom 5 records with the lowest total.
When getting the top N, duplicate values will occupy the ranking by default, such as the 4th and 5th both scoring 281.
A |
|
2 |
=score.derive((English+Chinese+Math):total) .top(-5;total) |
SQL:
SELECT
TOP 5
*, (English+Chinese+Math) as total
FROM score
ORDER BY (English+Chinese+Math) DESC
If you want duplicate values not to occupy the ranking, you can use the @i option. Among the top 5, if the 4th has duplicate values and there is a total of 3 people, then a total of 7 people will be found in the top 5.
***.top@i(-5;total)
To find the maximum and minimum records can also use the maxp and minp functions, which are equivalent to top 1 and top -1, respectively.
Max ***.maxp(total) = top(1,total) :
Min ***.minp(total) = top(-1,total)
TOP-N within groups
To find out the top 5 students in each class, you can use the top function in the group() function,
A |
|
2 |
=score.derive( (English+Chinese+Math):total ).group(Class;~.top(-5;total)) |
The top 5 students from each class as shown in the figure are a series of subsets. Clicking on Class 3 will reveal the top 5 students from Class 3.
The groups()and group() functions are both used for grouping. Simply put, the former calculates some aggregation values directly after grouping, while the latter obtains subsets of each group after grouping. The subsets can be flexibly defined for more calculations and may not always be aggregated.
SQL:
SELECT
* , (English+Chinese+Math) AS total ,
RANK OVER(
PARTITION BY Class ORDER BY
English+Chinese+Math DESC
) AS rank
FROM score WHERE rank <= 5
More calculation functions:Date calculation
SPL provides many related calculation functions for different types of data.
Here listed some calculations based on the send and receive date fields, to obtain the year, month, day, and day of the week, to calculate how many days and weeks there is a difference between two dates, and to calculate the new date after a date.
Based on send (Sending Time) :2023-12-03 15:12:23,receive(Receiving Time):2024-03-15 11:12:30:
year(send) = 2023
month(send) = 12
day(send) = 3
day@w(send) = 1 , Sunday
interval(send, receive) = 103,103 days in between
interval@w(send, receive) = 14,14 weeks in between
elapse(send,50) = 2024-01-22 15:12:23,50 days after
……
More calculation functions:String calculation
There are also many calculation functions for string type data. Multiple strings can be concatenated together using concat, to determine if all characters are lowercase letters, to convert to lowercase, to truncate left, right, or middle substrings, to match strings with wildcard characters, to replace strings, and to randomly generate new strings of specific lengths using certain characters.
Based on first(First Name):John,last(Last Name):Smith to calculate :
concat(first,last) = JohnSmith
concat(first, " ", last) = John Smith
islower(last) = false
lower(last) = smith
left(last,3) = Smi
mid(last,2,4) = mith
like(last, "*th") = true
replace(last, "th", "??") = Smi??
rands(“abcdefg12345“,5) = 4d32d
……
More calculation functions:Convert to date type
SPL provides conversion functions between date and time, various numerical values, and string types. The target type is used as the function name, and the function automatically determines the original data type and correctly converts it to the target type. For example, the date() function can pass in integers such as year, month, day, string date, and can also specify the parsing format of the string date, ultimately obtaining a date.
date(1982 , 8 , 9) = 1982-08-09
date("1982-08-09") = 1982-08-09
date("1982-08-09 12:15:30") = 1982-08-09
date("08/09/1982" , "MM/dd/yyyy") = 1982-08-09
date("9, Aug 1982" , "d MMM yyyy" : "en") = 1982-08-09
datetime("1982-08-09 13:15:30") = 1982-08-09 13:15:30
datetime("9 Aug 1982 1:15 PM" ,
"d MMM yyyy h:mm a" : "en") = 1982-08-09 13:15:00
time("13:15:30") = 13:15:30
time("13/15/30” ,"HH/mm/ss") = 13:15:30
time("1:15 PM", "h:mm a" : "en") = 13:15:00
……
More calculation functions:Convert to numerical type
There are many numerical types, including integer int, long integer, double precision floating-point number float, real number, and large floating-point number decimal. In addition to converting between various numerical types, string and date types can also be converted to numerical values.
int("68") = 68
int("68.73") = 68
int(68.73) = 68
int(3.1*3.2) = 9
float("68.73") = 68.73
float(68) = 68.0
number("68f") = 68.0
number("$1,100.05", "$#,###.##") = 1100.05
long("68.73") = 68
long(now()) = 1727677043202
decimal(1232456523427854423905234234)
……
More calculation functions:Convert to string type
When converting date, time, and numerical values into strings, the format can also be specified.
string(123) = "123"
string(now(), "MMM dd, yyyy") = "Sep 30, 2024"
string(1100.05 , "$#,##0.00") = "$1,100.05"
string(datetime("1982-08-09 13:15:30"), "d MMM yyyy h:mm a" : "en") = "9 Aug 1982 1:15 PM“
…….
There are more calculation functions available, which can be viewed in detail in the official online documentation https://doc.scudata.com.
Types of associations
Let's take a look at operations involving multiple file associations.
SPL regards associations differently from SQL, as it distinguishes between foreign key associations and primary key associations.
For example, if the City foreign key in the student table is linked to the primary key CityID in the city table, this is a foreign key association. The ordinary fields Class and School in the student table are respectively linked to the primary keys ClassID and SchoolID in the class table. This two field and multi field association is also a foreign key association.
The student table is associated with the primary key StudentID of the student health table using the primary key, which is called primary key association. These two tables have a one-to-one relationship, also known as a homo-dimensional table.
The student table is associated with the student payment table's partial primary key StudentID using the primary key, which also belongs to primary key association. These two tables have a one to many relationship, also known as the primary sub tables.
Based on different association characteristics, SPL uses different functions to distinguish and implement associations.
Association operations involve multiple files. Similarly, in A1, multiple texts will be loaded as SPL table sequence variables to facilitate subsequent calculations.
A |
|
1 |
>student=T("student.txt"), city=T("city.txt"), class=T("class.txt"), stu_pay=T("stu_pay.txt"), stu_health=T("stu_health.txt") |
Foreign key association calculation
There are class teacher ID, English teacher ID, and other information in class.txt, while teacher.txt records the teacher's name, gender, and subject being taught. We need to find out the classes where the class teacher is a Chinese teacher, and it is necessary to associate the ClassTeacher in class.txt with the teacherId in teacher.txt.
SPL uses the switch()method to join two tables. During the calculation process of A2, four table sequences are involved, with class.txt loaded as the table sequence class and teacher.txt loaded as the table sequence teacher, then use the switch() function to replace ClassTeacher field value of the class with the relevant record of the teacher, and calculate the CT table sequence. The field values of the SPL table sequence can be complex data types such as records or even sub table sequences. CT is a multi-level nested table sequence.
It is not difficult to obtain data from nested sub records. For example, when CT2 with a simple structure is generated by CT new(), ClassTeacher.Subject obtains the subject taught by the class teacher, and finally uses the select() function to find the Chinese subject.
A |
|
2 |
>CT=class.switch( ClassTeacher ,teacher:teacherId ) ,CT2=CT.new( Class , ClassTeacher.Name , ClassTeacher.Subject ).select(Subject=="Chinese") |
SQL:
SELECT
C.Class ,T.Name ,T.Subject
FROM
class C JOIN teacher T
ON C.ClassTeacher=T.treachId
WHERE T.Subject="Chinese"
Multi level foreign key association
The FromCity field of the student table is foreign key to the city table, the ClassID field is foreign key to the class table, and the English, Chinese, and Mathematics teacher fields in the class table are all foreign key to the teacher table.
The student table has two foreign key fields, and the class table has three. There is also a multi-level association between the student table and the class table, and between the class table and the teacher table.
To query students from City4 and whose teacher is Teacher8, four tables need to be associated together. The switch() function supports associating multiple foreign key tables simultaneously, for example, in A2, first associate the class table with the three subject teachers, and then associate the city table and A2's class table in A3, and the result of A3 association is a more complex multi-level table sequence, which can visually display the association results.
Finally, in A4, use the new() function to extract the required information from the table sequence at each level and filter out students from City4 who are taught by Teacher8.
A |
|
2 |
=class.switch( EnglishTeacher, teacher:TeacherID ; ChineseTeacher, teacher:TeacherID ; MathTeacher, teacher:TeacherID ) |
3 |
=student.switch( ClassID, A2 : ClassID ; FromCity, city : CityID ) |
4 |
=B2.new(StudentID, Name ,FromCity.Name : City ,ClassID.EnglishTeacher.Name : EnTeacher ,ClassID.ChineseTeacher.Name : CnTeacher,ClassID.MathTeacher.Name : MaTeacher ).select(City=="City4" && (EnTeacher=="Teacher8" || CnTeacher=="Teacher8" || MaTeacher=="Teacher8")) |
SQL:
SELECT
s.StudentID, s.Name, c.Name AS city, en.Name AS EnTeacher,
cn.Name AS CnTeacher, ma.Name AS MaTeacher
FROM
student s JOIN city c JOIN class cl
JOIN teacher en JOIN teacher cn JOIN ma
ON
s.FromCity=c.CityID AND s.ClassID=cl.ClassID AND cl.EnglishTeacher=en.TeacherID
AND cl.ChineseTeacher=cn.TeacherID AND cl.MathTeacher=ma.TeacherID
WHERE
City="City4" AND
(EnTeacher="Teacher8" OR CnTeacher=“Teacher8” OR MaTeacher=“Teacher8”)
Multi field foreign key association
The switch()function is suitable for single field foreign key associations, and when multiple fields are associated, the join() function should be used.
Add a School column to the score table to record the scores of 30 classes in 5 schools, with 6 classes in each school. The class table records these 30 classes, with the primary keys being the Class and School fields. The classes are classified into Type 1, Type 2, and Type 3.
Now we need to find out the scores of all students in Type3 classes. In the parameters of the join() function, before the first comma are multiple foreign key fields, Class and School; Before the second comma is the associated table class, along with its multiple primary key fields Class and School; After that are the target field selected from the associated table, which can be multiple, and we only selected one ClassType field here.
The switch() function is for single field association that supports multiple tables, resulting in nested multi-level table sequences.
The join() function can support multi field association, but not multi table. The result is to directly join the fields of two tables to form a single-layer structured table sequence.
A |
|
2 |
>class=class.select(ClassType==3) |
3 |
>CT=score.join( Class:School ,class:Class:School ,ClassType ) |
SQL:
SELECT
s.*, c.ClassType
FROM
score s JOIN class c
ON s.Class=c.Class
AND s.School=c.School
WHERE c.ClassType=3
Homo-dimensional table - primary key association
The primary keys of the student table, family table, and health table are all Student IDs, and the three tables have a one-to-one homo-dimensional relationship. The latter two tables provide supplementary information on different aspects of students.
The primary key association requires the use of a new association function, such as join (A, B, C), which defines information for multiple associated tables in the parameters: the associated table (student), the field name(stu) in the table sequence after association, and the associated primary key (StudentID).
Observing the association result of A2, the three table records with Student ID 2 are associated together.
After association in A2, A3 filters out students and parents with myopia degree exceeding 500 degrees.
A |
|
2 |
=join( student : stu , StudentID; family : stu_family , StudentID; health : stu_health , StudentID ) |
3 |
=A2.new(stu.Name, stu_family.Father , stu_family.Mother, stu_health.EyeVision) .select(EyeVision>500) |
SQL:
SELECT
stu.Name, stu_family.Father
, stu_family.Mother, stu_health.EyeVision
FROM
student stu
JOIN family stu_family
JOIN health stu_health
ON stu.StudentID=stu_family.StudentID
AND stu.StudentID=stu_family.StudentID
WHERE sty_health.EyeVision>500
Primary sub table - primary key association
Another type of primary key association is the primary sub table, where the student payment table 'pay' is a sub table. It has two primary keys, Student ID and PayDate, and uses the Student ID as a foreign key to the primary table’s ‘student‘ primary key.
The primary sub table also use the join() function to associate. A student has 5 payment records, and each payment record is associated with the student record in the primary table.
After association in A2, A3 uses the groups() function to calculate the total payment amount for each student.
A |
|
2 |
=join( student : stu,StudentID; family : stu_family,StudentID; pay : stu_pay,StudentID ) |
3 |
=A2.groups( stu.Name, stu_family.Father; sum(stu_pay.PayMoney):allPay ) |
SQL:
SELECT
stu.Name, stu_family.Father,
sum(stu_pay.PayMoney):allPay
FROM
student stu
JOIN family stu_family
JOIN pay stu_pay
ON stu.StudentID=stu_family.StudentID
AND stu.StudentID=stu_pay.StudentID
GROUP BY stu.Name, stu_family.Father
And more…
The above is the content of structured text calculation in SPL, which provides a simple and powerful calculation solution for data outside of databases.
The examples involved here are all small data that can be read into memory. For files larger than memory, SPL also provides a cursor solution for calculation. Interested friends can refer to SPL's official documentation.
Of course, SPL's capabilities go far beyond that. SPL also supports various text formats such as JSON and XML, cross database queries, and supports mixed computing between databases and other non databases, supports for big data or complex computing, and SPL's computing power can also be used to optimize SQL performance. We will gradually introduce these contents in later topics.
SPL is an open source software that can be obtained from GitHub. https://github.com/SPLWare/esProc
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
Chinese Version