title: HPL/SQL Support author: Hue Team type: post date: 2022-02-01T00:00:00+00:00 url: /blog/2022-02-01-hplsql-support sf_thumbnail_type:
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.
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.
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');
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.
Onwards!
Ayush from the Hue Team