Solved

Unable to connect to Snowflake, got read time out error

  • 4 February 2022
  • 9 replies
  • 122 views

Badge +4

I am trying to set up a database connection to my Snowflake 30-day free trial account. The connection parameters are shown below. When I test the connection, it takes a long time (about 5 minutes), and then get a Read timeout error as shown below. I have no problem login to Snowflake web console using https://IN17188.us-east-2.aws.snowflakecomputing.com. I use the account "IN17188.us-east-2.aws" in the FME database connection Account Name parameter. I use FME Desktop 2021.1.1. Anybody experienced this issue? I just try to follow the FME - Snowflake tutorial - https://www.safe.com/training/recorded/connect-data-even-geospatial-snowflake/

FME_Snowflake_connection 

net.snowflake.client.jdbc.SnowflakeSQLException: JDBC driver encountered communication error. Message: Exception encountered for HTTP request: Connect to IN17188.us-east-2.aws.snowflakecomputing.com:443 [IN17188.us-east-2.aws.snowflakecomputing.com/3.20.44.68, IN17188.us-east-2.aws.snowflakecomputing.com/13.58.248.132, IN17188.us-east-2.aws.snowflakecomputing.com/3.13.181.210] failed: Read timed out.

 

at net.snowflake.client.jdbc.RestRequest.execute(RestRequest.java:284)

 

at net.snowflake.client.core.HttpUtil.executeRequestInternal(HttpUtil.java:493)

 

at net.snowflake.client.core.HttpUtil.executeRequest(HttpUtil.java:438)

 

at net.snowflake.client.core.HttpUtil.executeGeneralRequest(HttpUtil.java:405)

 

at net.snowflake.client.core.SessionUtil.newSession(SessionUtil.java:574)

.........

icon

Best answer by steveatsafe 7 February 2022, 23:20

View original

9 replies

Badge +4

I suspected the "Read time out" error was because of the company blockage even though I can access the URL in the web browser. So I got a free home use FME desktop license and downloaded the latest FME 2021.2.3 . Repeated the above Snowflake database connection using my home internet connection. I don't see this "Read time out error" any more, but got a different error as shown below:

 

Creating reader for format: Snowflake Spatial

Trying to find a JAVA plugin for reader named `SNOWFLAKE_SPATIAL'

Creating reader for format: Snowflake Spatial

Trying to find a JAVA plugin for reader named `SNOWFLAKE_SPATIAL'

Snowflake Spatial Reader: Preloading JDBC Driver Class 'net.snowflake.client.jdbc.SnowflakeDriver'

Snowflake Spatial Reader: Creating connection using connection string 'jdbc:snowflake://17188.us-east-2.aws.snowflakecomputing.com/' and properties '{warehouse="DEMO_WH", db="SNOWFLAKE_SAMPLE_DATA", user=yguan22, application=SafeSoftwareFME, GEOGRAPHY_OUTPUT_FORMAT=WKB, password=************, schema="TPCH_SF1"}'

Snowflake Spatial Reader: Driver class 'net.snowflake.client.jdbc.SnowflakeDriver' is being loaded from '/C:/Apps/FME2021_64/plugins/snowflake-jdbc-3.13.8.jar'

at net.snowflake.client.core.HttpUtil.executeRequestInternal(HttpUtil.java:664)

at net.snowflake.client.core.HttpUtil.executeRequest(HttpUtil.java:584)

at net.snowflake.client.core.HttpUtil.executeGeneralRequest(HttpUtil.java:551)

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:446)

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:172)

at COM.safe.fme.jdbc.JdbcCommon.openConnection(JdbcCommon.java:565)

at COM.safe.fme.jdbc.JdbcReader.openHelper(JdbcReader.java:196)

at COM.safe.fme.jdbc.JdbcReader.open(JdbcReader.java:165)

UniversalReader -- readSchema resulted in 0 schema features being returned

Reader Parameter(0) = >17188.us-east-2.aws<

Reader Parameter(1) = >WAREHOUSE<

Reader Parameter(1) = >DEMO_WH<

Reader Parameter(3) = >DATABASE<

Reader Parameter(3) = >SNOWFLAKE_SAMPLE_DATA<

Reader Parameter(5) = >SCHEMA<

Reader Parameter(5) = >TPCH_SF1<

Reader Parameter(7) = >ROLE<

Reader Parameter(7) = ><

Reader Parameter(9) = >AUTHENTICATION_METHOD<

Reader Parameter(9) = >BASIC<

Reader Parameter(11) = >USER_NAME<

Reader Parameter(11) = >yguan22<

