Problem Description:

Export to snowflake failing with Exception: Invalid stage type: GCS during the creation of staging area when snowflake is hosted on the google cloud platform. Sample stack trace look like below,

21/05/27 18:08:38 ERROR SnowFlakeTargetExecutor: Exception: Invalid stage type: GCS
21/05/27 18:08:38 DEBUG DefaultTaskDagExecutorImpl: Failed io.infoworks.awb.exec.spark.task.SparkSnowFlakeTaskNode@2f100817 : java.lang.IllegalArgumentException: Invalid stage type: GCS
    at net.snowflake.client.jdbc.cloud.storage.StageInfo.createStageInfo(StageInfo.java:59)
    at net.snowflake.client.jdbc.SnowflakeFileTransferAgent.parseCommand(SnowflakeFileTransferAgent.java:1131)
    at net.snowflake.client.jdbc.SnowflakeFileTransferAgent.<init>(SnowflakeFileTransferAgent.java:915)
    at net.snowflake.spark.snowflake.io.SFInternalStage.sfAgent$lzycompute(SFInternalStage.scala:302)
    at net.snowflake.spark.snowflake.io.SFInternalStage.sfAgent(SFInternalStage.scala:297)
    at net.snowflake.spark.snowflake.io.SFInternalStage.encryptionMaterials$lzycompute(SFInternalStage.scala:307)
    at net.snowflake.spark.snowflake.io.SFInternalStage.encryptionMaterials(SFInternalStage.scala:307)
    at net.snowflake.spark.snowflake.io.SFInternalStage.getKeyIds$lzycompute(SFInternalStage.scala:367)
    at net.snowflake.spark.snowflake.io.SFInternalStage.getKeyIds(SFInternalStage.scala:358)
    
at net.snowflake.spark.snowflake.io.StageWriter$.unloadData(StageWriter.scala:415)



Root cause:

The snowflake connector we are shipping supports reading/writing data from/to snowflake hosted on Azure and Amazon platforms. Google cloud platform is not supported.


Solution:

When the instance is hosted on GCP, internal data transfer mode is supported only in version 2.7.0 (and higher) of the connector. By upgrading the driver to a higher version will resolve this issue. While upgrading the driver we need to choose it in such a way that it is compatible with the spark version available in the cluster. The below connector is for spark version 2.3.* 

source $IW_HOME/env.sh
Take a backup of $IW_HOME/lib/dt/libs/spark-snowflake_2.11-2.4.0.jar
Take a backup of $IW_HOME/lib/dt/libs/snowflake-jdbc-3.6.4.jar
cd $IW_HOME/lib/dt/libs/
wget https://repo1.maven.org/maven2/net/snowflake/spark-snowflake_2.11/2.8.3-spark_2.3/spark-snowflake_2.11-2.8.3-spark_2.3.jar
wget
https://repo1.maven.org/maven2/net/snowflake/snowflake-jdbc/3.12.15/snowflake-jdbc-3.12.15.jar


Applicable IWX versions:

IWX 3.3.3-GCP