How implementing data virtualization in Azure Synapse Analytics

pmscorca 877 Reputation points
2023-05-17T17:27:41.34+00:00

Hi,

inside Synapse Analytics I've implemented a dwh in a dedicated SQL pool, managing a preceeding data lake layer (ADLS gen2) and a SQL working area. Now I need to handle a new requirement: exposing some data in real-time.

I think to implement, if possible, a data virtualization solution in a parallel manner respect to the dwh. The data source for the ADLS gen2 data lake is a DB2 database.

So, how I could implement a data virtualization solution inside Synapse, in order to expose some DB2 data without writing any data both in the ADLS gen2 data lake and in the dedicated SQL pool dwh? Thanks

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,658 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Vinodh247 13,066 Reputation points
    2023-05-19T08:48:10.13+00:00

    Hi

    Thanks for reaching out to Microsoft Q&A.

    In SQL Server on-premise I can create a linked server to the DB2 source and then I can use the statement SELECT ... OPENQUERY(DB2linkedserver, ...).

    is it possible to implement a data virtualization solution inside Synapse

    I dont think it is possible in synapse, either you will have to create a linked service to DB2, run a pipeline with select statement as source and set the sink as sql server table or sink to file and query it using openrowset.

    Please Upvote and Accept as answer if the reply was helpful, this will be helpful to other community members.

    1 person found this answer helpful.

  2. AnnuKumari-MSFT 31,996 Reputation points Microsoft Employee
    2023-05-18T06:45:25.08+00:00

    Hi pmscorca ,

    Thankyou for using Microsoft Q&A platform and thanks for posting your question here. I understand you want to perform data virtualization using dedicated sql pool in Azure Synapse Analytics .

    Data Virtualization in SQL Server and Synapse Analytics : This video talks about implementation of data virtualization using polybase technical in Synapse DW.

    User's image

    Once the data is pulled in Synapse SQL pool, you can use power BI to create your visuals for analyzing the data.

    Hope it helps. Kindly accept the answer by clicking on Accept answer button if it is helpful. Thankyou