aes_decrypt function

Decrypts a binary produced using AES encryption.

Since: Databricks Runtime 10.3

Syntax

aes_decrypt(expr, key [, mode [, padding]])

Arguments

  • expr: The BINARY expression to be decrypted.
  • key: A BINARY expression. Must match the key originally used to produce the encrypted value and be 16, 24, or 32 bytes long.
  • mode: An optional STRING expression describing the encryption mode used to produce the encrypted value.
  • padding: An optional STRING expression describing how encryption handled padding of the value to key length.

Returns

A BINARY.

mode must be one of (case insensitive):

  • 'ECB': Use Electronic CodeBook (ECB) mode.
  • 'GCM': Use Galois/Counter Mode (GCM). This is the default.

padding must be one of (case insensitive):

  • 'NONE': Uses no padding. Valid only for 'GCM'.
  • 'PKCS': Uses Public Key Cryptography Standards (PKCS) padding. Valid only for 'ECB'.
  • 'DEFAULT': Uses 'NONE' for 'GCM' and 'PKCS' for 'ECB' mode.

Examples

> SELECT base64(aes_encrypt('Spark', 'abcdefghijklmnop'));
  4A5jOAh9FNGwoMeuJukfllrLdHEZxA2DyuSQAWz77dfn

> SELECT cast(aes_decrypt(unbase64('4A5jOAh9FNGwoMeuJukfllrLdHEZxA2DyuSQAWz77dfn'),
                          'abcdefghijklmnop') AS STRING);
  Spark

> SELECT base64(aes_encrypt('Spark SQL', '1234567890abcdef', 'ECB', 'PKCS'));
  3lmwu+Mw0H3fi5NDvcu9lg==

> SELECT cast(aes_decrypt(unbase64('3lmwu+Mw0H3fi5NDvcu9lg=='),
                          '1234567890abcdef', 'ECB', 'PKCS') AS STRING);
  Spark SQL

> SELECT base64(aes_encrypt('Spark SQL', '1234567890abcdef', 'GCM'));
  2sXi+jZd/ws+qFC1Tnzvvde5lz+8Haryz9HHBiyrVohXUG7LHA==

> SELECT cast(aes_decrypt(unbase64('2sXi+jZd/ws+qFC1Tnzvvde5lz+8Haryz9HHBiyrVohXUG7LHA=='),
                          '1234567890abcdef', 'GCM') AS STRING);
  Spark SQL