Connect Spark

[muhssamy]

in the documentation overview 

it is mentioned that i can create and manage my data in sql database using spark

can anyone help me how to do this ?

[g3kuser]

Hi,

I want to connect using Workspace Space Identity and was wondering how to achieve it from notebook given I don't get to see the secret for the identity created? 

Thanks,

Gayatri

[muhssamy · Accepted solution]

Thanks All 
i authorized using service principle using the following

jdbc_url = (
    "jdbc:sqlserver://<your-server-name>.database.windows.net:1433;"
    "database=<your-database-name>;"
    "authentication=ActiveDirectoryServicePrincipal;"
    "encrypt=true;"
    "trustServerCertificate=true;"
)
sql_db_options = {
    "url": jdbc_url,
    "dbtable": "<your-table-name>",
    "user": "<service-principal-client-id>",
    "password": "<service-principal-client-secret>"
}
df = spark.read.format("jdbc").options(**sql_db_options).load()
df.show()

[Poojara_D12]

Hi @muhssamy 

To create and manage your data in a SQL database using Spark within Microsoft Fabric:

1. Set up a Spark Cluster: First, create a Spark cluster in your Fabric workspace.

2. Use Notebooks for Data Engineering: Open a notebook in Fabric and use PySpark or Spark SQL to connect to your SQL database.

3. Data Management: Use Spark to perform operations like loading data, transforming it, and writing back to your SQL database. Here's a simple example:

# Example PySpark code to read from SQL and write to Spark DataFrame
jdbc_url = "jdbc:sqlserver://<server_name>:<port>;databaseName=<db_name>"
properties = {"user": "<username>", "password": "<password>", "driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver"}

# Load data from SQL Server
sql_df = spark.read.jdbc(url=jdbc_url, table="<your_table>", properties=properties)

# Perform transformation using Spark
transformed_df = sql_df.filter(sql_df["column_name"] > 100)

# Write data back to SQL
transformed_df.write.jdbc(url=jdbc_url, table="<new_table>", mode="overwrite", properties=properties)

4. Design Pipelines: Use Fabric's Data Engineering pipelines to automate data movement from your SQL database to Spark and back.

By following these steps, you can efficiently manage and process your SQL database data using Spark in Fabric.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS  (360 remaining for 500 SUBS) Please support!!

[karthikvalluri]

Hi @Poojara_D12 Getting the error as "Spark_Ambiguous_JDBC_SQLServerException" 

How to resolve this?

[muhssamy]

the problem here what is the user name and passoword
there is no sql user for this database

[v-xinruzhu-msft]

Hello @muhssamy 

The username is your account that sign in the fabric, and the password is the password is the your account password that sign in the fabric, you can see your server name and database name in settings of database. click the settings icon in home tab.

 Then click the connection strings, in this tab, it offers different strings of the data connection.

e.g You can click the php, then it will dislay your server information.

You can refer to the following link to know more.

Connect to your SQL database - Microsoft Fabric | Microsoft Learn

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[muhssamy]

will this work with Account with MFA ?

[v-xinruzhu-msft]

Hi @muhssamy 

Yes, after testing it can work for Account with MFA, but you need to configure the access for the account.

• To connect to a database, a user must have at least the Read permission in Fabric for the Fabric database item

You can refer to the following link.

Authorization in SQL database - Microsoft Fabric | Microsoft Learn

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.