User Tools

Site Tools


Sidebar

HPL/SQL is included to Apache Hive since version 2.0

udf-sproc

This is an old revision of the document!


User-Defined Functions and Stored Procedures

PL/HQL allows you to defined user-defined functions and stored procedures using CREATE FUNCTION and CREATE PROCEDURE statements, respectively.

Define Functions and Procedures in the Current Script

The easiest way to use PL/HQL functions and procedures is to define them in the current script before their actual use.

For example:

CREATE FUNCTION hello(text STRING)
 RETURNS STRING
BEGIN
 RETURN 'Hello, ' || text || '!';
END;
 
CREATE PROCEDURE set_message(IN name STRING, OUT result STRING)
BEGIN
 SET result = 'Hello, ' || name || '!';
END;
 
-- Invoke the function
PRINT hello('world');
 
-- Call the procedure and print the results
DECLARE str STRING;
CALL set_message('world', str);
PRINT str;

Once defined the function can be used in any PL/HQL and HQL expression as a built-in function. You can invoke a procedure using the CALL statement.

Permanent Functions and Stored Procedures

PL/HQL allows you to share functions and procedures between PL/HQL scripts so you do not need to put their content to every script.

Unlike databases that typically store functions and stored procedures in the database catalog, PL/HQL stores them in local files. There are several options how to you can include functions and procedures:

  • Use .plhqlrc file

You can put your functions and procedures to .plhqlrc file. The content of this file is automatically executed when you start plhql tool. For more information, see .plhqlrc file.

  • INCLUDE statement

Using INCLUDE statement you can load function and procedures definitions from any file. Note that you can also use INCLUDE statements in .plhqlrc file.

Using PL/HQL User-Defined Functions in Hive Queries

PL/HQL allows you to invoke user-defined functions written in PL/HQL language from Hive queries the same way as you use HQL built-in functions.

PL/HQL CLI tool automatically puts referenced PL/HQL functions and procedures to Distributed Cache, registers the Hive UDF and modifies the function call in the SQL statements. For more information, see Hive UDF to Run PL/HQL Scripts.

Version: PL/HQL 0.3.1

See also: