Data is not getting written in sorted format on target oracle table through SPARK

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Data is not getting written in sorted format on target oracle table through SPARK

abhijeet bedagkar
Hi,

I have a table in hive with below schema
emp_id:int
emp_name:string

I have created data frame from above hive table

df = sql_context.sql('SELECT * FROM employee ORDER by emp_id')
df.show()

After above code is run I see that data is sorted properly on emp_id

After this I am trying to write the data to Oracle table through below code
df.write.jdbc(url=url, table='target_table', properties=properties,  mode="overwrite")

When I see the Oracle table I see that ordering is not preserved and data is populated in random order

As per my understanding, This is happening because of multiple executor processes running at the same time on every data partitions and sorting applied through query is been applied on specific partition and when multiple processes writing data to Oracle at the same time the result table ordering is distorted

I further tried to repartition the data to just one partition(Which is not ideal solution) and post writing the data to oracle the sorting worked properly

Is there any way to write sorted data to RDBMS from SPARK

Thanks,
Abhijeet
Reply | Threaded
Open this post in threaded view
|

Re: Data is not getting written in sorted format on target oracle table through SPARK

JayeshLalwani

No. There is no way to control the order except for the option that you have already tried (repartition =1). When you are inserting in parallel from multiple nodes, then the order of inserts cannot be guaranteed. That is because of the very nature of doing things in parallel. The only way order can be controlled is by running inserts sequentially on one node, which is what repartition=1 does

 

Why does insertion order matter? Aren’t RDBMS databases designed so insertion order shouldn’t matter?

 

From: abhijeet bedagkar <[hidden email]>
Date: Wednesday, May 30, 2018 at 5:29 AM
To: "[hidden email]" <[hidden email]>
Subject: Data is not getting written in sorted format on target oracle table through SPARK

 

Hi,

 

I have a table in hive with below schema

emp_id:int

emp_name:string

 

I have created data frame from above hive table

 

df = sql_context.sql('SELECT * FROM employee ORDER by emp_id')

df.show()

 

After above code is run I see that data is sorted properly on emp_id

 

After this I am trying to write the data to Oracle table through below code

df.write.jdbc(url=url, table='target_table', properties=properties,  mode="overwrite")

 

When I see the Oracle table I see that ordering is not preserved and data is populated in random order

 

As per my understanding, This is happening because of multiple executor processes running at the same time on every data partitions and sorting applied through query is been applied on specific partition and when multiple processes writing data to Oracle at the same time the result table ordering is distorted

 

I further tried to repartition the data to just one partition(Which is not ideal solution) and post writing the data to oracle the sorting worked properly

 

Is there any way to write sorted data to RDBMS from SPARK

 

Thanks,

Abhijeet



The information contained in this e-mail is confidential and/or proprietary to Capital One and/or its affiliates and may only be used solely in performance of work or services for Capital One. The information transmitted herewith is intended only for use by the individual or entity to which it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any review, retransmission, dissemination, distribution, copying or other use of, or taking of any action in reliance upon this information is strictly prohibited. If you have received this communication in error, please contact the sender and delete the material from your computer.