Running a Hive Script

Step 1: 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.

hduser@Soumitra-PC:~$ sudo gedit input.txt

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

Step 2: To write the Hive Script the file should be saved with .sql extension. In this script,  a table will be created, described and data will be loaded and retrieved from the table.

hduser@Soumitra-PC:~$ sudo gedit hivescript.sql

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

Now, we write the below lines inside the hivescript.sql file:

create table employees ( id INT, name STRING, sal DOUBLE ) row format delimited fields terminated by ',';

describe employees;

load data local inpath '/home/hduser/input.txt' into table employees;

select * from employees;

Significance of each line explained below:

Line 1 -> Creating the Table in Hive:

Command: create table employees ( id INT, name STRING, sal DOUBLE ) row format delimited fields terminated by ',';

Here, employees is the table name and { id, name, sal} 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.

Line 2 -> Describing the Table:

Command: describe employees;

Line 3 -> Loading the Data into the Table.

Command : load data local inpath '/home/hduser/input.txt' into table employees;

To load the input data into the table that we have created earlier in input.txt file.

Line 4 -> Retrieving the Data:

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 hivescript.sql  can now be saved.

Step 3: Running the Hive Script

The following is the command to run the Hive script:

hduser@Soumitra-PC:~$ hive –f /home/hduser/hivescript.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 Hadoop.

References

https://www.edureka.co/blog/how-to-run-hive-scripts/

Document prepared by Mr. Soumitra Ghosh

Assistant Professor, Information Technology,

C.V.Raman College of Engineering, Bhubaneswar

Contact: soumitraghosh@cvrce.edu.in