Spark SQL Built-in Functions

https://spark.apache.org/docs/latest/api/sql/
Admin
This documentation provides a comprehensive list and detailed explanations of the built-in functions...
Tokens:275,326
Snippets:1,840
Trust Score:10
Update:19 hours ago
Show doc for...
Tokens:
Raw
### now() example

Source: https://spark.apache.org/docs/latest/api/sql/datetime-functions/

Returns the current timestamp at the start of query evaluation. The example shows the output format.

```sql
> SELECT now();
 2020-04-25 15:49:11.914
```

--------------------------------

### time_bucket() examples

Source: https://spark.apache.org/docs/latest/api/sql/datetime-functions/

Returns the start of the bucket that a timestamp falls into, with optional origin. The examples show bucketing by 15 minutes, 1 hour, and 1 month.

```sql
> SELECT time_bucket(INTERVAL '15' MINUTE, TIMESTAMP '2024-01-01 11:27:00', TIMESTAMP '1970-01-01 00:00:00');
 2024-01-01 11:15:00
> SELECT time_bucket(INTERVAL '1' HOUR, TIMESTAMP '2024-01-01 11:27:00');
 2024-01-01 11:00:00
> SELECT time_bucket(INTERVAL '1' MONTH, TIMESTAMP '2024-07-20 14:30:00', TIMESTAMP '2024-06-15 09:00:00');
 2024-07-15 09:00:00
```

--------------------------------

### input_file_block_start example

Source: https://spark.apache.org/docs/latest/api/sql/misc-functions/

Returns the start offset of the block being read, or -1 if not available. The example shows -1 when not reading a file.

```sql
> SELECT input_file_block_start();
 -1
```

--------------------------------

### first examples

Source: https://spark.apache.org/docs/latest/api/sql/agg-functions/

Shows usage of first to get the first value, with optional isIgnoreNull parameter. Note: non-deterministic.

```sql
> SELECT first(col) FROM VALUES (10), (5), (20) AS tab(col);
 10
```

```sql
> SELECT first(col) FROM VALUES (NULL), (5), (20) AS tab(col);
 NULL
```

```sql
> SELECT first(col, true) FROM VALUES (NULL), (5), (20) AS tab(col);
 5
```

--------------------------------

### session_window() fixed gap duration example

Source: https://spark.apache.org/docs/latest/api/sql/datetime-functions/

Generates session windows with a fixed gap duration of 5 minutes. The example groups data by session window and shows the start, end, and count for each window.

```sql
> SELECT a, session_window.start, session_window.end, count(*) as cnt FROM VALUES ('A1', '2021-01-01 00:00:00'), ('A1', '2021-01-01 00:04:30'), ('A1', '2021-01-01 00:10:00'), ('A2', '2021-01-01 00:01:00') AS tab(a, b) GROUP by a, session_window(b, '5 minutes') ORDER BY a, start;
  A1    2021-01-01 00:00:00 2021-01-01 00:09:30 2
  A1    2021-01-01 00:10:00 2021-01-01 00:15:00 1
  A2    2021-01-01 00:01:00 2021-01-01 00:06:00 1
```

--------------------------------

### cardinality function examples

Source: https://spark.apache.org/docs/latest/api/sql/collection-functions/

Shows how to use the cardinality function to get the size of an array or a map. The first example returns the size of an array, the second returns the size of a map.

```sql
> SELECT cardinality(array('b', 'd', 'c', 'a'));
 4
> SELECT cardinality(map('a', 1, 'b', 2));
 2
```

--------------------------------

### current_database example

Source: https://spark.apache.org/docs/latest/api/sql/misc-functions/

Returns the current database. The example shows the default database as default.

```sql
> SELECT current_database();
 default
```

--------------------------------

### first_value examples

Source: https://spark.apache.org/docs/latest/api/sql/agg-functions/

Shows usage of first_value to get the first value, with optional isIgnoreNull parameter. Note: non-deterministic.

```sql
> SELECT first_value(col) FROM VALUES (10), (5), (20) AS tab(col);
 10
```

```sql
> SELECT first_value(col) FROM VALUES (NULL), (5), (20) AS tab(col);
 NULL
```

```sql
> SELECT first_value(col, true) FROM VALUES (NULL), (5), (20) AS tab(col);
 5
```

--------------------------------

### max function example

Source: https://spark.apache.org/docs/latest/api/sql/agg-functions/

Shows how to use the max aggregate function to get the maximum value of a group.

```sql
> SELECT max(col) FROM VALUES (10), (50), (20) AS tab(col);
 50
```

--------------------------------

### startswith function examples

Source: https://spark.apache.org/docs/latest/api/sql/string-functions/

Shows usage of the startswith function with string and binary inputs. Returns true if left starts with right, false otherwise, and NULL if either input is NULL. Both inputs must be STRING or BINARY type. Available since 3.3.0.

