Hello All!

I have a python script which utilizes the databricks SQL for python package in order to pull a databricks table into a pandas dataframe which is used to create a table in a Spotfire report. The table contains ~1.28 million rows, with 155 columns, totaling around 140 MB of data.

The script I use to pull the data looks like this:

import pandas as pd
import time
from databricks import sql

#access_token and databricks_table_name are script parameters supplied by the Spotfire data function interface.

server_hostname = "###########.##.azuredatabricks.net"
connection = sql.connect(server_hostname=server_hostname,
    http_path="################",
    access_token = token)
query = f"SELECT DISTINCT Week FROM {databricks_table_name}"
cursor = connection.cursor()

cursor.execute(query)
result = cursor.fetchall()

weeks = [];
for row in result:
    weeks.append(row[0])
cursor.close()

df_list = []
for week in weeks:
    with connection.cursor() as cursor:
        query = f"SELECT * FROM {databricks_table_name} WHERE Week={date}"
        cursor.execute(query)        
        result = cursor.fetchall_arrow()
        temp_df = result.to_pandas()
        df_list.append(temp_df)
        cursor.close()

output_df = pd.concat(df_list)
connection.close()

I am located in the Northeastern United States and the server I'm using is in western Europe, my current assumption is that this is likely what is causing the slow down, but I'm not sure how to prove this. The table is composed of several parquet files joined together. I was able to download these parquet files directly in less than 5 mins when I downloaded them all simultaneously.

I tried using an 2x-small SQL warehouse (our company does not allow us to create our own SQL warehouse computes, and 2x-small is the default they provide to projects), a Standard 15.4 LTS (includes Apache Spark 3.5.0, Scala 2.12) compute (worker and driver had 14 GB Memory, 4 cores, 2 min workers, 8 max workers, with autoscaling), and a larger Standard 15.4 LTS (worker and driver had 32 GB Memory, 8 Cores, 4 min workers, 8 max). I didn't really see a speed difference between those three, but I haven't tried the current chunked approach on the SQL or smaller cluster yet.