Impala UDFs

This section explains the Impala protector, the UDFs provided, and the commands for protecting and unprotecting data in an Impala table.

Overview of the Impala Protector

Impala is an MPP SQL query engine for querying the data stored in a cluster. The Protegrity Impala protector extends the functionality of the Impala query engine and provides UDFs which protect or unprotect the data as it is stored or retrieved.

Impala Protector Usage

The Protegrity Impala protector provides UDFs for protecting data using encryption or tokenization, and unprotecting data by using decryption or detokenization.

Ensure that the /user/impala path exists in HDFS with the Impala supergroup permissions. To verify the path, use the following command:

# hadoop fs –ls /user

Creating the /user/impala path in Impala with Supergroup permissions

If the /user/impala path does not exist or does not have supergroup permissions, then perform the following steps.

1. To create the /user/impala directory in HDFS, run the following command:

   # sudo –u hdfs hadoop –mkdir /user/impala
   

2. To assign Impala supergroup permissions to the /user/impala path, run the following command:

   # sudo –u hdfs hadoop –chown –R impala:supergroup /user/impala
   

Inserting Data from a File into a Table

To insert data from a file into an Impala table, ensure that the required user permissions for the directory path in HDFS are assigned for the Impala table.

Preparing the environment for the basic_sample.csv file

1. To assign permissions to the path where data from the basic_sample.csv file needs to be copied, run the following command:

   sudo -u hdfs hadoop fs -chown root:root /tmp/basic_sample/sample/
   

2. To copy the basic_sample.csv file into HDFS, run the following command:

   hdfs dfs -put basic_sample.csv /tmp/basic_sample/sample/
   

3. To verify the presence of the basic_sample.csv file in the HDFS path, run the following command:

   hdfs dfs -ls /tmp/basic_sample/sample/
   

4. To assign permissions for Impala to the path where the basic_sample.csv file is located, run the following command:

   sudo -u hdfs hadoop fs -chown impala:supergroup /path/
   

Populating the table sample_table from the basic_sample_data.csv file

You can use the following command populate the basic_sample table with the data from the basic_sample_data.csv file:

create table sample_table(colname1 colname1_format, colname2 colname2_format, colname3 colname3_format) row format delimited fields terminated by ',';
LOAD DATA INPATH '/tmp/basic_sample/sample/basic_sample.csv' INTO TABLE sample_table;

Parameters:

• sample_table: Name of the Impala table created to load the data from the input CSV file from the required path.
• colname1, colname2, colname3: Name of the columns.
• colname1_format, colname2_format, colname3_format: The data types contained in the respective columns. The data types can only be of types STRING, INT, DOUBLE, or FLOAT.
• ATTRIBUTES: Additional parameters to consider when ingesting the data. In the example, the row format is delimited using the ‘,’ character because the row format in the input file is comma separated. If the input file is tab separated, then the the row format is delimited using ‘\t’.

Protecting Existing Data

To protect existing data, you must define the mappings between the columns and their respective data elements in the data security policy. The following commands ingest cleartext data from the basic_sample table to the basic_sample_protected table in protected form using Impala UDFs.

create table basic_sample_protected (colname1 colname1_format, colname2 colname2_format, colname3 colname3_format);
insert into basic_sample_protected(colname1, colname2, colname3) select ID,pty_stringins(colname1, dataElement1),pty_stringins(colname2, dataElement2),pty_stringins(colname3, dataElement3) from basic_sample;

Parameters:

• basic_sample_protected: Table to store protected data.
• colname1, colname2, colname3: Name of the columns.
• dataElement1, dataElement2, dataElement3: The data elements corresponding to the columns.
• basic_sample: Table containing the original data in cleartext form.

Unprotecting Protected Data

To unprotect the protected data, you must specify the name of the table which contains the protected data, the table which would store the unprotected data, and the columns and their respective data elements. Ensure that the user performing the task has permissions to unprotect the data as required in the data security policy. The following commands unprotect the protected data in a table and stores the data in cleartext form in to a different table, if the user has the required permissions.

