Apache Hive : HPL/SQL - ALLOCATE CURSOR Statement

ALLOCATE CURSOR statement allows you to declare a cursor and associate it with a result set returned from a stored procedure.

Syntax:

ALLOCATE cursor_name CURSOR FOR PROCEDURE procedure_name;  -- Teradata compatibility
|
ALLOCATE cursor_name CURSOR FOR RESULT SET locator_name;   -- DB2 compatibility

Example 1:

Sample stored procedure returning a single result set:

CREATE PROCEDURE spOpenIssues 
  DYNAMIC RESULT SETS 1
BEGIN
  DECLARE cur CURSOR WITH RETURN FOR
    SELECT id, name FROM issues;
  OPEN cur;
END;

Call a stored procedure and process the returned result set (Teradata compatibility):

Apache Hive : ASSOCIATE LOCATOR Statement

ASSOCIATE LOCATOR statement allows you to associate locator variable with a result set returned from a stored procedure.

Then you can use ALLOCATE CURSOR statement to assign a cursor for the locator and fetch data.

Syntax:

ASSOCIATE [RESULT SET] LOCATOR | LOCATORS (loc [, locN, ...]) 
  WITH PROCEDURE procedure_name

For examples, see ALLOCATE CURSOR statement.

Compatibility: IBM DB2

Apache Hive : HPL/SQL - BREAK Statement

BREAK statement exits the innermost loop.

Syntax:

BREAK;

Example:

DECLARE count INT DEFAULT 3;
WHILE 1=1 BEGIN
  SET count = count - 1;
  IF count = 0
    BREAK;
END

Compatibility: Microsoft SQL Server.

Apache Hive : HPL/SQL - CALL Statement

CALL statement allows you to execute a stored procedure.

Syntax:

CALL procedure_name [(parameter, ...)]; 

Example:

Define a procedure and then call passing a parameter:

CREATE PROCEDURE set_message(IN name STRING, OUT result STRING)
BEGIN
 SET result = 'Hello, ' || name || '!';
END;

-- Now call the procedure and print the results
DECLARE str STRING;
CALL set_message('world', str);
PRINT str;

Result:
--
Hello, world!

Compatibility: Teradata, IBM DB2 and MySQL

Apache Hive : HPL/SQL - CLOSE Statement

CLOSE statement closes a cursor.

Syntax:

CLOSE cursor_name;

Parameters:

Examples:

DECLARE id INT;
DECLARE cur CURSOR FOR 'SELECT id FROM db.orders';
OPEN cur;
FETCH cur INTO id;
CLOSE cur;

Compatibility: Oracle, IBM DB2, Teradata, SQL Server, PostgreSQL, MySQL.

See also:

• DECLARE CURSOR
• OPEN
• FETCH
• SQLCODE

Apache Hive : HPL/SQL - CMP Statement

CMP statement helps you compare data in tables that can be located in the same or different databases.

Syntax:

Compare the total number of rows:

CMP ROW_COUNT table1 [where_clause1] | (select_stmt1) [AT conn1], 
              table2 [where_clause2] | (select_stmt2) [AT conn2]

Compare the column summary (COUNT, SUM, MIN and MAX applied to columns):

CMP SUM table1 [where_clause1] [AT conn1], table2 [where_clause2] [AT conn2]

Notes:

• When data are equal, the CMP statement sets SQLCODE to 0. If data are not equal, SQLCODE is set to 1. In case of any SQL error, SQLCODE is set to -1
• When the connection profile is not specified, the default connection profile is used for the specified table

Example 1:

Apache Hive : HPL/SQL - COPY FROM FTP Statement

COPY FROM FTP statement allows to copy files from a FTP server to local or any Hadoop compatible file system. Using this statement you can easily copy FTP subdirectories into HDFS i.e.

The NEW option helps you build a ETL process and download only new files from FTP.

Syntax:

COPY FROM FTP host [USER user [PWD password]] [DIR directory] [FILES files_wildcard] 
  [TO [LOCAL] target_directory] [options]

options:
  OVERWRITE | NEW
  SUBDIR
  SESSIONS num  

Notes:

Apache Hive : HPL/SQL - COPY FROM LOCAL Statement

COPY FROM LOCAL statement allows to copy local directories and files to Hadoop compatible file system. Using this statement you can easily copy subdirectories into HDFS i.e.

Syntax:

COPY FROM LOCAL src [, src2, ...] TO tgt [options]

options:
  OVERWRITE
  DELETE
  IGNORE

Notes:

• srcN specifies a file or directory. If a directory is specified all subdirectories and their files are copied as well.
• srcN can be an expression, variable, quoted or unquoted string.
• If a single file is copied tgt must specify a path including the target file name.
• If multiple files are copied tgt must specify a directory.
• If the target directory does not exist it is created.
• OVERWRITE specifies to overwrite the target files if they exist.
• DELETE specifies to delete the source file upon successfull copy.
• IGNORE specifies to ignore errors when copying a file and proceed to copy the remaining files.

Example:

Apache Hive : HPL/SQL - COPY Statement

COPY statement allows to transfer data between tables and files. Use it to transfer relatively small volumes of data i.e. query results, look-up and dimension tables.

When you copy data between tables they can be located in different databases.

Syntax:

Export data to a file:

COPY table_name | (select_stmt) TO [HDFS] file_name [options]

options:
  DELIMITER 'char'
| SQLINSERT target_table_name

Copy data between existing tables:

Apache Hive : HPL/SQL - CREATE DATABASE Statement

CREATE DATABASE statement allows you to create a database.

Syntax:

CREATE DATABASE | SCHEMA [IF NOT EXISTS] dbname_expr
  [COMMENT comment_expr]
  [LOCATION path_expr]

Example:

Create a database named testYYYYMMDD (current date):

create database 'test' || replace(current_date, '-', '');

Compatibility: MySQL, MariaDB, Hive

See also:

• DROP DATABASE
• USE

Apache Hive : HPL/SQL - CREATE FUNCTION Statement

CREATE FUNCTION statement allows you to create a user-defined SQL function.

Syntax:

ALTER | CREATE [OR REPLACE] | REPLACE FUNCTION function_name ( [parameters] )
 RETURNS | RETURN data_type
 [AS | IS] 
 body

parameters:
  [IN] name data_type, ...
  |
  name [IN] data_type, ...

body:
  statement | expression | BEGIN statements END

Example 1:

Create a function without parameters:

CREATE FUNCTION hello()
 RETURNS STRING
BEGIN
 RETURN 'Hello, world';
END;

-- Call the function
PRINT hello();

Example 2:

Apache Hive : HPL/SQL - CREATE LOCAL TEMPORARY TABLE

CREATE LOCAL TEMPORARY TABLE statement allows you to create a temporary table for the current session.

Syntax:

CREATE LOCAL TEMPORARY TABLE table_name
(
   column_name data_type [NULL | NOT NULL]
   [, ...]
)
[ ON COMMIT DELETE ROWS | ON COMMIT PRESERVE ROWS]

Notes:

• The local temporary table is automatically dropped at the end of session.

For more details how temporary table support is implemented in HPL/SQL, see Native and Managed Temporary Tables.

Apache Hive : HPL/SQL - CREATE PACKAGE Statement

CREATE PACKAGE statement allows you to define a collection of related variables, procedures and functions.

Syntax

Package specification:

[CREATE [OR REPLACE] | REPLACE] PACKAGE package_name AS | IS package_spec END

package_spec:
  variable declaration |
  function declaration |
  procedure declaration

Package body:

[CREATE [OR REPLACE] | REPLACE] PACKAGE BODY package_name AS | IS package_body END

package_body:
  private variable declaration |
  function definition |
  procedure definition

Example:

Apache Hive : HPL/SQL - CREATE PROCEDURE Statement

CREATE PROCEDURE statement allows you to create a user-defined SQL procedure (stored procedure).

Syntax:

[ALTER | CREATE [OR REPLACE] | REPLACE] PROCEDURE | PROC procedure_name [parameters] 
 [AS | IS] 
 body

