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

[abhijeet bedagkar <qa...@gmail.com>]

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

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

["Lalwani, Jayesh" <Ja...@capitalone.com>]

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 <qa...@gmail.com>
Date: Wednesday, May 30, 2018 at 5:29 AM
To: "user@spark.apache.org" <us...@spark.apache.org>
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.