create table table_unprotected (colname1 colname1_format, colname2 colname2_format, colname3 colname3_format);
insert into table_unprotected (colname1, colname2, colname3) select ID,pty_stringsel(colname1,dataElement1), pty_stringsel(colname2, dataElement2),pty_stringsel(colname3, dataElement3) from table_protected;

Parameters:

• table_unprotected: Table to store unprotected data.
• colname1, colname2, colname3: Name of the columns.
• dataElement1, dataElement2, dataElement3: The data elements corresponding to the columns.
• table_protected: Table containing protected data.

Retrieving Data from a Table

To retrieve data from a table, you must have access to the table. The following command displays the data contained in the table.

select * from table;

Parameters:

• table: Name of the table.

Impala UDFs

pty_GetVersion()

The UDF returns the PepImpala version.

Signature:

pty_getversion()

Parameters:

• None

Result:

• The UDF returns the PepImpala version.

Example:

select pty_GetVersion();

pty_GetVersionExtended()

The UDF returns the extended version information.

Signature:

pty_getversionextended();

Parameters:

• None

Result:

• The UDF returns a string in the following format:

  Impala: <1>; CORE: <2>;
  

  where,
  • 1. Is the PepImpala version
  • 2. Is the Core library version

Example:

select pty_getversionextended();

pty_WhoAmI()

The UDF returns the logged in user name.

Signature:

pty_WhoAmI()

Parameters:

• None

Result:

• The UDF returns the logged in user name.

Example:

select pty_WhoAmI();

pty_StringEnc()

The UDF returns the encrypted value for a column containing String format data.

Signature:

pty_StringEnc(data string, dataElement string)

Parameters:

• data : Specifies the column name of the data to encrypt in the table.
• dataElement: Specifies the name of the data element to encrypt the string value.

Result:

• The UDF returns the string value.

Example:

select pty_StringEnc(column_name,'enc_3des') from table_name;

Supported Protection Methods:

pty_StringDec()

The UDF returns the decrypted value for a column containing String format data.

Signature:

pty_StringDec(data string, dataElement string)

Parameters:

• data : Specifies the column name of the data to decrypt in the table.
• dataElement: Is the variable specifying the unprotection method.

Result:

• The UDF returns the string value.

Example:

select pty_StringDec(column_name,'enc_3des') from table_name;

Supported Protection Methods:

pty_StringIns()

The UDF returns the tokenized value for a column containing String format data.

Note: For Date and Datetime type of data elements, the protect API returns an invalid input data error if the input value falls between the non-existent date range from 05-OCT-1582 to 14-OCT-1582 of the Gregorian Calendar.
For more information about the tokenization and de-tokenization of the cutover dates of the Proleptic Gregorian Calendar, refer to the section Date and Datetime tokenization.

Signature:

pty_StringIns(data string, dataElement string)

Parameters:

• data: Specifies the column name of the data to tokenize in the table.
• dataElement: Specifies the name of the data element to protect the string value.

Result:

• The UDF returns the tokenized string value.

Example:

select pty_StringIns(column_name, 'TOK_NAME') from table_name;

Supported Protection Methods:

pty_StringSel()

The UDF returns the detokenized value for a column containing String format data.

Note: For Date and Datetime type of data elements, the protect API returns an invalid input data error if the input value falls between the non-existent date range from 05-OCT-1582 to 14-OCT-1582 of the Gregorian Calendar.
For more information about the tokenization and de-tokenization of the cutover dates of the Proleptic Gregorian Calendar, refer Date and Datetime tokenization.

Signature:

pty_StringSel(data string, dataElement string)

Parameters:

• data: Specifies the column name of the data to detokenize in the table.
• dataElement: Specifies the name of the data element to unprotect the string value.

Result:

• The UDF returns the detokenized string value.