parameters:
  ([IN | OUT | INOUT | IN OUT] name data_type, ...)
  |
  (name [IN | OUT | INOUT | IN OUT] data_type, ...)

body:
  statement | expression | BEGIN statements END

Example:

Apache Hive : HPL/SQL - CREATE TABLE Statement

CREATE TABLE statement create a table in the database.

Syntax:

CREATE TABLE [IF NOT EXISTS] table_name
(
   column_name data_type [NULL | NOT NULL]
   [, constraint ...]
   [, ...]
)

CREATE TABLE Conversion

If the CREATE TABLE statement is defined using the syntax not supported by Hive, it is automatically converted to conform to Hive syntax.

Currently HPL/SQL converts data types, removes NOT NULL/NULL, constraints and default values. For more information, see On-the-Fly Conversion

Apache Hive : HPL/SQL - CREATE VOLATILE TABLE Statement

CREATE VOLATILE TABLE statement allows you to create a temporary table for the current session.

Syntax:

CREATE [SET | MULTISET] VOLATILE TABLE table_name
(
   column_name data_type [NULL | NOT NULL]
   [, ...]
)
[ ON COMMIT DELETE ROWS | ON COMMIT PRESERVE ROWS]

Notes:

• The volatile table is automatically dropped at the end of session.

For more details how temporary table support is implemented in HPL/SQL, see Native and Managed Temporary Tables.

Apache Hive : HPL/SQL - DECLARE CONDITION Statement

You can use DECLARE CONDITION statement to declare a user-defined condition.

Then you can define a handler for this condition using DECLARE HANDLER, and raise the condition using the SIGNAL statement.

Syntax:

DECLARE condition_name CONDITION;

Example:

Raise a condition if the number of rows is not equal to the specified number:

DECLARE cnt INT DEFAULT 0; 
DECLARE wrong_cnt_condition CONDITION;

DECLARE EXIT HANDLER FOR wrong_cnt_condition
  PRINT 'Wrong number of rows';  

SELECT COUNT(*) INTO cnt FROM TABLE (VALUES (1,2));

IF cnt <> 1 THEN
  SIGNAL wrong_cnt_condition;
END IF;

Compatibility: IBM DB2, Teradata and MySQL.

Apache Hive : HPL/SQL - DECLARE CURSOR Statement

You can use DECLARE CURSOR statement to declare a cursor using a dynamic SQL.

Syntax:

DECLARE name CURSOR FOR | AS | IS dynamic_sql_string | select_statement;

Parameters:

Notes:

• dynamic_sql_string expression is evaluated at cursor open time, not declare time.

Example 1:

Apache Hive : HPL/SQL - DECLARE HANDLER Statement

You can use DECLARE HANDLER statement to define one or more HPL/SQL statements to execute when a condition occurs.

Syntax:

DECLARE [CONTINUE | EXIT] HANDLER FOR 
  [SQLEXCEPTION | NOT FOUND | user_condition] code_block;

Description:

Examples:

Apache Hive : HPL/SQL - DECLARE TEMPORARY TABLE

DECLARE TEMPORARY TABLE statement allows you to define a temporary table for the current session.

Syntax:

DECLARE [GLOBAL] TEMPORARY TABLE table_name
(
   column_name data_type [NULL | NOT NULL]
   [, ...]
)
[ ON COMMIT DELETE ROWS | ON COMMIT PRESERVE ROWS]

Compatibility Options

The following options are supported for compatibility with other databases:

IBM DB2:

IN tablespace_name
WITH REPLACE
DISTRIBUTE BY HASH (col, ...) 
LOGGED | NOT LOGGED

For more details how temporary table support is implemented in HPL/SQL, see Native and Managed Temporary Tables.

Apache Hive : HPL/SQL - DESCRIBE Statement

DESCRIBE statement allows you to print a metadata information for the specified database object.

Syntax:

DESCRIBE | DESC [TABLE] table_name

Example:

Describe src table in Hive:

desc src;
--
key                     string                  default
value                   string                  default

Compatibility: Oracle, IBM DB2, Hive, MySQL, MariaDB.

Apache Hive : HPL/SQL - DROP DATABASE Statement

