Dataverse - Synapse

Question

Wajih Arfaoui 70

Hello,

I am using Azure Synapse Link and Dataverse to get data from my CRM but then I am a couple of questions regarding the use of it for analysis.

Can I use the data i pulled from dataverse which is in a lake database in a same query as a table in the dedicated cluster so mainly a table in Synapse schema ?
when trying to query the table using serverless i get this error : External table 'dataverse_letecproduct_3338efaf29c245db98b4da54098dec.dbo.bd_contact_status' is not accessible because content of directory cannot be listed.
How can I include a table from lake database as a source in an ADF pipeline ?

Thank you

Smaran Thoomu 35,375 Reputation points Microsoft External Staff Moderator

2024-03-22T08:01:40.1933333+00:00

@Wajih Arfaoui Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

1 answer

Smaran Thoomu 35,375 Reputation points Microsoft External Staff Moderator

2024-03-22T08:01:40.1933333+00:00

@Wajih Arfaoui Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 1

Nandan Hegde 36,886 MVP Volunteer Moderator

w.r.t your queries :

when trying to query the table using serverless i get this error : External table 'dataverse_letecproduct_3338efaf29c245db98b4da54098dec.dbo.bd_contact_status' is not accessible because content of directory cannot be listed.

Ans : you need to provide storage blob data contributor access. Similar thread :

ANs : You can connect to the lake database from ADF via Azure SQL database linked service and then query the data you need to copy

Can I use the data i pulled from dataverse which is in a lake database in a same query as a table in the dedicated cluster so mainly a table in Synapse schema ?

Can you be little more specific on this? what do you mean by dedicated cluster?

Wajih Arfaoui 70 Reputation points

2024-03-18T13:37:31.8+00:00

Hello, Thank you for your answer, Imagine that i have an external table in lake database and a dedicated table in synapse. I want to create a sql query that does a join between both. how can i do this knowing that the external table is queried via the serveless pool and the table via the dedicated pool. thank you
Smaran Thoomu 35,375 Reputation points Microsoft External Staff Moderator

2024-03-20T03:51:39.8366667+00:00

Hi Wajih Arfaoui

To join an external table in a lake database with a dedicated table in Synapse, you can use the PolyBase feature in Synapse. PolyBase allows you to query data from external sources like Azure Data Lake Storage Gen2 and join it with data in your dedicated SQL pool.

You can refer this document for more information on polybase: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/load-data-overview