User Tools

Site Tools


Sidebar

**HPL/SQL is included to Apache Hive since version 2.0** * [[home|Home]]\\ * [[why|Why HPL/SQL]]\\ * [[features|Key Features]]\\ * [[start|Get Started]]\\ * [[doc|HPL/SQL Reference]]\\ * [[download|Download]]\\ * [[new|What's New]]\\ * [[about|About]]

doc

This is an old revision of the document!


A PCRE internal error occured. This might be caused by a faulty plugin

===== HPL/SQL Reference ====== HPL/SQL reference and examples. ===== User Guide ===== * [[cli|Command Line]] * [[configuration|Configuration]] * [[connections|Connections]] * [[multiple-databases|Working with Multiple Databases]] * [[udf-sproc|User-Defined Functions and Stored Procedures]] * [[conversion|On-the-Fly SQL Conversion]] * [[udf|UDF to Run HPL/SQL Scripts from Hive Queries]] * [[error-handling|Error Handling]] * [[temporary-tables|Native and Managed Temporary Tables]] ===== Language Elements ===== * [[data-types|Data Types]] * [[declare|Declaration]] * [[assign|Assignment]] * [[date-literal|DATE Literal]] * [[timestamp-literal|TIMESTAMP Literal]] * [[interval|INTERVAL Expressions]] * [[case|CASE Expressions]] * [[type-attribute|%TYPE Attribute]] * [[rowtype-attribute|%ROWTYPE Attribute]] * [[cursor-attributes|%ISOPEN, %FOUND and %NOTFOUND Cursor Attributes]] * [[exceptions|Exceptions]] ===== Operators ===== * [[plus|Addition +]] * [[minus|Subtraction -]] * [[equal|Equality and Comparison =, <>, !=, <, >, <=, >=]] * [[twopipes|String Concatenation ||]] ===== Attributes and Built-in Variables ===== HPL/SQL attributes and built-in variables: | [[activity_count|ACTIVITY_COUNT]] | Number of rows affected by last SQL statement | | [[errorcode|ERRORCODE]] | Return code of the last SQL statement | | [[hostcode|HOSTCODE]] | Return code of the last OS command | | [[sqlcode|SQLCODE]] | Return code of the last SQL statement | | [[sqlstate|SQLSTATE]] | Return status of the last SQL statement | ===== Statements ===== HPL/SQL statements: | [[allocate-cursor|ALLOCATE CURSOR]] ||| Allocate cursor for procedure result set | | [[associate-locator|ASSOCIATE RESULT SET LOCATOR]] ||| Define locators for procedure result sets | | [[break|BREAK]] ||| Exit a loop | | [[call|CALL]] ||| Execute a stored procedure | | [[close|CLOSE]] ||| Close a cursor | | [[cmp|CMP]] ||| Compare data in tables | | [[copy|COPY]] ||| Copy data between tables and files | | [[copy-from-ftp|COPY FROM FTP]] ||| Copy FTP files to Hadoop compatible file system | | [[copy-from-local|COPY FROM LOCAL]] ||| Copy local files to Hadoop compatible file system | | [[create-database|CREATE DATABASE]] ||| Create a database | | [[create-function|CREATE FUNCTION]] ||| Create a user-defined SQL function | | [[create-local-temporary-table|CREATE LOCAL TEMPORARY TABLE]] ||| Create a session-level temporary table | | [[create-package|CREATE PACKAGE]] ||| Create a program package | | [[create-procedure|CREATE PROCEDURE]] ||| Create a user-defined SQL procedure | | [[create-table|CREATE TABLE]] ||| Create a table | | [[create-volatile-table|CREATE VOLATILE TABLE]] ||| Create a session-level temporary table | | [[declare|DECLARE]] ||| Declare a variable | | [[declare-condition|DECLARE CONDITION]] ||| Declare a condition | | [[declare-cursor|DECLARE CURSOR]] ||| Declare a cursor | | [[declare-handler|DECLARE HANDLER]] ||| Declare a condition handler | | [[declare-temporary-table|DECLARE TEMPORARY TABLE]] ||| Declare a temporary table | | [[describe|DESCRIBE]] ||| Describe a database object | | [[drop-database|DROP DATABASE]] ||| Drop a database | | [[drop-table|DROP TABLE]] ||| Drop a table | | [[execute|EXEC]] | [[execute|EXECUTE]] | [[execute|EXECUTE IMMEDIATE]] | Execute a dynamic SQL statement or procedure | | [[exit|EXIT WHEN]] ||| Exit a loop | | [[fetch|FETCH]] ||| Fetch the next row from a cursor | | [[for-cursor|FOR cursor]] ||| FOR statement (Cursor loop) | | [[for-range|FOR range]] ||| FOR statement (Integer range) | | [[get-diagnostics|GET DIAGNOSTICS]] ||| Get execution information | | [[host|HOST]] ||| Execute an OS command or run an external process | | [[if|IF]] ||| IF statement | | [[include|INCLUDE]] ||| Include statements from another script | | [[insert|INSERT]] ||| INSERT statement | | [[insert-directory|INSERT DIRECTORY]] ||| Write query results to a file | | [[leave|LEAVE]] ||| Exit a loop | | [[open|OPEN]] ||| Open a cursor | | [[loop|LOOP]] ||| Unconditional loop | | [[map-object|MAP OBJECT]] ||| Map object name to a connection profile | | [[null|NULL]] ||| No operation (no-op) statement | | [[print|PRINT]] ||| Print a line | | [[resignal|RESIGNAL]] ||| Resignal the exception | | [[return|RETURN]] ||| Return from a routine | | [[select|SELECT]] ||| SELECT statement | | [[select-into|SELECT INTO]] ||| Assign values from a query | | [[signal|SIGNAL]] ||| Raise a condition or exception | | [[assign|SET]] ||| Assign a value to a variable | | [[set-session|SET Session Option]] ||| Set a session option | | [[truncate|TRUNCATE]] ||| Truncate a table | | [[update|UPDATE]] ||| UPDATE statement | | [[use|USE]] ||| Change the default database | | [[values-into|VALUES INTO]] ||| Assign a value to a variable | | [[while|WHILE]] ||| While loop | ===== Functions ===== HPL/SQL built-in functions: | [[cast|CAST]] || Convert to data type | | [[char|CHAR]] || Convert to string | | [[coalesce|COALESCE]] || Return first non-NULL value | | [[concat|CONCAT]] || Concatenate two or more strings | | [[current-date|CURRENT_DATE]] || Get the current date (year, month and day) | | [[current-timestamp|CURRENT_TIMESTAMP]] || Get the current date and time | | [[current-user|CURRENT_USER]] || Get the current user name | | [[date|DATE]] || Convert to date | | [[decode|DECODE]] || Implement IF-THEN-ELSE logic | | [[dbms-output|DBMS_OUTPUT.PUT_LINE]] || Print a line | | [[from-unixtime|FROM_UNIXTIME]] || Convert number of seconds since 1970-01-01 to timestamp | | [[instr|INSTR]] || Find index of substring in a string | | [[lower|LOWER]] || Convert a string to lower case | | [[len|LEN]] || String length excluding trailing blanks | | [[length|LENGTH]] || String length | | [[max-part-string|MAX_PART_STRING]] || Get max partition value (string) | | [[min-part-string|MIN_PART_STRING]] || Get min partition value (string) | | [[max-part-int|MAX_PART_INT]] || Get max partition value (int) | | [[min-part-int|MIN_PART_INT]] || Get min partition value (int) | | [[max-part-date|MAX_PART_DATE]] || Get max partition value (date) | | [[min-part-date|MIN_PART_DATE]] || Get min partition value (date) | | [[now|NOW]] || Get the current date and time | | [[nvl|NVL]] || Return first non-NULL value | | [[nvl2|NVL2]] || If 1st operand is null return 3rd otherwise 2nd operand | | [[part-loc|PART_LOC]] || Get the location of a partition | | [[part-count|PART_COUNT]] || Get the number of partitions | | [[part-count-by|PART_COUNT_BY]] || Get the number of partitions (group by) | | [[replace|REPLACE]] || Replace a string | | [[substr|SUBSTR]] || Return a substring | | [[substring|SUBSTRING]] || Return a substring | | [[sysdate|SYSDATE]] || Get the current date and time | | [[timestamp-iso|TIMESTAMP_ISO]] || Convert string to timestamp | | [[to-char|TO_CHAR]] || Convert to string | | [[to-timestamp|TO_TIMESTAMP]] || Convert string to timestamp | | [[trim|TRIM]] || Remove leading and trailing characters | | [[unix-timestamp|UNIX_TIMESTAMP]] || Get the current date and time in seconds since 1970-01-01 | | [[upper|UPPER]] || Convert a string to upper case | | [[user|USER]] || Get the current user name | ===== BTEQ Statements ===== HPL/SQL BTEQ statements: | [[if|.IF]] | IF statement | | [[quit|.QUIT]] | Terminate execution and quit | ===== Commands ===== HPL/SQL built-in commands: | [[hive|hive]] || Invoke Hive CLI | | [[host|! cmd;]] || Execute an OS command or run an external process | ===== File I/O Operations ===== * [[utl-file|UTL_FILE Package]] ===== Troubleshooting ===== * [[troubleshooting|Troubleshooting]] ===== Compatibility ===== * [[plsql|Oracle PL/SQL]]