Sidebar

HPL/SQL is included to Apache Hive since version 2.0

doc

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 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
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

HPL/SQL BTEQ statements:

.IF IF statement
.QUIT Terminate execution and quit

Commands

HPL/SQL built-in commands:

hive Invoke Hive CLI
! cmd; Execute an OS command or run an external process

File I/O Operations

Troubleshooting

Compatibility