Creating User-Defined Hive Functions

When the built-in functions of Hive cannot meet requirements, you can compile user-defined functions (UDFs) and use them in queries.

According to implementation methods, UDFs are classified as follows:

Common UDFs: used to perform operations on a single data row and export a single data row.
User-defined aggregating functions (UDAFs): used to input multiple data rows and export a single data row.
User-defined table-generating functions (UDTFs): used to perform operations on a single data row and export multiple data rows.

According to use methods, UDFs are classified as follows:

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.

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.

The following uses AddDoublesUDF as an example to describe how to compile and use UDFs.

Function

AddDoublesUDF is used to add two or more floating point numbers. In this example, you can learn how to write and use UDFs.

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/.

Sample Code

The following is a UDF code example:

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; 
  } 
}

Creating User-Defined Hive Functions

Prepare the user who will execute the 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.
  - Username: Enter a username, for example, test.
  - User Type: Select Human-Machine.
  - Password and Confirm Password: Enter a password and enter it again.
  - User Group: Click Add, select hive and hadoop groups, and click OK.
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 1.e.
    - If the Disable Ranger button is grayed out (Ranger authentication has been disabled for Hive), go to 1.f.
  - The cluster is in normal mode.
    - If the Enable Ranger button is grayed out (Ranger authentication has been enabled for Hive), go to 1.e.
    - If the Disable Ranger button is grayed out (Ranger authentication has been disabled for Hive), go to 2.
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:
    - Policy Name: Set the policy name, for example, test_hive.
    - database
      - 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: Switch to udf and enter a specific function name or set it to *. You do not need to set this parameter for temporary functions.
    - In the Allow Conditions area, select the new user in the Select User column and add the following permissions to 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 that contains the user created by 1.c.
  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.
Package the preceding program into AddDoublesUDF.jar and upload it to the client installation node, for example, the opt directory. Then upload the package 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 file.
1. Go to the client installation directory and configure the environment variables:
  cd Client installation directory
  
  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
  
  hdfs dfs -chmod 777 /user/hive_examples_jars
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 enabled (security mode), run the following command:
  beeline -n Hive service user
Run the following commands on the Hive Server to define the 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 alias of the function, which is used in SELECT queries. 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 indicates the function alias that is used for SELECT query.
  - TEMPORARY indicates that the function is used only in the current session of the Hive Server.
Run the following command on the Hive Server to use the function:

SELECT addDoubles(1,2,3);

If an [Error 10011] error is displayed when you log in to the client again, run the reload function; command and then use this function.
Run the following command on the Hive Server to delete the function:

DROP FUNCTION addDoubles;