Example:

select pty_StringSel(column_name, 'TOK_NAME') from table_name;

Supported Protection Methods:

pty_UnicodeStringIns()

The UDF returns the tokenized value for a column containing String (Unicode) format data.

Signature:

pty_UnicodeStringIns(data string, dataElement string)

Parameters:

• data: Specifies the column name of the string (Unicode) format data to tokenize in the table.
• dataElement: Specifies the name of the data element to protect the string (Unicode) value.

Warning: This UDF should be used only if you want to tokenize Unicode data in Impala, and migrate the tokenized data from Impala to a Teradata database and detokenize the data using the Protegrity Database Protector. Ensure that you use this UDF with a Unicode tokenization data element only.

Result:

• The UDF returns the protected string value.

Example:

select pty_UnicodeStringIns(column_name, 'Token_unicode') from temp_table;

Supported Protection Methods:

pty_UnicodeStringSel()

The UDF unprotects the existing protected String value.

Signature:

pty_UnicodeStringSel(data string, dataElement string)

Parameters:

• data: Specifies the column name of the string format data to detokenize in the table.
• varchar dataElement: Specifies the name of data element to unprotect the string value.

Warning: This UDF should be used only if you want to tokenize Unicode data in Teradata using the Protegrity Database Protector, and migrate the tokenized data from a Teradata database to Impala and detokenize the data using the Protegrity Big Data Protector for Impala. Ensure that you use this UDF with a Unicode tokenization data element only.

Result:

• The UDF returns the detokenized string (Unicode) value.

Example:

select pty_UnicodeStringSel(column_name, 'Token_unicode') from temp_table;

Supported Protection Methods:

pty_UnicodeStringFPEIns()

The UDF returns the encrypted value for a column containing String (Unicode) format data with Format Preserving Encryption (FPE) as the protection method.

Note: Ensure that you use this UDF with an FPE data element only.

Warning: The pty_UnicodeStringFPEIns() UDF will be deprecated from the future releases. This UDF is retained in this build for backward compatibility purposes only.

Signature:

pty_UnicodeStringFPEIns(data string, dataElement string)

Parameters:

• data: Specifies the column name of the data to encrypt in the table.
• dataElement: Specifies the name of the FPE data element to protect the string value.

Result:

• The UDF returns the string value.

Example:

SELECT pty_unicodestringfpeins(column_name,'<DataElement>') from table_name;

Supported Protection Methods:

pty_UnicodeStringFPESel()

The UDF unprotects the existing encrypted String value that was encrypted using the FPE enabled data element.

Note: Ensure that you use this UDF with an FPE data element only.

Warning: The pty_UnicodeStringFPESel() UDF will be deprecated from the future releases. This UDF is retained in this build for backward compatibility purposes only.

Signature:

pty_UnicodeStringFPESel(data string, dataElement string)

Parameters:

• data: Specifies the column name of the data to decrypt in the table.
• varchar dataElement: Is the variable specifying the detokenization method. Note: Ensure that the FPE data element used to tokenize and detokenize the data is same.

Result:

• The UDF returns the decrypted string (Unicode) value.

Example:

select pty_unicodestringfpesel(NAME,'<DataElement>') from table_name;

Supported Protection Methods:

pty_IntegerEnc()

The UDF returns an encrypted value for a column containing Integer format data.

Signature:

pty_IntegerEnc(data integer, dataElement string)

Parameters:

• data: Specifies the column name of the data to encrypt in the table.
• dataElement: Specifies the name of the data element to encrypt the integer value.

Result:

• The UDF returns a string value.

Example:

select pty_IntegerEnc(column_name,'enc_3des') from table_name;

Supported Protection Methods:

pty_IntegerDec()

The UDF returns the decrypted value for a column containing Integer format data.

Signature:

pty_IntegerDec(data string, dataElement string)

Parameters:

• data: Specifies the column name of the data to decrypt in the table.
• dataElement: Specifies the name of the data element to decrypt the integer value.

