Repository navigation
Table Create With Identity Column Fails To Create Identity Column #175
Description
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
Metadata
Metadata
Assignees
Labels
No labels
Activity
narquette commentedon Jul 11, 2023
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 commentedon Jul 11, 2023
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 commentedon Jul 11, 2023
Thanks for the update. Think I pasted some information twice up above.
narquette commentedon Jul 11, 2023
The host key should be like this:
'host': config['DEFAULT']['host'].replace('https://', '').replace('/', ''),
narquette commentedon Jul 11, 2023
Appears to be ok for usage with DATEADD(text('d'))
susodapop commentedon Oct 11, 2023
Commenting to indicate that we've now implemented this in our forthcoming sqlalchemy 2 support.
Stop skipping Identity column tests
SQLAlchemy 2: Stop skipping some non-type tests (databricks#247)
SQLAlchemy 2: Stop skipping some non-type tests (#247)
SQLAlchemy 2: Stop skipping some non-type tests (#247)
E2E POC for python telemetry for connect logs (#581)
Add optional telemetry support to the python connector (#628)