Sending json data from DWH engine into an API
Rivery is an ingestion and transformation tool to DWH engines in the cloud, but in some cases, there is a need to send data from your DWH into an external or internal API.
In this case - Logic rivers to the rescue!
Use Case
In the next example, we’ll take a Snowflake table, and send its data into an external API. Our API expects the data in a json-array format (i.e [{“key”: “val”}, {“key”: “val2”}]) so we first need to convert our Snowflake data into this particular format.
In this case, we are assuming that you’ve already created an Action River in Rivery and saved it.
Creating the river
First, let’s create a Logic river, by going into +Create New River button -> Logic.
Our Logic will be assembled by 2 steps:
- Step 1: Querying the data, and saving the results into a variable.
- Step 2: Sending data to the API using an Action River.
Querying the data
Create new SQL - Snowflake step in the Logic river.
In the Source section, we’ll enter a currency rates query that takes the entire table data and constructs it into json rows using OBJECT_CONSTRUCT function. We’ll then create a json-array from the result, using the ARRAY_AGG function.
The query we used is:
select ARRAY_AGG(json_row) from ( select OBJECT_CONSTRUCT(*) as json_row from RIVERY_DEMO.PUBLIC.CURRENCIES) q
In the target section, we’ll set the target to be Variable and set the variable name we want. If there are no variables in the logic, we’ll create one using the + button to the right.
Sending the data into an external API
In the next logic step, we want to send the data that was saved in the variable into an external API. In this example, we’ve already created an Action river that gets the data into a {data} variable and sends it into our API as a payload of POST request.
So, what we need to do next is create an Action logic step.
We’ll choose the action we’ve created in the Rest Action River dropdown.
Under the Input Variables we can see that the Action river expects to get values inside the {data} variable - and we’ll set the value to be {json_array} variable value from the previous step.
