This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I have a PostgreSQL database containing multiple schemas with similar tables. I'm trying to import these tables into a workflow separately so I can work with them. The goal is to union them together. BUT the tables do not have the same structure – while a lot of the columns are similar, they're not the same, so because of this, I need to process them first.
Rather than having multiple data import tools that import the tables' data individually, I'm trying to build a macro that can do this for me, especially seeing that one input tool is capable of accessing all the schemas and tables I need.
This macro needs to;
Import data from a specific table within multiple schemas in a postgres database,
Obtain the schema and table names from the output of a SQL query that retrieves them from the information schema.
Dynamically input the data, add the table schema as a new column and output the data to my workflow
Move onto the next schema
Keep going until it has extracted all the tables I need.
Vary the number of outputs depending on whether a filter is applied that specifies which tables to import
The issue I'm running into is it seems macros are designed to union (knit) the data together and output that. It won't even import the data if the table structures differ.
I don't want it to do this. I just want the macro to get me the data from these tables and provide them as separate outputs in a workflow.
So, for the moment, I'm only able to retrieve data from the first table.
I've attached an image of my macro attempt. I can't provide the real thing because that won't work. Hopefully, you can get the jist of what I'm trying to do though.
I've scoured the internet for a solution to this problem. I'm hoping someone here can help.
Unfortunately, there's no way to do what you want. You can't dynamically add output connectors to a macro. This is why the Python and R tools come with 5 output predefined, even though I've never come across a case that used more than 3. The best that you can hope for is to pre-create as many outputs as you think you might need and then direct the data to the correct output according to the iteration number.
Hi @danilang , I'm still having issues getting that macro to work though. Previously I used a dynamic input tool but it doesn't like the tables being different, and throws really strange warnings containing Chinese characters (wtf?).
If I switch that out for static input tool or even a macro input and still get "The field schema for the output "Output" changed between iterations.
Seems it really doesn't like cycling through tables of different sizes.
Consequently I only get the first output, the rest fail.