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  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








21 comments:

  1. In 2.2 section that is From HDFS to Hive tables the query that given is wrong. it should not contain Keyword "LOCAL".

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

    ReplyDelete
  2. if we load emp data into table as line string,from emp if want to extract to only name and salary from emp,how to extract..............

    ReplyDelete
  3. Hi,I have a .dat file and I do not know how many columns it has, how do I load the file in Hive without Hive Table?

    ReplyDelete
  4. hi Balaswamy, thanks for creating such informative blog for us , i am referring your blogs since long , as an hadoop admin this is very much helpful for me ,

    if u have any specific doc for HAdoop integration with corporate AD and other open source tool please update here

    thanks and regards
    sonali

    ReplyDelete
  5. Hi, when i am trying to take the full path. Its throwing below error.
    Path is not legal \'"D:/dn/file/DEV/test/final_file.csv"\': Source file system should be "file" if "local" is specified.
    Can you please help me in keeping the whole path.

    ReplyDelete
  6. instead of create and load data simply create external table based on location its easy and recommended approach
    Thanks & Regards
    Venu
    bigdata hadoop training in Hyderabad

    ReplyDelete
  7. What an interesting post. This is so useful. www.carypavers.com

    ReplyDelete
  8. Good article about hadoop technology You may like Updated content at Hadoop Quiz all about hadoop

    ReplyDelete