python – How to connect to external REST API using Snowflake?

by


I need some help trying to access an external API from a Snowflake SQL Worksheet. Here is my complete code with building the integration and the python function that makes the API call:

USE ROLE SYSADMIN;

USE SCHEMA TALKDESK.BRONZE

CREATE OR REPLACE NETWORK RULE api_network_rule
  MODE = EGRESS
  TYPE = HOST_PORT
  VALUE_LIST = ('belnick.talkdeskid.com');
  
CREATE OR REPLACE SECURITY INTEGRATION talkdesk_security_integration
  TYPE = api_authentication
  AUTH_TYPE = oauth2
  OAUTH_CLIENT_AUTH_METHOD = client_secret_post
  OAUTH_CLIENT_ID = <ID>
  OAUTH_CLIENT_SECRET = <SECRET>
  OAUTH_TOKEN_ENDPOINT = 'https://belnick.talkdeskid.com/oauth/token'
  OAUTH_GRANT = 'CLIENT_CREDENTIALS'
  OAUTH_ALLOWED_SCOPES = ('[]')
  ENABLED = true;

CREATE OR REPLACE SECRET api_key_secret
    TYPE = OAUTH2
    API_AUTHENTICATION = talkdesk_security_integration
    OAUTH_REFRESH_TOKEN = <TOKEN>
    
CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION talkdesk_integration
  ALLOWED_NETWORK_RULES = (api_network_rule)
  ALLOWED_AUTHENTICATION_SECRETS = (api_key_secret)
  ENABLED = true;


CREATE OR REPLACE FUNCTION get_data()
RETURNS text
LANGUAGE PYTHON
RUNTIME_VERSION = 3.8
HANDLER = 'refresh_token'
EXTERNAL_ACCESS_INTEGRATIONS = (talkdesk_integration)
PACKAGES = ('requests')
SECRETS = ('api_key'= api_key_secret)
AS
$$
import _snowflake
import requests
import json

session = requests.Session()

api_key = _snowflake.get_oauth_access_token('api_key')
def refresh_token():

    url="https://belnick.talkdeskid.com/oauth/token"
    payload = { "grant_type": "client_credentials" }
    headers = {
        "accept": "application/json",
        "Authorization": f"Basic {api_key}",
        "content-type": "application/x-www-form-urlencoded"
    }
    
    response = session.post(url, data=payload, headers=headers)
    return response.json()['access_token']
$$;

Everything works fine until the function creation. The error that I get is the following:

SQL execution error: OAuth2 Access token request failed with error 'Failed to parse OAuth server response'.

The refresh_token function works just fine when executed using python in an IDE.

Does anyone have any idea about what could be wrong with my configurations?



Source link

Leave a Comment