Communication between Azure Synapse and Azure SQL Server via Script in Azure Serverless SQL pool

Question

Are we able to use Azure Synapse sql pool (T-SQL querying) with complexe queries to combine data query from datalake to read parquet files (with the openrowset) and an other source like Azure sql database.

The idea is to use in the same script query to extract data from parquet files and data from an other server which is Azure sql in Synapse.

I searched about link in Synapse but this need to replicated all the tables we need into Synapse database. Do you have any suggestions? Thanks.

Answer 1

Azure Synapse workspace allows us to create a SQL Database built on top of a Data Lake, but it does not utilize Spark for management. This type of database can only be used with a serverless SQL pool.

Create external tables in Azure synapse

Here is an example to query data in a Synapse serverless SQL pool:

SELECT *
FROM OPENROWSET(
   BULK 'https://stgsynp.dfs.core.windows.net/folder02/employee/*.parquet',
   FORMAT = 'PARQUET'
) AS rows;

As you mentioned, you have parquet files in ADLS. You can also follow these steps and use OPENROWSET:

Step 1: Point to the folder that contains the parquet files.

Right-click on the folder Choose the file format.

It creates auto-generated code like below:

SELECT
    TOP 100 *
FROM
    OPENROWSET(
        BULK 'https://stgsynp.dfs.core.windows.net/folder02/employee/**',
        FORMAT = 'PARQUET'
    ) AS [result]

Results:

Reference: Query Data Lake with SQL Database in Azure Synapse – Part 2

Note: Dedicated SQL pools in Azure Synapse do not support the OPENROWSET function.

Learn more about How to use OPENROWSET using serverless SQL pool in Azure Synapse Analytics.