Hive CLI Commands 2


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 (http://karmasphere.com ) (commercial product),
  • Cloudera’s open source Hue (https://git hub.com/cloudera/hue ),
  • A new “Hive-as-a-service” offering from Qubole (http://qubole.com)
  • 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 ModeWe 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:

We can queries from file even when, we are already in Hive shell with below command.

HDFS:

Hive Query Execution from HDFS file

Scenario 6: .hiverc file Initialization Script

We can initialize a Hive before entering interactive mode with -i option. If the CLI is invoked without the -i option, then Hive will attempt to load $HIVE_HOME/bin/.hiverc and $HOME/.hiverc as initialization files

The typical properties that can be placed in .hiverc file are like:

Example:

Hive Batch Mode Commands

As discussed in the above sections Hive supports below two types of batch mode commands.

  • hive -e “<query-string>”
  • hive -f <filepath>  – executes one or more SQL queries from a file
Comments in Hive Scripts

While writing Batch scripts for Hive, we can embed comments in the scripts file by prefixing with — before comment line as shown below.

Refer Next post for Interactive shell Commands.

References

Apache Hive Manual –> https://cwiki.apache.org/confluence/display/Hive/LanguageManual


Profile photo of Siva

About Siva

Senior Hadoop developer with 4 years of experience in designing and architecture solutions for the Big Data domain and has been involved with several complex engagements. Technical strengths include Hadoop, YARN, Mapreduce, Hive, Sqoop, Flume, Pig, HBase, Phoenix, Oozie, Falcon, Kafka, Storm, Spark, MySQL and Java.


Leave a comment

Your email address will not be published. Required fields are marked *

2 thoughts on “Hive CLI Commands


Review Comments
default gravatar

I am a plsql developer. Intrested to move into bigdata.

Neetika Singh ITA

.