Calling the UDF in a query

After registration of a UDF, you do not need to restart Hive before using the UDF in a query. In this example, you call the UDF you created in a SELECT statement, and Hive returns the data type of a column you specify.

  • For the example query in this task, you need to create a table in Hive and insert some data.
  • As a user, you need to have permission to call a UDF, which a Ranger policy can provide.
This task assumes you have the following example table in Hive:
+------------------+---------------+---------------+
|  students.name   | students.age  | students.gpa  |
+------------------+---------------+---------------+
| fred flintstone  | 35            | 1.28          |
| barney rubble    | 32            | 2.32          |
+------------------+---------------+---------------+
  1. Use the database in which you registered the UDF.
    For example:
    USE default;
  2. Query Hive depending on how you configured the cluster for Hive to find the JAR.
    • Direct JAR reference or Hive aux library directory
      For example:
      SELECT students.name, udftypeof(students.name) AS type FROM students WHERE age=35;
    • Reloadable aux JAR
      For example:
      RELOAD;
      SELECT students.name, udftypeof(students.name) AS type FROM students WHERE age=35;
    You get the data type of the name column in the students table.
    +------------------+------------------------------------------------------------+
    |  students.name   |                        type                                |
    +------------------+------------------------------------------------------------+
    | fred flintstone  | Type: org.apache.hadoop.hive.serde2.io.HiveVarcharWritable |
    +------------------+------------------------------------------------------------+