HiveQL - Macro

A HiveQL Macro is a way to define a reusable logic fragment (a "mini-function") within a Hive session using standard Hive SQL expressions.

Think of it as a SQL-based template. Instead of writing a complex calculation or a long CASE statement every time you need it, you define it once as a macro and then call it like a regular function.

---

1. Basic Syntax

You create a macro using the CREATE TEMPORARY MACRO command.

CREATE TEMPORARY MACRO calculate_tax(price DOUBLE, rate DOUBLE) 
price * rate;

-- Using the macro in a query
SELECT item_id, calculate_tax(price, 0.15) FROM sales;

2. Why use Macros?

• Simplicity: They are written in pure SQL. You don't need to write Java code (like you would for a UDF/User Defined Function) or compile a JAR file.

• No Deployment Overhead: Since they are "Temporary," they live only in your session. You don't need administrator permissions to add them to the Hive permanent function library.

• Consistency: If your business logic for "Profit" changes, you only update the macro definition once instead of hunting down 50 different scripts.

---

3. Macro vs. UDF (User Defined Function)

Feature	Hive Macro	Hive UDF
Language	Hive SQL	Java (usually)
Complexity	Simple logic only	Complex logic/External libraries
Setup	Zero (just run the SQL)	High (Coding, Compiling, JAR upload)
Persistence	Temporary only (Session-scoped)	Can be Permanent or Temporary
Performance	Very fast (Inline expansion)	Slower (Overhead of calling Java code)

---

4. Advanced Example: Complex Business Logic

You can use nested logic and multiple parameters in a macro.

CREATE TEMPORARY MACRO categorize_age(age INT)
CASE 
    WHEN age < 18 THEN 'Minor'
    WHEN age >= 18 AND age < 65 THEN 'Adult'
    ELSE 'Senior'
END;

-- Calling the macro
SELECT name, categorize_age(user_age) FROM users;

5. Limitations to Watch Out For

• Session Scope: Macros are not saved in the Metastore. When you close Beeline, the macro is gone.

• No Subqueries: You cannot use SELECT statements inside a macro. It can only contain expressions (math, string manipulation, CASE statements, etc.).

• No Permanent Macros: There is currently no CREATE PERMANENT MACRO in Hive. You must define them at the start of your script or in your .hiverc initialization file.

---

Tip: If you have a set of macros you use every day, you can save them in a file (e.g., my_macros.hql) and run them at the start of your Beeline session using:

SOURCE /path/to/my_macros.hql;