Reader Parameter(13) = >PASSWORD<

Reader Parameter(13) = >********<

Reader Parameter(15) = >OAUTHCONNECTION<

Reader Parameter(15) = ><Unused><

Reader Parameter(17) = >SPECIFY_JDBC_CONN_GROUP<

Reader Parameter(17) = >NO<

Reader Parameter(19) = >CONNECTION_STRING<

Reader Parameter(19) = ><Unused><

Reader Parameter(21) = >MASK_CONNECTION_STRING<

Reader Parameter(21) = ><Unused><

Reader Parameter(23) = >CONNECTION_PROPERTIES<

Reader Parameter(23) = ><Unused><

Reader Parameter(25) = >RETRIEVE_ALL_TABLE_NAMES<

Reader Parameter(25) = >YES<

Reader Directive(0) = >WAREHOUSE<

Reader Directive(1) = >DEMO_WH<

Reader Directive(2) = >DATABASE<

Reader Directive(3) = >SNOWFLAKE_SAMPLE_DATA<

Reader Directive(4) = >SCHEMA<

Reader Directive(5) = >TPCH_SF1<

Reader Directive(6) = >ROLE<

Reader Directive(7) = ><

Reader Directive(8) = >AUTHENTICATION_METHOD<

Reader Directive(9) = >BASIC<

Reader Directive(10) = >USER_NAME<

Reader Directive(11) = >yguan22<

Reader Directive(12) = >PASSWORD<

Reader Directive(13) = >********<

Reader Directive(14) = >OAUTHCONNECTION<

Reader Directive(15) = ><Unused><

Reader Directive(16) = >SPECIFY_JDBC_CONN_GROUP<

Reader Directive(17) = >NO<

Reader Directive(18) = >CONNECTION_STRING<

Reader Directive(19) = ><Unused><

Reader Directive(20) = >MASK_CONNECTION_STRING<

Reader Directive(21) = ><Unused><

Reader Directive(22) = >CONNECTION_PROPERTIES<

Reader Directive(23) = ><Unused><

Reader Directive(24) = >RETRIEVE_ALL_TABLE_NAMES<

Reader Directive(25) = >YES<

UniversalReader -- readSchema resulted in 0 schema features being returned

 

Badge +4

I suspected the "Read time out" error was because of the company blockage even though I can access the URL in the web browser. So I got a free home use FME desktop license and downloaded the latest FME 2021.2.3 . Repeated the above Snowflake database connection using my home internet connection. I don't see this "Read time out error" any more, but got a different error as shown below:

 

Creating reader for format: Snowflake Spatial

Trying to find a JAVA plugin for reader named `SNOWFLAKE_SPATIAL'

Creating reader for format: Snowflake Spatial

Trying to find a JAVA plugin for reader named `SNOWFLAKE_SPATIAL'

Snowflake Spatial Reader: Preloading JDBC Driver Class 'net.snowflake.client.jdbc.SnowflakeDriver'

Snowflake Spatial Reader: Creating connection using connection string 'jdbc:snowflake://17188.us-east-2.aws.snowflakecomputing.com/' and properties '{warehouse="DEMO_WH", db="SNOWFLAKE_SAMPLE_DATA", user=yguan22, application=SafeSoftwareFME, GEOGRAPHY_OUTPUT_FORMAT=WKB, password=************, schema="TPCH_SF1"}'

Snowflake Spatial Reader: Driver class 'net.snowflake.client.jdbc.SnowflakeDriver' is being loaded from '/C:/Apps/FME2021_64/plugins/snowflake-jdbc-3.13.8.jar'

at net.snowflake.client.core.HttpUtil.executeRequestInternal(HttpUtil.java:664)

at net.snowflake.client.core.HttpUtil.executeRequest(HttpUtil.java:584)

at net.snowflake.client.core.HttpUtil.executeGeneralRequest(HttpUtil.java:551)

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:446)

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:172)

at COM.safe.fme.jdbc.JdbcCommon.openConnection(JdbcCommon.java:565)

at COM.safe.fme.jdbc.JdbcReader.openHelper(JdbcReader.java:196)

at COM.safe.fme.jdbc.JdbcReader.open(JdbcReader.java:165)

UniversalReader -- readSchema resulted in 0 schema features being returned

Reader Parameter(0) = >17188.us-east-2.aws<

Reader Parameter(1) = >WAREHOUSE<

Reader Parameter(1) = >DEMO_WH<

Reader Parameter(3) = >DATABASE<

Reader Parameter(3) = >SNOWFLAKE_SAMPLE_DATA<

