A Deep Dive into Spark SQL: Relational Data Processing at Scale

Apache Spark has become the de facto standard for big data processing in the enterprise, thanks to its ability to handle both batch and streaming workloads at petabyte scale. A key component of Spark‘s success is Spark SQL, a module that integrates relational processing with Spark‘s functional programming API. Spark SQL has quickly become one of the most widely used technologies for big data analytics, with adoption by thousands of organizations across industries.

In this article, we‘ll take a comprehensive look at Spark SQL from the lens of an AI and machine learning expert. We‘ll explore Spark SQL‘s architecture and features, examine how it enables scalable relational data processing, and highlight its critical role in the modern AI/ML lifecycle. Along the way, we‘ll dive into performance benchmarks, real-world case studies, and best practices for using Spark SQL in production.

Why Spark SQL?

To understand the need for Spark SQL, it‘s helpful to examine the challenges of big data processing and analytics. With the explosion of data across every industry, organizations are struggling to extract timely insights from increasingly large and complex datasets. Traditional data warehouses and SQL-based analytics systems can‘t handle the scale and variety of big data. At the same time, first generation big data technologies like Hadoop MapReduce are too low-level and inflexible for many analytics use cases.

Spark SQL aims to bridge this gap by combining the scalability of Spark with the expressiveness of SQL. It provides a familiar DataFrame API that integrates seamlessly with Spark‘s support for Java, Scala, Python and R, while adding a highly optimized execution engine under the hood. With Spark SQL, developers and data scientists can express complex queries on structured and semi-structured data much more concisely than using low-level APIs.

The numbers speak to Spark SQL‘s massive popularity. According to the 2020 Spark Survey, Spark SQL is the 2nd most commonly used Spark component behind only Spark Core, with 74% of respondents using it in production [1]. That represents a significant jump from 2016, when Spark SQL adoption sat at just 47% [2].

Spark SQL Architecture

Spark SQL Architecture (Source: Databricks Documentation)

At the core of Spark SQL is the Catalyst optimizer, which uses advanced programming language features to allow easy addition of data sources, optimization rules, and data types [3]. Catalyst supports both rule-based and cost-based optimization, and powers all of Spark SQL‘s built-in performance enhancements.

On top of Catalyst sit the DataFrame API and Spark SQL query interface. The DataFrame API provides a programmatic abstraction for data manipulations, similar to Python‘s pandas library but distributed across a cluster. Spark SQL‘s query interface allows raw SQL queries to be run on Spark, with full support for subqueries, joins, aggregations, and more.

Under the hood, Spark SQL leverages several techniques to achieve high performance and scalability [4]:

• Columnar storage: Spark SQL supports column-oriented storage of data in memory and on disk, greatly reducing I/O for queries that only touch a few columns.
• Whole-stage code generation: Catalyst compiles SQL queries down to low-level RDD operations using Scala code generation, eliminating virtual function calls and leveraging CPU registers for intermediate data.
• Vectorized execution: Spark SQL‘s execution engine operates on vectors (batches) of data rather than single rows, taking advantage of modern CPU architectures.
• Adaptive query execution: Spark SQL can re-optimize a query plan in the middle of execution based on runtime statistics, handling skew and other challenges.

Together, these optimizations have allowed Spark SQL to achieve performance comparable to or better than dedicated SQL-on-Hadoop engines like Impala and Hive on analytical queries, while retaining Spark‘s flexibility and ease of use.

DataFrames and Data Sources

The DataFrame API is Spark SQL‘s most popular interface for structured data manipulation. A DataFrame is a distributed collection of rows with the same schema, conceptually equivalent to a table in a relational database. However, DataFrames have several key advantages over traditional RDBMS tables for big data workloads:

• Lazy evaluation: DataFrame operations are lazily evaluated, meaning they can be defined and optimized upfront before being executed on data. This allows Spark SQL to push down predicates, prune unnecessary columns, and optimize the physical execution plan.
• Unified APIs: The same DataFrame code works no matter where the data is stored, whether in flat files, Hive tables, or external databases. This greatly reduces the cognitive overhead of working with different data sources.
• Integration with Spark: DataFrames can be used in any Spark language API and combined seamlessly with Spark‘s other libraries for streaming, machine learning, and graph analytics.

To enable this flexibility, Spark SQL provides a unified Data Source API for integrating with structured data stores. Out of the box, Spark SQL supports reading and writing to CSV, JSON, Parquet, ORC, JDBC, Avro, and more. It also has first-class integration with the Hive metastore, providing transparent access to Hive tables and UDFs.

Spark 3.0 introduced a new, more flexible Data Source API (v2) that aims to significantly expand the analytical capabilities of Spark SQL [5]. The V2 API allows plug-in data sources to push down complex data types, support new physical layouts like indexing, and implement data modification operations like updates and deletes. This will enable much tighter integration between Spark and modern data stores like Delta Lake, Iceberg, and Apache Hudi.

Performance and Scalability

Spark SQL has been shown to achieve excellent performance at scale for analytical workloads. In a 2016 benchmark comparing big data SQL engines, Spark SQL was able to query 1 PB of data 2-5X faster than Presto and Hive on industry-standard TPC-DS queries [6]. It scaled to 100 nodes and an average of 3.2 GB/s of scan throughput per node.

More recent benchmarks from Databricks, the company founded by the creators of Spark, show even more impressive results. In a 2018 benchmark, Spark SQL was able to process 1 PB of data with 10.4 GB/s scan throughput per node, an over 3X improvement from 2016 [7]. It also demonstrated near linear scalability from 10 to 1000 nodes.

