CREATE FUNCTION

Syntax

CREATE FUNCTION qualified_function_name (

parameter_name parameter_type

[, ...]

)

RETURNS return_type

[ COMMENT function_description ]

[ LANGUAGE [ JAVA ] ]

[ SPECIFIC specificName ]

[ DETERMINISTIC | NOT DETERMINISTIC ]

[ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]

[ SYMBOL class_name ]

[ URI hdfs_path_to_jar ]

Description

It is used to create a function based on a given definition.

• Each function is uniquely identified by a qualified name and parameter type list. The value format of qualified_function_name must be catalog.schema.function_name. You can plan and manage the function namespace (its format is catalog.schema). It is irrelevant to the concepts of catalog and schema in HetuEngine. The value of parameter_type must be a data type supported by HetuEngine.
• The value of return_type must be a data type supported by HetuEngine and must match the actual type returned by the function. Forcible type conversion is not executed.
• You can specify a group of features to decorate a function and specify its behavior. Each feature can be specified only once. For details, see Table 1.

  Table 1 Feature description

  Feature	Default Value	Description
  Language clause	-	Language used to define a function. Currently, Java is supported. Java function: A JAR file for implementing a function needs to be provided, and the JAR file needs to be placed in HDFS that can be read by HetuEngine.
  Deterministic characteristic	NOT DETERMINISTIC	Whether a function is deterministic. DETERMINISTIC: A function is considered deterministic if it always returns the same result set when called with the same input set. NOT DETERMINISTIC: A function is considered nondeterministic if it does not return the same result set when called with the same input set.
  Null-call clause	CALLED ON NULL INPUT	Function behavior. RETURNS NULL ON NULL INPUT: When NULL is used as a function parameter, NULL is returned. CALLED ON NULL INPUT: When NULL is used as a function parameter, the function is called.
  Symbol class_name	-	Used by a Java function to specify a fully qualified class name for function implementation.
  Uri hdfs_path_to_jar	-	Used by a Java function to specify a JAR file path for function implementation.

Remarks

• Permissions are controlled only based on user groups. For details, see Table 2.

  Table 2 Permission control

  Operation	Permission Control
  CREATE	No permission control
  DROP	Only the owner has the permission to perform the operation.
  SELECT	No permission control
  SHOW	No permission control

Example

• Create a Java function example.default.add_two. (You need to build and deploy the UDF first.)

  CREATE FUNCTION example.default.add_two (
   num integer
  )
  RETURNS integer
  LANGUAGE JAVA
  DETERMINISTIC
  SYMBOL "com.example.functions.AddTwo"
  URI "hdfs://hacluster/udfs/function-1.0.jar";
  
  -- Execute the function.
  select hetu.default.add_two(2);