```sql
> SELECT startswith('Spark SQL', 'Spark');
 true
> SELECT startswith('Spark SQL', 'SQL');
 false
> SELECT startswith('Spark SQL', null);
 NULL
> SELECT startswith(x'537061726b2053514c', x'537061726b');
 true
> SELECT startswith(x'537061726b2053514c', x'53514c');
 false
```

--------------------------------

### current_schema example

Source: https://spark.apache.org/docs/latest/api/sql/misc-functions/

Returns the current database. The example shows the default schema as default.

```sql
> SELECT current_schema();
 default
```

--------------------------------

### to_char examples

Source: https://spark.apache.org/docs/latest/api/sql/string-functions/

Examples of the to_char function converting numeric, date, and binary values to strings using various formats. Each example shows the input and the resulting output.

```sql
> SELECT to_char(454, '999');
 454
```

```sql
> SELECT to_char(454.00, '000D00');
 454.00
```

```sql
> SELECT to_char(12454, '99G999');
 12,454
```

```sql
> SELECT to_char(78.12, '$99.99');
 $78.12
```

```sql
> SELECT to_char(-12454.8, '99G999D9S');
 12,454.8-
```

```sql
> SELECT to_char(date'2016-04-08', 'y');
 2016
```

```sql
> SELECT to_char(x'537061726b2053514c', 'base64');
 U3BhcmsgU1FM
```

```sql
> SELECT to_char(x'537061726b2053514c', 'hex');
 537061726B2053514C
```

```sql
> SELECT to_char(encode('abc', 'utf-8'), 'utf-8');
 abc
```

--------------------------------

### in examples

Source: https://spark.apache.org/docs/latest/api/sql/predicate-functions/

Examples of the in function checking if a value equals any of the listed values, including with structs.

```sql
> SELECT 1 in(1, 2, 3);
 true
```

```sql
> SELECT 1 in(2, 3, 4);
 false
```

```sql
> SELECT named_struct('a', 1, 'b', 2) in(named_struct('a', 1, 'b', 1), named_struct('a', 1, 'b', 3));
 false
```

```sql
> SELECT named_struct('a', 1, 'b', 2) in(named_struct('a', 1, 'b', 2), named_struct('a', 1, 'b', 3));
 true
```

--------------------------------

### try_make_interval examples

Source: https://spark.apache.org/docs/latest/api/sql/datetime-functions/

Examples of the try_make_interval function, which returns NULL on overflow. The examples show various argument combinations and their outputs.

```SQL
> SELECT try_make_interval(100, 11, 1, 1, 12, 30, 01.001001);
 100 years 11 months 8 days 12 hours 30 minutes 1.001001 seconds
```

```SQL
> SELECT try_make_interval(100, null, 3);
 NULL
```

```SQL
> SELECT try_make_interval(0, 1, 0, 1, 0, 0, 100.000001);
 1 months 1 days 1 minutes 40.000001 seconds
```

```SQL
> SELECT try_make_interval(2147483647);
 NULL
```

--------------------------------

### to_number examples

Source: https://spark.apache.org/docs/latest/api/sql/string-functions/

Examples of the to_number function converting strings to numbers using various formats. Each example shows the input string and the resulting numeric output.

```sql
> SELECT to_number('454', '999');
 454
```

```sql
> SELECT to_number('454.00', '000.00');
 454.00
```

```sql
> SELECT to_number('12,454', '99,999');
 12454
```

```sql
> SELECT to_number('$78.12', '$99.99');
 78.12
```

```sql
> SELECT to_number('12,454.8-', '99,999.9S');
 -12454.8
```

--------------------------------

### make_time examples

Source: https://spark.apache.org/docs/latest/api/sql/datetime-functions/

Shows creating a time from hour, minute, and second fields. Invalid inputs throw an error. The second example demonstrates NULL propagation.

```SQL
> SELECT make_time(6, 30, 45.887);
 06:30:45.887
> SELECT make_time(NULL, 30, 0);
 NULL
```

--------------------------------

### or function examples

Source: https://spark.apache.org/docs/latest/api/sql/predicate-functions/

Examples of the OR logical operator, showing results for various combinations of true, false, and NULL.

```sql
> SELECT true or false;
 true
```

```sql
> SELECT false or false;
 false
```

```sql
> SELECT true or NULL;
 true
```

```sql
> SELECT false or NULL;
 NULL
```

--------------------------------

### localtimestamp example

Source: https://spark.apache.org/docs/latest/api/sql/datetime-functions/

Returns the current timestamp without time zone at the start of query evaluation. Example shows a sample output.

```SQL
> SELECT localtimestamp();
 2020-04-25 15:49:11.914
```

--------------------------------

### day example

Source: https://spark.apache.org/docs/latest/api/sql/datetime-functions/

Returns the day of month of a date/timestamp. Example shows day('2009-07-30') returns 30.

```sql
> SELECT day('2009-07-30');
 30
```

--------------------------------

### slice: Subset array with start index and length

Source: https://spark.apache.org/docs/latest/api/sql/array-functions/

Examples of the slice function that subsets an array starting from a given index (1-based, negative starts from end) with specified length. Available since Spark 2.4.0.

```sql
> SELECT slice(array(1, 2, 3, 4), 2, 2);
 [2,3]
> SELECT slice(array(1, 2, 3, 4), -2, 2);
 [3,4]
```

--------------------------------

### to_xml examples

Source: https://spark.apache.org/docs/latest/api/sql/xml-functions/

Shows how to convert struct values to XML strings using to_xml. The second example uses a timestamp format option.

```sql
> SELECT to_xml(named_struct('a', 1, 'b', 2));
 <ROW>
     <a>1</a>
     <b>2</b>
 </ROW>
```

```sql
> SELECT to_xml(named_struct('time', to_timestamp('2015-08-26', 'yyyy-MM-dd')), map('timestampFormat', 'dd/MM/yyyy'));
 <ROW>
     <time>26/08/2015</time>
 </ROW>
```

--------------------------------

### row_number window function example

Source: https://spark.apache.org/docs/latest/api/sql/window-functions/

Assigns a unique sequential number to each row starting with one. The example uses row_number() over a partition by column a ordered by column b.

```sql
> SELECT a, b, row_number() OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
 A1 1   1
 A1 1   2
 A1 2   3
 A2 3   1
```

--------------------------------

### unbase64 example

Source: https://spark.apache.org/docs/latest/api/sql/string-functions/

Shows unbase64 converting a base64 string to binary. Available since 1.5.0.

```sql
> SELECT unbase64('U3BhcmsgU1FM');
 Spark SQL
```

--------------------------------

### position function examples

Source: https://spark.apache.org/docs/latest/api/sql/string-functions/

Examples of the position function returning 1-based position of substring, with optional starting position and POSITION ... IN syntax. Available since 2.3.0.

```SQL
> SELECT position('bar', 'foobarbar');
 4
```

```SQL
> SELECT position('bar', 'foobarbar', 5);
 7
```

```SQL
> SELECT POSITION('bar' IN 'foobarbar');
 4
```

--------------------------------

### from_csv examples

Source: https://spark.apache.org/docs/latest/api/sql/csv-functions/

Shows how to parse a CSV string into a struct using from_csv. The first example uses a simple schema, the second uses a timestamp format option.

```sql
> SELECT from_csv('1, 0.8', 'a INT, b DOUBLE');
 {"a":1,"b":0.8}
```

```sql
> SELECT from_csv('26/08/2015', 'time Timestamp', map('timestampFormat', 'dd/MM/yyyy'));
 {"time":2015-08-26 00:00:00}
```

--------------------------------

### current_time examples

Source: https://spark.apache.org/docs/latest/api/sql/datetime-functions/

Returns the current time at the start of query evaluation. The precision argument is optional and defaults to 6 fractional digits. Examples show precision 0, 3, and an expression.

```sql
> SELECT current_time();
 15:49:11.914120
> SELECT current_time;
 15:49:11.914120
> SELECT current_time(0);
 15:49:11
> SELECT current_time(3);
 15:49:11.914
> SELECT current_time(1+1);
 15:49:11.91
```

--------------------------------

### from_json examples

Source: https://spark.apache.org/docs/latest/api/sql/json-functions/

Shows how to parse JSON strings into structs using from_json with various schemas and options like timestampFormat.

```sql
> SELECT from_json('{"a":1, "b":0.8}', 'a INT, b DOUBLE');
 {"a":1,"b":0.8}
```

```sql
> SELECT from_json('{"time":"26/08/2015"}', 'time Timestamp', map('timestampFormat', 'dd/MM/yyyy'));
 {"time":2015-08-26 00:00:00}
```

```sql
> SELECT from_json('{"teacher": "Alice", "student": [{"name": "Bob", "rank": 1}, {"name": "Charlie", "rank": 2}]}', 'STRUCT<teacher: STRING, student: ARRAY<STRUCT<name: STRING, rank: INT>>>');
 {"teacher":"Alice","student":[{"name":"Bob","rank":1},{"name":"Charlie","rank":2}]}
```

--------------------------------

### get: Return element at 0-based index

Source: https://spark.apache.org/docs/latest/api/sql/array-functions/

Examples of the get function that returns the element at a given 0-based index. Returns NULL if the index is out of bounds or negative. Available since Spark 3.4.0.

