Creating a function

You need to know the syntax of HPL/SQL, which closely resembles Oracle’s PL/SQL. An example of creating a function and calling it in a Hive SELECT statement demonstrates the HPL/SQL basics.

Create a table and populate it with some numbers.

create table numbers (n int);

for i in 1..10 loop
  insert into numbers values(i);
end loop;

Create a function called fizzbuzz to return numbers.

create function fizzbuzz(n int) returns string
begin
  if mod(n, 15) == 0 then
     return 'FIZZBUZZ';
  elseif mod(n, 5) == 0 then
     return 'BUZZ';
  elseif mod(n, 3) == 0 then
     return 'FIZZ';
  else
     return n;
  end if;
end;

Call the function from a Hive select statement.

select fizzbuzz(n) from numbers;

Output looks something like this:

...
1
2
FIZZ
4
BUZZ
1
2
FIZZ
4
BUZZ
FIZZ
7
8
FIZZ
BUZZ