I've been trying to create (since I can't find a downloadable .grf) the graph that is given as the "Data Load" example on this page: http://www.cloveretl.com/examples/transformations, in which daily exchange rates for the Euro are obtained from a website and written to a CSV file. On the example page, there is a Reformat transformation named "Add timestamp", which I assume grabs the current time and adds it to each output row.
Now the XML returned from the website is a little unusual, but it does include the date for the given exchange rates:
The problem is, I have not been able to find a way to extract the time value from the XML and have it included in every output row.
I'm probably overlooking something obvious, but if anyone could point me in the right direction, I'd appreciate it. Is it possible, and if it is, is it possible without sequences (which, for example, are not available in the Community Edition)?
Thanks.
Now the XML returned from the website is a little unusual, but it does include the date for the given exchange rates:
<gesmes:Envelope xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref">
<gesmes:subject>Reference rates</gesmes:subject>
<gesmes:Sender>
<gesmes:name>European Central Bank</gesmes:name>
</gesmes:Sender>
<Cube>
<Cube time="2010-09-13">
<Cube currency="USD" rate="1.2801"/>
...
</Cube>
</Cube>
</gesmes:Envelope>The problem is, I have not been able to find a way to extract the time value from the XML and have it included in every output row.
I'm probably overlooking something obvious, but if anyone could point me in the right direction, I'd appreciate it. Is it possible, and if it is, is it possible without sequences (which, for example, are not available in the Community Edition)?
Thanks.
-
Hello,
the graph presented on the web pages can be accessed from the Welcome Page of CloverETL Designer (see attached picture) or created as the new example project (see CloverETL Examples Project).
To obtain the time directly from the xml file, you need to add another port (for upper Cube tag) and change the mapping as follows:<Mappings>
<Mapping element="gesmes:Envelope">
<Mapping element="Cube">
<Mapping element="Cube" outPort="0" cloverFields="date" xmlFields="time">
<Mapping element="Cube" outPort="1" parentKey="date" generatedKey="date" />
</Mapping>
</Mapping>
</Mapping>
</Mappings>
parentKey="date" informs port 1 to get value from "date" field of port 0
generatedKey="date" informs, that field "date" of port 1 is fulfilled with some value from port 0 -
Thanks for the quick reply. I had looked at parentKey and generatedKey, but misunderstood how they worked (I thought you needed a join function afterwards).
I now have a working graph. However, I came across something that I didn't think was intuitive. If this graph is to work, then both output ports from XMLExtract have to be connected to something. i.e. you can't just connect port1 (currency, rate and date) and ignore port0 (date). If you have just one connector, it always defaults to port0. So I had to connect port0 to trash, and port1 to my output object. I tried renaming port1 to port0, and port1 to port0, and just connect port0, but then date is *not* extracted from port1 and added to port0. Also, once I connect the 2 output ports, it shows a 3rd output port in the graph, even though I haven't defined one. IMHO, the graphical representation should always accurately reflect the graph.
EuroDailyRatesXMLToDatabase.png -
ParentKey and generatedKey (applicable both or none of them) create kind of a binding between a parent element and its child element. Using this keys it is uniquely determined to which parent element a child element belongs to. Moreover, this information is stored in the generatedKey field of child metadata. That is why no join is neccessary here - the time value becomes a part of child metadata. If the original xml file parent element does not have such unique attribute, a Clover sequence needs to be applied.
Regarding the ports, each nested xml element level that is mapped (according to mapping definition) must be sent to its own port. In this case, the <Cube time="2010-09-15"> element is mapped to get the generatedKey value and as we do not need the value any more, it is sent to Trash. That is why two ports are defined.
As it comes to the 3rd empty port displayed, it refers to CloverETL standard behaviour - anytime you connect an edge to a port and additional one appears, it is because the component allows you to connect the newly displayed port to an edge. Only if the number of output/input ports is stable and not changeable (e.g. Trash can have only one input port), no additional port appears. -
Thanks for that information. For those who are interested, I've attached the resulting graph. -
Well, I was going to attach the .grf file, but apparently that's not allowed, so I've inlined it here:
<?xml version="1.0" encoding="UTF-8"?>
<Graph author="James" created="Mon Sep 13 11:02:02 EST 2010" guiVersion="3.0.0" id="1284339893940" licenseCode="community" licenseType="Community" modified="Fri Sep 17 12:25:08 EST 2010" modifiedBy="James" name="EuroDailyRatesXMLToCSV" revision="1.65">
<Global>
<Metadata id="Metadata0" previewAttachmentCharset="UTF-8">
<Record fieldDelimiter=";" name="CurrencyRateDate" previewAttachmentCharset="UTF-8" recordDelimiter="\r\n" type="delimited">
<Field name="currency" type="string"/>
<Field name="rate" type="number"/>
<Field name="date" type="date"/>
</Record>
</Metadata>
<Metadata id="Metadata2" previewAttachmentCharset="UTF-8">
<Record fieldDelimiter=";" name="Date" previewAttachmentCharset="UTF-8" recordDelimiter="\r\n" type="delimited">
<Field name="date" type="date"/>
</Record>
</Metadata>
<Property fileURL="workspace.prm" id="GraphParameter0"/>
<Dictionary/>
</Global>
<Phase number="0">
<Node charset="UTF-8" enabled="enabled" fileURL="${DATAOUT_DIR}\eurofxref-daily.csv" guiHeight="0" guiName="UniversalDataWriter" guiWidth="0" guiX="204" guiY="72" id="DATA_WRITER0" type="DATA_WRITER"/>
<Node enabled="enabled" guiHeight="0" guiName="Trash" guiWidth="0" guiX="348" guiY="12" id="TRASH0" type="TRASH"/>
<Node charset="UTF-8" enabled="enabled" guiHeight="0" guiName="XMLExtract" guiWidth="0" guiX="24" guiY="24" id="XML_EXTRACT0" sourceUri="http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml" type="XML_EXTRACT" useNestedNodes="true">
<attr name="mapping"><![CDATA[<Mappings>
<Mapping element="gesmes:Envelope">
<Mapping element="Cube">
<Mapping element="Cube" outPort="0" cloverFields="date" xmlFields="time" >
<Mapping element="Cube" outPort="1" parentKey="date" generatedKey="date"/>
</Mapping>
</Mapping>
</Mapping>
</Mappings>
]]></attr>
</Node>
<Edge fromNode="XML_EXTRACT0:0" guiBendpoints="" guiRouter="Manhattan" id="Edge0" inPort="Port 0 (in)" metadata="Metadata2" outPort="Port 0 (out)" toNode="TRASH0:0"/>
<Edge fromNode="XML_EXTRACT0:1" guiBendpoints="" guiRouter="Manhattan" id="Edge1" inPort="Port 0 (in)" metadata="Metadata0" outPort="Port 1 (out)" toNode="DATA_WRITER0:0"/>
</Phase>
</Graph>
Please sign in to leave a comment.
Comments 5