```sql
> SELECT get(array(1, 2, 3), 0);
 1
> SELECT get(array(1, 2, 3), 3);
 NULL
> SELECT get(array(1, 2, 3), -1);
 NULL
```

--------------------------------

### from_xml examples

Source: https://spark.apache.org/docs/latest/api/sql/xml-functions/

Shows how to parse XML strings into structs using from_xml with various schemas and options. The first example parses simple fields, the second uses a timestamp format option, and the third parses nested structures.

```sql
> SELECT from_xml('<p><a>1</a><b>0.8</b></p>', 'a INT, b DOUBLE');
 {"a":1,"b":0.8}
```

```sql
> SELECT from_xml('<p><time>26/08/2015</time></p>', 'time Timestamp', map('timestampFormat', 'dd/MM/yyyy'));
 {"time":2015-08-26 00:00:00}
```

```sql
> SELECT from_xml('<p><teacher>Alice</teacher><student><name>Bob</name><rank>1</rank></student><student><name>Charlie</name><rank>2</rank></student></p>', 'STRUCT<teacher: STRING, student: ARRAY<STRUCT<name: STRING, rank: INT>>>');
 {"teacher":"Alice","student":[{"name":"Bob","rank":1},{"name":"Charlie","rank":2}]}
```

--------------------------------

### make_ym_interval examples

Source: https://spark.apache.org/docs/latest/api/sql/datetime-functions/

Shows how to create year-month intervals with make_ym_interval, including positive, negative, and omitted month arguments.

```SQL
> SELECT make_ym_interval(1, 2);
 1-2
```

```SQL
> SELECT make_ym_interval(1, 0);
 1-0
```

```SQL
> SELECT make_ym_interval(-1, 1);
 -0-11
```

```SQL
> SELECT make_ym_interval(2);
 2-0
```

--------------------------------

### to_json examples

Source: https://spark.apache.org/docs/latest/api/sql/json-functions/

Shows how to convert structs, arrays, and maps to JSON strings, including options like timestampFormat and sortKeys.

```sql
> SELECT to_json(named_struct('a', 1, 'b', 2));
 {"a":1,"b":2}
```

```sql
> SELECT to_json(named_struct('time', to_timestamp('2015-08-26', 'yyyy-MM-dd')), map('timestampFormat', 'dd/MM/yyyy'));
 {"time":"26/08/2015"}
```

```sql
> SELECT to_json(array(named_struct('a', 1, 'b', 2)));
 [{"a":1,"b":2}]
```

```sql
> SELECT to_json(map('a', named_struct('b', 1)));
 {"a":{"b":1}}
```

```sql
> SELECT to_json(map(named_struct('a', 1),named_struct('b', 2)));
 {"[1]":{"b":2}}
```

```sql
> SELECT to_json(map('a', 1));
 {"a":1}
```

```sql
> SELECT to_json(array(map('a', 1)));
 [{"a":1}]
```

```sql
> SELECT to_json(named_struct('b', 1, 'a', 2), map('sortKeys', 'true'));
 {"a":2,"b":1}
```

--------------------------------

### curdate example

Source: https://spark.apache.org/docs/latest/api/sql/datetime-functions/

Returns the current date at the start of query evaluation. All calls within the same query return the same value.

```sql
> SELECT curdate();
 2022-09-06
```

--------------------------------

### make_interval examples

Source: https://spark.apache.org/docs/latest/api/sql/datetime-functions/

Creates an interval from years, months, weeks, days, hours, minutes, and seconds. Examples show various inputs including NULL and fractional seconds.

```SQL
> SELECT make_interval(100, 11, 1, 1, 12, 30, 01.001001);
 100 years 11 months 8 days 12 hours 30 minutes 1.001001 seconds
> SELECT make_interval(100, null, 3);
 NULL
> SELECT make_interval(0, 1, 0, 1, 0, 0, 100.000001);
 1 months 1 days 1 minutes 40.000001 seconds
```

--------------------------------

### lead window function example

Source: https://spark.apache.org/docs/latest/api/sql/window-functions/

Returns the value of the input at the offset-th row after the current row. The example shows the output for the given query.

```sql
> SELECT a, b, lead(b) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
 A1 1   1
 A1 1   2
 A1 2   NULL
 A2 3   NULL
```

--------------------------------

### to_protobuf example

Source: https://spark.apache.org/docs/latest/api/sql/protobuf-functions/

Converts a Catalyst binary input value into its corresponding Protobuf format result. The example shows a SELECT query using to_protobuf with a descriptor file and options map, returning true for a NULL input.

```sql
> SELECT to_protobuf(s, 'Person', '/path/to/descriptor.desc', map('emitDefaultValues', 'true')) IS NULL FROM (SELECT NULL AS s);
 [true]
```

--------------------------------

### tuple_union_theta_double example

Source: https://spark.apache.org/docs/latest/api/sql/sketch-functions/