Reader Parameter(5) = >SCHEMA<

Reader Parameter(5) = >TPCH_SF1<

Reader Parameter(7) = >ROLE<

Reader Parameter(7) = ><

Reader Parameter(9) = >AUTHENTICATION_METHOD<

Reader Parameter(9) = >BASIC<

Reader Parameter(11) = >USER_NAME<

Reader Parameter(11) = >yguan22<

Reader Parameter(13) = >PASSWORD<

Reader Parameter(13) = >********<

Reader Parameter(15) = >OAUTHCONNECTION<

Reader Parameter(15) = ><Unused><

Reader Parameter(17) = >SPECIFY_JDBC_CONN_GROUP<

Reader Parameter(17) = >NO<

Reader Parameter(19) = >CONNECTION_STRING<

Reader Parameter(19) = ><Unused><

Reader Parameter(21) = >MASK_CONNECTION_STRING<

Reader Parameter(21) = ><Unused><

Reader Parameter(23) = >CONNECTION_PROPERTIES<

Reader Parameter(23) = ><Unused><

Reader Parameter(25) = >RETRIEVE_ALL_TABLE_NAMES<

Reader Parameter(25) = >YES<

Reader Directive(0) = >WAREHOUSE<

Reader Directive(1) = >DEMO_WH<

Reader Directive(2) = >DATABASE<

Reader Directive(3) = >SNOWFLAKE_SAMPLE_DATA<

Reader Directive(4) = >SCHEMA<

Reader Directive(5) = >TPCH_SF1<

Reader Directive(6) = >ROLE<

Reader Directive(7) = ><

Reader Directive(8) = >AUTHENTICATION_METHOD<

Reader Directive(9) = >BASIC<

Reader Directive(10) = >USER_NAME<

Reader Directive(11) = >yguan22<

Reader Directive(12) = >PASSWORD<

Reader Directive(13) = >********<

Reader Directive(14) = >OAUTHCONNECTION<

Reader Directive(15) = ><Unused><

Reader Directive(16) = >SPECIFY_JDBC_CONN_GROUP<

Reader Directive(17) = >NO<

Reader Directive(18) = >CONNECTION_STRING<

Reader Directive(19) = ><Unused><

Reader Directive(20) = >MASK_CONNECTION_STRING<

Reader Directive(21) = ><Unused><

Reader Directive(22) = >CONNECTION_PROPERTIES<

Reader Directive(23) = ><Unused><

Reader Directive(24) = >RETRIEVE_ALL_TABLE_NAMES<

Reader Directive(25) = >YES<

UniversalReader -- readSchema resulted in 0 schema features being returned

 

Errors in below:

 

net.snowflake.client.jdbc.SnowflakeSQLException: JDBC driver encountered communication error. Message: HTTP status=403.

 

at net.snowflake.client.core.HttpUtil.executeRequestInternal(HttpUtil.java:664)

 

at net.snowflake.client.core.HttpUtil.executeRequest(HttpUtil.java:584)

 

at net.snowflake.client.core.HttpUtil.executeGeneralRequest(HttpUtil.java:551)

 

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:446)

 

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:172)

 

at COM.safe.fme.jdbc.JdbcCommon.openConnection(JdbcCommon.java:565)

 

at COM.safe.fme.jdbc.JdbcReader.openHelper(JdbcReader.java:196)

 

at COM.safe.fme.jdbc.JdbcReader.open(JdbcReader.java:165)

 

net.snowflake.client.jdbc.SnowflakeSQLException: JDBC driver encountered communication error. Message: HTTP status=403.

Failed to read schema features from dataset '17188.us-east-2.aws' using the 'SNOWFLAKE_SPATIAL' reader

Badge +11

@aguan​ 

Could you confirm the "ROLE" you are using in the Snowflake Console... try setting the ROLE in your Database Connection in FME...

 

I suspect that the reason is permissions (at least at one point in the errors you have shared).

Badge +11

Also review:

https://community.snowflake.com/s/article/How-to-check-for-current-account-name

 

https://community.safe.com/s/article/snowflake-database-connection-parameters-are-case

And in this article the mention about the Account Names

https://community.safe.com/s/article/Issues-Connecting-to-Snowflake

Badge +4

@aguan​ 

Could you confirm the "ROLE" you are using in the Snowflake Console... try setting the ROLE in your Database Connection in FME...

 

I suspect that the reason is permissions (at least at one point in the errors you have shared).

@steveatsafe​ I tried using SYSADMIN and ACCOUNTOWNER role, no luck. The same error.

Badge +4

