Hi I have some metadata in a project stored as a SQLite data Kedro #beginners-need-help

Hi, I have some metadata in a project stored as a ...

dmb23

10/13/2021, 8:34 AM

Hi, I have some metadata in a project stored as a SQLite database file. In handling that more or less clean, I stumbled upon two questions, and would be curious if there are more elegant solutions: - currently I store the data I recieve e.g. in

data/01_raw/my_metadata.db

, but specify this location in the credentials.yml (which is not the first place I would look if future me has to change something). I think it might also be possible to put it in a globals.yml with a TemplatedConfigLoader, but I did not find a way to include it in a catalog.yml where I would search for it when I want to define SQLQueryDataSet(s) - and in that context: the documentation states that it might be possible (at least for a SQLTableDataSet) to provide the connection string not in credentials, but in the load_args. But both classes SQLTableDataSet and SQLQueryDataSet have a check for credentials[con] that raises a DataSetError when it is not found. Is there some way around that which I am missing? (or should I offer to change the documentation accordingly?)

datajoely

10/13/2021, 1:34 PM

Hi @User I've prototyped things on my end and can maybe describe how I'd set things up

datajoely

10/13/2021, 1:35 PM

The SQL credentials stuff is a bit ham-fisted, it feels even more so when the the database is actually on the local file system to reference things and as such the connection string being in credentials is weird I agree

datajoely

10/13/2021, 1:36 PM

Here is how I would set things up, for reference I used this chinook database available here: https://www.sqlitetutorial.net/sqlite-sample-database/

datajoely

10/13/2021, 1:37 PM

catalog.yml

my SQLite reference is as follows:

Copy code

yaml
chinook:
  type: pandas.SQLTableDataSet
  table_name: artists
  credentials: chinook

datajoely

10/13/2021, 1:37 PM

credentials.yml

I declare the

con

key as follows:

Copy code

yaml
chinook:
  con: sqlite:///${chinook_location}/chinook.db

datajoely

10/13/2021, 1:37 PM

global.yml

I declare the

chinook_location

as a key:

Copy code

yaml
chinook_location: 'data/01_raw'

datajoely

10/13/2021, 1:38 PM

I then switch to

TemplatedConfigLoader

hooks.py

datajoely

10/13/2021, 1:39 PM

We push this credentials pattern as we want to guide users to avoid storing sensitive data explicitly in the catalog

datajoely

10/13/2021, 1:39 PM

we want push users to make sure secrets are managed properly

datajoely

10/13/2021, 1:40 PM

Appreciate it feels a bit like overkill for local unsecured sqlite development, but in general we want to push people to follow software engineering best practice from day 1

datajoely

10/13/2021, 1:40 PM

Does that make sense?

dmb23

10/13/2021, 1:44 PM

ok, that's what I thought. Makes completely sense that a local SQLite file is not the key target for this system!

datajoely

10/13/2021, 1:44 PM

🙂

datajoely

10/15/2021, 5:44 PM

@User not quite the same thing but we spoke about SQLite earlier this week

Previous Next