Repository navigation
Connector reads 0 rows although Cluster returned results #383
Description
As the title says, I'm having an issue where the SQL connector, upon executing .fetchall() returns 0 rows, while the Cluster that ran the query returns approx. 151,653 rows - retrieved from cached in 500ms.
with adb_sql.connect(
server_hostname=db_creds_dict["db_name"],
http_path=db_creds_dict["db_path"],
access_token=db_creds_dict["db_token"],
) as conn:
with conn.cursor() as cursor:
if parameters is None:
cursor.execute(query)
else:
cursor.execute(query.format(*parameters))
data = pd.DataFrame(
cursor.fetchall(),
columns=[desc[0] for desc in cursor.description],
)In other situations where the same query returns fewer rows (i.e. 10s to return 28k rows), the connection reads correctly.
Python: 3.8.12
Package Version: 3.1.1
Databricks SQL Warehouse configuration:
- DBSQL v. 2023.50
- Serverless
- X-Small
Activity
benc-db commentedon Apr 1, 2024
@andrefurlan-db thoughts?
kravets-levko commentedon Apr 1, 2024
@pimonteiro
cursor.fetchall()itself is empty, or you checked pandas dataframe and it's empty?cursor.fetchall()actually returns data but dataframe if empty - have you checked what's incursor.description?use_cloud_fetch = falsetoadb_sql.connect(- does it change anything?pimonteiro commentedon Apr 3, 2024
use_cloud_fetch = falseseems to work. From what I know, it should work better with the cloud fetch on, given the amount of rows, correct?kravets-levko commentedon Apr 3, 2024
Thank you @pimonteiro! Yes, CloudFetch should improve handling of very large results. I asked you to disable it to narrow down the scope of the issue. If you're able to get your data with
use_cloud_fetch = false(+ considering that with smaller results everything is fine) - then probably there's some bug in CloudFetch-related code. I need to poke around, and when I'll have other questions - I'll get back to you.P.S. I see you provided library version and warehouse details - which is very nice, thank you! Can you please also tell us if you're using AWS or Azure workspace. Thanks!
pimonteiro commentedon Apr 3, 2024
Sounds good, we will in the meantime try and reduce the query results size, optimize it in order to continue development. I assume there's nothing wrong in, for now, disabling
use_cloud_fetch?And lastly, I'm using Azure Workspace. That should have been on the opening line of the ticket, I do apologize :)
kravets-levko commentedon Apr 3, 2024
Yes, you can just disable CloudFetch and go on. You'll still be able to get large results, just maybe less efficient than with CloudFetch enabled, that's it
pimonteiro commentedon Apr 16, 2024
Any update on this?
akshay-s-ciq commentedon May 2, 2024
Hi, we're facing the same issue. Any update on this?
kravets-levko commentedon May 28, 2024
@pimonteiro @akshay-s-ciq sorry, no much updates for now. I'm still trying to figure this out. However, recently we've got a very similar bug report but for Go driver. But we're still not sure what's going on.
Also, considering all the mentioned above, may I ask you to run same query but using a Nodejs connector? If any of you volunter to help - I can prepare a test project for you
kravets-levko commentedon Jun 14, 2024
@pimonteiro @akshay-s-ciq We have few hypotheses about what may cause your issues. Currently we're checking them, but you may help us. We just released a v3.2.0, which, among the other changes, has more debug logging added. You can help us by using the v3.2.0, enabling debug logging (add
import logging+logging.basicConfig(level=logging.DEBUG)), and when you see your issue again - collect and send us the log output. Thank you so much!P.S. Don't forget to re-enable CloudFetch -
use_cloud_fetch=Truernakshay commentedon Jun 27, 2024
I was also facing the same issue and use_cloud_fetch=False helped.
if it helps, here is what I get when I show debug logging
Python 3.11.9, databricks-sql-connector-3.2.0
kthejoker commentedon Jun 27, 2024
rnakshay commentedon Jun 28, 2024
Hmm, we do use VPN but I am not sure about the details.
In my case, the above issue happens only when I am in Python debug mode. When run end to end this issue doesn't occur.
kthejoker commentedon Jun 28, 2024
rnakshay commentedon Jul 2, 2024
hmm, I don't think so. We use pandas read_sql function with databricks_sql.connect for connection.
For now, this issue is noticed only in local debug runs in my case, I will use the use_cloud_fetch = false option and keep an eye out for any update/response from others. Thank you for your help.
kravets-levko commentedon Jul 2, 2024
Hi guys 👋 First - thank you all for your reports, logs, and any other information you provided on this issue. We also collected other reports from Databricks customers, and this helped to identify several problems in CloudFetch code. Now we do a first attempt to fix what we've found so far - #405. Hopefully, this will fix all the major issues we know about so far. I will let you know when we release this fix. Thank you for patience!
rth commentedon Jul 11, 2024
I can confirm that this replaces the 0 row response by a MaxRetryError for me (see #413) which is better but maybe not optimal.Edit: never mind, this fixes it. The linked issue was for a specific case of combining large queries with SSL verification that had to be disabled.
kravets-levko commentedon Jul 17, 2024
Hello here 👋 We just released v3.3.0 which includes a refactoring of CloudFetch-related code. We think it should fix your issues, so please give it a try and let me know if it helped or not (remember to enable CloudFetch via
use_cloud_fetch=True). If you still see any issues - please enable debug logging (see #383 (comment)) and share log output. Thank you!