Call user defined functions created natively on the backend data warehouse using Hyper-Q

Use Case:

User defined functions on Teradata can be created in SQL language or C language. Functions written in languages like C cannot be ported directly to the modern cloud data-warehouse. These need to be rewritten in a natively supported language (e.g., T-SQL for Azure Synapse Analytics or Python on other data-warehouse). Once the rewritten version of the function is available, the following process needs to be followed to make it accessible via Hyper-Q:

1. Create a dummy function via Hyper-Q:

Make sure of the following five things while creating dummy function:

• Function should be created in syslib schema (Please create syslib schema if it does not exist already)

• Name of the function should be same as native function

• The number and type of parameters should match the native function

• Return type should match the native function

• The dummy function must be created on all Hyper-Q instances serving the same Synapse instance (e.g., all production VMs behind the load balancer of the production environment); define the function using REPLACE FUNCTION (instead of CREATE FUNCTION) and explicitly connect to all Hyper-Q VMs sequentially to create the dummy function

2. Create the native function definition provided by partner or system integrator:

Make sure to drop and create the function natively so that it overrides the dummy definition from step above

• Function should be created in syslib schema

• Drop function created as part of step 1 (Redshift supports create or replace syntax so this step can be skipped but Synapse does not)

• Create the function with actual definition

3. Restart Hyper-Q on all VMs serving this environment

4. Run sample stmt referencing that function via Hyper-Q and validate that return value is as expected from actual definition.

Example:

Please see steps below for sample function (test1) which takes a Varchar(10) field as input and returns varchar. (See highlighted part in step1 and step2 on things to match between actual and dummy definition)

1. Create a dummy function via Hyper-Q (Teradata SQL syntax):

create FUNCTION syslib.test1(s1 varchar(10))
RETURNS varchar(3)
CONTAINS SQL
COLLATION INVOKER INLINE TYPE 1
RETURN 'N';

2. Create function natively to override dummy function (for e.g. Synapse T-SQL syntax ):

Drop function syslib.test1;

create FUNCTION syslib.test1 (@s1 varchar(10))
RETURNS varchar (3) AS
BEGIN
RETURN 'Y'
END;

3. Query via Hyper-Q:

select test1 ('abc');

Above query returns Y which confirms it is picking the right function.

Note: It is possible to create the function in any schema (other than syslib) but then while calling that function user should fully qualify the function ("schema_name.function_name")in the SQL.