cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
cancel
Showing results for 
Search instead for 
Did you mean: 

SQL connector from databricks-sql-connector takes too much time to convert to pandas

leon
New Contributor II

Hello,

I am using querying my Delta Lake with SQL Connect and later want to explore the result in pandas.

with connection.cursor() as cursor:
        cur = cursor.execute("""
            SELECT DISTINCT sample_timestamp, value, name
            FROM default.raw_delta
            WHERE name in ( 'sensor-1', 'sensor-2, 'sensor-3','sensor-4')
             AND date >= 20200601
             AND date <= 20200731
            ORDER BY name, sample_timestamp
        """)
df = pd.DataFrame.from_records(cur.fetchall(), columns=['sample_timestamp', 'value', 'name'])
display(df)

While the query is really fast ~8s, the conversion to pandas takes almost 2 minutes.

I am running the code in local jupyter and also in databricks notebook, both with same performance.

What might cause the bad performance and is there a way to speed it up?

I also tried fetchall_arrow() but the pandas dimensions got mixed up (rows become columns).

Thanks,

Leon

2 REPLIES 2

Kaniz
Community Manager
Community Manager

Hi @Leon Bam​, Please check this article and let us know if that helps.

leon
New Contributor II

thanks @Kaniz Fatma​  for the reply.

I am using sql.connector and do believe that the spark session is underline, is this config still relevant for sql.connector?

I overcome the fetchall_arrow() issue from my original question and do believe that I am using arrow implicitly now.

However, I don't see much improvements from fetchall and fetchall_arrow

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.