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.
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';
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
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.