Using Azure Synapse Analytics - can I use External Tables created using SQL Serveless pool in my Pipelines or data flows?

Xhevahir Mehalla 160 Reputation points
2024-06-04T13:15:37.9933333+00:00

Hello -

I have chosen Azure Synapse analytics tool as the ETL tool.

Here's the scenario:

  1. Copy data from oracle OCI to Azure Data lake storage
  2. Want to use SQL serverless pool to extract data from Data lake Storage and transform it and store into different directory within the data lake (raw/ folder to staging/ folder as parquet).
  3. I want to be able to refer to the tables created on my sql serverless pool (db - I create) and want to use these objects on my data flow activity and so on.

Can someone tells me if this is possible if i can do that at all as I have been told by my team that this is not possible?

Please help!

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,621 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. BhargavaGunnam-MSFT 28,616 Reputation points Microsoft Employee
    2024-06-04T18:21:32.9933333+00:00

    Hello Xhevahir Mehalla,

    Yes, using Synapse Analytics, you can create external tables with the SQL serverless pool and use them in your pipelines or data flows.

    The external tables act as virtual tables that point to data located in external storage, such as Azure Data Lake Storage. These tables can be used to read data from files or write data to files in Azure Storage, and they can be accessed and utilized within Synapse pipelines and data flows

    reference documents: https://video2.skills-academy.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop

    You can either use copy activity or dataflows(as they support synapse as data source) to connecting to external tables.

    other reference documents:

    https://video2.skills-academy.com/en-us/azure/data-factory/copy-activity-overview

    https://video2.skills-academy.com/en-us/azure/data-factory/data-flow-sink

    https://video2.skills-academy.com/en-us/azure/data-factory/data-flow-source

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

    0 comments