cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
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

1 REPLY 1

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

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group