Introduction

Spark SQL select() and selectExpr() are used to select the columns from DataFrame and Dataset.

Both these are transformation operations and return a new DataFrame or Dataset based on the usage of UnTyped and Type columns.

Spark select()

Spark select() is used to select the columns from DataFrame and Dataset. It has two different types of syntaxes.

Below are different ways to select the columns from DataFrame.

val data = Seq(("Java", "20000"), ("Python", "100000"), ("Scala", "3000"))
val df = spark.createDataFrame(data).toDF("language", "users_count")

// Example 1
df.select("language", "users_count as count")

// Example 2
df.select(df("language"), df("users_count").as("count")) 

// Example 3
import org.apache.spark.sql.functions.col
df.select(col("language"), col("users_count")) 

// Example 4
df.select(df.language, df.users_count)

It takes either all column names in a String or Column type, but you cannot do mix and match. Below returns a compile-time error.

df.select("language", col("users_count"))

Spark selectExpr()

Spark SQL function selectExpr() is similar to select(), the difference being it takes a set of SQL expressions in a string to execute. This gives the ability to run SQL-like expressions without creating temporary tables and views.

selectExpr() just has one signature that takes SQL expression in a String and returns a new DataFrame. Note like select() it doesn’t have a signature to take Column type and Dataset return type.

selectExpr(exprs: scala.Predef.String*): org.apache.spark.sql.DataFrame

Example

df.selectExpr("language", "users_count as count")

Below is an interesting difference between select() vs selectExpr().

Seq(("2019-01-23", 1), ("2019-06-24", 2), ("2019-09-20", 3))
  .toDF("date", "increment")
  .selectExpr("date", "increment", "add_months(to_date(date,'yyyy-MM-dd'),cast(increment as int)) as inc_date")
  .show()

Alternatively, you can also write the same statement using expr() SQL function on select. But the same cannot be achieved without expr() function.

Seq(("2019-01-23", 1), ("2019-06-24", 2), ("2019-09-20", 3))
  .toDF("date", "increment")
  .select(col("date"), col("increment"),
    expr("add_months(to_date(date,'yyyy-MM-dd'),cast(increment as int))").as("inc_date"))
  .show()