Macro help importing multiple db tables and outputting them separately into a workflow

[Amanda_Breeze]

Hi team,

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;

1. Import data from a specific table within multiple schemas in a postgres database,
2. Obtain the schema and table names from the output of a SQL query that retrieves them from the information schema.
3. Dynamically input the data, add the table schema as a new column and output the data to my workflow
4. Move onto the next schema
5. Keep going until it has extracted all the tables I need.
6. 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.

Cheers, Amanda.

[danilang · Accepted solution]

Hi @Amanda_Breeze 

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.

Dan

[Amanda_Breeze]

Thanks @danilang, I figured I may have been biting off more than I can chew. Your suggested solution should be suitable enough. I just need to manually add connectors as needed.

Thank you for your response.

Cheers,

Amanda.

P.S. is outputting multiple separate datasets into a workflow really that uncommon?

[Amanda_Breeze]

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.

[danilang · Accepted solution]

Hi @Amanda_Breeze 

Set these options in the Interface Designer - Properties in the macro

Dan

[Amanda_Breeze]

Thank you so much for your help @danilang. That works a treat!