How to use the command 'hive' (with examples)

The ‘hive’ command is a CLI tool for Apache Hive, which is a data warehouse infrastructure built on top of Hadoop. It provides a simple and easy-to-use interactive shell to run HiveQL queries and interact with Apache Hive.

Use case 1: Start a Hive interactive shell

Code:

hive

Motivation: Starting a Hive interactive shell allows users to execute HiveQL queries directly within the shell. This is useful for ad-hoc data analysis and exploration.

Explanation: The ‘hive’ command without any arguments starts the Hive interactive shell.

Example output:

Hive>

Use case 2: Run HiveQL

Code:

hive -e "hiveql_query"

Motivation: Running HiveQL queries directly from the command line can be useful for automation or executing specific queries.

Explanation: The ‘-e’ flag is used to provide the HiveQL query as a string.

Example output:

OK
+---------+-------------+----------+
| user_id |    name     | location |
+---------+-------------+----------+
|    1    | John Smith  |   USA    |
|    2    | Jane Doe    |   UK     |
+---------+-------------+----------+
2 rows selected (0.456 seconds)

Use case 3: Run a HiveQL file with variable substitution

Code:

hive --define key=value -f path/to/file.sql

Motivation: Running a HiveQL file with variable substitution allows users to pass variables to the Hive query, which can be useful for dynamic queries or reusing the same query with different inputs.

Explanation: The ‘–define’ flag is used to define variables that can be referenced in the HiveQL file. The ‘-f’ flag is used to specify the path to the HiveQL file.

Example output:

OK
+---------+-------------+----------+
| user_id |    name     | location |
+---------+-------------+----------+
|    1    | John Smith  |   USA    |
|    2    | Jane Doe    |   UK     |
+---------+-------------+----------+
2 rows selected (0.456 seconds)

Use case 4: Run a HiveQL with HiveConfig

Code:

hive --hiveconf conf_name=conf_value

Motivation: Running a HiveQL query with HiveConfig allows users to override specific Hive configurations for that specific query.

Explanation: The ‘–hiveconf’ flag is used to specify a specific configuration name-value pair.

Example output:

OK
+---------+-------------+----------+
| user_id |    name     | location |
+---------+-------------+----------+
|    1    | John Smith  |   USA    |
|    2    | Jane Doe    |   UK     |
+---------+-------------+----------+
2 rows selected (0.456 seconds)

Conclusion:

The ‘hive’ command provides a versatile CLI tool to interact with Apache Hive. It allows users to run HiveQL queries, pass variables, and override specific configuration values. Whether it’s for ad-hoc data analysis, automation, or running complex queries, the ‘hive’ command is a powerful tool in the Apache Hive ecosystem.