Oracle INSERT ALL Alternative in Hive/Spark SQL

Oracle database is one of widely used relational databases. It supports many syntax that are not available in any other transaction databases. One of such command is INSERT ALL. The INSERT ALL is used to insert computed records into multiple tables based on conditions. In this article, we will check what is Oracle INSERT ALL alternative in Hive and Spark SQL.

Oracle INSERT ALL alternative in Hive/Spark SQL

When migrating Oracle scripts to Apache Hive and Spark SQL, you will notice Hive and Spark SQL does not support many Oracle functionalities. One of such syntax is INSERT ALL. Before going into an INSERT ALL equivalent method in Hive and Spark SQL, let us check How INSERT ALL command works?

How Oracle INSERT ALL Command works?

You can use INSERT ALL command to insert multiple rows into one or more tables. In a multi-table insert, you insert computed rows derived from the rows returned from the evaluation of a subquery into one or more tables. In addition, it uses WHEN condition to insert into multiple tables.

Following is the example of Oracle INSERT ALL Command.

insert all 
when col1 = 1 then
into insert_all_test1 (col1, col2, col3) values (col1, col2,col3)
when col1 = 2 then
into insert_all_test2 (col1, col2, col3) values (col1, col2,col3)
else
into insert_all_test3 (col1, col2, col3) values (col1, col2,col3)
with cte as 
(select * from insert_all_tab)
select col1, col2, col3 
from cte;

You can specify the conditional_insert_clause to perform a conditional multitable insert. Oracle Database filters each insert_into_clause through the corresponding WHEN condition, which determines whether that insert_into_clause is executed.

Oracle INSERT ALL Equivalent in Hive/Spark SQL

Now, we know how the INSERT ALL statement works. Let us create equivalent Hive and Spark SQL statement.

The following steps allows you to create Oracle INSERT ALL Command equivalent in Hive and Spark SQL.

Create Temporary Table for Subquery

The INSERT ALL command is always associated with subquery. You can create a temporary table for it and you can use same in subsequent steps.

For example,

create table cte as
select * from insert_all_tab;

In other words, you can create a temporary table for cte subquery.

Create Separate INSERT Statement for each WHEN Condition

In this step, you can create a mulitple insert statement for all tables. In other words, you break INSERT ALL command into multiple steps.

For example,

-- Insert for condition 1
insert into insert_all_test1 
select * from cte
where col1 = 1;

-- Insert for condition 2
insert into insert_all_test2 
select * from cte
where col1 = 2;

-- Insert for else part.
insert into insert_all_test3 
select * from cte
where col1 not in (1,2);

This method will work on both Hadoop Hive and Apache Spark SQL. To sum up, it is fairly easy to break INSERT ALL command into multiple working chunks.