@steveatsafe​ my bad. I missed one char when pasting the account name in my home FME db connection set up. my home FME works now with or without filling in the role. Thanks for your help. I can continue to finish the course.

Badge +4

@aguan​  Were you able to get FME - IHS snowflake connection working for your company environment ? I am having similar error messages and failed connection. I can connect successfully in Spotfire using ODBC connection to snowflake

Badge +4

I received following error messages and failed connection:

 

Snowflake Non-Spatial Reader: Driver class 'net.snowflake.client.jdbc.SnowflakeDriver' is being loaded from '/C:/Program Files/FME/plugins/snowflake-jdbc-3.13.8.jar'

at net.snowflake.client.jdbc.RestRequest.execute(RestRequest.java:284)

at net.snowflake.client.core.HttpUtil.executeRequestInternal(HttpUtil.java:639)

at net.snowflake.client.core.HttpUtil.executeRequest(HttpUtil.java:584)

at net.snowflake.client.core.HttpUtil.executeGeneralRequest(HttpUtil.java:551)

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:446)

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:172)

at COM.safe.fme.jdbc.JdbcCommon.openConnection(JdbcCommon.java:565)

at COM.safe.fme.jdbc.JdbcReader.openHelper(JdbcReader.java:196)

at COM.safe.fme.jdbc.JdbcReader.open(JdbcReader.java:165)

Caused by: java.net.SocketException: Connection reset

at java.net.SocketInputStream.read(SocketInputStream.java:210)

at java.net.SocketInputStream.read(SocketInputStream.java:141)

at sun.security.ssl.InputRecord.readFully(InputRecord.java:465)

at sun.security.ssl.InputRecord.read(InputRecord.java:503)

at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:990)

at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1388)

at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1416)

at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1400)

at net.snowflake.client.jdbc.internal.apache.http.conn.ssl.SSLConnectionSocketFactory.createLayeredSocket(SSLConnectionSocketFactory.java:436)

at net.snowflake.client.jdbc.internal.apache.http.conn.ssl.SSLConnectionSocketFactory.connectSocket(SSLConnectionSocketFactory.java:384)

at net.snowflake.client.jdbc.internal.apache.http.impl.conn.DefaultHttpClientConnectionOperator.connect(DefaultHttpClientConnectionOperator.java:142)

at net.snowflake.client.jdbc.internal.apache.http.impl.conn.PoolingHttpClientConnectionManager.connect(PoolingHttpClientConnectionManager.java:376)

at net.snowflake.client.jdbc.internal.apache.http.impl.execchain.MainClientExec.establishRoute(MainClientExec.java:393)

at net.snowflake.client.jdbc.internal.apache.http.impl.execchain.MainClientExec.execute(MainClientExec.java:236)

at net.snowflake.client.jdbc.internal.apache.http.impl.execchain.ProtocolExec.execute(ProtocolExec.java:186)

at net.snowflake.client.jdbc.internal.apache.http.impl.execchain.RetryExec.execute(RetryExec.java:89)

at net.snowflake.client.jdbc.internal.apache.http.impl.execchain.RedirectExec.execute(RedirectExec.java:110)

at net.snowflake.client.jdbc.internal.apache.http.impl.client.InternalHttpClient.doExecute(InternalHttpClient.java:185)

at net.snowflake.client.jdbc.internal.apache.http.impl.client.CloseableHttpClient.execute(CloseableHttpClient.java:83)

at net.snowflake.client.jdbc.internal.apache.http.impl.client.CloseableHttpClient.execute(CloseableHttpClient.java:108)

at net.snowflake.client.jdbc.RestRequest.execute(RestRequest.java:160)

... 14 more

net.snowflake.client.jdbc.SnowflakeSQLException: JDBC driver encountered communication error. Message: Exception encountered for HTTP request: Connection reset.

Failed to obtain any schemas from reader 'SNOWFLAKE_NONSPATIAL' from 1 datasets. This may be due to invalid datasets or format accessibility issues due to licensing, dependencies, or module loading. See logfile for more information

UniversalReader -- readSchema resulted in 0 schema features being returned

Reader Parameter(0) = >IHS_Snowflake<

Badge +4

@steveatsafe​  Any more updating on FME reader for IHS snowflake. I am using fme 2021.2 and still have problems. It took me several days to figure the connection: we have to use proxy per our company policy. Now I am connected but find out that non-spatial reader for IHS snowflake database does not work or cannot pull data. For snowflake spatial reader, it works if I set max record to 100 or 200, simply hangs if I use 500 as max records. I also reported the same issue to IHS support.

 

Reply