1.2 Composite table
Composite tables support column-wise storage that enables high performance during traversal.
1.2.1 Dump data to composite table
Dump data from a text file:
A | |
---|---|
1 | =file(“Orders.txt”).cursor@mt(CustomerID:string, OrderDate:datetime, ProductID:string, Quantity:int, Unit:string, Price:decimal, Amount:decimal, EmployeeID:int,EmployeeName:string,ShipVia:string) |
2 | =file(“Orders.ctx”).create@y(CustomerID,OrderDate,ProductID, Quantity, Unit, Price, Amount, EmployeeID,EmployeeName,ShipVia) |
3 | =A2.append(A1) |
4 | =A2.close() |
A2 When creating a composite table with the create function, write data structure explicitly (order of fields should be consistent with that in A1). @y option enables overwriting the file even when the file already exists; and error appears if the option is omitted.
A3 Append data in A1’s cursor to the composite table (the composite table receives data only coming from the cursor).
Dump data from the database:
A | |
---|---|
1 | =connect(“sqlserver2012”) |
2 | =A1.cursor@x(“select CustomerID,OrderDate,ProductID, Quantity, Unit, Price, Amount, EmployeeID,EmployeeName,ShipVia from Orders”) |
3 | =file(“Orders.ctx”).create@y(CustomerID,OrderDate,ProductID, Quantity, Unit, Price, Amount, EmployeeID,EmployeeName,ShipVia) |
4 | =A3.append(A2) |
5 | =A3.close() |
A3 Keep same order of fields as A2.
1.2.2 Filtering, and grouping & aggregation
SQL
SELECT *
FROM Orders
WHERE Amount>1000
SPL
A | |
---|---|
1 | =file(“Orders.ctx”).open().cursor(;Amount>1000) |
2 | =A1.fetch() |
A1 Writing filtering condition in the cursor()function means that records are first judged according to the filtering condition before they are or aren’t added to the result set. Ineligible records will be directly skipped. cursor().select(…), however, first generates the result set with all records, including the ineligible ones, and then performs filtering.
SQL
SELECT EmployeeID,sum(Amount) AS Amount
FROM Orders
WHERE OrderDate>='2022-01-01'
GROUP BY EmployeeID
ORDER BY EmployeeID
SPL
A | |
---|---|
1 | =file(“Orders.ctx”).open().cursor@m(EmployeeID,Amount;OrderDate>=date(“2022-01-01”,“yyyy-MM-dd”)) |
2 | =A1.groups(EmployeeID;sum(Amount):Amount) |
A1 Importing only the necessary fields shortens the time of scanning data and creating objects. @m option enables importing data in parallel.
SQL
SELECT sum(Amount) AS Amount, sum(Quantity) AS Quantity,
count(1) AS num
FROM Orders
WHERE OrderDate>='2022-01-01'
SPL
Return a table sequence:
A | |
---|---|
1 | =file(“Orders.ctx”).open().cursor@m(Amount,Quantity;OrderDate>=date(“2022-01-01”,“yyyy-MM-dd”)) |
2 | =A1.groups(;sum(Amount):Amount, sum(Quantity): Quantity, count(1):num) |
Return a sequence:
A | |
---|---|
2 | =A1.total(sum(Amount), sum(Quantity), count(1)) |
SQL
SELECT EmployeeID,count(1) num
FROM Orders
WHERE Amount>1000
GROUP BY EmployeeID
SPL
A | |
---|---|
1 | =file(“Orders.ctx”).open().cursor@m(EmployeeID;Amount>1000) |
2 | =A1.groups(EmployeeID;count(1):num) |
SQL
SELECT EmployeeID,sum(Amount) AS Amount
FROM Orders
WHERE OrderDate>='2022-01-01'
GROUP BY EmployeeID
HAVING sum(Amount)>2100000
SPL
A | |
---|---|
1 | =file(“Orders.ctx”).open().cursor@m(EmployeeID,Amount;OrderDate>=date(“2022-01-01”,“yyyy-MM-dd”)) |
2 | =A1.groups(EmployeeID;sum(Amount):Amount).select(Amount>250000) |