How to fetch nested data structures in Databricks using JDBC

lukaseder — Wed, 12 Feb 2025 13:05:34 GMT

I've asked the question also here on stack overflow

When using nested data structures in Databricks (e.g. `ARRAY` or `ROW`) using JDBC, it appears that the results can be fetched as JSON `String` values, e.g.:

try (Statement s = connection.createStatement();
    ResultSet rs = s.executeQuery(
        """
        select array(1, 2), array('a', 'b'), array(true, false)
        """
)) {
    while (rs.next()) {
        System.out.println(rs.getString(1));
        System.out.println(rs.getObject(1).getClass());
        System.out.println(rs.getString(2));
        System.out.println(rs.getObject(2).getClass());
        System.out.println(rs.getString(3));
        System.out.println(rs.getObject(3).getClass());
    }
}

Produces valid JSON strings (at least, that's what they look like):

[1,2]
class java.lang.String
["a","b"]
class java.lang.String
[true,false]
class java.lang.String

Calling `ResultSet::getArray` isn't implemented:

java.sql.SQLDataException: [Databricks][JDBC](10400) Invalid type for data - column: 3, type: Array.
 at com.databricks.client.exceptions.ExceptionConverter.toSQLException(Unknown Source)
 at com.databricks.client.jdbc.common.SForwardResultSet.getArray(Unknown Source)
 at org.jooq.testscripts.JDBC.main(JDBC.java:44)

This seems fine until the array contains `"` characters, e.g. this query `select array('"')` produces this invalid value:

["""]

Instead of:

["\""]

Likewise, when reading `select array(date '2000-01-01')`, I'm getting:

[2000-01-01]

Instead of e.g.:

["2000-01-01"]

Is this a known bug / limitation in the 2.7.1 JDBC driver version that I'm using? Is there a known workaround?

Re: How to fetch nested data structures in Databricks using JDBC

Alberto_Umana — Wed, 12 Feb 2025 13:11:22 GMT

Hi @lukaseder,

This looks to be a bug, but will get more details about it internally. The driver is relatively new.

Have you tried with another version, for instance JDBC: 2.6.40

Re: How to fetch nested data structures in Databricks using JDBC

lukaseder — Wed, 12 Feb 2025 13:14:03 GMT

2.6.40 exposes the same behaviour.

topic Re: How to fetch nested data structures in Databricks using JDBC in Get Started Discussions

How to fetch nested data structures in Databricks using JDBC

Re: How to fetch nested data structures in Databricks using JDBC

Re: How to fetch nested data structures in Databricks using JDBC