Data Task Optimization Techniques and Practices

This article provides a concise analysis and summary of several less‑common data‑task optimization methods used in offline data development. It focuses on practical techniques rather than the usual optimizations such as small‑file merging or skew join handling.

1. Introduction

As data volumes grow and business logic becomes more complex, performance requirements for data tasks increase. Although many developers are familiar with classic tuning topics, the article argues that a systematic set of optimization methods can guide both development and problem‑solving.

2. Overview of Task Optimization

Optimization scenarios are categorized into three perspectives – point, line, and surface – and divided into two main groups: technical means and business means. Technical means are generic solutions at the development layer, while business means focus on adjusting the data pipeline according to specific business characteristics.

Key technical directions include:

IO and network communication improvements (e.g., more efficient serialization, compression, Netty‑based RPC).

Data‑level techniques such as small‑file merging, handling data skew, and random distribution.

3. Practical Optimization Cases

3.1 Data Redistribution (Distribute & Rand)

The goal is to shuffle data to mitigate small‑file issues and data skew. Two typical SQL patterns are shown:

SELECT column1,.... FROM TABLEX DISTRIBUTE BY column1[,...]

SELECT column1,.... FROM TABLEX DISTRIBUTE BY rand([,seed])[,....]

Rand() usage notes:

Rand() generates a double in the range [0,1].

Rand(int seed) produces a stable random sequence.

Combining Rand() with pmod(), mod(), floor(), ceil() enables generation of random integers in any desired range.

Example of small‑file merging using a fixed distribution column:

SELECT column1, column2, column.... FROM TABLEX
WHERE ds = '${bizdate}'
DISTRIBUTE BY '${bizdate}', column1....

Example of using Rand() to control the number of output files:

SELECT column1, column2, column.... FROM TABLEX
WHERE ds = '${bizdate}'
DISTRIBUTE BY FLOOR(RAND()*N)/CEIL(RAND()*N)

Or using a seeded Rand() for stable partitions:

SELECT column1, column2, column.... FROM (
SELECT column1, column2, column...., FLOOR(RAND(seed)*N) AS rep_partition FROM TABLEX
WHERE ds = '${bizdate}'
) DISTRIBUTE BY rep_partition

3.2 Data Explosion (Explode)

When a join suffers from severe skew, the smaller table’s join keys can be “exploded” to generate multiple copies, spreading the load across reducers. A sample Hive query demonstrates the use of explode() together with rand() to create additional keys.

SELECT A.* FROM (SELECT * FROM a) A
LEFT JOIN (SELECT CONCAT(C.rand_num,'_',D.key) AS key FROM (SELECT rand_num FROM dual LATERAL VIEW explode(ARRAY(0,1,2,3,4,5,6,7,8,9)) rand_num_list AS rand_num) C) B
ON CONCAT(CAST(9*RAND() AS BIGINT), '_', A.key) = B.key
WHERE A.ds = '${bizdate}';

3.3 Data Bucketing

For large tables with highly clustered columns, bucketing (hash‑based partitioning) can improve query efficiency and join performance. Hive requires set hive.enforce.bucketing=true to enable this feature, and the number of buckets typically matches the number of reducers.

3.4 Concurrency and Parallelism Control

Map tasks are limited to 9,999 and reduce tasks to 1,000. Adjusting these numbers can increase throughput but may also increase resource contention. Scripts (Shell/Python) combined with SQL can be used to orchestrate parallel job execution.

3.5 Multi‑Output and Materialization (Read‑Once Output More)

Using Common Table Expressions (CTE) with WITH ... AS ... improves readability. Materializing CTEs avoids repeated scans of the same data. Hive’s materialization threshold can be tuned via hive.optimize.cte.materialize.threshold .

WITH cte_name AS (cte_query), cte_name2 AS (cte_query2) ...

Multi‑insert statements allow up to 255 output paths in a single query, with restrictions on partition and non‑partition table duplication.

FROM
INSERT OVERWRITE TABLE table1 [PARTITION (pt_spec1)] SELECT ...
INSERT OVERWRITE TABLE table2 [PARTITION (pt_spec2)] SELECT ...
...;

4. Reflection & Summary

The article emphasizes that technical optimizations alone cannot solve all business problems; a deep understanding of business characteristics is essential. Effective data task optimization combines technical tricks, business‑driven adjustments, and collaborative engineering practices.