topic Connect Spark in SQL database

Connect Spark

muhssamy — Wed, 27 Nov 2024 05:40:09 GMT

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 ?

Re: Connect Spark

Poojara_D12 — Wed, 27 Nov 2024 07:07:43 GMT

Hi muhssamy

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

Set up a Spark Cluster: First, create a Spark cluster in your Fabric workspace.
Use Notebooks for Data Engineering: Open a notebook in Fabric and use PySpark or Spark SQL to connect to your SQL database.
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!!

Re: Connect Spark

muhssamy — Wed, 27 Nov 2024 09:01:14 GMT

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

Re: Connect Spark

Anonymous — Thu, 28 Nov 2024 03:09:30 GMT

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.

Re: Connect Spark

muhssamy — Thu, 28 Nov 2024 09:25:31 GMT

will this work with Account with MFA ?

Re: Connect Spark

Anonymous — Thu, 28 Nov 2024 09:35:19 GMT

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.

Re: Connect Spark

muhssamy — Mon, 02 Dec 2024 11:28:07 GMT

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()

Re: Connect Spark

karthikvalluri — Fri, 24 Jan 2025 07:53:57 GMT

Hi Poojara_D12 Getting the error as "Spark_Ambiguous_JDBC_SQLServerException"

How to resolve this?

Re: Connect Spark

g3kuser — Mon, 17 Feb 2025 06:50:36 GMT

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