Hello! Does anyone know how to properly update an existing data set from the catalog? It is not possible for us to download the whole data set again (from a sql db), so we would love to be able to download only the data after a certain date, which is determined at runtime.

+1 I'd like to know this too

If I understand the situation, you could use the SqlQueryDataset and have a filter statement in the query argument. With that you can use templatedconfigloader in settings.py to inject any string to the filter statement from a cli argument, such as a preferred date

Thanks, I'll look that up

As a simple example with environment variables, you could do this:

# in settings.py
from kedro.config import TemplatedConfigLoader

CONFIG_LOADER_CLASS = TemplatedConfigLoader
CONFIG_LOADER_ARGS = {
    "globals_dict": {'filter_date': os.getenv('FILTER_DATE')},
}

then the query argument in the dataset could look like this

select * from table where dt>${filter_date}

Great, thanks again! I'm going to try that soon

if you check here you can see the docs for templating https://kedro.readthedocs.io/en/stable/kedro.config.TemplatedConfigLoader.html if you have globals_pattern in the config_loader_args then you can put entries in globals.yml if thats preferred, then they get injected to parameters or catalog entries with the

${variable}

pattern

Allright! 🙏

I've been looking at the same thing. We are pulling from some upstream tables that have a long history, and when running our feature engineering, training etc pipelines we only want to grab a certain window of data (last year for example). This isn't a static query and can change with each successive run of the pipeline. What would the current recommended way to do this be? I see two options: 1. Something along the lines of what's discussed in this thread, using hooks to inject this behavior during execution, this feels kind of hacky 2. Extending the customsqlquery dataset to behave in a similar way to partitioneddatasets, evaluating lazily and doing the actual loading in a node which receives as input the loader and can filter using parameters. I like the idea of this but seems like it would be too much work for a "simple" need. Has anybody faced this issue and would be willing to share how they approached it?

I think this is a very valid pitch @Iñigo Hidalgo - the short answer is that it needs careful design as I think it would be non-trivial in execution. My suggestion to you is to build a custom dataset for your purposes and then raise a GitHub issue for the purposes of discussion and potentially adoption in the framework? Happy to help think through the custom dataset design if helpful

I will give it some thought over the next few days. The benefit to effort ratio for my team isn't exactly there for me to dedicate it too much work time, but it's an interesting problem for me personally so might dedicate it some extra time on my own

using sqlalchemy directly likely let us do this programatically.

could you explain what you mean by this? I'm not familiar with the sql dataset implementation at all, haven't gotten round to looking at it just yet, so if I just need to go look at the code tell me that haha

so far we delegate to spark and pandas for reading SQL, but if we’re talking about lazy implementations an ORM may be the right way to do it. In terms of thinking time - raising an issue with some ideas is still a great contribution to the project kedroid

okay, so at the moment it's just a read_sql with the connection string generated from credentials etc?

pretty much - just a thin layer ontop of pandas or the spark jdbc connection

Thanks, I've started to look into possible options. I'm not familiar with SQLAlchemy at all but I'd always heard it was more geared towards CRUD applications rather than Analytics workflows. Is there something about its functionality that draws you to it, vs something simpler like composing queries through string addition? Bc that was my first idea for the lazy evaluation lol

pandas actually uses it as the mechanism to talk to any database too

Interesting, had no idea tbh. Thanks 🙂

❤️

One problem I might have when writing this stuff out is all our projects at work are still on 0.17.1 but as far as I know not much has changed functionality-wise for datasets, right?

no on the whole it should be the same

But I am aware of that and wouldn't want to depend on that behavior

thanks for raising that!

Thanks 🙂 I look forward to you and your team's thoughts when you do get a chance to look at it. I will probably start using this functionality in my pipelines soon (we don't use spark and have simple loading needs)

absolutely

No rush, we will start using it so I guess I'll have some additional thoughts soon

Interesting! We really didn't manage to come up with a satisfying solution within Kedro yet, so I'm very happy to see this!