HPL/SQL Support

Published on 01 February 2022 in Version 4.11 / Development / Query - 2 minutes read - Last modified on 02 February 2022

HPL/SQL is an Apache open source procedural extension for SQL for Hive users. It has its own grammar. It is included with Apache Hive from version 2.0.

HPL/SQL is a hybrid and heterogeneous language that understands syntaxes and semantics of almost any existing procedural SQL dialect, and you can use it with any database. For example, you can run existing Oracle PL/SQL code on Apache Hive and Microsoft SQL Server, or Transact-SQL code on Oracle, Cloudera Impala, or Amazon Redshift. For more information about the HPL/SQL language, see the HPL/SQL Reference.

How to enable HPL/SQL dialect in Hue:

In the desktop/conf/hue.ini config file section, add the HPL/SQL interpreter:


Note: HPL/SQL uses the beeswax config like Hive uses.

Key features of HPL/SQL:

  • 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

HPL/SQL limitations:

  • Some of the Hive specific CREATE TABLE parameters are missing
  • No colon syntax to parametrize SQL strings
  • No quoted string literals
  • No GOTO and Label
  • EXECUTE does not have output parameters
  • Some complex data types, such as Arrays and Records are not supported
  • No object-oriented extension

HPL/SQL examples:

The following example creates a function that takes the input of your name and returns “Hello <name>”:

  PRINT 'Hello ' || name;


The following example prints the sum of numbers between 1 and 10:

declare sum int = 0;
for i in 1..10 loop
  sum := sum + i;
end loop;
select sum;


Attention: In hplsql mode, you must terminate the commands using the forward slash character (/). The semicolon (;) is used throughout procedure declarations and can no longer be relied upon to terminate a query in the editor.

You can try this feature in the latest Hue version.

For feedback, questions, or suggestions, feel free to comment here or on the Forum and quick start SQL querying!


Ayush from the Hue Team

comments powered by Disqus

More recent stories

03 May 2023
Discover the power of Apache Ozone using the Hue File Browser
Read More
23 January 2023
Hue 4.11 and its new dialects and features are out!
Read More