HPL/SQL is included to Apache Hive since version 2.0
Sidebar
doc
Table of Contents
HPL/SQL Reference
HPL/SQL reference and examples.
User Guide
Language Elements
Operators
Attributes and Built-in Variables
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 |
Statements
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 | ||
Functions
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 | |
BTEQ Statements
Commands
HPL/SQL built-in commands:
File I/O Operations
Troubleshooting
Compatibility
Page Tools
