In this post , we will learn how to load data into a hive table .
Types of table in hive
The following are the two types of tables in the hive .
- Internal table or Managed table
- External table
In this post, let us discuss the internal tables and their loading ways.
What are the ways to load data into a table ?
There are three ways to load data into a hive table.
- Loading data from a file
- Inserting values into a table
- Loading data from some other table
1 . Loading data from a file
We can refer to the path of the file as below. More importantly, we need to specify the details of the file like delimiter while creating the table itself.
Please refer to the below link to understand it clearly.
https://beginnersbug.com/how-to-create-a-table-in-hive/
hive> load data local inpath '/tmp/temp.csv' into table truck;
Loading data to table vehicle_details.truck
OK
Time taken: 0.846 seconds
hive> select * from truck limit 5;
OK
1901 50 bangalore
1903 48 kolkata
1900 26 pune
1902 12 darjeling
1903 23 delhi
Time taken: 0.954 seconds, Fetched: 5 row(s)
2 . Inserting values into a table
We shall insert values into the table manually like below.
We can even add multiple records into the table in a similar way.
hive> insert into table truck1 values ('2020',65,'Chennai');
Let us verify whether the inserted data looks good.
hive> select * from truck1 where year='2020';
OK
2020 65 Chennai
Time taken: 0.161 seconds, Fetched: 1 row(s)
3 . Loading data from some other table
We can create one more table with the following command.
hive> create table if not exists truck1
> (year string , temp int , place string)
> row format delimited
> fields terminated by ','
> ;
OK
Time taken: 0.099 seconds
As below, we can select the data from the existing table and load it into the new table.
hive> insert into table truck1 select * from truck where temp < 50;
After loading, we could find the new table loaded with the selected data.
hive> select * from truck1 limit 5;
OK
1903 48 kolkata
1900 26 pune
1902 12 darjeling
1903 23 delhi
1902 25 delhi
Time taken: 0.145 seconds, Fetched: 5 row(s)