Result:

• The UDF returns an integer value.

Example:

select pty_IntegerDec(column_name,'enc_3des') from table_name;

Supported Protection Methods:

pty_IntegerIns()

The UDF returns the tokenized value for a column containing Integer format data.

Signature:

pty_IntegerIns(data integer, dataElement string)

Parameters:

• data: Specifies the column name of the data to tokenize in the table.
• dataElement: Specifies the name of the data element to protect the integer value.

Result:

• The UDF returns the tokenized integer value.

Example:

select pty_IntegerIns(column_name,'integer_de') from table_name;

Supported Protection Methods:

pty_IntegerSel()

The UDF returns the detokenized value for a column containing Integer format data.

Signature:

pty_IntegerSel(data integer, dataElement string)

Parameters:

• data: Specifies the column name of the data to detokenize in the table.
• dataElement: Specifies the name of the data element to unprotect the integer value.

Result:

• The UDF returns the detokenized integer value.

Example:

select pty_IntegerSel(column_name,'integer_de') from table_name;

Supported Protection Methods:

pty_FloatEnc()

The UDF returns the encrypted value for a column containing Float format data.

Signature:

pty_FloatEnc(data float, dataElement string)

Parameters:

• data: Specifies the column name of the data to encrypt in the table.
• dataElement: Specifies the name of the data element to encrypt the float value.

Result:

• The UDF returns a string value.

Example:

select pty_FloatEnc(column_name,'enc_3des') from table_name;

Supported Protection Methods:

pty_FloatDec()

The UDF returns the decrypted value for a column containing Float format data.

Signature:

pty_FloatDec(data string, dataElement string)

Parameters:

• data: Specifies the column name of the data to decrypt in the table.
• dataElement: Specifies the name of the data element to decrypt the float value.

Result:

• The UDF returns a string value.

Example:

select pty_FloatDec(column_name,'enc_3des') from table_name;

Supported Protection Methods:

pty_FloatIns()

The UDF returns the tokenized value for a column containing Float format data.

Signature:

pty_FloatIns(data float, dataElement string)

Parameters:

• data: Specifies the column name of the data to tokenize in the table.
• dataElement: Specifies the name of the data element to protect the float value.

Result:

• The UDF returns the tokenized float value.

Example:

select pty_FloatIns(cast(12.3 as float), 'no_enc');

Warning: Ensure that you use the data element with the No Encryption method only. Using any other data element would return an error mentioning that the operation is not supported for that data type. If you want to tokenize the Float column, then load the Float column into a String column and use the pty_StringIns() UDF to tokenize the column. For more information about pty_StringIns() UDF, refer section pty_StringIns().

Supported Protection Methods:

pty_FloatSel()

The UDF returns the detokenized value for a column containing Float format data.

Signature:

pty_FloatSel(data float, dataElement string)

Parameters:

• data: Specifies the column name of the data to detokenize in the table.
• dataElement: Specifies the name of the data element to unprotect the float value.

Result:

• The UDF returns the detokenized float value.

Example:

select pty_FloatSel(tokenized_value, 'no_enc');

Warning: Ensure that you use the data element with the No Encryption method only. Using any other data element would return an error mentioning that the operation is not supported for that data type.

Supported Protection Methods:

pty_DoubleEnc()

The UDF returns the encrypted value for a column containing Double format data.

Signature:

pty_DoubleEnc(data double, dataElement string)

Parameters:

• data: Specifies the double data column to encrypt in the table.
• dataElement: Specifies the name of the data element to encrypt the double value.

Result:

• The UDF returns a string.

Example:

select pty_DoubleEnc(column_name,'enc_3des') from table_name;

Supported Protection Methods:

pty_DoubleDec()

The UDF returns the decrypted value for a column containing Double format data.

Signature:

Pty_DoubleDec(data string, dataElement string)

Parameters:

