Migrating from Oozie and Sqoop to Airflow and Spark: 70% Faster Data Ingestion and 66x Query Performance Improvement

The existing infrastructure based on Oozie and Sqoop faced performance improvement limitations due to technical debt, including end-of-support, unintuitive XML management, and the proliferation of Small Files. To address this, over 1,200 pipelines were completely revamped into a new stack combining Airflow, Spark, and the Parquet+Snappy format.

Dynamic Extraction and Resource Optimization

The Oggre framework analyzes table capacity and characteristics at runtime to dynamically branch between single and parallel extraction modes. To prevent DB connection pool exhaustion, it fixes the concurrent connection limit based on the number of Spark Executors and cores, and automatically explores partition keys to prevent Data Skew. Additionally, by separating JDBC extraction (Phase 1) and sorting/storage (Phase 2) via temporary paths to block memory conflicts, it ensures stable processing without OOM even on heterogeneous tables.

Performance Improvement and File Optimization

By applying a file count estimation logic based on actual measurements, Small Files were suppressed and Large Files were appropriately split. As a result, ingestion time was reduced by 70.4% for specific tables, and query performance improved by up to 66.8x for column-based queries due to the transition from Text+Gzip to Parquet+Snappy. This is attributed to the Pruning effect of sorted Row Groups and selective reading capabilities.

Operational Efficiency Assurance

By applying Airflow's Dag Factory pattern, common settings and individual settings were separated, and monitoring and alerts were automated through custom Operators. This secured a scalable structure where the entire pipeline can be managed with a single template without individual XML modifications.