Question about Databricks data load

Question

Hello MS support:

I have 2 question about databricks data ETL via ADF.

1. I have a Excel file, how can I load the Excel file automatically from internal network to databricks?
2. I have a data table in databricks, is that possible download it to local sql server database?

Thanks,
Jacky

Answer 1

For the first part, does this post answer your question about Excel files (https://learn.microsoft.com/en-us/answers/questions/239732/excel-as-a-source-in-adf.html)?

From an on-premises source you may also need a Data Gateway installed (https://learn.microsoft.com/en-us/data-integration/gateway/)

You can then save the content to Azure Data Lake Storage in Parquet format which Databricks will then be able to access.

For the second part, look at the SQL connector available in Azure Databricks (https://learn.microsoft.com/en-us/azure/databricks/data/data-sources/sql-databases) and then update your notebook code so that when you populate the table in Databricks you also write a copy of the dataframe to SQL Server.

Answer 2

Hello @Dong, Jacky ,

In additional to @Martin Cairney response.

Note: You can connect to SQL databases using JDBC and Apache Spark connector.

If you are using JDBC connector you no need to import any packages.

Reference: SQL databases using JDBC

If you are using Apache Spark Connector you need to import the below packages as per the Databricks Runtime which you are using:

Reference: SQL Databases using the Apache Spark connector and Process & Analyze SQL Server Data in Azure Databricks

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators