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.

SELECT array_max(array(1, 20, null, 3));
 
array_max(array(1, 20, CAST(NULL AS INT), 3))
1
20

Showing all 1 rows.

array_min(array<T>): T

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

SELECT array_min(array(1, 20, null, 3));
 
array_min(array(1, 20, CAST(NULL AS INT), 3))
1
1

Showing all 1 rows.

array_position(array<T>, T): Long

Returns the (1-based) index of the first element of the given array as long.

SELECT array_position(array(3, 2, 1), 1);
 
array_position(array(3, 2, 1), 1)
1
3

Showing all 1 rows.

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

Remove all elements that equal to the given element from the given array.

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

Showing all 1 rows.

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

Returns true if array1 contains at least a non-null element present also in array2. If the arrays have no common element and they are both non-empty and either of them contains a null element null is returned, false otherwise.

SELECT arrays_overlap(array(1, 2, 3), array(3, 4, 5));
 
arrays_overlap(array(1, 2, 3), array(3, 4, 5))
1
true

Showing all 1 rows.

array_sort(array<T>): array<T>

Sorts the input array in ascending order. The elements of the input array must be orderable. Null elements will be placed at the end of the returned array.

SELECT array_sort(array('b', 'd', null, 'c', 'a'));
 
array_sort(array(b, d, CAST(NULL AS STRING), c, a), lambdafunction((IF(((namedlambdavariable() IS NULL) AND (namedlambdavariable() IS NULL)), 0, (IF((namedlambdavariable() IS NULL), 1, (IF((namedlambdavariable() IS NULL), -1, (IF((namedlambdavariable() < namedlambdavariable()), -1, (IF((namedlambdavariable() > namedlambdavariable()), 1, 0)))))))))), namedlambdavariable(), namedlambdavariable()))

Showing all 1 rows.

concat(String, ...): String / concat(array<T>, ...): array<T>

Returns the concatenation of col1, col2, ..., colN.

This function works with strings, binary and compatible array columns.

SELECT concat('Spark', 'SQL');
 
concat(Spark, SQL)
1
SparkSQL

Showing all 1 rows.

SELECT concat(array(1, 2, 3), array(4, 5), array(6));
 
concat(array(1, 2, 3), array(4, 5), array(6))
1
[1, 2, 3, 4, 5, 6]

Showing all 1 rows.

flatten(array<array<T>>): array<T>

Transforms an array of arrays into a single array.

SELECT flatten(array(array(1, 2), array(3, 4)));
 
flatten(array(array(1, 2), array(3, 4)))
1
[1, 2, 3, 4]

Showing all 1 rows.

array_repeat(T, Int): array<T>

Returns the array containing element count times.

SELECT array_repeat('123', 2);
 
array_repeat(123, 2)
1
["123", "123"]

Showing all 1 rows.

reverse(String): String / reverse(array<T>): array<T>

Returns a reversed string or an array with reverse order of elements.

SELECT reverse('Spark SQL');
 
reverse(Spark SQL)
1
LQS krapS

Showing all 1 rows.

SELECT reverse(array(2, 1, 4, 3));
 
reverse(array(2, 1, 4, 3))
1
[3, 4, 1, 2]

Showing all 1 rows.

sequence(T, T[, T]): array<T>

Generates an array of elements from start to stop (inclusive), incrementing by step. The type of the returned elements is the same as the type of argument expressions.

SELECT sequence(1, 5);
 
sequence(1, 5)
1
[1, 2, 3, 4, 5]

Showing all 1 rows.

SELECT sequence(5, 1);
 
sequence(5, 1)
1
[5, 4, 3, 2, 1]

Showing all 1 rows.

SELECT sequence(to_date('2018-01-01'), to_date('2018-03-01'), interval 1 month);
 
sequence(to_date('2018-01-01'), to_date('2018-03-01'), INTERVAL '1 months')
1
["2018-01-01", "2018-02-01", "2018-03-01"]

Showing all 1 rows.