How Youzan Scaled 5,000 Daily SparkSQL Jobs: Migration Lessons from Hive

1. Youzan Data Platform Overview

The platform consists of three layers: a data ingestion layer (DataY for MySQL incremental loads, DataX for full loads, and Flume for log and binlog streams), a compute layer (Hadoop/HBase storage, Hive and Spark ETL, Spark/Presto/Druid for interactive queries, and real‑time engines JStorm, Spark Streaming, Flink), and a developer‑facing data platform offering scheduling, data transfer, quality checks, ad‑hoc and metadata queries.

2. SparkSQL Evolution at Youzan

2.1 Usability Improvements

To match Hive users' expectations, Youzan added progress logging to the Spark Thrift Server (STS), exposing job and stage percentages every two seconds. Monitoring was enhanced by extending SparkListener to publish session‑level audit events (SQL text, timestamps, CPU usage, workflow ID) to the event bus and adding a "cancelled" status.

2.1.3 Thrift Server High Availability

STS is a single‑process service, vulnerable to OOM failures. Youzan separated ad‑hoc and batch STS instances, registered each in ZooKeeper, and used ZK‑based load balancing so that a failed node can be bypassed without disrupting scheduled jobs.

2.1.4 Permission Control

The team adopted Apache Ranger with plugin integration for fine‑grained authorization, replacing Hive's native mechanisms.

2.2 Performance Optimizations

Load‑table operations on large partitioned tables caused long stalls; the solution switched from copyFile to moveFile (SPARK‑20187) and aligned Hive metastore JAR versions via spark.sql.hive.metastore.jars=maven to ensure consistent behavior.

2.3 Small‑File Mitigation

SparkSQL generated many tiny files, stressing the NameNode. The team applied SPARK‑24940 using SQL hints to coalesce output and scheduled a daily merge job. Setting

spark.hadoop.mapreduce.fileoutputcommitter.algorithm.version=2

cut commit time by over 70%.

3. Migration Path from Hive to SparkSQL

Initial migration targeted the top‑100 resource‑heavy Hive jobs (the 80/20 rule). By the end of 2017, SparkSQL handled 5% of tasks but consumed only 20% of the resources, achieving a 10‑30% reduction compared to Hive.

Manual case‑by‑case migration proved slow; therefore, an automated engine‑selection service called SQL Engine Proposer was built. It analyses query patterns (using ANTLR4), consults historical execution metrics, and applies rule‑based, whitelist, and priority strategies to decide between Hive, SparkSQL, or a high‑memory SparkSQL instance.

As of the latest measurement, SparkSQL accounts for 73% of engine‑selected jobs, using only 32% of cluster resources and delivering a 67% overall resource saving.

4. Future Outlook

The roadmap aims to shift 80% of Hadoop cluster capacity to SparkSQL, run 70% of jobs on it, and open the highest‑priority tier to engine selection. Integration of Intel’s open‑source Adaptive Execution (https://github.com/Intel-bigdata/spark-adaptive) will further reduce shuffle volume, enable cost‑based broadcast‑join optimization, replace sort‑merge joins, and eradicate small‑file issues.

For technical details, the community can refer to the linked articles and the Youzan tech blog.