Merges a TupleSketch with double summary and a ThetaSketch using default lgNomEntries (12) and mode ('sum'). The ThetaSketch entries get a default summary of 0.0 in 'sum' mode. The example estimates the union as 6.0.

```sql
> SELECT tuple_sketch_estimate_double(tuple_union_theta_double(tuple_sketch_agg_double(col1, val1), theta_sketch_agg(col2))) FROM VALUES (1, 1.0D, 4), (2, 2.0D, 5), (3, 3.0D, 6) tab(col1, val1, col2);
 6.0
```

--------------------------------

### current_timestamp examples

Source: https://spark.apache.org/docs/latest/api/sql/datetime-functions/

Returns the current timestamp at the start of query evaluation. The syntax without braces has been supported since 2.0.1.

```sql
> SELECT current_timestamp();
 2020-04-25 15:49:11.914
> SELECT current_timestamp;
 2020-04-25 15:49:11.914
```

--------------------------------

### from_protobuf example

Source: https://spark.apache.org/docs/latest/api/sql/protobuf-functions/

Converts a binary Protobuf value into a Catalyst value. The example shows a SELECT query using from_protobuf with a descriptor file and options map, returning false for a NULL result. The schema must match the actual data, otherwise behavior is undefined.

```sql
> SELECT from_protobuf(s, 'Person', '/path/to/descriptor.desc', map()) IS NULL AS result FROM (SELECT NAMED_STRUCT('name', name, 'id', id) AS s FROM VALUES ('John Doe', 1), (NULL,  2) tab(name, id));
 [false]
```

--------------------------------

### current_date examples

Source: https://spark.apache.org/docs/latest/api/sql/datetime-functions/

Returns the current date at the start of query evaluation. The syntax without braces has been supported since 2.0.1.

```sql
> SELECT current_date();
 2020-04-25
> SELECT current_date;
 2020-04-25
```

--------------------------------

### json_array_length examples

Source: https://spark.apache.org/docs/latest/api/sql/json-functions/

Shows how to get the number of elements in a JSON array. Returns NULL for invalid JSON or non-array JSON.

```sql
> SELECT json_array_length('[1,2,3,4]');
  4
```

```sql
> SELECT json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]');
  5
```

```sql
> SELECT json_array_length('[1,2');
  NULL
```

--------------------------------

### ntile window function example

Source: https://spark.apache.org/docs/latest/api/sql/window-functions/

Divides rows in each window partition into n buckets. The example uses ntile(2) over a partition by column a ordered by column b.

```sql
> SELECT a, b, ntile(2) OVER (PARTITION BY a ORDER BY b) FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b);
 A1 1   1
 A1 1   1
 A1 2   2
 A2 3   1
```

--------------------------------

### max_by function examples

Source: https://spark.apache.org/docs/latest/api/sql/agg-functions/

Shows how to use the max_by aggregate function to get the value associated with the maximum of another column, and with a k parameter to get an array of the top k values. The function is non-deterministic for ties, and the maximum k is 100000.

```sql
> SELECT max_by(x, y) FROM VALUES ('a', 10), ('b', 50), ('c', 20) AS tab(x, y);
 b
```

```sql
> SELECT max_by(x, y, 2) FROM VALUES ('a', 10), ('b', 50), ('c', 20) AS tab(x, y);
 ["b","c"]
```

--------------------------------

### json_object_keys examples

Source: https://spark.apache.org/docs/latest/api/sql/json-functions/

Shows how to get all keys of the outermost JSON object as an array. Returns null for non-object JSON or empty string.

```sql
> SELECT json_object_keys('{}');
  []
```

```sql
> SELECT json_object_keys('{"key": "value"}');
  ["key"]
```

```sql
> SELECT json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}');
  ["f1","f2"]
```

--------------------------------

### to_csv examples

Source: https://spark.apache.org/docs/latest/api/sql/csv-functions/

Shows how to convert a struct to a CSV string using to_csv. The first example uses a simple struct, the second uses a timestamp format option.

```sql
> SELECT to_csv(named_struct('a', 1, 'b', 2));
 1,2
```

```sql
> SELECT to_csv(named_struct('time', to_timestamp('2015-08-26', 'yyyy-MM-dd')), map('timestampFormat', 'dd/MM/yyyy'));
 26/08/2015
```

--------------------------------

### tuple_intersection_theta_integer example

Source: https://spark.apache.org/docs/latest/api/sql/sketch-functions/

Intersects a TupleSketch with integer summaries and a ThetaSketch. The ThetaSketch entries get a default integer summary based on mode (0 for 'sum', Integer.MAX_VALUE for 'min', Integer.MIN_VALUE for 'max', 1 for 'alwaysone'). The example returns the estimated distinct count of the intersection.

