Category: 4 SPL Big Data Computing

Target task: We have a user events table T. Below is its structure and part of its data: Time UserID EventTypeID ProductID Quantity 2022/6/1 10:20 1072755 3 100001 2022/6/1 12:12 1078030 2 100002 2022/6/1 12:36 1005093 5 100003 3 2022/6/1 13:21 1048655 1 2022/6/1 14:46 1037824 6 2022/6/1 15:19 1049626 4 100004 4 2022/6/1 16:00 1009296 5 100005 6 2022/6/1 16:39 1070713 2 100006 2022/6/1 17:40 1090884 3 100007 Fields in table T: Field name Data type Description Time Datetime Time stamp of an event, accurate to milliseconds UserID String User ID EventTypeID Integer Event type ID ProductID String Product

Target task: We have a user events table T. Below is its structure and part of its data: Time UserID EventTypeID ProductID Quantity 2022/6/1 10:20 1072755 3 100001 2022/6/1 12:12 1078030 2 100002 2022/6/1 12:36 1005093 5 100003 3 2022/6/1 13:21 1048655 1 2022/6/1 14:46 1037824 6 2022/6/1 15:19 1049626 4 100004 4 2022/6/1 16:00 1009296 5 100005 6 2022/6/1 16:39 1070713 2 100006 2022/6/1 17:40 1090884 3 100007 Fields in table T: Field name Data type Description Time Datetime Time stamp of an event, accurate to milliseconds UserID String User ID EventTypeID Integer Event type ID ProductID String Product

Target task: We have a user events table T. Below is its structure and part of its data: Time UserID EventTypeID EventType Product Quantity Unit Price … 2022/6/1 10:20 1072755 3 Search Apple 5 Pound 5.5 … 2022/6/1 12:12 1078030 2 Browse Tissue 4 Packs 16 … 2022/6/1 12:36 1005093 5 Submit Beef 3 Pound 35 … 2022/6/1 13:21 1048655 1 Login … 2022/6/1 14:46 1037824 6 Logout … 2022/6/1 15:19 1049626 4 AddtoCart Wine 4 Bottles 120 … 2022/6/1 16:00 1009296 5 Submit Pork 6 Pound 25 … 2022/6/1 16:39 1070713 2 Browse Bread 3 Packs 10 … 2022/6/1

Target task: We have a user events table T. Below is its structure and part of its data: Time UserID EventTypeID EventType 2022/6/1 10:20 1072755 3 Search 2022/6/1 12:12 1078030 2 Browse 2022/6/1 12:36 1005093 5 Submit 2022/6/1 13:21 1048655 1 Login 2022/6/1 14:46 1037824 6 Logout 2022/6/1 15:19 1049626 4 AddtoCart 2022/6/1 16:00 1009296 5 Submit 2022/6/1 16:39 1070713 2 Browse 2022/6/1 17:40 1090884 3 Search Fields in table T: Field name Data type Description Time Datetime Time stamp of an event, accurate to milliseconds UserID Integer User ID EventTypeID Integer Event type ID EventType String Event type name

Target task: We have a user events table T. Below is its structure and part of its data: Time UserID EventTypeID EventType 2022/6/1 10:20 1072755 3 Search 2022/6/1 12:12 1078030 2 Browse 2022/6/1 12:36 1005093 5 Submit 2022/6/1 13:21 1048655 1 Login 2022/6/1 14:46 1037824 6 Logout 2022/6/1 15:19 1049626 4 AddtoCart 2022/6/1 16:00 1009296 5 Submit 2022/6/1 16:39 1070713 2 Browse 2022/6/1 17:40 1090884 3 Search Fields in table T: Field name Data type Description Time Datetime Time stamp of an event, accurate to milliseconds UserID Integer User ID EventTypeID Integer Event type ID EventType String Event type name

Target task We have a user events table T. Below is its structure and part of its data: Time UserID EventType 2022/6/1 10:20 1072755 Search 2022/6/1 12:12 1078030 Browse 2022/6/1 12:36 1005093 Submit 2022/6/1 13:21 1048655 Login 2022/6/1 14:46 1037824 Logout 2022/6/1 15:19 1049626 AddtoCart 2022/6/1 16:00 1009296 Submit 2022/6/1 16:39 1070713 Browse 2022/6/1 17:40 1090884 Search Fields in table T: Field name Data type Description Time Datetime Time stamp of an event, accurate to milliseconds UserID Integer User ID EventType String Types of events Computing task: Find the number of events under each type and that of distinct users

JOIN is a long-standing challenge in optimizing database performance. The more large tables in a JOIN operation, the worse the performance. The key of JOIN optimization is to properly classify the operations so that we can design and choose the right optimization method according to the characteristics of each category. SQL defines JOINs in one single way that is too general and simple to leave little room for performance optimization. This is why it is hard to speed up JOIN operations in relational databases. The open-source esProc SPL categorizes JOINs into more types and designs specific high-performance algorithms for each

HANA is a popular in-memory database and, theoretically, has the potential to replace Spark, but that it is not open-source keeps people away. SQLite, another in-memory database, is open-source, but it only supports embedded call, which puts great limits on data size and computing performance. Redis is open-source, high-performance, and supports processing of huge volumes of data, but it is extremely bad at doing computations and turns to a great deal of hardcoding for performing in-memory computations. The best Spark alternative is esProc SPL. SPL is an open-source, in-memory big data computing library that uses fewer memory resources, offers higher

Some databases intrinsically support cross-database computations, such as DBLink and Linked Server, but they are not open-source and complicated to configure. And on most occasions, data should be loaded to the local for computation, resulting in low performance. Among the open-source databases that support cross-database computations, Scala can make most use of databases’ computing abilities and offer excellent performance, but it is heavy-weighted, produces lengthy code, and is error-prone when data cannot fit into the memory. Both Calcite and Tablesaw are simple to configure and light-weighted, but they are underdeveloped and support too few functions. SPL, the Java-based open-source library

The same type of database components, besides DBLink, also includes Federated Database and Linked Server. Their configurations are complicated. And for most computations, data needs to be loaded to the local computer, which wastes the computing abilities of the remote database. Calcite is a Java class library that also supports cross-database computations. It is simple to configure and convenient to integrate, and is offered under an open-source license. Yet it is underdeveloped, without support of many functions. Scala, the language intended to compute big data, supplies abilities to perform cross-database computations, too. It is more mature and has rather good