Making py connector to raise an error for wrong SQL when asking to plan a query about databricks-sql-python

[susodapop]

Thanks for opening this issue and apologies for the late response. I've added a ticket for this to our development backlog. Will update this ticket once I have further information (should be next week).

[susodapop]

Okay I've investigated this a bit further. There is no bug in databricks-sql-connector here.

The problem with that implementation is that the driver does not throws an error, but instead retrieves me a string containing the error details, so I need to parse the string result to distinguish if the explained query was valid or not.

This is expected behaviour. EXPLAIN only raises an exception if there was an problem during execution. In all other cases it returns rows of strings. It is left to the end-user to reason with these outputs. A common pattern is to simply display the output to the end user (this is what Databricks SQL does, so does Redash).

If you need to programmatically handle the output of an EXPLAIN command, then you will need to parse the rows of strings as you said or choose a different method of verification. One option is to instead SELECT the query with a LIMIT 0 clause. Doing so does raise an Exception.

INPUT
SELECT BAD-QUERY AS FOO LIMIT 0

OUTPUT

Exception has occurred: ServerOperationError
[MISSING_COLUMN] Column 'BAD' does not exist. Did you mean one of the following? []; line 1 pos 7
  File "databricks-sql-connector/src/databricks/sql/thrift_backend.py", line 484, in _check_command_not_in_error_or_closed_state
    "diagnostic-info": get_operations_resp.diagnosticInfo,
  File "databricks-sql-connector/src/databricks/sql/thrift_backend.py", line 751, in _wait_until_command_done
    op_handle, initial_operation_status_resp
  File "databricks-sql-connector/src/databricks/sql/thrift_backend.py", line 908, in _handle_execute_response
    resp.directResults and resp.directResults.operationStatus,
  File "databricks-sql-connector/src/databricks/sql/thrift_backend.py", line 814, in execute_command
    return self._handle_execute_response(resp, cursor)
  File "databricks-sql-connector/src/databricks/sql/client.py", line 321, in execute
    cursor=self,
  File "databricks-sql-connector/main.py", line 18, in <module>
    cursor.execute("SELECT BAD-QUERY AS FOO LIMIT 0")

[susodapop]

Makes sense. The answer for our connector is the same: we won't raise an exception unless the SQL warehouse / cluster raises one. But I'm passing this feedback onto that team within Databricks. It's unusual that SQL warehouses don't raise a standard error in this case.

[ni-todo-spot]

Thanks for opening this issue and apologies for the late response. I've added a ticket for this to our development backlog. Will update this ticket once I have further information (should be next week).

Sweet thanks a lot!

[ni-todo-spot]

@susodapop Thanks for your reply & time checking this out!

Guess I was used to other outcome as other connectors do raise exception..

The limit 0 option is definitely better than parsing strings, so I'll use that.

Do you happen to know the cost of executing such query?

I'd like to prevent high costs for checking validity of an SQL statement..

[susodapop]

Guess I was used to other outcome as other connectors do raise exception

Which other connectors raise an exception in this case?

[ni-todo-spot]

For my project purposes I also use AWS athena, Vertica, postgresql & clickhouse

Using explain SELECT 1 AS FOO & explain SELECT BAD_QUERY AS FOO you can examine the different output in other DBs 😃:

from clickhouse_driver import Client

client = Client(host="play.clickhouse.com", user="explorer", secure=True)

good = client.query_dataframe("explain SELECT 1 AS FOO")

bad = client.query_dataframe("explain SELECT BAD_QUERY AS FOO")

[ni-todo-spot]

Sounds great, Thanks!