Hive CLI Commands 1

In our previous posts, we have seen about Hive Overview and Hive Architecture and now we will discuss about the default service in hive, Hive Command Line Interface and Hive CLI Commands.

Ways to Interact with Hive

  • CLI, command-line interface .
  • Karmasphere ( ) (commercial product),
  • Cloudera’s open source Hue (https://git ),
  • A new “Hive-as-a-service" offering from Qubole (
  • A simple web interface called Hive web interface (HWI), and
  • programmatic access through JDBC, ODBC, and a Thrift server

Hive CLI Commands

Hive CLI (Command Line Interface) , which is nothing but Hive Shell is the default service in Hive and it is the most common way of interacting with Hive. We can run both batch and Interactive shell commands via CLI service which we will cover in the following sections.

We can get the list of commands/options allowed on Hive CLI with $ hive –service cli –help command from terminal. Below are the Options/arguments allowed for CLI Service. Examples for these options are provided below the table.

Argument Description
-d,–define <key=value> Defining new variables for Hive Session.
–database <databasename> Specify the database to use in Hive Session
-e <quoted-query-string> Running a Hive Query from the command line.
-f <filename> Execute the hive queries from file
-h <hostname> Connecting to Hive Server on remote host
-p <port> Connecting to Hive Server on port number
–hiveconf <property=value> Setting Configuration Property for current Hive Session
–hivevar <key=value> Same as –define argument
-i <filename> Initialization of Hive Session from an SQL properties file
-S,–silent Silent mode in interactive shell, suppresses log messages
-v,–verbose Verbose mode (prints executed SQL to the console)

We will provide these options or arguments at the time of starting Hive CLI service itself.

For Example, $ hive -d flag=N

Types of Hive Variables

Here we define variables or properties with –define, –hivevar and –hiveconf arguments. In Hive there can be four types of variables or properties. They are

  • env : Environment variables defined by the shell. These are read only in Hive session
  • hivevar : User-defined custom variables and these can be overridden with set command in hive session/shell.
  • hiveconf : Hive-specific configuration properties
  • system : Configuration properties defined by Java.

Examples of using these options

As shown in the table, both –define and –hivevar arguments are same and used for defining hive variables.

Scenario 1: — define or –hivevar Options

Lets define a hive variable address with value ‘country, state, city, post’ so that whenever we need to pull all these columns from a table we can simply use the address variable in our HiveQL queries instead of writing all these columns.

But after some time during the session, if we need to know what are the values of the defined variables for that hive session, we can use set command in Hive Shell. We will discuss more about this in the following sections.

Scenario 2: — database Option

When we have multiple databases in Hive and for current session we need to use a particular database then we can set the database with this argument as shown below or we can try use database command in hive shell.

Scenario 3: -S, -e Options, Environment variables & Redirecting Output to File

Lets run HiveQL commands in batch mode, or single shot commands, and make use of hive variables and redirect the output to a file on local FS. Here -S, silent will suppress the log messages like (OK, Time Taken …lines) from the output.

Lets create VAR1 environment variable and try to access that variable value in Hive Query.

Hive Single Shot Commands in Silent Mode We can see the cat command output to verify the results.

Scenario 4: Connecting Remote Hive Server

By Using -h and -p options to hive command we login into remote Hive server as shown below. It gives the more flexibility to work on production Hive server from Hive client in Development environment, instead of logging directly into Hive Server Machine for accessing hive tables in production.

Scenario 5: Running Queries from file

When we have our HiveQL written in separate file then we can perform the batch execution of HiveQL commands. Here the file can be present on Local File System or HDFS. Below are the examples of running queries from file.

Local FS: