1. Analytical Application Infrastructure User Guide
  2. Data Management Framework
  3. User Defined Functions

User Defined Functions

This feature allows you to register Hive Permanent and Temporary User Defined Functions that can be used in Expression Builders in OFSAAI.

Hive supports a lot of built-in SQL-like functions in HiveQL. However, a few functions that are available in Oracle are not yet supported in Hive. A Java implementation for such functions is provided as custom Hive UDFs by OFSAAI.

  • TO_NUMBER(String input [, String format]) The TO_NUMBER function converts String input to a value of NUMBER datatype.
  • TO_DATE(String input, String format)
    • The TO_DATE function converts input to a value of DATE datatype in the specified format.
    • Native Hive to_date(String) function when format is not specified works as is, and expects the input to be specified in yyyy-MM-dd [HH:mm:ss] format.
  • TO_CHAR(Number/Date input [, String format])

    The TO_CHAR function converts a Date, Number, or String input to a String expression in a specified format.

  • NVL2(T Input1, T Input2, T Input3)

    NVL2 lets you determine the value returned by a query based on whether a specified expression is null or not null. If Input1 is not null, then NVL2 returns Input2. If expr1 is null, then NVL2 returns Input3.

    These functions are registered in OFSAAI and are available in the User Defined Functions Summary window for using in the metadata definitions. However, you should register the OFSAAI Hive UDF jar in the Hive server. The Hive UDF classes are present in the $OFSAA_HOME/utility/DMT/UDF/lib/ofsaa-hive-udf.jar folder. Copy the Jar to $HIVE_AUX_LIB path on the Hive server and then restart Hive services to use the functions in HiveQL.

    Note:

    User Defined Functions support only Java Date format.

The Roles mapped for User Defined Functions are as follows:

  • UDFACCESS
  • UDFREAD
  • UDFWRITE
  • UDFPHANTOM
  • UDFAUTH
  • UDFADV

For all the roles, functions, and descriptions, see Appendix A.

Figure 7-29 UDF Summary window


This image displays the UDF Summary window.

The User Defined Functions Summary window displays the available UDFs with details such as Function Name, Function Description, Origin, Type, and Category. You can add new UDFs, modify, view, and purge existing UDFs.