HPL/SQL - Procedural SQL on Hadoop, NoSQL and RDBMS

This is an old revision of the document!

---

====== 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 database. Consider the following example: <code language=sql> 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; </code> 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 <code language=sql> CREATE TABLE dept ( deptno DECIMAL(2,0), dname DECIMAL(14), loc STRING ); </code> On-the-fly conversion is enabled by default and set using [[configuration#hplsqlconnconvertdefault|hplsql.conn.convert.default]] option. ===== Data Types ===== Conversions for data types in CREATE TABLE statements: | **Source** | **Hive SQL** | | VARCHAR2 | STRING | | NUMBER | DECIMAL | For more details, see [[/data-types#data_type_conversion|Data Type Conversion]]. ===== Language Elements and Operators ===== Conversions for language elements and operators in SQL DDL and DML statements: | **Source** || **Hive SQL** | | "identifier", [identifier] | Identifier | `identifier` | | dbo, [dbo] | Schema name | Removed | | [[twopipes|expr || expr2 || ...]] | String concatenation | [[concat|CONCAT(expr, expr2, ...)]] | ===== Built-In SQL Functions ===== 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 |:::| ===== SQL SELECT Statement ===== Conversions of SQL SELECT statements: | **Source** || **Hive SQL** | | SELECT TOP n ... FROM ... | Row limit | SELECT ... FROM ... LIMIT n | | SELECT without FROM || FROM [[configuration#hplsqldualtable|hplsql.dual.table]] added for Hive 0.13 and earlier | | FROM TABLE (VALUES ... ) clause | Row constructor | SELECT UNION ALL subquery | For more information, see [[select|SELECT Statement]]. ===== SQL Statements ===== Conversion of SQL statements: | **Source** || **Hive SQL** | | [[set-session#current-schema|SET CURRENT SCHEMA = name]] || [[use|USE name]] |