Overview
Version
FineDataLink Version | Function Description |
- | - |
4.1.5.1 | Added encryption and decryption functions. |
Function Description
Functions such as the encoding, encryption, and signature functions are commonly used to generate tokens for API encryption authentication during data fetching.
This document mainly introduces the encoding functions, environment variables, and other related features supported by the Spark SQL operator in FineDataLink.
Environment Variable

1. The relevant functions are in uppercase by default.
2. There is no difference between using single or double quotes when introducing strings.
Name | Description | Example |
UUID | A Random String | SELECT UUID() Note: The length of UUID cannot be changed. |
UNIX_TIMESTAMP | Timestamp (Timestamp) | SELECT unix_timestamp() |
Example:
For example, the user wants to fetch data through the UDESK API. The API authentication method requires the use of UNIX time. The nonce, a unique identifier for the request, is a random string provided by the caller that can only be used once within 15 minutes. In this example, the UUID function in SQL can be used to generate the nonce. Therefore, both of the above environment variables would be required.
Use Spark SQL directly as Input Source and enter the following SQL Statement.
SELECT UNIX_TIMESTAMP(now()) AS time_str, UUID() AS uuid_str;
Then the environment variables can be used to fetch the two specified values, as shown in the following figure.
Encoding and Decoding Functions

1. The relevant functions are in uppercase by default.
2. There is no difference between using single or double quotes when introducing strings.
SELECT SHA2(CONCAT("email&open_api_token",time_str,"&",uuid_str,"&v2") ,256) as sign_str,time_str,uuid_str from SparkSQL
Name | Function | Example |
BASE16 | BASE16(Expression) | SELECT BASE16('FineDataLink') |
base64 | base64(Expression) | Convert Expression to a base 64 string.
|
unbase64 | unbase64(Expression) ![]() | Returns the decoded base64 string in binary form. Example One You can use the following statement in the Spark SQL operator.
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 |
Encryption Function

1. The relevant functions are in uppercase by default.
2. There is no difference between using single or double quotes when introducing strings.
Name | Function | Parameter Description | Format and Return Value | Usage in the Spark SQL Operator of FDL |
MD5 | MD5(Expression) | Example: SELECT MD5 ('FineDataLink') | ||
SHA | SHA(Expression) | Example: SELECT SHA ('FineDataLink') | ||
SHA1 | SHA1(Expression) | Example: SELECT SHA1 ('FineDataLink') | ||
SHA2 | SHA2(Expression, BitLength) | ![]() 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) | ![]() 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 | ||
AES ![]() | aes_encrypt(Expression, key[, mode[, padding[, iv[, aad]]]]) |
| SELECT hex(aes_encrypt('Spark', '0000111122223333')); | |
DES ![]() | 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 ![]() | 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 ![]() | 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 |
Example:
In the section "Encoding and Decoding Functions", two values, timestamp and nonce, are obtained. The signature sign is generated by concatenating email, open_api_token, timestamp, nonce, and sign_version. You can use the following SQL Statement. sign = SHA256 (email & open_api_token & timestamp & nonce & sign_version). In this example, the encryption function is used.
Add a Spark SQL operator onto the page, connect it with the operator added in the section "Decryption Function," and enter the following statement.
SELECT SHA2(CONCAT("email&open_api_token",time_str,"&",uuid_str,"&v2") ,256) as sign_str,time_str,uuid_str from SparkSQL
Decryption Function

Name | Function | Parameter Description | Format and Return Value | Usage in the Spark SQL Operator of FDL |
AES | aes_encrypt(Expression, key[, mode[, padding[, iv[, aad]]]]) |
| 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 Return value: spark |
Example: For instance, use the decryption function by entering the following statement. The decrypted value is shown in the following figure.
SELECT des_decrypt(unhex('f0ecd50abef6bc01da5d0af7da1bdfb8879f8ec06c2d3837'),'my8bytky','CBC','PKCS5','12345678')
Signature Function

1. The relevant functions are in uppercase by default.
2. There is no difference between using single or double quotes when introducing strings.
Name | Function | Parameter Description | Format and Return Value | Usage in the Spark SQL Operator of FDL |
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 ![]() | sm3(Expression) | Expression: the text to be signed | Return value: a hexadecimal string | Example: SELECTsm3('spark'); Return value: 16CF694758A1BD2A93C6AA91389918908BFEDD14D9A95F567B557306B681C41C |
SM2 ![]() | 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'); |
Appendix
The sm2 key is generated online at https://config.net.cn/tools/SM2.html.
The sm4 key is generated online at https://config.net.cn/tools/SM4.html.
The RSA Key is generated online at https://www.metools.info/code/c80.html.