HPL/SQL is included to Apache Hive since version 2.0
HPL/SQL is included to Apache Hive since version 2.0
HPL/SQL reference and examples.
HPL/SQL attributes and built-in variables:
ACTIVITY_COUNT | Number of rows affected by last SQL statement |
ERRORCODE | Return code of the last SQL statement |
HOSTCODE | Return code of the last OS command |
SQLCODE | Return code of the last SQL statement |
SQLSTATE | Return status of the last SQL statement |
HPL/SQL statements:
ALLOCATE CURSOR | Allocate cursor for procedure result set | ||
ASSOCIATE RESULT SET LOCATOR | Define locators for procedure result sets | ||
BREAK | Exit a loop | ||
CALL | Execute a stored procedure | ||
CLOSE | Close a cursor | ||
CMP | Compare data in tables | ||
COPY | Copy data between tables and files | ||
COPY FROM FTP | Copy FTP files to Hadoop compatible file system | ||
COPY FROM LOCAL | Copy local files to Hadoop compatible file system | ||
CREATE DATABASE | Create a database | ||
CREATE FUNCTION | Create a user-defined SQL function | ||
CREATE LOCAL TEMPORARY TABLE | Create a session-level temporary table | ||
CREATE PACKAGE | Create a program package | ||
CREATE PROCEDURE | Create a user-defined SQL procedure | ||
CREATE TABLE | Create a table | ||
CREATE VOLATILE TABLE | Create a session-level temporary table | ||
DECLARE | Declare a variable | ||
DECLARE CONDITION | Declare a condition | ||
DECLARE CURSOR | Declare a cursor | ||
DECLARE HANDLER | Declare a condition handler | ||
DECLARE TEMPORARY TABLE | Declare a temporary table | ||
DESCRIBE | Describe a database object | ||
DROP DATABASE | Drop a database | ||
DROP TABLE | Drop a table | ||
EXEC | EXECUTE | EXECUTE IMMEDIATE | Execute a dynamic SQL statement or procedure |
EXIT WHEN | Exit a loop | ||
FETCH | Fetch the next row from a cursor | ||
FOR cursor | FOR statement (Cursor loop) | ||
FOR range | FOR statement (Integer range) | ||
GET DIAGNOSTICS | Get execution information | ||
HOST | Execute an OS command or run an external process | ||
IF | IF statement | ||
INCLUDE | Include statements from another script | ||
INSERT | INSERT statement | ||
INSERT DIRECTORY | Write query results to a file | ||
LEAVE | Exit a loop | ||
OPEN | Open a cursor | ||
LOOP | Unconditional loop | ||
MAP OBJECT | Map object name to a connection profile | ||
NULL | No operation (no-op) statement | ||
Print a line | |||
RESIGNAL | Resignal the exception | ||
RETURN | Return from a routine | ||
SELECT | SELECT statement | ||
SELECT INTO | Assign values from a query | ||
SIGNAL | Raise a condition or exception | ||
SUMMARY | Summary stats for a table or result set | ||
SET | Assign a value to a variable | ||
SET Session Option | Set a session option | ||
TRUNCATE | Truncate a table | ||
UPDATE | UPDATE statement | ||
USE | Change the default database | ||
VALUES INTO | Assign a value to a variable | ||
WHILE | While loop |
HPL/SQL built-in functions:
CAST | Convert to data type | |
CHAR | Convert to string | |
COALESCE | Return first non-NULL value | |
CONCAT | Concatenate two or more strings | |
CURRENT_DATE | Get the current date (year, month and day) | |
CURRENT_TIMESTAMP | Get the current date and time | |
CURRENT_USER | Get the current user name | |
DATE | Convert to date | |
DECODE | Implement IF-THEN-ELSE logic | |
DBMS_OUTPUT.PUT_LINE | Print a line | |
FROM_UNIXTIME | Convert number of seconds since 1970-01-01 to timestamp | |
INSTR | Find index of substring in a string | |
LOWER | Convert a string to lower case | |
LEN | String length excluding trailing blanks | |
LENGTH | String length | |
MAX_PART_STRING | Get max partition value (string) | |
MIN_PART_STRING | Get min partition value (string) | |
MAX_PART_INT | Get max partition value (int) | |
MIN_PART_INT | Get min partition value (int) | |
MAX_PART_DATE | Get max partition value (date) | |
MIN_PART_DATE | Get min partition value (date) | |
NOW | Get the current date and time | |
NVL | Return first non-NULL value | |
NVL2 | If 1st operand is null return 3rd otherwise 2nd operand | |
PART_LOC | Get the location of a partition | |
PART_COUNT | Get the number of partitions | |
PART_COUNT_BY | Get the number of partitions (group by) | |
REPLACE | Replace a string | |
SUBSTR | Return a substring | |
SUBSTRING | Return a substring | |
SYSDATE | Get the current date and time | |
TIMESTAMP_ISO | Convert string to timestamp | |
TO_CHAR | Convert to string | |
TO_TIMESTAMP | Convert string to timestamp | |
TRIM | Remove leading and trailing characters | |
UNIX_TIMESTAMP | Get the current date and time in seconds since 1970-01-01 | |
UPPER | Convert a string to upper case | |
USER | Get the current user name |
HPL/SQL built-in commands: