topic Re: SQL Warehouse: Retrieving SQL ARRAY Type via JDBC driver in Data Engineering

SQL Warehouse: Retrieving SQL ARRAY Type via JDBC driver

wilco — Fri, 05 Apr 2024 07:40:21 GMT

Hi all,

we are currently running into the following issue

we are using serverless SQL warehouse
in a JAVA application we are using the latest Databricks JDBC driver (v2.6.36)
we are querying the warehouse with a collect_list function, which should return an ARRAY type
but instead we are getting a Json List as String

We are trying the query which is mentioned in the documentation

SELECT collect_list(col) FROM VALUES (1), (2), (NULL), (1) AS tab(col);

The MetaData instance of the ResultSet instance indicates that the java.sql.Types constant for the retrieved column is VARCHAR.

String query = "SELECT collect_list(col) result_value FROM VALUES (1), (1), (NULL), (2) AS tab(col)"; ResultSet resultSet = connection.createStatement().executeQuery(query); ResultSetMetaData resultSetMetaData = resultSet.getMetaData(); int columnType = resultSetMetaData.getColumnType(1); // columnType equals java.sql.Types.VARCHAR with int value == 12

Running the same query in the Databricks SQL Editor in the browser returns the correct type (of course).

Any idea how to get the array type with using the JDBC driver?

Many thanks in advance!

Re: SQL Warehouse: Retrieving SQL ARRAY Type via JDBC driver

wilco — Tue, 09 Apr 2024 07:30:13 GMT

Hi @Retired_mod

many thanks for your response. I followed your proposed steps to resolve the issue

We use the latest released driver version which is 2.6.36: https://www.databricks.com/spark/jdbc-drivers-archive
yes. this is indeed the issue: not an ARRAY is returned but a VARCHAR type
Unfortunately with explicitely defining the result name does not change the resulting type. I don´t see that the expected data type is specified with adding AS result_value
Unfortunately nothing changed

Does the underlying Simba Driver support complex types such as ARRAY?

Re: SQL Warehouse: Retrieving SQL ARRAY Type via JDBC driver

KTheJoker — Mon, 03 Jun 2024 04:02:05 GMT

Hey Wilco,

The answer is no, ODBC/JDBC don't support complex types so these need to be compressed into strings over the wire (usually in JSON representation) and rehydrated on the client side into a complex object.