2 weeks ago
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
2 weeks ago
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.
2 weeks ago
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.
2 weeks ago
2 weeks ago
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')
2 weeks ago - last edited 2 weeks ago
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'.
2 weeks ago
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.
2 weeks ago
@filipniziol
Hi! Thank you for your response! Please see my comments above.
2 weeks ago
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.
2 weeks ago
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