```sql
> SELECT tuple_sketch_estimate_integer(tuple_intersection_theta_integer(tuple_sketch_agg_integer(col1, val1), theta_sketch_agg(col2))) FROM VALUES (1, 1, 1), (2, 2, 2), (3, 3, 4) tab(col1, val1, col2);
 2.0
```

--------------------------------

### overlay function examples

Source: https://spark.apache.org/docs/latest/api/sql/string-functions/

Examples of the overlay function replacing substrings with PLACING syntax, including binary input using encode. Available since 3.0.0.

```SQL
> SELECT overlay('Spark SQL' PLACING '_' FROM 6);
 Spark_SQL
```

```SQL
> SELECT overlay('Spark SQL' PLACING 'CORE' FROM 7);
 Spark CORE
```

```SQL
> SELECT overlay('Spark SQL' PLACING 'ANSI ' FROM 7 FOR 0);
 Spark ANSI SQL
```

```SQL
> SELECT overlay('Spark SQL' PLACING 'tructured' FROM 2 FOR 4);
 Structured SQL
```

```SQL
> SELECT overlay(encode('Spark SQL', 'utf-8') PLACING encode('_', 'utf-8') FROM 6);
 Spark_SQL
```

```SQL
> SELECT overlay(encode('Spark SQL', 'utf-8') PLACING encode('CORE', 'utf-8') FROM 7);
 Spark CORE
```

```SQL
> SELECT overlay(encode('Spark SQL', 'utf-8') PLACING encode('ANSI ', 'utf-8') FROM 7 FOR 0);
 Spark ANSI SQL
```

```SQL
> SELECT overlay(encode('Spark SQL', 'utf-8') PLACING encode('tructured', 'utf-8') FROM 2 FOR 4);
 Structured SQL
```

--------------------------------

### tuple_intersection_theta_double example

Source: https://spark.apache.org/docs/latest/api/sql/sketch-functions/

Intersects a TupleSketch with double summaries and a ThetaSketch. The ThetaSketch entries get a default double summary based on mode (0.0 for 'sum', +Infinity for 'min', -Infinity for 'max', 1.0 for 'alwaysone'). The example returns the estimated distinct count of the intersection.

```sql
> SELECT tuple_sketch_estimate_double(tuple_intersection_theta_double(tuple_sketch_agg_double(col1, val1), theta_sketch_agg(col2))) FROM VALUES (1, 1.0D, 1), (2, 2.0D, 2), (3, 3.0D, 4) tab(col1, val1, col2);
 2.0
```

--------------------------------

### spark_partition_id: get current partition id

Source: https://spark.apache.org/docs/latest/api/sql/misc-functions/

Returns the current partition id. The example shows a sample output. Available since Spark 1.4.0.

```sql
> SELECT spark_partition_id();
 0
```

--------------------------------

### input_file_name example

Source: https://spark.apache.org/docs/latest/api/sql/misc-functions/

Returns the name of the file being read, or an empty string if not available. The example shows no output when not reading a file.

```sql
> SELECT input_file_name();
```

--------------------------------

### array_insert function examples

Source: https://spark.apache.org/docs/latest/api/sql/array-functions/

Inserts a value into an array at a specified index. Indices start at 1; negative indices prepend. Available since Spark 3.4.0.

```sql
> SELECT array_insert(array(1, 2, 3, 4), 5, 5);
 [1,2,3,4,5]
```

```sql
> SELECT array_insert(array(5, 4, 3, 2), -1, 1);
 [5,4,3,2,1]
```

```sql
> SELECT array_insert(array(5, 3, 2, 1), -4, 4);
 [5,4,3,2,1]
```

--------------------------------

### base64 function examples

Source: https://spark.apache.org/docs/latest/api/sql/string-functions/

Converts a binary argument to a base 64 string. The examples show that both a string and a binary literal produce the same base64 output.

```sql
> SELECT base64('Spark SQL');
 U3BhcmsgU1FM
> SELECT base64(x'537061726b2053514c');
 U3BhcmsgU1FM
```

--------------------------------

### time_diff(unit, start, end)

Source: https://spark.apache.org/docs/latest/api/sql/datetime-functions/

Gets the difference between two TIME values in the specified unit. Supports units of HOUR, MINUTE, SECOND, MILLISECOND, and MICROSECOND.

```APIDOC
## time_diff(unit, start, end)

### Description
Gets the difference between the times in the specified units.

### Function Signature
time_diff(unit, start, end)

### Arguments
- **unit** (string) - Required - The unit of the difference. Valid values: "HOUR", "MINUTE", "SECOND", "MILLISECOND", "MICROSECOND".
- **start** (TIME) - Required - A starting TIME expression.
- **end** (TIME) - Required - An ending TIME expression.

### Examples
```sql
> SELECT time_diff('HOUR', TIME'20:30:29', TIME'21:30:28');
 0
