Table Create With Identity Column Fails To Create Identity Column

[narquette]

Issue:

When I attempt to create a sqlalchemy table with an indentity seed the compiled code doesn't have the required information needed to setup an identity column in the database.

Sample Code:

assume that you already have databricks configured for command line usage ("databricks config")

from configparser import ConfigParser
from pathlib import Path

from sqlalchemy import create_engine, Identity
from sqlalchemy.orm import Session
from sqlalchemy.engine import URL
from sqlalchemy.schema import Table, Column, MetaData, CreateTable
from sqlalchemy.sql.sqltypes import BIGINT, VARCHAR


config_path = Path.home() / '.databrickscfg'
config = ConfigParser()
config.read(config_path)

token = config['DEFAULT']['token']

url_info = {
  'drivername': 'databricks',
  'username': 'token',
  'password': token,
  'host': config['DEFAULT']['token'].replace('http://', '').replace('/', ''),
  'port': 43,
  'database': 'curv'
}

catalog = config['DEFAULT']['catalog']
http_path = "/sql/1.0/warehouses/3c0fbf823204df89"

url = URL.create(
  **{key: value for key, value in url_info.items()},
  query={'http_path': http_path, 'catalog': catalog}
)

engine = create_engine(url=url)

session = Session(engine)

table = Table(
  'test',
  MetaData(),
  Column('id', BIGINT, primary_key=True, nullable=False, server_default=Identity(start=1)),
  Column('comments', VARCHAR(255), nullable=True)
)

from configparser import ConfigParser
from pathlib import Path

from sqlalchemy import create_engine, Identity
from sqlalchemy.orm import Session
from sqlalchemy.engine import URL
from sqlalchemy.schema import Table, Column, MetaData, CreateTable
from sqlalchemy.sql.sqltypes import BIGINT, VARCHAR


config_path = Path.home() / '.databrickscfg'
config = ConfigParser()
config.read(config_path)

token = config['DEFAULT']['token']

url_info = {
  'drivername': 'databricks',
  'username': 'token',
  'password': token,
  'host': config['DEFAULT']['token'].replace('http://', '').replace('/', ''),
  'port': 43,
  'database': '<database_name>'
}

catalog = config['DEFAULT']['catalog']
http_path = "<path_to_cluster>"

url = URL.create(
  **{key: value for key, value in url_info.items()},
  query={'http_path': http_path, 'catalog': catalog}
)

engine = create_engine(url=url)

session = Session(engine)

table = Table(
  'test',
  MetaData(),
  Column('id', BIGINT, primary_key=True, nullable=False, server_default=Identity(start=1)),
  Column('comments', VARCHAR(255), nullable=True)
)

# with needed column information
print(CreateTable(table))

# without needed column information
print(CreateTable(table).compile(bind=engine))

what printed CreateTable looks like (correct)

# with needed column information
print(CreateTable(table))

# output
CREATE TABLE test (
	id BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 1), 
	comments VARCHAR(255), 
	PRIMARY KEY (id)
)

what printed compiled CreateTable looks like (incorrect)

# without needed column information
print(CreateTable(table).compile(bind=engine))

CREATE TABLE test (
	id BIGINT NOT NULL, 
	comments STRING, 
	PRIMARY KEY (id)
) USING DELTA

Machine Info

OS: Window Server 2022 VM

Software Info:

Python - 3.10.2

Python Packages:

databricks-sql-connector : 2.5.2
sqlalchemy : 1.4.48

[narquette]

Why is this important?

If you create a identity column in databricks, it will not add the identity column correctly causing inserts into the table to not automatically create an id.

[susodapop]

Thanks for the robust report. We've got some big work in the hopper for the sqlalchemy dialect. I would not expect this to work at present because it wasn't a use-case we targeted when we first developed the dialect. At that time (autumn 2022) primary keys and constraints were merely experimental on Databricks.

That has of course changed in the meantime.

In the past few days I've been fixing little nits with the sqlalchemy dialect here and there. Mainly targeting issues raised here on the repository. But broadly what we need is to tidy-up and merge #125 so we can run the entire test suite from SQLAlchemy and determine the total set of gaps that we can plug.

[narquette]

Thanks for the update. Think I pasted some information twice up above.

[narquette]

The host key should be like this:

'host': config['DEFAULT']['host'].replace('https://', '').replace('/', ''),

[narquette]

Appears to be ok for usage with DATEADD(text('d'))

[susodapop]

Commenting to indicate that we've now implemented this in our forthcoming sqlalchemy 2 support.