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.
|-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.
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.