Parameterizing Several Lists to Build a URL

Support/help with CloverETL implementation problems

KMac21
Posts: 20
Joined: Thu Apr 28, 2016 6:03 pm

Parameterizing Several Lists to Build a URL

Postby KMac21 » Tue Feb 21, 2017 9:27 pm

I am trying to build a URL to pass into the HTTP Connector in a graph. A couple parts of the URL will be generated by a list. One of these parameters is a game app ID that I tested by creating a text file with a list of the game app IDs in the data-in folder, used a UniversalDataReader in a jobflow which reads the entries of the text file and passes them to an ExecuteGraph component, and created an empty parameter for game app ID in the graph to iterate over. This works as expected and iterates over each game app ID. Passing in another parameter to iterate over per game app ID is where I am having trouble executing.

For every game app ID there is a list of KPIs that I also want passed into the URL. The KPI list is the same for each game app ID. Here is an example of what I would like to happen:
http://www.website.com/dashboard/${kpi}.html?game_app_id=${game_app_id}

So, for game_app_id #1 I expect:
http://www.website.com/dashboard/kpi1.html?game_app_id=game1
http://www.website.com/dashboard/kpi2.html?game_app_id=game1
http://www.website.com/dashboard/kpi3.html?game_app_id=game1

For game_app_id #2 I expect:
http://www.website.com/dashboard/kpi1.html?game_app_id=game2
http://www.website.com/dashboard/kpi2.html?game_app_id=game2
http://www.website.com/dashboard/kpi3.html?game_app_id=game2

The code should iterate over the game and for each game iterate over a KPI list.

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

Re: Parameterizing Several Lists to Build a URL

Postby jandikovae » Wed Feb 22, 2017 10:39 am

Hi,

If I understand you correctly, you need to create URLs for every possible combination of two lists of "parameters" (one list is "KPIs" and another is "games"), right? In fact, to achieve that by using Jobflow with parameters, you would actually need a Jobflow in another Jobflow.

However, I believe that there is another approach you should consider. If you prepare the input data first, you can then create the URLs in the same graph. In order to do that you can use a component called CrossJoin. This component will join KPIs and Games to a single table with two columns and creates every possible combination of them (joins each KPI with each game). Then you can add a component Reformat with a Transform function that would look as follows:

Code: Select all

$out.0.URLpath=concat("http://www.website.com/dashboard/",$in.0.KPI,".html?game_app_id=",$in.0.game);


The output of this Reformat component will be a list of URLs that you can then pass to HTTP connector component. I am attaching a graph for review. Please give this a try and let me know if it meets your requirements.

Thanks Eva
Attachments
KPI-game.grf
graph
(3.29 KiB) Downloaded 17 times
games.csv
input data
(21 Bytes) Downloaded 16 times
kpis.csv
input data
(18 Bytes) Downloaded 15 times
---
Eva Jandikova
CloverCARE Support
CloverETL | Rapid Data Integration

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

KMac21
Posts: 20
Joined: Thu Apr 28, 2016 6:03 pm

Re: Parameterizing Several Lists to Build a URL

Postby KMac21 » Wed Feb 22, 2017 7:55 pm

That worked beautifully! Thank you! I have one last question. Because I was using parameters before I was writing the output file from the HTTPConnector as ${game}_${kpi}.json, but now that I am not using parameters so how can I get the same result?

KMac21
Posts: 20
Joined: Thu Apr 28, 2016 6:03 pm

Re: Parameterizing Several Lists to Build a URL

Postby KMac21 » Thu Feb 23, 2017 6:34 pm

Here is what I tried that has not worked:

In the HTTPConnector in Output Mapping:

Code: Select all

function integer transform() {
   $out.0.content = $in.1.content;
   $out.0.FileName = concat($in.0.game,"_",$in.0.kpi);

   return ALL;
}


I am connecting the HTTPConnector to the UniversalDataWriter component with the following settings:
File URL: ${DATAOUT_DIR}/#.txt
Exclude Fields: FileName
Partition key: FileName

The result I am getting is:
1.txt
2.txt
3.txt
etc.

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

Re: Parameterizing Several Lists to Build a URL

Postby svecp » Thu Feb 23, 2017 6:42 pm

There's probably one additional attribute which needs to be set:

Partition file tag = Key file tag
--
Pavel Švec | CloverETL | Sales Engineer | 2111 Wilson Blvd | Suite 320 | Arlington, VA 22201

KMac21
Posts: 20
Joined: Thu Apr 28, 2016 6:03 pm

Re: Parameterizing Several Lists to Build a URL

Postby KMac21 » Thu Feb 23, 2017 7:15 pm

So I am almost there! I added the setting "Partition file tag: Key file tag" to the UniversalDataWriter properties while keeping the other settings mentioned above. This works and gives me the file with the correct file name, but it also gives me duplicate files of everything except that they are named i.e. 0.txt1, 0.txt2, 0.txt3, etc. How can I make it so I am not getting these duplicate files while keeping the files that product the correct name?

KMac21
Posts: 20
Joined: Thu Apr 28, 2016 6:03 pm

Re: Parameterizing Several Lists to Build a URL

Postby KMac21 » Thu Feb 23, 2017 7:52 pm

The setting "Partition file tag: Key file tag" worked. The reason I thought I was getting duplicate files was that even though I removed them from designer it had not completely synced with the server and the files showed up again so I thought they were being duplicated. Thanks for the help all!

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

Re: Parameterizing Several Lists to Build a URL

Postby svecp » Thu Feb 23, 2017 11:34 pm

Glad we could help :-)
--
Pavel Švec | CloverETL | Sales Engineer | 2111 Wilson Blvd | Suite 320 | Arlington, VA 22201