Apache Hive : HPL/SQL - Configurations

You can set all options dynamically using the SET statement in a HPL/SQL script: SET option=value;

hplsql.onerror

The hplsql.onerror option defines how HPL/SQL handles errors.

Values:

• exception - HPL/SQL raises an exception when an error occurs. This is the default.
• seterror - HPL/SQL sets the error code to SQLCODE or HOSTCODE variables and continues execution
• stop - HPL/SQL stops executing the script and exits.

For more information, see Error Handling.

Apache Hive : HPL/SQL - Error Handling

HPL/SQL allows you to use exceptions, condition handlers and error code to handle errors. The hplsql.onerror configuration option defines how HPL/SQL handles errors. It accepts the following values:

• Exception (default)

In this case when an error occurs, HPL/SQL raises an exception. If there is an exception or condition handler for this error, it is executed.

• Seterror

When Seterror is specified, HPL/SQL sets the error code to SQLCODE or HOSTCODE variables and continues execution.

Apache Hive : HPL/SQL - Hive UDF to Run HPL/SQL Scripts from Beeline in non-hplsql mode

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

For example, let’s call the following function from a Hive query:

CREATE FUNCTION hello(text STRING)
 RETURNS STRING
BEGIN
 RETURN 'Hello, ' || text || '!';
END;

Running HPL/SQL scripts from Beeline in non-hplsql mode

Now let’s use hello function written in HPL/SQL language in Hive query:

Apache Hive : HPL/SQL - Native and Managed Temporary Tables

HPL/SQL provides you with two options to work with temporary tables: native and managed.

Use the hplsql.temp.tables option to define how to handle temporary tables, the default value is native.

Native Temporary Tables

When native temporary tables are used HPL/SQL relies on the underlying HiveServer to manage temporary tables.

HPL/SQL converts DECLARE TEMPORARY TABLE statement to CREATE TEMPORARY TABLE in Hive.

Apache Hive : HPL/SQL - On-the-Fly SQL Conversion in Hive

HPL/SQL supports many procedural dialects that allows you to reuse your existing code without changes. But not all statements are executed by HPL/SQL, some of them must be executed by the HiveServer.

Consider the following example:

IF code = 'A' THEN
  CREATE TABLE dept 
  (
    deptno NUMBER(2,0),
    dname  NUMBER(14),
    loc    VARCHAR2(13),
    CONSTRAINT pk_dept PRIMARY KEY (deptno)
  );
END IF;

In this example, IF statement is executed by HPL/SQL itself. It checks the value of code variable and if it is equal to ‘A’, HPL/SQL executes the CREATE TABLE statement.

Apache Hive : HPL/SQL - User-Defined Functions and Stored Procedures

HPL/SQL 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 HPL/SQL 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/procedure, the metadata of the function/procedure will be stored in the HMS permanently and they can be used in any HPL/SQL and HQL expression as a built-in function. You can invoke a procedure using the CALL statement.