topic Change data feed for tables with allowColumnDefaults property "enabled" in Data Engineering

Change data feed for tables with allowColumnDefaults property "enabled"

n_joy — Wed, 04 Sep 2024 17:28:28 GMT

I have a Delta table already created, with both enabled the #enableChangeDataFeed option and #allowColumnDefaults properties. However when writing to CDC table with streaming queries it fails with the following error [CREATE TABLE command because it assigned a column DEFAULT value, but corresponding table feature was not enabled. Please retry command again after executing ALTER TABLE table_name SET TBLPROPERTIES(#'delta.feature.allowColumnDefaults' = 'supported')

My understanding is that this CREATE TABLE is inside the box of

#enableChangeDataFeed feature, since adding metadata columns (https://docs.delta.io/latest/delta-change-data-feed.html#what-is-the-schema-for-the-change-data-feed) , so I was wondering is there a way to set required for this case properties (#'delta.feature.allowColumnDefaults' = 'supported')when reading and writing with Streaming queries.
I mean how to resolve missing property before streaming query starts if I am not creating or running CREATE TABLE statement for CDC table myself?

Re: Change data feed for tables with allowColumnDefaults property "enabled"

n_joy — Wed, 04 Sep 2024 18:26:43 GMT

ERROR:[WRONG_COLUMN_DEFAULTS_FOR_DELTA_FEATURE_NOT_ENABLED] Failed to execute CREATE TABLE command because it assigned a column DEFAULT value, but corresponding table feature was not enabled. Please retry command again after executing ALTER TABLE table_name SET TBLPROPERTIES(#'delta.feature.allowColumnDefaults' = 'supported')

Re: Change data feed for tables with allowColumnDefaults property "enabled"

filipniziol — Thu, 05 Sep 2024 07:21:41 GMT

Hi @n_joy ,
what is your limitation here?
You cannot run the statement mentioned in the error message?

ALTER TABLE table_name SET TBLPROPERTIES('delta.feature.allowColumnDefaults' = 'supported')

Do I understand correctly that the table is created, you do not have permissions to run ALTER statement, so you want to somehow change this property with steaming query?

If yes, it is not possible.

Streaming queries that insert data are DML (Data Manipulation Language) statements, which are used to modify the data within the table (e.g., INSERT, UPDATE).

ALTER statements are DDL (Data Definition Language) statements, which are used to modify the structure or properties of the table itself.

DML and DDL statements cannot be mixed in a streaming query. This is a fundamental limitation in SQL operations, meaning you cannot modify table properties while performing data insertions through streaming.

Additionally, even creating a table with DEFAULT without 'delta.feature.allowColumnDefaults' property fails.

To sum up, the only way to make it work is to run the ALTER command and set 'delta.feature.allowColumnDefaults' = 'supported'.

Re: Change data feed for tables with allowColumnDefaults property "enabled"

n_joy — Thu, 05 Sep 2024 08:23:46 GMT

Case is the following: "For some reason, the Change Data Feed feature recognizes DEFAULT columns from the source but does not enable this property when creating a _cdc table.":

So it uses the following DDL that is impossible to execute:

Instead of this one:

As a workaround , prior to run streaming queries I create a cdc table myself

I was just wondering whether there is any other way without explicitly running DDL for _cdc table myself.

Re: Change data feed for tables with allowColumnDefaults property "enabled"

n_joy — Thu, 05 Sep 2024 08:32:00 GMT

@filipniziol
Hi! Thank you for your response! Please see my comments above.

Re: Change data feed for tables with allowColumnDefaults property "enabled"

filipniziol — Thu, 05 Sep 2024 10:38:12 GMT

Hi @n_joy ,

Based on the docs it will not be possible.

https://spark.apache.org/docs/latest/api/python/reference/pyspark.ss/api/pyspark.sql.streaming.DataStreamWriter.htm

If you check toTable definition, it suggest the table is already in place ("output results to the given table").
I think the fact that the table is is being created is anyway pretty cool.

Neither delta lake documentation you shared nor the Apache Spark documentation offer any parameter that enable configuring table properties.

The solution here is to have CREATE script for a table, specify the table properties and then to run streaming query to insert the data.

Re: Change data feed for tables with allowColumnDefaults property "enabled"

n_joy — Thu, 05 Sep 2024 11:14:03 GMT

@filipniziol
Yes, that is what I do 🙂
Thanks for feedback !