functions

object functions

Provides utility functions that generate Column expressions that you can pass to DataFrame transformation methods. These functions generate references to columns, literals, and SQL expressions (e.g. "c + 1").

This object also provides functions that correspond to Snowflake system-defined functions (built-in functions), including functions for aggregation and window functions.

The following examples demonstrate the use of some of these functions:

// Use columns and literals in expressions.
df.select(col("c") + lit(1))

// Call system-defined (built-in) functions.
// This example calls the function that corresponds to the ADD_MONTHS() SQL function.
df.select(add_months(col("d"), lit(3)))

// Call system-defined functions that have no corresponding function in the functions object.
// This example calls the RADIANS() SQL function, passing in values from the column "e".
df.select(callBuiltin("radians", col("e")))

// Call a user-defined function (UDF) by name.
df.select(callUDF("some_func", col("c")))

// Register and call an anonymous UDF.
val myudf = udf((x:Int) => x + x)
df.select(myudf(col("c")))

// Evaluate an SQL expression
df.select(sqlExpr("c + 1"))

For functions that accept scala types, e.g. callUdf, callBuiltin, lit(), the mapping from scala types to Snowflake types is as follows:

String => String
Byte => TinyInt
Int => Int
Short => SmallInt
Long => BigInt
Float => Float
Double => Double
Decimal => Number
Boolean => Boolean
Array => Array
Timestamp => Timestamp
Date => Date

Since: 0.1.0

Linear Supertypes

AnyRef, Any

Ordering

Alphabetic
By Inheritance

Inherited

functions
AnyRef
Any

Hide All
Show All

Visibility

Public
Protected

Type Members

case class builtin(functionName: String) extends Product with Serializable
Function object to invoke a Snowflake builtin.
Function object to invoke a Snowflake builtin. Use this to invoke any builtins not explicitly listed in this object.
Example
```
val repeat = functions.builtin("repeat")
df.select(repeat(col("col_1"), 3))
```
Since
0.1.0

Value Members

