Creating User-Defined Hive Functions

Scenario

When the built-in functions of Hive cannot meet requirements, you can compile user-defined functions (UDFs) and use them in queries. This section describes how to develop and run UDFs.

Introduction to Hive UDF

UDFs are classified as follows based on their usage:

• Temporary functions: used only in the current session and must be recreated after a session restarts.
• Permanent functions: used in multiple sessions. You do not need to create them every time a session restarts.

Notes and Constraints

• You need to properly control the memory and thread usage of variables in UDFs. Improper control may cause memory overflow or high CPU usage.
• If Ranger authentication is enabled for the cluster, you need to disable Ranger authentication before using Python UDFs. For details about how to disable Ranger authentication, see Disabling Ranger Authentication.
• MRS 3.3.0-LTS or later:
  • Deleting a UDF

    If multiple HiveServers are available and a custom UDF is deleted from one of them, attempting to execute the deleted UDF on that HiveServer will result in an error message indicating that the UDF does not exist. However, other HiveServers can still access the deleted UDF due to caching. To remove it consistently, connect to each HiveServer and run the reload function command to refresh the UDF cache or restart the HiveServer.

  • Modifying a UDF

    When modifying the logic of a custom UDF, you are advised to keep the JAR package path on HDFS unchanged. This allows the modified UDF to be accessed simply by re-establishing the client connection. If the HDFS path of the JAR package changes, connect to each HiveServer and run the reload function command to refresh the UDF cache or restart the HiveServer to apply the changes.

Step 1: Develop a UDF

This section uses AddDoublesUDF as an example to describe how to compile and use UDFs.

AddDoublesUDF is used to add two or more floating point numbers. Note the following:

• A common UDF must be inherited from org.apache.hadoop.hive.ql.exec.UDF.
• A common UDF must implement at least one evaluate(). The evaluate function supports overloading.
• To develop a UDF, add the hive-exec-*.jar dependency package to the project. You can obtain the package from the Hive service installation directory, for example, ${BIGDATA_HOME}/components/FusionInsight_HD_*/Hive/disaster/plugin/lib/.
• In MRS 3.6.0-LTS and later versions, JDK 8 is used by default for MapReduce jobs running on Hive SQL. Therefore, you are advised to use JDK 8 to compile the Hive UDF JAR package. If JDK 17 or JDK 21 is used for compilation, add <maven.compiler.target>1.8</maven.compiler.target> to properties in the pom.xml file of the UDF sample, and the new syntax of JDK 17 or JDK 21 cannot be included.

The following is the sample code of AddDoublesUDF. xxx indicates the name of the organization that develops the program.

package com.xxx.bigdata.hive.example.udf;
import org.apache.hadoop.hive.ql.exec.UDF;

public class AddDoublesUDF extends UDF { 
 public Double evaluate(Double... a) { 
    Double total = 0.0; 
    // Processing logic
    for (int i = 0; i < a.length; i++) 
      if (a[i] != null) 
        total += a[i]; 
    return total; 
  } 
} 

Package the preceding sample program into AddDoublesUDF.jar. For details, see Hive Developer Guide.

Step 2: Create a User for Executing the UDF Function

1. Log in to Manager as user admin, choose Cluster > Cluster Properties, and check and record the authentication method of the cluster.
2. Choose Cluster > Services > Hive, click More in the upper right corner of the page, and check whether Ranger authentication is enabled for Hive.
3. Choose System > Permission > User, click Create, set the following parameters, and click OK.

   Table 2 Creating a user

   Parameter	Example Value	Parameters
   Username	test	Enter a custom username.
   User Type	Human-Machine	The options are Human-Machine and Machine-Machine.
   Password	xxx	Enter the password of the user.
   Confirm Password	xxx	Enter the password of the user again.
   User Group	Click Add, select the hive and hadoop user groups, and click OK.	In the User Group area, click Add to add one or more user groups to the list.

4. Assign permissions to the new user based on the cluster authentication method and whether Ranger authentication is enabled.
   • The cluster is in security mode.
     • If the Enable Ranger button is grayed out (Ranger authentication has been enabled for Hive), go to 5.
     • If the Disable Ranger button is grayed out (Ranger authentication has been disabled for Hive), go to 6.
   • The cluster is in normal mode.
     • If the Enable Ranger button is grayed out (Ranger authentication has been enabled for Hive), go to 5.
     • If the Disable Ranger button is grayed out (Ranger authentication has not enabled for Hive), no further action is required.

