Hive Single Table Multi-Table Insertion

Before getting into hive commands along with Hive Single Table Multi-Table Insertion, we should know these points,

1. To open the Hive shell we should use the command “hive” in the terminal.

2. After getting into hive shell, firstly need to create database, then use the database.

3. Then Start to create the hive table, it is similar to RDBMS table (internal and external table creation is explained in hive commands topic)

4. In hive table creation we use,

“row format delimited” this line is telling Hive file to contain one row per line.

“fields terminated by ‘,’ or ‘\t’  ” this line is telling Hive file that each column is separated either by coma or tab.

“ location” Indicates that a specific distributed file system directory must be used to store data files.

Let us discuss about Single table and multi-table insertion

1. Single table insertion

Below steps involved in single table insertion.

  • Create new table called “target”
  • From the previous table “employee” load the data into the newly created table “target”.
  • Both the tables should be of same database.

Example

hive>use beyond corner;

hive>create table target(id string,name string)

>row format delimited

>fields terminated by ’,’;

Insertion

hive>insert overwrite table target

>select id,name from employee;

 Queries

hive>select * from target;

hive>select count(*) from target;

hive>alter table  target rename to target1;

2. Multi-table insertion

Below steps involved in Multi- table insertion.

  • Create three new tables called target1, target2, target3
  • From the previous table “employee” load data to the newly created three tables.
  • All the tables should be of same database.

Example

hive>create table target1(id int,name string)

>row format delimited

>fields terminated by ’,’;

hive>create table target2(id int,city string)

>row format delimited

>fields terminated by ’,’;

hive>create table target3(id int,age int)

>row format delimited

>fields terminated by ’,’;

Insertion

hive> from employee

> insert overwrite table target1

> select id,name

>insert overwrite table target2

>select id,city

>insert overwrite table target3

>select id,age;

Queries

hive>select * from target1;

>select * from target2;

>select * from target3;

Conclusion

From the above topic we can conclude that from the existing table we can perform single and multi table insertion both the tables should be in the same database.