final def !=(arg0: Any): Boolean
Definition Classes
AnyRef → Any
final def ##: Int
Definition Classes
AnyRef → Any
final def ==(arg0: Any): Boolean
Definition Classes
AnyRef → Any
def abs(e: Column): Column
Returns the absolute value of a numeric expression.
Returns the absolute value of a numeric expression.
Since
0.1.0
def acos(e: Column): Column
Computes the inverse cosine (arc cosine) of its input; the result is a number in the interval [-pi, pi].
Computes the inverse cosine (arc cosine) of its input; the result is a number in the interval [-pi, pi].
Since
0.1.0
def add_months(startDate: Column, numMonths: Column): Column
Adds or subtracts a specified number of months to a date or timestamp, preserving the end-of-month information.
Adds or subtracts a specified number of months to a date or timestamp, preserving the end-of-month information.
Since
0.1.0
def any_value(e: Column): Column
Returns a non-deterministic value for the specified column.
Returns a non-deterministic value for the specified column.
Since
0.12.0
def approx_count_distinct(e: Column): Column
Uses HyperLogLog to return an approximation of the distinct cardinality of the input (i.e.
Uses HyperLogLog to return an approximation of the distinct cardinality of the input (i.e. returns an approximation of COUNT(DISTINCT col)).
Since
0.1.0
def approx_percentile(col: Column, percentile: Double): Column
Returns an approximated value for the desired percentile.
Returns an approximated value for the desired percentile. This function uses the t-Digest algorithm.
Since
0.2.0
def approx_percentile_accumulate(col: Column): Column
Returns the internal representation of the t-Digest state (as a JSON object) at the end of aggregation.
Returns the internal representation of the t-Digest state (as a JSON object) at the end of aggregation. This function uses the t-Digest algorithm.
Since
0.2.0
def approx_percentile_combine(state: Column): Column
Combines (merges) percentile input states into a single output state.
Combines (merges) percentile input states into a single output state.
This allows scenarios where APPROX_PERCENTILE_ACCUMULATE is run over horizontal partitions of the same table, producing an algorithm state for each table partition. These states can later be combined using APPROX_PERCENTILE_COMBINE, producing the same output state as a single run of APPROX_PERCENTILE_ACCUMULATE over the entire table.
Since
0.2.0
def approx_percentile_estimate(state: Column, percentile: Double): Column
Returns the desired approximated percentile value for the specified t-Digest state.
Returns the desired approximated percentile value for the specified t-Digest state. APPROX_PERCENTILE_ESTIMATE(APPROX_PERCENTILE_ACCUMULATE(.)) is equivalent to APPROX_PERCENTILE(.).
Since
0.2.0

def array(c: Column*): Column

Returns an ARRAY constructed from zero, one, or more inputs.

Example:

  val df = session.createDataFrame(Seq((1, 2, 3), (4, 5, 6))).toDF("id")
  df.select(array(col("a"), col("b")).as("id")).show()

 --------
|"ID"  |
--------
|[     |
|  1,  |
|  2   |
|]     |
|[     |
|  4,  |
|  5   |
|]     |
--------

c: Columns to build the array.
returns: The array.

Since: 1.14.0

def array_agg(col: Column): Column
Returns the input values, pivoted into an ARRAY.
Returns the input values, pivoted into an ARRAY. If the input is empty, an empty ARRAY is returned.
Since
0.2.0
def array_append(array: Column, element: Column): Column
Returns an ARRAY containing all elements from the source ARRAYas well as the new element.
Returns an ARRAY containing all elements from the source ARRAYas well as the new element. The new element is located at end of the ARRAY.
array
The column containing the source ARRAY.
element
The column containing the element to be appended. The element may be of almost any data type. The data type does not need to match the data type(s) of the existing elements in the ARRAY.
Since
0.2.0
def array_cat(array1: Column, array2: Column): Column
Returns the concatenation of two ARRAYs.
Returns the concatenation of two ARRAYs.
array1
Column containing the source ARRAY.
array2
Column containing the ARRAY to be appended to array1 .
Since
0.2.0
def array_compact(array: Column): Column
Returns a compacted ARRAY with missing and null values removed, effectively converting sparse arrays into dense arrays.
Returns a compacted ARRAY with missing and null values removed, effectively converting sparse arrays into dense arrays.
Since
0.2.0
def array_construct(cols: Column*): Column
Returns an ARRAY constructed from zero, one, or more inputs.
Returns an ARRAY constructed from zero, one, or more inputs.
cols
Columns containing the values (or expressions that evaluate to values). The values do not all need to be of the same data type.
Since
0.2.0
def array_construct_compact(cols: Column*): Column
Returns an ARRAY constructed from zero, one, or more inputs; the constructed ARRAY omits any NULL input values.
Returns an ARRAY constructed from zero, one, or more inputs; the constructed ARRAY omits any NULL input values.
cols
Columns containing the values (or expressions that evaluate to values). The values do not all need to be of the same data type.
Since
0.2.0
def array_contains(variant: Column, array: Column): Column
Returns true if the specified VARIANT is found in the specified ARRAY.
Returns true if the specified VARIANT is found in the specified ARRAY.
variant
Column containing the VARIANT to find.
array
Column containing the ARRAY to search.
Since
0.2.0

def array_flatten(array: Column): Column

Flattens an array of arrays into a single array, removing only one level of nesting.

Examples

Example 1: Flattens a two-level nested array into a single array of elements.

val df = Seq(
  Array(Array(1, 2), Array(3, 4)),
  Array(Array(5, 6, 7), Array(8)),
  Array(Array.empty[Int], Array(9, 10)),
).toDF("a")

df.select(array_flatten(col("a"))).show()
--------------------------
|"ARRAY_FLATTEN(""A"")"  |
--------------------------
|[                       |
|  1,                    |
|  2,                    |
|  3,                    |
|  4                     |
|]                       |
|[                       |
|  5,                    |
|  6,                    |
|  7,                    |
|  8                     |
|]                       |
|[                       |
|  9,                    |
|  10                    |
|]                       |
--------------------------

Example 2: Flattens only one level of a three-level nested array.

val df = Seq(
  Array(Array(Array(1, 2), Array(3)), Array(Array(4, 5)))
).toDF("a")

df.select(array_flatten(col("a"))).show()
--------------------------
|"ARRAY_FLATTEN(""A"")"  |
--------------------------
|[                       |
|  [                     |
|    1,                  |
|    2                   |
|  ],                    |
|  [                     |
|    3                   |
|  ],                    |
|  [                     |
|    4,                  |
|    5                   |
|  ]                     |
|]                       |
--------------------------

array

Column containing the array of arrays to flatten.

If any element of array is not an ARRAY, the function throws an error.
If array is NULL, the function returns NULL.

returns

A column containing the flattened array.

Since: 1.17.0

def array_insert(array: Column, pos: Column, element: Column): Column
Returns an ARRAY containing all elements from the source ARRAY as well as the new element.
Returns an ARRAY containing all elements from the source ARRAY as well as the new element.
array
Column containing the source ARRAY.
pos
Column containing a (zero-based) position in the source ARRAY. The new element is inserted at this position. The original element from this position (if any) and all subsequent elements (if any) are shifted by one position to the right in the resulting array (i.e. inserting at position 0 has the same effect as using array_prepend). A negative position is interpreted as an index from the back of the array (e.g. -1 results in insertion before the last element in the array).
element
Column containing the element to be inserted. The new element is located at position pos . The relative order of the other elements from the source array is preserved.
Since
0.2.0
def array_intersection(col1: Column, col2: Column): Column
Returns an ARRAY that contains the matching elements in the two input ARRAYs.
Returns an ARRAY that contains the matching elements in the two input ARRAYs.
Since
0.1.0
def array_position(variant: Column, array: Column): Column
Returns the index of the first occurrence of an element in an ARRAY.
Returns the index of the first occurrence of an element in an ARRAY.
variant
Column containing the VARIANT value that you want to find. The function searches for the first occurrence of this value in the array.
array
Column containing the ARRAY to be searched.
Since
0.2.0
def array_prepend(array: Column, element: Column): Column
Returns an ARRAY containing the new element as well as all elements from the source ARRAY.
Returns an ARRAY containing the new element as well as all elements from the source ARRAY. The new element is positioned at the beginning of the ARRAY.
array
Column containing the source ARRAY.
element
Column containing the element to be prepended.
Since
0.2.0
def array_size(array: Column): Column
Returns the size of the input ARRAY.
Returns the size of the input ARRAY.
If the specified column contains a VARIANT value that contains an ARRAY, the size of the ARRAY is returned; otherwise, NULL is returned if the value is not an ARRAY.
Since
0.2.0
def array_slice(array: Column, from: Column, to: Column): Column
Returns an ARRAY constructed from a specified subset of elements of the input ARRAY.
Returns an ARRAY constructed from a specified subset of elements of the input ARRAY.
array
Column containing the source ARRAY.
from
Column containing a position in the source ARRAY. The position of the first element is 0 . Elements from positions less than this parameter are not included in the resulting ARRAY.
to
Column containing a position in the source ARRAY. Elements from positions equal to or greater than this parameter are not included in the resulting array.
Since
0.2.0
def array_to_string(array: Column, separator: Column): Column
Returns an input ARRAY converted to a string by casting all values to strings (using TO_VARCHAR) and concatenating them (using the string from the second argument to separate the elements).
Returns an input ARRAY converted to a string by casting all values to strings (using TO_VARCHAR) and concatenating them (using the string from the second argument to separate the elements).
array
Column containing the ARRAY of elements to convert to a string.
separator
Column containing the string to put between each element (e.g. a space, comma, or other human-readable separator).
Since
0.2.0
def arrays_overlap(a1: Column, a2: Column): Column
Compares whether two arrays have at least one element in common.
Compares whether two arrays have at least one element in common. Returns TRUE if there is at least one element in common; otherwise returns FALSE. The function is NULL-safe, meaning it treats NULLs as known values for comparing equality.
Since
0.1.0
final def asInstanceOf[T0]: T0
Definition Classes
Any
def as_array(variant: Column): Column
Casts a VARIANT value to an array.
Casts a VARIANT value to an array.
Since
0.2.0
def as_binary(variant: Column): Column
Casts a VARIANT value to a binary string.
Casts a VARIANT value to a binary string.
Since
0.2.0
def as_char(variant: Column): Column
Casts a VARIANT value to a string.
Casts a VARIANT value to a string. Does not convert values of other types into string.
Since
0.2.0
def as_date(variant: Column): Column
Casts a VARIANT value to a date.
Casts a VARIANT value to a date. Does not convert from timestamps.
Since
0.2.0
def as_decimal(variant: Column, precision: Int, scale: Int): Column
Casts a VARIANT value to a fixed-point decimal (does not match floating-point values), with precision and scale.
Casts a VARIANT value to a fixed-point decimal (does not match floating-point values), with precision and scale.
Since
0.2.0
def as_decimal(variant: Column, precision: Int): Column
Casts a VARIANT value to a fixed-point decimal (does not match floating-point values), with precision.
Casts a VARIANT value to a fixed-point decimal (does not match floating-point values), with precision.
Since
0.2.0
def as_decimal(variant: Column): Column
Casts a VARIANT value to a fixed-point decimal (does not match floating-point values).
Casts a VARIANT value to a fixed-point decimal (does not match floating-point values).
Since
0.2.0
def as_double(variant: Column): Column
Casts a VARIANT value to a floating-point value.
Casts a VARIANT value to a floating-point value.
Since
0.2.0
def as_integer(variant: Column): Column
Casts a VARIANT value to an integer.
Casts a VARIANT value to an integer. Does not match non-integer values.
Since
0.2.0
def as_number(variant: Column, precision: Int, scale: Int): Column
Casts a VARIANT value to a fixed-point decimal (does not match floating-point values), with precision and scale.
Casts a VARIANT value to a fixed-point decimal (does not match floating-point values), with precision and scale.
Since
0.2.0
def as_number(variant: Column, precision: Int): Column
Casts a VARIANT value to a fixed-point decimal (does not match floating-point values), with precision.
Casts a VARIANT value to a fixed-point decimal (does not match floating-point values), with precision.
Since
0.2.0
def as_number(variant: Column): Column
Casts a VARIANT value to a fixed-point decimal (does not match floating-point values).
Casts a VARIANT value to a fixed-point decimal (does not match floating-point values).
Since
0.2.0
def as_object(variant: Column): Column
Casts a VARIANT value to an object.
Casts a VARIANT value to an object.
Since
0.2.0
def as_real(variant: Column): Column
Casts a VARIANT value to a floating-point value.
Casts a VARIANT value to a floating-point value.
Since
0.2.0
def as_time(variant: Column): Column
Casts a VARIANT value to a time value.
Casts a VARIANT value to a time value. Does not convert from timestamps.
Since
0.2.0
def as_timestamp_ltz(variant: Column): Column
Casts a VARIANT value to a TIMESTAMP value with local timezone.
Casts a VARIANT value to a TIMESTAMP value with local timezone.
Since
0.2.0
def as_timestamp_ntz(variant: Column): Column
Casts a VARIANT value to a TIMESTAMP value with no timezone.
Casts a VARIANT value to a TIMESTAMP value with no timezone.
Since
0.2.0
def as_timestamp_tz(variant: Column): Column
Casts a VARIANT value to a TIMESTAMP value with timezone.
Casts a VARIANT value to a TIMESTAMP value with timezone.
Since
0.2.0
def as_varchar(variant: Column): Column
Casts a VARIANT value to a string.
Casts a VARIANT value to a string. Does not convert values of other types into string.
Since
0.2.0
def asc(colName: String): Column
Returns a Column expression with values sorted in ascending order.
Returns a Column expression with values sorted in ascending order. Example:
```
  val df = session.createDataFrame(Seq(3, 2, 1)).toDF("id")
  df.sort(asc("id")).show()

--------
|"ID"  |
--------
|1     |
|2     |
|3     |
--------
```
colName
Column name.
returns
Column object ordered in an ascending manner.
Since
1.14.0
def ascii(e: Column): Column
Returns the ASCII code for the first character of a string.
Returns the ASCII code for the first character of a string. If the string is empty, a value of 0 is returned.
Since
0.1.0
def asin(e: Column): Column
Computes the inverse sine (arc sine) of its argument; the result is a number in the interval [-pi, pi].
Computes the inverse sine (arc sine) of its argument; the result is a number in the interval [-pi, pi].
Since
0.1.0
def atan(e: Column): Column
Computes the inverse tangent (arc tangent) of its argument; the result is a number in the interval [-pi, pi].
Computes the inverse tangent (arc tangent) of its argument; the result is a number in the interval [-pi, pi].
Since
0.1.0
def atan2(y: Column, x: Column): Column
Computes the inverse tangent (arc tangent) of the ratio of its two arguments.
Computes the inverse tangent (arc tangent) of the ratio of its two arguments.
Since
0.1.0
def avg(e: Column): Column
Returns the average of non-NULL records.
Returns the average of non-NULL records. If all records inside a group are NULL, the function returns NULL.
Since
0.1.0
def base64(col: Column): Column
Computes the BASE64 encoding of a column and returns it as a string column.
Computes the BASE64 encoding of a column and returns it as a string column. This is the reverse of unbase64. Example
```
 val df = session.createDataFrame(Seq("test")).toDF("a")
 df.select(base64(col("a")).as("base64")).show()
------------
|"BASE64"  |
------------
|dGVzdA==  |
------------
```
returns
base64 encoded value of the given input column.
Since
1.14.0
def bitnot(e: Column): Column
Returns the bitwise negation of a numeric expression.
Returns the bitwise negation of a numeric expression.
Since
0.1.0
def bitshiftleft(e: Column, numBits: Column): Column
Shifts the bits for a numeric expression numBits positions to the left.
Shifts the bits for a numeric expression numBits positions to the left.
Since
0.1.0
def bitshiftright(e: Column, numBits: Column): Column
Shifts the bits for a numeric expression numBits positions to the right.
Shifts the bits for a numeric expression numBits positions to the right.
Since
0.1.0
def callBuiltin(functionName: String, args: Any*): Column
Invokes a built-in snowflake function with the specified name and arguments.
Invokes a built-in snowflake function with the specified name and arguments. Arguments can be of two types
1. Column, or
b. Basic types such as Int, Long, Double, Decimal etc. which are converted to Snowpark literals.
Since
0.1.0
def callUDF(udfName: String, cols: Any*): Column
Calls a user-defined function (UDF) by name.
Calls a user-defined function (UDF) by name.
Since
0.1.0
def ceil(e: Column): Column
Returns values from the specified column rounded to the nearest equal or larger integer.
Returns values from the specified column rounded to the nearest equal or larger integer.
Since
0.1.0
def char(col: Column): Column
Converts a Unicode code point (including 7-bit ASCII) into the character that matches the input Unicode.
Converts a Unicode code point (including 7-bit ASCII) into the character that matches the input Unicode.
Since
0.1.0
def charindex(targetExpr: Column, sourceExpr: Column, position: Column): Column
Searches for targetExpr in sourceExpr and, if successful, returns the position (1-based) of the targetExpr in sourceExpr.
Searches for targetExpr in sourceExpr and, if successful, returns the position (1-based) of the targetExpr in sourceExpr.
Since
0.1.0
def charindex(targetExpr: Column, sourceExpr: Column): Column
Searches for targetExpr in sourceExpr and, if successful, returns the position (1-based) of the targetExpr in sourceExpr.
Searches for targetExpr in sourceExpr and, if successful, returns the position (1-based) of the targetExpr in sourceExpr.
Since
0.1.0
def check_json(col: Column): Column
Checks the validity of a JSON document.
Checks the validity of a JSON document. If the input string is a valid JSON document or a NULL (i.e. no error would occur when parsing the input string), the function returns NULL. In case of a JSON parsing error, the function returns a string that contains the error message.
Since
0.2.0
def check_xml(col: Column): Column
Checks the validity of an XML document.
Checks the validity of an XML document. If the input string is a valid XML document or a NULL (i.e. no error would occur when parsing the input string), the function returns NULL. In case of an XML parsing error, the output string contains the error message.
Since
0.2.0
def clone(): AnyRef
Attributes
protected[lang]
Definition Classes
AnyRef
Annotations
@throws(classOf[java.lang.CloneNotSupportedException]) @HotSpotIntrinsicCandidate() @native()
def coalesce(e: Column*): Column
Returns the first non-NULL expression among its arguments, or NULL if all its arguments are NULL.
Returns the first non-NULL expression among its arguments, or NULL if all its arguments are NULL.
Since
0.1.0
def col(df: DataFrame): Column
Generate a Column representing the result of the input DataFrame.
Generate a Column representing the result of the input DataFrame. The parameter df should have one column and must produce one row. Is an alias of toScalar.
For Example:
```
import functions._
val df1 = session.sql("select * from values(1,1,1),(2,2,3) as T(c1,c2,c3)")
val df2 = session.sql("select * from values(2) as T(a)")
df1.select(Column("c1"), col(df2)).show()
df1.filter(Column("c1") < col(df2)).show()
```
Since
0.2.0
def col(colName: String): Column
Returns the Column with the specified name.
Returns the Column with the specified name.
Since
0.1.0
def collate(expr: Column, collationSpec: String): Column
Returns a copy of expr, but with the specified collationSpec property instead of the original collation specification property.
Returns a copy of expr, but with the specified collationSpec property instead of the original collation specification property.
Collation Specification is specified here
Since
0.1.0
def collation(expr: Column): Column
Returns the collation specification of expr.
Returns the collation specification of expr.
Since
0.1.0

def collect_list(s: String): Column

Returns the input values, pivoted into an ARRAY.

Returns the input values, pivoted into an ARRAY. If the input is empty, an empty ARRAY is returned.

Example::

>>> df = session.create_dataframe([[1], [2], [3], [1]], schema=["a"])
>>> df.select(array_agg("a", True).alias("result")).show()
------------
|"RESULT"  |
------------
|[         |
|  1,      |
|  2,      |
|  3       |
|]         |
------------

s: Column name to be collected.
returns: The array.

Since: 1.14.0

def collect_list(c: Column): Column

Returns the input values, pivoted into an ARRAY.

Returns the input values, pivoted into an ARRAY. If the input is empty, an empty ARRAY is returned. <pr> Example::

>>> df = session.create_dataframe([[1], [2], [3], [1]], schema=["a"])
>>> df.select(array_agg("a", True).alias("result")).show()
------------
|"RESULT"  |
------------
|[         |
|  1,      |
|  2,      |
|  3       |
|]         |
------------

</pr>

c: Column to be collect.
returns: The array.

Since: 1.14.0

def collect_set(e: String): Column
Aggregate function: returns a set of objects with duplicate elements eliminated.
Aggregate function: returns a set of objects with duplicate elements eliminated. Returns the input values, pivoted into an ARRAY. If the input is empty, an empty ARRAY is returned.
Example::
```
>>> df = session.create_dataframe([[1], [2], [3], [1]], schema=["a"])
>>> df.select(array_agg("a", True).alias("result")).show()
------------
|"RESULT"  |
------------
|[         |
|  1,      |
|  2,      |
|  3       |
|]         |
------------
```
e
The column to collect the list values
returns
A list with unique values
Since
1.15.0
def collect_set(e: Column): Column
Aggregate function: returns a set of objects with duplicate elements eliminated.
Aggregate function: returns a set of objects with duplicate elements eliminated. Returns the input values, pivoted into an ARRAY. If the input is empty, an empty ARRAY is returned.
Example::
```
>>> df = session.create_dataframe([[1], [2], [3], [1]], schema=["a"])
>>> df.select(array_agg("a", True).alias("result")).show()
------------
|"RESULT"  |
------------
|[         |
|  1,      |
|  2,      |
|  3       |
|]         |
------------
```
e
The column to collect the list values
returns
A list with unique values
Since
1.15.0
def column(colName: String): Column
Returns a Column with the specified name.
Returns a Column with the specified name. Alias for col.
Since
0.1.0
def concat(exprs: Column*): Column
Concatenates one or more strings, or concatenates one or more binary values.
Concatenates one or more strings, or concatenates one or more binary values. If any of the values is null, the result is also null.
Since
0.1.0
def concat_ws(separator: Column, exprs: Column*): Column
Concatenates two or more strings, or concatenates two or more binary values.
Concatenates two or more strings, or concatenates two or more binary values. If any of the values is null, the result is also null.
Since
0.1.0

def concat_ws_ignore_nulls(separator: String, exprs: Column*): Column

Concatenates two or more strings ignoring any null values.

Unlike concat_ws, this function automatically filters out null values before concatenation.

Examples

val df = session.createDataFrame(
  Seq(
    Row("Hello", "World", null),
    Row(null, null, null),
    Row("Hello", null, null),
  ),
  StructType(
    StructField("A", StringType),
    StructField("B", StringType),
    StructField("C", StringType),
  )
)

df.select(
  concat_ws_ignore_nulls(" | ", col("A"), col("B"), col("C")).as("concat_ws_ignore_nulls")
).show()
----------------------------
|"CONCAT_WS_IGNORE_NULLS"  |
----------------------------
|Hello | World             |
|                          |
|Hello                     |
----------------------------

separator: A string literal used as the separator between concatenated values.
exprs: The columns to be concatenated.
returns: A Column containing the concatenated values with null values filtered out.

Since: 1.17.0

def contains(col: Column, str: Column): Column
Returns true if col contains str.
Returns true if col contains str.
Since
0.1.0
def convert_timezone(targetTimeZone: Column, sourceTimestamp: Column): Column
Converts the given sourceTimestampNTZ to targetTimeZone.
Converts the given sourceTimestampNTZ to targetTimeZone.
Supported time zones are listed here
Example
```
timestamp.select(convert_timezone(lit("America/New_York"), col("time")))
```
Since
0.1.0
def convert_timezone(sourceTimeZone: Column, targetTimeZone: Column, sourceTimestampNTZ: Column): Column
Converts the given sourceTimestampNTZ from sourceTimeZone to targetTimeZone.
Converts the given sourceTimestampNTZ from sourceTimeZone to targetTimeZone.
Supported time zones are listed here
Example
```
timestampNTZ.select(convert_timezone(lit("America/Los_Angeles"), lit("America/New_York"), col("time")))
```
Since
0.1.0
def corr(column1: Column, column2: Column): Column
Returns the correlation coefficient for non-null pairs in a group.
Returns the correlation coefficient for non-null pairs in a group.
Since
0.1.0
def cos(e: Column): Column
Computes the cosine of its argument; the argument should be expressed in radians.
Computes the cosine of its argument; the argument should be expressed in radians.
Since
0.1.0
def cosh(e: Column): Column
Computes the hyperbolic cosine of its argument.
Computes the hyperbolic cosine of its argument.
Since
0.1.0
def count(e: Column): Column
Returns either the number of non-NULL records for the specified columns, or the total number of records.
Returns either the number of non-NULL records for the specified columns, or the total number of records.
Since
0.1.0
def countDistinct(expr: Column, exprs: Column*): Column
Returns either the number of non-NULL distinct records for the specified columns, or the total number of the distinct records.
Returns either the number of non-NULL distinct records for the specified columns, or the total number of the distinct records. An alias of count_distinct.
Since
1.13.0
def countDistinct(colName: String, colNames: String*): Column
Returns either the number of non-NULL distinct records for the specified columns, or the total number of the distinct records.
Returns either the number of non-NULL distinct records for the specified columns, or the total number of the distinct records. An alias of count_distinct.
Since
1.13.0
def count_distinct(expr: Column, exprs: Column*): Column
Returns either the number of non-NULL distinct records for the specified columns, or the total number of the distinct records.
Returns either the number of non-NULL distinct records for the specified columns, or the total number of the distinct records.
Since
0.1.0
def covar_pop(column1: Column, column2: Column): Column
Returns the population covariance for non-null pairs in a group.
Returns the population covariance for non-null pairs in a group.
Since
0.1.0
def covar_samp(column1: Column, column2: Column): Column
Returns the sample covariance for non-null pairs in a group.
Returns the sample covariance for non-null pairs in a group.
Since
0.1.0
def cume_dist(): Column
Finds the cumulative distribution of a value with regard to other values within the same window partition.
Finds the cumulative distribution of a value with regard to other values within the same window partition.
Since
0.1.0
def current_account(): Column
Returns the account used by the user's current session.
Returns the account used by the user's current session.
Since
0.1.0
def current_available_roles(): Column
Returns a JSON string that lists all roles granted to the current user.
Returns a JSON string that lists all roles granted to the current user.
Since
0.1.0
def current_database(): Column
Returns the name of the database in use for the current session.
Returns the name of the database in use for the current session.
Since
0.1.0
def current_date(): Column
Returns the current date of the system.
Returns the current date of the system.
Since
0.1.0
def current_region(): Column
Returns the name of the region for the account where the current user is logged in.
Returns the name of the region for the account where the current user is logged in.
Since
0.1.0
def current_role(): Column
Returns the name of the role in use for the current session.
Returns the name of the role in use for the current session.
Since
0.1.0
def current_schema(): Column
Returns the name of the schema in use by the current session.
Returns the name of the schema in use by the current session.
Since
0.1.0
def current_schemas(): Column
Returns active search path schemas.
Returns active search path schemas.
Since
0.1.0
def current_session(): Column
Returns a unique system identifier for the Snowflake session corresponding to the present connection.
Returns a unique system identifier for the Snowflake session corresponding to the present connection.
Since
0.1.0
def current_statement(): Column
Returns the SQL text of the statement that is currently executing.
Returns the SQL text of the statement that is currently executing.
Since
0.1.0
def current_time(): Column
Returns the current time for the system.
Returns the current time for the system.
Since
0.1.0
def current_timestamp(): Column
Returns the current timestamp for the system.
Returns the current timestamp for the system.
Since
0.1.0
def current_user(): Column
Returns the name of the user currently logged into the system.
Returns the name of the user currently logged into the system.
Since
0.1.0
def current_version(): Column
Returns the current Snowflake version.
Returns the current Snowflake version.
Since
0.1.0
def current_warehouse(): Column
Returns the name of the warehouse in use for the current session.
Returns the name of the warehouse in use for the current session.
Since
0.1.0
def date_add(start: Column, days: Column): Column
Returns the date that is days days after start.
Returns the date that is days days after start. Usage:
```
DATE_ADD( date_or_time_part, value, date_or_time_expr )
```
Example::
```
SELECT TO_DATE('2013-05-08') AS v1, DATE_ADD(year, 2, TO_DATE('2013-05-08')) AS v;
+------------+------------+
| V1         | V          |
|------------+------------|
| 2013-05-08 | 2015-05-08 |
+------------+------------+
```
start
A date, timestamp or string. If a string, the data must be in a format that can be cast to a date, such as yyyy-MM-dd or yyyy-MM-dd HH:mm:ss.SSSS
days
The number of days to add to start, can be negative to subtract days
returns
A date, or null if start was a string that could not be cast to a date
Since
1.15.0

def date_add(days: Int, start: Column): Column

Returns the date that is days days after start.

Returns the date that is days days after start. Usage -

DATE_ADD( date_or_time_part, value, date_or_time_expr )

Example::

SELECT TO_DATE('2013-05-08') AS v1, DATE_ADD(year, 2, TO_DATE('2013-05-08')) AS v;
+------------+------------+
| V1         | V          |
|------------+------------|
| 2013-05-08 | 2015-05-08 |
+------------+------------+

days: Int .
start: Column name
returns: Column.

Since: 1.15.0

def date_format(c: Column, s: String): Column

Converts an input expression into the corresponding date in the specified date format.

Converts an input expression into the corresponding date in the specified date format. Example:

 val df = Seq("2023-10-10", "2022-05-15", null.asInstanceOf[String]).toDF("date")
 df.select(date_format(col("date"), "YYYY/MM/DD").as("formatted_date")).show()

--------------------
|"FORMATTED_DATE"  |
--------------------
|2023/10/10        |
|2022/05/15        |
|NULL              |
--------------------

c: Column to format to date.
s: Date format.
returns: Column object.

Since: 1.14.0

def date_from_parts(year: Column, month: Column, day: Column): Column
Creates a date from individual numeric components that represent the year, month, and day of the month.
Creates a date from individual numeric components that represent the year, month, and day of the month.
Since
0.1.0
def date_trunc(format: String, timestamp: Column): Column
Truncates a DATE, TIME, or TIMESTAMP to the specified precision.
Truncates a DATE, TIME, or TIMESTAMP to the specified precision.
Since
0.1.0
def dateadd(part: String, value: Column, expr: Column): Column
Adds the specified value for the specified date or time art to date or time expr.
Adds the specified value for the specified date or time art to date or time expr.
Supported date and time parts are listed here
Example: add one year on dates
```
date.select(dateadd("year", lit(1), col("date_col")))
```
Since
0.1.0
def datediff(part: String, col1: Column, col2: Column): Column
Calculates the difference between two date, time, or timestamp columns based on the date or time part requested.
Calculates the difference between two date, time, or timestamp columns based on the date or time part requested.
Supported date and time parts are listed here
Example: year difference between two date columns
```
date.select(datediff("year", col("date_col1"), col("date_col2"))),
```
Since
0.1.0
def dayname(expr: Column): Column
Extracts the three-letter day-of-week name from the specified date or timestamp.
Extracts the three-letter day-of-week name from the specified date or timestamp.
Since
0.1.0
def dayofmonth(e: Column): Column
Extracts the day of month from a date or timestamp.
Extracts the day of month from a date or timestamp.
Since
0.1.0
def dayofweek(e: Column): Column
Extracts the day of week from a date or timestamp.
Extracts the day of week from a date or timestamp.
Since
0.1.0
def dayofyear(e: Column): Column
Extracts the day of year from a date or timestamp.
Extracts the day of year from a date or timestamp.
Since
0.1.0
def degrees(e: Column): Column
Converts radians to degrees.
Converts radians to degrees.
Since
0.1.0
def dense_rank(): Column
Returns the rank of a value within a group of values, without gaps in the ranks.
Returns the rank of a value within a group of values, without gaps in the ranks. The rank value starts at 1 and continues up sequentially. If two values are the same, they will have the same rank.
Since
0.1.0
def desc(colName: String): Column
def div0(dividend: Column, divisor: Column): Column
Performs division like the division operator (/), but returns 0 when the divisor is 0 (rather than reporting an error).
Performs division like the division operator (/), but returns 0 when the divisor is 0 (rather than reporting an error).
Since
0.1.0
def endswith(expr: Column, str: Column): Column
Returns TRUE if expr ends with str.
Returns TRUE if expr ends with str.
Since
0.1.0
final def eq(arg0: AnyRef): Boolean
Definition Classes
AnyRef
def equal_nan(e: Column): Column
Return true if the value in the column is not a number (NaN).
Return true if the value in the column is not a number (NaN).
Since
0.1.0
def equals(arg0: AnyRef): Boolean
Definition Classes
AnyRef → Any
def exp(e: Column): Column
Computes Euler's number e raised to a floating-point value.
Computes Euler's number e raised to a floating-point value.
Since
0.1.0
def expr(s: String): Column
Creates a Column expression from raw SQL text.
Creates a Column expression from raw SQL text.
Note that the function does not interpret or check the SQL text.
Example:
```
 val df = session.createDataFrame(Seq(Array(1, 2, 3))).toDF("id")
 df.filter(expr("id > 2")).show()

--------
|"ID"  |
--------
|3     |
--------
```
s
SQL Expression as text.
returns
Converted SQL Expression.
Since
1.14.0
def factorial(e: Column): Column
Computes the factorial of its input.
Computes the factorial of its input. The input argument must be an integer expression in the range of 0 to 33.
Since
0.1.0
def floor(e: Column): Column
Returns values from the specified column rounded to the nearest equal or smaller integer.
Returns values from the specified column rounded to the nearest equal or smaller integer.
Since
0.1.0
def format_number(x: Column, d: Int): Column
Formats numeric column x to a format like '#,###,###.##', rounded to d decimal places with HALF_EVEN round mode, and returns the result as a string column.
Formats numeric column x to a format like '#,###,###.##', rounded to d decimal places with HALF_EVEN round mode, and returns the result as a string column.
x
numeric column to be transformed
d
Amount of decimal for the number format
returns
Number casted to the specific string format
Since
1.15.0 If d is 0, the result has no decimal point or fractional part. If d is less than 0, the result will be null.
def from_unixtime(ut: Column, f: String): Column
Converts the number of seconds from unix epoch (1970-01-01 00:00:00 UTC) to a string representing the timestamp of that moment in the current system time zone in the given format.
Converts the number of seconds from unix epoch (1970-01-01 00:00:00 UTC) to a string representing the timestamp of that moment in the current system time zone in the given format.
ut
A number of a type that is castable to a long, such as string or integer. Can be negative for timestamps before the unix epoch
f
A date time pattern that the input will be formatted to
returns
A string, or null if ut was a string that could not be cast to a long or f was an invalid date time pattern
Since
1.15.0
def from_unixtime(ut: Column): Column
Converts the number of seconds from unix epoch (1970-01-01 00:00:00 UTC) to a string representing the timestamp of that moment in the current system time zone in the yyyy-MM-dd HH:mm:ss format.
Converts the number of seconds from unix epoch (1970-01-01 00:00:00 UTC) to a string representing the timestamp of that moment in the current system time zone in the yyyy-MM-dd HH:mm:ss format.
ut
A number of a type that is castable to a long, such as string or integer. Can be negative for timestamps before the unix epoch
returns
A string, or null if the input was a string that could not be cast to a long
Since
1.15.0
def from_utc_timestamp(ts: Column): Column
Given a timestamp like '2017-07-14 02:40:00.0', interprets it as a time in UTC, and renders that time as a timestamp in the given time zone.
Given a timestamp like '2017-07-14 02:40:00.0', interprets it as a time in UTC, and renders that time as a timestamp in the given time zone. For example, 'GMT+1' would yield '2017-07-14 03:40:00.0'.
```
ALTER SESSION SET TIMEZONE = 'America/Los_Angeles';
SELECT TO_TIMESTAMP_TZ('2024-04-05 01:02:03');
+----------------------------------------+
| TO_TIMESTAMP_TZ('2024-04-05 01:02:03') |
|----------------------------------------|
| 2024-04-05 01:02:03.000 -0700          |
+----------------------------------------+
```
ts
A date, timestamp or string. If a string, the data must be in a format that can be cast to a timestamp, such as yyyy-MM-dd or yyyy-MM-dd HH:mm:ss.SSSS A string detailing the time zone ID that the input should be adjusted to. It should be in the format of either region-based zone IDs or zone offsets. Region IDs must have the form 'area/city', such as 'America/Los_Angeles'. Zone offsets must be in the format '(+|-)HH:mm', for example '-08:00' or '+01:00'. Also 'UTC' and 'Z' are supported as aliases of '+00:00'. Other short names are not recommended to use because they can be ambiguous.
returns
A timestamp, or null if ts was a string that could not be cast to a timestamp or tz was an invalid value
Since
1.15.0
def get(col1: Column, col2: Column): Column
Extracts a value from an object or array; returns NULL if either of the arguments is NULL.
Extracts a value from an object or array; returns NULL if either of the arguments is NULL.
Since
0.2.0
final def getClass(): Class[_ <: AnyRef]
Definition Classes
AnyRef → Any
Annotations
@HotSpotIntrinsicCandidate() @native()
def get_ignore_case(obj: Column, field: Column): Column
Extracts a field value from an object; returns NULL if either of the arguments is NULL.
Extracts a field value from an object; returns NULL if either of the arguments is NULL. This function is similar to GET but applies case-insensitive matching to field names.
Since
0.2.0
def get_path(col: Column, path: Column): Column
Extracts a value from semi-structured data using a path name.
Extracts a value from semi-structured data using a path name.
Since
0.2.0
def greatest(exprs: Column*): Column
Returns the largest value from a list of expressions.
Returns the largest value from a list of expressions. If any of the argument values is NULL, the result is NULL. GREATEST supports all data types, including VARIANT.
Since
0.1.0
def grouping(e: Column): Column
Describes which of a list of expressions are grouped in a row produced by a GROUP BY query.
Describes which of a list of expressions are grouped in a row produced by a GROUP BY query.
Since
0.1.0
def grouping_id(cols: Column*): Column
Describes which of a list of expressions are grouped in a row produced by a GROUP BY query.
Describes which of a list of expressions are grouped in a row produced by a GROUP BY query.
Since
0.1.0
def hash(cols: Column*): Column
Returns a signed 64-bit hash value.
Returns a signed 64-bit hash value. Note that HASH never returns NULL, even for NULL inputs.
Since
0.1.0
def hashCode(): Int
Definition Classes
AnyRef → Any
Annotations
@HotSpotIntrinsicCandidate() @native()

def hex(c: Column): Column

Computes hex value of the given column.

Computes hex value of the given column. Example

  val df = session.createDataFrame(Seq((1), (2), (3))).toDF("a")
  df.withColumn("hex_col", hex(col("A"))).select("hex_col").show()
-------------
|"HEX_COL"  |
-------------
|31         |
|32         |
|33         |
-------------

c: Column to encode.
returns: Encoded string.

Since: 1.14.0

def hour(e: Column): Column
Extracts the hour from a date or timestamp.
Extracts the hour from a date or timestamp.
Since
0.1.0
def iff(condition: Column, expr1: Column, expr2: Column): Column
Returns one of two specified expressions, depending on a condition.
Returns one of two specified expressions, depending on a condition.
This is equivalent to an if-then-else expression. If condition evaluates to TRUE, the function returns expr1. Otherwise, the function returns expr2.
condition
The condition to evaluate.
expr1
The expression to return if the condition evaluates to TRUE.
expr2
The expression to return if the condition is not TRUE (i.e. if it is FALSE or NULL).
Since
0.9.0
def in(columns: Seq[Column], df: DataFrame): Column
Returns a conditional expression that you can pass to the filter or where method to perform the equivalent of a WHERE ...
Returns a conditional expression that you can pass to the filter or where method to perform the equivalent of a WHERE ... IN query with the subquery represented by the specified DataFrame.
The expression evaluates to true if the value in the column is one of the values in the column of the same name in a specified DataFrame.
For example, the following code returns a DataFrame that contains the rows where the values of the columns c1 and c2 in df2 match the values of the columns a and b in df1. This is equivalent to SELECT * FROM table2 WHERE (c1, c2) IN (SELECT a, b FROM table1).
```
val df1 = session.sql("select a, b from table1").
val df2 = session.table(table2)
val dfFilter = df2.filter(functions.in(Seq(col("c1"), col("c2")), df1))
```
columns
A sequence of the columns to compare for the IN operation.
df
The DataFrame used as the values for the IN operation
Since
0.10.0
def in(columns: Seq[Column], values: Seq[Seq[Any]]): Column
Returns a conditional expression that you can pass to the filter or where method to perform the equivalent of a WHERE ...
Returns a conditional expression that you can pass to the filter or where method to perform the equivalent of a WHERE ... IN query that matches rows containing a sequence of values.
The expression evaluates to true if the values in a row matches the values in one of the specified sequences.
For example, the following code returns a DataFrame that contains the rows in which the columns c1 and c2 contain the values:
- 1 and "a", or
- 2 and "b" This is equivalent to SELECT * FROM table WHERE (c1, c2) IN ((1, 'a'), (2, 'b')).
```
val df2 = df.filter(functions.in(Seq(df("c1"), df("c2")), Seq(Seq(1, "a"), Seq(2, "b"))))
```
columns
A sequence of the columns to compare for the IN operation.
values
A sequence containing the sequences of values to compare for the IN operation.
Since
0.10.0
def initcap(e: Column): Column
Returns the input string with the first letter of each word in uppercase and the subsequent letters in lowercase.
Returns the input string with the first letter of each word in uppercase and the subsequent letters in lowercase.
Since
0.1.0
def insert(baseExpr: Column, position: Column, length: Column, insertExpr: Column): Column
Replaces a substring of the specified length, starting at the specified position, with a new string or binary value.
Replaces a substring of the specified length, starting at the specified position, with a new string or binary value.
Since
0.1.0

def instr(str: Column, substring: String): Column

Locate the position of the first occurrence of substr column in the given string.

Locate the position of the first occurrence of substr column in the given string. Returns null if either of the arguments are null. For example

SELECT id,
       string1,
        REGEXP_SUBSTR(string1, 'nevermore\\d') AS substring,
       REGEXP_INSTR( string1, 'nevermore\\d') AS position
   FROM demo1
   ORDER BY id;
+----+-------------------------------------+------------+----------+
 | ID | STRING1                             | SUBSTRING  | POSITION |
 |----+-------------------------------------+------------+----------|
 |  1 | nevermore1, nevermore2, nevermore3. | nevermore1 |        1 |
 +----+-------------------------------------+------------+----------+

Since: 1.15.0
Note: The position is not zero based, but 1 based index. Returns 0 if substr could not be found in str.

final def isInstanceOf[T0]: Boolean
Definition Classes
Any
def is_array(col: Column): Column
Returns true if the specified VARIANT column contains an ARRAY value.
Returns true if the specified VARIANT column contains an ARRAY value.
Since
0.1.0
def is_binary(col: Column): Column
Returns true if the specified VARIANT column contains a binary value.
Returns true if the specified VARIANT column contains a binary value.
Since
0.1.0
def is_boolean(col: Column): Column
Returns true if the specified VARIANT column contains a Boolean value.
Returns true if the specified VARIANT column contains a Boolean value.
Since
0.1.0
def is_char(col: Column): Column
Returns true if the specified VARIANT column contains a string value.
Returns true if the specified VARIANT column contains a string value.
Since
0.1.0
def is_date(col: Column): Column
Returns true if the specified VARIANT column contains a DATE value.
Returns true if the specified VARIANT column contains a DATE value.
Since
0.1.0
def is_date_value(col: Column): Column
Returns true if the specified VARIANT column contains a DATE value.
Returns true if the specified VARIANT column contains a DATE value.
Since
0.1.0
def is_decimal(col: Column): Column
Returns true if the specified VARIANT column contains a fixed-point decimal value or integer.
Returns true if the specified VARIANT column contains a fixed-point decimal value or integer.
Since
0.1.0
def is_double(col: Column): Column
Returns true if the specified VARIANT column contains a floating-point value, fixed-point decimal, or integer.
Returns true if the specified VARIANT column contains a floating-point value, fixed-point decimal, or integer.
Since
0.1.0
def is_integer(col: Column): Column
Returns true if the specified VARIANT column contains an integer value.
Returns true if the specified VARIANT column contains an integer value.
Since
0.1.0
def is_null(e: Column): Column
Return true if the value in the column is null.
Return true if the value in the column is null.
Since
0.1.0
def is_null_value(col: Column): Column
Returns true if the specified VARIANT column is a JSON null value.
Returns true if the specified VARIANT column is a JSON null value.
Since
0.1.0
def is_object(col: Column): Column
Returns true if the specified VARIANT column contains an OBJECT value.
Returns true if the specified VARIANT column contains an OBJECT value.
Since
0.1.0
def is_real(col: Column): Column
Returns true if the specified VARIANT column contains a floating-point value, fixed-point decimal, or integer.
Returns true if the specified VARIANT column contains a floating-point value, fixed-point decimal, or integer.
Since
0.1.0
def is_time(col: Column): Column
Returns true if the specified VARIANT column contains a TIME value.
Returns true if the specified VARIANT column contains a TIME value.
Since
0.1.0
def is_timestamp_ltz(col: Column): Column
Returns true if the specified VARIANT column contains a TIMESTAMP value to be interpreted using the local time zone.
Returns true if the specified VARIANT column contains a TIMESTAMP value to be interpreted using the local time zone.
Since
0.1.0
def is_timestamp_ntz(col: Column): Column
Returns true if the specified VARIANT column contains a TIMESTAMP value with no time zone.
Returns true if the specified VARIANT column contains a TIMESTAMP value with no time zone.
Since
0.1.0
def is_timestamp_tz(col: Column): Column
Returns true if the specified VARIANT column contains a TIMESTAMP value with a time zone.
Returns true if the specified VARIANT column contains a TIMESTAMP value with a time zone.
Since
0.1.0
def is_varchar(col: Column): Column
Returns true if the specified VARIANT column contains a string value.
Returns true if the specified VARIANT column contains a string value.
Since
0.1.0
def isnull(c: Column): Column
Wrapper for Snowflake built-in isnull function.
Wrapper for Snowflake built-in isnull function. Gets a boolean depending if value is NULL or not. Return true if the value in the column is null. Example:: >>> from snowflake.snowpark.functions import is_null >>> df = session.create_dataframe([1.2, float("nan"), None, 1.0], schema=["a"]) >>> df.select(is_null("a").as_("a")).collect() [Row(A=False), Row(A=False), Row(A=True), Row(A=False)]
c
Column to qnalize if it is null value.
returns
Column object.
Since
1.14.0
def json_extract_path_text(col: Column, path: Column): Column
Parses a JSON string and returns the value of an element at a specified path in the resulting JSON document.
Parses a JSON string and returns the value of an element at a specified path in the resulting JSON document.
col
Column containing the JSON string that should be parsed.
path
Column containing the path to the element that should be extracted.
Since
0.2.0
def kurtosis(e: Column): Column
Returns the population excess kurtosis of non-NULL records.
Returns the population excess kurtosis of non-NULL records. If all records inside a group are NULL, the function returns NULL.
Since
0.1.0
def lag(e: Column): Column
Accesses data in a previous row in the same result set without having to join the table to itself.
Accesses data in a previous row in the same result set without having to join the table to itself.
Since
0.1.0
def lag(e: Column, offset: Int): Column
Accesses data in a previous row in the same result set without having to join the table to itself.
Accesses data in a previous row in the same result set without having to join the table to itself.
Since
0.1.0
def lag(e: Column, offset: Int, defaultValue: Column): Column
Accesses data in a previous row in the same result set without having to join the table to itself.
Accesses data in a previous row in the same result set without having to join the table to itself.
Since
0.1.0

def last(c: Column): Column

Returns the last value of the column in a group.

Returns the last value of the column in a group. Example

 val df = session.createDataFrame(Seq((5, "a", 10),
                                      (5, "b", 20),
                                      (3, "d", 15),
                                      (3, "e", 40))).toDF("grade", "name", "score")
    val window = Window.partitionBy(col("grade")).orderBy(col("score").desc)
    df.select(last(col("name")).over(window)).show()

---------------------
|"LAST_SCORE_NAME"  |
---------------------
|a                  |
|a                  |
|d                  |
|d                  |
---------------------

c: Column to obtain last value.
returns: Column object.

Since: 1.14.0

def last_day(e: Column): Column
Returns the last day of the specified date part for a date or timestamp.
Returns the last day of the specified date part for a date or timestamp. Commonly used to return the last day of the month for a date or timestamp.
Since
0.1.0
def lead(e: Column): Column
Accesses data in a subsequent row in the same result set without having to join the table to itself.
Accesses data in a subsequent row in the same result set without having to join the table to itself.
Since
0.1.0
def lead(e: Column, offset: Int): Column
Accesses data in a subsequent row in the same result set without having to join the table to itself.
Accesses data in a subsequent row in the same result set without having to join the table to itself.
Since
0.1.0
def lead(e: Column, offset: Int, defaultValue: Column): Column
Accesses data in a subsequent row in the same result set without having to join the table to itself.
Accesses data in a subsequent row in the same result set without having to join the table to itself.
Since
0.1.0
def least(exprs: Column*): Column
Returns the smallest value from a list of expressions.
Returns the smallest value from a list of expressions. LEAST supports all data types, including VARIANT.
Since
0.1.0
def left(strExpr: Column, lengthExpr: Column): Column
Returns a left most substring of strExpr.
Returns a left most substring of strExpr.
Since
0.1.0
def length(e: Column): Column
Returns the length of an input string or binary value.
Returns the length of an input string or binary value. For strings, the length is the number of characters, and UTF-8 characters are counted as a single character. For binary, the length is the number of bytes.
Since
0.1.0
def listagg(col: Column): Column
Returns the concatenated input values, separated by empty string.
Returns the concatenated input values, separated by empty string.
For example:
```
df.groupBy(df.col("col1")).agg(listagg(df.col("col2"), ",")
    .withinGroup(df.col("col2").asc))

df.select(listagg(df.col("col2"), ",", false))
```
col
The expression (typically a Column) that determines the values to be put into the list. The expression should evaluate to a string, or to a data type that can be cast to string.
Since
0.12.0
def listagg(col: Column, delimiter: String): Column
Returns the concatenated input values, separated by delimiter string.
Returns the concatenated input values, separated by delimiter string.
For example:
```
df.groupBy(df.col("col1")).agg(listagg(df.col("col2"), ",")
    .withinGroup(df.col("col2").asc))

df.select(listagg(df.col("col2"), ",", false))
```
col
The expression (typically a Column) that determines the values to be put into the list. The expression should evaluate to a string, or to a data type that can be cast to string.
delimiter
A string delimiter.
Since
0.12.0
def listagg(col: Column, delimiter: String, isDistinct: Boolean): Column
Returns the concatenated input values, separated by delimiter string.
Returns the concatenated input values, separated by delimiter string.
For example:
```
df.groupBy(df.col("col1")).agg(listagg(df.col("col2"), ",")
    .withinGroup(df.col("col2").asc))

df.select(listagg(df.col("col2"), ",", false))
```
col
The expression (typically a Column) that determines the values to be put into the list. The expression should evaluate to a string, or to a data type that can be cast to string.
delimiter
A string delimiter.
isDistinct
Whether the input expression is distinct.
Since
0.12.0
def lit(literal: Any): Column
Creates a Column expression for a literal value.
Creates a Column expression for a literal value.
Since
0.1.0
def locate(substr: String, str: Column, pos: Int = 1): Column
Locate the position of the first occurrence of substr in a string column, after position pos.
Locate the position of the first occurrence of substr in a string column, after position pos.
substr
string to search
str
value where string will be searched
pos
index for starting the search. default to 1.
returns
Returns the position of the first occurrence
Since
1.14.0
Note
The position is not zero based, but 1 based index. returns 0 if substr could not be found in str. This function is just leverages the SF POSITION builtin Example
val df = session.createDataFrame(Seq("java scala python")).toDF("a") df.select(locate("scala", col("a")).as("locate")).show() ------------ |"LOCATE" | ------------ |6 | ------------
def locate(substr: Column, str: Column, pos: Int): Column
Locate the position of the first occurrence of substr in a string column, after position pos.
Locate the position of the first occurrence of substr in a string column, after position pos.
substr
string to search
str
value where string will be searched
pos
index for starting the search
returns
returns the position of the first occurrence.
Since
1.14.0
Note
The position is not zero based, but 1 based index. returns 0 if substr could not be found in str. This function is just leverages the SF POSITION builtin Example
val df = session.createDataFrame(Seq(("b", "abcd"))).toDF("a", "b") df.select(locate(col("a"), col("b"), 1).as("locate")).show() ------------ |"LOCATE" | ------------ |2 | ------------
def log(base: Column, a: Column): Column
Returns the logarithm of a numeric expression.
Returns the logarithm of a numeric expression.
Since
0.1.0
def log10(columnName: String): Column
Computes the logarithm of the given column in base 10.
Computes the logarithm of the given column in base 10. Example
```
 val df = session.createDataFrame(Seq(100)).toDF("a")
 df.select(log10("a"))).show()
-----------
|"LOG10"  |
-----------
|2.0      |
-----------
```
columnName
ColumnName in String to apply logarithm operation
returns
log10 of the given column
Since
1.14.0
def log10(c: Column): Column
Computes the logarithm of the given value in base 10.
Computes the logarithm of the given value in base 10. Example
```
 val df = session.createDataFrame(Seq(100)).toDF("a")
 df.select(log10(col("a"))).show()

-----------
|"LOG10"  |
-----------
|2.0      |
-----------
```
c
Column to apply logarithm operation
returns
log10 of the given column
Since
1.14.0
def log1p(columnName: String): Column
Computes the natural logarithm of the given value plus one.
Computes the natural logarithm of the given value plus one. Example
```
 val df = session.createDataFrame(Seq(0.1)).toDF("a")
 df.select(log1p("a").as("log1p")).show()
-----------------------
|"LOG1P"              |
-----------------------
|0.09531017980432493  |
-----------------------
```
columnName
ColumnName in String to apply logarithm operation
returns
the natural logarithm of the given value plus one.
Since
1.14.0
def log1p(c: Column): Column
Computes the natural logarithm of the given value plus one.
Computes the natural logarithm of the given value plus one. Example
```
 val df = session.createDataFrame(Seq(0.1)).toDF("a")
 df.select(log1p(col("a")).as("log1p")).show()
-----------------------
|"LOG1P"              |
-----------------------
|0.09531017980432493  |
-----------------------
```
c
Column to apply logarithm operation
returns
the natural logarithm of the given value plus one.
Since
1.14.0
def lower(e: Column): Column
Returns the input string with all characters converted to lowercase.
Returns the input string with all characters converted to lowercase.
Since
0.1.0
def lpad(str: Column, len: Int, pad: Array[Byte]): Column
Left-pads a binary value with bytes from another binary value.
Left-pads a binary value with bytes from another binary value.
Examples:
```
val df = Seq(Array[Byte](0x41, 0x42)).toDF("a")
df.select(lpad(col("a"), 5, Array[Byte](0x00))).show()

------------------------------------
|"LPAD(""A"", 5, '00' :: BINARY)"  |
------------------------------------
|'0000004142'                      |
------------------------------------
```
str
The binary column to pad.
len
The target length of the resulting binary value (in bytes).
pad
The byte array to use for padding.
returns
A new column containing the left-padded binary value.
Since
1.17.0
def lpad(str: Column, len: Int, pad: String): Column
Left-pads a string with characters from another string.
Left-pads a string with characters from another string.
Examples:
```
val df = Seq("hello", "world").toDF("a")
df.select(lpad(col("a"), 10, "*")).show()

--------------------------
|"LPAD(""A"", 10, '*')"  |
--------------------------
|*****hello              |
|*****world              |
--------------------------
```
str
The string column to pad.
len
The target length of the resulting string value (in characters).
pad
The string literal to use for padding.
returns
A new column containing the left-padded string.
Since
1.17.0
def lpad(str: Column, len: Column, pad: Column): Column
Left-pads a string with characters from another string, or left-pads a binary value with bytes from another binary value.
Left-pads a string with characters from another string, or left-pads a binary value with bytes from another binary value.
Since
0.1.0
def ltrim(e: Column): Column
Removes leading characters, including whitespace, from a string.
Removes leading characters, including whitespace, from a string.
Since
0.1.0
def ltrim(e: Column, trimString: Column): Column
Removes leading characters, including whitespace, from a string.
Removes leading characters, including whitespace, from a string.
Since
0.1.0
def max(e: Column): Column
Returns the maximum value for the records in a group.
Returns the maximum value for the records in a group. NULL values are ignored unless all the records are NULL, in which case a NULL value is returned.
Since
0.1.0
def max(colName: String): Column
Returns the maximum value for the records in a group.
Returns the maximum value for the records in a group. NULL values are ignored unless all the records are NULL, in which case a NULL value is returned.
Example:
```
val df = session.createDataFrame(Seq(1, 3, 10, 1, 3)).toDF("x")
df.select(max("x")).show()

----------------
|"MAX(""X"")"  |
----------------
|10            |
----------------
```
colName
The name of the column
returns
The maximum value of the given column
Since
1.13.0
def md5(e: Column): Column
Returns a 32-character hex-encoded string containing the 128-bit MD5 message digest.
Returns a 32-character hex-encoded string containing the 128-bit MD5 message digest.
Since
0.1.0
def mean(e: Column): Column
Returns the average of non-NULL records.
Returns the average of non-NULL records. If all records inside a group are NULL, the function returns NULL. Alias of avg
Since
0.1.0
def mean(colName: String): Column
Returns the average of non-NULL records.
Returns the average of non-NULL records. If all records inside a group are NULL, the function returns NULL. Alias of avg.
Example:
```
val df = session.createDataFrame(Seq(1, 3, 10, 1, 3)).toDF("x")
df.select(mean("x")).show()

----------------
|"AVG(""X"")"  |
----------------
|3.600000      |
----------------
```
colName
The name of the column
returns
The average value of the given column
Since
1.13.0
def median(e: Column): Column
Returns the median value for the records in a group.
Returns the median value for the records in a group. NULL values are ignored unless all the records are NULL, in which case a NULL value is returned.
Since
0.5.0
def min(e: Column): Column
Returns the minimum value for the records in a group.
Returns the minimum value for the records in a group. NULL values are ignored unless all the records are NULL, in which case a NULL value is returned.
Since
0.1.0
def min(colName: String): Column
Returns the minimum value for the records in a group.
Returns the minimum value for the records in a group. NULL values are ignored unless all the records are NULL, in which case a NULL value is returned.
Example:
```
val df = session.createDataFrame(Seq(1, 3, 10, 1, 3)).toDF("x")
df.select(min("x")).show()

----------------
|"MIN(""X"")"  |
----------------
|1             |
----------------
```
colName
The name of the column
returns
The minimum value of the given column
Since
1.13.0
def minute(e: Column): Column
Extracts the minute from a date or timestamp.
Extracts the minute from a date or timestamp.
Since
0.1.0
def monotonically_increasing_id(): Column
A column expression that generates monotonically increasing 64-bit integers.
A column expression that generates monotonically increasing 64-bit integers. Returns a sequence of monotonically increasing integers, with wrap-around which happens after largest representable integer of integer width 8 byte.
Args: sign: When 0, the sequence continues at 0 after wrap-around. When 1, the sequence continues at smallest representable 8 byte integer. Defaults to 0.
See Also:
- :meth:Session.generator, which can be used to generate in tandem with seq8 to generate sequences.
Example:: >>> df = session.generator(seq8(0), rowcount=3) >>> df.collect() [Row(SEQ8(0)=0), Row(SEQ8(0)=1), Row(SEQ8(0)=2)]
Since
1.15.0
def month(e: Column): Column
Extracts the month from a date or timestamp.
Extracts the month from a date or timestamp.
Since
0.1.0
def monthname(expr: Column): Column
Extracts the three-letter month name from the specified date or timestamp.
Extracts the three-letter month name from the specified date or timestamp.
Since
0.1.0
def months_between(end: String, start: String): Column
Returns number of months between dates start and end.
Returns number of months between dates start and end.
A whole number is returned if both inputs have the same day of month or both are the last day of their respective months. Otherwise, the difference is calculated assuming 31 days per month.
For example:
```
months_between("2017-11-14", "2017-07-14")  // returns 4.0
months_between("2017-01-01", "2017-01-10")  // returns 0.29032258
months_between("2017-06-01", "2017-06-16 12:00:00")  // returns -0.5
```
end
Column name. If a string, the data must be in a format that can be cast to a timestamp, such as yyyy-MM-dd or yyyy-MM-dd HH:mm:ss.SSSS
start
Column name . If a string, the data must be in a format that can cast to a timestamp, such as yyyy-MM-dd or yyyy-MM-dd HH:mm:ss.SSSS
returns
A double, or null if either end or start were strings that could not be cast to a timestamp. Negative if end is before start
Since
1.15.0
final def ne(arg0: AnyRef): Boolean
Definition Classes
AnyRef
def negate(e: Column): Column
Returns the negation of the value in the column (equivalent to a unary minus).
Returns the negation of the value in the column (equivalent to a unary minus).
Since
0.1.0
def next_day(date: Column, dayOfWeek: Column): Column
Returns the date of the first specified DOW (day of week) that occurs after the input date.
Returns the date of the first specified DOW (day of week) that occurs after the input date.
Since
0.1.0
def not(e: Column): Column
Returns the inverse of a boolean expression.
Returns the inverse of a boolean expression.
Since
0.1.0
final def notify(): Unit
Definition Classes
AnyRef
Annotations
@HotSpotIntrinsicCandidate() @native()
final def notifyAll(): Unit
Definition Classes
AnyRef
Annotations
@HotSpotIntrinsicCandidate() @native()
def ntile(n: Int): Column
Window function: returns the ntile group id (from 1 to n inclusive) in an ordered window partition.
Window function: returns the ntile group id (from 1 to n inclusive) in an ordered window partition. For example, if n is 4, the first quarter of the rows will get value 1, the second quarter will get 2, the third quarter will get 3, and the last quarter will get 4.
This is equivalent to the NTILE function in SQL. Example
```
  val df = Seq((5, 15), (5, 15), (5, 15), (5, 20)).toDF("grade", "score")
  val window = Window.partitionBy(col("grade")).orderBy(col("score"))
  df.select(ntile(2).over(window).as("ntile")).show()
-----------
|"NTILE"  |
-----------
|1        |
|1        |
|2        |
|2        |
-----------
```
n
number of groups
returns
returns the ntile group id (from 1 to n inclusive) in an ordered window partition.
Since
1.14.0
def ntile(n: Column): Column
Divides an ordered data set equally into the number of buckets specified by n.
Divides an ordered data set equally into the number of buckets specified by n. Buckets are sequentially numbered 1 through n.
Since
0.1.0
def object_construct(key_values: Column*): Column
Returns an OBJECT constructed from the arguments.
Returns an OBJECT constructed from the arguments.
Since
0.2.0
def object_delete(obj: Column, key1: Column, keys: Column*): Column
Returns an object containing the contents of the input (i.e.source) object with one or more keys removed.
Returns an object containing the contents of the input (i.e.source) object with one or more keys removed.
Since
0.2.0
def object_insert(obj: Column, key: Column, value: Column, update_flag: Column): Column
Returns an object consisting of the input object with a new key-value pair inserted (or an existing key updated with a new value).
Returns an object consisting of the input object with a new key-value pair inserted (or an existing key updated with a new value).
Since
0.2.0
def object_insert(obj: Column, key: Column, value: Column): Column
Returns an object consisting of the input object with a new key-value pair inserted.
Returns an object consisting of the input object with a new key-value pair inserted. The input key must not exist in the object.
Since
0.2.0
def object_keys(obj: Column): Column
Returns an array containing the list of keys in the input object.
Returns an array containing the list of keys in the input object.
Since
0.2.0
def object_pick(obj: Column, key1: Column, keys: Column*): Column
Returns a new OBJECT containing some of the key-value pairs from an existing object.
Returns a new OBJECT containing some of the key-value pairs from an existing object.
To identify the key-value pairs to include in the new object, pass in the keys as arguments, or pass in an array containing the keys.
If a specified key is not present in the input object, the key is ignored.
Since
0.2.0
def objectagg(key: Column, value: Column): Column
Returns one OBJECT per group.
Returns one OBJECT per group. For each (key, value) input pair, where key must be a VARCHAR and value must be a VARIANT, the resulting OBJECT contains a key:value field.
Since
0.2.0
def parse_json(col: Column): Column
Parse the value of the specified column as a JSON string and returns the resulting JSON document.
Parse the value of the specified column as a JSON string and returns the resulting JSON document.
Since
0.2.0
def parse_xml(col: Column): Column
Parse the value of the specified column as a JSON string and returns the resulting XML document.
Parse the value of the specified column as a JSON string and returns the resulting XML document.
Since
0.2.0
def percent_rank(): Column
Returns the relative rank of a value within a group of values, specified as a percentage ranging from 0.0 to 1.0.
Returns the relative rank of a value within a group of values, specified as a percentage ranging from 0.0 to 1.0.
Since
0.1.0

def pow(l: Double, r: String): Column