Spark SQL Encoding, Encryption, and Decryption Functions

  • Last update: February 11, 2025
  • 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

    iconNote:

    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.

    1.png

    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.

    2.png

    Encoding and Decoding Functions

    iconNote:

    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") ,256as 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.

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

    3.png

    unbase64

    unbase64(Expression)

    iconNote:
    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==')

    4.png

    Example Two

    1. Use the base64 function to encode strings.

    5.png

    The following figure shows the encoded data.

    6.png

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

    7.png

    The following figure shows the effect.

    8.png

    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

    iconNote:

    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 ValueUsage 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(ExpressionBitLength)



    iconNote:
    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)



    iconNote:

    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

    iconNote:
    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

    iconNote:
    Supported in 4.1.5.1 and later versions.

    des_encrypt(plainTextkey, 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

    iconNote:
    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

    iconNote:
    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

    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") ,256as sign_str,time_str,uuid_str from SparkSQL

    9.png

    Decryption Function

    iconNote:
    Supported in 4.1.5.1 and later versions.

    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]]]])

    • 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

    10.png

    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

    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')

    11.png


    Signature Function

    iconNote:

    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

    iconNote:
    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

    iconNote:
    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');

    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.

     

     


    附件列表


    主题: Data Development - Scheduled Task
    • Helpful
    • Not helpful
    • Only read

    滑鼠選中內容,快速回饋問題

    滑鼠選中存在疑惑的內容,即可快速回饋問題,我們將會跟進處理。

    不再提示

    10s後關閉

    Get
    Help
    Online Support
    Professional technical support is provided to quickly help you solve problems.
    Online support is available from 9:00-12:00 and 13:30-17:30 on weekdays.
    Page Feedback
    You can provide suggestions and feedback for the current web page.
    Pre-Sales Consultation
    Business Consultation
    Business: international@fanruan.com
    Support: support@fanruan.com
    Page Feedback
    *Problem Type
    Cannot be empty
    Problem Description
    0/1000
    Cannot be empty

    Submitted successfully

    Network busy