Unlocking Snowflake Data Integration in Power Platform: Manipulating Snowflake REST API response in Custom Connector – Part 2

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.

{
  "resultSetMetaData": {
    "numRows": 10,
    "format": "jsonv2",
    "partitionInfo": [
      {
        "rowCount": 10,
        "uncompressedSize": 243
      }
    ]
  },
  "data": [
    [
      "Mohamed Ashiq",
      "27"
    ]
  ],
  "code": "090001",
  ....
}

Update Custom Connector:

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

Reference:

https://learn.microsoft.com/en-us/connectors/custom-connectors/write-code

https://learn.microsoft.com/en-us/connectors/custom-connectors/policy-templates/convertarraytoobject/convertarraytoobject

Summary:

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.

Original Post https://ashiqf.com/2024/03/31/unlocking-snowflake-data-integration-in-power-platform-manipulating-snowflake-rest-api-response-in-custom-connector-part-2/