There are several restrictions:
- You cannot use user defined functions written in C and Java in a single statement. There are some exceptions, such as if one appears at the top level of a statement and the other appears in a sub-select.
- You cannot create a view based on a query using a user defined function.
- You cannot put more than one user defined function written in Java in the same jar file.
For the most part, UDSFs and UDAFs can appear anywhere in a SQL statement that their corresponding native expressions can appear. For UDSFs, this means that they can appear anywhere a scalar expression can appear and for UDAFs, this means that they can appear anywhere a native aggregate can appear. However, there are some restrictions in the placement of UDSFs and UDAFs in a SQL statement. UDSFs or UDAFs cannot appear in the following contexts:
- Any expression of a CREATE TABLE statement, including:
- the default clause
- the check constraint expressions
- the range or list expression of a partitioning clause
- Any expression or predicate of a CREATE INDEX statement
- Any expression in the values clause of an INSERT INTO ... VALUES statement
- Any expression in the MERGE statement
- Any expression in the UPDATE statement
- Any expression in the DELETE statement
- Any expression that is part of a CREATE VIEW statement
UDFs or UDAs appearing in these contexts will result in an error message. For example:
merge into test3 t3 using test2 t2 on t3.key = t2.key when matched then update set t3.d1 = average(t2.d1) when not matched then insert (t3.key, t3.d1, t3.d2) values (t2.key, t2.d1, t2.d2); ERROR: SQL-Analytics function average is not supported in UPDATE/MERGE/DELETE statements