cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

JDBC Connection query row limit

glevin
New Contributor

Anyone know how to increase the amount of rows returned in a JDBC query? Currently we're receiving 1000 rows per query.

Have tried adding a LIMIT 5000 to the end of the query, but no luck.

6 REPLIES 6

KaranamS
Contributor II

Hi @glevin ,

By default the limit is set to 1000 rows for JDBC queries. You can try specifying the maxRows parameter in the JDBC url to 5000. Example below

jdbc:spark://<databricks-host>:443/default;httpPath=<http-path>;AuthMech=3;maxRows=5000;

Hope this helps!

glevin
New Contributor

Thanks for your response! I just tried your suggestion, but it's still returning 1000 rows.

Here's my full connection string:

jdbc:databricks://adb-xxxx.xx.azuredatabricks.net:443;httpPath=sql/protocolv1/o/xxxx/xxxx;AuthMech=11;Auth_Flow=1;OAuth2ClientId=xxxx;OAuth2Secret=xxxx;ConnCatalog=ndp_uat;ConnSchema=appian;EnableArrow=0;maxRows=5000;

KaranamS
Contributor II

Hi @glevin , No problem, we can try a different approach. Can you please try the fetchsize option as below?

jdbc:databricks://adb-xxxx.xx.azuredatabricks.net:443;httpPath=sql/protocolv1/o/xxxx/xxxx;AuthMech=11;Auth_Flow=1;OAuth2ClientId=xxxx;OAuth2Secret=xxxx;ConnCatalog=ndp_uat;ConnSchema=appian;EnableArrow=0;fetchsize=5000;

glevin
New Contributor

Just tried, same issue, still returns 1000 rows

KaranamS
Contributor II

@glevin , That is unfortunate. Here are few other options you could try

1. Please ensure you are using latest version of the JDBC driver

2. Try setting the fetch size before the query execution instead of the url as below

SET spark.databricks.jdbc.fetchsize = 5000;
SELECT * FROM my_table;

3. If you are open to using Rest API, it would help in getting all the records.

Reference links for API: https://docs.databricks.com/api/workspace/introduction

https://docs.databricks.com/api/workspace/statementexecution/executestatement

SantoshJoshi
New Contributor II

Hi @glevin ,

Can I check what is your data source (Azure SQL, Oracle, MySQL etc.), may be the driver of your source is just picking the default fetch value?

Can you share your profiler output which shows, it's returning 1000 records not 5K as desired?

 

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group