Update table with Kedro DataCatalog or accesss credentials programmatically

[noklam]

What are you trying to do?

I want to update a certain table with Kedro, which require

1. Delete some rows base on some condition
2. Insert new rows

Trials:

1. Use Kedro DataCatalog API

table:
  type: pandas.SQLQueryDataSet
  sql: delete * from table where x=1
  credentials: oracle

Failed: Kedro DataCatalog API seems does not let you just delete, it expects rows return.

2. Use plain pd.read_sql(), I cannot find a way to access to the credentials.yml

[lorenabalan]

Please see this Stackoverflow thread. Since pandas doesn't support it, the deletion part should be done within a node, where inputs and outputs are sql datasets.
Please consider directing this type of questions to Stackoverflow and tag them with kedro, as we monitor that as well and it's a platform better suited for this purpose.

[noklam]

yes, I end up overwriting ProjectContext to make credentials part of parameters, since the crendentials is not accesible outside of data catalog, not sure if this is the ideal way.

[lorenabalan]

Thanks for getting back to us. Credentials should definitely not be exposed in the parameters as it's sensitive information. The SQLTableDataSet can handle the credentials and loading/saving so no need to access them in the node. The node can just read the table, operate on the dataframe to delete the rows, then write the resulting dataframe back to sql.

[noklam]

@lorenabalan
I see, so your suggestion is instead of doing a "delete" operation in database, I should read the table, delete in pandas, then just overwrite with a new table?

I think it should work for this case, but it may not be an efficient way if I am only updating a small amount of data in a table.

[lorenabalan]

Yup exactly. Depending on the use case it may not be the optimal workflow. I believe Kedro is not very well suited for a sql-only or sql-heavy workflow. I'll leave this thread here for more inspiration: https://www.thetopsites.net/article/58169947.shtml

[lorenabalan]

Closing as answered.