SQLAlchemy Dialect Compliance Tests about databricks-sql-python

[unj1m]

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

[susodapop]

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.

[unj1m]

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

[susodapop]

Yes that would be a terrific contribution.

[unj1m]

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?

[susodapop]

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.

[unj1m]

No worries. Thanks for clarifying.

[susodapop]

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. :)

[susodapop]

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!

[unj1m]

particularly combing out the many references to bigquery

Yeah, sorry about that. :(