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:

[notebook]
[[interpreters]]
[[[hplsql]]]
name=Hplsql
interface=hiveserver2

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>”:

CREATE PROCEDURE greet(name STRING)
BEGIN
  PRINT 'Hello ' || name;
END;
greet('World');

Example1

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;

Example2

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!

Onwards!

Ayush from the Hue Team


comments powered by Disqus

More recent stories

15 November 2021
Create SQL tables from excel files
Read More
21 September 2021
Access your data in ABFS without any credential keys!
Read More