SQL Warehouse: Retrieving SQL ARRAY Type via JDBC driver

[wilco]

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!

[wilco]

Hi @Retired_mod 

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

1. We use the latest released driver version which is 2.6.36: https://www.databricks.com/spark/jdbc-drivers-archive 
2. yes. this is indeed the issue: not an ARRAY is returned but a VARCHAR type
3. 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
4. Unfortunately nothing changed

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

[KTheJoker]

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.