By | August 5, 2015

Hive Tutorial – Hive multiple table insert – Insert data into multiple hive tables

In this tutorial we will see how hive query inserts record from one table into two or more tables

Let’s get started

Syntax – Multi table insert into hive

FROM table_name

INSERT OVERWRITE TABLE table_one SELECT table_name.column_one,table_name.column_two

INSERT OVERWRITE TABLE table_two SELECT table_name.column_two WHERE table_name.column_one == 'something'

You can even write more complex logic this is just a basic example so for more information on this you can always refer Hive documentation on official site

Example –

I have tested this with Hive 0.13 version

Consider default database available in hive and one sample_07 table

select * from sample_07

Let’s create three new tables

create table toodey1(code string,salary int);

create table toodey2(code string,salary string);

create table toodey3(total_emp int,salary int)

If you see the syntax you can even fire two queries at the same time using ‘;’ separator and above syntax will create 3 tables at once

Now let’s write data into this three tables from sample_07 table
sample_07 is default table available in hive with data so we are going to refer this now

Example –

FROM default.sample_07

INSERT OVERWRITE TABLE toodey1 SELECT sample_07.code,sample_07.salary

INSERT OVERWRITE TABLE toodey2 SELECT sample_07.code,sample_07.salary WHERE sample_07.salary >= 50000

INSERT OVERWRITE TABLE toodey3 SELECT sample_07.total_emp,sample_07.salary WHERE sample_07.salary <= 50000


After execution you will see data populated in all tables

select * from toodey1;


select * from toodey2;


select * from toodey3;


Share this knowledge ! Join us on Facebook ! Now Whatsapp sharing is supportable ! TooDey Inc.

Leave a Reply

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