Hive and HPL/SQL Integration

HPL/SQL includes a Hive UDF function that allows you to execute HPL/SQL scripts (user-defined functions written in HPL/SQL language) in Hive queries.

HPL/SQL uses the hplsql_locals.sql file to parse a prepared procedure that can be used in the Hive query. If you want to add and use multiple functions, you should add each function to the hplsql_locals.sql file.

For example, to call the hello function from a Hive query, you can add a hello function to the hplsql_locals.sql file:
CREATE FUNCTION hello(text STRING)
 RETURNS STRING
BEGIN
 RETURN 'Hello, ' || text || '!';
END;

There are two possible ways to run the HPL/SQL hello function:

Running HPL/SQL from Hive CLI/Hive Beeline

The hplsql_locals.sql file must be located in the directory where the Hive CLI is started or in the /opt/mapr/hive/hive-<version>/bin directory if you are using Beeline. After adding the hello function to the hplsql_locals.sql file, register the HPL/SQL UDF in Hive as follows:

CREATE TEMPORARY FUNCTION hplsql AS 'org.apache.hive.hplsql.Udf';

To use the hello function written in HPL/SQL language in Hive, use a query such as the following:
SELECT hplsql('hello(:1)', name) FROM users;

Running HPL/SQL from the HPL/SQL CLI

When you run HPL/SQL scripts using the HPL/SQL CLI, you can use user-defined functions the same way you use built-in functions:
hplsql -e "SELECT hello(name) FROM users;"

The HPL/SQL CLI automatically connects to HiveServer2 using the configuration from the hplsql-site.xml file, registers the Hive UDF, and modifies the function call in the SQL statements. But you must ensure that the hplsql_locals.sql file containing the user-defined functions is located in the /opt/mapr/hive/hive-<version>/bin directory, where HiveServer2 can parse it.

For more information, see User-Defined Functions and Stored Procedures.