Architectural Overview and Optimization Techniques for SQL‑on‑Hadoop Systems

System Architecture

SQL‑on‑Hadoop platforms can be classified into two main architectures: those that build a query engine on top of an existing runtime framework (e.g., Hive) and those that adopt a classic Massively Parallel Processing (MPP) design (e.g., Impala, Presto). The MPP style often yields better performance because it avoids intermediate disk writes, enables pipeline execution, and reduces task‑level overhead, although it can suffer from limited scalability and poorer fault tolerance.

Core Components

UI layer – provides Web/GUI/CLI interfaces for users to submit queries.

QL layer – parses SQL into an executable plan (AST, logical plan).

Execution layer – a master node schedules jobs and workers perform local computation.

IO layer – interacts with storage (HDFS, NoSQL, relational DB) via format‑specific handlers.

Storage layer – typically HDFS, but can also query external stores.

Metadata service – manages table schemas and other catalog information.

Compilation Process

The transformation from SQL to an executable plan consists of five steps:

Convert SQL to an abstract syntax tree (AST) – often using tools like ANTLR.

Semantic analysis – validate tables, columns, and SQL semantics.

Generate a logical plan – a DAG of logical operators (e.g., TableScan, GroupBy).

Logical‑plan optimization – see the Optimizer section.

Physical plan generation – produce MR/Tez jobs for Hive, plan fragments for Impala, etc.

Example SQL (Hive on MR):

select count(1) from status_updates where ds = '2009-08-01';

Example Presto query (from Meituan):

select c1.rank, count(*)
from dim.city c1 join dim.city c2 on c1.id = c2.id
where c1.id > 10 group by c1.rank limit 10;

Optimizer

Early Hive versions used simple rule‑based optimizations (predicate push‑down, operator merging). Later releases added more sophisticated rules (join‑group‑by merging, star‑schema joins) and a correlation optimizer that eliminates redundant scans. Cost‑Based Optimization (CBO) – still immature in many open‑source engines – estimates join orders using statistics such as cardinality and histograms. Projects like Apache Optiq aim to bring CBO to Hive.

Execution Efficiency

CPU

CPU‑bound bottlenecks arise from virtual‑function overhead, boxing of primitive types, branch mispredictions, and cache misses. Two common remedies are:

Dynamic code generation – generate native code for expressions, eliminating interpretation overhead. For example, Spark SQL and Presto use reflection; Impala uses LLVM.

Vectorization – process data in batches (e.g., 1k rows) using columnar buffers, improving cache locality and enabling SIMD.

Vectorized add function example:

void add(int vecNum, long[] result, long[] col1, long[] col2, int[] selVec) {
    if (selVec == null) {
        for (int i = 0; i < vecNum; i++) {
            result[i] = col1[i] + col2[i];
        }
    } else {
        for (int i = 0; i < vecNum; i++) {
            int selIdx = selVec[i];
            result[selIdx] = col1[selIdx] + col2[selIdx];
        }
    }
}

IO

Since data resides in HDFS, IO optimizations focus on reducing latency and increasing throughput:

Short‑circuit local reads – bypass the DataNode when the block is local.

Zero‑copy – avoid copying between kernel and user buffers.

Disk‑aware scheduling – assign tasks to CPUs that are close to the required disks.

Storage Formats

Columnar storage is essential for analytical workloads. The two dominant formats are ORCFile (Hive) and Parquet (Impala, Hive, Pig). Both provide block‑level statistics, advanced encodings (RLE, dictionary, bitmap, delta), and support nested data via Dremel‑style definition and repetition levels.

ORCFile stores data in strips (default 256 MB) with per‑strip indexes for min/max values, enabling fast filtering and aggregation.

Parquet offers broader compatibility and a Dremel‑inspired nested representation, allowing complex types (array, map, struct) to be efficiently stored and queried.

Resource Control

Runtime Resource Adjustment

Frameworks such as Tez introduce a Vertex Manager that dynamically decides the number of reduce tasks based on runtime statistics. TAJO calls this progressive query optimization, which can also adjust join order.

Integration with YARN

SQL‑on‑Hadoop engines must coexist with YARN. To mitigate AM startup latency, long‑lived ApplicationMasters are used, and containers are reused across tasks. Impala adopts an aggressive strategy of waiting for all containers before starting execution.

Other Advanced Features

Multi‑source query – Presto can read from MySQL, Cassandra, Kafka; Impala and Hive can query HBase.

Approximate query – HyperLogLog based distinct‑count estimation (Impala ndv, Presto via BlinkDB).

Conclusion

SQL‑on‑Hadoop systems have matured considerably, yet continuous improvements are needed in areas such as advanced analytics functions, richer columnar encodings, and robust cost‑based optimizers. The evolving ecosystem promises faster, more flexible data exploration over massive datasets.