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.

  1. 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;              
  2. 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;                    
  3. 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