How to Run Hive Scripts?

Being a Data Warehousing package built on top of Hadoop, Apache Hive is increasingly getting used for data analysis, data mining and predictive modeling. Organizations are looking for professionals with a firm hold on Hive & Hadoop skills. In this post, let’s look at how to run Hive Scripts. In general, we use the scripts to execute a set of statements at once. Hive Scripts are used pretty much in the same way. It will reduce the time and effort we put on to writing and executing each command manually.

Hive Scripts are supported in the Hive 0.10.0 and above versions. As Hive 0.90 version is installed in CDH3, we cannot run Hive Scripts in CDH3. You can try the below steps in CDH4 as it has Hive 0.10.0 version installed in them. Are you aware of how to create a Hive script? If no, click here to gain more clarification.

Now, let us see how to write the scripts in Hive and run them in CDH4:

Step 1: Writing a Hive script.

To write the Hive Script the file should be saved with .sql extension. Open a terminal in your Cloudera CDH4 distribution and give the following command to create a Hive Script.
Command: sudo gedit sample.sql

On executing the above command, it will open the file with the list of all the Hive commands that need to be executed.

In this script,  a table will be created, described and data will be loaded and retrieved from the table.

1. Creating the Table in Hive:

Command: create table product ( productid: int, productname: string, price: float, category: string) rows format delimited fields terminated by ‘,’ ;

Here, product is the table name and { productid, productname, price, category} are the columns of this table.

Fields terminated by ‘,’ indicate that the columns in the input file are separated by the symbol ‘,’.

By default the records in the input file are separated by a new line.

2. Describing the Table:

Command: describe product;

3. Loading the Data into the Table.

To load the data into the table first we need to create an input file which contains the records that need to be inserted in the table.

Let us create an input file.

Command: sudo gedit input.txt

Edit the contents in the file as shown in the figure.

4. Retrieving the Data:

To retrieve the data, the select command is used.

Command: Select * from product;

The above command is used to retrieve the value of all the columns present in the table. The script should be like as it is shown in the below image.

Now, we are done with writing the Hive script.  The file sample.sql  can now be saved.

Step 2: Running the Hive Script

The following is the command to run the Hive script:

Command: hive –f /home/cloudera/sample.sql

While executing the script, make sure that the entire path of the location of the Script file is present.

We can see that all the commands are executed successfully.

This is how Hive scripts are run and executed in CDH4.

Hive is a critical component of Hadoop and your expertise in Hive can land you top-paying Hadoop jobs! Edureka has a specially curated Hadoop course that helps you master concepts such as MapReduce, Yarn, Pig, Hive, HBase, Oozie, Flume and Sqoop. Click on the button below to get started.

Got a question for us? Please mention them in the comments section and we will get back to you.

Related Posts:

Get Started with Hadoop

Hive Commands

Hive Data Models

5 Reasons to Learn Hadoop