Complete Guide to Variable Setting and Usage in Hive Scripts

Overview of Hive Variable System

Hive, as a data warehouse tool based on Hadoop, provides a flexible variable mechanism to support dynamic SQL queries. Unlike traditional SQL, Hive's variable system is more comprehensive, supporting multiple types of variable definitions and usage methods.

Variable Types and Syntax

Hive primarily supports four variable types: hiveconf, hivevar, env, and system. Each type has specific usage scenarios and syntax rules.

hiveconf Variables

hiveconf are Hive configuration variables, commonly used to store parameter values related to queries. The syntax for setting hiveconf variables in Hive CLI is:

set CURRENT_DATE='2012-09-16';

When using hiveconf variables in queries, the format ${hiveconf:variable_name} must be used:

select * from foo where day >= ${hiveconf:CURRENT_DATE}

Command Line Parameter Passing

In addition to setting variables in CLI, hiveconf variables can also be passed via command line parameters:

% hive -hiveconf CURRENT_DATE='2012-09-16' -f test.hql

This method is particularly suitable for scripted environments, enabling parameterized query execution.

Other Variable Types

Environment Variables

Hive can directly reference system environment variables using the syntax ${env:variable_name}. For example, to reference the current user:

select * from table where user = ${env:USER}

System Variables

System variables use the syntax ${system:variable_name} and can access Java system properties.

Advanced Usage of hivevar Variables

hivevar variables provide a more flexible way to manage variables, supporting differentiation between global and local scopes.

Variable Definition and Usage

Define hivevar variables in a setup.hql file:

set hivevar:tablename=mytable;

Load and execute the script in Hive CLI:

source /path/to/setup.hql;

There are two ways to use variables:

select * from ${tablename}

Or explicitly specify the variable type:

select * from ${hivevar:tablename}

Scope Management

An important feature of hivevar variables is support for scope management. When setting a local variable:

set tablename=newtable;

Using ${tablename} will reference the local variable value 'newtable', while ${hivevar:tablename} still retains the original 'mytable' value. This mechanism allows flexible control over variable visibility in complex scripts.

Variable Viewing and Management

To view all available variables in Hive, execute from the command line:

% hive -e 'set;'

Or run in Hive CLI:

set;

This command lists all hiveconf, hivevar, env, and system variables along with their current values.

Best Practice Recommendations

In practical development, it is recommended to choose the appropriate variable type based on the usage scenario:

• Use hiveconf variables for configuration parameters
• Use hivevar variables for script-level shared variables
• Use env variables when referencing system environments is needed
• Leverage the source command and -i option for modular script management

By properly utilizing Hive's variable system, the maintainability and reusability of SQL scripts can be significantly enhanced.