Sending Json Data From Snowflake into an API using Logic river

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 - we’ll use the Logic river 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 expected the data as json-array format (i.e [{"key": "val"}, {"key": "val2"}]) so there is a need to convert Snowflake’s data inside a table into this particular format.

The use case is determined that you’ve already created the Action River in Rivery and saved it.

Creating the river
At 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 into a variable
  • Step 2: Sending it into the API using the 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, constructs it into json rows using OBJECT_CONSTRUCT function, and 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 is no variables in the logic, we’ll create one using the + button in 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 sending it into our API as a payload of POST request.

So, what we just need to do is creating 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 is excepting to get values inside the {data} variable - and we’ll set the value to be {json_array} variable value.

7 Likes