Spark SQL Encoding, Encryption, and Decryption Functions

-

-

4.1.5.1

Added encryption and decryption functions.

UUID

A Random String

SELECT UUID()

Note: The length of UUID cannot be changed.

UNIX_TIMESTAMP

Timestamp (Timestamp)

SELECT unix_timestamp()

BASE16

BASE16(Expression)

SELECT BASE16('FineDataLink')

base64

base64(Expression)

Convert Expression to a base 64 string.

select base64(cast('abcd' as binary))

unbase64

unbase64(Expression)

Note:

The data in parentheses needs to be a Base64-encoded string

Returns the decoded base64 string in binary form.

Example One

You can use the following statement in the Spark SQL operator. 

select unbase64('YWJjZA==')

Example Two

1. Use the base64 function to encode strings.

The following figure shows the encoded data.

2. Then use the unbase64 function to decode the string encoded by the base64 function.

The following figure shows the effect.

URLENCODE

URLENCODE(Expression)

SELECT URLENCODE('FineDataLink')

unhex

unhex(Expression)  

select unhex('537061726B2053514C')

Return value: Spark SQL

hex

hex(Expression)

select hex('Spark SQL')

Return value: 537061726 B2053514 C

encode

encode(String, Charset)

SELECT encode('abc', 'utf-8');

Return value: abc

decode

decode(Binary, Charset)

SELECT decode(encode('abc', 'utf-8'), 'utf-8');

Return value: abc

MD5(Expression)

Example: SELECT MD5 ('FineDataLink')

SHA

SHA(Expression)

Example: SELECT SHA ('FineDataLink')

SHA1

SHA1(Expression)

Example: SELECT SHA1 ('FineDataLink')

SHA2

SHA2(Expression, BitLength)

Note:

bitLength is the bit length of SHA 2, supporting SHA-224, SHA-256, SHA-384, and SHA-512, with the default being SHA-256.

Example: SELECT SHA 2('FineDataLink',256)

RSA

RSA(Expression,secretKey,keyFormat)

Note:

secretKey is the secret key entered. It is in base64 encoding format. keyFormat is the key format, with values of 'PKCS 1' and 'PKCS 8'(case-insensitive). The return value of this function is also in base64 encoding format.