• data: Specifies the double data column to decrypt in the table.
• dataElement: Specifies the name of the data element to decrypt the double value.

Result:

• The UDF returns a double value.

Example:

select pty_DoubleDec(column_name,'enc_3des') from table_name;

Supported Protection Methods:

pty_DoubleIns()

The UDF returns the tokenized value for a column containing Double format data.

Signature:

pty_DoubleIns(data double, dataElement string)

Parameters:

• data: Specifies the column name of the data to tokenize in the table.
• dataElement: Specifies the name of the data element to protect the double value.

Result:

• The UDF returns the double value.

Example:

select pty_DoubleIns(cast(1.2 as double), 'no_enc');

Warning: Ensure that you use the data element with the No Encryption method only. Using any other data element would return an error mentioning that the operation is not supported for that data type. If you want to tokenize the Double column, then load the Double column into a String column and use the pty_StringIns() UDF to tokenize the column. For more information about pty_StringIns() UDF, refer pty_StringIns().

Supported Protection Methods:

pty_DoubleSel()

The UDF returns the detokenized value for a column containing Double format data.

Signature:

pty_DoubleSel(data double, dataElement string)

Parameters:

• data: Specifies the column name of the data to detokenize in the table.
• dataElement: Specifies the name of the data element to unprotect the double value.

Result:

• The UDF Returns the detokenized double value.

Example:

select pty_DoubleSel(tokenized_value, 'no_enc');

Warning: Ensure that you use the data element with the No Encryption method only. Using any other data element would return an error mentioning that the operation is not supported for that data type.

Supported Protection Methods:

pty_SmallIntEnc()

The UDF returns the encrypted value for a column containing SmallInt format data.

Signature:

pty_SmallIntEnc(data SmallInt, dataElement string)

Parameters:

• data: Specifies the column name of the data to encrypt in the table.
• dataElement: Specifies the name of the data element to encrypt the SmallInt value.

Result:

• The UDF returns a string value.

Example:

select pty_SmallIntEnc(column_name,'enc_3des') from table_name;

Supported Protection Methods:

pty_SmallIntDec()

The UDF returns the decrypted value for a column containing SmallInt format data.

Signature:

pty_SmallIntDec(data string, dataElement string)

Parameters:

• data: Specifies the column name of the data, to decrypt, in the table.
• dataElement: Specifies the name of the data element to decrypt the SmallInt value.

Result:

• The UDF returns a SmallInt value.

Example:

select pty_SmallIntDec(column_name,'enc_3des') from table_name;

Supported Protection Methods:

pty_SmallIntIns()

The UDF returns the tokenized value for a column containing SmallInt format data.

Signature:

pty_SmallIntIns(data SmallInt, dataElement string)

Parameters:

• data: Specifies the column name of the data, to tokenize, in the table.
• dataElement: Specifies the name of the data element to protect the SmallInt value.

Result:

• The UDF returns the tokenized SmallInt value.

Example:

select pty_SmallIntIns(column_name,'integer_de') from table_name;

Supported Protection Methods:

pty_SmallIntSel()

The UDF the detokenized value for a column containing SmallInt format data.

Signature:

pty_SmallIntSel(data SmallInt, dataElement string)

Parameters:

• data: Specifies the column name of the data, to detokenize, in the table.
• dataElement: Specifies the name of the data element to unprotect the SmallInt value.

Result:

• The UDF returns the detokenized SmallInt value.

Example:

select pty_SmallIntSel(column_name,'integer_de') from table_name;

Supported Protection Methods:

pty_BigIntEnc()

The UDF returns the encrypted value for a column containing BigInt format data.

Signature:

pty_BigIntEnc(data BigInt, dataElement string)

Parameters:

• data: Specifies the column name of the data, to encrypt, in the table.
• dataElement: Specifies the name of the data element to encrypt the BigInt value.

Result:

• The UDF returns a string value.

Example:

select pty_BigIntEnc(column_name,'enc_3des') from table_name;

Supported Protection Methods:

pty_BigIntDec()

The UDF returns the decrypted value for a column containing BigInt format data.

Signature:

pty_BigIntDec(data string, dataElement string)

Parameters:

• data: Specifies the column name of the data, to decrypt, in the table.
• dataElement: Specifies the name of the data element to decrypt the BigInt value.

Result:

• The UDF returns a BigInt value.

Example:

select pty_BigIntDec(column_name,'enc_3des') from table_name;

Supported Protection Methods:

pty_BigIntIns()

The UDF returns the tokenized value for a column containing BigInt format data.

Signature:

pty_BigIntIns(data BigInt, dataElement string)

Parameters:

• data: Specifies the column name of the data, to tokenize, in the table.
• dataElement: Specifies the name of the data element to protect the BigInt value.

Result:

• The UDF returns the tokenized BigInt value.

Example:

select pty_BigIntIns(column_name,'BigInt_de') from table_name;

Supported Protection Methods:

pty_BigIntSel()

The UDF returns the detokenized value for a column containing BigInt format data.

Signature:

pty_BigIntSel(data BigInt, dataElement string)

Parameters:

• data: Specifies the column name of the data, to detokenize, in the table.
• dataElement: Specifies the name of the data element to unprotect the BigInt value.

Result:

• The UDF returns the detokenized BigInt value.

Example:

select pty_BigIntSel(column_name,'BigInt_de') from table_name;

Supported Protection Methods:

pty_DateEnc()

The UDF returns the encrypted value for a column containing Date format data.

Signature:

pty_DateEnc(data Date, dataElement string)

Parameters:

• data: Specifies the column name of the data, to encrypt, in the table.
• dataElement: Specifies the name of the data element to encypt the date value.

Result:

• The UDF returns a string value.

Example:

select pty_DateEnc(column_name,'enc_3des') from table_name;

Note: For the Date UDFs:

• Impala supports the date range from 0001-01-01 to 9999-12-31.
• Protegrity supports the date range from 0600-01-01 to 3337-11-27.

Supported Protection Methods:

pty_DateDec()

The UDF returns the decrypted value for a column containing Date format data.

Signature:

pty_DateDec(data string, dataElement string)

Parameters:

• data: Specifies the column name of the data, to decrypt, in the table.
• dataElement: Specifies the name of the data element to decypt the date value.

Result:

• The UDF returns the Date value.

Example:

select pty_DateDec(column_name,'enc_3des') from table_name;

Note: For the Date UDFs:

• Impala supports the date range from 0001-01-01 to 9999-12-31.
• Protegrity supports the date range from 0600-01-01 to 3337-11-27.

Supported Protection Methods:

pty_DateIns()

The UDF returns the tokenized value for a column containing Date format data.

Signature:

pty_DateIns(data Date, dataElement string)

Parameters:

• data: Specifies the column name of the data, to tokenize, in the table.
• dataElement: Specifies the name of the data element to protect the date value.

Result:

• The UDF returns the tokenized Date value

Example:

select pty_DateIns(column_name,'Date_de') from table_name;

Note: For the Date UDFs:

• Impala supports the date range from 0001-01-01 to 9999-12-31.
• Protegrity supports the date range from 0600-01-01 to 3337-11-27.

Supported Protection Methods:

pty_DateSel()

The UDF returns the detokenized value for a column containing Date format data.

Signature:

pty_DateSel(data Date, dataElement string)

Parameters:

• data: Specifies the column name of the data, to detokenize, in the table.
• dataElement: Specifies the name of the data element to unprotect the date value.

Result:

• The UDF returns the detokenized Date value.

Example:

select pty_DateSel(column_name,'Date_de') from table_name;

Note: For the Date UDFs:

• Impala supports the date range from 0001-01-01 to 9999-12-31.
• Protegrity supports the date range from 0600-01-01 to 3337-11-27.

Supported Protection Methods: