从Database ricks连接到Snowflake

Question

使用Database ricks笔记本，我能够从Database ricks连接到“雪花”，并使用“scala”将内容写入Snowflake中的表，但使用“python”无法正常工作。

我在数据砖中添加了这两个库，这有助于在数据砖和雪花之间建立联系：雪花-jdbc-3.6.8和火花-snowflake_2.11-2.4.4-spark_2.2。

我的目标是使用Databricks(用于机器学习- Spark)并在Databricks和雪花之间来回移动数据。

下面是我尝试将数据帧的内容写入雪花的代码:

1. 将数据加载到DataFrame

    df1 = spark.read.format('csv').option("header", "true")\
               .option("inferSchema", "true")\
               .load("dbfs:/databricks-datasets/airlines/part-00000")
    display(df1)

我可以看到数据已加载到数据帧中。

    user = dbutils.secrets.get(scope="jdbc", key="snowflake_username")
    password = dbutils.secrets.get(scope="jdbc", key="snowflake_password")

    options = dict(sfUrl="https://***snowflake_username***.snowflakecomputing.com",
                   sfUser=user,
                   sfPassword=password,
                   sfDatabase="my_database",
                   sfSchema="PUBLIC",
                   sfWarehouse="my_warehouse")

    SNOWFLAKE_SOURCE_NAME = "net.snowflake.spark.snowflake"

到目前为止没有错误

    df1.write.format(SNOWFLAKE_SOURCE_NAME) \
        .options(**options) \
        .option("dbtable", "datafromspark") \
          .save()

这导致：

**Error : 
java.sql.SQLException: No suitable driver found for jdbc:snowflake://https://*snowflake_username*.snowflakecomputing.com**

Answer 1

这实际上工作正常（考虑到允许用户写入模式并稍后从中读取）

import dbutils

# Use secrets DBUtil to get Snowflake credentials.
user = dbutils.secrets.get("snowflake", "xx")
password = dbutils.secrets.get("snowflake", "xy")

# snowflake connection options
options = {
    "sfUrl": "XXXXXXX.snowflakecomputing.com",
    "sfUser": user,
    "sfPassword": password,
    "sfDatabase": "DBX_TEST",
    "sfSchema": "DBX_SCHEMA"
}

# write the dataset to Snowflake
# sparkDF = spark.createDataFrame(...)
sparkDF.write.format("snowflake").options(**options).option("dbtable", "name_table").save()

# Connect to Snowflake and build data frame
df = spark.read.format("net.snowflake.spark.snowflake") \
    .options(**options) \
    .option("query", "select * from name_table") \
    .load()

Answer 2

我发现错误：

在第3步中，无需提及"https：//"

即

sfUrl="https://snowflake_username.snowflakecomputing.com"   # giving an error

sfUrl="snowflake_username.snowflakecomputing.com"          #works fine