Example: SELECT RSA( 'FineDataLink' , 'MIIBCgKCAQEAnLdoA3ba57YHBAenYbLGTcdC48VVvVVDXV6N/W+1FztBRjvNPV1D

MOcIJBrveTlgKug2PCVynaIttaNql6p/+Bm4G41kyZYy7RSaUCaJ3ryjcXsKfClt
nG9vCwbIN+bVchxRzj739zIA1tBHn9v22PhFcEfsSAy2G2EwM4bQ38n2UrMse9wb
LUGT0kzyquwPQs7vriU+1XBkrdssoAqbwgW5yUqxDosYB5h7D1YTW0qKkJ6PPNnL
XbMv2Meyjxq1sbWoF/m8uboaKklqal1ep5UqTp9OFNOaTrVyXY4Gkt7wq3OoNvk9
2cJ1fHz9wnriGo+oNut9gQr1WVjOzRkAwwIDAQAB' , 'PKCS1')

AES

Note:

Supported in 4.1.5.1 and later versions.

aes_encrypt(Expression, key[, mode[, padding[, iv[, aad]]]])

• Expression: the text to be encrypted

• key: the encryption key

• mode: the encryption mode, with the valid values being ECB, and GCM (optional)

• padding: the padding scheme for a specified length, with the valid values being PKCS, NONE, or DEFAULT (optional)

SELECT hex(aes_encrypt('Spark', '0000111122223333'));

DES

Note:

Supported in 4.1.5.1 and later versions.

des_encrypt(plainText, key, mode, padding, iv)

plainText: the plaintext   (the text to be encrypted)

plainText: the text

key: in text format or byte array format. Use unhex for keys in hexadecimal format, and use unbase64 for keys in Base64 format. For example: "miyao," unhex("123def"), unbase64("some_base64_string")

Return value: A byte array. To visualize the result, you need to add the hex function or base64 function to the original statement. For example: hex (des_encrypt (...)) or base64 (des_encrypt (...))

Example:

SELECT hex(des_encrypt(`Roxy@fanraun.com`,`my8bytky`,`ECB`,`PKCS5`,null))

key: the encryption key

mode: the encryption   mode, with the valid values being ECB (default), CBC, CFB, OFB, and CTR If null is passed, ECB encryption is used. An error will be reported if an invalid value is provided.

padding: padding mode, with the valid values being PKCS5(default), ISO10126, and no If null is passed, PKCS5 padding will be used. If an invalid value is provided, an error will be reported, and the case is not sensitive.

iv: the initialization vector, which is required for CBC, CFB, and OFB modes. For other modes, null can be passed.

sm4

Note:

Supported in 4.1.5.1 and later versions.

sm4_encrypt(plainText, key, mode, iv)

plainText: the text to   be encrypted

plainText: Text

key: in text format or byte array format. Use unhex for keys in hexadecimal format, and use unbase64 for keys in Base64 format. Example: "miyao", unhex("123def"), unbase64("some_base64_string")

Return value: A byte array. To visualize the result, you need to add the hex function or base64 function to the original statement. For example: hex(sm4_encrypt(...)) or base64(sm4_encrypt(...))

Example:

SELECT BASE64(sm4_encrypt('spark', unhex('30303030313131313232323233333333'), 'ECB', null));

O6F0JI1MwSQkSbU9Bjgb4A==
 

SELECT BASE64(sm4_encrypt('spark', '0000111122223333', 'ECB', null));

O6F0JI1MwSQkSbU9Bjgb4A==

key: the encryption key

mode: the encryption   mode, with the valid values being ECB (default), CBC, CFB, OFB, and CTR If null is passed, ECB encryption is used. An error will be reported if an invalid value is provided.

iv: the initialization vector, which is required for CBC mode. For ECB mode, pass null.

The padding mode is fixed to PKCS7.

sm2

Note:

Supported in 4.1.5.1 and later versions.

sm2_encrypt(plainText, publicKey)

plainText: the text to be encrypted

publicKey: the recipient's public key

The encryption mode is fixed to the new C1C3C2 version.

publicKey: hexadecimal data

Return value: Base64 encoding

SELECT sm2_encrypt('spark',   '04BDE0D3CBC2D3472AFAD7D020DDB3A8C3A8C22914E5E29753DF12EA91A199E2E1D9F0821EFEDB622D56DA4B719B62C7342852C0C42C84C4C83102761B678CF39F');

Return value:

BEucJcdQXwOxg7HRgZ8eWY00d2giXuEy6q5aI1w3QyVTH/np9LV4YrTfMOYCsQi3sGK6j/WP2UevXUb25g55Pri+Kqp97qfxxnxF655267XYBoHXyeWG/lCf1ZIzOk5GdHcz6UKh

sm2_encrypt_C1C2C3(plainText, publicKey)

plainText: the text to be encrypted

publicKey: the public key

The encryption mode is fixed to the old C1C2C3 version.

publicKey: the hexadecimal data

Return value: Base64 encoding

SELECT sm2_encrypt_C1C2C3('spark', '04BDE0D3CBC2D3472AFAD7D020DDB3A8C3A8C22914E5E29753DF12EA91A199E2E1D9F0821EFEDB622D56DA4B719B62C7342852C0C42C84C4C83102761B678CF39F');

Return value:

BJYqv1G2F7sSeC8/u2oHp5HBbg6KH9P0AmjtjT5hDcwXvBpLYLDVGZ8J7MvF8la3sMryd9hWTkxA/AOHlQLF5xa3Ko9X/+zgoNOR3pmY9P2pw9+hwO6e/xI4rgld1P2qdy9AAJ5W

AES

aes_encrypt(Expression, key[, mode[, padding[, iv[, aad]]]])

• Expression: the text to be decrypted

• key: the encryption key

• mode: the encryption mode, with the valid values being ECB, and GCM

• padding: the padding scheme for a specified length, with the valid values being PKCS, NONE, and DEFAULT

SELECT aes_decrypt(unhex('83F16B2AA704794132802D248E6BFD4E380078182D1544813898AC97E709B28A94'), '0000111122223333');

DES

des_decrypt(cipherText, key, mode, padding, iv)

cipherText: the ciphertext to be decrypted

cipherText: in text format or byte array format. Use unhex for ciphertext in hexadecimal format, and use unbase64 for ciphertext in Base64 format. For example: "miwen," unhex("123def"),   unbase64("some_base64_string")

Example: SELECT des_decrypt(unhex('f0ecd50abef6bc01da5d0af7da1bdfb8879f8ec06c2d3837'),'my8bytky','CBC','PKCS5','12345678')

Result: user@example.com

key: the decryption key

mode: the decryption mode, with the valid values being ECB (default), CBC, CFB, OFB, and CTR. If null is passed, ECB encryption is used. An error will be reported if an invalid value is provided.

padding: padding mode, with the valid values being PKCS5(default), ISO10126 and no If null is passed, PKCS5 padding will be used. If an invalid value is provided, an error will be reported, and the case is not sensitive.

SM4

sm4_encrypt(plainText, key, mode, iv)

Expression: the text to be decrypted

cipherText: in text format or byte array format. Use unhex for ciphertext in hexadecimal format, and use unbase64 for ciphertext in Base64 format. For example:   "miwen," unhex("123def"),   unbase64("some_base64_string")

key: in text format or byte array format. Use unhex   for keys in hexadecimal format, and use unbase64 for keys in Base64 format. For example: "miyao," unhex("123def"), unbase64("some_base64_string")

Return value: text

SELECT sm4_decrypt(unbase64('O6F0JI1MwSQkSbU9Bjgb4A=='), unhex('30303030313131313232323233333333'), 'ECB', null);

Return value: spark

SELECT sm4_decrypt(unbase64('O6F0JI1MwSQkSbU9Bjgb4A=='), unhex('30303030313131313232323233333333'), 'ECB', null);

Return value: spark

key: the encryption key

mode: the encryption mode, with the valid values being ECB (default) and CBC If null is passed, ECB encryption is used. An error will be reported if an invalid value is provided.

iv: the initialization vector, which is required for CBC mode. For ECB mode, pass null.

The padding mode is fixed to PKCS7.

SM2

sm2_decrypt(cipherText, privateKey)

Expression: the text to   be decrypted

key: the recipient's private key

cipherText: the Base64-encoded text

privateKey: hexadecimal data

Return value: text

SELECT sm2_decrypt('BEucJcdQXwOxg7HRgZ8eWY00d2giXuEy6q5aI1w3QyVTH/np9LV4YrTfMOYCsQi3sGK6j/WP2UevXUb25g55Pri+Kqp97qfxxnxF655267XYBoHXyeWG/lCf1ZIzOk5GdHcz6UKh','160430D73E9284086AF3B0D4B1D2EB2E40E00637D5288B6B0A736B2EF78AAEC6');

Return value: spark

sm2_decrypt_C1C2C3(cipherText, privateKey)

cipherText: the text to be decrypted

privateKey: The recipient's private key

cipherText: the Base64-encoded text

privateKey: hexadecimal data

Return value: text

SELECT sm2_decrypt_C1C2C3('BJYqv1G2F7sSeC8/u2oHp5HBbg6KH9P0AmjtjT5hDcwXvBpLYLDVGZ8J7MvF8la3sMryd9hWTkxA /AOHlQLF5xa3Ko9X/+zgoNOR3pmY9P2pw9+hwO6e/xI4rgld1P2qdy9AAJ5W','160430D73E9284086AF3B0D4B1D2EB2E40E00637D5288B6B0A736B2EF78AAEC6');

Return value: spark

RSA

rsa_decrypt(cipherText,   privateKey,keyFormat)

cipherText: the ciphertext to be decrypted

privateKey: the private   key

keyFormat: the format of   the key, which can be either 'PKCS1' or 'PKCS8' (case-insensitive)

cipherText: the Base64-encoded text

privateKey: the Base64-encoded key

Return value: text

SELECT rsa_decrypt('Xi/4Y2dsOFqbqpDXHGpqXZMWnbN5/t5OxFsqvOFBs+TVLWX9IiV5Ch63aFD9/SgG/ws2SOKhLhGJiIhuNQmS3w==','MIIBOAIBAAJAbN3FUygwojWFHamHJ5hhX2QvFSgRCAEuph1bqwh3kzi8uKj7A1So
  P4etzUY8zeda4eBNdy15uBSbHagY/N2JkQIDAQABAkAO7pgrIL8S8J5ShDcqgicQ
  62/dygIUA/1/AVXabq3f/mFwUppR/A7wopSB2eDdNPLyws5trUuF6ht1vbqFM0nl
  AiEAp7q9AY0ZnM1PAv/Ef0eRZCmMtxHWbK5ckQo06iSuNHMCIQCmKLmTi901LVCz
  Gz6CSRUOHBiolJEHa+0HUVvD/goM6wIgHc2bmfP1chw1BuZlhuwzlGWhoEHmJOeS
  gR8cKeKcpasCIB/9DSUbsx2OuM+SeilI+pW50QF7gKRGId7byefkm3i7AiB9DDuP
  mxDvGmv+vyiIlEtelk1nPXoMKAFdy19q3zA9Cg==','PKCS1');

Return value: spark

HMAC-MD5

HMACMD5(Expression,secretKey,format)

secretKey is the secret key entered. The format can be 'HEX' or 'BASE64'(case-insensitive).

SELECT HMACMD5( 'FineDataLink' , 'Im a secret key' , 'HEX')

HMAC-SHA1

HMACSHA1(Expression,secretKey,format)

secretKey is the secret key entered. The format can be 'HEX' or 'BASE64'(case-insensitive).

SELECT HMACSHA1( 'FineDataLink' , 'Im a secret key' , 'HEX')

HMAC-SHA256

HMACSHA256(Expression,secretKey,format)

secretKey is the secret key entered. The format can be 'HEX' or 'BASE64'(case-insensitive).

SELECT HMACSHA256( 'FineDataLink' , 'Im a secret key' , 'HEX')

SM3

Note:

Supported in 4.1.5.1 and later versions. 

sm3(Expression)

Expression: the text to be signed

Return value: a hexadecimal string

Example:

SELECTsm3('spark');

Return value: 16CF694758A1BD2A93C6AA91389918908BFEDD14D9A95F567B557306B681C41C

SM2

Note:

Supported in 4.1.5.1 and later versions.  

sm2_sign(data,   privateKey)

data: the data to be   signed

privateKey: the private   key (hexadecimal data)

the hexadecimal data

Example:  

SELECT sm2_sign('FineDataLink','9DAEF71B895D6C6E0CD0BE09E1B6A3356144892D6A03E46065EBA7EF24EDF3E3');