Category: 5 SPL Learning Material

1. Data preparation and environment SPL script is used to generate the data file with two columns of data. The first column ID consists of random integers of less than 2 billion, and the second column amount is random real numbers of no more than 10 million. The data record is 8 billion lines, and the original text file size generated is 169 G. With the data importing tool provided by the database, the file data is imported into Oracle’s data table topn, and the SPL composite table file topn.ctx is also generated with the file data. The test is

As we all know, the bottleneck of big data computing performance often lies in external storage (i.e., hard disk) IO because the access performance of external storage is one or two orders of magnitude slower than that of memory. Therefore, it is sometimes more important to reduce the amount of hard disk access than to reduce the amount of CPU calculation when optimizing performance. If the algorithm with less hard disk access can be used, better performance will be obtained for the same task even with the same or slightly more CPU computation. Grouping and aggregation require traversing the dataset. The same

1 Preparing data We use TPC-H tool to generate the original data of text file format. There are altogether 8 tables, with a total size of 100G (See table sizes below). Then we generate SPL binary files for these text files. Each data table corresponds to a binary file. Acomposite table has index information that always requires at least a minimum space utilization, so it is not suitable for storing small data tables. That’s why we store small data tables in row-wise storage bin files, which helps decrease the size of data stored and enables loading all data at one

In a lot of grouping tasks in daily analytic work, the result set is required to be aligned to a specified base set. The more generalized alignment grouping is enumeration grouping. This article illustrates how to handle alignment grouping conveniently and efficiently and provides sample program in esProc. Looking Handling Alignment Grouping for details.

An aggregate operation summarizes data and returns the result. An aggregation is always preceded by a grouping operation. Common aggregate calculations include sum, max, min, count and logic operations. In this article, you’ll find illustrations of aggregate problems and their simple and efficient solutions with esProc scripts. Looking Non-basic Aggregation Examples for details.

Sometimes we need to judge whether a member belongs to a certain set or not when selecting data from a table, such as getting housing prices of major cities from house price table, getting VIP customers’ sales records from sales table, etc. This article illustrates how to deal with these membership issues fast and efficiently, and offers esProc sample scripts.

When retrieving data from a table, we sometimes need to check whether a member exists according to a specific condition. The condition could come from another data table. For instance, there is a score field in score table and we want to get records from student table where every subject gets a score above 80. So through a series of examples, let’s see how to handle such existence checking tasks conveniently and efficiently. The article provides esProc sample script for each example. Looking Existence Checking for Structured Data for details.

A “top N” query gets the first or bottom N values, the records, or their positions, from a data set. Sometimes we use such a query after a grouping operation to get values, specific information, or positions from a subset. This article will tell you how to handle different types of “top N” scenarios through examples and offers sample scripts in esProc SPL. Looking “Top N” Queries on Structured Data for details.

We can perform alignment operations on two ordered sets. Such as comparing the sizes of two sets, checking whether two sets are equal, etc. Read this article to find ways of handling these operations conveniently and efficiently and their esProc sample scripts.

Sometimes it’s a record’s position in an ordered sets that we are more concerned with in order to accomplish the computing goal. For instance, we want to find which row the order whose ID is 752084 settles in the orders table, or which trading date when SSE Composite Index has the highest closing price in the year of 2019. Here we have the efficient and fast ways to locate records and provide sample scripts in esProc SPL.