PostgreSQL Table Relationships

Hello Knimers! How are you doing?, I hope that everything is going well!

I want to do a Data Base but… well, i’m not too expert in Data Bases but let me explain what i’m tying to do so maybe someone could help me to resolve this issue.

Ok, I’m downloading some data in .txt extension and processing through a workflow in Knime. The Idea is to do the following:

1- Do a ETL Process In Knime
2- Load the result data to a PostgreSQL DB
3- Use the Data Base for Power BI, Power Pivot and Tableau

Ok the ETL Process is done and works great, but i’m having issues to create the data base in PostgreSQL. What I have done having no luck is this:

1 - I’ve Loaded all the tables in PostgreSQL. Loaded the Tables in Power Pivot from PostgreSQL to create a relationship model with Primary and Foreign Key included. Done it but when it comes to reading, create a Pivot Table or Graphic I got the infamous “No sufficient resources” error. I Think is because Excel or Power BI are not programs to create Data Bases so the memory resources they need are really big.

2 - I tried to create the relationships on PostgreSQL, The Primary Keys and Foreign Keys were created in Knime (Concats). The problem is that every time I reset and run the Work Flow the constraints (P.Keys and F.Keys) are being deleted and it is needed to redoing all constraints again. This is because I’m overwriting the tables from Knime and that must be done.

The Data Base is not huge, The Keys are correct (Double Checked on that) also relationships where as a Star Schema so the tables are just simply related to a Fact Table.

The Question is, is there a way to create relationships in a Data Base in Knime? or a way to do not erase the constraints? or is there anything you could advice me to do instead?

Thanks for all your support Knimers!

Regards!

You question mostly DB related. If you plan to overwrite tables then it is not possible to keep constraints. Instead you need to truncate tables and reload them. KNIME can help to do this.
Also, to keep constraints you will need to follow the specific order of truncate and load operations. Or to restore constrains every time.

1 Like

Thank you for your response,

Is there a Node that could help me to truncate and load the tables?

I’m not a DB expert but what I understand is like I have to erase only the data of the table and then append the new data maintaining the table configuration. Is that right?

I really need to Overwrite some tables because those are catalogs, those catalogs change their values in columns but not the ID.

Use Database SQL Executor.
See examples.
https://www.knime.com/nodeguide/data-access/databases/database-advanced-example

1 Like