How to run a stored procedure from Cloudera Data Explorer (Hue) in Cloudera Data WarehouseHow to run a stored procedure from Cloudera Data Explorer (Hue) in Cloudera Data Hub

HPL/SQL allows you to implement business logic using variables, expressions, flow-of-control statements, and iterations. HPL/SQL makes SQL-on-Hadoop more dynamic. You can leverage your existing procedural SQL skills, and use functions and statements to make your typical ETL development more productive. In Cloudera Data Warehouse, Data Explorer provides a smart interface to run stored procedures.

To run stored procedures from Data Explorer, create a Hive Virtual Warehouse in Cloudera Data Warehouse and enable the hplsql option in the hue-safety-valve field.

The following example creates a procedure and returns records by passing a cursor:

print 'Hello world';/
CREATE PROCEDURE greet(name STRING)
BEGIN
  PRINT 'Hello ' || name;
END;/
CREATE PROCEDURE even(cur OUT SYS_REFCURSOR)
BEGIN
  OPEN cur FOR
SELECT n FROM NUMBERS
WHERE MOD(n, 2) == 0;
END;/
CREATE PROCEDURE set_message(IN name STRING, OUT result STRING)
BEGIN
 SET result = 'Hello, ' || name || '!';
END;
-- Call the procedure and print the results
DECLARE str STRING;
CALL set_message('world', str);
PRINT str;