Modulo (a.k.a. remainder) expression.

Boolean AND.

// Scala: The following selects people that are in school and employed at the same time.
people.select( people("inSchool") && people("isEmployed") )

// Java:
people.select( people.col("inSchool").and(people.col("isEmployed")) );

Multiplication of this expression and another expression.

// Scala: The following multiplies a person's height by their weight.
people.select( people("height") * people("weight") )

// Java:
people.select( people.col("height").multiply(people.col("weight")) );

Sum of this expression and another expression.

// Scala: The following selects the sum of a person's height and weight.
people.select( people("height") + people("weight") )

// Java:
people.select( people.col("height").plus(people.col("weight")) );

Subtraction. Subtract the other expression from this expression.

// Scala: The following selects the difference between people's height and their weight.
people.select( people("height") - people("weight") )

// Java:
people.select( people.col("height").minus(people.col("weight")) );

Division this expression by another expression.

// Scala: The following divides a person's height by their weight.
people.select( people("height") / people("weight") )

// Java:
people.select( people.col("height").divide(people.col("weight")) );

Less than.

// Scala: The following selects people younger than 21.
people.select( people("age") < 21 )

// Java:
people.select( people.col("age").lt(21) );

Less than or equal to.

// Scala: The following selects people age 21 or younger than 21.
people.select( people("age") <= 21 )

// Java:
people.select( people.col("age").leq(21) );

Equality test that is safe for null values.

Inequality test.

// Scala:
df.select( df("colA") =!= df("colB") )
df.select( !(df("colA") === df("colB")) )

// Java:
import static org.apache.spark.sql.functions.*;
df.filter( col("colA").notEqual(col("colB")) );

Equality test.

// Scala:
df.filter( df("colA") === df("colB") )

// Java
import static org.apache.spark.sql.functions.*;
df.filter( col("colA").equalTo(col("colB")) );

Greater than.

// Scala: The following selects people older than 21.
people.select( people("age") > 21 )

// Java:
import static org.apache.spark.sql.functions.*;
people.select( people.col("age").gt(21) );

Greater than or equal to an expression.

// Scala: The following selects people age 21 or older than 21.
people.select( people("age") >= 21 )

// Java:
people.select( people.col("age").geq(21) )

Gives the column an alias. Same as as.

// Renames colA to colB in select output.
df.select($"colA".alias("colB"))

Boolean AND.

// Scala: The following selects people that are in school and employed at the same time.
people.select( people("inSchool") && people("isEmployed") )

// Java:
people.select( people.col("inSchool").and(people.col("isEmployed")) );

Extracts a value or values from a complex type. The following types of extraction are supported:

• Given an Array, an integer ordinal can be used to retrieve a single value.
• Given a Map, a key of the correct type can be used to retrieve an individual value.
• Given a Struct, a string fieldName can be used to extract that field.
• Given an Array of Structs, a string fieldName can be used to extract filed of every struct in that array, and return an Array of fields.

Gives the column an alias with metadata.

val metadata: Metadata = ...
df.select($"colA".as("colB", metadata))

Gives the column an alias.

// Renames colA to colB in select output.
df.select($"colA".as("colB"))

If the current column has metadata associated with it, this metadata will be propagated to the new column. If this not desired, use the API as(alias: String, metadata: Metadata) with explicit metadata.

Assigns the given aliases to the results of a table generating function.

// Renames colA to colB in select output.
df.select(explode($"myMap").as("key" :: "value" :: Nil))

(Scala-specific) Assigns the given aliases to the results of a table generating function.

// Renames colA to colB in select output.
df.select(explode($"myMap").as("key" :: "value" :: Nil))

Gives the column an alias.

// Renames colA to colB in select output.
df.select($"colA".as("colB"))

If the current column has metadata associated with it, this metadata will be propagated to the new column. If this not desired, use the API as(alias: String, metadata: Metadata) with explicit metadata.

Provides a type hint about the expected return value of this column. This information can be used by operations such as select on a Dataset to automatically convert the results into the correct JVM types.

Returns a sort expression based on ascending order of the column.

// Scala: sort a DataFrame by age column in ascending order.
df.sort(df("age").asc)

// Java
df.sort(df.col("age").asc());

Returns a sort expression based on ascending order of the column, and null values return before non-null values.

// Scala: sort a DataFrame by age column in ascending order and null values appearing first.
df.sort(df("age").asc_nulls_first)

// Java
df.sort(df.col("age").asc_nulls_first());

Returns a sort expression based on ascending order of the column, and null values appear after non-null values.

// Scala: sort a DataFrame by age column in ascending order and null values appearing last.
df.sort(df("age").asc_nulls_last)

// Java
df.sort(df.col("age").asc_nulls_last());

True if the current column is between the lower bound and upper bound, inclusive.

Compute bitwise AND of this expression with another expression.

df.select($"colA".bitwiseAND($"colB"))

Compute bitwise OR of this expression with another expression.

df.select($"colA".bitwiseOR($"colB"))

Compute bitwise XOR of this expression with another expression.

df.select($"colA".bitwiseXOR($"colB"))

Casts the column to a different data type, using the canonical string representation of the type. The supported types are: string, boolean, byte, short, int, long, float, double, decimal, date, timestamp.

// Casts colA to integer.
df.select(df("colA").cast("int"))

Casts the column to a different data type.

// Casts colA to IntegerType.
import org.apache.spark.sql.types.IntegerType
df.select(df("colA").cast(IntegerType))

// equivalent to
df.select(df("colA").cast("int"))

Contains the other element. Returns a boolean column based on a string match.

Returns a sort expression based on the descending order of the column.

// Scala
df.sort(df("age").desc)

// Java
df.sort(df.col("age").desc());

Returns a sort expression based on the descending order of the column, and null values appear before non-null values.

// Scala: sort a DataFrame by age column in descending order and null values appearing first.
df.sort(df("age").desc_nulls_first)

// Java
df.sort(df.col("age").desc_nulls_first());

Returns a sort expression based on the descending order of the column, and null values appear after non-null values.

// Scala: sort a DataFrame by age column in descending order and null values appearing last.
df.sort(df("age").desc_nulls_last)

// Java
df.sort(df.col("age").desc_nulls_last());

Division this expression by another expression.

// Scala: The following divides a person's height by their weight.
people.select( people("height") / people("weight") )

// Java:
people.select( people.col("height").divide(people.col("weight")) );

An expression that drops fields in StructType by name. This is a no-op if schema doesn't contain field name(s).

val df = sql("SELECT named_struct('a', 1, 'b', 2) struct_col")
df.select($"struct_col".dropFields("b"))
// result: {"a":1}

val df = sql("SELECT named_struct('a', 1, 'b', 2) struct_col")
df.select($"struct_col".dropFields("c"))
// result: {"a":1,"b":2}

val df = sql("SELECT named_struct('a', 1, 'b', 2, 'c', 3) struct_col")
df.select($"struct_col".dropFields("b", "c"))
// result: {"a":1}

val df = sql("SELECT named_struct('a', 1, 'b', 2) struct_col")
df.select($"struct_col".dropFields("a", "b"))
// result: org.apache.spark.sql.AnalysisException: [DATATYPE_MISMATCH.CANNOT_DROP_ALL_FIELDS] Cannot resolve "update_fields(struct_col, dropfield(), dropfield())" due to data type mismatch: Cannot drop all fields in struct.;

val df = sql("SELECT CAST(NULL AS struct<a:int,b:int>) struct_col")
df.select($"struct_col".dropFields("b"))
// result: null of type struct<a:int>

val df = sql("SELECT named_struct('a', 1, 'b', 2, 'b', 3) struct_col")
df.select($"struct_col".dropFields("b"))
// result: {"a":1}

val df = sql("SELECT named_struct('a', named_struct('a', 1, 'b', 2)) struct_col")
df.select($"struct_col".dropFields("a.b"))
// result: {"a":{"a":1}}

val df = sql("SELECT named_struct('a', named_struct('b', 1), 'a', named_struct('c', 2)) struct_col")
df.select($"struct_col".dropFields("a.c"))
// result: org.apache.spark.sql.AnalysisException: Ambiguous reference to fields

This method supports dropping multiple nested fields directly e.g.

val df = sql("SELECT named_struct('a', named_struct('a', 1, 'b', 2)) struct_col")
df.select($"struct_col".dropFields("a.b", "a.c"))
// result: {"a":{"a":1}}

However, if you are going to drop multiple nested fields, it is more optimal to extract out the nested struct before dropping multiple fields from it e.g.

val df = sql("SELECT named_struct('a', named_struct('a', 1, 'b', 2)) struct_col")
df.select($"struct_col".withField("a", $"struct_col.a".dropFields("b", "c")))
// result: {"a":{"a":1}}

String ends with another string literal. Returns a boolean column based on a string match.

String ends with. Returns a boolean column based on a string match.

