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.

HPL/SQL cannot execute CREATE TABLE, this statement must be sent to the HiveServer to create a physical persistent table accessible to other sessions.

But you can see that the syntax of CREATE TABLE does not conform to Hive, it uses NUMBER and VARCHAR2 data types as well as a constraint.

Here is where On-the-fly SQL conversion comes into play. Before sending the CREATE TABLE statement to Hive, HPL/SQL converts it to

CREATE TABLE dept 
(
  deptno DECIMAL(2,0),
  dname  DECIMAL(14),
  loc    STRING
);

Data Types

Conversions for data types in CREATE TABLE statements:

For more details, see Data Type Conversion.

Language Elements and Operators

Conversions for language elements and operators in SQL DDL and DML statements:

Built-In SQL Functions

Conversion of built-in SQL functions in executable SQL statements:

SQL SELECT Statement

Conversions of SQL SELECT statements:

For more information, see SELECT Statement.

SQL Statements

Conversion of SQL statements: