Search This Blog

Loading data into Hive Table

We can load data into hive table in three ways.Two of them are DML operations of Hive.Third way is using hdfs  command.If we have data in RDBMS system like Oracle,Mysql,DB2 or SQLServer we can import it using SQOOP tool.That Part we are not discussing now.

To Practice below commands ,create a table called Employee with below data

eno,ename,salary,dno

11,Balu,100000,15
12,Radha,120000,25
13,Nitya,150000,15
14,Sai Nirupam,120000,35



1. Using Insert Command

We can load data into a table using Insert  command in two ways.One Using Values command and other is using queries.

     1.1 Using Values
       Using Values command ,we can append more rows of data into        existing table.
       for example ,to existing above employee table we can add                extra row 15,Bala,150000,35 like below

Insert into table employee values (15,'Bala',150000,35)

After this You can run a select command to see newly added row.

     1.2 Using Queries




You can also upload query output into a table.for example Assume you have emp table,from this, you can upload data into employee table like below

Insert into table employee Select * from emp where dno=45;

After this also You can fire select query to see uploaded rows.

2.Using Load



You can load data into a hive table using Load statement in two ways.
One is from local file system to hive table and other is from  HDFS to Hive table.

  2.1 From LFS to Hive Table

Assume we have data like below in LFS file called /data/empnew.csv.
15,Bala,150000,35 
Now We can use load statement like below.

Load data local inpath '/data/empnew.csv' into table emp

2.2 From HDFS to Hive Table

if we do not use local keyword ,it assumes it as a HDFS Path.

Load data local inpath '/data/empnew.csv' into table emp

After these two statements you can fire a select query to see loaded rows into table.

3. Using HDFS command



Assume You have data  in a local file,You can simply upload data using hdfs commands.

run describe command to get the location of table like below.

describe formatted employee;

It will display Location of the table ,Assume You got location as /data/employee, you can upload data into table by using one of below commands.

hadoop fs -put /path/to/localfile /Data/employee

hadoop fs -copyFromLocal /path/to/localfile /Data/employee

hadoop fs -moveFromLocal /path/to/localfile /Data/employee