search

Azure / Azure-DataFactory

Other
482 stars 586 forks source link

COPY activity dynamic column mapping not supported #278

Open spowellnet opened 3 years ago

spowellnet commented 3 years ago

So if you are using a COPY activity and you have columns with different names you have to use a hard coded dataset to provide the mapping. We use parametrized data sets for much of our work (1300 tables are loaded daily)

It feels odd that you can't pass a JSON structure into the copy activity to provide the column mapping. The whole I'll map based on CASE SENSITIVE column names and some ranodm guesswork at the data types (I'm sure it isn't random but as it's not published anywhere or indeed surfaced in any of the outputs it's difficult to figure out what's going on under the covers so it may as well be random)

Being able to PASS in a list of name mappings col1_a--> col1_b and or apply explicit CASTS rather than the implicit ones that currently get imposed feels like it would be a bit of a useful feature (a bit advanced admittedly)

CharithEkanayake commented 3 years ago

If you are willing to test out mapping dataflows this might help your course.

https://www.youtube.com/watch?v=nJjRzlFktlA&t=328s https://charith-ekanayake.medium.com/handling-dynamic-column-headers-with-azure-data-factory-mapping-dataflows-4169c7bc1e82

spowellnet commented 3 years ago

Yeah. That’s great if you have 1 thing to move. We’re using parameterised datasets. We have 1300 of these things. Building a crappy workaround for each one would take months. Also our experience suggests data flows are slow to the point of being useless. Something like 4-8 times slower than a straight copy. We’ve got a limited window (3 hrs overnight) to get these things loaded.