The column-level encryption feature of DLC allows you to encrypt sensitive data at the column level, such as email addresses, phone numbers, or ID numbers. By managing encryption keys in a separate key table and using the built-in AES encryption functions in DLC, you can protect sensitive fields while keeping other columns in plaintext to meet normal business query requirements.
Operation Overview
The implementation of column-level encryption is divided into the following four steps:
Step 1: Create databases and tables.
Create a database under DataLakeCatalog, and then create a key table (which stores the AES key for each customer) and a source data table (which contains the sensitive fields to be encrypted).
Step 2: Generate Encryption Keys.
Use the aes_key_v2() function to generate a unique AES key for each record, and then write it to the key table using a MERGE INTO statement to ensure that existing records do not have keys generated repeatedly.
Step 3: Encrypt Sensitive Data.
Create an encrypted data table, associate the source data table with the key table, and then use aes_encrypt_v2() to encrypt the specified sensitive columns and write them into the table, while keeping non-sensitive columns in plaintext.
Step 4: Decrypt and Read Data.
Authorized users can achieve on-demand decryption and reading by associating the encrypted data table with the key table and using aes_decrypt_v2() to convert ciphertext back to plaintext.
Prerequisites:
Use the standard Spark engine from DLC.
Have the corresponding permissions to create tables and execute queries.
Core Functions
aes_key_v2(): Generates a unique AES encryption key for each record.
aes_encrypt_v2(key, data, id): Encrypts a plaintext string using an AES key and an identifier.
aes_decrypt_v2(key, data, id): Decrypts an encrypted string and restores it to plaintext.
Assume you want to perform function encryption on the customer table information in the tpcds_sql_orc database. The operation steps are as follows:
Step 1: Creating a Database and Tables
1.1 Creating a Database
Open the Data Exploration page, select your standard Spark engine, and run the following SQL to create a database:
CREATE DATABASE IF NOT EXISTS tpcds_sql_orc;
1.2 Creating a Key Table
Create a table for storing the encryption key for each record. Each row maps a customer identifier to its unique AES key.
CREATE TABLE IF NOT EXISTS tpcds_sql_orc.cryptokey (c_customer_sk INT,c_key STRING) USING iceberg;
1.3 Creating a Source Data Table
Create a raw data table containing sensitive fields (such as email addresses):
CREATE TABLE IF NOT EXISTS tpcds_sql_orc.customer (c_customer_sk INT,c_first_name STRING,c_last_name STRING,c_email_address STRING) USING iceberg;
Insert sample data:
INSERT INTO tpcds_sql_orc.customer VALUES(1, 'John', 'Doe', 'john.doe@example.com'),(2, 'Jane', 'Smith', 'jane.smith@example.com'),(3, 'Bob', 'Wilson', 'bob.wilson@example.com');
Step 2: Generating an Encryption Key
Use MERGE INTO with aes_key_v2() to generate a unique AES key for each customer and insert it into the key table. The MERGE INTO statement ensures that new keys are generated only for new customers (that is, those not yet present in the key table).
MERGE INTO tpcds_sql_orc.cryptokey AS targetUSING (SELECT c_customer_sk, aes_key_v2() AS c_keyFROM tpcds_sql_orc.customer) AS sourceON target.c_customer_sk = source.c_customer_skWHEN NOT MATCHED THENINSERT (c_customer_sk, c_key)VALUES (source.c_customer_sk, source.c_key);
Verify the generated keys:
SELECT * FROM tpcds_sql_orc.cryptokey;
Expected result:
c_customer_sk | c_key |
1 | Ev3LK4ZN7ya06WQKZMVSm7N4GeOd2Rq4omucnuECHs= |
2 | inBr4BTgPWoAaCjNaGWpZsUaV0RAbFwjRxhOganF4= |
3 | xSpCNYO+qbj/hFWzUKcZd4fP/bMOauKO7zunxdcY= |
Note:
Each key is a Base64-encoded AES key and is unique per customer.
Step 3: Encrypting Sensitive Data
Create a new table to store encrypted user profile data. Then, by associating the source data table with the key table, encrypt and write the email addresses.
3.1 Creating an Encrypted Data Table
CREATE TABLE IF NOT EXISTS tpcds_sql_orc.customer_profile (c_customer_sk INT,c_first_name STRING,c_last_name STRING,c_email_address STRING) USING iceberg;
3.2 Writing Encrypted Data
Use aes_encrypt_v2(key, data, id) to encrypt the c_email_address column. The third parameter (id) serves as AAD (Additional Authenticated Data) and is used to bind the ciphertext to a specific record.
INSERT INTO tpcds_sql_orc.customer_profileSELECTc.c_customer_sk,c.c_first_name,c.c_last_name,aes_encrypt_v2(k.c_key, c.c_email_address, CAST(c.c_customer_sk AS STRING))FROM tpcds_sql_orc.customer cJOIN tpcds_sql_orc.cryptokey kON c.c_customer_sk = k.c_customer_sk;
Verify the encrypted data:
SELECT * FROM tpcds_sql_orc.customer_profile;
Expected result:
c_customer_sk | c_first_name | c_last_name | c_email_address |
1 | John | Doe | E5v9kORhAZceEe251xrUQbfx+PNkP4Q2Z2... |
2 | Jane | Smith | Y3aalCHBAMYzFDxqUgDQd8S3EXRS8Wby250p... |
3 | Bob | Wilson | KMEV9CUkdXd7YFwwcdqMC2phd2KHPrPRGBXU... |
You can see that the c_email_address column has been converted to encrypted ciphertext, while the c_first_name and c_last_name columns remain in plaintext.
Step 4: Decrypting and Reading Data
Authorized users can decrypt data by associating the encrypted table with the key table and using aes_decrypt_v2().
SELECTp.c_customer_sk,p.c_first_name,p.c_last_name,aes_decrypt_v2(k.c_key, p.c_email_address, CAST(p.c_customer_sk AS STRING)) AS decrypted_emailFROM tpcds_sql_orc.customer_profile pJOIN tpcds_sql_orc.cryptokey kON p.c_customer_sk = k.c_customer_sk;
Expected result:
c_customer_sk | c_first_name | c_last_name | decrypted_email |
1 | John | Doe | john.doe@example.com |
2 | Jane | Smith | jane.smith@example.com |
3 | Bob | Wilson | bob.wilson@example.com |
The original plaintext email address has been successfully restored.
Function Reference
Function | Syntax | Description |
aes_key_v2() | aes_key_v2() | Generates a random AES encryption key (returns a Base64-encoded string). |
aes_encrypt_v2 | aes_encrypt_v2(key, plaintext, aad) | Encrypts the plaintext using the given key and aad (additional authenticated data). Returns a Base64-encoded ciphertext string. |
aes_decrypt_v2 | aes_decrypt_v2(key, ciphertext, aad) | Decrypts the ciphertext using the given key and aad. Returns the original plaintext string. |
Note:
key (STRING): An AES encryption key generated by aes_key_v2().
plaintext (STRING): The plaintext data to be encrypted.
ciphertext (STRING): The encrypted data to be decrypted.
aad (STRING): Additional Authenticated Data (AAD). This value must remain consistent during encryption and decryption. Typically, a unique record identifier is used, such as CAST(c_customer_sk AS STRING).
Best Practices
1. Store Keys Separately: Always store encryption keys in a dedicated key table, separate from the encrypted data. This enables fine-grained access control—users who have access to the encrypted table but not the key table will be unable to decrypt the data.
2. Generate Keys Using MERGE INTO: Using MERGE INTO ensures that existing customers retain their original keys while generating new keys for new customers. This prevents key duplication and data corruption.
3. Keep AAD Values Consistent: The AAD (Additional Authenticated Data) parameter must match exactly during encryption and decryption. It is recommended to use a stable, unique identifier, such as a primary key.
4. Access Control: Configure DLC permissions to ensure that only authorized roles can query the key table. Common users should only have access to the encrypted data table.
5. Key Rotation: To rotate a key, first use aes_key_v2() to generate a new key. Then, decrypt the existing data using the old key, re-encrypt it with the new key, and finally update the key table.
Limitations:
The column-level encryption functions (aes_key_v2, aes_encrypt_v2, aes_decrypt_v2) are available only in the standard Spark engine.
The data type of the encrypted column must be STRING.
In DataLakeCatalog, tables must be created using the Iceberg format.