[Python] Efficient SQL query with pandas using databricks-sql-python

Databricks allows to make SQL queries via an API using the databricks-sql-python package.

There are then two ways of creating a connection object that can be put into a pd.read_sql_query(sql, con=connection). I'm wondering which one is better in terms of performance and reliability when doing SQL queries from pandas:

1. 
   Creating Python DB API 2.0 with
   
   from databricks import sql
   
   
   connection = sql.connect(server_hostname=host, http_path=http_path)
   
   
   this works but produces the following warning,
   
   UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or
   database string URI or sqlite3 DBAPI2
   connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
   
   
   In the implementation code it looks like they are using pyarrow, which sounds to me like an efficient way of creating pandas DataFrames. The warning is a bit dissuasive though.
   
   
2. 
   The other alternative is to use the SQLAlchemy which has a databricks connector exposed by the same package,
   
   from sqlalchemy import create_engine
   
   engine = create_engine(f"databricks://<rest of the URL")
   
   
   This works and gets rids of the warning, but doesn't it imply an extra serialization step to the SQLAlchemy types?
   

Also asked in databricks-sql-python#476

So in general which one should be used with pandas?

Continue reading...