HPL/SQL is included to Apache Hive since version 2.0
HPL/SQL is included to Apache Hive since version 2.0
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 database.
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 PL/HQL 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 database 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 );
On-the-fly conversion is enabled by default and set using hplsql.conn.convert.default option.
Conversions for data types in CREATE TABLE statements:
Source | Hive SQL |
VARCHAR2 | STRING |
NUMBER | DECIMAL |
For more details, see Data Type Conversion.
Conversions for language elements and operators in SQL DDL and DML statements:
Source | Hive SQL | |
“identifier”, [identifier] | Identifier | `identifier` |
dbo, [dbo] | Schema name | Removed |
expr || expr2 || ... | String concatenation | CONCAT(expr, expr2, ...) |
Conversion of built-in SQL functions in executable SQL statements:
Source | Hive SQL |
CURRENT_DATE | TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP())) |
CURRENT DATE | |
CURRENT_TIMESTAMP | FROM_UNIXTIME(UNIX_TIMESTAMP()) |
CURRENT TIMESTAMP |
Conversions of SQL SELECT statements:
Source | Hive SQL | |
SELECT TOP n … FROM … | Row limit | SELECT … FROM … LIMIT n |
FROM TABLE (VALUES … ) clause | Row constructor | SELECT UNION ALL subquery |
For more information, see SELECT Statement.
Conversion of SQL statements:
Source | Hive SQL | |
DELETE FROM table ALL | TRUNCATE TABLE table | |
SET CURRENT SCHEMA = name | USE name |