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 four 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

2. Create the native function definition provided by partner/SI:

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. 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.

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.