Hi
@Rose_15,
The behavior you are seeing is expected for a result set of this size fetched over a single long-lived cursor. Here is what is happening and several approaches to resolve it.
WHY THE FAILURE OCCURS
The Databricks SQL Connector for Python uses a Thrift-based session to the SQL warehouse. When you call cursor.execute(), the warehouse runs the query and stages the result set. You then pull rows incrementally with fetchmany(). Two things work against you at the 53M-row scale:
1. OAuth token lifetime: The default OAuth access token expires after roughly 60 minutes. Even with proactive refresh, the connector's internal Thrift session may not seamlessly pick up the new token mid-fetch. Once the session is invalidated, the cursor and its associated result set become unrecoverable.
2. CloudFetch presigned URL expiry: When CloudFetch is enabled (the default), the warehouse returns presigned cloud storage URLs for result chunks. These URLs have their own expiration window. If your client cannot download a chunk before its URL expires, you get the "CloudFetch download slower than threshold" warning followed by a hard failure.
The combination of these two factors is why you consistently hit a wall at the 55 to 65 minute mark, and why retrying on the same cursor does not help.
RECOMMENDED APPROACH: PARTITION THE EXTRACT ON THE SERVER SIDE
Rather than pulling 53M rows through a single cursor, break the work into smaller, independently resumable queries. This avoids both the token-expiry and URL-expiry windows entirely.
Option A: Range-based partitioning using a key column
If your table has an integer or date column you can partition on (for example, an id or created_date column), issue multiple queries with WHERE clauses:
from databricks import sql
import os
def get_connection():
return sql.connect(
server_hostname=os.getenv("DATABRICKS_HOST"),
http_path=os.getenv("DATABRICKS_HTTP_PATH"),
auth_type="databricks-oauth"
)
partitions = [
(0, 999999),
(1000000, 1999999),
(2000000, 2999999),
# ... generate ranges to cover the full key space
]
for low, high in partitions:
conn = get_connection()
cursor = conn.cursor()
cursor.execute(
f"SELECT * FROM my_table WHERE id BETWEEN {low} AND {high}"
)
while True:
batch = cursor.fetchmany(50000)
if not batch:
break
write_to_cockroachdb(batch)
cursor.close()
conn.close()
Each partition gets a fresh connection and token, so no single query runs longer than a few minutes.
Option B: Export to cloud storage, then load externally
This is the most robust pattern for very large extracts. Write the data to cloud storage as Parquet files, then load from there into CockroachDB:
Step 1, inside a Databricks notebook or SQL warehouse:
CREATE TABLE my_catalog.my_schema.export_table
USING PARQUET
LOCATION 's3://my-bucket/exports/my_table/'
AS SELECT * FROM my_table;
Or, if you prefer to keep it as a simple write:
df = spark.table("my_table")
df.write.mode("overwrite").parquet("s3://my-bucket/exports/my_table/")
Step 2, from your Python application, read the Parquet files from S3 (using pyarrow, pandas, or DuckDB) and load into CockroachDB. This completely decouples the Databricks session from the downstream insert workload.
ADDITIONAL TUNING IF YOU STAY WITH THE CONNECTOR
If you prefer to keep using the connector directly (for smaller tables or after partitioning), these settings help:
1. Use fetchmany_arrow() instead of fetchmany(). The Arrow-based path is significantly faster for large result sets and reduces serialization overhead:
cursor.execute("SELECT * FROM my_table WHERE id BETWEEN 0 AND 999999")
while True:
batch = cursor.fetchmany_arrow(50000)
if batch.num_rows == 0:
break
# Convert to pandas or process directly
df = batch.to_pandas()
write_to_cockroachdb(df)
2. Increase arraysize on the cursor to improve internal buffering:
cursor.arraysize = 100000
3. If CloudFetch is causing download-speed warnings on your network, you can disable it and fall back to inline result transfer:
conn = sql.connect(
server_hostname=os.getenv("DATABRICKS_HOST"),
http_path=os.getenv("DATABRICKS_HTTP_PATH"),
auth_type="databricks-oauth",
use_cloud_fetch=False
)
This trades off peak throughput for more predictable behavior on constrained networks.
DOCUMENTATION REFERENCES
- Databricks SQL Connector for Python:
https://docs.databricks.com/aws/en/dev-tools/python-sql-connector.html- SQL warehouse configuration (Auto Stop, sizing):
https://docs.databricks.com/aws/en/sql/admin/sql-endpoints.html- COPY INTO for loading data:
https://docs.databricks.com/aws/en/sql/language-manual/delta-copy-into.htmlTo directly answer your closing questions: the connector can handle large result sets, but a single cursor fetch over 53M rows will likely exceed the OAuth/session lifetime. Partitioning the query into smaller ranges (Option A) or staging to cloud storage (Option B) are the two production-proven patterns for this scale.
* This reply used an agent system I built to research and draft this response based on the wide set of documentation I have available and previous memory. I personally review the draft for any obvious issues and for monitoring system reliability and update it when I detect any drift, but there is still a small chance that something is inaccurate, especially if you are experimenting with brand new features.
If this answer resolves your question, could you mark it as "Accept as Solution"? That helps other users quickly find the correct fix.
