UDF Sample Code and Operations

Complete UDF Sample Code

For details, see IoTDB UDF Sample Code.

Procedure

1. Register a UDF.

   Register a UDF with a full class name com.huawei.bigdata.iotdb.UDTFExample as follows:

   1. Package the project into a JAR. If you use Maven to manage your project, you can refer to step "Build a JAR file" in following section:
      • For details about clusters with Kerberos authentication enabled, see Registering a UDF.
      • For details about how to disable Kerberos authentication for a cluster, see Registering a UDF
   2. Log in to the node where IoTDBServer is located as user root, run su - omm to switch to user omm, and import the JAR package obtained in 1.a to the $BIGDATA_HOME/FusionInsight_IoTDB_*/install/FusionInsight-IoTDB-*/iotdb/ext/udf directory.
      

      During cluster deployment, ensure that a corresponding JAR package exists in the UDF JAR package path of each IoTDBserver node. You can modify the IoTDB configuration file udf_root_dir to specify the root path for the UDF to load the JAR package.

   3. Execute the following SQL statement to register the UDF:

      CREATE FUNCTION <UDF-NAME> AS '<UDF-CLASS-FULL-PATHNAME>'

      For example, to register a UDF named example, execute the following statement:

      CREATE FUNCTION example AS 'com.huawei.bigdata.iotdb.UDTFExample'

      Because IoTDB UDF instances are dynamically loaded through the reflection technology, you do not need to restart the server during the UDF registration process.

      

      • UDF function names are case insensitive.
      • Ensure that the function name given to the UDF is different from all built-in function names. A UDF with the same name as a built-in function cannot be registered.
      • It is recommended that you do not use classes that have the same class name but different function logic in different JAR packages. For example, in UDF(UDAF/UDTF): udf1, udf2, the JAR package of udf1 is udf1.jar and the JAR package of udf2 is udf2.jar. Assume that both JAR packages contain the com.huawei.bigdata.iotdb.UDTFExample class. If you use two UDFs in the same SQL statement at the same time, the system will randomly load either of them and may cause inconsistency in UDF execution behavior.

2. Query the UDF.
   • Basic SQL syntax supported:
     • SLIMIT / SOFFSET
     • LIMIT / OFFSET
     • NON ALIGN
     • Queries with value filters
     • Queries with time filters
   • Queries with aligned time series

     Currently, aligned time series are not supported in UDF queries. An error message is reported if you use UDF queries with aligned time series selected.

   • Queries with an asterisk (*) in SELECT clauses

     Assume that there are two time series (root.sg.d1.s1 and root.sg.d1.s2) in the system.

     • Execute SELECT example(*) from root.sg.d1.

       Then the result set will include the results of example (root.sg.d1.s1) and example (root.sg.d1.s2).

     • Execute SELECT example(s1, *) from root.sg.d1.

       Then the result set will include the results of example(root.sg.d1.s1, root.sg.d1.s1) and example(root.sg.d1.s1, root.sg.d1.s2).

     • Execute SELECT example(*, *) from root.sg.d1.

       Then the result set will include the results of example(root.sg.d1.s1, root.sg.d1.s1), example(root.sg.d1.s2, root.sg.d1.s1), example(root.sg.d1.s1, root.sg.d1.s2), and example(root.sg.d1.s2, root.sg.d1.s2).

   • Queries with key-value pair attributes in UDF parameters

     You can pass any number of key-value pair parameters to the UDF when constructing a UDF query. The key and value in the key-value pair need to be enclosed in single or double quotes. Note that key-value pair parameters can be passed in only after all time series have been passed in. Example:

     SELECT example(s1, 'key1'='value1', 'key2'='value2'), example(*, 'key3'='value3') FROM root.sg.d1;
     SELECT example(s1, s2, 'key1'='value1', 'key2'='value2') FROM root.sg.d1;

   • Showing all registered UDFs

     SHOW FUNCTIONS

3. Deregister the UDF.

   The following shows the SQL syntax of how to deregister a UDF:

   DROP FUNCTION <UDF-NAME>

   To deregister the UDF named example, execute the following statement:

   DROP FUNCTION example