DROP DATABASE statement allows you to drop a database.

Syntax:

DROP DATABASE | SCHEMA [IF EXISTS] dbname_expr

Example:

Drop a database named testYYYYMMDD (current date):

drop database if exists 'test' || replace(current_date, '-', '');

Compatibility: Hive

See also:

• CREATE DATABASE
• USE

Apache Hive : HPL/SQL - DROP TABLE Statement

DROP TABLE statement drops a table.

Syntax:

DROP TABLE [IF EXISTS] table_name 

Compatibility: Oracle, Microsoft SQL Server, IBM DB2, Teradata, PostgreSQL, MySQL, Hive

See also:

• TRUNCATE TABLE

Apache Hive : HPL/SQL - Execute OS Command or External Process

HPL/SQL allows you to execute an OS command or external process from a HPL/SQL script:

Syntax:

! command:

!cmd [arguments];

HOST statement:

HOST string_expr

Parameters:

Notes:

• Blank characters are allowed between ! and cmd
• The ! command must be terminated by a semicolon (;)

Example:

Apache Hive : HPL/SQL - EXECUTE Statement

EXECUTE (EXEC or EXECUTE IMMEDIATE) statement executes a dynamic SQL statement and can return the scalar result to local variables.

You can also use this statement to call a stored procedure.

Syntax:

EXEC | EXECUTE | EXECUTE IMMEDIATE dynamic_sql_string [INTO var1, var2, ...];
|
EXEC | EXECUTE proc_name [parm1 = val1, ... ]

Parameters:

Notes:

Apache Hive : HPL/SQL - EXIT WHEN Statement

EXIT WHEN statement exits the loop or block marked by the given label. If the label is not specified, EXIT leaves the innermost loop.

If a boolean expression is specified, and it evaluates to true then EXIT statement is executed, otherwise it is ignored and the execution continues from the statement following EXIT.

Syntax:

EXIT [label] [WHEN boolean_expression];

Example:

WHILE count > 0 LOOP
  count := count - 1;
  EXIT WHEN count = 0;
END LOOP;

<<lbl>>
WHILE 1=1 LOOP
  <<lbl1>>
  WHILE 1=1 LOOP
    EXIT lbl;
  END LOOP;
END LOOP;

Compatibility: Oracle, PostgreSQL and Netezza.

Apache Hive : HPL/SQL - FETCH Statement

FETCH statement retrieve the next row from a cursor and assigns column values to local variable.

Syntax:

FETCH [FROM] cursor_name INTO var1 [, var2, ...];

Parameters:

Examples:

DECLARE tabname VARCHAR DEFAULT 'db.orders';
DECLARE id INT;
DECLARE cur CURSOR FOR 'SELECT id FROM ' || tabname;
OPEN cur;
FETCH cur INTO id;
WHILE SQLCODE=0 THEN
  PRINT id;
  FETCH cur INTO id;
END WHILE;
CLOSE cur;

Compatibility: Oracle, IBM DB2, Teradata, SQL Server, MySQL, PostgreSQL and Netezza.

Apache Hive : HPL/SQL - FOR Statement (Cursor Loop)

FOR statement opens a cursor, executes one or more statements repeatedly for each row and closes the cursor.

Syntax:

FOR cur_name IN [(] select_stmt [)] LOOP
  statements
END LOOP;

Notes:

• You can refer to the cursor columns using cur_name.col_name syntax

Example:

FOR item IN (
    SELECT dname, loc as location
    FROM dept
    WHERE dname LIKE '%A%'
    AND deptno > 10
    ORDER BY location)
LOOP
  DBMS_OUTPUT.PUT_LINE('Name = ' || item.dname || ', Location = ' || item.location);
END LOOP;

Compatibility: Oracle, PostgreSQL and Netezza

Apache Hive : HPL/SQL - FOR Statement (Integer Range)

FOR statement executes one or more statements repeatedly for the specified range of integer values.

Syntax:

FOR index IN [REVERSE] lower_bound..upper_bound [BY | STEP increment] LOOP
  statements
END LOOP;

Notes:

