Hey everybody, Is there a way to define the TBLPROPERTIES with '.option' ? Something like this:

df.write.format("delta")
 .mode("overwrite")
 .option("TBLPROPERTIES", "key1=value1, key2=value2") <-- HERE
 .save("./")

I know there is a way to update TBLPROPERTIES with the following code:

alter_table_query = f"ALTER TABLE delta.`hdfs:///{table_path}` SET TBLPROPERTIES ('delta.logRetentionDuration' = 'interval 1 days')"

spark.sql(alter_table_query)

but I want to add this as part of streaming write.

Some properties are reachable through options atleast.

df.write.format("delta") \  .option("delta.autoOptimize.optimizeWrite , "true") \  .option("delta.autoOptimize.autoCompact", "true") \
.saveAsTable(table)

Do you know where I can find the list of properties that I can initialize with .option? Can I initialize

delta.logRetentionDuration

?

Hey Daniel, I think you're looking for table properties documentation. Hope you'll find the below links useful. If you're using Delta Lake (open source), you can refer to this doc: https://docs.delta.io/latest/table-properties.html If you're using Databricks Delta Lake, you can refer to this doc: https://docs.databricks.com/delta/table-properties.html

Hey @Omkar The links you provided are for the list of table properties in general. I searching the list of table properties that I can initialize using the

.option()

When I tiring to initialize the properties like this:

df.write.format("delta") \ 
 .option("delta.autoOptimize.optimizeWrite" , "true") \ <---This way
 .saveAsTable(table)

the TBLPROPERTIES don't change.

@Daniel Bariudin This particular table property

delta.autoOptimize.optimizeWrite

is available in Databricks Delta and not available in Delta Lake (open source). You'll have to check which one you're using and plan accordingly.

Hey @Omkar I tried your solution and although it compiles web I run

SHOW TBLPROPERTIES

I don't see the property☹️ More details of what I have done: 1. initialize spark-session the same way you showed above 2. Reading existing Delta table 3. Writing the data to the same table 4. Running

SHOW TBLPROPERTIES

to see the property - don't see the property Do I missing something? Have any other ideas🙏?

@Daniel Bariudin Can you post the Spark Session initialization config you're using and the output of

SHOW TBLPROPERTIES

here? Let's check it out.

@Omkar Here are the configs:

spark = SparkSession.builder.appName("test")\
        .config("spark.sql.extensions", "io.delta.sql.DeltaSparkSessionExtension")\
        .config("spark.sql.catalog.spark_catalog","org.apache.spark.sql.delta.catalog.DeltaCatalog")\
        .config("spark.databricks.delta.properties.defaults.logRetentionDuration", "interval 7 days")\
        .getOrCreate()

and after writing to the Delta table the output is :

+----------------------+-----+
|key                   |value|
+----------------------+-----+
|delta.minReaderVersion|1    |
|delta.minWriterVersion|2    |
+----------------------+-----+

@Daniel Bariudin do you have an already existing Delta table or you're creating a new one? Asking because the default table properties only work on new delta tables created in the Spark session where you set the properties.

@Omkar I working on existing Delta table I this case , if I create new table and initialize it with delta table properties( that I add to spark session) , can I change the property in the future, in the same way but with existing table? I assume that on table create I can add default property (like delta.logRetentionDuration", "interval 7 days" ) but I want to insure it can be changed. **The problem with running streaming write is that I getting MetadataChangedException ( I make concurrent write and table property change, and I don't want to shut down the writing)

Yes correct @Daniel Bariudin, it will be a good idea to not change table properties on the tables which are actively running in streaming write mode, as Delta doesn't allow that (this is mentioned in this doc in the Note section that I shared above). Quoting the Note section here for your reference:

Note

• Modifying a Delta table property is a write operation that will conflict with other concurrent write operations, causing them to fail. We recommend that you modify a table property only when there are no concurrent write operations on the table.

Yes your assumption is correct, the new tables created will have the table properties that you'll set in the Spark Session config. You can change them any time in the future with the

ALTER TABLE tablename SET TBLPROPERTIES (...)

query - only do this when no other write operation is being performed on that particular Delta table. Hope this answers all your questions!

@Omkar Thank you very much!!!!!!!!!!!!!!!!!