> SELECT time_diff('HOUR', TIME'20:30:29', TIME'21:30:29');
 1
> SELECT time_diff('HOUR', TIME'20:30:29', TIME'12:00:00');
 -8
```

### Since
4.1.0
```

--------------------------------

### date_sub: Subtract days from date

Source: https://spark.apache.org/docs/latest/api/sql/datetime-functions/

Returns the date that is num_days before start_date. The example subtracts 1 day from '2016-07-30' to get '2016-07-29'. Available since 1.5.0.

```sql
> SELECT date_sub('2016-07-30', 1);
 2016-07-29
```

--------------------------------

### sum examples

Source: https://spark.apache.org/docs/latest/api/sql/agg-functions/

Examples of the sum function showing calculation over a group, handling of NULL values, and result when all values are NULL.

```sql
> SELECT sum(col) FROM VALUES (5), (10), (15) AS tab(col);
 30
```

```sql
> SELECT sum(col) FROM VALUES (NULL), (10), (15) AS tab(col);
 25
```

```sql
> SELECT sum(col) FROM VALUES (NULL), (NULL) AS tab(col);
 NULL
```

--------------------------------

### time_diff example

Source: https://spark.apache.org/docs/latest/api/sql/datetime-functions/

Computes the difference between two TIME values in the specified unit (HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND). Returns an integer; negative when end is before start.

```sql
> SELECT time_diff('HOUR', TIME'20:30:29', TIME'21:30:28');
 0
> SELECT time_diff('HOUR', TIME'20:30:29', TIME'21:30:29');
 1
> SELECT time_diff('HOUR', TIME'20:30:29', TIME'12:00:00');
 -8
```

--------------------------------

### session_user: get current user name

Source: https://spark.apache.org/docs/latest/api/sql/misc-functions/

Returns the user name of the current execution context. The example shows a sample output. Available since Spark 4.0.0.

```sql
> SELECT session_user();
 mockingjay
```

--------------------------------

### window function with 5-minute tumbling window

Source: https://spark.apache.org/docs/latest/api/sql/datetime-functions/

Buckets rows into 5-minute windows using the window function. The example groups by window and shows the start and end times along with counts.

```sql
> SELECT a, window.start, window.end, count(*) as cnt FROM VALUES ('A1', '2021-01-01 00:00:00'), ('A1', '2021-01-01 00:04:30'), ('A1', '2021-01-01 00:06:00'), ('A2', '2021-01-01 00:01:00') AS tab(a, b) GROUP by a, window(b, '5 minutes') ORDER BY a, start;
  A1    2021-01-01 00:00:00 2021-01-01 00:05:00 2
  A1    2021-01-01 00:05:00 2021-01-01 00:10:00 1
  A2    2021-01-01 00:00:00 2021-01-01 00:05:00 1
```

--------------------------------

### aggregate function examples

Source: https://spark.apache.org/docs/latest/api/sql/collection-functions/

Shows how to use the aggregate function to apply a binary operator to an initial state and all elements in an array, with an optional finish function. The first example sums the array elements, the second multiplies the final sum by 10.

```sql
> SELECT aggregate(array(1, 2, 3), 0, (acc, x) -> acc + x);
 6
> SELECT aggregate(array(1, 2, 3), 0, (acc, x) -> acc + x, acc -> acc * 10);
 60
```

--------------------------------

### typeof: get DDL-formatted type string

Source: https://spark.apache.org/docs/latest/api/sql/misc-functions/

Returns the DDL-formatted type string for the data type of the input. The example shows the type for an integer and an array. Available since Spark 3.0.0.

```sql
> SELECT typeof(1);
 int
> SELECT typeof(array(1));
 array<int>
```

--------------------------------

### approx_top_k function examples

Source: https://spark.apache.org/docs/latest/api/sql/agg-functions/

Shows usage of approx_top_k with default and custom k and maxItemsTracked parameters.

```sql
> SELECT approx_top_k(expr) FROM VALUES (0), (0), (1), (1), (2), (3), (4), (4) AS tab(expr);
 [{"item":0,"count":2},{"item":4,"count":2},{"item":1,"count":2},{"item":2,"count":1},{"item":3,"count":1}]

> SELECT approx_top_k(expr, 2) FROM VALUES 'a', 'b', 'c', 'c', 'c', 'c', 'd', 'd' AS tab(expr);
 [{"item":"c","count":4},{"item":"d","count":2}]

> SELECT approx_top_k(expr, 10, 100) FROM VALUES (0), (1), (1), (2), (2), (2) AS tab(expr);
 [{"item":2,"count":3},{"item":1,"count":2},{"item":0,"count":1}]
```

--------------------------------

### skewness examples

Source: https://spark.apache.org/docs/latest/api/sql/agg-functions/

