Introduction

One of the main advantages of Apache Spark is working with SQL along with the DataFrame/Dataset API. So if you are comfortable with SQL, you can create a temporary view on DataFrame/Dataset by using createOrReplaceTempView() and using SQL to select and manipulate the data.

A Temporary view in Spark is similar to a real SQL table that contains rows and columns, but the view is not materialized into files. In this article, we will be discussing what createOrReplaceTempView() is, and how to use it to create a temporary view and run Spark SQL queries.

Note that throughout this article, I will use a table and view interchangeably.

1. Syntax of createOrReplaceTempView()

Following is the syntax of the createOrReplaceTempView() method. This method is from the Dataset class. So you can use this method from Spark DataFrame and Dataset objects.

// Syntax of createOrReplaceTempView()
createOrReplaceTempView(viewName): Unit

2. How Does createOrReplaceTempView() work in Spark?

createOrReplaceTempView() in Spark creates a view only if it does not exist; if it exists, it replaces the existing view with the new one. Spark 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 Spark session.
• Once created, you can use it to run SQL queries.
• These temporary views are session-scoped, i.e., valid only during that running Spark session.
• They can’t be shared between sessions.
• These views will be dropped when the session ends unless you create them as Hive tables.
• Use saveAsTable() to materialize the contents of the DataFrame and create a pointer to the data in the metastore.

3. Create a Temporary View

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

If you are using an older version prior to Spark 2.0, you can use registerTempTable() to create a temporary table.

Following are the steps to create a temporary view in Spark and access it.

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

3.1 Create a DataFrame

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

// Create DataFrame
val spark = SparkSession.builder
    .master("local[1]")
    .appName("AIStudyLabs.com")
    .getOrCreate()

val data = Seq(("James", "Smith", "USA", "CA"),
  ("Michael", "Rose", "USA", "NY"),
  ("Robert", "Williams", "USA", "CA"),
  ("Maria", "Jones", "USA", "FL"))

val columns = Seq("firstname", "lastname", "country", "state")
import spark.implicits._
val df = data.toDF(columns: _*)
df.show(false)

3.2 Create Table/View using Spark createOrReplaceTempView()

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

// Create Temporary View/Table
df.createOrReplaceTempView("Person")

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

3.3 Access View using Spark SQL Query

Using SparkSession, you can access Spark SQL capabilities in Apache Spark. 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()

Yields below output.

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