0
0
During the last post we have seen how to configure the Remote Service Layer via AWS Lambda function. Also we configured Proxy Service (Amazon API Gateway) using REST API. Later on we deployed the API to reflect the changes. However, In this post we will create API integration and External Function at Snowflake layer. An API object stores information about an HTTPS proxy service. In order to create API integration, Use a Snowflake role with ACCOUNTADMIN privileges or the CREATE INTEGRATION privilege.
API Integration in Snowflake:
- Firstly, Create API Integration in Snowflake.
- Finally, Setting up a trust relationship between Snowflake and the new IAM role.
API creation:
create or replace api integration my_api_integration
api_provider = aws_api_gateway
api_aws_role_arn = 'arn:aws:iam::913267004595:role/NewhttpExternal'
api_allowed_prefixes = ('https://zjnc6qtby0.execute-api.us-east-1.amazonaws.com/httppost/create-http-entity')
enabled = true;
Moreover, Edit the command above like so:
• Replace the api_aws_role_arn with your Cloud Platform (IAM) Role ARN.
• Replace the api_allowed_prefixes field with your resource invocation URL.
Setting up Trust Relationship:
Execute the command, then follow it up with the following
DESCRIBE INTEGRATION my_api_integration:
Look for the property named “API_AWS_IAM_USER_ARN” and then record that property’s “property_value.”
Look for the property named “API_AWS_EXTERNAL_ID” and then record that property’s “property_value.
Creating an external function in Snowflake.
create or replace external function EXT_UDF_fetchHTTPData(url string)
returns variant
api_integration = my_api_integration
as 'https://zjnc6qtby0.execute-api.us-east-1.amazonaws.com/httppost/create-http-entity';
The parameters adjustments you need to make are:
• Replace the < api_integration_name > with the name of your API integration
• Replace the < invocation_url > value with your resource invocation URL.
Calling the external function:
We have created a table and insert two records with below public URL:
https://api.coindesk.com/v1/bpi/currentprice.json;
https://www.cryptingup.com/api/markets;
# Invoke the external function
select SRC_TYPE,src_url, demo_db.public.EXT_UDF_fetchHTTPData( src_url ) PAYLOAD_RESPONSE,current_Date from API_INFO;
Verify the Payload received from the External function:
