Trying to connect snowflake with fabric notebook to create a dataframe using jdbc connection

[Abinaya95]

I'm trying to connect snowflake with notebook using this below code. I referred in the documentation they mentioned if we want to create a frame we need to import jdbc jar file and spark-snowflake jar file. i imported it in the lakehouse file and called in this notebook i got error and tried in multiple ways and got error. i'm added the error also. can you anyone please fix this issue to get data in a dataframe.

while using this below using python snowflake connector i got output but couldn't able to convert this into a spark dataframe. to mention the core of issue only i mentioned this code. please take a reference

[v-jingzhan-msft · Accepted solution]

Additionally, as you are able to use the Python Snowflake Connector to get the data, you can convert the result into a Pandas dataframe, then convert the Pandas dataframe into a Spark dataframe. Although this method is a bit roundabout, it is feasible.

# Get data from snowflake and convert it into a Pandas dataframe

import snowflake.connector
import pandas as pd

# Establish connection
conn = snowflake.connector.connect(
    user='YOUR_USER',
    password='YOUR_PASSWORD',
    account='YOUR_ACCOUNT'
)

# Execute query
query = "SELECT * FROM YOUR_TABLE_NAME"
cursor = conn.cursor()
cursor.execute(query)

# Fetch result into a pandas DataFrame
df = cursor.fetch_pandas_all()

# Close cursor and connection
cursor.close()
conn.close()

Reference: Reading data from a Snowflake database to a pandas DataFrame 

# Convert a Pandas dataframe into a Spark dataframe

from pyspark import SparkConf
import pandas as pd

conf = SparkConf()
# Enable Arrow-based spark configuration
conf.set("spark.sql.execution.arrow.enabled", "true")

# Generate a pandas DataFrame
data = [(1,'Product A',10),(2,'Product B',20),(3,'Product C',30)]
pdf = pd.DataFrame(data)

# Create a Spark DataFrame from a pandas DataFrame using Arrow
df = spark.createDataFrame(pdf)
# display(df)

Reference: 

5 Steps to Converting Python Jobs to PySpark | by Mohini Kalamkar | Hashmap, an NTT DATA Company | M...

Convert between PySpark and pandas DataFrames | Databricks on AWS

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[v-jingzhan-msft]

Hi @Abinaya95 

mssparkutils.fs.ls is to list the content of a directory. It’ll give you file properties like name, path, size, modification time, and whether it’s a directory or a file. It probably doesn't install the jar package into the Session where the code is running. You may try the following method. 

# Method 1:

The .jar files are support at notebook sessions with following command. The code cell is using Lakehouse's storage as an example. At the notebook explorer, you can copy the full file ABFS path and replace in the code.

%%configure -f
{
    "conf": {
        "spark.jars": "abfss://<<Lakehouse prefix>>.dfs.fabric.microsoft.com/<<path to JAR file>>/<<JAR file name>>.jar",
    }
}

Reference doc: Manage Jar libraries through inline installation

# Method 2:

This code is found from How to connect Snowflake with PySpark? - Stack Overflow. You may give it a try. 

from pyspark import SparkConf

conf = SparkConf()
conf.set('spark.jars','/path/to/driver/snowflake-jdbc-3.12.17.jar , \
/path/to/connector/spark-snowflake_2.12-2.10.0-spark_3.2.jar')

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!