Py4JJavaError An error occurred while calling o37.load. : net.snowflake.client.jdbc.SnowflakeSQLException: JDBC driver encountered communication error

Question

I am trying to connect (PySpark + Snowflake) been continuously getting the error.

I am using PySpark 3.1, JDBC and Spark_Snowflake jar files also placed in Classpath. Not sure why I am getting the following error.

Code:

from pyspark import SparkConf, SparkContext
from pyspark.sql import SQLContext
from pyspark.sql.types import *
from pyspark import SparkConf, SparkContext
sc = SparkContext("local", "Test App")
spark = SQLContext(sc)
spark_conf = SparkConf().setMaster('local').setAppName('Testing Spark SF')
sfOptions = {
  "sfURL" : "<account_identifier>.snowflakecomputing.com",
  "sfUser" : "<user_name>",
  "sfPassword" : "<password>",
  "sfDatabase" : "<database>",
  "sfSchema" : "<schema>",
  "sfWarehouse" : "<warehouse>"
}

SNOWFLAKE_SOURCE_NAME = "net.snowflake.spark.snowflake"

df = spark.read.format(SNOWFLAKE_SOURCE_NAME) \
  .options(**sfOptions) \
  .option("query",  "select 1 as my_num union all select 2 as my_num") \
  .load()

df.show()

Error:

Py4JJavaError: An error occurred while calling o37.load. : net.snowflake.client.jdbc.SnowflakeSQLException: JDBC driver encountered communication error. Message: Exception encountered for HTTP request: sun.security.validator.ValidatorException: No trusted certificate found. 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:587) at net.snowflake.client.core.SessionUtil.openSession(SessionUtil.java:285) 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.(SnowflakeConnectionV1.java:96) at net.snowflake.client.jdbc.SnowflakeDriver.connect(SnowflakeDriver.java:172) at java.sql.DriverManager.getConnection(DriverManager.java:664) at java.sql.DriverManager.getConnection(DriverManager.java:208) at net.snowflake.spark.snowflake.JDBCWrapper.getConnector(SnowflakeJDBCWrapper.scala:209) at net.snowflake.spark.snowflake.SnowflakeRelation.$anonfun$schema$1(SnowflakeRelation.scala:60) at net.snowflake.spark.snowflake.SnowflakeRelation$$Lambda$866/22415031.apply(Unknown Source) at scala.Option.getOrElse(Option.scala:189) at net.snowflake.spark.snowflake.SnowflakeRelation.schema$lzycompute(SnowflakeRelation.scala:57) at net.snowflake.spark.snowflake.SnowflakeRelation.schema(SnowflakeRelation.scala:56) at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:449) at org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:325) at org.apache.spark.sql.DataFrameReader.$anonfun$load$3(DataFrameReader.scala:307) at org.apache.spark.sql.DataFrameReader$$Lambda$858/5135046.apply(Unknown Source) at scala.Option.getOrElse(Option.scala:189) at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:307) at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:225) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:497) at py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244) at py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:357) at py4j.Gateway.invoke(Gateway.java:282) at py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132) at py4j.commands.CallCommand.execute(CallCommand.java:79) at py4j.GatewayConnection.run(GatewayConnection.java:238) at java.lang.Thread.run(Thread.java:745) Caused by: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: No trusted certificate found at sun.security.ssl.Alerts.getSSLException(Alerts.java:198) at sun.security.ssl.SSLSocketImpl.fatal(SSLSocketImpl.java:1958) at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:322) at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:316) at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1526) at sun.security.ssl.ClientHandshaker.processMessage(ClientHandshaker.java:215) at sun.security.ssl.Handshaker.processLoop(Handshaker.java:1024) at sun.security.ssl.Handshaker.process_record(Handshaker.java:954) at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:1065) at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1384) at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1412) at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1396) at net.snowflake.client.jdbc.internal.apache.http.conn.ssl.SSLConnectionSocketFactory.createLayeredSocket(SSLConnectionSocketFactory.java:436)

Answer 1

It's obvious you have issues with the SSL certificate. You can override that temporarily.

sfOptions = {
   ...
   "sfSSL" : "false",
}

However, you can check if you access Snowflake through a proxy. You will need to import proxy's certificate and include it inside your cacerts. the default location of cacerts of your running java version. you can locate it inside the java home directory under lib/security.

keytool -import -trustcacerts -alias cert_ssl -file proxy.cer -noprompt -storepass changeit -keystore cacerts