โ09-04-2024 10:28 AM
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
โ09-05-2024 01:23 AM
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.
โ09-05-2024 03:38 AM
Hi @n_joy ,
Based on the docs it will not be possible.
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.
โ09-05-2024 04:14 AM
โ09-04-2024 11:26 AM
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')
โ09-05-2024 12:19 AM - edited โ09-05-2024 12:21 AM
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'.
โ09-05-2024 01:23 AM
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.
โ09-05-2024 01:32 AM
@filipniziol
Hi! Thank you for your response! Please see my comments above.
โ09-05-2024 03:38 AM
Hi @n_joy ,
Based on the docs it will not be possible.
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.
โ09-05-2024 04:14 AM
Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโt want to miss the chance to attend and share knowledge.
If there isnโt a group near you, start one and help create a community that brings people together.
Request a New Group