ODATA doesn't support expand in ADF

Question

ODATA doesn't support expand in ADF

Bajaj, Smita 1

Hi,
The expand verb does not seem to work in ADF with the ODATA connector. This means that we cannot fetch the child / nested items. The select works fine but not expand. Anyone has experience or workaround with this ?

1 answer

Your answer

Answer 1

svijay-MSFT 5,256 Microsoft Employee Moderator

Hello @Bajaj, Smita ,

Thanks for the question and using MS Q&A platform.

From this doc and from my testing ODATA connector - unfortunately, currently there is no support for the complex object type at this point of time.

Reference : https://learn.microsoft.com/en-us/azure/data-factory/connector-odata?tabs=data-factory

Having said, I had recently answered a thread dealing a similar requirement.

Thread : https://learn.microsoft.com/en-us/answers/questions/638195/odata-query-expand-is-not-executed-in-adf-copy-act.html

You could make use of REST API Connector and Mapping Data flow to make the necessary transformation to achieve the required output - You could get the nested output - flatten it to tabular structure and consume it for the further steps.

Note: Please refer to the comment of the answer for the RESTAPI + Data Flow Implementation.

Hope this will help. Please let us know if you have any further queries.

------------------------------

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
Want a reminder to come back and check responses? Here is how to subscribe to a notification
If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Bajaj, Smita 1 Reputation point

2021-12-14T04:48:27.893+00:00

Thanks @svijay-MSFT

Does DataFlow support flattening multiple nested levels for the JSON ? Apparently that support is not present in the Copy Data Activity mapping at present.
Also, The copy data activity mapping can map only the first element of the first nested level array to a target column . Does DataFlow have the same behaviour ?

From prior experience, found the initial warm up time to be quite high in DataFlow , considering the same can be implemented using Azure Functions / Databricks ; is there a reason apart from the 'low code experience' to opt for this approach. Just looking for the optimum approach and tool to achieve the outcome here.
svijay-MSFT 5,256 Reputation points Microsoft Employee Moderator

2021-12-16T01:13:21.053+00:00

Speaking from the ADF end - I think ADF Mappng flow is optimised for handling very large data and you can set/enable partitions at the sink level. Yes, this is not achievable through the Copy Activity. You will have to use the dataflow.
svijay-MSFT 5,256 Reputation points Microsoft Employee Moderator

2021-12-23T17:38:56.303+00:00

Hello @Bajaj, Smita ,

Just checking in to see if the above answer helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.
svijay-MSFT 5,256 Reputation points Microsoft Employee Moderator

2022-01-05T06:11:13.093+00:00

Hello @Bajaj, Smita ,

Following up to see if the above suggestion was helpful. And, if you have any further query do let us know.
Wing Yee Cheung 0 Reputation points

2024-11-08T15:42:38.2333333+00:00

The expand verb still not work in ADF with the ODATA connector? The select works fine but not expand. Anyone has experience or workaround with this ?

Share via

ODATA doesn't support expand in ADF

1 answer

Your answer