question

PhilipORourke-7866 avatar image
0 Votes"
PhilipORourke-7866 asked PhilipORourke-7866 commented

azure data factory view code and stop direct path loads to oracle

Hi,

We are using pipelines to copy data from oracle to azure.

We accidentally set the sink (target) last week to a table on oracle and it inserted data into a table which had a primary/unique key constraint on it and thus made the index unusable.

It looks like azure has used a direct path load to write data to data blocks rather than using a conventional path load which would have failed due to the presence of the unique key constraint.

I have a few questions

1) How can we see the exact sql azure uses when writes to an oracle or azure sl database? Azure data factory graphical tool but woudl like to see the exact sql it generates as belive it will have have used an insert with hints on it when loaded o oracle.

2) How can we disable the direct path load the copy activity seems to use when using copy activity to copy data to oracle target.

3) Does sql server/azure slq server database have the concept of direct path load whereby can write to data blocks directly thus bypassing primary key constraints on tables?
Concern is if someone runs a copy activity to a table when sink has target it doesn't obey the unique key constraint.


Many Thanks

azure-data-factory
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @PhilipORourke-7866 ,

Just checking in to see if the below answer helped. If this answers your query, do click 130616-image.png and upvote 130671-image.png for the same. And, if you have any further query do let us know.

0 Votes 0 ·

Hi ShaikMaheer,


Yes helped thanks but have you heard back from internal team and oracle direct path loads behaviour.

Thanks

0 Votes 0 ·

1 Answer

ShaikMaheer-MSFT avatar image
0 Votes"
ShaikMaheer-MSFT answered PhilipORourke-7866 commented

Hi @PhilipORourke-7866 ,

Thank you posting query in Microsoft Q&A Platform.

Usually, When we have primary key or Unique key constrains we should see error while loading duplicate values. But in your case, you are saying its not happening with oracle. I am checking with internal team more on this and get back with updates.

There is no way to see SQL getting used behind the scenes of copy activity and I don't think we can enable or disable some behavior if there is no such option available inside Copy activity GUI.

SQL server don't have Direct path load for loading bulk data. Instead in SQL server Bulk Copy Program (BCP) for loading bulk data.


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,

Thanks for update. Looking forward to reply with regards copy activity and oracle as sink

I'm new to data factory.

For data flows other than copy activity, can you see the sql code which gets generated?

Thanks

0 Votes 0 ·

Hi @PhilipORourke-7866 - No we cannot see SQL which may get generate behind the scenes with data flow and also with Copy activity. Regarding direct path load I am checking with internal team. I will share updates. Thank you.

0 Votes 0 ·

Hi,

Thanks for confirming cannot see the SQL

0 Votes 0 ·