aidp-snowflake — Snowflake via the Snowflake Spark connector

Bridge AIDP Spark to Snowflake using the official Snowflake Spark connector. Useful for migration off Snowflake or for cross-warehouse joins where Snowflake holds the source of truth.

When to use

• Reading or writing a Snowflake warehouse from AIDP.
• Mentioned: "Snowflake", "sfUrl", "sfWarehouse".

When NOT to use

• For a generic JDBC-only DB (no Spark connector available) → aidp-jdbc-custom.

Cluster prerequisite — install the connector JARs

The Snowflake Spark connector is not in the AIDP cluster image by default. Two ways to get it in.

Option A — Runtime-load (recommended; no cluster restart)

The plugin's add_spark_connector_at_runtime helper downloads + registers both JARs in the running Spark session.

from oracle_ai_data_platform_connectors.jdbc import (
    add_spark_connector_at_runtime, download_jdbc_jar,
)

jars = [
    download_jdbc_jar(
        maven_url="https://repo1.maven.org/maven2/net/snowflake/"
                  "spark-snowflake_2.12/3.1.1/spark-snowflake_2.12-3.1.1.jar",
        target_path="/tmp/spark-snowflake_2.12-3.1.1.jar"),
    download_jdbc_jar(
        maven_url="https://repo1.maven.org/maven2/net/snowflake/"
                  "snowflake-jdbc/3.19.0/snowflake-jdbc-3.19.0.jar",
        target_path="/tmp/snowflake-jdbc-3.19.0.jar"),
]
add_spark_connector_at_runtime(
    spark,
    jar_paths=jars,
    verify_classes=[
        "net.snowflake.spark.snowflake.DefaultSource",
        "net.snowflake.client.jdbc.SnowflakeDriver",
    ],
    register_jdbc_driver_class="net.snowflake.client.jdbc.SnowflakeDriver",
)

The helper does three things in one call: builds a URLClassLoader covering both JARs and sets it as the thread context CL (so Spark's ServiceLoader finds the snowflake format), registers the JDBC driver with DriverManager (for any code path that goes through getConnection), and calls SparkContext.addJar on each JAR (so executors fetch them — required because Snowflake reads partition across executors). All without a kernel restart.

Option B — Cluster Library tab (durable, requires admin)

Upload both JARs to a Volume and attach via the cluster Library tab. Persists across cluster restarts. Requires admin access. After restart, skip the runtime-load helper.

Pin the versions — newer Snowflake connector / JDBC may not be compatible with the cluster's Spark version. The pair tested on Spark 3.5.0 / Scala 2.12 is spark-snowflake_2.12-3.1.1 + snowflake-jdbc-3.19.0.

Read

import os

snowflake_options = {
    "sfUrl":       os.environ["SNOW_URL"],         # e.g. xy12345.us-east-1.snowflakecomputing.com
    "sfUser":      os.environ["SNOW_USER"],
    "sfPassword":  os.environ["SNOW_PASSWORD"],
    "sfDatabase":  os.environ.get("SNOW_DATABASE", "DATAFLOW"),
    "sfSchema":    os.environ.get("SNOW_SCHEMA",   "DF_SCHEMA"),
    "sfWarehouse": os.environ.get("SNOW_WAREHOUSE", "COMPUTE_WH"),
}

df = (spark.read
      .format("snowflake")
      .options(**snowflake_options)
      .option("dbtable", os.environ["SNOW_TABLE"])
      .load())
df.show(5)

Write

(df.write
   .format("snowflake")
   .options(**snowflake_options)
   .option("dbtable", os.environ["SNOW_TARGET_TABLE"])
   .mode("overwrite")
   .save())

Gotchas

• No Spark JDBC fallback in this skill. The Snowflake JDBC alone (no Spark connector) doesn't push down predicates and is much slower. Use the Spark connector.
• Network reachability — Snowflake is public over TLS; the AIDP cluster needs egress. If your cluster is in a strict NSG, allow outbound HTTPS to *.snowflakecomputing.com.
• Auth — only password auth shown here. Snowflake key-pair auth (RSA) and OAuth are also supported by the connector but require additional pem_private_key / sfAuthenticator options not covered in this skill.
• dbtable is the simplest spec. For complex pushdown use query instead — option("query", "SELECT ... FROM ... WHERE ...") runs the query in Snowflake and only ships the result.
• Case sensitivity — Snowflake folds unquoted names to UPPERCASE. If a Spark write fails with "table not found" on a lowercase target, quote the name in dbtable.

References

• Official sample: oracle-samples/oracle-aidp-samples → data-engineering/ingestion/Connect_Using_Custom_JDBC_Driver.ipynb
• Snowflake Spark connector docs: https://docs.snowflake.com/en/user-guide/spark-connector