SQLAlchemy Dialect Compliance Tests

I did a lot of work on the BigQuery SQLAlchemy dialect on 2021.

(I needed a BigQuery connection to develop/test that. 😄 )

I leaned heavily on the SQLAlchemy Dialect Compliance test suite.

It doesn't look like y'all are using the compliance suite. It might be a good way to provide a lot more testing of your dialect.

FWIW.

Thanks for the insight and links. Our focus when writing the initial SQLAlchemy dialect for this package was to functionally replace the community-made sqlalchemy-databricks pypi package, which is not being maintained. Full implementation of the SQLA api wasn't our target; although long-term that's desirable. For now, development has been driven by specific customer use-cases.

Would you like to have a PR that sets up the dialect-compliance tests?

Yes that would be a terrific contribution.

Cool. I'm going to PyCon this week, so it may be a couple of weeks. :)

I started working on this.

With a clean checkout, unit tests run fine.

When I ran the end-to-end tests, which by default only run the sqlalchemy tests, I got lots of errors like:

Catalog 'None' plugin class not found: spark.sql.catalog.None is not defined.

I then:

export catalog=hive_metastore

which got me past those errors and then I got a lot of:

Database 'none' not found.

which were fixed by:

export schema=default

I'll update CONTRIBUTING.md to mention setting catalog and schema.

Finally, I'm getting:

self = <databricks.sql.thrift_backend.ThriftBackend object at 0x7f28145012a0>
op_handle = TOperationHandle(operationId=THandleIdentifier(guid=b'RJ"M\x82\x19G\xea\x92\xb3\x80\xfa\xc1R\x02\x15', secret=b'\xb7\x...x8dM\xb4\x92\xcb\xa9\x83\x03i\xefC', executionVersion=None), operationType=0, hasResultSet=True, modifiedRowCount=None)
get_operations_resp = TGetOperationStatusResp(status=TStatus(statusCode=0, infoMessages=None, sqlState=None, errorCode=None, errorMessage=No...5)\n\t... 16 more\n", responseValidation=None, idempotencyType=None, statementTimeout=None, statementTimeoutLevel=None)

    def _check_command_not_in_error_or_closed_state(
        self, op_handle, get_operations_resp
    ):
        if get_operations_resp.operationState == ttypes.TOperationState.ERROR_STATE:
            if get_operations_resp.displayMessage:
>               raise ServerOperationError(
                    get_operations_resp.displayMessage,
                    {
                        "operation-id": op_handle and op_handle.operationId.guid,
                        "diagnostic-info": get_operations_resp.diagnosticInfo,
                    },
                )
E               sqlalchemy.exc.DatabaseError: (databricks.sql.exc.ServerOperationError) 
E               [PARSE_SYNTAX_ERROR] Syntax error at or near '('(line 6, pos 13)
E               
E               == SQL ==
E               
E               CREATE TABLE `PySQLTest_1683167898` (
E               	name STRING NOT NULL, 
E               	episodes INT, 
E               	some_bool BOOLEAN, 
E               	PRIMARY KEY (name)
E               -------------^^^
E               ) USING DELTA
E               
E               [SQL: 
E               CREATE TABLE `PySQLTest_1683167898` (
E               	name STRING NOT NULL, 
E               	episodes INT, 
E               	some_bool BOOLEAN, 
E               	PRIMARY KEY (name)
E               ) USING DELTA
E               
E               ]
E               (Background on this error at: https://sqlalche.me/e/14/4xp6)

src/databricks/sql/thrift_backend.py:484: DatabaseError

Any suggestions?

@unj1m

Hi @unj1m sorry for the late response. yes that error is happening because you're running Unity Catalog tests against a hive_metastore catalog. In the Unity Catalog world the hive_metastore catalog is dedicated to maintaining backwards compatibility with pre-UC metastores. It doesn't implement any of the new features of UC, including primary keys.

For this to work, you need to set the catalog to any other catalog in a UC enabled SQL warehouse or endpoint.

[edit] Just not the samples catalog since it's read-only.

No worries. Thanks for clarifying.

@unj1m

Let me know if you need a hand with anything @unj1m!

@unj1m

Let me know if you need a hand with anything @unj1m!

@susodapop Thanks!

Here's a PR that sets up the tests to run.

See the note there about updating requirements.py. It would be good good for someone who knows databricks well to update that.

Current status:
73 failed, 201 passed, 277 skipped, 1 warning, 63 errors in 2550.73s (0:42:30)

which is a pretty good start. :)

Yes that's a very good start. I will be reviewing this PR carefully, particularly combing out the many references to bigquery. More soon. Thanks!

particularly combing out the many references to bigquery

Yeah, sorry about that. :(

chore(kernel): re-pin KERNEL_REV to updated feat/pyo3-oauth-tls HEAD

feat(kernel): wire OAuth (M2M/U2M), TLS/mTLS, and Geometry on use_ker…

feat(bots): select author flow from issue Type; bump engine to d24ca21

docs(bots): reconcile design-doc engine SHA + flag the 4-way pin lock…

feat(bots): local install-bot-engine + bot-prelude composites (PAT-free)

feat(bots): onboard reviewer-bot to pinned databricks-bot-engine (dat…

Navigation Menu

Repository navigation

SQLAlchemy Dialect Compliance Tests #107

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

SQLAlchemy Dialect Compliance Tests #107

Description

Activity

susodapop commented on Apr 18, 2023

unj1m commented on Apr 18, 2023

susodapop commented on Apr 19, 2023

unj1m commented on Apr 19, 2023

unj1m commented on May 3, 2023

susodapop commented on May 9, 2023

unj1m commented on May 9, 2023

susodapop commented on May 12, 2023

unj1m commented on May 14, 2023

susodapop commented on May 18, 2023

unj1m commented on May 18, 2023

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions