How to DUMP / UNLOAD / EXPORT data from Azure Synapse SQL Pool to Data Lake Gen 2 Blob Storage

Mariusz Zyśk 16 Reputation points
2021-02-24T13:24:24.587+00:00

Hello there,
quick question: how do I unload / dump / export table data from Synapse SQL pool into file on Blob Storage?
Can not find anything about that in official documentation. Could you please help me out here?

Azure Data Lake Storage
Azure Data Lake Storage
An Azure service that provides an enterprise-wide hyper-scale repository for big data analytic workloads and is integrated with Azure Blob Storage.
1,338 questions
Azure Blob Storage
Azure Blob Storage
An Azure service that stores unstructured data in the cloud as blobs.
2,427 questions
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,362 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Vaibhav Chaudhari 38,576 Reputation points
    2021-02-24T15:10:59.397+00:00

    Azure data factory can be used to create a pipeline that will copy data from SQL pool to Blob storage. This pipeline can be scheduled to run daily or schedule as required.

    In below document, just replace SQL server settings by SQL pool settings

    https://learn.microsoft.com/en-us/azure/data-factory/tutorial-hybrid-copy-portal

    ----------

    Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

    1 person found this answer helpful.
    0 comments
  2. Mariusz Zyśk 16 Reputation points
    2021-02-24T15:15:15.85+00:00

    Hi,
    I don't want to use any external services. Is there any chance to use UNLOAD command, just the same like COPY INTO from Blob Storage?

    0 comments
  3. Vaibhav Chaudhari 38,576 Reputation points
    2021-02-24T15:24:09.167+00:00

    Check if this can be achieved somehow using - Create external table as select

    https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/create-external-table-as-select
    https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-table-as-select-transact-sql?view=aps-pdw-2016-au7

    ----------

    Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav