apache-spark-2.4-functions(SQL)
Import Notebook

Apache Spark Built-in and Higher-Order Functions Examples

For array types

array_distinct(array<T>): array<T>

Removes duplicate values from the given array.

SELECT array_distinct(array(1, 2, 3, null, 3));
 
array_distinct(array(1, 2, 3, CAST(NULL AS INT), 3))
1
[1, 2, 3, null]

Showing all 1 rows.

array_intersect(array<T>, array<T>): array<T>

Returns an array of the elements in the intersection of the given two arrays, without duplicates.

SELECT array_intersect(array(1, 2, 3), array(1, 3, 5));
 
array_intersect(array(1, 2, 3), array(1, 3, 5))
1
[1, 3]

Showing all 1 rows.

array_union(array<T>, array<T>): array<T>

Returns an array of the elements in the union of the given two arrays, without duplicates.

SELECT array_union(array(1, 2, 3), array(1, 3, 5));
 
array_union(array(1, 2, 3), array(1, 3, 5))
1
[1, 2, 3, 5]

Showing all 1 rows.

array_except(array<T>, array<T>): array<T>

Returns an array of the elements in array1 but not in array2, without duplicates.

SELECT array_except(array(1, 2, 3), array(1, 3, 5));
 
array_except(array(1, 2, 3), array(1, 3, 5))
1
[2]

Showing all 1 rows.

array_join(array<String>, String[, String]): String

Concatenates the elements of the given array using the delimiter and an optional string to replace nulls. If no value is set for null replacement, any null value is filtered.

SELECT array_join(array('hello', 'world'), ' ');
 
array_join(array(hello, world), )
1
hello world

Showing all 1 rows.

SELECT array_join(array('hello', null ,'world'), ' ');
 
array_join(array(hello, CAST(NULL AS STRING), world), )
1
hello world

Showing all 1 rows.

SELECT array_join(array('hello', null ,'world'), ' ', ',');
 
array_join(array(hello, CAST(NULL AS STRING), world), , ,)
1
hello , world

Showing all 1 rows.

array_max(array<T>): T

Returns the maximum value in the given array. null elements are skipped.