topic Re: SQL Error when querying any tables/views on a Databricks cluster via Dbeaver. in Data Engineering

SQL Error when querying any tables/views on a Databricks cluster via Dbeaver.

sage5616 — Tue, 12 Jul 2022 15:40:36 GMT

I am able to connect to the cluster, browse its hive catalog, see tables/views and columns/datatypes

Running a simple select statement from a view on a parquet file produces this error and no other results:

"SQL Error [500540] [HY000]: [Databricks][DatabricksJDBCDriver](500540) Error caught in BackgroundFetcher. Foreground thread ID: 180. Background thread ID: 223. Error caught: sun.misc.Unsafe or java.nio.DirectByteBuffer.<init>(long, int) not available."

Standard Databricks cluster:

Standard_DS3_v2

Advanced Options Spark Config:

spark.databricks.cluster.profile singleNode

spark.databricks.io.directoryCommit.createSuccessFile false

spark.master local[*, 4]

spark.driver.extraJavaOptions -Dio.netty.tryReflectionSetAccessible=true

spark.hadoop.fs.azure.account.key.<reducted>.blob.core.windows.net <reducted>

spark.executor.extraJavaOptions -Dio.netty.tryReflectionSetAccessible=true

parquet.enable.summary-metadata false

My local machine:

Dbeaver Version 22.1.2.202207091909

Dbeaver JDBC URL:

jdbc:databricks://<reducted>.1.azuredatabricks.net:443/default;transportMode=http;ssl=1;httpPath=sql/protocolv1/o/<reducted>/<reducted>;AuthMech=3;UID=token;PWD=<reducted>

MacOS version (M1 chip): Monterey 12.4

Java version:

java --version

openjdk 18.0.1 2022-04-19

OpenJDK Runtime Environment Homebrew (build 18.0.1+0)

OpenJDK 64-Bit Server VM Homebrew (build 18.0.1+0, mixed mode, sharing)

I am able to do the following with no errors (Databricks default test dataset):

CREATE TABLE diamonds USING CSV OPTIONS (path "/databricks-datasets/Rdatasets/data-001/csv/ggplot2/diamonds.csv", header "true");

When I run this "select color from diamonds;" or this "select * from diamonds;"

I get this:

"SQL Error [500618] [HY000]: [Databricks][DatabricksJDBCDriver](500618) Error occured while deserializing arrow data: sun.misc.Unsafe or java.nio.DirectByteBuffer.<init>(long, int) not available"

Hence, any select query on any object (parquet file or anything else) causes the error described above.

What could be the problem? Any recommendations how to resolve this error? Why am I able to connect and see the metadata of the schemas/tables/views/columns, but not query or view the data?

P.S. I followed this guide exactly: https://docs.microsoft.com/en-us/azure/databricks/dev-tools/dbeaver#step-3-connect-dbeaver-to-your-azure-databricks-databases

Re: SQL Error when querying any tables/views on a Databricks cluster via Dbeaver.

Hubert-Dudek — Tue, 12 Jul 2022 17:20:20 GMT

You can try to prefix the path with dbfs:/

Please also do tests from within databricks without using the JDBC driver.

Please also use the Databricks Simba JDBC driver. You can download the driver on https://databricks.com/spark/jdbc-drivers-download?_ga=2.198791215.1433237262.1657645460-440078606.1655449547

If you still have problems, you can use the legacy spark driver as described here https://docs.databricks.com/integrations/bi/jdbc-odbc-bi.html#building-the-connection-url-for-the-legacy-spark-driver

Re: SQL Error when querying any tables/views on a Databricks cluster via Dbeaver.

sage5616 — Tue, 12 Jul 2022 18:56:46 GMT

Hi Hubert,

My responses:

"Please also do tests from within databricks without using the JDBC driver." Queries work within databricks without using the JDBC driver.
"Please also use the Databricks Simba JDBC driver. You can download the driver on https://databricks.com/spark/jdbc-drivers-download?_ga=2.198791215.1433237262.1657645460-440078606.1655449547". Yes, I am already using the latest Databricks Simba JDBC driver.

NOTE: I have tried another tool, DbVisualizer, which does not seem to have this issue. The issue is with Dbeaver and the Databricks Simba JDBC driver. I need help.

Re: SQL Error when querying any tables/views on a Databricks cluster via Dbeaver.

sage5616 — Wed, 20 Jul 2022 14:37:37 GMT

Update. I have tried SQL Workbench/J and encountered exactly the same error(s) as with Dbeaver.

I have also tried JetBrains DataGrip and it worked flawlessly. Able to connect, browse the databases and query tables/views. https://docs.microsoft.com/en-us/azure/databricks/dev-tools/datagrip

Either the instructions for Dbeaver and SQL Workbench/J are missing something.
Latest Dbeaver and SQL Workbench/J have bugs or need updates in order to work with Databricks JDBC driver.

Re: SQL Error when querying any tables/views on a Databricks cluster via Dbeaver.

Anonymous — Tue, 02 Aug 2022 01:16:04 GMT

Which java version are you running with those tools?

Try setting the java property io.netty.tryReflectionSetAccessible=true

This may be the issue: https://issues.apache.org/jira/browse/ARROW-7223

Re: SQL Error when querying any tables/views on a Databricks cluster via Dbeaver.

sage5616 — Fri, 12 Aug 2022 19:33:42 GMT

"Which java version are you running with those tools?" - have you seen my original post? It is clearly listed there:

"MacOS version (M1 chip): Monterey 12.4

Java version:

java --version

openjdk 18.0.1 2022-04-19

OpenJDK Runtime Environment Homebrew (build 18.0.1+0)

OpenJDK 64-Bit Server VM Homebrew (build 18.0.1+0, mixed mode, sharing)"

"Try setting the java property io.netty.tryReflectionSetAccessible=true" - can you please provide clear instructions for how to do that? Which file, where?

Also, keep in mind JetBrains DataGrip works just fine without setting any additional java properties. It is just Dbeaver and SQL Workbench/J are the tools that have this problem.

Big thank you 🙂