ENH: Pluggable SQL performance

xhochy commented 4 years ago

Currently the pandas SQL logic is using SQLAlchemy with results being returned as Python objects before being converted to a DataFrame. While the API is simple, it doesn't have good performance characteristics due to the intermediate Python objects. There exist currently some faster alternatives with inconsistent and more complicated APIs.

In addition to not having a uniform API, these implementations are only concerned about fast result en-/decoding. Functionality like automatic table creation as we have in pandas.DataFrame.to_sql doesn't exist there.

Thus it would be nice to have a way to use these connector implementations behind the standard pandas API.

Faster alternatives

bcpandas: Use BCP to insert data into MS SQL Server
turbodbc: Fast access to databases which have an ODBC driver via Apache Arrow (fetchallarrow().to_pandas()), e.g. MS SQL or Exasol.
snowflake-connector-python: Brings native Apache Arrow to speed via fetch_all_pandas()
pyarrow.jvm / JDBC: Use pyarrow's JVM module to get faster access to JDBC results via Arrow
postgres-copy-arrow-decode: Not yet opensourced (shame on me): Cython-based encoder/decoder for Postgres' COPY BINARY command that decodes Postgres' binary protocol from/to Arrow. Works together with psycopg2 and gives roughly a 2x speedup and type stability on the COPY CSV method in pandas's docs.
PostgresAdapter: NumPy support for Postgres connections
d6tstack: Fast insert into Postgres/MySQL/MSSQL via CSV files

General implementation idea

pandas users should only deal with read_sql and to_sql in its current fashion.
There shouldn't be any new hard dependencies in pandas.
The SQLAlchemy engine is a nice uniform interface to specify a database connection, keep this.
We only need a limited set of operations implemented by the performance backend, basically to_sql(DataFrame) and read_sql(query) -> DataFrame. Table creation, index adjustment and further convenience functionality can still be handled by the high-level SQLAlchemy layer.

Implementation idea (1) – Dispatch on `type(engine.raw_connection().connection)`

SQLAlchemy exposes the underlying connection of the database driver via engine.raw_connection(). This is a useful way to detect how we connect to the database. We could provide a registry where each backend implementation provides a function supports_connection(engine.raw_connection().connection) -> bool to determine whether it can be used.

Pro:

Users doesn't need to change their code. If the backend is loaded, they will automatically get the speedup.

Con:

Users need to take care that the backend is loaded, otherwise queries will work but stay slow.
Only one implementation per database connection class can be implemented

Implementation idea (2) – Extend the `method=` param

pandas.DataFrame.to_sql already has a method parameter where the user can supply a callable that is used to insert the data into the Database. Currently the callable gets a row-iterator and not a DataFrame. Thus this interface is already hard-wired that the intermediate result needs to be converted into Python objects. Instead of providing a row-iterator, we could pass the original DataFrame to this method

Pro:

Clear control on which method is used
Backend implementations could be used via method=turbodbc.pandas.to_sql

Con:

Potentially a breaking change on the method paramter or a second parameter needs to be added that is doing nearly the same things is introduced.
Needs explicit usage for the speedup.

Implementation idea (3) - Introduce `engine=` param

As we have with the the Parquet and CSV IO implementations, we could also go for providing an engine parameter where users could easily switch based on the name of an implementation. A prototype implementation would look like:

import pandas as pd

class DatabaseEngine:

    name = "fastengine"

    @staticmethod
    def supports_connecton(connection) -> bool:  # for engine="auto"
        return isinstance(connection, FastConnection)

    def to_sql(engine, df: pd.DataFrame, table: str):
        …

    def from_sql(engine, query: str) -> pd.DataFrame:
        …

pd.register_sql_backend(DatabaseEngine.name, DatabaseEngine)

Pro:

In contrast to (1), here you would get an error when the backend was not loaded.
Clear control on which method is used.
User doesn't need to provide the exact function but only the name of the engine
We could provide an engine="auto" setting which on explicit usage tries to find a matching backend and will otherwise fallback to the plain SQLAlchemy implementation.
We can provide some of these engines as part of pandas, others can come from third-party libraries.

Con:

Needs explicit usage for the speedup.

Personally, I would prefer this approach.

Related issues

Use Turbodbc/Arrow for read_sql_table, would be fixed by this proposal
Faster SQL implementation in d6stack library, would be fixed by this proposal
Improve type handling in read_sql and read_sql_table, related to this proposal but won't be fully fixed

jreback commented 4 years ago

+1 on the engine= idea (3) (though the 'auto' would need some thought / hints). This is in-line with other ways of selecting backends and performance.

TomAugspurger commented 4 years ago

Happy to have improvements here, and I trust your judgement on the best API for users. The engine proposal sounds reasonable.

yehoshuadimarsky commented 3 years ago

+1 on the idea, but my only question is wouldn't this in effect be the Pandas project taking a stance on which "side" or extension projects they like and which they don't? With so many alternatives out there (as @xhochy specified in the original post), how to pick what gets included? As opposed to read_csv for example where the only options for engine are Python or C, and we don't have to take a stance on the worthiness of other projects

yehoshuadimarsky commented 3 years ago