5. Hive uses Ranger for authentication. Log in to the Ranger management page as the Ranger administrator (rangeradmin for security mode and admin for normal mode) to add Hive permission control policies for the user.
   1. Choose Cluster > Services > Ranger and click the hyperlink on the right of Ranger web UI.
   2. For a cluster in security mode, click the username in the upper right corner of the page, click Log Out. Log in to the Ranger management page as user rangeradmin.
   3. On the home page, click the component plug-in name in the HADOOP SQL area, for example, Hive.
   4. In the Access tab, click Add New Policy, set the following parameters, and click Add:

      Table 3 Adding a Hive access control policy

      Parameter	Example Value	Parameters
      Policy Name	test_hive	A custom policy name, which must be unique in the service.
      database	default	Name of the Hive database to which the policy applies. Permanent function: Enter the name of the database to which the function is to be added, for example, default. Temporary function: Switch database to global and enter a specific function name or set it to *.
      table	-	Name of the Hive table to which the policy applies. Switch the value to udf and enter a specific function name or set it to *. You do not need to set this parameter for temporary functions.
      Allow Conditions	-	Policy allowed condition. You can configure permissions and exceptions allowed by the policy. Select the new user in the Select User column and add the following permissions in Permissions: Permanent function: Grant permission based on service requirements. For example, you can add the create, select, and drop permissions. Temporary function: Add the Temporary UDF Admin permission.

6. Hive uses the Manager role for authentication. Create a user with the Hive administrator permission to execute permanent and temporary functions.
   1. On the homepage of Manager, choose System > Permission > Role, click Create Role, set the following parameters, and click OK.
      • Role Name: Enter a role name, for example, test_role.
      • Configure Resource Permission: Click the name of the desired cluster, click Hive, and select Admin.
   2. Click User and click Modify in the row where the user created in 3 is located.
   3. On the Modify User page, click Add on the right of Role, add the newly created role with the Hive administrator rights, and click OK.

Step 3: Run the UDF.

1. Upload the AddDoublesUDF.jar package to the client installation node, for example, the opt directory.
2. Log in to the node where the client is installed as the client installation user and upload AddDoublesUDF.jar to a specified directory in HDFS, for example, /user/hive_examples_jars. Both the user who creates the function and the user who uses the function must have the read permission on the package.
   1. Go to the client installation directory and configure the environment variables:

      Go to the client installation directory.

      cd Client installation directory

      Configure environment variables.

      source bigdata_env

   2. Authenticate the user.
      • For a cluster with Kerberos authentication enabled (security mode):

        kinit Service user

      • For a cluster with Kerberos authentication disabled (normal mode):

        export HADOOP_USER_NAME=Service user

   3. Upload the UDF JAR package to the HDFS directory.

      hdfs dfs -put /opt /user/hive_examples_jars

      Modify permissions.

      hdfs dfs -chmod 777 /user/hive_examples_jars

3. Log in to the Hive client.
   • For a cluster with Kerberos authentication enabled (security mode), run the following command:

     beeline

     If the user is assigned the Hive administrator role, run the following command to switch to the admin role in each beeline maintenance operation session and then perform subsequent operations:

     set role admin;

   • For a cluster with Kerberos authentication disabled (normal mode), run the following command:

     beeline -n Hive service user

4. Run the following commands on the Hive Server to create a custom function.
   • Create a permanent function.

     CREATE FUNCTION addDoubles AS 'com.xxx.bigdata.hive.example.udf.AddDoublesUDF' using jar 'hdfs://hacluster/user/hive_examples_jars/AddDoublesUDF.jar';

     addDoubles is the name of the function, which is used for SELECT operations. xxx is typically the name of the organization that develops the program.

   • Create a temporary function.

     CREATE TEMPORARY FUNCTION addDoubles AS 'com.xxx.bigdata.hive.example.udf.AddDoublesUDF' using jar 'hdfs://hacluster/user/hive_examples_jars/AddDoublesUDF.jar';

     • addDoubles is the name of the function, which is used for SELECT operations.
     • TEMPORARY indicates that the function is used only in the current session of the Hive Server.

5. Run the following command on the Hive Server to use the function:

   SELECT addDoubles(1,2,3);

   If the error message "[Error 10011]" is displayed when you reconnect to the client and use the function, run the reload function; command and then use the function again. For example, the error message is as follows:

   Error: Error while compiling statement: FAILED: SemanticException [Error 10011]: Line 1:7 ...

6. Run the following command on the Hive Server to delete the function:

   DROP FUNCTION addDoubles;

Helpful Links

• If the error message "ERROR 10011" or "invalid function" is displayed when you use Spark to create a UDF on the Hive client or use UDFs between multiple HiveServers, rectify the fault by referring to "Invalid function" Is Displayed When a UDF Is Used.
• If an error indicating insufficient user permission is reported when you create a permanent Hive UDF, rectify the fault by referring to An Error Is Reported When a UDF Is Created on the Beeline Client.
• To perform operations on local files with Hive UDFs, see How to Perform Operations on Local Files with Hive User-Defined Functions.
• To delete all permanent functions from the HiveServer, see How Do I Delete All Permanent Functions from HiveServer?.