Multi Insert in Apache Hive

Multiple records in the same table is a well-known technique. But in this post, we’ll look at inserting into multiple tables using a single expression. This isn’t a new concept, although it isn’t commonly known.

This is a simple method for separating data from a table into numerous tables.

Consider the below datasets for the practice.

Customers-Agents-Suppliers Data:
ID, Name, City, EmailID, Phone, Work

100001, Steve, Baltimore,, 12220010, Customer
100002, Frank Muller, Philadelphia,, 12220011, Customer
100003, Melanie Pike,Pasadena,, 12220012, Customer
100004, Tony Curtis, Houston,, 12220013, Customer
200001, John Smith,Pasadena,, 2200021, Agent
200002, Jordan Katharine,Philadelphia,, 2200022, Agent
200003, James William, Houston,, 2200023, Agent
200004, Bella, Baltimore,, 2200024, Agent
300001, Scott, Pasadena,, 330001, Supplier
300002, Amanda Lee, Baltimore,, 330002, Supplier
300003, Sara Dyer,Houston,, 330003, Supplier
300004, Connie Angland, Philadelphia,, 330004, Supplier

Hive> FROM PersonsData
INSERT INTO tbCustomers
SELECT  ID, Name, City, EmailID, Phone, Work WHERE work=’Customer’

SELECT  ID, Name, City, EmailID, Phone, Work WHERE work=’Agent’

INSERT INTO tbSuppliers
SELECT  ID, Name, City, EmailID, Phone, Work WHERE work=’Supplier’;

The query above will assist in the separation of data from a composite data source into its separate tables. Since it was provided at the start, no “From” clause is necessary in each SELECT statement.

Hope you find this article helpful.

Please subscribe for more interesting updates.

One comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s