Apache Hive : HPL/SQL Reference
Apache Hive : HPL/SQL Reference
Hive Hybrid Procedural SQL On Hadoop (HPL/SQL) is a tool that implements procedural SQL for Hive. It is available in Hive 2.0.0 (HIVE-11055).
HPL/SQL is an open source tool (Apache License 2.0) that implements procedural SQL language for Apache Hive, SparkSQL, Impala as well as any other SQL-on-Hadoop implementation, any NoSQL and any RDBMS.
HPL/SQL is a hybrid and heterogeneous language that understands syntaxes and semantics of almost any existing procedural SQL dialect, and you can use with any database, for example, running existing Oracle PL/SQL code on Apache Hive and Microsoft SQL Server, or running Transact-SQL on Oracle, Cloudera Impala or Amazon Redshift.
HPL/SQL language is compatible to a large extent with Oracle PL/SQL, ANSI/ISO SQL/PSM (IBM DB2, MySQL, Teradata i.e), PostgreSQL PL/pgSQL (Netezza), Transact-SQL (Microsoft SQL Server and Sybase) that allows you leveraging existing SQL/DWH skills and familiar approach to implement data warehouse solutions on Hadoop. It also facilitates migration of existing business logic to Hadoop.
HPL/SQL is an efficient way to implement ETL processes in Hadoop.
Initially it was provided as a stand alone command line tool. As a part of (HIVE-24230) HPL/SQL has been re-architected to an integrated part of HiveServer (HS2). From a JDBC client, such as Beeline, you connect to HiveServer. The interpreter executes the abstract syntax tree (AST) from the parser. Hive metastore securely stores the function and procedure code permanently. The procedure is loaded and cached on demand to the interpreter’s memory when needed. You can close the session or restart HiveServer without losing the definitions.
Getting Started
User Guide
- User-Defined Functions and Stored Procedures
- On-the-Fly SQL Conversion
- UDF to Run HPL/SQL Scripts from Hive Queries
- Error Handling
- Native and Managed Temporary Tables
Language Elements
- Data Types
- Declaration
- Assignment
- DATE Literal
- TIMESTAMP Literal
- INTERVAL Expressions
- CASE Expressions
- %TYPE Attribute
- %ROWTYPE Attribute
- %ISOPEN, %FOUND and %NOTFOUND Cursor Attributes
- Exceptions
Operators
Attributes and Built-in Variables
| Name | Description |
|---|---|
| ACTIVITY_COUNT | Number of rows affected by last SQL statement |
| HOSTCODE | Return code of the last OS command |
| SQLCODE | Return code of the last SQL statement |
| SQLSTATE | Return status of the last SQL statement |
Statements
| Statement | Description |
|---|---|
| ALLOCATE CURSOR | Allocate cursor for procedure result set |
| ASSOCIATE RESULT SET LOCATOR | Define locators for procedure result sets |
| BREAK | Exit a loop |
| CALL | Execute a stored procedure |
| CLOSE | Close a cursor |
| CMP | Compare data in tables |
| COPY | Copy data between tables and files |
| COPY FROM FTP | Copy FTP files to Hadoop compatible file system |
| COPY FROM LOCAL | Copy local files to Hadoop compatible file system |
| CREATE DATABASE | Create a database |
| CREATE FUNCTION | Create a user-defined SQL function |
| CREATE LOCAL TEMPORARY TABLE | Create a session-level temporary table |
| CREATE PACKAGE | Create a program package |
| CREATE PROCEDURE | Create a user-defined SQL procedure |
| CREATE TABLE | Create a table |
| CREATE VOLATILE TABLE | Create a session-level temporary table |
| DECLARE | Declare a variable |
| DECLARE CONDITION | Declare a condition |
| DECLARE CURSOR | Declare a cursor |
| DECLARE HANDLER | Declare a condition handler |
| DECLARE TEMPORARY TABLE | Declare a temporary table |
| DESCRIBE | Describe a database object |
| DROP DATABASE | Drop a database |
| DROP TABLE | Drop a table |
| EXEC / EXECUTE / EXECUTE IMMEDIATE | Execute a dynamic SQL statement or procedure |
| EXIT WHEN | Exit a loop |
| FETCH | Fetch the next row from a cursor |
| FOR cursor | FOR statement (Cursor loop) |
| FOR range | FOR statement (Integer range) |
| GET DIAGNOSTICS | Get execution information |
| HOST | Execute an OS command or run an external process |
| IF | IF statement |
| INCLUDE | Include statements from another script |
| INSERT | INSERT statement |
| INSERT DIRECTORY | Write query results to a file |
| LEAVE | Exit a loop |
| OPEN | Open a cursor |
| LOOP | Unconditional loop |
| MAP OBJECT | Map object name to a connection profile |
| NULL | No operation (no-op) statement |
| Print a line | |
| RESIGNAL | Resignal the exception |
| RETURN | Return from a routine |
| SELECT | SELECT statement |
| SELECT INTO | Assign values from a query |
| SIGNAL | Raise a condition or exception |
| SET | Assign a value to a variable |
| SET Session Option | Set a session option |
| TRUNCATE | Truncate a table |
| UPDATE | UPDATE statement |
| USE | Change the default database |
| VALUES INTO | Assign a value to a variable |
| WHILE | While loop |
Functions
| Function | Description |
|---|---|
| CAST | Convert to data type |
| CHAR | Convert to string |
| COALESCE | Return first non-NULL value |
| CONCAT | Concatenate two or more strings |
| CURRENT_DATE | Get the current date (year, month and day) |
| CURRENT_TIMESTAMP | Get the current date and time |
| CURRENT_USER | Get the current user name |
| DATE | Convert to date |
| DECODE | Implement IF-THEN-ELSE logic |
| DBMS_OUTPUT.PUT_LINE | Print a line |
| FROM_UNIXTIME | Convert number of seconds since 1970-01-01 to timestamp |
| INSTR | Find index of substring in a string |
| LOWER | Convert a string to lower case |
| LEN | String length excluding trailing blanks |
| LENGTH | String length |
| MAX_PART_STRING | Get max partition value (string) |
| MIN_PART_STRING | Get min partition value (string) |
| MAX_PART_INT | Get max partition value (int) |
| MIN_PART_INT | Get min partition value (int) |
| MAX_PART_DATE | Get max partition value (date) |
| MIN_PART_DATE | Get min partition value (date) |
| NOW | Get the current date and time |
| NVL | Return first non-NULL value |
| NVL2 | If 1st operand is null return 3rd otherwise 2nd operand |
| PART_LOC | Get the location of a partition |
| PART_COUNT | Get the number of partitions |
| PART_COUNT_BY | Get the number of partitions (group by) |
| REPLACE | Replace a string |
| SUBSTR | Return a substring |
| SUBSTRING | Return a substring |
| SYSDATE | Get the current date and time |
| TIMESTAMP_ISO | Convert string to timestamp |
| TO_CHAR | Convert to string |
| TO_TIMESTAMP | Convert string to timestamp |
| TRIM | Remove leading and trailing characters |
| UNIX_TIMESTAMP | Get the current date and time in seconds since 1970-01-01 |
| UPPER | Convert a string to upper case |
| USER | Get the current user name |
Commands
| Command | Description |
|---|---|
| ! cmd; | Execute an OS command or run an external process |
File I/O Operations
Compatibility
Apache Hive : HPL/SQL - Get Started
Aug 12, 2026Apache Hive : HPL/SQL - Get Started
Quick guide how to start using HPL/SQL.
You can enable and use HPL/SQL from any host or third-party tool that can make a JDBC connection to HiveServer. Beeline is a popular client for use with HPL/SQL because other third-party tools do not show you some of the error messages about syntax mistakes.
Enabling HPL/SQL in the beeline connection string
After setting up a client to connect to HiveServer, you append mode=hplsql to the JDBC URL that connects the client to HiveServer.
Apache Hive : HPL/SQL - Key Features
Aug 12, 2026Apache Hive : HPL/SQL - Key Features
HPL/SQL key features:
- Flow of Control Statements (FOR, WHILE, IF, CASE, LOOP, LEAVE, RETURN)
- Functions, procedures, and packages
- Built-in functions (string manipulations, datetime functions, conversions)
- Exception handling and conditions
- Constants and variable, assignment (DECLARE count INT := 1)
- Processing results using a CURSOR
- On-the-fly SQL Conversion
- UDF to run HPL/SQL scripts from Hive queries
(SELECT hplsql('mycustomfunc(:1)', name) FROM users;)
Apache Hive : Why HPL/SQL
Aug 12, 2026Apache Hive : Why HPL/SQL
The role of Hadoop in Data Warehousing is huge. But to implement comprehensive ETL, reporting, analytics and data mining processes you not only need distributed processing engines such as MapReduce, Spark or Tez, you also need a way to express comprehensive business rules.
1. Business Logic Driver and Advanced Error Handling
HPL/SQL allows you to implement business logic using variables, expressions, flow-of-control statements and iterations. HPL/SQL supports error handling using exceptions and condition handlers. You can develop programs that manages and controls distributed processes but at the same time is not a bottleneck of the system.