Open In App

Hive – One Shot Commands

Improve
Improve
Like Article
Like
Save
Share
Report

Hive comes with various “One Shot” commands that a user can use through Hive CLI(Command Line Interface) without entering the Hive shell to execute one or more than one query separated by a semicolon. Hive CLI offers multiple options that provide various functionalities to the user. We will be discussing various modes and their features and how to use them in the Hive. To perform the below implementation make sure Hive is already started.

Follow the below steps to launch the hive

Step 1: Start all your Hadoop Daemon

start-dfs.sh                    # this will start namenode, datanode and secondary namenode
start-yarn.sh                   # this will start node manager and resource manager  
jps                             # To check running daemons

Step 2: Launch Hive

hive

Let’s discuss the hive one-shot commands

-e option/mode

Whenever a user requirement is to run single or multiple queries (separated by semicolon) on Hive CLI with terminating the Hive shell as soon as the query got fired one can use the -e option with Hive to enable this functionality. Perform the below exercise to understand its implementation.

Create Table

CREATE TABLE IF NOT EXISTS student_details(
name STRING,
marks FLOAT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

Load Data To Table

The Data is available in CSV format

LOAD DATA LOCAL INPATH '/home/dikshant/Documents/data.csv' INTO TABLE student_details;

Now come out of the hive shell by pressing ‘ctrl+d‘.

Use below hive one-shot command on CLI(Command Line Interface) if your requirement is to terminate the hive shell as soon as the query shows its result.

Syntax:

hive -e "<command>"; hive -e "<command>"; hive -e "<command>";...........

Command

hive -e "select * from student_details";

In the below output we can observe that the hive shell automatically gets terminated as soon as the query execution is finished. 

Similarly, we can use multiple queries separated with semicolons as shown below.

hive -e "select name from student_details"; hive -e "select * from student_details LIMIT 1";

The output of the above query:

-S option/mode

The -S option allows the user to store the result of a hive CLI query in a file. The -S removes all the unnecessary details that we see on CLI like Time taken, The Hive session information, or any other information. The feature is very much useful when we want to store the clean result of a query in a file.

Syntax:

hive -S -e "<command>" > /path/file-name;

Whatever file name we provide next to the path will be automatically created by the hive and store our query result. Make sure the path you provide is of your local system not of HDFS(Hadoop Distributed File System) because Hive writes its output to the ‘Stdout’ and the shell always redirects it to the local path.

Command:

hive -S -e "select * from student_details" > /home/dikshant/Desktop/query_result.csv;

We can use the hive SET command to either override an existing hive property(can not override environmental variables) or display the configuration property of the hive containing system and environmental variables. The separated property can also be checked with the SET command in the hive. 

Command:

hive -S -e "SET" | grep warehouse;

The above command will display the internal warehouse location for Internal or Managed tables.


Last Updated : 22 Jan, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads