DESCRIBE EXTENDED table AS JSON does not work in DBR<16.2

[filipeo2-mck]

What happened?

Hello!

Problem:
ibis-framework[databricks]==10.5 does not work with Databricks Runtime 13.3 LTS.

Later, I noted this item in 10.5's release notes:

databricks: use AS JSON for programmatic output of schema information (d55a5ee)

At some point, when reading a table, it tries a DESCRIBE EXTENDED table AS JSON, option that is not supported by DBR < 16.2, according to Databricks docs

Temporary solution:
Limiting my ibis-framework version to <10.5 fixed it temporarily.

---

Parallel discussion #1:
Besides the error above, I was curious about which DBR versions ibis-framework is being tested against.
It would be interesting to test it against all supported DBR versions. For example, the old 11.3 LTS support is set to finish in 19/oct/2025. All more recent LTS versions still have a long life and should be supported (ideally).

---

Parallel discussion #2:
I still don't see the databricks backend documentation, as reported under #10969 :(

Is there some kind of "experimental" phase before being officially published?

Thank you :)

What version of ibis are you using?

10.5.0

What backend(s) are you using, if any?

databricks

Relevant log output

ServerOperationError                      Traceback (most recent call last)
File /local_disk0/.ephemeral_nfs/envs/pythonEnv-20975c93-5c0f-4a43-b49b-3d225a17cd61/lib/python3.10/site-packages/ibis/backends/databricks/__init__.py:313, in Backend.get_schema(self, table_name, catalog, database)
    312     with self.con.cursor() as cur:
--> 313         [(out,)] = cur.execute(f"DESCRIBE EXTENDED {table} AS JSON").fetchall()
    314 except databricks.sql.exc.ServerOperationError as e:

File /local_disk0/.ephemeral_nfs/envs/pythonEnv-20975c93-5c0f-4a43-b49b-3d225a17cd61/lib/python3.10/site-packages/databricks/sql/client.py:773, in Cursor.execute(self, operation, parameters)
    772 self._close_and_clear_active_result_set()
--> 773 execute_response = self.thrift_backend.execute_command(
    774     operation=prepared_operation,
    775     session_handle=self.connection._session_handle,
    776     max_rows=self.arraysize,
    777     max_bytes=self.buffer_size_bytes,
    778     lz4_compression=self.connection.lz4_compression,
    779     cursor=self,
    780     use_cloud_fetch=self.connection.use_cloud_fetch,
    781     parameters=prepared_params,
    782 )
    783 self.active_result_set = ResultSet(
    784     self.connection,
    785     execute_response,
   (...)
    788     self.arraysize,
    789 )

File /local_disk0/.ephemeral_nfs/envs/pythonEnv-20975c93-5c0f-4a43-b49b-3d225a17cd61/lib/python3.10/site-packages/databricks/sql/thrift_backend.py:882, in ThriftBackend.execute_command(self, operation, session_handle, max_rows, max_bytes, lz4_compression, cursor, use_cloud_fetch, parameters)
    881 resp = self.make_request(self._client.ExecuteStatement, req)
--> 882 return self._handle_execute_response(resp, cursor)

File /local_disk0/.ephemeral_nfs/envs/pythonEnv-20975c93-5c0f-4a43-b49b-3d225a17cd61/lib/python3.10/site-packages/databricks/sql/thrift_backend.py:974, in ThriftBackend._handle_execute_response(self, resp, cursor)
    972 self._check_direct_results_for_error(resp.directResults)
--> 974 final_operation_state = self._wait_until_command_done(
    975     resp.operationHandle,
    976     resp.directResults and resp.directResults.operationStatus,
    977 )
    979 return self._results_message_to_execute_response(resp, final_operation_state)

File /local_disk0/.ephemeral_nfs/envs/pythonEnv-20975c93-5c0f-4a43-b49b-3d225a17cd61/lib/python3.10/site-packages/databricks/sql/thrift_backend.py:807, in ThriftBackend._wait_until_command_done(self, op_handle, initial_operation_status_resp)
    806 if initial_operation_status_resp:
--> 807     self._check_command_not_in_error_or_closed_state(
    808         op_handle, initial_operation_status_resp
    809     )
    810 operation_state = (
    811     initial_operation_status_resp
    812     and initial_operation_status_resp.operationState
    813 )

File /local_disk0/.ephemeral_nfs/envs/pythonEnv-20975c93-5c0f-4a43-b49b-3d225a17cd61/lib/python3.10/site-packages/databricks/sql/thrift_backend.py:604, in ThriftBackend._check_command_not_in_error_or_closed_state(self, op_handle, get_operations_resp)
    603 if get_operations_resp.displayMessage:
--> 604     raise ServerOperationError(
    605         get_operations_resp.displayMessage,
    606         {
    607             "operation-id": op_handle
    608             and self.guid_to_hex_id(op_handle.operationId.guid),
    609             "diagnostic-info": get_operations_resp.diagnosticInfo,
    610         },
    611     )
    612 else:

ServerOperationError: 
[PARSE_SYNTAX_ERROR] Syntax error at or near 'JSON': extra input 'JSON'.(line 1, pos 36)

== SQL ==
DESCRIBE EXTENDED `raw_customer` AS JSON
------------------------------------^^^

Code of Conduct

• I agree to follow this project's Code of Conduct

[cpcloud]

We don't really have enough people working on Ibis to support all LTS versions of DBR. In this particular case I think we can figure out an alternative way to get schema information that doesn't require using DESCRIBE EXTENDED.

[cpcloud]

How do I get full schema information for struct types?

Using DESCRIBE t I get an ellipsis, and I see the same thing for using information_schema.columns:

Using EXTENDED is the only way I've found to get this information.

[filipeo2-mck]

Yes, the DESCRIBE EXTENDED is the only way I know, that was already available. If I'm not mistaken, we get a table with the results.
The problem is just the usage of AS JSON as output format, that is not supported in DBR < 16.2.

I'll try something with a table I have and return.