FX Rate Conversions
回答済みHi,
We are trying to insert exchange rates into a Snowflake table using Open Exchange’s API (https://docs.openexchangerates.org/)
- Currently, the chain looks like Image 1:
- The Open Exchange API outputs a single JSON Object string (Image 2). We are trying to pull the ‘rates’ from this JSON using a Group Node and insert them into a Snowflake table via an ‘Execute Query’ command.
- We’d like to know if this is possible with the JSON Object that Open Exchange provides.
- Snowflake Query looks like (Image 3)
Image1
Image2
Image 3
-
正式なコメント
Hi Todd Choi,
I played around a bit more with your issue today, here is what I was able to narrow it down to at the simplest. I hope you find the level of detail helpful. Also I have gone ahead and migrated a legacy article over to Workiva Support under the following URL to assist in this chain build: https://support.workiva.com/hc/en-us/articles/4416658911124 The section that will be most helpful is the UNPIVOT command.
- In my example, I used the Convert JSON to CSV command under the Tabular Transformation connector and connected it to the HTTP-GET command.
- From there, I configured it to only use the RATES object from the HTTP-GET.
- When executed, this produces the results like below
🔌 Tabular Transformation
⚙️ JSON to CSV Configuration:
Input File: CXRUZCG2P76GN2VV6N42EONBGU.json
Output File:
Preview result: true
Command description: Converts a JSON array of objects to a CSV file
======== START PREVIEW ========
AED,AFN,ALL,AMD,ANG,AOA,ARS,AUD
3.672538,66.809999,125.716501,484.902502,1.788575,135.295998,9.750101,1.390866
======== END PREVIEW ========
Result: Success - Looking forward at the UNPIVOT command; it will need a header field. So I've also added a custom Dynamic Output to the CONVERT JSON TO CSV command. This will pull the first row out of the converted file that we can use automatically as a header; and will be dynamic as the rates grow.
- Next I added an UNPIVOT command to the pallet and connected it to the previous command.
- I configured the command as follows:
The HEADER has the following transformation applied:
- The final output is a file you can now convert to JSON and iterate over and use to insert records into your SQL for SnowFlake.
🔌 Tabular Transformation
⚙️ Unpivot File Configuration:
Aggregation: none
Data Column: Data
Data Header Names: ["AED","AFN","ALL","AMD","ANG","AOA","ARS","AUD"]
Delimiter: ,
Ending Data Column Index:
Ending Data Column:
Input File: 90685428_converted_1651351221947.csv
New Column: CurKey
Preview result: true
Starting Data Column Index:
Starting Data Column:
Command description: Unpivot carries out almost the reverse operation of Pivot, by rotating columns into rows
======== START PREVIEW ========
CurKey,Data
AED,3.672538
AFN,66.809999
ALL,125.716501
AMD,484.902502
ANG,1.788575
AOA,135.295998
ARS,9.750101
AUD,1.390866
======== END PREVIEW ========
Result: Success
Hi Todd Choi,
What you are looking to do is possible. It appears how ever you configured your Group Node is incorrect. I would start there. Since you have not included details I have no input to provide.
Thank you,
Wayne0
Hi Wayne,
Really appreciate your help on this!
So I believe I got the Group Node to work in a very roundabout fashion (Image 1). As you can see, I utilized the 'Object to CSV' command to get specific FX rates from Open Exchange's API. Then used a 'CSV to JSON' command to pass through the Group Node.
I was wondering if there was a way to automatically iterate through all of the FX rates without defining the 'path' and having to individually select the currencies.
When I try to connect the Group Node directly to the GET command (Open Exchange API), I do not see any options to iterate through the JSON object that contain the rates (Image 2)
Image 1
Image 2
Thanks,
Todd
0
Hi Todd Choi,
While I can visually see what you have put on the pallet, I cannot technically see why you are putting them on the pallet in that order. Nor can I see what actions you have configured each command to take. The missing information is making this a bit difficult to assist. There are some legacy documents over at https://help.onecloud.io/en/articles/4684749-onecloud-for-transformations-quick-start that may be able to assist.
Thank you,
Wayne0
Hi Wayne,
I came across this documentation as well, but I don't think this would resolve the issues we are having.
Open Exchange's API outputs the following JSON object. We are trying to only extract the 'rates' from this data:
As you can see, this is JSON nested object (data surrounded by curly brackets { }). However, all of the documentation I've come across deals with nested arrays and objects.
Is there a way to transform Open Exchange's JSON output to look something similar to the image below? Correct me if I'm wrong, but I believe this format is the best way to automatically retrieve the rate's 'ID' and 'Values'.
Again, thank you for your help and feedback!
-Todd
0
Hi Todd Choi,
Thanks for providing some additional information. I don't believe you need to edit the file for what you are attempting to do. My suggestions would be to take the data out of the rates object you have identified with the Handlebars command. You can render a more proper text or the SQL syntax to execute that way. Handle bars is a different engine for processing JSON objects of this fashion. It also allows you to actually reference and write out the key as if it were a value as you are needing.
Within the same legacy help center (https://help.onecloud.io) there are several articles there as well.
Thank you,
Wayne1サインインしてコメントを残してください。
- In my example, I used the Convert JSON to CSV command under the Tabular Transformation connector and connected it to the HTTP-GET command.
コメント
6件のコメント