You are viewing a plain text version of this content. The canonical link for it is here.
Posted to reviews@spark.apache.org by kevinyu98 <gi...@git.apache.org> on 2017/01/24 11:58:37 UTC
[GitHub] spark pull request #16692: [SPARK-19335] Introduce UPSERT feature to SPARK
GitHub user kevinyu98 opened a pull request:
https://github.com/apache/spark/pull/16692
[SPARK-19335] Introduce UPSERT feature to SPARK
## What changes were proposed in this pull request?
This PR proposes to add the UPSERT feature support into SPARK through DataFrameWriter's JDBC data source options.
For example:
If the mytable2 in mysql database have unique constraints on column c1, and the user wants to save the dataframe into the mysql database, it will fail with violation of unique constraints.
`val df = Seq((1,4)).toDF("c1","c2")`
`val url = "jdbc:mysql://9.30.167.220:3306/mydb"`
`df.write.mode(org.apache.spark.sql.SaveMode.Append)
.option("user","kevin").option("password","kevin").jdbc(url,"mytable2",new java.util.Properties())`
With this feature, the user can use this UPSERT options to write the dataframe into the mysql database table.
`df.write.mode(org.apache.spark.sql.SaveMode.Append)
.option(\u201cupsert\u201d,true).option(\u201cupsertUpdateColumn\u201d,\u201dc1\u201d).option("user","kevin").option("password","kevin").jdbc(url,"mytable2",new java.util.Properties())`
Here is the design doc.
[UPSERT DESIGN DOC](https://drive.google.com/open?id=1IoafDm78v7ATP-npKbTaw2_dTFiXplCd9NzEe8CBH6E)
## How was this patch tested?
Local test: run the test case from spark-shell and connect to MySQL and Postgresql database
Test case: add test cases in the existing test cases including docker integration suite
Please review http://spark.apache.org/contributing.html before opening a pull request.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/kevinyu98/spark upsert2
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/spark/pull/16692.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #16692
----
commit 3b44c5978bd44db986621d3e8511e9165b66926b
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-04-20T18:06:30Z
adding testcase
commit 18b4a31c687b264b50aa5f5a74455956911f738a
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-04-22T21:48:00Z
Merge remote-tracking branch 'upstream/master'
commit 4f4d1c8f2801b1e662304ab2b33351173e71b427
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-04-23T16:50:19Z
Merge remote-tracking branch 'upstream/master'
get latest code from upstream
commit f5f0cbed1eb5754c04c36933b374c3b3d2ae4f4e
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-04-23T22:20:53Z
Merge remote-tracking branch 'upstream/master'
adding trim characters support
commit d8b2edbd13ee9a4f057bca7dcb0c0940e8e867b8
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-04-25T20:24:33Z
Merge remote-tracking branch 'upstream/master'
get latest code for pr12646
commit 196b6c66b0d55232f427c860c0e7c6876c216a67
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-04-25T23:45:57Z
Merge remote-tracking branch 'upstream/master'
merge latest code
commit f37a01e005f3e27ae2be056462d6eb6730933ba5
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-04-27T14:15:06Z
Merge remote-tracking branch 'upstream/master'
merge upstream/master
commit bb5b01fd3abeea1b03315eccf26762fcc23f80c0
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-04-30T23:49:31Z
Merge remote-tracking branch 'upstream/master'
commit bde5820a181cf84e0879038ad8c4cebac63c1e24
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-05-04T03:52:31Z
Merge remote-tracking branch 'upstream/master'
commit 5f7cd96d495f065cd04e8e4cc58461843e45bc8d
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-05-10T21:14:50Z
Merge remote-tracking branch 'upstream/master'
commit 893a49af0bfd153ccb59ba50b63a232660e0eada
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-05-13T18:20:39Z
Merge remote-tracking branch 'upstream/master'
commit 4bbe1fd4a3ebd50338ccbe07dc5887fe289cd53d
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-05-17T21:58:14Z
Merge remote-tracking branch 'upstream/master'
commit b2dd795e23c36cbbd022f07a10c0cf21c85eb421
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-05-18T06:37:13Z
Merge remote-tracking branch 'upstream/master'
commit 8c3e5da458dbff397ed60fcb68f2a46d87ab7ba4
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-05-18T16:18:16Z
Merge remote-tracking branch 'upstream/master'
commit a0eaa408e847fbdc3ac5b26348588ee0a1e276c7
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-05-19T04:28:20Z
Merge remote-tracking branch 'upstream/master'
commit d03c940ed89795fa7fe1d1e9f511363b22cdf19d
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-05-19T21:24:33Z
Merge remote-tracking branch 'upstream/master'
commit d728d5e002082e571ac47292226eb8b2614f479f
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-05-24T20:32:57Z
Merge remote-tracking branch 'upstream/master'
commit ea104ddfbf7d180ed1bc53dd9a1005010264aa1f
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-05-25T22:52:57Z
Merge remote-tracking branch 'upstream/master'
commit 6ab1215b781ad0cccf1752f3a625b4e4e371c38e
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-05-27T17:18:46Z
Merge remote-tracking branch 'upstream/master'
commit 0c566533705331697eb1b287b30c8b16111f6fa2
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-06-01T06:48:57Z
Merge remote-tracking branch 'upstream/master'
commit d7a187490b31185d0a803cbbdeda67cb26c40056
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-06-01T22:55:17Z
Merge remote-tracking branch 'upstream/master'
commit 85d35002ce864d5ce6fd3be7215a868a8867caf9
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-06-02T14:08:30Z
Merge remote-tracking branch 'upstream/master'
commit c056f91036ec75d1e2c93f6f47ad842eb28a3e0b
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-06-03T06:06:51Z
Merge remote-tracking branch 'upstream/master'
commit 0b8189dd454897ae73bb3a5ffc245b2c65f6b226
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-06-03T23:33:39Z
Merge remote-tracking branch 'upstream/master'
commit c2ea31de654bd8c32ab0dd9a0362ee90b47c8756
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-06-06T21:04:09Z
Merge remote-tracking branch 'upstream/master'
commit a2d3056e148a1b38b1b2bf84eabf13962a452e36
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-06-08T02:52:43Z
Merge remote-tracking branch 'upstream/master'
commit 39e564887667b57ec15b87f9ee6c760fd1938a15
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-06-08T18:05:50Z
Merge remote-tracking branch 'upstream/master'
commit b9370a35acbc47d1ed30417d843284ac0cf1a6d5
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-07-25T18:41:03Z
Merge remote-tracking branch 'upstream/master'
commit 01224a4c133fa2b47b921ff9fff170ff22f5de44
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-08-03T05:21:29Z
Merge remote-tracking branch 'upstream/master'
commit d05d39a0e51d087ff2102a0685b66a85d0e5a984
Author: Kevin Yu <qy...@us.ibm.com>
Date: 2016-08-19T21:55:21Z
Merge remote-tracking branch 'upstream/master'
----
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16692: [SPARK-19335] Introduce UPSERT feature to SPARK
Posted by AydinChavez <gi...@git.apache.org>.
Github user AydinChavez commented on the issue:
https://github.com/apache/spark/pull/16692
Why was this PR closed? Such feature is quite needed - please proceed
(there are plenty of ppl on SO searching for such `SaveMode.Upsert `mode)
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request #16692: [SPARK-19335] Introduce UPSERT feature to SPARK
Posted by asfgit <gi...@git.apache.org>.
Github user asfgit closed the pull request at:
https://github.com/apache/spark/pull/16692
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16692: [SPARK-19335] Introduce UPSERT feature to SPARK
Posted by ilganeli <gi...@git.apache.org>.
Github user ilganeli commented on the issue:
https://github.com/apache/spark/pull/16692
Hi, all - thanks for this submission. Overall it's a very clean implementation and I like it a lot. There's obviously a large amount of effort that went into developing this. The main issue with this approach however is that the Upsert statement itself is an extremely expensive operation. Depending on how your uniqueness condition is defined, validating against the uniqueness constraint proves to be the most expensive part of this whole sequence. In https://github.com/apache/spark/pull/16685 I chose to implement this by reading in the existing table and doing a join operation to identify conflicts. The reason for this is that operation is easily distributed across the entire dataset.
In contrast, the implementation as it stands in this PR ultimately depends entirely on the database to enforce the uniqueness constraint, something that in fact can ONLY be executed serially and requires a full traversal of the index created on the uniqueness constraint. Furthermore, this index, in both MySQL and Postgres (the examples you've provided) cannot be implemented as a Hash index. Unless the owner of the database manually computes and enforces hashes on individual rows, this approach instead relies on btree indices to do this lookup.
This is a marginal cost when the btree is on a single field but if the uniqueness constraint spans multiple columns, this index is implemented as nested btrees. This, in turn, proves to be an extraordinarily costly update with non-linear performance degradation as both the size of the database and the size of the table being upserted increase.
This approach mirrors our initial approach to the problem but we ultimately moved away from this approach in favor of the one in https://github.com/apache/spark/pull/16685 for performance reasons. We were able to achieve a more than 10x performance increase, even taking into account the cost of the additional joins. Our tests were not massive - we tested against a roughly 10gb database in Postgres with approximately 10 million rows - on a relatively middle-line machine. I would love to know if you guys have done any performance benchmarks with this approach and if you could try out the approach in https://github.com/apache/spark/pull/16685 and let me know how that performs. Thanks!
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16692: [SPARK-19335] Introduce UPSERT feature to SPARK
Posted by gatorsmile <gi...@git.apache.org>.
Github user gatorsmile commented on the issue:
https://github.com/apache/spark/pull/16692
@kevinyu98 How about closing this PR at first and revisit it later?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16692: [SPARK-19335] Introduce UPSERT feature to SPARK
Posted by gatorsmile <gi...@git.apache.org>.
Github user gatorsmile commented on the issue:
https://github.com/apache/spark/pull/16692
We are closing it due to inactivity. please do reopen if you want to push it forward. Thanks!
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark issue #16692: [SPARK-19335] Introduce UPSERT feature to SPARK
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the issue:
https://github.com/apache/spark/pull/16692
Can one of the admins verify this patch?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org