Why OLTP Falls Short for Big Data: OLAP, Hadoop & MPP Explained

Scientific Method for Data Insight

Data analysis is treated as the central step of the scientific method: formulate a hypothesis, design experiments, collect data, and analyse the results to obtain actionable insight (intelligence).

Three‑Step Data Analysis Process

Collect raw data (signals, logs, sensor readings) and store it as data.

Transform data into structured information through cleaning, enrichment, and schema definition.

Apply analytical techniques (SQL, statistical models, machine learning) on information to derive intelligence (insight).

OLTP vs. OLAP

Access pattern : OLTP processes a few rows per transaction; OLAP executes large‑scale scans and joins.

Data type : OLTP keeps only the current state; OLAP retains full historical snapshots with timestamps.

Scale : OLTP typically handles GB‑level datasets; OLAP targets TB‑to‑PB scale.

Latency : OLTP updates in real time; OLAP may ingest with a short delay, but modern pipelines reduce this window.

Schema : OLTP uses normalized 3NF tables; OLAP adopts star schemas or flat wide tables, often tolerating redundancy for query performance.

Tooling : OLTP benefits from row‑oriented storage and in‑memory columnar compression; OLAP relies on specialized BI engines that can process massive result sets efficiently.

Impact of Big Data and Cloud Computing

The explosion of heterogeneous data (IoT streams, social media, logs) and the availability of elastic cloud services have shifted data‑warehouse deployments from on‑premise clusters to managed, on‑demand platforms. Managed Hadoop services (e.g., Baidu Managed Hadoop – BMR) allow one‑click cluster provisioning, automatic scaling, and pay‑as‑you‑go resource consumption.

Hadoop Ecosystem for Data Warehousing

HDFS : Distributed file system; can be backed by cloud object stores such as Baidu Object Store (BOS) to separate compute and storage.

YARN : Cluster‑wide resource manager.

Hive and Pig : Batch query languages (HiveQL, Pig Latin) for ETL and reporting.

Spark SQL : In‑memory distributed SQL engine for low‑latency analytics.

HBase : NoSQL key‑value store for random‑access workloads.

Key storage features in Hadoop:

Columnar compression to reduce I/O.

Data partitioning (e.g., by date) to prune irrelevant files during query.

Intelligent indexing (e.g., Bloom filters) for faster predicate evaluation.

Support for importing data from external databases directly into the distributed file system.

Query engine capabilities include:

HiveQL / Spark SQL support.

Cost‑based optimizer (gradually maturing in Hadoop).

Distributed joins on large tables.

Predicate push‑down to storage nodes.

Massive Parallel Processing (MPP) Architecture

MPP systems combine a shared‑nothing node topology with columnar storage. Typical characteristics:

Fine‑grained data partitioning with explicit node assignment, enabling local joins.

Single‑table capacity up to several terabytes.

Composite partitioning across heterogeneous disks (SSD/HDD).

High‑efficiency columnar compression.

Batch‑level atomic commits and MVCC for concurrency control.

Advanced query execution: vectorized engine, predicate push‑down, partition pruning, and distributed join strategies.

Full MySQL protocol compatibility, allowing existing MySQL clients and BI tools to connect directly.

Comparison: Hadoop vs. MPP

Architecture : Hadoop offers MapReduce and Spark data‑flow engines; MPP uses shared‑nothing nodes with native columnar stores.

Supported data types : Hadoop handles structured, semi‑structured, and unstructured data; MPP is optimized for structured relational data.

Storage locality : MPP provides tighter data‑node locality, reducing network traffic for joins.

Query interface : Hadoop relies on HiveQL / Spark SQL; MPP exposes standard ANSI SQL.

Performance : For pure relational workloads, MPP’s cost‑based optimizer and local joins typically outperform Hadoop’s MapReduce‑based execution.

Practical Deployment Guidance

Unstructured or log‑heavy workloads : Deploy a managed Hadoop service (e.g., BMR). Use Hive for batch ETL and Spark SQL for interactive analysis. Leverage schema‑on‑read to handle weakly structured data.

Highly relational, low‑latency BI workloads : Load cleaned data into an MPP warehouse such as Palo (MySQL‑compatible). Use standard SQL from BI tools for sub‑second reporting on large fact tables.

Data pipeline : Ingest raw data into Hadoop, process/clean it with Spark or Hive, then export the resulting relational tables into the MPP system to serve as the “single version of the truth”.

Future Directions

Emerging trends point toward logical data warehouses that combine data virtualization, data lakes, and pay‑per‑query cloud services:

Data virtualization : Query engines access raw data in place (e.g., Hadoop, object stores) without materializing ETL pipelines, providing real‑time, schema‑on‑read access.

Data lake : Centralized storage of raw and processed data, decoupled from compute; supports both batch and streaming workloads.

Pay‑per‑query services : Serverless SQL offerings charge only for query execution time, reducing the need for long‑running clusters.

Open‑source compatibility : Preference for engines that support standard SQL (e.g., Spark SQL) to avoid vendor‑specific syntax lock‑in.