Categories
hive

How to load data into a hive table

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 .

  1. Internal table or Managed table
  2. 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)

Leave a Reply

Your email address will not be published. Required fields are marked *