Minimum Required Role: Configurator (also provided by Cluster Administrator, Full Administrator)

The following sections describe how to create permanent and temporary functions using the command line.

To update existing UDFs, you must first update the Java class in the JAR file. Once the Java class is updated, create the new JAR file. Then you must drop the existing function in Beeline and re-create it with the CREATE FUNCTION statement. These steps are explained in detail below:

1. Update the Java class in the JAR file to update the UDF. For more information, see the Apache Hive wiki.

2. Drop the UDF that has been updated. For example, if you have updated a UDF named my_udf, log into Beeline and run the following command:

   DROP FUNCTION my_udf;
             

3. Delete the JAR file from which the my_udf function was created from both HDFS and the local file system. On the local filesystem, the JAR can be found at the location pointed to by either the hive.aux.jars.path or the hive.reloadable.aux.jars.path property.

4. Copy the updated JAR file to the local filesystem as described in Step 1 of Using Cloudera Manager to Create User-Defined Functions (UDFs) with HiveServer2.

     Important:

   If you specified the old JAR file location with the hive.reloadable.aux.jars.path property, make sure that you copy the updated JAR to the same directory. This enables you to use the Beeline reload command so HiveServer2 can read the new JAR file without needing the service to be restarted. Thus you can avoid a service disruption.

5. Set the hive.reloadable.aux.jars.path property with the location of the updated JAR file:

   1. In the Cloudera Manager Admin Console, go to the Hive service.
   2. Click the Configuration tab.
   3. Under Filters, click Hive (Service-Wide) scope.
   4. Click the Advanced category.

   5. In the panel on the right, locate the Hive Service Advanced Configuration Snippet (Safety Value) for hive-site.xml, click the plus sign (+) to the right of it, and enter the following information:

      • In the Name field, enter the hive.reloadable.aux.jars.path property.
      • In the Value field, enter the path where you copied the JAR file to in Step 3.
      • In the Description field, enter the property description. For example, Path to Hive UDF JAR files..
   6. Click Save Changes.

   7. Redeploy the Hive client configuration.

      1. In the Cloudera Manager Admin Console, go to the Hive service.
      2. From the Actions menu at the top right of the service page, select Deploy Client Configuration.
      3. Click Deploy Client Configuration.

      4. Restart the Hive service.

           Important: You need to restart the Hive service only when you first specify the JAR files location to the hive.reloadable.aux.jars.path property so the server can read the location. Afterwards, if you add or remove JAR files from this directory location, you can use the Beeline reload command to refresh the changes with the Hive service.

6. If Sentry is enabled on your cluster, otherwise ignore this step: Grant privileges on the JAR files to the roles that require access. Log in to Beeline as user hive and use the Hive SQL GRANT statement to do so. For example:

   GRANT ALL ON URI 'file:///usr/lib/hive/lib/<my.jar>' TO ROLE <example_role>;
     

7. Run the CREATE FUNCTION command in Beeline to create the UDF from the class in the JAR file:

   • If Sentry is enabled on your cluster:

     The USING JAR command is not supported. To load the jar, you must make sure the JAR file is at the location pointed to by the hive.reloadable.aux.jars.path, and then use the following CREATE FUNCTION statement:

     CREATE FUNCTION <your_function_name> AS '<fully_qualified_class_name>';
                     

     Where the <fully_qualified_class_name> is the full path to the Java class in your JAR file. For example, if your Java class is located at directory_1/directory_2/directory_3/udf_class.class in your JAR file, use:

     CREATE FUNCTION <your_function_name> AS 'directory_1.directory_2.directory_3.udf_class';
                     

   • Without Sentry enabled on your cluster:

     1. Copy the JAR file to HDFS and make sure the hive user can access this JAR file, and make note of the path (for example, hdfs:///user/hive/udf_jars/).

     2. Run the CREATE FUNCTION command as follows and point to the JAR file location in HDFS:

        CREATE FUNCTION <your_function_name> AS
                          '<fully_qualified_class_name>' USING JAR
                          'hdfs:///<path/to/jar/in/hdfs>';
                        

        Where the <fully_qualified_class_name> is the full path to the Java class in your JAR file.

HiveServer2 maintains a blacklist for built-in UDFs to secure itself against attacks in a multi user scenario where the hive user's credentials can be used to execute any Java code.

To check whether hive.server2.builtin.udf.blacklist contains any UDFs, run the following SET statement in Beeline:

SET hive.server2.builtin.udf.blacklist;
      

If any UDFs are set to be blacklisted, they are returned after running this command. For example, if character_length() and ascii() are blacklisted, the SET command returns the following information which shows these two built-in UDFs are blacklisted. The UDFs are shown in bold font in the following example:

+----------------------------------------------------+--+
|                        set                         |
+----------------------------------------------------+--+
| hive.server2.builtin.udf.blacklist=character_length(),ascii() |
+----------------------------------------------------+--+
      

To add built-in UDF names to the hive.server2.builtin.udf.blacklist property with Cloudera Manager:

1. In the Cloudera Manager Admin Console, go to the Hive service.
2. On the Hive service page, click the Configuration tab.
3. On the Configuration page, click HiveServer2 under Scope and click Advanced under Category.

4. Search for HiveServer2 Advanced Configuration Snippet (Safety Valve) for hive-site.xml and add the following information:

   • Name: hive.server2.builtin.udf.blacklist
   • Value: <builtin_udf_name1>,<builtin_udf_name2>...
   • Description: Blacklisted built-in UDFs.
5. Click Save Changes and restart the HiveServer2 service for the changes to take effect.

If you are not using Cloudera Manager to manage your cluster, set the hive.server2.builtin.udf.blacklist property in the hive-site.xml file.