SPARK SQL – update MySql table using DataFrames and JDBC

by

It is not possible. As for now (Spark 1.6.0 / 2.2.0 SNAPSHOT) Spark DataFrameWriter supports only four writing modes:

  • SaveMode.Overwrite: overwrite the existing data.
  • SaveMode.Append: append the data.
  • SaveMode.Ignore: ignore the operation (i.e. no-op).
  • SaveMode.ErrorIfExists: default option, throw an exception at runtime.

You can insert manually for example using mapPartitions (since you want an UPSERT operation should be idempotent and as such easy to implement), write to temporary table and execute upsert manually, or use triggers.

In general achieving upsert behavior for batch operations and keeping decent performance is far from trivial. You have to remember that in general case there will be multiple concurrent transactions in place (one per each partition) so you have to ensure that there will no write conflicts (typically by using application specific partitioning) or provide appropriate recovery procedures. In practice it may be better to perform and batch writes to a temporary table and resolve upsert part directly in the database.

More Related Contents:

  1. Spark Unable to find JDBC Driver
  2. How to use SQL query to define table in dbtable?
  3. Partitioning in spark while reading from RDBMS via JDBC
  4. Does spark predicate pushdown work with JDBC?
  5. How to optimize partitioning when migrating data from JDBC source?
  6. What is the meaning of partitionColumn, lowerBound, upperBound, numPartitions parameters?
  7. Spark functions vs UDF performance?
  8. Find maximum row per group in Spark DataFrame
  9. How to change dataframe column names in pyspark?
  10. How to export a table dataframe in PySpark to csv?
  11. How can we JOIN two Spark SQL dataframes using a SQL-esque “LIKE” criterion?
  12. Including null values in an Apache Spark Join
  13. Defining a UDF that accepts an Array of objects in a Spark DataFrame?
  14. Processing multiple files as independent RDD’s in parallel
  15. How to save/insert each DStream into a permanent table
  16. How to get other columns when using Spark DataFrame groupby?
  17. Spark load data and add filename as dataframe column
  18. PySpark create new column with mapping from a dict
  19. Apache Spark: Get number of records per partition
  20. How to find count of Null and Nan values for each column in a PySpark dataframe efficiently?
  21. Derive multiple columns from a single column in a Spark DataFrame
  22. How to save a spark DataFrame as csv on disk?
  23. Total size of serialized results of 16 tasks (1048.5 MB) is bigger than spark.driver.maxResultSize (1024.0 MB)
  24. What is going wrong with `unionAll` of Spark `DataFrame`?
  25. spark dataframe drop duplicates and keep first
  26. pyspark: count distinct over a window
  27. PySpark: How to fillna values in dataframe for specific columns?
  28. When to cache a DataFrame?
  29. How to calculate Median in spark sqlContext for column of data type double
  30. PySpark error: AttributeError: ‘NoneType’ object has no attribute ‘_jvm’

Leave a Comment