Equality test that is safe for null values.

Equality test.

// Scala:
df.filter( df("colA") === df("colB") )

// Java
import static org.apache.spark.sql.functions.*;
df.filter( col("colA").equalTo(col("colB")) );

Prints the expression to the console for debugging purposes.

Greater than or equal to an expression.

// Scala: The following selects people age 21 or older than 21.
people.select( people("age") >= 21 )

// Java:
people.select( people.col("age").geq(21) )

An expression that gets a field by name in a StructType.

An expression that gets an item at position ordinal out of an array, or gets a value by key key in a MapType.

Greater than.

// Scala: The following selects people older than 21.
people.select( people("age") > lit(21) )

// Java:
import static org.apache.spark.sql.functions.*;
people.select( people.col("age").gt(21) );

SQL ILIKE expression (case insensitive LIKE).

A boolean expression that is evaluated to true if the value of this expression is contained by the provided collection.

Note: Since the type of the elements in the collection are inferred only during the run time, the elements will be "up-casted" to the most common type for comparison. For eg: 1) In the case of "Int vs String", the "Int" will be up-casted to "String" and the comparison will look like "String vs String". 2) In the case of "Float vs Double", the "Float" will be up-casted to "Double" and the comparison will look like "Double vs Double"

A boolean expression that is evaluated to true if the value of this expression is contained by the provided collection.

Note: Since the type of the elements in the collection are inferred only during the run time, the elements will be "up-casted" to the most common type for comparison. For eg: 1) In the case of "Int vs String", the "Int" will be up-casted to "String" and the comparison will look like "String vs String". 2) In the case of "Float vs Double", the "Float" will be up-casted to "Double" and the comparison will look like "Double vs Double"

True if the current expression is NaN.

True if the current expression is NOT null.

True if the current expression is null.

A boolean expression that is evaluated to true if the value of this expression is contained by the evaluated values of the arguments.

Note: Since the type of the elements in the list are inferred only during the run time, the elements will be "up-casted" to the most common type for comparison. For eg: 1) In the case of "Int vs String", the "Int" will be up-casted to "String" and the comparison will look like "String vs String". 2) In the case of "Float vs Double", the "Float" will be up-casted to "Double" and the comparison will look like "Double vs Double"

Less than or equal to.

// Scala: The following selects people age 21 or younger than 21.
people.select( people("age") <= 21 )

// Java:
people.select( people.col("age").leq(21) );

SQL like expression. Returns a boolean column based on a SQL LIKE match.

Less than.

// Scala: The following selects people younger than 21.
people.select( people("age") < 21 )

// Java:
people.select( people.col("age").lt(21) );

Subtraction. Subtract the other expression from this expression.

// Scala: The following selects the difference between people's height and their weight.
people.select( people("height") - people("weight") )

// Java:
people.select( people.col("height").minus(people.col("weight")) );

Modulo (a.k.a. remainder) expression.

Multiplication of this expression and another expression.

// Scala: The following multiplies a person's height by their weight.
people.select( people("height") * people("weight") )

// Java:
people.select( people.col("height").multiply(people.col("weight")) );

Gives the column a name (alias).

// Renames colA to colB in select output.
df.select($"colA".name("colB"))

If the current column has metadata associated with it, this metadata will be propagated to the new column. If this not desired, use the API as(alias: String, metadata: Metadata) with explicit metadata.

Inequality test.

// Scala:
df.select( df("colA") !== df("colB") )
df.select( !(df("colA") === df("colB")) )

// Java:
import static org.apache.spark.sql.functions.*;
df.filter( col("colA").notEqual(col("colB")) );

Boolean OR.

// Scala: The following selects people that are in school or employed.
people.filter( people("inSchool") || people("isEmployed") )

// Java:
people.filter( people.col("inSchool").or(people.col("isEmployed")) );

Evaluates a list of conditions and returns one of multiple possible result expressions. If otherwise is not defined at the end, null is returned for unmatched conditions.

// Example: encoding gender string column into integer.

// Scala:
people.select(when(people("gender") === "male", 0)
  .when(people("gender") === "female", 1)
  .otherwise(2))

// Java:
people.select(when(col("gender").equalTo("male"), 0)
  .when(col("gender").equalTo("female"), 1)
  .otherwise(2))

Defines an empty analytic clause. In this case the analytic function is applied and presented for all rows in the result set.

df.select(
  sum("price").over(),
  avg("price").over()
)

Defines a windowing column.

