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
Let’s discuss the hive one-shot commands
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 IF NOT EXISTS student_details( name STRING, marks FLOAT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
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.
hive -e "<command>"; hive -e "<command>"; hive -e "<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:
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.
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.
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.
hive -S -e "SET" | grep warehouse;
The above command will display the internal warehouse location for Internal or Managed tables.