Spark SQL Basics

Spark tutorial · PySpark.in

Spark SQL Basics

Spark SQL is Apache Spark's module for working with structured data using SQL syntax. It allows data engineers and analysts to leverage SQL queries on large datasets, all while benefiting from Spark’s distributed computing power and optimization engine. Spark SQL integrates closely with the PySpark DataFrame API – in fact, it runs on the same underlying engine. This means whether you write code in DataFrame operations or in SQL, the performance is identical because both are translated to the same optimized execution plans by Spark’s Catalyst optimizer. In other words, you can choose the syntax (SQL vs. DataFrame) based on what is more readable or convenient for you, without worrying about speed differences.

What is Spark SQL and Why Use It?

Spark SQL provides a uniform interface for working with structured data. You can think of it as giving Spark a relational database capability on top of its distributed data processing engine. Key features and reasons to use Spark SQL include:

Spark's Catalyst Optimizer transforms SQL queries and DataFrame operations through multiple phases before execution. When you run a query, Spark first parses it into an unresolved logical plan and then performs analysis (resolving columns and data types, using the catalog to check table definitions). Next, Catalyst applies logical optimizations – e.g., simplifying expressions, pushing down filters to data sources, and rearranging joins for efficiency. After optimization, Spark creates one or more physical plans (actual strategies to execute the query, such as selecting a join algorithm or using an index), and may compare their estimated costs. Finally, Spark uses code generation to compile parts of the plan into bytecode for efficiency. Catalyst uses a mix of rule-based and cost-based optimization during these stages, all transparently – you usually don’t need to tweak anything to benefit from these optimizations. The figure above illustrates this query planning pipeline in Catalyst.

Spark SQL in the PySpark Ecosystem: In PySpark, Spark SQL is accessed via the SparkSession object .The SparkSession is the entry point that combines the Spark Context, SQL Context, and Hive Context . To get started, you need to initialize a SparkSession in your Python environment:

```

from pyspark.sql import SparkSession

# Initialize a SparkSession

spark = SparkSession.builder \

.appName("SparkSQLBasics") \

.getOrCreate()

# Example: creating a DataFrame (this could also be loaded from a file or other source)

data = [("Michael", 29), ("Andy", 30), ("Justin", 19)]

columns = ["name", "age"]

people_df = spark.createDataFrame(data, schema=columns)

people_df.printSchema()

people_df.show()

```

This confirms Spark has recognized the data types. DataFrames in Spark are conceptually like tables: they have named columns and schema. You can perform operations using the DataFrame API (as shown by printSchema, show, filters, etc.), which are optimized by Catalyst under the hood just like SQL queries. In fact, you can seamlessly intermix DataFrame code and SQL: for instance, you could filter with the DataFrame API then use SQL for aggregation, and both will be combined into one optimized plan.

Query Execution: Spark SQL (and DataFrame operations) uses lazy evaluation. When you write transformations (SQL queries or DataFrame methods), Spark builds a logical plan but defers actual computation until an action is called (like show(), collect(), or writing data out). Upon an action, Spark’s Catalyst optimizer steps in (as described above) to optimize the query plan before execution. The work is then parallelized across the cluster’s worker nodes. This approach means that as a user you focus on what data you want, and Spark decides how to efficiently get it. You can inspect the query plan by calling df.explain() or spark.sql("YOUR QUERY").explain(), which will show the physical plan and confirm things like filter pushdowns or join strategies chosen by Catalyst.

In summary, Spark SQL is a powerful way to work with big data using familiar SQL syntax, tightly integrated with PySpark’s DataFrame operations. It plays a pivotal role in the PySpark ecosystem by enabling declarative data analysis (you state what you want via SQL, and Spark figures out the rest) while still achieving high performance through advanced query optimizations. Whether you prefer SQL or Python code, Spark SQL ensures you get the best of both worlds: ease of expression and efficient execution.

Creating Temporary Tables and Views

Overview: In Spark SQL, a temporary view (or temp table) is a way to assign a name to a DataFrame so that you can query it with SQL commands. This is analogous to creating a virtual table in your Spark session. Once you create a temporary view, you can run SQL queries as if the DataFrame were a table in a database. Spark actually supports two kinds of temp views: session-scoped temporary views and global temporary views. It’s important to understand the difference and when to use each.