Databricks Runtime (including Spark SQL) scalability (Source: What‘s New in Databricks Runtime 7.0)

Part of these performance gains come from Spark SQL‘s Adaptive Query Execution (AQE) feature introduced in Spark 3.0. AQE allows Spark SQL to adapt query plans on the fly based on runtime statistics, handling challenges like data skew, correlated subqueries, and complex joins [8]. In benchmarks, AQE improved TPC-DS query performance by up to 18X compared to Spark 2.4.

Enabling AI and Machine Learning

Spark SQL plays a crucial role in the modern AI and machine learning lifecycle by providing a powerful substrate for data preparation and feature engineering. The DataFrame API makes it easy to express complex data transformations, while Spark SQL‘s tight integration with MLlib (Spark‘s machine learning library) allows refined datasets to flow seamlessly into model training and deployment pipelines.

For example, Uber has used Spark SQL and MLlib to build a real-time machine learning platform called Michelangelo [9]. Michelangelo uses Spark SQL to process and aggregate raw data from multiple sources into clean, labelled training datasets. It then employs MLlib to train and serve thousands of models for uses cases like ETA prediction, fraud detection, and dynamic pricing. All together, Michelangelo runs over 100,000 Spark jobs per day on petabytes of data.

Uber‘s Michelangelo architecture powered by Spark and MLlib (Source: Meet Michelangelo: Uber‘s Machine Learning Platform)

Spark SQL‘s DataFrame API is also a natural fit for a variety of data science tools and frameworks. For example, Databricks has created a hosted platform for collaborative data science that exposes Spark SQL as a backend for interactive Python and R notebooks [10]. This allows data scientists to leverage familiar tools like pandas, scikit-learn, and TensorFlow while transparently scaling their workloads to massive datasets using Spark.

Looking ahead, Spark SQL will play a key role in simplifying end-to-end machine learning pipelines as the interface between the data lake and data science environments. Its strong support for semi-structured data, flexible UDF framework, and high-performance execution engine make it a natural choice for the complex feature engineering and model scoring workloads required for modern AI applications.

The Future of Spark SQL

As the big data landscape continues to evolve, Spark SQL remains well-positioned to be a key player thanks to its unique combination of usability, performance, and integration with the Spark ecosystem. There are several notable trends that will shape Spark SQL‘s future:

• Lakehouse architecture: There is growing convergence between data warehouses and data lakes into a new architecture called the "lakehouse". Lakehouses combine the best of both worlds by layering ACID transactions and BI-style analytics on top of low-cost object storage [11]. Spark SQL is a key enabler of the lakehouse, providing the query engine and transactional guarantees needed to power lakehouse technologies like Delta Lake.

• Data mesh and data fabric: Enterprises are increasingly looking to decentralize their data architectures and create self-serve data platforms. Two notable approaches are the data mesh, which treats data as a product owned by domain teams, and the data fabric, which provides a unified view of data across hybrid environments [12]. Spark SQL‘s unified Data Source APIs and integration with the Hive metastore will make it a natural fit to power analytics in these decentralized data architectures.

• Serverless and elastic: Cloud data platforms are increasingly moving toward serverless, pay-per-query models that can automatically scale resources to meet changing workloads. Spark SQL already integrates with serverless query services like AWS Athena and Azure Synapse Serverless SQL pools [13]. Open source projects like Openwhisk Spark are also exploring how to run Spark in a purely serverless fashion [14].

• Unifying batch and streaming: One of Spark‘s key value propositions has always been unification of batch and streaming workloads. The Structured Streaming API, added in Spark 2.0, allows developers to express continuous queries using the same DataFrame API as static data [15]. Expect to see deeper unification between Spark SQL and Structured Streaming, providing a single interface to manage real-time data pipelines end-to-end.

Conclusion

Spark SQL has emerged as the de facto standard for big data analytics thanks to its unique combination of expressive querying, high performance, and integration with the Spark ecosystem. Its familiar DataFrame API and SQL interface substantially lower the bar to big data for data analysts and scientists, while its advanced Catalyst optimizer and execution engine provide near bare-metal speed.

As we‘ve seen, Spark SQL is already powering massive-scale data pipelines and AI applications across the enterprise. It will continue to play a critical role as the big data ecosystem evolves, serving as the unified query layer for decentralized data architectures and the lakehouse. Organizations looking to build scalable, flexible, and performant data platforms will find Spark SQL a powerful foundation for their big data journey.

References

[1] 2020 Spark Survey Results
[2] Apache Spark Survey 2016 Results
[3] Deep Dive into Spark SQL‘s Catalyst Optimizer
[4] Apache Spark as a Compiler: Joining a Billion Rows per Second on a Laptop
[5] Introducing the Apache Spark 3.0 DataSource API V2
[6] Spark SQL: Relational Data Processing in Spark
[7] Processing Petabytes of Data in Seconds with Databricks Delta
[8] Processing Petabytes of Data in Seconds with Databricks Delta
[9] Meet Michelangelo: Uber‘s Machine Learning Platform
[10] Collaborative Data Science with Databricks and Apache Spark
[11] What is a Data Lakehouse?
[12] Data Mesh vs. Data Fabric: Understanding the Differences
[13] Serverless Spark SQL in the Cloud
[14] Serverless Spark on OpenWhisk
[15] Structured Streaming In Apache Spark