CREATE FUNCTION

Description

This statement is used to create a custom function.

Syntax

CREATE [ GLOBAL ] 
    [{AGGREGATE | TABLES | ALIAS }] FUNCTION <function_name>
    (<arg_type> [, ...])
    [ RETURNS <ret_type> ]
    [ INTERMEDIATE <inter_type> ]
    [ WITH PARAMETER(<param> [,...]) AS <origin_function> ]
    [ PROPERTIES ("<key>" = "<value>" [, ...]) ]

Required Parameters

1. <function_name>

If function_name includes a database name, such as db1.my_func, the custom function will be created in the corresponding database. Otherwise, the function will be created in the database of the current session. The name and parameters of the new function must not be identical to an existing function in the current namespace; otherwise, the creation will fail.

2. <arg_type>

The input parameter type of the function. For variable-length parameters, use , ... to indicate them. If it is a variable-length type, the type of the variable-length parameters must be consistent with the type of the last non-variable-length parameter.

3. <ret_type>

The return parameter type of the function. This is a required parameter for creating a new function. If creating an alias for an existing function, this parameter is not necessary.

Optional Parameters

1. GLOBAL

If specified, the created function is effective globally.

2. AGGREGATE

If specified, the created function is an aggregate function.

3. TABLES

If specified, the created function is a table function.

4. ALIAS

If specified, the created function is an alias function.

If none of the above parameters representing the function type is selected, it indicates that the created function is a scalar function.

5. <inter_type>

Used to indicate the data type during the intermediate stage of an aggregate function.

6. <param>

Used to indicate the parameters of an alias function, with at least one parameter required.

7. <origin_function>

Used to indicate the original function corresponding to the alias function.

8. <properties>

• file: Indicates the JAR package containing the user-defined function (UDF). In a multi-machine environment, it can also be downloaded via HTTP. This parameter is mandatory.
• symbol: Indicates the class name containing the UDF class. This parameter is mandatory.
• type: Indicates the UDF call type. The default is Native. Use JAVA_UDF when using a Java UDF.
• always_nullable: Indicates whether the UDF result may contain NULL values. This is an optional parameter with a default value of true.

Access Control Requirements

To execute this command, the user must have ADMIN_PRIV privileges.

Example

1. Create a custom UDF function. For more details, refer to JAVA-UDF.

   CREATE FUNCTION java_udf_add_one(int) RETURNS int PROPERTIES (
       "file"="file:///path/to/java-udf-demo-jar-with-dependencies.jar",
       "symbol"="org.apache.doris.udf.AddOne",
       "always_nullable"="true",
       "type"="JAVA_UDF"
   );

2. Create a custom UDAF function.

   CREATE AGGREGATE FUNCTION simple_sum(INT) RETURNS INT PROPERTIES (
       "file"="file:///pathTo/java-udaf.jar",
       "symbol"="org.apache.doris.udf.demo.SimpleDemo",
       "always_nullable"="true",
       "type"="JAVA_UDF"
   );

3. Create a custom UDTF function.

   CREATE TABLES FUNCTION java_udtf(string, string) RETURNS array<string> PROPERTIES (
       "file"="file:///pathTo/java-udaf.jar",
       "symbol"="org.apache.doris.udf.demo.UDTFStringTest",
       "always_nullable"="true",
       "type"="JAVA_UDF"
   );

4. Create a custom alias function. For more information, refer to Alias Function.

   CREATE ALIAS FUNCTION id_masking(INT) WITH PARAMETER(id) AS CONCAT(LEFT(id, 3), '****', RIGHT(id, 4));

5. Create a global custom alias function.

   CREATE GLOBAL ALIAS FUNCTION id_masking(INT) WITH PARAMETER(id) AS CONCAT(LEFT(id, 3), '****', RIGHT(id, 4));