Working with Tables and Views in PySpark

One of the biggest strengths of Apache PySpark is the ability to use SQL alongside the DataFrame API. If you are comfortable working with SQL, you can easily register a DataFrame as a temporary view using the createOrReplaceTempView() method and then run SQL queries on top of it.

A temporary view in PySpark behaves like a regular SQL table with rows and columns, but it is not physically stored as files. In this lesson, we will explain what createOrReplaceTempView() is and how to use it to create temporary views and execute SQL queries in PySpark.

Note that throughout this lesson, we will use the terms table and view interchangeably.

1. Syntax of createOrReplaceTempView()

Following is the syntax of the createOrReplaceTempView() method. This method is used with PySpark DataFrame objects.

# Syntax of createOrReplaceTempView()
createOrReplaceTempView(viewName)

2. How Does createOrReplaceTempView() work in PySpark?

createOrReplaceTempView() in PySpark creates a view only if it does not already exist; if it already exists, it replaces the existing view with the new one. PySpark SQL views are lazily evaluated, meaning they do not persist in memory unless you cache the dataset by using the cache() method.

Some Key Points to note:

• createOrReplaceTempView() is used when you want to store the table for a specific SparkSession.
• Once created, you can use it to run SQL queries.
• These temporary views are session-scoped, i.e., valid only for the running SparkSession.
• They can’t be shared between sessions.
• These views will be dropped when the session ends unless you created them as a Hive table.

3. Create a Temporary View

The createOrReplaceTempView() is used to create a temporary view/table from the PySpark DataFrame objects. Since it is a temporary view, the lifetime of the table/view is tied to the current SparkSession and will be automatically removed when your SparkSession ends.

The following are the steps to create a temporary view in PySpark and access it.

Step 1: Create a PySpark DataFrame
Step 2: Convert it to an SQL table (a.k.a view)
Step 3: Access the view using SQL query

3.1 Create a DataFrame

First, let’s create a PySpark DataFrame with columns firstname, lastname, country, and state.

from pyspark.sql import SparkSession

# Create spark session
spark = SparkSession \
    .builder \
    .appName("AIStudyLabs.com") \
    .enableHiveSupport() \
    .getOrCreate()

data = [
    ("James", "Smith", "USA", "CA"),
    ("Michael", "Rose", "USA", "NY"),
    ("Robert", "Williams", "USA", "CA"),
    ("Maria", "Jones", "USA", "FL")
]
columns = ["firstname", "lastname", "country", "state"]

# Create dataframe
sampleDF = spark.createDataFrame(data, schema=columns)
sampleDF.show()

3.2 Create Table/View using PySpark createOrReplaceTempView()

Let’s create a temporary view on top of the DataFrame object by using sampleDF.createOrReplaceTempView(). PySpark SQL temporary views are session-scoped and will not be available if the session that creates them terminates. The following example creates a Person table.

# Create Temporary View/Table
sampleDF.createOrReplaceTempView("Person")

If you want to have a temporary view that is shared among all sessions and keep alive until the PySpark application terminates, you can create a global temporary view using createGlobalTempView().

3.3 Access View using PySpark SQL Query

Using SparkSession, you can access PySpark SQL capabilities in Apache PySpark. Once you have a temporary view, you can run any ANSI SQL queries using the spark.sql() method.

# Run SQL Query
spark.sql("SELECT firstname, lastname FROM Person").show()

This will yield the following output:

| firstname | lastname | |-----------|----------| | James | Smith | | Michael | Rose | | Robert | Williams | | Maria | Jones |