Spring Boot应用连接Snowflake时出现JDBC连接超时问题求助

问题描述 #

尝试从Snowflake获取数据时，配置DataSource出现连接超时错误。

数据源配置代码 #

@Primary
@Bean 
public DataSource dataSource() throws SQLException { 
    BasicDataSource dataSource = new BasicDataSource();
    dataSource.setDriverClassName("net.snowflake.client.jdbc.SnowflakeDriver");
    dataSource.setUrl("jdbc:snowflake://<account>.snowflakecomputing.com/?warehouse=<warehouse>&database=<database>&schema=<schema>"); 
    dataSource.setUsername(username);
    dataSource.setPassword(password);
    return dataSource; 
}

错误信息 #

java.sql.SQLException: Cannot create PoolableConnectionFactory (JDBC driver encountered communication error. Message: Exception encountered for HTTP request: Connect to <account>.snowflakecomputing.com:443 <account>.snowflakecomputing.com/3.136.103.32, <account>.snowflakecomputing.com/18.118.91.6, <account>.snowflakecomputing.com/3.130.158.108] failed: Connection timed out: connect.)
    at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:653)
    at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:531)
    at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:731)
    at com.cat.cso.indexoptimizesp.serviceImpl.IndexOptimizeServiceImpl.executesp(IndexOptimizeServiceImpl.java:30)
    at com.cat.cso.indexoptimizesp.CsoIndexOptimizeSpApplication.run(CsoIndexOptimizeSpApplication.java:39)
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:782)
    at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:766)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:314)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1317)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1306)
    at com.cat.cso.indexoptimizesp.CsoIndexOptimizeSpApplication.main(CsoIndexOptimizeSpApplication.java:31)
Caused by: net.snowflake.client.jdbc.SnowflakeSQLException: JDBC driver encountered communication error. Message: Exception encountered for HTTP request: Connect to <account>.snowflakecomputing.com:443 [<account>.snowflakecomputing.com/3.136.103.32, <account>.snowflakecomputing.com/18.118.91.6, <account>.snowflakecomputing.com/3.130.158.108] failed: Connection timed out: connect.
    at net.snowflake.client.jdbc.RestRequest.execute(RestRequest.java:284)
    at net.snowflake.client.core.HttpUtil.executeRequestInternal(HttpUtil.java:496)
    at net.snowflake.client.core.HttpUtil.executeRequest(HttpUtil.java:441)
    at net.snowflake.client.core.HttpUtil.executeGeneralRequest(HttpUtil.java:408)
    at net.snowflake.client.core.SessionUtil.newSession(SessionUtil.java:586)
    at net.snowflake.client.core.SessionUtil.openSession(SessionUtil.java:284)
    at net.snowflake.client.core.SFSession.open(SFSession.java:435)
    at net.snowflake.client.jdbc.DefaultSFConnectionHandler.initialize(DefaultSFConnectionHandler.java:104)
    at net.snowflake.client.jdbc.DefaultSFConnectionHandler.initializeConnection(DefaultSFConnectionHandler.java:79)
    at net.snowflake.client.jdbc.SnowflakeConnectionV1.initConnectionWithImpl(SnowflakeConnectionV1.java:116)
    at net.snowflake.client.jdbc.SnowflakeConnectionV1.<init>(SnowflakeConnectionV1.java:96)
    at net.snowflake.client.jdbc.SnowflakeDriver.connect(SnowflakeDriver.java:164)
    at org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:52)
    at org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:374)
    at org.apache.commons.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:106)
    at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:649)
    ... 10 more

解决方案 #

连接超时通常由网络或配置问题导致，按以下步骤排查：

• 网络连通性检查

  1. 在应用服务器执行telnet <account>.snowflakecomputing.com 443或curl -v https://<account>.snowflakecomputing.com，确认能否访问Snowflake的443端口。若不通，说明存在防火墙/安全组限制，需联系运维开放对应IP段的443端口访问权限。
  2. 若环境需通过代理连接，在JDBC URL中添加代理参数：&proxyHost=<proxy_host>&proxyPort=<proxy_port>，若代理需认证，补充&proxyUser=<proxy_user>&proxyPassword=<proxy_pass>。

• 账户配置验证

  1. 确认<account>已替换为正确的Snowflake账户标识符（跨区域部署时格式为<account>.<region>.<cloud>，如xy12345.us-west-2.aws）。
  2. 验证用户名、密码、仓库、数据库、schema名称的正确性，可通过Snowflake Web UI登录确认资源存在且用户有访问权限。

• 连接池与驱动优化

  1. 增加连接池超时设置，避免默认时间过短导致失败：

     dataSource.setMaxWaitMillis(30000); // 最大等待时间30秒
     dataSource.setConnectionTimeout(30000); // 连接超时30秒
     

  2. 升级Snowflake JDBC驱动至最新稳定版，确保与服务端版本兼容。

• DNS解析检查
  在应用服务器执行nslookup <account>.snowflakecomputing.com，确认解析出的IP与错误信息一致且可正常访问，若解析异常，检查服务器DNS配置。

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