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');
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!
Onwards!
Ayush from the Hue Team