[sqlalchemy] [Feature Request] Support default values about databricks-sql-python HOT 6 CLOSED

try to use

sa.func.now()

from databricks-sql-python.

Hey folks thanks for the write-up and debugging! I'm pleased to see that you worked out how to hack this together using event.listens_for. The sqlalchemy events system is a powerful tool, since nearly every behaviour of a given dialect can be patched in this way. I agree that this belongs in the dialect itself.

@dhirschfeld would you shoot an email to [email protected] referencing this GitHub issue so we can track it and add it to the pipeline for enhancements to the dialect?

from databricks-sql-python.

Thanks to this thread I ws able to find out the magic syntax:

CREATE TABLE test.`ModelMetadata` (
	pkid BIGINT GENERATED ALWAYS AS IDENTITY, 
	name STRING NOT NULL, 
	version STRING NOT NULL, 
	insert_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, 
	PRIMARY KEY (pkid)
) USING DELTA
TBLPROPERTIES(
  'delta.minReaderVersion' = '1',
  'delta.minWriterVersion'='7',
  'delta.feature.allowColumnDefaults' = 'enabled'
);

...but I don't really know how to integrate that with sqlalchemy.

If that is required for defaults then I guess this projects needs to implement a custom compiler to add the required TBLPROPERTIES?

from databricks-sql-python.

With sa.func.now() sqlalchemy emits the below SQL:

CREATE TABLE test.`ModelMetadata` (
	pkid BIGINT GENERATED ALWAYS AS IDENTITY, 
	name STRING NOT NULL, 
	version STRING NOT NULL, 
	insert_timestamp TIMESTAMP DEFAULT now() NOT NULL, 
	PRIMARY KEY (pkid)
) USING DELTA;

...which gives the below error:

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

from databricks-sql-python.

If that is required for defaults then I guess this projects needs to implement a custom compiler to add the required TBLPROPERTIES?

Or, even better, just enable allowColumnDefaults by default so it Just Works without having to touch the sqlalchemy compiler.

from databricks-sql-python.

In my specific case it appears I can hack around it with:

@event.listens_for(Base.metadata, "after_create")
def set_default_insert_timestamp(target, connection, **kw):
    for table in target.tables:
        connection.execute(DDL(f"""
        ALTER TABLE {table} SET TBLPROPERTIES(
            'delta.feature.allowColumnDefaults' = 'enabled'
        );
        """))
        connection.execute(DDL(f"""
        ALTER TABLE {table}
        ALTER COLUMN insert_timestamp
        SET DEFAULT CURRENT_TIMESTAMP;
        """))

...but that's a bit gross 🤢

It would be nice for the standard sqlalchemy server_default to Just Work with no hacks required (by the end-user!)

from databricks-sql-python.