• index - Implicitly declared integer variable
• If REVERSE is specified the index is decreased
• If specified, BY (or STEP) define the iteration step, default is 1

Examples:

FOR i IN 1..10 LOOP
  -- i will have values: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
END LOOP;

FOR i IN REVERSE 10..1 LOOP
  -- i will have values: 10, 9, 8, 7, 6, 5, 4, 3, 1, 1
END LOOP;

FOR i IN 1..10 BY 2 LOOP
  -- i will have values: 1, 3, 5, 7, 9
END LOOP;

Compatibility: Oracle, PostgreSQL and Netezza.

Apache Hive : HPL/SQL - GET DIAGNOSTICS Statement

GET DIAGNOSTICS statement allows you to retrieve the error message, the number of rows about the previous SQL statement.

Syntax:

Get the error text:

GET DIAGNOSTICS EXCEPTION 1 var_name = MESSAGE_TEXT;

Get the number of rows associate with the previous SQL statement:

GET DIAGNOSTICS var_name = ROW_COUNT;

Important Note:

• Hive does not support JDBC Statement.getUpdateCount() for INSERT statements, so GET DIAGNOSTICS ROW_COUNT will return 0 for Hive 0.13 and earlier and -1 for Hive 0.14 and later. See HIVE-7680 for more details.

Compatibility: IBM DB2

Apache Hive : HPL/SQL - IF Statement

IF statement executes a set of statements depending on the value of a boolean expression.

HPL/SQL supports multiple syntaxes for IF statement.

IF - THEN - ELSIF/ELSEIF - ELSE - END IF

Syntax:

IF boolean_expression THEN
  statements
[ELSIF | ELSEIF THEN
  statements
...]
[ELSE
  statements]
END IF;

Example:

IF state = 'CA' THEN
  code := 1;
ELSIF state = 'NY' THEN
  code := 2;
ELSIF state = 'MA' THEN
  code := 3;
ELSE
  code := 5;
END IF;

Compatibility: Oracle, Teradata, IBM DB2, MySQL, PostgreSQL, Netezza.

Apache Hive : HPL/SQL - INCLUDE Statement

INCLUDE statement allows you to include statements from another HPL/SQL script.

You can define user-defined functions and stored procedures in separate HPL/SQL scripts and then use INCLUDE statements to make them available in the current script.

Additionally you can put INCLUDE statements to .hplsqlrc configuration file, so these functions and procedures are always available to users similar to persistent objects in the database.

Syntax:

Apache Hive : HPL/SQL - INSERT DIRECTORY Statement

INSERT DIRECTORY statement allows you to write the query results to a local or HDFS-compatible file system.

Syntax:

INSERT OVERWRITE [LOCAL] DIRECTORY directory select_statement

Notes:

• directory specifies the target directory (path, variable or expression)
• select_statement specifies the query (you can also use dynamic SQL string)

Examples:

Export sales data:

insert overwrite directory '/data/sales_daily' select * from sales_daily;

Export sales data from the specified table and put to the directory for the current date:

Apache Hive : HPL/SQL - INSERT Statement

INSERT statement inserts rows into a table.

Syntax:

Insert from SELECT:

INSERT OVERWRITE TABLE table_name select_statement
| 
INSERT INTO [TABLE] table_name select_statement

Insert values:

INSERT INTO [TABLE] table_name VALUES (exrp, expr2, ...) [, (exrp, expr2, ...), ...] 

INSERT VALUES

HPL/SQL provides you with two options to run INSERT VALUES statement: native and select.

Use the hplsql.insert.values option to define how to handle INSERT VALUES statement, the default value is native.

Apache Hive : HPL/SQL - LEAVE Statement

LEAVE statement exits the loop or block marked by the given label. If the label is not specified, LEAVE exists the innermost loop.

Syntax:

LEAVE [label];

Example:

lbl:
WHILE count > 0 DO
  SET count = count - 1;
  IF count = 0 THEN
    LEAVE lbl;
  END IF;
END WHILE;

lbl:
WHILE 1=1 DO
  lbl1:
  WHILE 1=1 DO
    LEAVE lbl;
  END WHILE;
END WHILE;

Compatibility: Teradata, IBM DB2 and MySQL.

Apache Hive : HPL/SQL - LOOP Statement

LOOP statement executes one or more statements until you exit the loop using EXIT, LEAVE or BREAK statements, or raising an exception.

Syntax:

[<<label>> | label:]
LOOP
  statements
END LOOP;

Examples:

-- Oracle, PostgreSQL, Netezza
LOOP
  count := count - 1;
  EXIT WHEN count = 0;
END LOOP;

-- DB2, Teradata, MySQL
lbl:
LOOP
  SET count = count - 1;
  IF count = 0 THEN
    LEAVE lbl;
  END IF;
END LOOP;

Compatibility: Oracle, Teradata, IBM DB2, MySQL, PostgreSQL and Netezza.

Apache Hive : HPL/SQL - MAP OBJECT Statement

MAP OBJECT statement allows you to map an object (table or view) to a connection profile. You can also use this statement to map an object name used in a HPL/SQL script to the actual object name in the database.

Depending on the connection profile linked to the object, HPL/SQL can work with multiple databases to access different objects in a single HPL/SQL script.

Apache Hive : HPL/SQL - NULL Statement

NULL statement is a no operation statement (no-op), it just passes control to the next statement.

Syntax:

NULL;

Example:

declare 
  code char(1) := 'a';
begin
  null;
end;

Compatibility: Oracle

Apache Hive : HPL/SQL - OPEN Statement

OPEN statement opens a cursor.

Syntax:

OPEN cursor_name [FOR expression | select_statement];

Description:

Examples:

Open the previously declared cursor:

DECLARE tabname VARCHAR(20) DEFAULT 'db.orders';
DECLARE id INT;
DECLARE cur CURSOR FOR 'SELECT id FROM ' || tabname;
OPEN cur;
FETCH cur INTO id;
WHILE SQLCODE=0 THEN  
  PRINT id;
  FETCH cur INTO id;
END WHILE;
CLOSE cur;

Open a cursor using a dynamic SQL:

Apache Hive : HPL/SQL - PRINT Statement

PRINT statement prints a line and can be helpful to debug programs. The statement appends a line terminator.

Syntax:

PRINT exp
or
PRINT(exp)

Parameters:

Return Value:

No.

Examples:

PRINT 'Hello, world!';
PRINT 'Hello, ' || 'world!';
PRINT('Hello, world!');

Compatibility: Microsoft SQL Server

Apache Hive : RESIGNAL Statement - HPL/SQL Reference

RESIGNAL statement in used in a condition or exception handler to re-raise an error so it can be processed at a higher level.

Syntax:

RESIGNAL
|
RESIGNAL SQLSTATE [VALUE] sqlstate [SET MESSAGE_TEXT = message_text]

Example 1:

Re-raise the same error:

BEGIN
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  BEGIN
    PRINT 'Error raised';
    RESIGNAL;
  END;
  PRINT 'Before executing SQL';
  SELECT * FROM abc.abc;      -- Table does not exist, error will be raised
  PRINT 'After executing SQL - will not be printed in case of error';
END;

Result:

Apache Hive : HPL/SQL - RETURN Statement

RETURN statement is used to return from a routine.

Syntax:

RETURN [expr];

Parameters:

Notes:

• If the return value is not specified, 0 is returned

Examples:

RETURN;

Return the result of an expression:

RETURN NVL(v1, 1);

Compatibility: Oracle, IBM DB2, SQL Server, Teradata, PostgreSQL, MySQL, Netezza.

Apache Hive : HPL/SQL - SELECT INTO Statement

SELECT INTO statement allows you to assign values to variables using a SQL SELECT query.

Example:

DECLARE cnt INT = 0;
SELECT COUNT(*) INTO cnt FROM users;
PRINT 'Users: ' || cnt; 

Compatibility: Oracle, IBM DB2, Teradata, PostgreSQL, MySQL and Netezza.

See also:

• SELECT

Apache Hive : HPL/SQL - SELECT Statement

SELECT statement allows you to run queries.

SELECT TOP n

