Working with Hive Macros, Syntax and Examples

Many relational databases such as Teradata supports Macro functions. In RDBMS, Macros are stored in the data dictionary. Users can share macros and can execute based on the requirements. Hive Macros are a bit different compared to that of relational databases. In this article, we will check what are Macros, its syntax, how to use them and some macro examples.

What are Macros in Hive?

The macros in Hive are set of SQL statements which are stored and executed by calling macro function names. Macros exist for the duration of the current session. Macros are temporary and if you want to have permanent macros better to create user defined functions and register it on Hive. You cannot share the macros as they are available only within the current session. You must create macro every time if you want to use it.

The Hive starting from version 0.13 provides support macro.

Hive Macro Syntax

You can use following syntax to create Macro function in Hive.

CREATE TEMPORARY MACRO macro_name([col_name col_type, ...]) expression;

CREATE TEMPORARY MACRO creates a macro using the given optional list of columns as inputs to the expression.

You can execute macros same as you execute function using SELECT clause.

Hive Macro Examples

Following are some of macro example.

Hive Macro to Check Data Type – Check if given string is numeric

In my other post, we have discussed how to check if a string is numeric using user defined function. However, you can achieve the same by creating Hive macro.

For example, consider below

DROP TEMPORARY MACRO IF EXISTS isNumeric;

CREATE TEMPORARY MACRO isNumeric (input INT)
CASE
 WHEN input rlike '^(([0-9]*)|(([0-9]*)\.([0-9]*)))$' THEN true else false
END
;

Now, let us test this macro with some input.

select isNumeric(1.23), isNumeric('1ad23');
+-------+--------+--+
|  _c0  |  _c1   |
+-------+--------+--+
| true  | false  |
+-------+--------+--+

Hadoop Hive isnumeric Function Alternative and Example

Hive Macro Convert Date To YYYY-MM-DD Format

You can create macro to convert date value to specific format.

For example, consider below example to convert input date format to yyyy-mm-dd format.

DROP TEMPORARY MACRO IF EXISTS cast_date_YMD;

CREATE TEMPORARY MACRO cast_date_YMD(input string, format string) 
CASE 
    WHEN UPPER(format) = "YMD"
    THEN from_unixtime(unix_timestamp(input ,'yyyy-MM-dd HH:mm:ss.sss'), 'yyyy-MM-dd')
END
;

Result

select cast_date_YMD('2019-11-16 22:10:16.994', 'YMD');

+-------------+--+
|     _c0     |
+-------------+--+
| 2019-11-16  |
+-------------+--+

Hive Macro to Check if String is Number

You can create macro to check if given string is number.

For example, consider below macro to check number.

DROP TEMPORARY MACRO IF EXISTS isNumber;

CREATE TEMPORARY MACRO isNumber(inputstring string)
CASE WHEN CAST(inputstring AS INT) IS NULL THEN false else true
END
;

Result

+-------+--------+--+
|  _c0  |  _c1   |
+-------+--------+--+
| true  | false  |
+-------+--------+--+

Hive Macro SemanticException Error

This is one of the common errors with macros. The error is caused by upper case and lower case string issue.

Macro expect parameter to be in lower case.

For example, consider below example that throws SemanticException error.

CREATE TEMPORARY MACRO isNumber(inputString string)
CASE WHEN CAST(inputString AS INT) IS NULL THEN false else true
END
;

Error: Error while compiling statement: FAILED: SemanticException Expected columns [inputstring] but found [inputString] (state=42000,code=40000)

Note that, the error is because of upper case ‘S’ in ‘inputString’ parameter and in an expression. Change it to lower case and it will work perfectly.

CREATE TEMPORARY MACRO isNumber(inputstring string)
CASE WHEN CAST(inputstring AS INT) IS NULL THEN false else true
END
;