Introduction

In Apache Spark, the select() and selectExpr() methods are used in Spark SQL to retrieve columns from a DataFrame. These are transformation operations that yield a new DataFrame based on the selection criteria.

Spark select()

In Java Spark, the select() method is used to select columns from a DataFrame. It supports several syntaxes to define the columns to be selected.

Here are different examples of how to select columns from a DataFrame in Java:

import org.apache.spark.sql.SparkSession;
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import static org.apache.spark.sql.functions.col;
import java.util.Arrays;
import java.util.List;

public class SelectExample {
    public static void main(String[] args) {
        SparkSession spark = SparkSession.builder().appName("SelectExample").getOrCreate();

        List<Row> data = Arrays.asList(
            RowFactory.create("Java", "20000"),
            RowFactory.create("Python", "100000"),
            RowFactory.create("Scala", "3000")
        );

        StructType schema = new StructType()
            .add("language", "string")
            .add("users_count", "string");

        Dataset<Row> df = spark.createDataFrame(data, schema);

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

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

        // Example 3
        df.select(col("language"), col("users_count")).show();

        // Example 4
        df.select(df.col("language"), df.col("users_count")).show();
    }
}

When selecting columns, either all column names should be Strings or Column types. Mixing the two results in a compile-time error.

// This will cause a compile-time error
df.select("language", col("users_count"));

Spark selectExpr()

The selectExpr() function in Spark SQL works like the select() function but allows for using SQL expressions within a select statement. This enables SQL-like expression execution without needing temporary tables or views.

The selectExpr() method in Java only takes SQL expressions as Strings and returns a new DataFrame.

Dataset<Row> selectExpr(String... exprs)

Example

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

Here's an example demonstrating the distinction between select() and selectExpr():

List<Row> dateData = Arrays.asList(
    RowFactory.create("2019-01-23", 1),
    RowFactory.create("2019-06-24", 2),
    RowFactory.create("2019-09-20", 3)
);

StructType dateSchema = new StructType()
    .add("date", "string")
    .add("increment", "int");

Dataset<Row> dateDf = spark.createDataFrame(dateData, dateSchema);

dateDf.selectExpr("date", "increment", 
    "add_months(to_date(date, 'yyyy-MM-dd'), cast(increment as int)) as inc_date").show();

Alternatively, the same expression can be achieved using the expr() SQL function embedded within select(), but this isn't possible without using the expr() function:

dateDf.select(
    col("date"), 
    col("increment"), 
    expr("add_months(to_date(date, 'yyyy-MM-dd'), cast(increment as int))").as("inc_date")
).show();