Shows how to use the skewness aggregate function to compute the skewness of a group of values. The examples demonstrate positive and negative skewness results.

```sql
> SELECT skewness(col) FROM VALUES (-10), (-20), (100), (1000) AS tab(col);
 1.1135657469022011
```

```sql
> SELECT skewness(col) FROM VALUES (-1000), (-100), (10), (20) AS tab(col);
 -1.1135657469022011
```

--------------------------------

### substring function examples

Source: https://spark.apache.org/docs/latest/api/sql/string-functions/

Shows usage of the substring function with both positional and FROM/FOR syntax. Returns substring starting at pos with optional length, or byte array slice. Available since 1.5.0.

```sql
> SELECT substring('Spark SQL', 5);
 k SQL
> SELECT substring('Spark SQL', -3);
 SQL
> SELECT substring('Spark SQL', 5, 1);
 k
> SELECT substring('Spark SQL' FROM 5);
 k SQL
> SELECT substring('Spark SQL' FROM -3);
 SQL
> SELECT substring('Spark SQL' FROM 5 FOR 1);
 k
> SELECT substring(encode('Spark SQL', 'utf-8'), 5);
 k SQL
```

--------------------------------

### version() example

Source: https://spark.apache.org/docs/latest/api/sql/misc-functions/

Returns the Spark version, containing a release version and a git revision. Example output shows '3.1.0 a6d6ea3efedbad14d99c24143834cd4e2e52fb40'. Introduced in Spark 3.0.0.

```sql
> SELECT version();
 3.1.0 a6d6ea3efedbad14d99c24143834cd4e2e52fb40
```

--------------------------------

### substr function examples

Source: https://spark.apache.org/docs/latest/api/sql/string-functions/

Shows usage of the substr function with both positional and FROM/FOR syntax. Returns substring starting at pos with optional length, or byte array slice. Available since 1.5.0.

```sql
> SELECT substr('Spark SQL', 5);
 k SQL
> SELECT substr('Spark SQL', -3);
 SQL
> SELECT substr('Spark SQL', 5, 1);
 k
> SELECT substr('Spark SQL' FROM 5);
 k SQL
> SELECT substr('Spark SQL' FROM -3);
 SQL
> SELECT substr('Spark SQL' FROM 5 FOR 1);
 k
> SELECT substr(encode('Spark SQL', 'utf-8'), 5);
 k SQL
```

--------------------------------

### last_value function examples

Source: https://spark.apache.org/docs/latest/api/sql/agg-functions/

Shows how to use the last_value aggregate function to get the last value in a group, with optional isIgnoreNull parameter. The function is non-deterministic because results depend on row order after a shuffle.

```sql
> SELECT last_value(col) FROM VALUES (10), (5), (20) AS tab(col);
 20
```

```sql
> SELECT last_value(col) FROM VALUES (10), (5), (NULL) AS tab(col);
 NULL
```

```sql
> SELECT last_value(col, true) FROM VALUES (10), (5), (NULL) AS tab(col);
 5
```

--------------------------------

### to_avro examples

Source: https://spark.apache.org/docs/latest/api/sql/avro-functions/

Converts a Catalyst binary input value into its corresponding Avro format result. Two examples are shown: one with a JSON schema and one without, both returning true for NULL input.

```sql
> SELECT to_avro(s, '{"type": "record", "name": "struct", "fields": [{ "name": "u", "type": ["int","string"] }]}') IS NULL FROM (SELECT NULL AS s);
 [true]
```

```sql
> SELECT to_avro(s) IS NULL FROM (SELECT NULL AS s);
 [true]
```

--------------------------------

### some examples

Source: https://spark.apache.org/docs/latest/api/sql/agg-functions/

Shows how to use the some aggregate function to check if at least one value in a group is true. The examples demonstrate behavior with NULL values.

```sql
> SELECT some(col) FROM VALUES (true), (false), (false) AS tab(col);
 true
```

```sql
> SELECT some(col) FROM VALUES (NULL), (true), (false) AS tab(col);
 true
```

```sql
> SELECT some(col) FROM VALUES (false), (false), (NULL) AS tab(col);
 false
```

--------------------------------

### last function examples

Source: https://spark.apache.org/docs/latest/api/sql/agg-functions/

Shows how to use the last aggregate function to get the last value in a group, with optional isIgnoreNull parameter. The function is non-deterministic because results depend on row order after a shuffle.

```sql
> SELECT last(col) FROM VALUES (10), (5), (20) AS tab(col);
 20
```

```sql
> SELECT last(col) FROM VALUES (10), (5), (NULL) AS tab(col);
 NULL
```

```sql
> SELECT last(col, true) FROM VALUES (10), (5), (NULL) AS tab(col);
 5
```
Tokens:
Raw