LOAD TABLE ENCRYPTED Clause

The column-specs must follow the column name in a LOAD TABLE statement in this order:

format-specs
null-specs
encrypted-specs

Syntax

| ENCRYPTED(data-type ‘key-string’ [, ‘algorithm-string’ ] )

Parameters

data-type – the data type that the input file field should be converted to as input to the AES_ENCRYPT function. data-type should be the same as the data type of the output of the AES_DECRYPT function.
key-string – the encryption key used to encrypt the data. This key must be a string literal. To obtain the original value, use the same key to decrypt the value. This parameter is case-sensitive, even in case-insensitive databases.
As you should for most passwords, choose a key value that cannot be easily guessed. Choose a value for that is at least 16 characters long, contains a mix of uppercase and lowercase letters, and includes numbers and special characters. You will need this key each time you want to decrypt the data.

Warning! Protect your key; store a copy of your key in a safe location. A lost key results in the encrypted data becoming completely inaccessible, from which there is no recovery.
algorithm-string – the algorithm used to encrypt the data. This parameter is optional, but data must be encrypted and decrypted using the same algorithm. Currently, AES is the default, as it is the only supported algorithm. AES is a block encryption algorithm chosen as the new Advanced Encryption Standard (AES) for block ciphers by the National Institute of Standards and Technology (NIST).

Usage

The ENCRYPTED column specification allows you to specify the encryption key and, optionally, the algorithm to use to encrypt the data that is loaded into the column. The target column for this load should be VARBINARY. Specifying other data types returns an error.

Example

LOAD TABLE table_name
(
plaintext_column_name,
a_ciphertext_column_name 
NULL('nil') 
ENCRYPTED(varchar(6),'tHefiRstkEy') ,
another_encrypted_column
ENCRYPTED(bigint,'thEseconDkeY','AES')
)
FROM '/path/to/the/input/file'
FORMAT ascii
DELIMITED BY ';'
ROW DELIMITED BY '\0xa'
QUOTES OFF
ESCAPES OFF

where the format of the input file for the LOAD TABLE statement is:

a;b;c;
d;e;f;
g;h;i;