如何在Snowflake中直接调用REST API？求无AWS权限替代方案

我尝试在Snowflake中直接调用REST API，通过Python存储过程调用requests.get()方法，但Snowflake目前不支持建立HTTP连接。社区建议使用外部函数，但我因多种原因无法获取AWS账号权限，恳请提供替代方案——比如调用公开REST API（https://api.publicapis.org/entries）的方法；若无法直接实现，也推荐无需使用外部函数的近似解决方案。

我的Snowpark代码 #

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

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'))

方案1：本地预取数据后导入Snowflake #

Snowflake内部沙箱无法发起外部HTTP请求，最直接的解决方式是在本地或你有权限的外部环境中调用API获取数据，再将数据导入Snowflake表中。示例脚本如下：

import requests
import snowflake.connector

# 调用公开API获取数据
response = requests.get("https://api.publicapis.org/entries")
api_entries = response.json()["entries"]

# 连接Snowflake并写入数据
conn = snowflake.connector.connect(
    user="你的Snowflake用户名",
    password="你的Snowflake密码",
    account="你的Snowflake账号标识（如xxx.us-west-2）",
    warehouse="你的仓库名称",
    database="目标数据库",
    schema="目标Schema"
)

# 创建存储API数据的表（如果不存在）
cursor = conn.cursor()
cursor.execute("""
CREATE OR REPLACE TABLE PUBLIC_APIS (
    API_NAME VARCHAR,
    DESCRIPTION VARCHAR,
    AUTH_TYPE VARCHAR,
    IS_HTTPS BOOLEAN,
    CORS_STATUS VARCHAR,
    API_LINK VARCHAR,
    CATEGORY VARCHAR
)
""")

# 批量插入API数据
insert_stmt = """
INSERT INTO PUBLIC_APIS (API_NAME, DESCRIPTION, AUTH_TYPE, IS_HTTPS, CORS_STATUS, API_LINK, CATEGORY)
VALUES (%s, %s, %s, %s, %s, %s, %s)
"""
data_values = [
    (entry["API"], entry["Description"], entry["Auth"], entry["HTTPS"], entry["Cors"], entry["Link"], entry["Category"])
    for entry in api_entries
]
cursor.executemany(insert_stmt, data_values)

conn.commit()
cursor.close()
conn.close()

完成导入后，你可以直接通过Snowpark查询这个表，实现原本需要调用API后进行的数据分析操作。

方案2：利用Snowflake任务调度外部脚本（无需AWS权限） #

如果需要定期同步API数据，可以将上述导入脚本部署到你有权限的服务器（如个人电脑、公司内部服务器），然后通过Snowflake的**任务（Task）配合外部访问集成（External Access Integration）**调度脚本执行。这种方式不需要AWS账号，只要你有可运行脚本的外部环境即可。

方案3：修正代码笔误（非核心问题，但需注意） #

你的原代码存在一个小笔误：request.get应该改为requests.get（少了末尾的s），不过这不是导致连接失败的原因，只是需要修正的细节问题。

内容的提问来源于stack exchange，提问作者blurred_8216