What is a Temporary View?

A temporary view is essentially a pointer or reference to the DataFrame’s data, accessible by a name. It does not persist data to disk – it’s just a metadata object within Spark. Creating a temp view is straightforward: you call DataFrame.createOrReplaceTempView("view_name"). For example:

This gives the DataFrame people_df the name "people" for SQL queries. You haven’t duplicated the data; you’ve just created a label for it. Now, you can use spark.sql() to run SQL commands against this view. For instance:

```

from pyspark.sql import SparkSession

# Initialize a SparkSession

spark = SparkSession.builder \

.appName("SparkSQLBasics") \

.getOrCreate()

# Example: creating a DataFrame (this could also be loaded from a file or other source)

data = [("Michael", 29), ("Andy", 30), ("Justin", 19)]

columns = ["name", "age"]

people_df = spark.createDataFrame(data, schema=columns)

# Register the DataFrame as a temporary view (session-scoped)

people_df.createOrReplaceTempView("people")

# Query the temporary view using Spark SQL

result_df = spark.sql("SELECT name, age FROM people WHERE age < 25")

result_df.show()

```

This would select the name and age of all people under 25 from our people view. Under the hood, Spark translates that SQL into the equivalent DataFrame operations on people_df. The ability to register DataFrames as views means you can seamlessly mix SQL and PySpark in your workflow. A view “gives your DataFrame a name that SQL queries can reference, just like a table in a traditional database”. It’s a convenient bridge between the DataFrame API and Spark SQL.

Temporary View vs. Global Temporary View: By default, a temp view like the one above is tied to your SparkSession. This is a session-scoped temporary view. It exists only for the duration of your Spark session (or until you explicitly drop it), and it’s visible only to that SparkSession. If you create a new Spark session or if the current one restarts, the view disappears. Spark also provides global temporary views, which are a bit different:

In code, creating a global view looks like:

```

# Create a global temporary view

people_df.createGlobalTempView("people_global")

#Now the DataFrame’s contents can be queried as global_temp.people_global from any session.

spark.newSession().sql("SELECT COUNT(*) FROM global_temp.people_global").show()

```

Now the DataFrame’s contents can be queried as global_temp.people_global from any session. For instance, if you start a new SparkSession (using spark.newSession()), you could still do it.

This would retrieve data from the global view in a new session (where a normal temp view would not be accessible).

When to use which?

In most cases, you’ll use session-scoped temp views for convenience within a single job or interactive session. They are simple and automatically cleaned up when your session ends. Use createOrReplaceTempView when you just need to run some SQL queries in your current session or notebook. On the other hand, global temp views are useful if you have multiple SparkSessions (for example, in a multi-user environment or if you deliberately create separate sessions) that need to share the same data view. A global view can be accessed from different sessions as long as the Spark application is running. This can be useful in certain long-running applications or if you create a view in one notebook and want to use it in another (within the same Spark application).

However, caution is warranted with global views:

To summarize:

Example – Using Temp and Global Views: Suppose we have our people_df DataFrame. Let’s create both a temp view and a global view and see how to use them:

```

from pyspark.sql import SparkSession

# Initialize a SparkSession

spark = SparkSession.builder \

.appName("SparkSQLBasics") \

.getOrCreate()

# Example: creating a DataFrame (this could also be loaded from a file or other source)

data = [("Michael", 29), ("Andy", 30), ("Justin", 19)]

columns = ["name", "age"]

people_df = spark.createDataFrame(data, schema=columns)

people_df.createOrReplaceTempView("people_view") # session-scoped temp view

people_df.createGlobalTempView("people_global_view") # global temp view

# Query the session-scoped view

spark.sql("SELECT COUNT(*) AS count_people FROM people_view").show()

# Query the global view (note the global_temp database)

spark.sql("SELECT COUNT(*) AS count_people FROM global_temp.people_global_view").show()

```

You would see that both queries return the same count of people (3 in our small example). If we were to open a new Spark session and try to query people_view, it would fail (not found), but querying global_temp.people_global_view would still work (as long as the original Spark application is still running). This demonstrates the difference in scope.

More Spark tutorials

All tutorials · Try the free PySpark compiler · Practice challenges