Search code examples
sqlfunctionhivetemporary

Create Temporary Function in Hive

i have a query like this

CREATE TEMPORARY MACRO AAA (input VARCHAR(16))
input||
SUBSTR(
LPAD(
(CASE WHEN SUBSTR(LPAD(input,16,0),15,1) = 1 THEN 2
WHEN SUBSTR(LPAD(input,16,0),15,1) = 2 THEN 4
WHEN SUBSTR(LPAD(input,16,0),15,1) = 3 THEN 6
WHEN SUBSTR(LPAD(input,16,0),15,1) = 4 THEN 8
WHEN SUBSTR(LPAD(input,16,0),15,1) = 5 THEN 10
WHEN SUBSTR(LPAD(input,16,0),15,1) = 6 THEN 1
WHEN SUBSTR(LPAD(input,16,0),15,1) = 7 THEN 3
WHEN SUBSTR(LPAD(input,16,0),15,1) = 8 THEN 5
WHEN SUBSTR(LPAD(input,16,0),15,1) = 9 THEN 7
ELSE 0
END) +
(CASE WHEN SUBSTR(LPAD(input,16,0),16,1) = 1 THEN 1
WHEN SUBSTR(LPAD(input,16,0),16,1) = 2 THEN 2
WHEN SUBSTR(LPAD(input,16,0),16,1) = 3 THEN 3
WHEN SUBSTR(LPAD(input,16,0),16,1) = 4 THEN 4
WHEN SUBSTR(LPAD(input,16,0),16,1) = 5 THEN 5
WHEN SUBSTR(LPAD(input,16,0),16,1) = 6 THEN 6
WHEN SUBSTR(LPAD(input,16,0),16,1) = 7 THEN 7
WHEN SUBSTR(LPAD(input,16,0),16,1) = 8 THEN 8
WHEN SUBSTR(LPAD(input,16,0),16,1) = 9 THEN 9
ELSE 0
END)
,3,0), 3, 1);

but I want to create a temporary function for hive. What needs to be changed and what format is it in to create a temporary function?

Solution

  • You will have to write your own UDF.

    Ref : https://cwiki.apache.org/confluence/display/Hive/HivePlugins

    Once you have jar file with custom logic you can create function like below:

    Tested in Hive version apache-hive-4.0.0-alpha-1

    CREATE TEMPORARY FUNCTION aa_fun as 'AAA' using jar 'hdfs://localhost:9000/user/hive/aaa_func.jar';

select aa_fun('fwfrgwre12a3') fun_res, AAA('fwfrgwre12a3') macro_res;

+----------------+----------------+
|    fun_res     |   macro_res    |
+----------------+----------------+
| fwfrgwre12a33  | fwfrgwre12a33  |
+----------------+----------------+