How to set a parameter on subgraph?

Support/help with CloverETL implementation problems

PBizme
Posts: 9
Joined: Thu Feb 09, 2017 2:59 pm

How to set a parameter on subgraph?

Postby PBizme » Thu Feb 09, 2017 9:07 pm

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'.

cholastal
Posts: 107
Joined: Tue Sep 01, 2015 1:22 pm

Re: How to set a parameter on subgraph?

Postby cholastal » Mon Feb 13, 2017 4:22 pm

Hi,

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.

Hope this helps,
Attachments
pass_2_sgrf.rar
(2.5 KiB) Downloaded 37 times
---
Lukas Cholasta
CloverCARE Support
CloverETL | Rapid Data Integration

Visit us online at http://www.cloveretl.com
How to speed up communication with CloverCARE support

PBizme
Posts: 9
Joined: Thu Feb 09, 2017 2:59 pm

Re: How to set a parameter on subgraph?

Postby PBizme » Tue Feb 14, 2017 4:38 pm

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?

jandikovae
Posts: 28
Joined: Fri Nov 04, 2016 8:51 am

Re: How to set a parameter on subgraph?

Postby jandikovae » Wed Feb 15, 2017 8:53 am

Hi,

I have noticed that your follow-up post is related to your question in another topic How to do a dynamic insert into a table?.

I am currently working on the issue and will provide you with an update within the other topic.

Thanks.
---
Eva Jandikova
CloverCARE Support
CloverETL | Rapid Data Integration

Visit us online at http://www.cloveretl.com
How to speed up communication with CloverCARE support

svecp
Posts: 25
Joined: Wed Nov 09, 2016 11:51 pm
Location: 2111 Wilson Blvd., Arlington VA 22201
Contact:

Re: How to set a parameter on subgraph?

Postby svecp » Sun Feb 19, 2017 3:02 pm

PBizme wrote: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.


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.

PBizme wrote: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.


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.

PBizme wrote:Do you think that there is a way to accomplish what I'm trying to do dynamically using Clover ETL?


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):
Attachments
listdb.PNG
listdb.PNG (13.79 KiB) Viewed 687 times
dumptables.PNG
dumptables.PNG (11.7 KiB) Viewed 687 times
--
Pavel Švec | CloverETL | Sales Engineer | 2111 Wilson Blvd | Suite 320 | Arlington, VA 22201


cron