In both Part 1 and Part 2 of this blog series, we explored integration techniques using the Snowflake REST API through a custom connector. There is a Snowflake connector in Preview for an extended period, featuring actions for submitting, monitoring status, and cancelling SQL statements. In this post, we’ll delve into how you can leverage this connector within a Power Automate flow to execute a SQL statement
There is a prerequisite to be done within the Snowflake environment by executing the following SQL script to make modifications to the security integration created in the Part 1 of the blog series.
ALTER SECURITY INTEGRATION connector
SET EXTERNAL_OAUTH_AUDIENCE_LIST = ('https://analysis.windows.net/powerbi/connector/snowflake', 'api://8a3865da-e301-46c6-801f-7417a75a8271');
The OOB Snowflake connector uses Power BI Analysis service API.
Enter the necessary details related to the Snowflake instance you intend to connect to and the SQL statement you aim to execute, you are prepared to test the connector.
The Select action is make the data transformation.
Hope you have found this informational & thanks for reading. If you are visiting my blog for the first time, please do look at my other blogposts.
Do you like this article?
Subscribe to my blog with your email address using the widget on the right side or on the bottom of this page to have new articles sent directly to your inbox the moment I publish them.
In Part 1 of the blog series, we covered the setup and configuration necessary for Snowflake integration using the Snowflake REST API with a custom connector. The API response isn’t directly usable in Power Apps if the action is directly called within Power Apps instead of Power Automate flow leveraging Data operations (Select) connector. In this blog post, let’s explore how to manipulate the Snowflake API response using custom code with C# to transform the response payload. Find below the response from the API, without transformation, appears as follows for the SQL statement select name, age from rockers_table.
Revise the custom connector created in the previous blog post by clicking “Code” to add the C# code for transforming the data. The required response to the SQL query is under the key named data, and the value before and after transformation, is shown below.
The following C# code transforms the response in the required format:
public class script: ScriptBase
{
public override async Task < HttpResponseMessage > ExecuteAsync()
{
// Check which operation ID was used
if (this.Context.OperationId == "GETSFData")
{
return await this.ManipulateResponse().ConfigureAwait(false);
}
// Handle an invalid operation ID
HttpResponseMessage response = new HttpResponseMessage(
HttpStatusCode.BadRequest
);
response.Content = CreateJsonContent(
$"Unknown operation ID '{this.Context.OperationId}'"
);
return response;
}
private async Task < HttpResponseMessage > ManipulateResponse()
{
// Use the context to forward/send an HTTP request
HttpResponseMessage response = await this.Context.SendAsync(
this.Context.Request,
this.CancellationToken
).ConfigureAwait(continueOnCapturedContext: false);
// Do the transformation if the response was successful
if (response.IsSuccessStatusCode)
{
var responseString = await response.Content.ReadAsStringAsync().ConfigureAwait(
continueOnCapturedContext: false
);
// Example case: response string is some JSON object
var result = JObject.Parse(responseString);
// Initialize an empty array to store the new formatted data
var newDataArray = new JArray();
// Iterate over the original "data" array
foreach (var item in result["data"])
{
// Create a new JObject for each inner array
var newItem = new JObject
{
["Name"] = item[0], // Set the "Name" property
["Age"] = item[1] // Set the "Age" property
};
// Add the new JObject to the new data array
newDataArray.Add(newItem);
}
// Create a new JObject to hold the formatted data
var newResult = new JObject
{
["data"] = newDataArray // Set the "data" property to the new formatted data array
};
response.Content = CreateJsonContent(newResult.ToString());
}
return response;
}
}
In the Power Apps, the PowerFX to execute the SQL query is
Set(sfData, 'CC-SnowFlake-AWS'.GETSFData({database: "HOL_DB",role:"PUBLIC",schema:"PUBLIC",statement:"select name, age from rockers_table;",warehouse:"HOL_WH"}))
To display the value in the gallery control, the Power FX is sfData.data
The scenario provided above serves as just one example of data transformation. While Power Automate offers data operations that can assist in such tasks, the method described above facilitates using the connector action directly within Power Apps. Hope you have found this informational & thanks for reading. If you are visiting my blog for the first time, please do look at my other blogposts.
Do you like this article?
Subscribe to my blog with your email address using the widget on the right side or on the bottom of this page to have new articles sent directly to your inbox the moment I publish them.