review and improvements to sql_database about verified-sources HOT 5 CLOSED

btw. I'm thinking of high performance database cloning with https://github.com/sfu-db/connector-x . we could replace the sqlalchemy with it even now but I'm not sure there will be much difference. it however can load directly to arrow tables and once dlt can process arrow natively I could use it to load data from db to db almost directly.

from verified-sources.

Amazing! Thank you for the detailed feedback. Will work on these.

from verified-sources.

btw. I'm thinking of high performance database cloning with https://github.com/sfu-db/connector-x . we could replace the sqlalchemy with it even now but I'm not sure there will be much difference. it however can load directly to arrow tables and once dlt can process arrow natively I could use it to load data from db to db almost directly.

Nice, I didn't know about this. We could use both together I guess. SQLAlchemy for schema reflection and building queries, and connector-x to run them.

from verified-sources.

there are a few weird things about it:

1. it loads the whole data set and gives you panda frame or pyarrow. so requires more dependencies and more memory and will not work for big datasets
2. only a few sources are supported
3. bigquery credentials are passed as file so we should probably fallback to SqlAlchemy in that case

my take: if you want to experiment with it then great but I'd do it as an option or as a separate pipeline. just my 2c

from verified-sources.

@rudolfix Question about this:

note that you can pass a list of resources/sources/generators to dlt.run method so people can pick their own tables.

This would be passed with calling source().with_resources(...), right?
Can this be handled lazily somehow? I can't find a way to access the resource list from within source.
I'm thinking I need to know which tables are requested before generating the resources dynamically, so this could replace my table_names argument. So something like this where the source is a generator:

@dlt.source
def sql_database(...):
    ...
    # Get arguments passed to `with_resource` from somewhere (if none do full schema reflection)   
    resource_names = get_requested_resources()

    # lazy generate resources
    for table in resource_names:
        yield dlt.source(table_rows ....)(...)

from verified-sources.