Install Hyper-Q Custom UDFs

Aside from the many built in functions utilized by Teradata and supported by Hyper-Q, many customers rely on custom user defined functions (UDFs) which they create to perform specific tasks within their data processes. When possible, Datometry assists in converting or rewriting these UDFs so that the same functionality can be achieved on a cloud data warehouse. 

The deployment method for custom UDFs varies from that of the standard UDFs included in the Hyper-Q installation, which is described in Installing and Upgrading Hyper-Q Standard UDFs. Once a rewrite has been completed and shared by Datometry, perform the following steps to successfully deploy the UDF. 

In the following example, we use the rewritten function TRANSLATE_CHK for Azure Synapse. 

To install a custom Hyper-Q UDF:

1. Create an empty Teradata function using Hyper-Q that has the same input and output as the rewritten function definition.
   

   Rewritten Function

   CREATE FUNCTION __DTM_MDSTORE.[TRANSLATE_CHK]
   (
   @InputString NVARCHAR(MAX)
   ,@Conversion VARCHAR(MAX)
   )
   RETURNS int

    

   Empty Teradata Function

   CREATE function __DTM_MDSTORE.TRANSLATE_CHK (
   i varchar(64000),
   s varchar(64000)
   )
   RETURNS INT
   ...
   RETURN 1; 

2. On Synapse, drop the function definition and create the full Synapse definition provided by Datometry.

   drop function __DTM_MDSTORE.[TRANSLATE_CHK];
   
   CREATE FUNCTION __DTM_MDSTORE.[TRANSLATE_CHK]
   (
   @InputString NVARCHAR(MAX)
   ,@Conversion VARCHAR(MAX)
   )
   RETURNS int
   AS
   BEGIN
   ...

3. Execute the function from Hyper-Q to confirm it has been successfully created.