I. Move data out of database

When a TP database is overloaded, we can utilize SPL to undertake AP task, and the first thing to do is to move data out of the TP database. .

Connecting to relational database to retrieve data is usually implemented with JDBC. Take Oracle database as an example, and refer to TPC-H for data structure.

The script to move the ORDERS out of the database and store as SPL high-performance composite table is as follows:

Retrieving data through JDBC is slow, we can use multi-core parallel retrieving method to speed up. The data volume of LINEITEM is large; if we still used the above script to retrieve data, it would be time-consuming.

To address the problem, we can divide data into n parts based on the remainder [0,..,n-1] of dividing L_ORDERKEY by n, and then make each part of data correspond to a thread to move data and store as one bin file, and finally merge the data of n bin files as composite table. SPL script:

The n of forkn.() in A1 is 4, which represents the parallel number .

When the data volume in database is so large that the database is close to full load, executing ORDER BY still in SQL to sort data and then take them out may cause the database to get stuck during sorting. To avoid this, we can use SPL’s cursor to sort (cs.sortx). When executing, we can observe whether sortx generates temporary files continuously, based on which we can judge if the program is executed normally.

For incremental data, such as those added at a fixed time every day, we can retrieve them based on the timestamp field.

Assume the O_ORDERDATE of the ORDERS is a timestamp field and the O_ORDERKEY contains the incremental data, and the LINEITEM is associated with the primary key O_ORDERKEY of ORDERS through foreign key L_ORDERKEY, the script to obtain the incremental data and merge with the cold data is as follows:

The st in A2 is the maximum O_ORDERDATE in ORDERS composite table;

The now() function in A3 is the current time of system;

The maxkey in A4 is the maximum O_ORDERDATE in ORDERS composite table.

II. General cold data operations

Having moved and stored the data, we can, based on the composite table file of SPL, perform high-performance computations, including but not limited to the conditional filtering, grouping and aggregating, inter-table association, etc. For conventional computations, refer to: General operations on SPL files . Below we take Q1 and Q3 queries of TPCH as examples:

1. General grouping and aggregation of small result set

SQL query statement:

SELECT
	l_returnflag,
	l_linestatus,
	sum(l_quantity) AS sum_qty,
	sum(l_extendedprice) AS sum_base_price,
	sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
	sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
	avg(l_quantity) AS avg_qty,
	avg(l_extendedprice) AS avg_price,
	avg(l_discount) AS avg_disc,
	count(*) AS count_order
FROM
	lineitem
WHERE
	l_shipdate <= date '1995-12-01' - INTERVAL '90' DAY(3)
GROUP BY
	l_returnflag,
	l_linestatus
ORDER BY
	l_returnflag,
	l_linestatus;

Corresponding SPL script:

2. Group and aggregate after primary-sub table association

SQL query statement:

SELECT
	*
FROM
	(
	SELECT
		l_orderkey,
		sum(l_extendedprice * (1 - l_discount)) AS revenue,
		o_orderdate,
		o_shippriority
	FROM
		customer,
		orders,
		lineitem
	WHERE
		c_mktsegment = 'BUILDING'
		AND c_custkey = o_custkey
		AND l_orderkey = o_orderkey
		AND o_orderdate < date '1995-03-15'
		AND l_shipdate > date '1995-03-15'
	GROUP BY
		l_orderkey,
		o_orderdate,
		o_shippriority
	ORDER BY
		revenue DESC,
		o_orderdate )
WHERE
	rownum <= 10;

Corresponding SPL script:

More performance optimization examples of TPC-H can be found at: Performance Optimization Exercises Using TPC-H .

In addition, we recommend that the data analysts read SPL CookBook . This book collects hundreds of common tasks and corresponding SPL codes in data processing, covering most of the scenarios faced by data analysts. Once mastering the implementation methods for these tasks and utilizing them in practice, data analysts can easily handle conventional data analysis and processing tasks.

III. Mixed operation of hot and cold data

When the amount of hot data is small (memory can hold), they can be read directly from database and merged during calculation. Take Q1 of TPC-H as an example:

The maxkey in A4 is the maximum L_ORDERKEY in LINEITEM composite table.

The update@y in A8 will keep new data in memory and merge during computation, and also supports handling of deleted data.

SPL also provides the OGG external library, which allows for obtaining data update information based on logs for some tables whose new data cannot be determined directly or indirectly by timestamp field when data changes.