Mastering ODPS SQL: Proven Tips to Slash Query Time and Tackle Data Skew

ODPS (Open Data Processing Service) is Alibaba's massive data processing platform built on its proprietary distributed OS. This article shares practical SQL optimization techniques drawn from extensive data‑warehouse development experience on ODPS.

Background

Data warehouses rely on SQL for massive data processing across platforms such as Oracle, MPP, Hadoop, Flink, and data lakes. Efficient SQL execution is a key challenge.

Fundamental Knowledge

1. Hive SQL execution process – see Hive SQL compilation reference.

2. Basic SQL syntax – refer to Hive official documentation.

Practical Tips

Handle NULL by using NVL or COALESCE.

Avoid SELECT *; explicitly list required columns to reduce I/O.

Use FROM (…) INSERT OVERWRITE A INSERT OVERWRITE B for multi‑insert scenarios.

Always limit partitions (e.g., ds) in queries to avoid full scans.

Add LIMIT for exploratory queries to save resources.

Push UDF logic to the first sub‑query to improve performance.

Leverage COLLECT_SET or LATERAL VIEW for row‑to‑column and column‑to‑row transformations.

Apply window functions such as ROW_NUMBER() or RANK() for ordered grouping.

Choose appropriate join types (inner, left, right, left anti, left semi) and ensure matching data types.

Use Cartesian product wisely, e.g., via a dimension table or LATERAL VIEW POSEXPLODE.

Increase map tasks for specific tables using hints like /*+SPLIT_SIZE(8)*/.

Data Skew Handling

For large‑table‑small‑table joins, apply /*+MAPJOIN(b)*/ and adjust odps.sql.mapjoin.memory.max.

For large‑table‑large‑table joins, consider bucketed joins or /*+SKEWJOIN*/ hints.

Replace COUNT(DISTINCT) with a pre‑aggregation GROUP BY step.

Stay updated with new ODPS features such as MaxCompute 2.0.

Use dynamic filter hints ( /*+DYNAMIC_FILTER(A,B)*/) for small‑to‑large joins.

Parameter Settings

Typical adjustments include mapper, joiner, reducer instance counts, CPU and memory settings, merge limits, and UDF memory/time limits. Example settings:

set odps.sql.mapper.cpu=100
set odps.sql.mapper.memory=1024
set odps.sql.mapper.merge.limit.size=64
set odps.sql.mapper.split.size=256
set odps.sql.joiner.instances=-1
set odps.sql.joiner.cpu=100
set odps.sql.joiner.memory=1024
set odps.sql.reducer.instances=-1
set odps.sql.reducer.cpu=100
set odps.sql.reducer.memory=1024
set odps.merge.cross.paths=true
set odps.merge.smallfile.filesize.threshold=64
set odps.merge.maxmerged.filesize.threshold=256
set odps.sql.udf.jvm.memory=1024
set odps.sql.udf.timeout=1800
set odps.sql.udf.python.memory=256
set odps.sql.udf.optimize.reuse=true
set odps.sql.udf.strict.mode=false

Case Study 1 – Join and Data Skew

Joining a 90‑day Taobao order table with product and SKU attribute tables caused a 4‑hour runtime. Skewjoin hint reduced time to ~2 hours but not enough. Traditional hot‑data split (top 500 k products) also yielded ~2 hours. Detailed plan analysis revealed implicit conversion of item_id and SKU_ID to DOUBLE, causing mismatches. Converting both sides to STRING reduced runtime to 40 minutes.

Case Study 2 – Bucketed Join for Large‑Large Tables

Aggregating user‑behavior data over 90 days showed poor performance. After creating a hash‑clustered test table with 1024 buckets on user_id, query resource consumption dropped from 175 CPU · min / 324 GB · min to 0.34 CPU · min / 0.61 GB · min.

Case Study 3 – Business‑Driven Optimizations

Switching from DOUBLE to BIGINT for behavior flags eliminated precision loss. Using a single read with multiple insert statements ( FROM (…) INSERT1 INSERT2) further cut map‑stage time by 40 minutes.

Case Study 4 – Bitmap for Multi‑Dimensional Aggregation

Bitmap UDFs ( bitmap_cardinality, bitmap_merge, bitmap_counter) were applied to de‑duplicate large‑scale metrics such as UV and order count, improving performance and avoiding skew.

Conclusion

SQL is a stable tool for translating business logic into physical execution. Effective optimization requires deep understanding of data characteristics, appropriate hints, parameter tuning, and sometimes structural changes such as hash clustering.

Reference links: https://tech.meituan.com/2014/02/12/hive-sql-to-mapreduce.html, https://cwiki.apache.org/confluence/display/Hive//GettingStarted#GettingStarted-SQLOperations