Misc Functions

This page lists all misc functions available in Spark SQL.

aes_decrypt

aes_decrypt(expr, key[, mode[, padding[, aad]]]) - Returns a decrypted value of expr using AES in mode with padding. Key lengths of 16, 24 and 32 bits are supported. Supported combinations of (mode, padding) are ('ECB', 'PKCS'), ('GCM', 'NONE') and ('CBC', 'PKCS'). Optional additional authenticated data (AAD) is only supported for GCM. If provided for encryption, the identical AAD value must be provided for decryption. The default mode is GCM.

Arguments:

  • expr - The binary value to decrypt. An expression that evaluates to a binary.
  • key - The passphrase to use to decrypt the data. An expression that evaluates to a binary.
  • mode - Specifies which block cipher mode should be used to decrypt messages. Valid modes: ECB, GCM, CBC. An expression that evaluates to a string.
  • padding - Specifies how to pad messages whose length is not a multiple of the block size. Valid values: PKCS, NONE, DEFAULT. The DEFAULT padding means PKCS for ECB, NONE for GCM and PKCS for CBC. An expression that evaluates to a string.
  • aad - Optional additional authenticated data. Only supported for GCM mode. This can be any free-form input and must be provided for both encryption and decryption. An expression that evaluates to a binary.

Examples:

> SELECT aes_decrypt(unhex('83F16B2AA704794132802D248E6BFD4E380078182D1544813898AC97E709B28A94'), '0000111122223333');
 Spark
> SELECT aes_decrypt(unhex('6E7CA17BBB468D3084B5744BCA729FB7B2B7BCB8E4472847D02670489D95FA97DBBA7D3210'), '0000111122223333', 'GCM');
 Spark SQL
> SELECT aes_decrypt(unbase64('3lmwu+Mw0H3fi5NDvcu9lg=='), '1234567890abcdef', 'ECB', 'PKCS');
 Spark SQL
> SELECT aes_decrypt(unbase64('2NYmDCjgXTbbxGA3/SnJEfFC/JQ7olk2VQWReIAAFKo='), '1234567890abcdef', 'CBC');
 Apache Spark
> SELECT aes_decrypt(unbase64('AAAAAAAAAAAAAAAAAAAAAPSd4mWyMZ5mhvjiAPQJnfg='), 'abcdefghijklmnop12345678ABCDEFGH', 'CBC', 'DEFAULT');
 Spark
> SELECT aes_decrypt(unbase64('AAAAAAAAAAAAAAAAQiYi+sTLm7KD9UcZ2nlRdYDe/PX4'), 'abcdefghijklmnop12345678ABCDEFGH', 'GCM', 'DEFAULT', 'This is an AAD mixed into the input');
 Spark

Since: 3.3.0


aes_encrypt

aes_encrypt(expr, key[, mode[, padding[, iv[, aad]]]]) - Returns an encrypted value of expr using AES in given mode with the specified padding. Key lengths of 16, 24 and 32 bits are supported. Supported combinations of (mode, padding) are ('ECB', 'PKCS'), ('GCM', 'NONE') and ('CBC', 'PKCS'). Optional initialization vectors (IVs) are only supported for CBC and GCM modes. These must be 16 bytes for CBC and 12 bytes for GCM. If not provided, a random vector will be generated and prepended to the output. Optional additional authenticated data (AAD) is only supported for GCM. If provided for encryption, the identical AAD value must be provided for decryption. The default mode is GCM.

Arguments:

  • expr - The binary value to encrypt. An expression that evaluates to a binary.
  • key - The passphrase to use to encrypt the data. An expression that evaluates to a binary.
  • mode - Specifies which block cipher mode should be used to encrypt messages. Valid modes: ECB, GCM, CBC. An expression that evaluates to a string.
  • padding - Specifies how to pad messages whose length is not a multiple of the block size. Valid values: PKCS, NONE, DEFAULT. The DEFAULT padding means PKCS for ECB, NONE for GCM and PKCS for CBC. An expression that evaluates to a string.
  • iv - Optional initialization vector. Only supported for CBC and GCM modes. Valid values: None or ''. 16-byte array for CBC mode. 12-byte array for GCM mode. An expression that evaluates to a binary.
  • aad - Optional additional authenticated data. Only supported for GCM mode. This can be any free-form input and must be provided for both encryption and decryption. An expression that evaluates to a binary.

Examples:

> SELECT hex(aes_encrypt('Spark', '0000111122223333'));
 83F16B2AA704794132802D248E6BFD4E380078182D1544813898AC97E709B28A94
> SELECT hex(aes_encrypt('Spark SQL', '0000111122223333', 'GCM'));
 6E7CA17BBB468D3084B5744BCA729FB7B2B7BCB8E4472847D02670489D95FA97DBBA7D3210
