Snowflake错误253003：写入数据时403 Forbidden问题求助

问题背景 #

通过SQLAlchemy连接Snowflake云平台，读取本地CSV文件转为DataFrame后尝试写入Snowflake数据库，使用的代码如下：

import pandas as pd

# The Snowflake Connector library.
import snowflake.connector as snow
from snowflake.connector.pandas_tools import write_pandas
from snowflake.connector.pandas_tools import pd_writer
from sqlalchemy import create_engine
from urllib.parse import quote_plus

class SnowFlakeHelper:

    def __init__(self,CONFIG):
        self.account = CONFIG['snowflake_accountname']
        self.account_fullurl = CONFIG["snowflake_account_fullurl"]
        self.dbname = CONFIG['snowflake_dbname']
        self.tablename = CONFIG['snowflake_tablename']
        self.uname = CONFIG['snowflake_uname']
        self.password = CONFIG['snowflake_password']
        self.syncdatacsvpath = CONFIG['data_csv_path']
        self.cur,self.conn = self.connect()
        self.engine = self.connect_using_sqlalchemy()
    
    
    def connect_using_sqlalchemy(self):
        conn_string = f"snowflake://{self.uname}:{quote_plus(self.password)}@{self.account}/{self.dbname}/PUBLIC"
        engine = create_engine(conn_string)
        return engine
    
    def load_csv_with_sqlalchemy(self):
        df = pd.read_csv(self.syncdatacsvpath)
        if_exists = 'replace'

        #Write the data to Snowflake, using pd_writer to speed up loading
        print('.... Trying to send csv data to snowflake .....')
        with self.engine.connect() as con:
            df.to_sql(name=self.tablename.lower(), con=con, if_exists=if_exists, method=pd_writer, index=False)

触发的错误 #

调用load_csv_with_sqlalchemy()方法时，连接成功但执行df.to_sql语句时触发如下错误：

Exception has occurred: OperationalError
253003: While putting file(s) there was an error: 'HTTPError('403 Client Error: Forbidden for url: https://sfc-in-ds1-99-customer-stage.s3.amazonaws.com/3f0c0000-s/stages/decf206a-e8c1-484e-9231-5d7feaa038f0/file0.txt')', this might be caused by your access to the blob storage provider, or by Snowflake.
  File "D:\AAIN1464\reconciliation\lib\snowflake_helper.py", line 55, in load_csv_with_sqlalchemy
    df.to_sql(name=self.tablename.lower(), con=con, if_exists=if_exists, method=pd_writer, index=False)
  File "D:\AAIN1464\reconciliation\run.py", line 104, in sync_data
    snowflakehelper.load_csv_with_sqlalchemy()
  File "D:\AAIN1464\reconciliation\run.py", line 203, in <module>
    sync_data()
snowflake.connector.errors.OperationalError: 253003: While putting file(s) there was an error: 'HTTPError('403 Client Error: Forbidden for url: https://sfc-in-ds1-99-customer-stage.s3.amazonaws.com/3f0c0000-s/stages/decf206a-e8c1-484e-9231-5d7feaa038f0/file0.txt')', this might be caused by your access to the blob storage provider, or by Snowflake.

已尝试方案 #

• 使用write_pandas()函数替代df.to_sql
• 为操作账号的角色赋予全权限

原因分析及解决办法 #

1. 网络/防火墙拦截请求 #

Snowflake写入数据时会临时使用AWS S3的客户阶段存储，若本地防火墙、企业代理限制了对该S3地址的访问，会触发403错误。
解决措施：

• 检查本地防火墙、企业代理规则，允许访问*.s3.amazonaws.com域名，尤其是错误信息中的sfc-in-ds1-99-customer-stage.s3.amazonaws.com
• 若使用代理，需在Snowflake连接配置中添加代理参数：
  • SQLAlchemy连接串可补充：?proxy_host=PROXY_HOST&proxy_port=PROXY_PORT
  • 或直接在Snowflake Connector连接时设置proxy参数

2. 临时内部阶段权限异常 #

Snowflake自动创建的临时内部阶段可能存在权限继承问题，即使角色有全库权限，也可能缺少该阶段的写入权限。
解决措施：

• 手动创建内部阶段：CREATE STAGE IF NOT EXISTS MY_TEMP_STAGE;
• 为角色赋予该阶段的写入权限：GRANT WRITE ON STAGE MY_TEMP_STAGE TO ROLE YOUR_ROLE_NAME;
• 在写入时指定该阶段，比如使用write_pandas时添加参数stage_name='MY_TEMP_STAGE'，或修改pd_writer的配置

3. 依赖库版本兼容问题 #

旧版本的Snowflake Connector、SQLAlchemy或pandas可能存在兼容性bug，导致S3请求异常。
解决措施：

• 升级所有相关依赖到最新稳定版：

  pip install --upgrade snowflake-connector-python snowflake-sqlalchemy pandas
  

4. Snowflake账号区域配置错误 #

若account参数未包含完整的区域信息，会连接到错误的Snowflake实例，进而访问错误的S3存储地址。
解决措施：

• 检查account参数格式，正确格式应为<account_identifier>.<region>.<cloud>，例如xy12345.us-west-2.aws
• 更新SQLAlchemy连接串中的account部分为完整标识

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