pyodbc in Databricks: A retriable error occurred while attempting to download a result file from the cloud store but the retry limit had been exceeded

user_19117454 0
Viewed 139

I have an SQL endpoint in Azure Databricks that I need to query. I have installed the Simba Spark ODCB connector and configured it correctly because when I call the end point with the Python Databricks library it returns the full dataframe (about 900K rows).

from databricks import sql
import pandas as pd

def databricks_to_dataframe():
    with sql.connect(server_hostname="<server host name>",
                    http_path="<http path>",
                    access_token="<access token to databricks>") as connection:
        with connection.cursor() as cursor:
            cursor.execute("SELECT * FROM my_table")
            result = cursor.fetchall()
            df = pd.DataFrame(result)
    return df

When I try to do the same with pyodc, I get the following error:

Error: ('HY000', "[HY000] [Simba][Hardy] (35) Error from server: error code: '0' error message: '[Simba][Hardy] (134) File 31dc6dfe-3a38-4e4a-8f03-66f6efdb4581: A retriable error occurred while attempting to download a result file from the cloud store but the retry limit had been exceeded. Error Detail: File 31dc6dfe-3a38-4e4a-8f03-66f6efdb4581: The result file URL had expired on 1658755584065 (Unix timestamp)'. (35) (SQLFetch)")

Here the code for reference:

import pyodbc
conn = pyodbc.connect("DSN=My_DSN", autocommit=True)
cursor = conn.cursor()
cursor.execute("SELECT * FROM my_table")
data = cursor.fetchall()

When I limit the query to, say to 20k rows it works fine.

And I have the same issue with R (RODBC) but this time no error message at all, just an empty dataframe! Below is the code in R:

library(RODBC)
conn <- odbcConnect("My_DSN")
Data <- sqlQuery(conn, "SELECT * FROM my_table")

Here too, when I limit the query to a few k, it works fine. any ideas? Thanks!

databricks-sql apache-spark-sql pyodbc r python
1 Answers

found the way in python, leaving this here in case someone will need it

I just needed to add EnableQueryResultDownload=0 to the conn like so

import pyodbc
import pandas as pd
conn = pyodbc.connect("DSN=My_DSN;EnableQueryResultDownload=0;",autocommit=True)
cursor = conn.cursor()
cursor.execute("SELECT * FROM my_table")
df = pd.DataFrame.from_records(cursor.fetchall(), columns=[col[0] for col in cursor.description])

in R:

library(RODBC)
conn <- odbcConnect("My_DSN;EnableQueryResultDownload=0;")
Data <- sqlQuery(conn, "SELECT * FROM my_table")
user_191174540
Related