Snowflake

You can access AirOps Apps directly in Snowflake by creating a Snowflake External Function.

The below steps walk through creating and running a Snowflake External Function to access any AirOps App. If you have any questions, just reach out to us!

You can also find these details on the Integrate tab of any AirOps app:

1. Create the API Integration in Snowflake

First, you must create an API connection between Snowflake and AirOps. To do so, run the below statement in Snowflake with ACCOUNT_ADMIN permissions.

create or replace api integration airops_api_integration
 api_provider = aws_api_gateway
 api_aws_role_arn = 'arn:aws:iam::843219976259:role/rails_app_api_gateway'
 api_allowed_prefixes = ('https://x4xrin77vf.execute-api.us-east-1.amazonaws.com/production')
 enabled = true;

More information about Snowflake API integrations can be found here.

2. Create the External Function

Secondly you must create a function for each App you wish you use in Snowflake. When running the CREATE command to create a new function, you’ll add the bearer token for authentication, identify the data app ID, give it a name, and run the command. The function can be given any name, but we recommend including “AIROPS” so it is easily identifiable (e.g. AIROPS_SENTIMENT_ANALYZE)

a. Find your workspace Bearer token

AirOps API requests require authentication using your workspace Bearer token that forms part of the request. You can find your token by navigating to ⚙️Settings → Workspace.

You will need the bearer token to create the External Function - the bearer token should be in the following format: Bearer YOUR_API_KEY

b. Get the App ID you wish to run

You will also use your App ID during the Create External Function step. The App ID is the number found at the end of the app's URL.

Example:

https://app.airops.com/{your_workspace}/apps/**56**

the App ID is 56.

c. Set the App parameters

AirOps Apps require specific parameters in order to function properly. The parameters are inputs that are passed into the model.

To obtain these parameters, go to the App and click on "API Doc." Next, look for the "Request Body" section. For example, in the image below, you can see that the available parameters are input_text, from_language, and to_language.

d. Create the External Function

You’re almost there! With the App ID, api key, and parameters, create the external function.

create or replace external function AIROPS_<APP NAME>(data_row object)
returns variant
api_integration = AIROPS_API_INTEGRATION
HEADERS = ( 'Authorization' = 'Bearer <YOUR_API_KEY>', 'Content-Type' = 'application/json' )
MAX_BATCH_ROWS = 25
as 'https://x4xrin77vf.execute-api.us-east-1.amazonaws.com/production/snowflake-execute-data-app/{YOUR_DATA_APP_ID}';

3. App Execution

Showtime. To execute an app from your newly created Snowflake function, you must first construct an object with the request parameters. The object should contain the parameter name, and the column or value to be passed into it.

In the example below, a request object is created using the OBJECT_CONSTRUCT function in Snowflake. The order of parameters in the object does not matter.

The constructed object is passing the text_column field into the input_text parameter, from_lanugage_column into the from_language parameter, and to_language_column into the to_language parameter.

select 
    **OBJECT_CONSTRUCT**(**'input_text', text_column, 'from_language', from_lanugage_column, 'to_language',to_language_column )** as request_data
from
	language_data;

This will create an object to pass into the function. Below is an object created by the above query for the Language Translate app:

{
	"input_text": "Translate this text!",
  "from_language": "Detect",
  "to_language": "Spanish"
}

The object is then used in the function, which will pass the mapped fields to the parameters in the AirOps App.

select 
    object_construct('input_text', text_input, 'from_language', from_lanugage, 'to_language',to_language ) as data_row
	, **AIROPS_LANGUAGE_TRANSLATE(data_row) as result**
from
	language_data;