Category: 5 SPL Learning Material

Ⅰ Prepare data We use 1GB TPC-H data to show how to make DQL queries. Below are eight data files (*.tbl) generated by TPC-H: The file content is of text format. The first row contains field names and rows after it are detail data; and each row of data is separated by the vertical bar |. As shown in the following part table: The data table queried in DQL should be stored in SPL composite table file format (*.ctx). We can use the following SPL script to convert text files to a composite table file: A B C 1 E:\TPCH\

esProc Elastic Compute Service (ECS) is a general computing software running on enterprise-class LAN and proprietary cloud. It has three components. The service-side consists of QVA and QVM; the application-side is made up of the esProc ECS application (hereinafter called APP) and QVS; the storage-side is NFS, HDFS or an object storage system compatible with S3 protocol. Both the service-side and the application-side involve the SPL script. At the service-side, the SPL script is executed on QVM and it is also called QVM script. On the application-side, a SPL script is needed to call the QVM script and it is

There are generally not many and frequent updates on the target data of OLAP. Usually, the update actions happen when new data is appended or when data is inserted, modified and deleted. SPL offers the multizone composite table that can effectively shorten the time of handling data updating while ensuring the computing performance. A multizone composite table is made up of multiple composite table files. We call these composite tables the multizone composite table’s zone tables. Each zone table has its own zone table number. 1. Append-type multizone composite tables In order to increase performance, SPL needs to store data

1. Introduction AWS Lambda provides convenient to use functions service, which allows running code without presetting or administering the server and which enables calling functions directly from any Web or mobile application to get expected result. We can read business data, perform complex computations and output the result to the caller in the function’s code. Data processing and computation, however, is complicated, involving heavy workload and time-consuming programming and debugging tasks. SPL is an excellent tool for performing data computations. It allows connecting to various types of data sources, including different databases and different formats of data files, offers an

esProc can not only prepare and compute data, but also call the external library ReportLite to generate reports with complex format. Environment configuration Download and Install ReportLite Download ReportLite from the official website, decompress the zip file and install it directly. After installation, a trial license is provided, allowing you to use ReportLite directly. Configure external library Create a new directory ‘extlib\ReportLiteCli’ under the esProc installation directory\esProc\ (you can also put ReportLiteCli in another directory, as long as you can find it when setting in esProc tool). Copy the following jars from ReportLite installation directory\reportlite\lib to the newly created extlib\ReportLiteCli

In-memory column-wise computing What is columnar storage The table sequence in memory generally adopts the row-based storage. For example, the employee table contains three fields ‘id, name and birthday’, which are stored in memory roughly as follows: Each row (i.e., each record) is stored as an Object array, including three member objects: [Integer,String,Date]. In general, each column (field) contains the same type of data. Under this premise, SPL can store data by column. For example, if the data in the id column are all integers, they can be stored as an int array; if the data in the name column

What is a time key? While relatively stable, the data of dimension table may still change. For example, the city where a certain customer is located changed from New York to Chicago on May 15, 2020. When associating the order table with customer table, the order before this date should be associated with the old customer record (that is, the city should still be New York), while the order on and after this date should be associated with the new customer record (that is, the city should be Chicago). In other words, we need to find the correct customer record

“Association calculation in SPL – In-memory join” presents the classification of association calculations in SPL and the programming methods for in-memory join. “Association calculation in SPL – external storage join” presents the programming methods for external storage join. This article will continue to present new association calculation methods of SPL, including the fjoin function and composite table cursor association & filtering mechanism for foreign key join, as well as the pjoin and new/news functions for primary key join. When used in appropriate scenarios, these new methods can achieve better performance than those introduced in the previous two articles. However, the

The previous article “Association calculation in SPL – In-memory join” (In-memory join for short) presents the classification of association calculations in SPL and the programming methods for in-memory join. When one or more association tables have a large amount of data and need to be stored in external storage, the in-memory join algorithms cannot be used. For this reason, SPL specifically provides external storage join algorithms. When solving external storage join problems, there are similarities with in-memory join: 1. Clearly distinguish the type of join, and find the (logical) primary key participating in association; 2. Choose different SPL functions to

The association calculation in SPL differs significantly from that in SQL. SQL defines join as an operation that first calculates the Cartesian product and then filters. SPL also provides this operation, yet it has better alternatives in most scenarios, so this operation is not recommended. Programming in SPL to implement association calculation needs to subdivide join into different types first, and then select the corresponding function to code. Classification of association calculations The equivalence JOIN in the figure refers to the join whose filter condition is that the field of one table is equal to the corresponding field of associated