Boost ETL Performance: Key Tips for Resources, Partitioning & Monitoring

Check Resource Allocation

Resources should be configured to maximize effective usage based on workload. For batch processing with Inceptor, allocate about 50% of CPU, keep each executor under 16 cores, and prefer multiple executors (e.g., 2 executors ×10 cores) over a single large executor. Ensure other services retain sufficient resources; more compute generally yields near‑linear performance gains.

Collect Data Characteristics, Determine Partitioning and Bucketing

Partition

Choose partition columns based on business logic and data distribution. Time fields are common for fact tables; department or region codes suit other scenarios. Avoid columns with highly skewed values unless mitigated with range partitions. Determine an appropriate number of partitions—typically dozens—not too few to mix hot/cold data, nor too many (e.g., avoid daily partitions or partitions smaller than 1 GB).

Bucketing

Select high‑cardinality columns for bucketing, aiming for uniform record distribution. Bucket count depends on storage format; for ORC tables keep each bucket ≤200 MB (≤100 MB for transactional ORC). Adjust bucket numbers when tables are already partitioned to avoid excessive small files that degrade task efficiency.

When both partition and bucket counts are large, the total number of Map tasks (partition × bucket) can explode, harming concurrency.

Task Runtime Collection and Monitoring

Monitor via the 4040 UI focusing on three aspects:

Task count : Too many tasks may indicate over‑partitioning/bucketing; too few tasks can cause long‑running tasks.

Task skew : Look for keys causing data skew (e.g., NULLs, hot regions) or window functions without proper partitioning that create tail tasks.

Concurrent stage count : Estimate expected concurrent stages as total CPU cores ÷ average bucket count per task; deviations suggest configuration issues.

Case Study

In a mail‑processing job, a row_number() over(order by ...) window function without partition by caused a single task to run over 10 minutes due to global sorting, leading to severe skew.

Another scenario showed a >3‑minute gap between stages caused by lock contention; after mitigating the lock, concurrency and performance improved.

/* 以邮件收寄为主表，以邮件号码关联生成临时表数据 */
CREATE TABLE PIMS_SDATA.TMP_MAIL_33_20170106_0123 AS SELECT * FROM
(
  SELECT
    A.PostId PostId ,
    ......
  FROM (
    SELECT
      row_number() over(order by x.PostNbr, x.CollCpOrgCd, x.CollDt, x.CollTm, x.PostBusnTypCd, x.SeatNbr, x.prikey) + y.PostId PostId ,
      ......
    FROM PIMS_SDATA.PEO_TXNPCL_20170106_33 x,
         (SELECT ROW_MAXID PostId FROM tmp_maxid123) y
  ) A
  LEFT OUTER JOIN PIMS_SDATA.PEO_TXNPCF_20170106_33 B
    ON A.PostNbr = B.PostNbr
   AND A.CollCpOrgCd = B.CollCpOrgCd
  ......
  ......
);

Summary

The article presents three main ETL optimization ideas—resource configuration, partitioning & bucketing based on data characteristics, and task monitoring—detailing principles, common pitfalls, and practical solutions, with case studies to illustrate each point.