I’m struggling with how to set a parameter on a subgraph. My current goal is to write out a CSV for several different Oracle tables.
I’ve got a graph that uses a DBInputTable to get the names of the tables I want to dump out. Then I made a subgraph which has a parameter and it writes the contents of the table to a file. This all works except I can’t figure out how to set the parameter.
I got it partially working if I only do one table at a time and I set a dictionary entry before the subgraph and then use that on the Subgraph Input Mapping like this: $DB_TABLE = dictionary.inputtablename;. I want to do multiple tables though and with the dictionary method it only writes out one file even if I have several tables coming from my DBInputTable. I’m not sure why I can’t use $DB_TABLE = $in.0.table_name; in my Input Mapping. When I try that I get Cannot read from input port ‘0’.
You cannot pass a parameter value to a subgraph from an input port exactly due to the fact that there might be more than one records flowing through that edge. Subgraphs are designed for a different purpose than run as many times as how many records are on the input. It will be started only once and then the data flow through it. Parameter values are assigned only once at the start of the subgraph and that’s why it won’t let you to assign a value from input port to a parameter. If I understand your use case correctly, you want to pass multiple strings to the subgraph and let it process them. In this case you should just simply pass these strings to the subgraph through an edge. I’ve attached a simple graph and subgraph, so you can see how to do this.
What I want to do is run transfer data from a table in my Oracle DB to a table in Azure that has the same format. And I want to do this an unknown amount of times.
My idea was to query my Oracle DB to get a list of the tables and then loop through that list somehow and execute the subgraph each time. I am very new to CloverETL so I was trying to get it working without the looping first, but I thought I needed to be able to set a parameter on the subgraph in order to reference it during various DB Input and DB Output operations.
Do you think that there is a way to accomplish what I’m trying to do dynamically using Clover ETL?
My idea was to query my Oracle DB to get a list of the tables and then loop through that list somehow and execute the subgraph each time.
“PBizme”
In simple terms, you can imagine subgraph as a wrapper for your components. Therefore, you’d get the same result as if you’d have connected contents of that subgraph to the dataflow. Once subgraph is initialized, it CANNOT change its configuration.
I am very new to CloverETL so I was trying to get it working without the looping first, but I thought I needed to be able to set a parameter on the subgraph in order to reference it during various DB Input and DB Output operations.
“PBizme”
You’re right. You can set subgraph’s parameter different with each execution. This way though, you’ll be able to do just one table at a time. Not the whole bunch of them at once. Not even loop (which I recommend not to use in graphs at all) would help in this case I’m afraid.
Do you think that there is a way to accomplish what I’m trying to do dynamically using Clover ETL?
“PBizme”
In fact, yes it is. Quite easily, Corporate server is a must to do that. Corporate server provides jobflows, those can be used to execute several graphs with different configurations - basically exactly the feature you’re trying to use with subgraphs. The final setup could look like this (picture with rounded-cornered components is jobflow; yellow component is ExecuteGraph):
