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
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
In 2.2 section that is From HDFS to Hive tables the query that given is wrong. it should not contain Keyword "LOCAL".
ReplyDeleteLoad data inpath '/data/empnew.csv' into table emp
Thank Jeffin, it is Updated.
DeleteGreat Article Artificial Intelligence Projects
DeleteProject Center in Chennai
JavaScript Training in Chennai
JavaScript Training in Chennai
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..............
ReplyDeleteselect name,salary from emp;
DeleteHi,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?
ReplyDeletehi 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 ,
ReplyDeleteif u have any specific doc for HAdoop integration with corporate AD and other open source tool please update here
thanks and regards
sonali
Hi, when i am trying to take the full path. Its throwing below error.
ReplyDeletePath 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.
Aivivu chuyên vé máy bay, tham khảo
ReplyDeleteCác chuyến bay từ Incheon về Hà Nội hôm nay
mua vé máy bay hà nội đi sài gòn
giá vé máy bay đi hà nội vietnam airline
hà nội nha trang
vé máy bay đi Mỹ giá rẻ 2021
instead of create and load data simply create external table based on location its easy and recommended approach
ReplyDeleteThanks & Regards
Venu
bigdata hadoop training in Hyderabad