Apache Hive : HPL/SQL - CAST Function

CAST function converts an expression to the specified data type.

Syntax:

CAST(expression AS datatype[(length)]); 

Notes:

• If length is specified for CAST as CHAR or VARCHAR function, the resulting string is truncated to this length.

Example 1:

Convert to a string with the specified length:

CAST('Abc' AS CHAR(1)); 
--
A

Example 2:

Truncate a timestamp string:

CAST(TIMESTAMP '2015-03-12 10:58:34.111' AS CHAR(10));
--
2015-03-12

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

Apache Hive : HPL/SQL - CHAR Function

CHAR function converts a number to string.

Syntax:

CHAR(num_expression); 

Return Type:

STRING

Example 1:

CHAR(1000); 
--
1000

Compatibility: IBM DB2

See also:

• TO_CHAR Function

Apache Hive : HPL/SQL - COALESCE Function

COALESCE function returns first non-NULL expression.

Syntax:

COALESCE(expr1, expr2 [, expr3, ...]); 

Parameters:

Notes:

• When first non-NULL expression is found the following expressions are not evaluated
• COALESCE and NVL functions are synonyms

Return Value:

• First non-NULL expression
• NULL if all expressions evaluate to NULL

Return Type:

The data type of first non-NULL expression.

Example 1:

COALESCE(NULL, 1, 2, 3); 

Result: 1

Apache Hive : HPL/SQL - CONCAT Function

CONCAT function concatenates two or more strings.

Syntax:

CONCAT(expr, expr2 [, expr3, ...]); 

Notes:

• If an expression evaluates to NULL it is treated as an empty string
• CONCAT returns NULL only if all expressions evaluate to NULL

Return Type:

STRING

Example:

CONCAT('a', 'b', NULL, 'c'); 

Result: abc

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

See also:

• || Operator

Apache Hive : HPL/SQL - CURRENT_DATE Function

CURRENT_DATE function return the current date (year, month and day).

Syntax:

CURRENT_DATE | CURRENT DATE 

Return Type:

DATE

Compatibility: IBM DB2, Teradata, MySQL.

See also:

• CURRENT_TIMESTAMP
• FROM_UNIXTIME
• NOW
• SYSDATE
• UNIX_TIMESTAMP

Apache Hive : HPL/SQL - CURRENT_TIMESTAMP Function

CURRENT_TIMESTAMP function returns the current date and time (year, month, day, hour, minute, seconds and fractional seconds).

Syntax:

CURRENT_TIMESTAMP | CURRENT TIMESTAMP [(precision)] 

Parameters:

Return Type:

TIMESTAMP

Example:

Get the current and date and time without fraction:

CURRENT_TIMESTAMP(0)
--
2015-03-02 13:04:42

Compatibility: Oracle, IBM DB2, Teradata, MySQL.

See also:

Apache Hive : HPL/SQL - CURRENT_USER Function

CURRENT_USER function returns the name of the user executing the current HPL/SQL script.

Syntax:

CURRENT_USER | CURRENT USER 

Return Type:

STRING

Example:

Get the current user:

CURRENT_USER
--
paul

Compatibility: IBM DB2, Teradata.

See also:

• USER

Apache Hive : HPL/SQL - DATE Function

DATE function converts an expression to DATE data type.

Syntax:

DATE(expression); 

Return Data Type:

DATE

Example:

Convert a string and timestamp to DATE:

DATE('2015-03-12');
DATE('2015' || '-03-' || '12');
DATE(TIMESTAMP '2015-03-12 10:58:34.111');

Compatibility: IBM DB2

See also:

• DATE Literal
• TIMESTAMP Literal
• TIMESTAMP_ISO Function

Apache Hive : HPL/SQL - DBMS_OUTPUT Package

DBMS_OUTPUT package allows you to send messages and can be helpful to debug programs

Example:

BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello, world!');
END;

Compatibility: Oracle

PUT_LINE Function

PUT_LINE function writes a text string to the standard output (screen, by default). The function appends a line terminator.

Syntax:

DBMS_OUTPUT.PUT_LINE(text);

Parameters:

Return Value:

No.

Example:

BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello, world!');
END;

Apache Hive : HPL/SQL - DECODE Function

DECODE function allows you to implement IF-THEN-ELSE logic.

Syntax:

DECODE(expr, when_exp1, then_expr1 [, ...n] [, else_expr]) 

Notes:

• If expr is NULL it will match the first when_exprN that is NULL
• If when_exprN is not matched then_exprN is not evaluated

Examples:

DECLARE var1 INT DEFAULT 3;

PRINT DECODE (var1, 1, 'A', 2, 'B', 3, 'C');       -- Result: C
PRINT DECODE (var1, 1, 'A', 2, 'B', 'C');          -- Result: C

SET var1 = NULL;
PRINT DECODE (var1, 1, 'A', 2, 'B', NULL, 'C');    -- Result: C  

Compatibility: Oracle, IBM DB2 and Teradata

Apache Hive : HPL/SQL - FROM_UNIXTIME Function

FROM_UNIXTIME function converts the specified number of seconds since 1970-01-01 00:00:00 to timestamp value.

Syntax:

FROM_UNIXTIME(epoch, [format])

Parameters:

• epoch is the number of seconds since 1970-01-01 00:00:00
• format is the timestamp format, optional. The default format is yyyy-MM-dd HH:mm:ss

Return Type:

STRING

Example:

Convert the number of seconds to timestamp value:

from_unixtime(1447141681);
---
2015-11-10 04:48:01

from_unixtime(1447141681, 'yyyy-MM-dd');
---
2015-11-10

Compatibility: Hive.

See also:

• CURRENT_DATE
• CURRENT_TIMESTAMP
• NOW
• SYSDATE
• UNIX_TIMESTAMP

Apache Hive : HPL/SQL - INSTR Function

INSTR function returns the starting position of a substring within a string.

Syntax:

INSTR(string, substring [, position [, occurrence]]) 

Notes:

• position specifies the staring position for search, the default is 1 (the beginning of string)
• If position is negative INSTR counts and searches backward from the end of string
• occurrence specifies which occurrence of substring to search, the default is 1 (finds the first occurrence)
• If string is NULL the return value is NULL
• if string is not NULL and substring not found the return value is 0

Return Type:

Apache Hive : HPL/SQL - LEN Function

LEN function returns the length of the specified string expression in characters, excluding the trailing blanks.

Syntax:

LEN(string_expression); 

Return Data Type:

STRING

Example:

LEN('Abc ');
---
3

Compatibility: Microsoft SQL Server

See also:

• LENGTH

Apache Hive : HPL/SQL - LENGTH Function

LENGTH function returns the length of the specified string expression in characters.

Syntax:

LENGTH(string_expression); 

Return Data Type:

STRING

Example:

LENGTH('Abc ');
---
4

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

See also:

• LEN

Apache Hive : HPL/SQL - LOWER Function

LOWER function converts a string expression to lower case.

Syntax:

LOWER(expression); 

Return Data Type:

STRING

Example:

Convert a string to lower case:

LOWER('ABC');
---
abc

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

Apache Hive : HPL/SQL - MAX_PART_DATE Function

MAX_PART_DATE function finds the maximum value for the specified partition column of type DATE.

Syntax:

MAX_PART_DATE([db_name.]table_name [, column_name [, part_col=filter, ...]]); 

Parameters:

Notes:

• If column name is not specified, the first partition column is used
• Partition filter applied before finding the maximum value

Return Value:

Apache Hive : HPL/SQL - MAX_PART_INT Function

MAX_PART_INT function finds the maximum value for the specified partition column of type INT.

Syntax:

MAX_PART_INT([db_name.]table_name [, column_name [, part_col=filter, ...]]); 

Parameters:

Notes:

• If column name is not specified, the first partition column is used
• Partition filter applied before finding the maximum value
• If the partition contains non-integer values they are ignored

Return Value:

Apache Hive : HPL/SQL - MAX_PART_STRING Function

MAX_PART_STRING function finds the maximum value (last in alphabetical order) for the specified partition column of type STRING (VARCHAR/CHAR).

Syntax:

MAX_PART_STRING([db_name.]table_name [, column_name [, part_col=filter, ...]]); 

Parameters:

Notes:

• If column name is not specified, the first partition column is used
• Partition filter applied before finding the maximum value

Return Value:

Apache Hive : HPL/SQL - MIN_PART_DATE Function

MIN_PART_DATE function finds the minimum value for the specified partition column of type DATE.

Syntax:

MIN_PART_DATE([db_name.]table_name [, column_name [, part_col=filter, ...]]); 

