Share via

How to Load data from Azure SQL DB to Synapse Spark Datalake ?

Devender 61 Reputation points
2022-10-28T10:58:28.947+00:00

Hi Community,
I am looking for a Solution through which we can load the data from Azure SQL DB to Azure Synapse Spark Datalake (not in dedicated pool).

The Requirements are:

  1. we have a csv file in which data is present. Currently we are updating or inserting the data into csv file which we are reading through the spark datalake and loading into dataframes.
  2. But rather than using a csv file we want to load our csv data into Azure SQL DB and in future if any new updates or insert happen we should do directly in Azure SQL DB only.
  3. Currently we are doing our transformations in Synapse using Pyspark and reading the File data through the spark tables which is in our lake database. we have put those csv files in our Synapse ADLS and reading data from there only.
  4. We want to make a connection from the Azure SQL DB to Synapse Spark Datalake. So in Future if any upsert happen in SQL DB those changes will also reflect in our table in Spark datalake and when we are loading those tables in our Synapse notebook as a dataframe it should always pick up the latest Data.

Thanks in Advance for your Responses

Azure SQL Database
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,696 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Bhargava-MSFT 29,266 Reputation points Microsoft Employee
    2022-10-31T23:23:49.297+00:00

    Hello @Devender ,
    Welcome to the MS Q&A platform.

    Please correct me if my understanding is wrong.

    You are looking for a solution to load data from Azure SQL DB to the lake database (using serverless SQL endpoint).

    My understanding is you can't directly load Azure SQL DB tables into lake database using serverless SQL endpoint. As lake database holds the data from the external tables(lake database doesn't store data). but you can use Azure datalake store gen2 account to load the data into the Azure datalake storage.

    Please see this article explained how to export data from Azure SQL database to azure datalake storage.

    I hope this helps. Please let me know if you have any further questions.