I'd be happy to work on this PR though, I have a fair amount of experience with the Pandas <> SQL interface and backend code. But would want more feedback from the core maintainers first if you think this is worth the time and effort, and that it will get merged.

Full disclosure - I'm the author of one of the libraries mentioned (bcpandas).

jreback commented 3 years ago

+1 on the idea, but my only question is wouldn't this in effect be the Pandas project taking a stance on which "side" or extension projects they like and which they don't? With so many alternatives out there (as @xhochy specified in the original post), how to pick what gets included? As opposed to read_csv for example where the only options for engine are Python or C, and we don't have to take a stance on the worthiness of other projects

well we would have to start somewhere - you can be the first!

we don't need to take a stance per se - would likely accept any compatible and well tested engines

we did this for excel reading for example and now have a number of community supported engines

yehoshuadimarsky commented 3 years ago

Ok, good point.

As far as I see it, we will go with implementing option 3 - specifying an engine option.

Regarding implementation, the prototype by @xhochy is great, and I see we already have a base class for this in the SQL module that currently only supports 2 subclasses - SQLAlchemy and SQLite. So all we would have to do is create more subclasses to implement this base class. Do you agree?

https://github.com/pandas-dev/pandas/blob/613f098bfbe2aff9b69c887814946bfe9eb91660/pandas/io/sql.py#L1105-L1120

Also I would heavily ~copy/paste~ borrow from the Parquet module, including the tests.

yehoshuadimarsky commented 3 years ago

take

xhochy commented 3 years ago

I think most of the implementations would subclass from the SQLAlchemy engine again. We would like to reuse the table (re)creation routines and similar convenience patterns from it and only overload the actual "data retrieval" / "data push" part.

yehoshuadimarsky commented 3 years ago

On second thought, not sure I'm equipped to tackle this. Have never used any of the engines proposed other than bcpandas and that's not even its own engine

AbhayGoyal commented 3 years ago

Hey, I have also never used the engines but would be happy to startup. Would really need your help here.

yehoshuadimarsky commented 3 years ago

Hey, I have also never used the engines but would be happy to startup. Would really need your help here.

Open to working together on this if you want

AbhayGoyal commented 3 years ago

Open to working together on this if you want

I guess we should start with SQLAlchemy right?

erfannariman commented 3 years ago

Happy to help as well if you guys need more hands.

xhochy commented 3 years ago

Open to working together on this if you want

I guess we should start with SQLAlchemy right?

You could refactor some things out in the current SQLAlchemy code so that you have places where an engine could easily hook in. For example for bcpandas, you would only want to overwrite the "to_sql" hook, thus this would be a nice start for an engine.

yehoshuadimarsky commented 3 years ago

@xhochy will this code in BCPandas mess things up with circular imports?

https://github.com/yehoshuadimarsky/bcpandas/blob/481267404bdb1508a98205a506c3390f9ac5de64/bcpandas/main.py#L14-L15

(not sure why it's not rendering the preview snippet inline)

xhochy commented 3 years ago

No that shouldn't be a problem. In the final implementation, I would not expect that pandas would depend on bcpandas or that bcpandas needs to be imported to use it as an engine.

Personally, I would like to see the use of Python's entrypoint mechanism as a way to declare possible engines. https://amir.rachum.com/blog/2017/07/28/python-entry-points/ is a good introduction for that topic and how it could be used. With that you could define in the package metadata possible engines that pandas can detect without the need for circular imports.

proinsias commented 3 years ago

@yehoshuadimarsky & @AbhayGoyal – do you have what you need to make progress with this?

yehoshuadimarsky commented 3 years ago

@yehoshuadimarsky & @AbhayGoyal – do you have what you need to make progress with this?

Yes - not sure where to start.

I can try implementing a bcpandas engine option like we do in the Parquet part, but would be tricky without ripping out some of the old SQLAlchemy stuff. If the tests all pass, then is it ok?
Also, I'm not clear on @xhochy's response on how bcpandas won't be a circular import.
Finally, I don't have any exposure to the other engines like turbodbc

yehoshuadimarsky commented 3 years ago

Started work on this here https://github.com/yehoshuadimarsky/pandas/tree/sql-engine.

So far, mostly just refactored the SQLAlchemy parts to make an entry point for other engines, and got the existing test suite to pass on my machine.

jreback commented 3 years ago

smaller / refactoring PRs are good to push separately

yehoshuadimarsky commented 3 years ago

smaller / refactoring PRs are good to push separately

Good idea - just pushed a PR as a first step to refactor the existing code, before adding new engines. Will add bcpandas in a subsequent PR once this is approved.

yehoshuadimarsky commented 3 years ago

Almost done with first part, just stuck on a CI testing failure - anyone able to help? https://github.com/pandas-dev/pandas/pull/40556#issuecomment-821221567

xhochy commented 3 years ago

@yehoshuadimarsky I'll take a look in the next days!

yehoshuadimarsky commented 3 years ago

@yehoshuadimarsky I'll take a look in the next days!

Any luck @xhochy?

xhochy commented 3 years ago

@yehoshuadimarsky I'll take a look in the next days!

Any luck @xhochy?

Sorry, done now!

pandas-dev / pandas