Apache Spark SQL Introduction

Spark SQL is a Spark module for structured data processing. Unlike Spark’s core engine, which works with unstructured collections of data, Spark SQL knows the structure of both the data and the computation being run on it, and uses that extra information to optimize queries automatically.

The result is that you can execute genuine SQL queries against your data, read from an existing Hive installation, or interact with Spark SQL over JDBC or ODBC from an external tool. Whichever way you run a query, in a different programming language or through the SQL interface directly, the result always comes back as a DataFrame or Dataset.

Three Core Capabilities of Spark SQL

• DataFrames, available in Python, Java, and Scala, give you a structured abstraction over your data, conceptually the same as a table in a relational database, which makes working with structured datasets far simpler than handling raw, unstructured collections.
• Reading and writing multiple formats, such as JSON, Parquet, and Hive tables, so the same engine can pull data in from, and write results out to, whatever format your pipeline actually uses.
• Querying with SQL, both from inside a Spark program and from external tools that connect to Spark SQL, so the same query language works whether you are writing code or connecting a reporting tool.

DataFrame and Dataset

A Dataset is an optimized version of Spark’s basic RDD abstraction, built to take advantage of Spark SQL’s execution engine while still giving you the type safety and functional style of an RDD, using transformations such as map, flatMap, and filter. The Dataset API is available in Scala and Java. Python does not have a separate Dataset API, though it still gets many of the same conveniences, such as accessing a column by name directly off a row.

A DataFrame is a Dataset organized into named columns, conceptually the same as a table in a relational database, or a data frame in R or Python, but with Spark SQL’s optimizations working underneath it. DataFrames can be built from structured files, Hive tables, external databases, or existing RDDs, and the DataFrame API is available in Scala, Java, Python, and R. In Scala specifically, a DataFrame is simply a type alias for Dataset of Row, so the two ideas are closely related rather than entirely separate concepts.

For the hands on syntax behind creating, querying, and transforming a DataFrame or Dataset, the Apache Spark SQL Commands guide walks through each one with examples.

Features of Spark SQL DataFrames

• Scales from a single node to a large cluster, and from datasets measured in kilobytes up to petabytes, without changing how you write your queries.
• Supports a wide range of formats, such as Avro, CSV, and Parquet, and storage systems including HDFS, Hive, and MySQL.
• Integrates directly with Spark’s core engine, so it works alongside the rest of the big data tools and frameworks already built around Spark.
• Offers the same DataFrame API across Python, Java, Scala, and R, so a concept learned in one language largely transfers to the others.
• Provides unified data access, meaning Hive tables, Parquet files, and JSON files can all be queried through the same interface rather than needing a different tool for each format.

How Spark SQL Optimizes Your Queries?

Two things happen behind the scenes that make Spark SQL faster than it would be if it simply ran your code line by line.

• Lazy evaluation. Spark does not run a transformation the moment you write it. Instead, it builds up a logical plan of everything you have asked for, and only actually executes anything once you call an action, such as show, count, or write. This gives Spark room to look at your entire chain of operations before running any of it.
• The Catalyst optimizer. Once an action triggers execution, Catalyst takes that logical plan and rewrites it into a more efficient physical plan, for example running a filter before a join instead of after, so less data has to move across the cluster. This is the extra optimization that comes from Spark SQL knowing the structure of your data in advance.

In practice, this means writing a query as plain SQL or as chained DataFrame methods usually performs about the same, since both get compiled down through the same Catalyst plan before anything actually runs.

Spark SQL Compared to a Traditional SQL Database

It is worth being clear about what Spark SQL is not. It is a processing module inside Spark, not a standalone database server that stores your data permanently. Spark SQL can read from an existing Hive installation, but it does not require Hive to function, and it can just as easily query a Parquet file, a JSON file, or a table already loaded into memory as a DataFrame. External tools can still connect to Spark SQL over JDBC or ODBC to run queries against it, which is what lets a BI tool treat Spark SQL similarly to a conventional database, even though the underlying engine works quite differently.

Uses of Spark SQL

• Executing SQL queries directly against structured data, whether that data lives in memory, in files, or in Hive.
• Reading data from an existing Hive installation without needing to move or reformat it first.
• Running the same query from a different programming language and getting the result back as a DataFrame or Dataset either way.

Common Misconceptions to Watch For

• Expecting a transformation to run immediately. Because of lazy evaluation, a chain of filters or selects does nothing on its own until an action such as show or count is called. If a transformation seems to do nothing, check whether an action has actually triggered it yet.
• Assuming Hive is required. Spark SQL can read Hive tables, but it works perfectly well against files or in memory DataFrames without any Hive installation at all.
• Treating SQL syntax and the DataFrame API as different performance choices. Both compile down through the same Catalyst optimizer, so the choice between them is really about readability for your specific query, not speed.

Watch It in Action

The video below, embedded on the original page, walks through these Spark SQL concepts.

That covers the core concepts behind Spark SQL. For the hands on commands, from setting up a Spark context through DataFrames, Datasets, and user defined functions, see the Apache Spark SQL Commands guide, or explore Prwatech’s Apache Spark training program, which includes placement assistance.