Use Azure Cognitive Services with Snowflake External Functions
This blog will show you how to use Snowflake External Functions to interact with Azure.
1. Create Cognitive Services in Text Analytics
To begin, follow the steps below to create text analytics.
Step 1: Select Create a resource, and then go to AI + Machine Learning > Text Analytics. Or go to Create Text Analytics
Step 2: Enter all the required settings
Step 3: Select Create and wait for the resource to be created. Your browser automatically redirects to the newly created resource page.
Step 4: The created text analytics is visible under text analytics tab. bi3mlpoc is the text analytics created.
Collect the configured endpoint and an API key:
Since Snowflake cannot call the function app URL directly, we need to create an API gateway as a proxy.
2. Create API Gateway
Follow the steps below to create an API gateway.
Step 1: From the Azure portal menu, select Create a resource. You can also select Create a resource on the Azure Home page.
Step 2: On the New page, select Integration > API Management.
Step 3: In the API Management service page, enter settings.
Step 4 : Select Create.
On the API Management services page, select your API Management instance.
3.Create Function App
To create and deploy Function App through Visual Studio follow below steps.
Step 1: Choose the Azure icon in the Activity bar, then in the Azure: Functions area, select the Create new project… icon.
Step 2: Choose a directory location for your project workspace and choose Select.
Note: These steps were designed to be completed outside of a workspace. In this case, do not select a project folder that is part of a workspace.
Step 3: Provide the information for the prompts
Create a new file and enter the below code. This C# code will trigger bi3mlpoc text analytics in cognitive services.
using System;
using System.IO;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using System.Globalization;
using Azure.AI.TextAnalytics;
using Azure;
using System.Collections;
using System.Collections.Generic;namespace Company.Function.SentimentAnalysis
{public class responsedata
{
public ArrayList data {get; set;}
}public class Root
{
public List<List<object>> data { get; set; }
}public static class SentimentAnalysis
{
private static readonly AzureKeyCredential credentials = new AzureKeyCredential(“8dxxxsdaxxxxxxx46a1e2”);
private static readonly Uri endpoint = new Uri(https://bi3mlpoc.cognitiveservices.azure.com/);
[FunctionName(“SentimentAnalysis”)]
public static async Task<IActionResult> Run(
[HttpTrigger(AuthorizationLevel.Anonymous, “get”, “post”, Route = null)] HttpRequest req,
ILogger log)
{responsedata rd = new responsedata();
var client = new TextAnalyticsClient(endpoint, credentials);
string name = req.Query[“name”];
string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
Root myDeserializedClass = JsonConvert.DeserializeObject<Root>(requestBody);
int i= 0;
var overallarray = new ArrayList();
var rowArray = new ArrayList();
var columnArray = new ArrayList();
string inputText = string.Empty;foreach(List<Object> datarow in myDeserializedClass.data )
{rowArray = new ArrayList();
columnArray = new ArrayList();
rowArray.Add(datarow[0]);
inputText = datarow[1].ToString();
DocumentSentiment documentSentiment = client.AnalyzeSentiment(inputText);
columnArray.Add(JsonConvert.SerializeObject(documentSentiment));
rowArray.Add(columnArray);
overallarray.Add(rowArray);
}rd.data= overallarray;
return new OkObjectResult(rd);
}
}
}
Run the function locally
i. To call your function, press F5 to start the function app project. Output from Core Tools is displayed in the Terminal panel. Your app starts in the Terminal panel.
ii. With Core Tools running, go to the Azure: Functions area. Under Functions, expand Local Project > Functions. Right-click (Windows) or Ctrl — click (macOS) the HttpExample function and choose Execute Function Now
iii. Press Ctrl + C to stop Core Tools and disconnect the debugger.
Sign into Azure
If you aren’t already signed in, choose the Azure icon in the Activity bar, then in the Azure: Functions area, choose Sign in to Azure
Publish the project to Azure
Choose the Azure icon in the Activity bar, then in the Azure: Functions area, choose the Deploy to function app… button.
ii. Provide the following information at the prompts:
-> Select folder
-> Select subscription
-> Choose Create New Function App
-> Enter a globally unique name for the function app
-> Select a location for new resources
The extension shows the status of individual resources as they are being created in Azure in the notification area.
A notification is displayed after your function app is created and the deployment package is applied.
Tip: By default, the Azure resources required by your function app are created based on the function app name you provide. By default, they are also created in the same new resource group with the function app. If you want to either customize the names of these resources or reuse existing resources, you need to instead publish the project with advanced create options.
2. Select View Output in this notification to view the creation and deployment results, including the Azure resources that you created. If you miss the notification, select the bell icon in the lower right corner to see it again.
4. Add API to created Function App
Step 1: Click on the created service, In the API tab click on Function App as shown below,
From the left menu of the APIs window, select APIs, then select Add from the top menu.
Step 2: From the Add APIs window, select the Function App template.
This will show a pop up, click on browse to choose function app, and select the deployed function app.
5. Create Snowflake API integration
Create API integration in snowflake as below,
create or replace api integration SNOWFLAKE_SAFEGUARD_NONPROD_INTEGRATION_1
enabled = TRUE
API_PROVIDER=azure_api_management
API_KEY ='aaf288xxxx2chbec4'
AZURE_TENANT_ID= '758732axxxxx1e0bbja3'
AZURE_AD_APPLICATION_ID='0bbc8d65axxxxxaec71eh095'
api_allowed_prefixes = ('https://bi3-poc-apimanagement.azure-api.net');Admin role should approve to call the AZURE consent URL.
6. Create Snowflake Function
Create a function to call API integration in snowflake.
create or replace external function FN_SNOWFLAKE_EXTERNAL_FUNCTION_SENTIMENT_ANALYSIS(body varchar)
returns variant
volatile
api_integration = SNOWFLAKE_SAFEGUARD_NONPROD_INTEGRATION_1
HEADERS =(‘Host’=’bi3-poc-apimanagement.azure-api.net’
,’Ocp-Apim-Trace’=’true’
,’Ocp-Apim-Subscription-Key’=’a51969axxxxxf5341'
) as ‘https://bi3-poc-apimanagement.azure-api.net/snowflakeexternalfunction/SentimentAnalysis'Note: There is a limitation that we have pass the body in function but not the headers. Headers can be hard coded into the function.
7. Create Snowflake TABLE
Create a table that contains inputs to the above created function in snowflake as below.
CREATE OR REPLACE TABLE DEMO_DB.INCUBATION_TESTING.SENTIMENT_ANALYSIS
AS
SELECT CAST(‘This is very good job :-)’ aS VARCHAR) AS feedback
UNION ALL
SELECT ‘this is a bad job :-)’
UNION ALL
SELECT ‘i am not vastly satisfied with the work, but i understand the problem’;The created table will provide below result set.
SELECT * from DEMO_DB.INCUBATION_TESTING.SENTIMENT_ANALYSIS;
Using below select query it will provide corresponding outputs.
SELECT feedback, FN_SNOWFLAKE_EXTERNAL_FUNCTION_SENTIMENT_ANALYSIS(feedback) AS sentiment_analysis FROM DEMO_DB.INCUBATION_TESTING.SENTIMENT_ANALYSIS;
Finally, with the inputs provided through the Snowflake external function, we received the desired result from text analytics in Azure.
About Us
Bi3 has been recognized for being one of the fastest-growing companies in Australia. Our team has delivered substantial and complex projects for some of the largest organizations around the globe and we’re quickly building a brand that is well known for superior delivery.
Website : https://bi3technologies.com/
Follow us on,
LinkedIn : https://www.linkedin.com/company/bi3technologies
Instagram : https://www.instagram.com/bi3technologies/
Twitter : https://twitter.com/Bi3Technologies
