Question 294344 in Azure Question posted in
The official documentation to get you started can be found here.

Azure Synapse — Copy Activity — run a query that pulls from tables in more than one database?

I’m looking to use Azure Synapse’s Copy activity to pull data in a big select that has tables coming from more than a single database. Something like this in traditional SQL using Linked Servers —

Select t1.fielda, t1.fieldb, t2.fieldc, t2.fieldd
from [server1].[database1].[dbo].table1 t1 with (nolock)
LEFT OUTER JOIN [server1].[database2].[dbo].table2 t2 with (nolock)
  ON t1.fielda = t2.fieldq

I’d like to do this in the Copy activity, using its Query option to type the source code so that all of my source is being CM’d in the same place. I can make this work for pulls from a single database, and I can make this work specifying code like above that I send to a man-in-the-middle SQL*Server database with Linked Services set up to the two databases. But Synapse doesn’t seem smart enough to do that, and I don’t see any documentation even speaking to the concept.

The question — can Synapse Copy activity execute a query that can pull data from more than one database? I don’t seem to be able to specify the Link Service Connection in the source code to get it to do that.

Tags:

2

Answers


  1. 0

    Unfortunately synapse doesn’t support cross database queries. In case if you want to transform data from multiple sources within ADF, you would have to use dataflow activity with multiple source transformations

    Login or Signup to reply.
  2. 0

    I agree with @Nandan that you can make use of Dataflows to make cross database queries.

    Also if any of your table rows are less than 5000 rows, you can try the below workaround.

    Give the table which contains less than 5000 rows to the lookup activity. Use the lookup outout JSON array in the copy activity query.

    DECLARE @json1 NVARCHAR(MAX) = '@{activity('Lookup1').output.value}';

select t.id,t.name from openjson(@json1,'$') with(
    id int,
    name varchar(32),
    age int
) t 
left outer join sample2 on t.id=sample2.id;

    Use openjson() on the lookup output array and join it with the other table in the query like above. Here for sample I have used another lookup for the query. You can use the same query in the copy activity.

    enter image description here

    Result:

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search