aes_encrypt function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 10.3 and above

Encrypts a binary using AES encryption.


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


  • expr: The BINARY expression to be encrypted.

  • key: A BINARY expression. The key to be used to encrypt expr. It must be 16, 24, or 32 bytes long.

  • mode: An optional STRING expression describing the encryption mode.

  • padding: An optional STRING expression describing how encryption handles padding of the value to key length.



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'. PKCS padding adds between 1 and key-length number of bytes to pad expr to a multiple of key length. The value of each pad byte is the number of bytes being padded.

  • 'DEFAULT': Uses 'NONE' for 'GCM' and 'PKCS' for 'ECB' mode.

The algorithm depends on the length of the key:

  • 16: AES-128

  • 24: AES-192

  • 32: AES-256


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

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

> SELECT base64(aes_encrypt('Spark SQL', '1234567890abcdef', 'ECB', 'PKCS'));

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

> SELECT base64(aes_encrypt('Spark SQL', '1234567890abcdef', 'GCM'));

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