You can specify a SELECT statement with the TOP clause. PL/SQL automatically converts it to LIMIT clause for Hive.

Example:

SELECT TOP 3 name FROM sales;    -- SELECT name FROM sales LIMIT 3 is executed

Compatibility: Microsoft SQL Server.

SELECT Without FROM Clause

You can specify a SELECT statement without FROM. PL/SQL automatically adds the FROM clause using the table name defined by the hplsql.dual.table option.

Apache Hive : HPL/SQL - SET Session Option

SET statement allows you to set various session-level options.

CURRENT SCHEMA

Changing the current schema (database):

Syntax:

SET [CURRENT] SCHEMA [=] schema_name;
|
SET CURRENT_SCHEMA [=] schema_name;

Note:

• schema_name is an identifier, string literal or expression.
• HPL/SQL converts this statement to USE schema_name statement in Hive.

Example:

SET CURRENT SCHEMA = default;
SET SCHEMA = 'default';
SET SCHEMA 'def' || 'ault';

Compatibility: IBM DB2

Apache Hive : HPL/SQL - SIGNAL Statement

SIGNAL statement raises a user-defined condition (exception).

Syntax:

SIGNAL condition_name;

Example:

Raise a condition if the number of rows is not equal to the specified number:

DECLARE cnt INT DEFAULT 0; 
DECLARE wrong_cnt_condition CONDITION;

DECLARE EXIT HANDLER FOR wrong_cnt_condition
  PRINT 'Wrong number of rows';  

SELECT COUNT(*) INTO cnt FROM TABLE (VALUES (1,2));

IF cnt <> 1 THEN
  SIGNAL wrong_cnt_condition;
END IF;

Compatibility: IBM DB2, Teradata and MySQL

Apache Hive : HPL/SQL - TRUNCATE TABLE Statement

TRUNCATE TABLE statement removes all rows in the specified table.

Syntax:

TRUNCATE [TABLE] table_name 

Example:

Remove all rows in users2015 table:

truncate table users2015;

Compatibility: Oracle, Microsoft SQL Server, IBM DB2, MySQL, Hive

See also:

• DROP TABLE

Apache Hive : HPL/SQL - UPDATE Statement

UPDATE statement allows you to update columns of existing rows in the specified table.

Syntax:

UPDATE table_name
  SET col = expr [, coln = exprn] ...
  [WHERE condition]

UPDATE table_name
  SET (col [, coln] ...) = (expr [, exprn] ... | select_statement)
  [WHERE condition]

Apache Hive : HPL/SQL - USE Statement

USE statement allows you to change the default database used in SQL statements for the current connection.

Syntax:

USE database_expr;

Note: HPL/SQL allows you to use an expression to specify the database name

Example:

USE sales;

USE SUBSTR(var, 1, 3);

Compatibility: Hive, MySQL, MariaDB

See also:

• CREATE DATABASE
• DROP DATABASE

Apache Hive : HPL/SQL - VALUES INTO Statement

You can use the VALUES INTO statement to assign values to variables in HPL/SQL.

If the variable was not explicitly declared before the assignment, a new variable is created and its data type is derived from the assignment expression.

Syntax:

VALUES expression INTO var;
|
VALUES (expression [, expression2, ...]) INTO (var [, var2, ...]); 

Example:

VALUES 'A' INTO code;
VALUES (0, 100) INTO (count, limit); 

Compatibility: IBM DB2

Apache Hive : HPL/SQL - WHILE Statement

WHILE statement executes one or more statements while the condition is true.

Syntax:

[<<label>> | label:]
WHILE boolean_expression LOOP | DO | BEGIN
  statements
END [LOOP | WHILE;]

Examples:

-- Oracle, PostgreSQL, Netezza
WHILE count > 0 LOOP
  count := count - 1;
END LOOP;

-- DB2, Teradata, MySQL
WHILE count > 0 DO
  SET count = count - 1;
END WHILE;

-- SQL Server
WHILE count > 0 BEGIN
  SET count = count - 1;
END

Compatibility: Oracle, Teradata, IBM DB2, Microsoft SQL Server, MySQL, PostgreSQL and Netezza.