Trino User Defined Functions and Procedures

• 1: General UDFs
• 2: VarChar UDFs
• 3: BigInt UDFs
• 4: SmallInt UDFs
• 5: Integer UDFs
• 6: Date UDFs
• 7: DateTime UDFs
• 8: VarChar Encryption UDFs
• 9: Unicode UDFs
• 10: Decimal UDFs
• 11: Double UDFs
• 12: VarBinary Encryption UDFs

1 - General UDFs

This section includes list of general UDFs that can be used to retrieve the Trino Protector version and the current user.

ptyWhoAmI()

This function returns the name of the user.

Signature:

ptyWhoAmI()

Parameters:
None

Returns:
This UDF returns the name of the user logged in to the database as VARCHAR.

Example:

SELECT ptyWhoAmI();

ptyGetVersion()

This UDF returns the JpepLite version used in Trino UDFs.

Signature:

ptyGetVersion()

Parameters:
None

Returns:
This UDF returns the JpepLite version used in Trino UDFs.

Example:

select ptyGetVersion();

ptyGetVersionExtended()

The UDF returns the extended version information.

Signature:

pty_getversionextended();

Parameters:

• None

Returns:
The UDF returns a string in the following format:

JpepLite: <1>; CORE: <2>;

where,

• 1. Is the JpepLite version
• 2. Is the Core library version

Example:

select pty_getversionextended();

2 - VarChar UDFs

This section provides a list of Varchar UDFs for the protect, unprotect, and reprotect operations.

Consider a Trino session where you impersonate a user using the –user parameter as shown in the following example.

./TrinoCLI --server localhost:8080 --catalog hive --schema default --user=<sample_user>

If you execute any UDF after impersonating a user, then the query execution happens for the impersonated user <sample_user>. This is a limitation of Trino.

ptyProtectStr()

This UDF protects the varchar values.

Signature:

ptyProtectStr(varchar input, varchar dataElement)

Parameters:

Returns:
This UDF returns the protected varchar value.

Example:

select ptyProtectStr('ProtegrityProt','Varchar_DE');

Supported Protection Methods:

ptyUnprotectStr()

This UDF unprotects the existing protected varchar value.

Signature:

ptyUnprotectStr(varchar input, varchar dataElement)

Parameters:

Returns:
This UDF returns the unprotected varchar value.

Example:

select ptyUnProtectStr(PtyProtectStr('ProtegrityProt','Varchar_DE'),'Varchar_DE');

Supported Protection Methods:

ptyReprotect() - Str

This UDF reprotects the varchar protected data, which was earlier protected using the ptyProtectStr UDF, with a different data element.

Signature:

ptyReprotect(varchar input, varchar oldDataElement, varchar newDataElement)

Parameters:

Returns:
This UDF returns the protected varchar value.

Example:

select ptyReprotect(PtyProtectStr('ProtegrityProt','Varchar_DE'),'Varchar_DE','new_Varchar_DE');

Supported Protection Methods:

3 - BigInt UDFs

This section provides a list of the BigInt UDFs for the protect, unprotect, and reprotect operations.

ptyProtectBigInt()

This UDF protects the BigInt value.

Signature:

ptyProtectBigInt(bigint input, varchar dataElement)

Parameters:

Returns:
This UDF returns the protected BigInt value.

Example:

select PtyProtectBigInt(1234567, 'BigInt_DE');

Supported Protection Methods:

ptyUnprotectBigInt()

This UDF unprotects the protected BigInt value.

Signature:

ptyUnProtectBigInt(bigint input, varchar dataElement)

Parameters:

Returns:
This UDF returns the unprotected BigInt value.

Example:

select PtyUnProtectBigInt(PtyProtectBigInt(1234567, 'BigInt_DE'), 'BigInt_DE');

Supported Protection Methods:

ptyReprotect() - BigInt

This UDF reprotects the BigInt format protected data with a different data element.

If you are using numeric data with the ptyReprotect() UDF for protection, then ensure to cast the data to BigInt before using the UDF.

Signature:

ptyReprotect(bigint input, varchar oldDataElement, varchar newDataElement)

Parameters:

Returns:
This UDF returns the protected BigInt value.

Example:

select ptyReprotect(PtyProtectBigInt(123456, 'BigInt_DE'),'BigInt_DE','new_BigInt_DE');

Supported Protection Methods:

4 - SmallInt UDFs

This section provides a list of the SmallInt UDFs for the protect, unprotect, and reprotect operations.

ptyProtectSmallInt()

This UDF protects the SmallInt value.

Signature:

ptyProtectSmallInt(smallint input, varchar dataElement)

Parameters:

Returns:
This UDF returns the protected SmallInt value.

Example:

select ptyProtectSmallInt(cast(12 as smallint), 'SmallInt_DE');

Supported Protection Methods:

ptyUnprotectSmallInt()

This UDF unprotects the protected SmallInt value.

Signature:

ptyUnprotectSmallInt(smallint input, varchar dataElement)

Parameters:

Returns:
This UDF returns the unprotected SmallInt value.

Example:

select PtyUnprotectSmallInt(PtyProtectSmallInt(cast(12 as smallint), 'SmallInt_DE'), 'SmallInt_DE');

Supported Protection Methods:

ptyReprotect() - SmallInt

This UDF reprotects the SmallInt format protected data, which was earlier protected using the ptyProtectSmallInt UDF, with a different data element.

Signature:

ptyReprotect (SmallInt input, varchar oldDataElement, varchar newDataElement)

Parameters:

Returns:
This UDF returns the protected SmallInt value.

Example:

select ptyReprotect(PtyProtectSmallInt(cast(12 as smallint), 'SmallInt_DE'),'SmallInt_DE','new_SmallInt_DE');

Supported Protection Methods:

5 - Integer UDFs

This section provides a list of the integer UDFs for the protect, unprotect, and reprotect operations.

ptyProtectInt()

This UDF protects the Int value.

Signature:

ptyProtectInt(Int input, varchar dataElement)

Parameters:

Returns:
This UDF returns the protected Int value.

Example:

select ptyProtectInt(1234567, 'Int_DE');

Supported Protection Methods:

ptyUnprotectInt()

This UDF unprotects the protected Int value.

Signature:

ptyUnprotectInt(int input, varchar dataElement)

Parameters:

Returns:
This UDF returns the unprotected Int value.

Example:

select ptyUnprotectInt(ptyProtectInt(1234567, 'Int_DE'), 'Int_DE');

Supported Protection Methods:

ptyReprotect() - Int

This UDF reprotects the Int format protected data, which was earlier protected using the ptyProtectInt UDF, with a different data element.

Signature:

ptyReprotect(int input, varchar oldDataElement, varchar newDataElement)

Parameters:

Returns:
This UDF returns the protected Int value.

Example:

select ptyReprotect(ptyProtectInt(1234567, 'Token_Integer'), 'Token_Integer','new_Token_Integer');

Supported Protection Methods:

6 - Date UDFs

This section provides a list of the Date UDFs for the protect, unprotect, and reprotect operations.

Caution: There are inconsistencies observed when Trino is used to fetch and store date values from HDFS, where data was stored using Hive. It is recommended to verify if the correct date and datetime values are retrieved when the data is fetched from or stored in HDFS without using the Trino UDFs. If the data consistency is maintained, only then proceed to the Trino Date or DateTime UDFs.

ptyProtectDate()

This UDF protects the Date value.

Signature:

ptyProtectDate(date input, varchar dataElement)

Parameters:

Returns:
This UDF returns the protected Date value.

Example:

select PtyProtectDate(cast('2018-10-10' as date), 'Date_DE');

Supported Protection Methods:

ptyUnprotectDate()

This UDF unprotects the protected Date value.

Signature:

ptyUnprotectDate(date input, varchar dataElement)

Parameters:

Returns:
This UDF returns the unprotected Date value.

Example:

select ptyUnprotectDate(PtyProtectDate(cast('2018-10-10' as date), 'Date_DE'), 'Date_DE');

Supported Protection Methods:

ptyReprotect() - Date

This UDF reprotects the Date format protected data, which was earlier protected using the ptyProtectDate UDF, with a different data element.

Signature:

ptyReprotect(date input, varchar oldDataElement, varchar newDataElement)

Parameters:

Warning: Ensure to use the data element with the No Encryption method only. Using any other data element might cause data corruption.

Returns:
This UDF returns the protected Date value.

Example:

select PtyReprotect(cast('2018-10-10' as date), 'Date_DE', 'new_Date_DE');

Supported Protection Methods:

7 - DateTime UDFs

This section provides a list of DateTime UDFs for the protect, unprotect, and reprotect operations.

Caution: There are inconsistencies observed when Trino is used to fetch and store date values from HDFS, where data was stored using Hive. It is recommended to verify if the correct date and datetime values are retrieved when the data is fetched from or stored in HDFS without using the Trino UDFs. If the data consistency is maintained, only then proceed to the Trino Date or DateTime UDFs.

ptyProtectDateTime()

This UDF protects the TIMESTAMP value. The DateTime UDFs in Trino support an input precision of 12 digits or picoseconds.

Signature:

ptyProtectDateTime(timestamp(p) input, varchar dataElement)

Parameters:

Returns:
This UDF returns the protected Timestamp(p) value.

Example:

select ptyProtectDateTime(cast('2018-10-10 20:35:17.123' as TIMESTAMP(3)), 'DateTime_DE');

Supported Protection Methods:

ptyUnprotectDateTime()

This UDF unprotects the protected Timestamp value.

Signature:

ptyUnprotectDateTime(timestamp(p) input, varchar dataElement)

Parameters:

Returns:
This UDF returns the unprotected Timestamp(p) value.

Example:

select ptyUnprotectDateTime(ptyProtectDateTime(cast('2018-10-10 03:04:05.123' as TIMESTAMP(3)), 'DateTime_DE'), 'DateTime_DE');

Supported Protection Methods:

ptyReprotect() - DateTime

8 - VarChar Encryption UDFs

This section provides a list of VarChar encryption UDFs for the protect, unprotect, and reprotect operations.

ptyStringEnc()

This UDF encrypts the Varchar value.

Signature:

ptyStringEnc(varchar input, varchar DataElement)

Parameters:

Warning:

• The string encryption UDFs are limited to accept 2 GB data size at maximum as input.
• Ensure that the field size for the protected binary data post the required encoding does not exceed the 2 GB input limit.
• The field size to store the input data is dependent on the encryption algorithm selected, such as, AES-128, AES-256, 3DES, and CUSP, and the encoding type selected, such as, No Encoding, Base64, and Hex.
• Ensure to set the input data size based on the required encryption algorithm and encoding so that the it does not exceed the 2 GB input limit.

Returns:
This UDF returns the encrypted Varbinary value.

Example:

select ptyStringEnc('ProtegrityProt','AES128_DE');

Exception:

• ptyTrinoProtectorException: INPUT-ERROR: Tokenization or Format Preserving Data Elements are not supported: An unsupported data element is provided.
• java.io.IOException: Too many bytes before newline: 2147483648: The length of the input needs to be less than the maximum limit of 2 GB.

Supported Protection Methods:

ptyStringDec()

This UDF decrypts the Varbinary value.

Signature:

ptyStringDec(varbinary input, varchar DataElement)

Parameters:

Returns:
This UDF returns the decrypted VarChar value.

Example:

select ptyStringDec(ptyStringEnc('ProtegrityProt','AES128_DE'),'AES128_DE');

Exception:

• ptyTrinoProtectorException: INPUT-ERROR: First argument (Input Data to be unprotected) is not a valid Binary Datatype: The input data, which is not in binary format is provided.
• ptyHiveProtectorException: INPUT-ERROR: Tokenization or Format Preserving Data Elements are not supported: An unsupported data element is provided.

Supported Protection Methods:

ptyStringReEnc()

This UDF re-encrypts the Varbinary format encrypted data with a different data element.

Signature:

ptyStringReEnc(varbinary input, varchar oldDataElement, varchar newDataElement)

Parameters:

Returns:
This UDF returns the Varbinary format data, which is re-encrypted.

Example:

select ptyStringReEnc(ptyStringEnc('ProtegrityProt','AES128_DE'),'AES128_DE','new_AES128_DE');

Exception:

• ptyTrinoProtectorException: INPUT-ERROR: First argument (Input Data to be reprotected) is not a valid Binary Datatype: The input data, which is not in binary format is provided.
• java.io.IOException: Too many bytes before newline: 2147483648: The length of the input needs to be less than the maximum limit of 2 GB.
• com.protegrity.hive.udf.ptyTrinoProtectorException: 26, Unsupported algorithm or unsupported action for the specific data element: An unsupported data element is provided.

Supported Protection Methods:

9 - Unicode UDFs

This section provides a list of Unicode UDFs for the protect, unprotect, and reprotect operations. This UDF should be used only to tokenize the Unicode data in Trino, migrate the tokenized data from Trino to a Teradata database, and detokenize the data using the Protegrity Database Protector. Ensure to use this UDF with a Unicode tokenization data element only.

ptyProtectUnicode()

This UDF protects the Varchar (Unicode) values.

Signature:

ptyProtectUnicode(varchar input, varchar dataElement)

Parameters:

Returns:
This UDF returns the protected Varchar value.

Example:

select ptyProtectUnicode('ProtegrityProt','Unicode_DE');

Supported Protection Methods:

ptyUnprotectUnicode()

This UDF unprotects the existing protected string value. This UDF should be used only to tokenize the Unicode data in Trino, migrate the tokenized data from Trino to a Teradata database, and detokenize the data using the Protegrity Database Protector. Ensure to use this UDF with a Unicode tokenization data element only.

Signature:

ptyUnprotectUnicode(varchar input, varchar dataElement)

Parameters:

Returns:
This UDF returns the unprotected Varchar(Unicode) value.

Example:

select ptyUnprotectUnicode(ptyProtectUnicode('ProtegrityProt','Unicode_DE'),'Unicode_DE');

Supported Protection Methods:

ptyReprotectUnicode()

This UDF reprotects the Varchar format protected data, which was earlier protected using the ptyProtectUnicode() UDF, with a different data element. This UDF should be used only to tokenize the Unicode data in Trino, migrate the tokenized data from Trino to a Teradata database, and detokenize the data using the Protegrity Database Protector. Ensure to use this UDF with a Unicode tokenization data element only.

Signature:

ptyReprotectUnicode(varchar input, varchar oldDataElement, varchar newDataElement)

Parameters:

Returns:
This UDF returns the protected VarChar value.

Example:

select ptyReprotectUnicode(ptyProtectUnicode('ProtegrityProt','Unicode_DE'),'Unicode_DE','new_Unicode_DE');

Supported Protection Methods:

10 - Decimal UDFs

This section provides a list of the Decimal UDFs for the protect, unprotect, and reprotect operations. Ensure to use the data element with the No Encryption method only. Using any other data element might cause data corruption.

ptyProtectDecimal()

This UDF protects the Decimal value.

Signature:

ptyProtectDecimal(decimal input, varchar dataElement)

Parameters:

Returns:
This UDF returns the protected Decimal value. If the input value is NULL and the data element is not a part of the policy, then the output value returned is NULL.

Example:

select ptyProtectDecimal(12332212222223.033, 'NoEnc');

Supported Protection Methods:

ptyUnprotectDecimal()

This UDF unprotects the protected Decimal value. Ensure to use the data element with the No Encryption method only. Using any other data element might cause data corruption.

Signature:

ptyUnprotectDecimal(decimal input, varchar dataElement)

Parameters:

Returns:
This UDF returns the unprotected Decimal value. If the input value is NULL and the data element is not a part of the policy, then the output value returned is NULL.

Example:

select ptyUnprotectDecimal(12332212222223.033, 'NoEnc');

Supported Protection Methods:

ptyReprotect() - Decimal

This UDF reprotects the Decimal format protected data, which was earlier protected using the ptyProtectDecimal() UDF, with a different data element.

Signature:

ptyReprotect(decimal input, varchar oldDataElement, varchar newDataElement)

Parameters:

Returns:
This UDF returns the protected Decimal value.

Example:

select ptyReprotect(12332212222223.033,'NoEnc','NoEnc');

Supported Protection Methods:

11 - Double UDFs

This section provides a list of Double UDFs for the protect, unprotect, and reprotect operations.

ptyProtectDouble()

This UDF protects the Double value.

Signature:

ptyProtectDouble(double input, varchar dataElement)

Parameters:

Note: It is an observed behavior with Trino that the UDF accepting double parameter also accepts decimal and integer parameter due to internal data type conversion.

Returns:
This UDF returns the protected Double value.

Example:

select ptyProtectDouble(12345, 'No_Enc');

Supported Protection Methods:

ptyUnprotectDouble()

This UDF unprotects the protected Double value. Ensure to use the data element with the No Encryption method only. Using any other data element might cause data corruption.

Signature:

ptyUnprotectDouble(double input, varchar dataElement)

Parameters:

Returns:
This UDF returns the original Double value.

Example:

select ptyUnprotectDouble(12345, 'No_Enc');

Supported Protection Methods:

ptyReprotect() - Double

This UDF reprotects the Double format protected data, which was earlier protected using the ptyProtectDouble() UDF, with a different data element. Ensure to use the data element with the No Encryption method only. Using any other data element might cause data corruption.

Signature:

ptyReprotect(double input, varchar oldDataElement, varchar newDataElement)

Parameters:

Returns:
This UDF returns the protected Double value.

Example:

select ptyReprotect(09457, 'No_Enc', 'new_No_Enc');

Supported Protection Methods:

12 - VarBinary Encryption UDFs

ptyBinaryEnc()

This UDF protects the VarBinary values. If the varbinary data size exceeds 32 MB, then set the configuration in the config.properties file and then restart the Trino server on all the nodes (co-ordinator and worker).

node-manager.http-client.max-content-length=64MB
exchange.http-client.max-content-length=64MB

Signature:

ptyBinaryEnc(VarBinary input, Varchar DataElement)

Parameters:

Returns:
This UDF returns the encrypted Varbinary value.

Example:

select ptyBinaryEnc(X'12A23D43', 'AES256');
select ptyBinaryEnc(binary_col1, 'AES256') from table1;

Supported Protection Methods:

ptyBinaryDec()

This UDF decrypts the Varbinary value.

Signature:

ptyBinaryDec(VarBinary input, Varchar DataElement)

Parameters:

Returns:
This UDF returns the decrypted VarBinary value.

Example:

select ptyBinaryDec(X'215b807cdfbc', 'AES256');
select ptyBinaryDec(binary_col1, 'AES256') from table1;

Supported Protection Methods:

ptyBinaryReEnc()

This UDF re-encrypts the Varbinary format encrypted data with a different data element.

Signature:

ptyBinaryReEnc(VarBinary input, Varchar oldDataElement, Varchar newDataElement)

Parameters:

Returns:
This UDF returns the Varbinary format data, which is re-encrypted.

Tokenization or Format Preserving Data Elements are not supported. Only encryption data elements are supported.

Example:

select ptyBinaryReEnc(X'12A23D43', 'Old_AES256', 'New_AES256');
select ptyBinaryReEnc(binary_col1, 'Old_AES256', 'New_AES256') from table1;

Supported Protection Methods: