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:
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. Run sample stmt referencing that function via Hyper-Q and validate that return value is as expected from actual definition.
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))
COLLATION INVOKER INLINE TYPE 1
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
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.
Please sign in to leave a comment.