topic Re: Why Spark Save Modes , "overwrite" always drops table although "truncate" is true ? in Data Engineering

Why Spark Save Modes , "overwrite" always drops table although "truncate" is true ?

AkifCakir — Thu, 21 Oct 2021 14:56:24 GMT

Hi Dear Team,

I am trying to import data from databricks to Exasol DB.

I am using following code in below with Spark version is 3.0.1 ,

dfw.write \
    .format("jdbc") \
    .option("driver", exa_driver) \
    .option("url", exa_url) \
    .option("dbtable", "table") \
    .option("user", username) \
    .option("password", exa_password) \
    .option("truncate", "true") \
    .option("numPartitions", "1") \
    .option("fetchsize", "100000") \
    .mode("overwrite") \
    .save()

The problem is when mode is "overwrite", it always drop target table in Exasol db, although in the spark documentation (https://spark.apache.org/docs/3.0.1/sql-data-sources-jdbc.html#content) it says for "truncate" option that

truncate --> This is a JDBC writer related option. When SaveMode.Overwrite is enabled, this option causes Spark to truncate an existing table instead of dropping and recreating it. This can be more efficient, and prevents the table metadata (e.g., indices) from being removed. However, it will not work in some cases, such as when the new data has a different schema. It defaults to false. This option applies only to writing.

According to this explanation , I would expect with option("truncate", "true"), it should not drop but truncate the table. Nevertheless it drops the table even in that case. Note: we can have separate truncate command and go with append mode but I do not want to have extra second command but solve in one command as suggested in Exasol documentation here (https://github.com/exasol/spark-exasol-connector/blob/main/doc/user_guide/user_guide.md#spark-save-modes) as well.

Am I missing something or do you have any resolution ?

Re: Why Spark Save Modes , "overwrite" always drops table although "truncate" is true ?

jose_gonzalez — Sat, 13 Nov 2021 00:35:59 GMT

Hi @Akif Cakir ,

You are correct, this is the expected behavior when using JDBC connector. Docs here

Have you try to use the "exasol" connector instead of JDBC? do you also get this same behavior?

Re: Why Spark Save Modes , "overwrite" always drops table although "truncate" is true ?

mick042 — Fri, 12 Aug 2022 16:06:04 GMT

Facing the same problem I used the following:

sfOptions = {

"sfURL" : "<account>.snowflakecomputing.com",

"sfAccount" : "<account>",

"sfUser" : "<user>",

"sfPassword" : "***",

"sfDatabase" : "<database>",

"sfSchema" : "<schema>",

"sfWarehouse" : "<warehouse>",

"truncate_table" : "ON",

"usestagingtable" : "OFF",

}

https://community.snowflake.com/s/article/How-to-Load-Data-in-Spark-with-Overwrite-mode-without-Changing-table-Structure

Re: Why Spark Save Modes , "overwrite" always drops table although "truncate" is

Gembo — Wed, 29 Nov 2023 17:39:00 GMT

@AkifCakir , Were you able to find a way to truncate without dropping the table using the .write function as I am facing the same issue as well.