Parameters:

Notes:

• If column name is not specified, the first partition column is used
• Partition filter applied before finding the minimum value

Return Value:

Apache Hive : HPL/SQL - MIN_PART_INT Function

MIN_PART_INT function finds the minimum value for the specified partition column of type INT.

Syntax:

MIN_PART_INT([db_name.]table_name [, column_name [, part_col=filter, ...]]); 

Parameters:

Notes:

• If column name is not specified, the first partition column is used
• Partition filter applied before finding the minimum value
• If the partition contains non-integer values they are ignored

Return Value:

Apache Hive : HPL/SQL - MIN_PART_STRING Function

MIN_PART_STRING function finds the minimum value (first in alphabetical order) for the specified partition column of type STRING (VARCHAR/CHAR).

Syntax:

MIN_PART_STRING([db_name.]table_name [, column_name [, part_col=filter, ...]]); 

Parameters:

Notes:

• If column name is not specified, the first partition column is used
• Partition filter applied before finding the minimum value

Return Value:

Apache Hive : HPL/SQL - NOW Function

NOW function returns the current date and time (year, month, day, hour, minute, seconds and fractional seconds).

Syntax:

NOW()

Return Type:

TIMESTAMP

Example:

Get the current and date and time:

NOW()
--
2015-11-02 07:59:25.833

Compatibility: PostgreSQL and MySQL.

See also:

• CURRENT_DATE
• CURRENT_TIMESTAMP
• FROM_UNIXTIME
• UNIX_TIMESTAMP
• SYSDATE

Apache Hive : HPL/SQL - NVL Function

NVL function returns first non-NULL expression.

Syntax:

NVL(expr1, expr2 [, expr3, ...]); 

Parameters:

Notes:

• When first non-NULL expression is found the following expressions are not evaluated
• NVL and COALESCE functions are synonyms

Return Value:

• First non-NULL expression
• NULL if all expressions evaluate to NULL

Return Type:

The data type of first non-NULL expression.

Example 1:

NVL(NULL, 1); 

Result: 1

Apache Hive : HPL/SQL - NVL2 Function

If the first expression is NOT NULL, NVL2 function returns the result of the second expression, otherwise it returns the result of the third expression.

Syntax:

NVL2(expr1, expr2, expr3); 

Parameters:

Notes:

• If expr1 is not NULL, expr2 is only evaluated; and if expr1 is NULL, expr3 is only evaluated

Return Type:

The data type of the returned expression by expr2 or expr3 depending whether expr1 is NULL or not.

Apache Hive : HPL/SQL - PART_COUNT Function

PART_COUNT function returns the number partitions in the specified table.

Syntax:

PART_COUNT([db_name.]table_name, part_col=filter, ...); 

Parameters:

Notes:

• HPL/SQL uses the following Hive statement to get the partition information:

SHOW PARTITIONS db_name.tab_name [PARTITION (part_col=filter, ...)]

Return Value:

• The number of partitions
• NULL if the table does not exist or an error occurs

Return Type:

Apache Hive : HPL/SQL - PART_COUNT_BY Function

PART_COUNT_BY function returns the number partitions grouped by specified partition columns in the table.

Syntax:

PART_COUNT_BY([db_name.]table_name, [part_col, ...]); 

Parameters:

Return Value:

• The number of top-level partitions if part_col is not specified
• Partition value and total number of existing partitions with the same value if part_col is not specified

Examples:

Apache Hive : HPL/SQL - PART_LOC Function

PART_LOC function returns the location of the specified table partition in HDFS or other storage.

Syntax:

PART_LOC([db_name.]table_name, part_col=filter, ... [, with_hostname]); 

Parameters:

Notes:

• HPL/SQL uses the following Hive statement to get the partition information:

DESCRIBE EXTENDED db_name.tab_name PARTITION (part_col=filter, ...)

Return Value:

Apache Hive : HPL/SQL - REPLACE Function

REPLACE function replaces all occurrences of the specified substring with another substring.

Syntax:

REPLACE(string, what, with)

Parameters:

Return Type:

String.

Example:

replace('2016-03-03', '-', '');
--
20160303 

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

Apache Hive : HPL/SQL - SUBSTR Function

SUBSTR function returns a substring from string.

Syntax:

SUBSTR(string, start_pos [, substring_len])

Parameters:

Notes:

• If start_pos is 0 then it is treated as 1
• SUBSTR and SUBSTRING functions are synonyms

Return Type:

String.

Example:

Apache Hive : HPL/SQL - SUBSTRING Function

SUBSTRING function returns a substring from string.

Syntax:

SUBSTRING(string, start_pos [, substring_len])
|
SUBSTRING(string FROM start_pos [FOR substring_len])

Parameters:

Notes:

• If start_pos is 0 then it is treated as 1
• SUBSTRING and SUBSTR functions are synonyms

Return Type:

Apache Hive : HPL/SQL - SYSDATE Function

SYSDATE function returns the current date and time (year, month, day, hour, minute and seconds).

Syntax:

SYSDATE

Return Type:

TIMESTAMP

Example:

Get the current and date and time:

SYSDATE
--
2015-03-03 11:06:31

Compatibility: Oracle

See also:

• CURRENT_DATE
• CURRENT_TIMESTAMP
• FROM_UNIXTIME
• NOW
• UNIX_TIMESTAMP

Apache Hive : HPL/SQL - TIMESTAMP_ISO Function

TIMESTAMP_ISO function converts a string or date expression to TIMESTAMP data type.

The string must be in ‘YYYY-MM-DD HH24:MI:SS.FF’ or ‘YYYY-MM-DD’ format.

Syntax:

TIMESTAMP_ISO(expression); 

Return Data Type:

TIMESTAMP

Example 1:

Convert a string to TIMESTAMP:

TIMESTAMP_ISO('2015-03-12');
--
2015-03-12 00:00:00

Example 2:

Convert a date to TIMESTAMP:

TIMESTAMP_ISO(DATE '2015-03-12');
--
2015-03-12 00:00:00

Compatibility: IBM DB2

See also:

• DATE Literal
• TIMESTAMP Literal
• DATE Function
• TO_TIMESTAMP Function

Apache Hive : HPL/SQL - TO_CHAR Function

TO_CHAR function converts an expression to string.

Syntax:

TO_CHAR(expression); 

Return Type:

STRING

Example:

TO_CHAR(CURRENT_DATE); 

Compatibility: Oracle, IBM DB2 and Teradata

See also:

• CHAR Function

Apache Hive : HPL/SQL - TO_TIMESTAMP Function

TO_TIMESTAMP function converts a string to TIMESTAMP data type using the specified format.

Syntax:

TO_TIMESTAMP(string_expression, format_expression); 

Return Data Type:

TIMESTAMP

Format Elements:

Examples:

TO_TIMESTAMP('2015-04-02', 'YYYY-MM-DD');
TO_TIMESTAMP('04/02/2015', 'mm/dd/yyyy');
TO_TIMESTAMP('2015-04-02 13:51:31', 'YYYY-MM-DD HH24:MI:SS');

Compatibility: Oracle, IBM DB2, Teradata

See also:

• DATE Literal
• TIMESTAMP Literal
• DATE Function
• TIMESTAMP_ISO function

Apache Hive : HPL/SQL - TRIM Function

TRIM function removes leading and trailing characters from a string.

Syntax:

TRIM(string_expression); 

Return Type:

STRING

Example 1:

'#' || TRIM(' Hello ') || '#'; 
--
#Hello#

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

Apache Hive : HPL/SQL - UNIX_TIMESTAMP Function

UNIX_TIMESTAMP function returns the current date and time in seconds since 1970-01-01 00:00:00.

Syntax:

UNIX_TIMESTAMP()

Return Type:

INT

Example:

Get the current and date and time in seconds:

UNIX_TIMESTAMP()
--
1446631617

Compatibility: Hive.

See also:

• CURRENT_DATE
• CURRENT_TIMESTAMP
• FROM_UNIXTIME
• NOW
• SYSDATE

Apache Hive : HPL/SQL - UPPER Function

UPPER function converts a string expression to upper case.

Syntax:

UPPER(expression); 

Return Data Type:

STRING

Example:

Convert a string to upper case:

UPPER('abc');
---
ABC

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

Apache Hive : HPL/SQL - USER Function

USER function returns the name of the user executing the current HPL/SQL script.

Syntax:

USER

Return Type:

STRING

Example:

Get the current user:

USER
--
paul

Compatibility: Oracle, IBM DB2 and Teradata.

See also:

• CURRENT_USER