How to call Rest API directly in Snowflake?

Question

I am trying to call rest API directly in snowflake. For that I am trying to use Python Stored Procedure. As usual I was trying to invoke requests.get() method. But unfortunately Snowflake don't support building http connection at the present. According to I need to execute external application from Snowflake. Any option executing shell script using snowflake query window.. I need to use external function. But I donot have access to aws account and will not be provided due to various reason.

Can any one please suggest any alternative method.

For instance we are using public REST API

If not possible , still can you please suggest some great nearest solution without using any External function.

The snowpark code is:

import snowflake.snowpark as snowpark
from snowflake.snowpark.functions import col, avg, as_integer
import requests
def main(session: snowpark.Session): 
    r = request.get('https://api.publicapis.org/entries')
    return r

Error in Snowpark

Traceback (most recent call last):
  File "urllib3/connection.py", line 174, in _new_conn
    conn = connection.create_connection(
  File "urllib3/util/connection.py", line 72, in create_connection
    for res in socket.getaddrinfo(host, port, family, socket.SOCK_STREAM):
  File "/usr/lib/python_udf/7514b8b71a5770c388d7c6f6030134a2a250933021f5c5a0e176ded90a60847a/lib/python3.8/socket.py", line 918, in getaddrinfo
    for res in _socket.getaddrinfo(host, port, family, type, proto, flags):
socket.gaierror: [Errno -3] Temporary failure in name resolution

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "urllib3/connectionpool.py", line 714, in urlopen
    httplib_response = self._make_request(
  File "urllib3/connectionpool.py", line 403, in _make_request
    self._validate_conn(conn)
  File "urllib3/connectionpool.py", line 1053, in _validate_conn
    conn.connect()
  File "urllib3/connection.py", line 363, in connect
    self.sock = conn = self._new_conn()
  File "urllib3/connection.py", line 186, in _new_conn
    raise NewConnectionError(
urllib3.exceptions.NewConnectionError: <urllib3.connection.HTTPSConnection object at 0xffff728c2c10>: Failed to establish a new connection: [Errno -3] Temporary failure in name resolution

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "requests/adapters.py", line 486, in send
    resp = conn.urlopen(
  File "urllib3/connectionpool.py", line 798, in urlopen
    retries = retries.increment(
  File "urllib3/util/retry.py", line 592, in increment
    raise MaxRetryError(_pool, url, error or ResponseError(cause))
urllib3.exceptions.MaxRetryError: HTTPSConnectionPool(host='api.publicapis.org', port=443): Max retries exceeded with url: /entries (Caused by NewConnectionError('<urllib3.connection.HTTPSConnection object at 0xffff728c2c10>: Failed to establish a new connection: [Errno -3] Temporary failure in name resolution'))

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  Worksheet, line 5, in main
  File "requests/api.py", line 73, in get
    return request("get", url, params=params, **kwargs)
  File "requests/api.py", line 59, in request
    return session.request(method=method, url=url, **kwargs)
  File "requests/sessions.py", line 589, in request
    resp = self.send(prep, **send_kwargs)
  File "requests/sessions.py", line 703, in send
    r = adapter.send(request, **kwargs)
  File "requests/adapters.py", line 519, in send
    raise ConnectionError(e, request=request)
requests.exceptions.ConnectionError: HTTPSConnectionPool(host='api.publicapis.org', port=443): Max retries exceeded with url: /entries (Caused by NewConnectionError('<urllib3.connection.HTTPSConnection object at 0xffff728c2c10>: Failed to establish a new connection: [Errno -3] Temporary failure in name resolution'))

Answer 1

this walkthrough from snowflake explains that process

note that this won't work on trial accounts since one cannot create external access integrations objects, but currently the process is to

• create network policy
• create external access integration
• create (python) udf that does requests

update: here is the code to copy paste and here is the code i copy pasted from

-- make network rule 
CREATE OR REPLACE NETWORK RULE ifconfignr
 MODE = EGRESS
 TYPE = HOST_PORT  --   indicates that the network rule will allow outgoing network traffic based on the domain of the request destination.
 VALUE_LIST = ('www.ifconfig.me');

 -- make access integration , doesnt work on trial ccounts:
CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION ifconfigeai
 ALLOWED_NETWORK_RULES = (ifconfignr)
 ENABLED = true;

-- make udf 
create or replace function myfun()
returns varchar
language python
runtime_version = '3.8'
EXTERNAL_ACCESS_INTEGRATIONS = (ifconfigeai)
handler = 'myfun'
packages=('requests')
as
$$
import requests 
def myfun():
  r=requests.get('https://www.ifconfig.me')
  return r.text
$$;
 
-- check if it works 
select  myfun()