> SELECT base64(aes_encrypt('Spark SQL', '1234567890abcdef', 'ECB', 'PKCS'));
 3lmwu+Mw0H3fi5NDvcu9lg==
> SELECT base64(aes_encrypt('Apache Spark', '1234567890abcdef', 'CBC', 'DEFAULT'));
 2NYmDCjgXTbbxGA3/SnJEfFC/JQ7olk2VQWReIAAFKo=
> SELECT base64(aes_encrypt('Spark', 'abcdefghijklmnop12345678ABCDEFGH', 'CBC', 'DEFAULT', unhex('00000000000000000000000000000000')));
 AAAAAAAAAAAAAAAAAAAAAPSd4mWyMZ5mhvjiAPQJnfg=
> SELECT base64(aes_encrypt('Spark', 'abcdefghijklmnop12345678ABCDEFGH', 'GCM', 'DEFAULT', unhex('000000000000000000000000'), 'This is an AAD mixed into the input'));
 AAAAAAAAAAAAAAAAQiYi+sTLm7KD9UcZ2nlRdYDe/PX4

Since: 3.3.0


assert_true

assert_true(expr [, message]) - Throws an exception if expr is not true.

Arguments:

  • expr - The expression that is expected to be true. An expression that evaluates to a boolean.
  • message - The message for the exception thrown when the expression is not true. An expression that evaluates to a string.

Examples:

> SELECT assert_true(0 < 1);
 NULL

Since: 2.0.0


bitmap_bit_position

bitmap_bit_position(child) - Returns the bit position for the given input child expression.

Arguments:

  • child - The input expression to compute the bit position for. An expression that evaluates to a long.

Examples:

> SELECT bitmap_bit_position(1);
 0
> SELECT bitmap_bit_position(123);
 122

Since: 3.5.0


bitmap_bucket_number

bitmap_bucket_number(child) - Returns the bucket number for the given input child expression.

Arguments:

  • child - The input expression to compute the bucket number for. An expression that evaluates to a long.

Examples:

> SELECT bitmap_bucket_number(123);
 1
> SELECT bitmap_bucket_number(0);
 0

Since: 3.5.0


bitmap_count

bitmap_count(child) - Returns the number of set bits in the child bitmap.

Examples:

> SELECT bitmap_count(X '1010');
 2
> SELECT bitmap_count(X 'FFFF');
 16
> SELECT bitmap_count(X '0');
 0

Since: 3.5.0


current_catalog

current_catalog() - Returns the current catalog.

Examples:

> SELECT current_catalog();
 spark_catalog

Since: 3.1.0


current_database

current_database() - Returns the current database.

Examples:

> SELECT current_database();
 default

Since: 1.6.0


current_path

current_path() - Returns the current SQL path (qualified schema names).

Examples:

> SELECT current_path();
 system.builtin,system.session,spark_catalog.default

Since: 4.2.0


current_schema

current_schema() - Returns the current database.

Examples:

> SELECT current_schema();
 default

Since: 3.4.0


current_user

current_user() - user name of current execution context.

Examples:

> SELECT current_user();
 mockingjay

Since: 3.2.0


hmac

hmac(key, message[, algorithm]) - Returns the keyed-hash message authentication code (HMAC) of message using key and the hash algorithm. The result is returned as raw MAC bytes; wrap it with hex or base64 for a textual value. The default algorithm is 'SHA-256'.

Arguments:

  • key - The secret key, as a binary value.
  • message - The message to authenticate, as a binary value.
  • algorithm - Optional hash algorithm. Valid values: SHA-224, SHA-256, SHA-384, SHA-512, SHA-1, MD5. The default is SHA-256.

Examples:

> SELECT hex(hmac('key', 'message'));
 6E9EF29B75FFFC5B7ABAE527D58FDADB2FE42E7219011976917343065F58ED4A
> SELECT hex(hmac('key', 'message', 'SHA-512'));
 E477384D7CA229DD1426E64B63EBF2D36EBD6D7E669A6735424E72EA6C01D3F8B56EB39C36D8232F5427999B8D1A3F9CD1128FC69F4D75B434216810FA367E98

Since: 4.3.0


input_file_block_length

input_file_block_length() - Returns the length of the block being read, or -1 if not available.

Examples:

> SELECT input_file_block_length();
 -1

Since: 2.2.0


input_file_block_start

input_file_block_start() - Returns the start offset of the block being read, or -1 if not available.

Examples:

> SELECT input_file_block_start();
 -1

Since: 2.2.0


input_file_name

input_file_name() - Returns the name of the file being read, or empty string if not available.

Examples:

> SELECT input_file_name();

Since: 1.5.0


java_method

java_method(class, method[, arg1[, arg2 ..]]) - Calls a method with reflection.

Examples:

> SELECT java_method('java.util.UUID', 'randomUUID');
 c33fb387-8500-4bfa-81d2-6e0e3e930df2
> SELECT java_method('java.util.UUID', 'fromString', 'a5cf6c42-0c85-418f-af6c-3e4e5b1328f2');
 a5cf6c42-0c85-418f-af6c-3e4e5b1328f2

Since: 2.0.0


monotonically_increasing_id

monotonically_increasing_id() - Returns monotonically increasing 64-bit integers. The generated ID is guaranteed to be monotonically increasing and unique, but not consecutive. The current implementation puts the partition ID in the upper 31 bits, and the lower 33 bits represent the record number within each partition. The assumption is that the data frame has less than 1 billion partitions, and each partition has less than 8 billion records. The function is non-deterministic because its result depends on partition IDs.

Examples:

> SELECT monotonically_increasing_id();
 0

Since: 1.4.0


raise_error

raise_error( expr ) - Throws a USER_RAISED_EXCEPTION with expr as message.

Arguments:

  • expr - The message for the raised exception. An expression that evaluates to a string.

Examples:

> SELECT raise_error('custom error message');
 [USER_RAISED_EXCEPTION] custom error message

Since: 3.1.0


reflect

reflect(class, method[, arg1[, arg2 ..]]) - Calls a method with reflection.

Examples:

> SELECT reflect('java.util.UUID', 'randomUUID');
 c33fb387-8500-4bfa-81d2-6e0e3e930df2
> SELECT reflect('java.util.UUID', 'fromString', 'a5cf6c42-0c85-418f-af6c-3e4e5b1328f2');
 a5cf6c42-0c85-418f-af6c-3e4e5b1328f2

Since: 2.0.0


session_user

session_user() - user name of current execution context.

Examples:

> SELECT session_user();
 mockingjay

Since: 4.0.0


spark_partition_id

spark_partition_id() - Returns the current partition id.

Examples:

> SELECT spark_partition_id();
 0

Since: 1.4.0


try_aes_decrypt

try_aes_decrypt(expr, key[, mode[, padding[, aad]]]) - This is a special version of aes_decrypt that performs the same operation, but returns a NULL value instead of raising an error if the decryption cannot be performed.

Arguments:

  • expr - The encrypted value to decrypt. An expression that evaluates to a binary.
  • key - The key used to decrypt the value. An expression that evaluates to a binary.
  • mode - The block cipher mode used for decryption. An expression that evaluates to a string.
  • padding - The padding scheme used for decryption. An expression that evaluates to a string.
  • aad - The additional authenticated data. An expression that evaluates to a binary.

Examples:

> SELECT try_aes_decrypt(unhex('6E7CA17BBB468D3084B5744BCA729FB7B2B7BCB8E4472847D02670489D95FA97DBBA7D3210'), '0000111122223333', 'GCM');
 Spark SQL
> SELECT try_aes_decrypt(unhex('----------468D3084B5744BCA729FB7B2B7BCB8E4472847D02670489D95FA97DBBA7D3210'), '0000111122223333', 'GCM');
 NULL

Since: 3.5.0


try_reflect

try_reflect(class, method[, arg1[, arg2 ..]]) - This is a special version of reflect that performs the same operation, but returns a NULL value instead of raising an error if the invoke method thrown exception.

Examples:

> SELECT try_reflect('java.util.UUID', 'randomUUID');
 c33fb387-8500-4bfa-81d2-6e0e3e930df2
> SELECT try_reflect('java.util.UUID', 'fromString', 'a5cf6c42-0c85-418f-af6c-3e4e5b1328f2');
 a5cf6c42-0c85-418f-af6c-3e4e5b1328f2
> SELECT try_reflect('java.net.URLDecoder', 'decode', '%');
 NULL

Since: 4.0.0


typeof

typeof(expr) - Return DDL-formatted type string for the data type of the input.

Examples:

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

Since: 3.0.0


user

user() - user name of current execution context.

Examples:

> SELECT user();
 mockingjay

Since: 3.4.0


uuid

uuid() - Returns an universally unique identifier (UUID) string. The value is returned as a canonical UUID 36-character string.

Examples:

> SELECT uuid();
 46707d92-02f4-4817-8116-a4c3b23e6266

Note:

The function is non-deterministic.

Since: 2.3.0


version

version() - Returns the Spark version. The string contains 2 fields, the first being a release version and the second being a git revision.

Examples:

> SELECT version();
 3.1.0 a6d6ea3efedbad14d99c24143834cd4e2e52fb40

Since: 3.0.0