val w = Window.partitionBy("name").orderBy("id")
df.select(
  sum("price").over(w.rangeBetween(Window.unboundedPreceding, 2)),
  avg("price").over(w.rowsBetween(Window.currentRow, 4))
)

Sum of this expression and another expression.

// Scala: The following selects the sum of a person's height and weight.
people.select( people("height") + people("weight") )

// Java:
people.select( people.col("height").plus(people.col("weight")) );

SQL RLIKE expression (LIKE with Regex). Returns a boolean column based on a regex match.

String starts with another string literal. Returns a boolean column based on a string match.

String starts with. Returns a boolean column based on a string match.

An expression that returns a substring.

An expression that returns a substring.

Casts the column to a different data type and the result is null on failure.

// Casts colA to integer.
df.select(df("colA").try_cast("int"))

Casts the column to a different data type and the result is null on failure.

// Casts colA to IntegerType.
import org.apache.spark.sql.types.IntegerType
df.select(df("colA").try_cast(IntegerType))

// equivalent to
df.select(df("colA").try_cast("int"))

Inversion of boolean expression, i.e. NOT.

// Scala: select rows that are not active (isActive === false)
df.filter( !df("isActive") )

// Java:
import static org.apache.spark.sql.functions.*;
df.filter( not(df.col("isActive")) );

Unary minus, i.e. negate the expression.

// Scala: select the amount column and negates all values.
df.select( -df("amount") )

// Java:
import static org.apache.spark.sql.functions.*;
df.select( negate(col("amount") );

Evaluates a list of conditions and returns one of multiple possible result expressions. If otherwise is not defined at the end, null is returned for unmatched conditions.

// Example: encoding gender string column into integer.

// Scala:
people.select(when(people("gender") === "male", 0)
  .when(people("gender") === "female", 1)
  .otherwise(2))

// Java:
people.select(when(col("gender").equalTo("male"), 0)
  .when(col("gender").equalTo("female"), 1)
  .otherwise(2))

An expression that adds/replaces field in StructType by name.

val df = sql("SELECT named_struct('a', 1, 'b', 2) struct_col")
df.select($"struct_col".withField("c", lit(3)))
// result: {"a":1,"b":2,"c":3}

val df = sql("SELECT named_struct('a', 1, 'b', 2) struct_col")
df.select($"struct_col".withField("b", lit(3)))
// result: {"a":1,"b":3}

val df = sql("SELECT CAST(NULL AS struct<a:int,b:int>) struct_col")
df.select($"struct_col".withField("c", lit(3)))
// result: null of type struct<a:int,b:int,c:int>

val df = sql("SELECT named_struct('a', 1, 'b', 2, 'b', 3) struct_col")
df.select($"struct_col".withField("b", lit(100)))
// result: {"a":1,"b":100,"b":100}

val df = sql("SELECT named_struct('a', named_struct('a', 1, 'b', 2)) struct_col")
df.select($"struct_col".withField("a.c", lit(3)))
// result: {"a":{"a":1,"b":2,"c":3}}

val df = sql("SELECT named_struct('a', named_struct('b', 1), 'a', named_struct('c', 2)) struct_col")
df.select($"struct_col".withField("a.c", lit(3)))
// result: org.apache.spark.sql.AnalysisException: Ambiguous reference to fields

This method supports adding/replacing nested fields directly e.g.

val df = sql("SELECT named_struct('a', named_struct('a', 1, 'b', 2)) struct_col")
df.select($"struct_col".withField("a.c", lit(3)).withField("a.d", lit(4)))
// result: {"a":{"a":1,"b":2,"c":3,"d":4}}

However, if you are going to add/replace multiple nested fields, it is more optimal to extract out the nested struct before adding/replacing multiple fields e.g.

val df = sql("SELECT named_struct('a', named_struct('a', 1, 'b', 2)) struct_col")
df.select($"struct_col".withField("a", $"struct_col.a".withField("c", lit(3)).withField("d", lit(4))))
// result: {"a":{"a":1,"b":2,"c":3,"d":4}}

Boolean OR.

// Scala: The following selects people that are in school or employed.
people.filter( people("inSchool") || people("isEmployed") )

// Java:
people.filter( people.col("inSchool").or(people.col("isEmployed")) );

Inequality test.

// Scala:
df.select( df("colA") !== df("colB") )
df.select( !(df("colA") === df("colB")) )

// Java:
import static org.apache.spark.sql.functions.*;
df.filter( col("colA").notEqual(col("colB")) );