How to use in partition conditions dynamic content for SAP CDC?

Question

How to use in partition conditions dynamic content for SAP CDC?

Oleksandr Bondariev 0

I have data flow with SAP cdc source . And I try to create partitions using dynamic content.
User's image

Unfortunately, I didn't see anything in the documentation. So I tried lots of options that seem suitable to me but unsuccessfully.
For example this one:

 '{"FieldName":"ERDAT","Sign":"I","Option":"BT","Low":"20201025","High":"20201125"}'

Any help or pointers appreciated.

KranthiPakala-MSFT 46,627 Reputation points Microsoft Employee

2023-02-20T18:38:31.9166667+00:00

Hi @Oleksandr Bondariev ,

Welcome to Microsoft Q&A forum and thanks for reaching out here.

Could you please confirm what is the specific data source you are referring to?

When I tried to check on dynamic expression feature, wasn't able to find it in my data flow source.

Thank you
Oleksandr Bondariev 0 Reputation points

2023-02-20T18:46:21.17+00:00

Sorry. The correct source name is SAP CDC
KranthiPakala-MSFT 46,627 Reputation points Microsoft Employee

2023-02-22T00:09:57.5933333+00:00

@Oleksandr Bondariev Thanks for confirming the source. I did looked by creating a dummy linked service and dataset.
What I think is you can use between function in Expression builder and point the column name from the input/source schema as valueToCheck and then pass parameter values dynamically using dataflow parameter or based on your custom criteria for leftBound andrightBound and see if that helps to achieve your requirement. Sorry I don't have an SAP instance to provide exact screenshots of the implementation, but I tried my best creating a dummy SAP CDC linked service (without any source data or schema).

Hope this info helps. Please let us know how it goes.

Thank you
MAYER Hannes (ICS450.ext) 0 Reputation points

2023-03-03T22:57:16.85+00:00

I am working on the exact same topic.

I could not get it to work yet.

I also created a github doc request. There you can see how it looks.

https://github.com/MicrosoftDocs/azure-docs/issues/105829

I know how to construct the basic string - but I do not know how I sepcify no filtering - I tried NULL, empty string, single quotes with dataflow expression or not, but nothing really works - other then having tow dataflows for now.

I really wished there would be a clear discription for it. The Sample comes as ...

"[[{"fieldName": "ID","sign": "I", "option": "BT","low": "1","high": "4"}]]"
Hannes Mayer 0 Reputation points

2023-03-03T22:57:52.8333333+00:00

I am haveing the same question.

I posted a documetnation request at https://github.com/MicrosoftDocs/azure-docs/issues/105829

I can construct a basic query, but I do not know how to do AND or OR conditions yet, and I also do not know what I need to specify if I wo not want to filter (empty string did not work, single quote empty only if I pass it to the dataflow as expression, so the regular stuff then stops working) - its really hard.

I am looking forward to get some more clearifications.

At @KranthiPakala-MSFT , if yo want we can try together, I have an SAP System at hand.
Oleksandr Bondariev 0 Reputation points

2023-03-03T23:20:59.3133333+00:00

@KranthiPakala-MSFT thank you for your reply.
I tried this - between('ERDAT', '20201001', '20201030') and I have got error:

If add quotes "between('ERDAT', '20201001', '20201030')":

at Source 'srcS4hana': Please check error message, if it contains SAP server related error stacktrace, please contact SAP admin for assistance. Otherwise, please contact Microsoft support for further assistance. Error Message: DF-SAPODP-010 - Invalid source partition settings are provided with value:between('ERDAT', '20201001', '20201030') and exception:unknown token b Near: b, Exception: com.microsoft.dataflow.Utils$.failure(Utils.scala:76) com.microsoft.dataflow.store.sapodp.SapOdpUtil$.getSapSourcePartitionsFromString(SapOdpUtil.scala:41) com.microsoft.dataflow.store.sapodp.SapOdpUtil$.getSapSourcePartitions(SapOdpUtil.scala:15) com.microsoft.dataflow.store.sapodp.SapOdpReader.com$microsoft$dataflow$store$sapodp$SapOdpReader$$getSapQueryPayload(SapOdpStore.scala:307) com.microsoft.dataflow.store.sapodp.SapOdpReader$$anonfun$getTableSchemaOrPreviewData$1$$anonfun$30.apply(SapOdpStore.scala:292) com.microsoft.dataflow.store.sapodp.SapOdpReader$$anonfun$getTableSchemaOrPreviewData$1$$anonfun$30.apply(SapOdpStore.scala:292) scala.util.Try$.apply(Try.scala:192) com.microsoft.dataflow.store.sapodp.SapOdpReader$$anonfun$getTableSchemaOrPreviewData$1.apply(SapOdpStore.scala:292) com.microsoft.dataflow.store.sapodp.SapOdpReader$$anonfun$getTableSchemaOrPreviewData$1.apply(SapOdpStore.scala:288) scala.util.Try$.apply(Try.scala:192) com.microsoft.dataflow.store.sapodp.SapOdpReader.getTableSchemaOrPreviewData(SapOdpStore.scala:288) com.microsoft.dataflow.store.connectorservice.staging.SHIRBaseReader.read(SHIRBaseStore.scala:98) com.microsoft.dataflow.transformers.SourcePlanner$$anonfun$physicalPlan$1$$anonfun$apply$37$$anonfun$apply$38.apply(Source.scala:435) com.microsoft.dataflow.transformers.SourcePlanner$$anonfun$physicalPlan$1$$anonfun$apply$37$$anonfun$apply$38.apply(Source.scala:425) scala.util.Success.flatMap(Try.scala:231) com.microsoft.dataflow.transformers.SourcePlanner$$anonfun$physicalPlan$1$$anonfun$apply$37.apply(Source.scala:425) com.microsoft.dataflow.transformers.SourcePlanner$$anonfun$physicalPlan$1$$anonfun$apply$37.apply(Source.scala:418) scala.util.Success.flatMap(Try.scala:231) com.microsoft.dataflow.transformers.SourcePlanner$$anonfun$physicalPlan$1.apply(Source.scala:418) com.microsoft.dataflow.transformers.SourcePlanner$$anonfun$physicalPlan$1.apply(Source.scala:388) scala.util.Success.flatMap(Try.scala:231) com.microsoft.dataflow.transformers.SourcePlanner$.physicalPlan(Source.scala:388) com.microsoft.dataflow.FlowRunner$$anonfun$com$microsoft$dataflow$FlowRunner$$runner$1$$anon$2$$anonfun$117.apply(FlowRunner.scala:991) com.microsoft.dataflow.FlowRunner$$anonfun$com$microsoft$dataflow$FlowRunner$$runner$1$$anon$2$$anonfun$117.apply(FlowRunner.scala:981) scala.collection.immutable.List.takeWhile(List.scala:361) com.microsoft.dataflow.FlowRunner$$anonfun$com$microsoft$dataflow$FlowRunner$$runner$1$$anon$2.traverseStreamGroup(FlowRunner.scala:981) com.microsoft.dataflow.FlowRunner$$anonfun$com$microsoft$dataflow$FlowRunner$$runner$1$$anon$2.getNextTaskGroup(FlowRunner.scala:888) com.microsoft.dataflow.FlowCode$$anonfun$run$1$$anonfun$1$$anonfun$2.apply$mcV$sp(FlowRunner.scala:202) com.microsoft.dataflow.FlowCode$$anonfun$run$1$$anonfun$1$$anonfun$2.apply(FlowRunner.scala:193) com.microsoft.dataflow.FlowCode$$anonfun$run$1$$anonfun$1$$anonfun$2.apply(FlowRunner.scala:193) scala.util.Try$.apply(Try.scala:192) com.microsoft.dataflow.FlowCode$$anonfun$run$1$$anonfun$1.apply$mcV$sp(FlowRunner.scala:193) com.microsoft.dataflow.FlowCode$$anonfun$run$1$$anonfun$1.apply(FlowRunner.scala:183) com.microsoft.dataflow.FlowCode$$anonfun$run$1$$anonfun$1.apply(FlowRunner.scala:183) scala.util.Try$.apply(Try.scala:192) com.microsoft.dataflow.FlowCode$$anonfun$run$1.apply$mcV$sp(FlowRunner.scala:183) com.microsoft.dataflow.FlowCode$$anonfun$run$1.apply(FlowRunner.scala:175) com.microsoft.dataflow.FlowCode$$anonfun$run$1.apply(FlowRunner.scala:175) scala.concurrent.impl.Future$PromiseCompletingRunnable.liftedTree1$1(Future.scala:24) scala.concurrent.impl.Future$PromiseCompletingRunnable.run(Future.scala:24) scala.concurrent.impl.ExecutionContextImpl$AdaptedForkJoinTask.exec(ExecutionContextImpl.scala:121) scala.concurrent.forkjoin.ForkJoinTask.doExec(ForkJoinTask.java:260) scala.concurrent.forkjoin.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1339) scala.concurrent.forkjoin.ForkJoinPool.runWorker(ForkJoinPool.java:1979) scala.concurrent.forkjoin.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:107)
KranthiPakala-MSFT 46,627 Reputation points Microsoft Employee

2023-03-03T23:23:01.25+00:00

@Oleksandr Bondariev , MAYER Hannes (ICS450.ext), Hannes Mayer - Thank you for sharing additional information. I have reached out to respective product owners to get additional insights from them and awaiting a response. Will keep you posted as soon as I hear back from the team.

We appreciate your patience.

Thank you
KranthiPakala-MSFT 46,627 Reputation points Microsoft Employee

2023-03-22T23:49:12.59+00:00
Hi @Oleksandr Bondariev , All,

Thank you for your patience. I have an update from the internal team on this requirement.

It's a little trickier than we I tried initially. For the data flow parameter, the JSON containing the partitions needs to be stringified (because the MDF DSL is not JSON).

Easiest way to do this is as follows (see the screenshots below):

Put the JSON array containing your partition spec into a pipeline parameter

when passing it to the data flow, put a @string(...) around it

Regarding the JSON itself: each partition definition can consist of multiple conditions, and you can define multiple partitions in one JSON - so it's an array of arrays of objects. For one partition with a single condition, it looks like this:

[[{"fieldName":"CREATIONDATE","sign":"I","option":"BT","low":"20181001","high":"20181001"}]]

Hope this info helps. Let me know if you have any questions.
Francis Apel 11 Reputation points

2023-06-06T18:25:54.2933333+00:00

How would you define multiple conditions in one partition? What would that look like?

1 answer

Your answer

KranthiPakala-MSFT 46,627 Reputation points Microsoft Employee

2023-02-20T18:38:31.9166667+00:00

Hi @Oleksandr Bondariev ,

Welcome to Microsoft Q&A forum and thanks for reaching out here.

Could you please confirm what is the specific data source you are referring to?

When I tried to check on dynamic expression feature, wasn't able to find it in my data flow source.

Thank you
Oleksandr Bondariev 0 Reputation points

2023-02-20T18:46:21.17+00:00

Sorry. The correct source name is SAP CDC
KranthiPakala-MSFT 46,627 Reputation points Microsoft Employee

2023-02-22T00:09:57.5933333+00:00

@Oleksandr Bondariev Thanks for confirming the source. I did looked by creating a dummy linked service and dataset.
What I think is you can use between function in Expression builder and point the column name from the input/source schema as valueToCheck and then pass parameter values dynamically using dataflow parameter or based on your custom criteria for leftBound andrightBound and see if that helps to achieve your requirement. Sorry I don't have an SAP instance to provide exact screenshots of the implementation, but I tried my best creating a dummy SAP CDC linked service (without any source data or schema).

Hope this info helps. Please let us know how it goes.

Thank you
MAYER Hannes (ICS450.ext) 0 Reputation points

2023-03-03T22:57:16.85+00:00

I am working on the exact same topic.

I could not get it to work yet.

I also created a github doc request. There you can see how it looks.

https://github.com/MicrosoftDocs/azure-docs/issues/105829

I know how to construct the basic string - but I do not know how I sepcify no filtering - I tried NULL, empty string, single quotes with dataflow expression or not, but nothing really works - other then having tow dataflows for now.

I really wished there would be a clear discription for it. The Sample comes as ...

"[[{"fieldName": "ID","sign": "I", "option": "BT","low": "1","high": "4"}]]"
Hannes Mayer 0 Reputation points

2023-03-03T22:57:52.8333333+00:00

I am haveing the same question.

I posted a documetnation request at https://github.com/MicrosoftDocs/azure-docs/issues/105829

I can construct a basic query, but I do not know how to do AND or OR conditions yet, and I also do not know what I need to specify if I wo not want to filter (empty string did not work, single quote empty only if I pass it to the dataflow as expression, so the regular stuff then stops working) - its really hard.

I am looking forward to get some more clearifications.

At @KranthiPakala-MSFT , if yo want we can try together, I have an SAP System at hand.
KranthiPakala-MSFT 46,627 Reputation points Microsoft Employee

2023-03-03T23:23:01.25+00:00

@Oleksandr Bondariev , MAYER Hannes (ICS450.ext), Hannes Mayer - Thank you for sharing additional information. I have reached out to respective product owners to get additional insights from them and awaiting a response. Will keep you posted as soon as I hear back from the team.

We appreciate your patience.

Thank you
KranthiPakala-MSFT 46,627 Reputation points Microsoft Employee

2023-03-22T23:49:12.59+00:00

Hi @Oleksandr Bondariev , All,

Thank you for your patience. I have an update from the internal team on this requirement.

It's a little trickier than we I tried initially. For the data flow parameter, the JSON containing the partitions needs to be stringified (because the MDF DSL is not JSON).

Easiest way to do this is as follows (see the screenshots below):

Put the JSON array containing your partition spec into a pipeline parameter

when passing it to the data flow, put a @string(...) around it

Regarding the JSON itself: each partition definition can consist of multiple conditions, and you can define multiple partitions in one JSON - so it's an array of arrays of objects. For one partition with a single condition, it looks like this:

[[{"fieldName":"CREATIONDATE","sign":"I","option":"BT","low":"20181001","high":"20181001"}]]

Hope this info helps. Let me know if you have any questions.
Francis Apel 11 Reputation points

2023-06-06T18:25:54.2933333+00:00

How would you define multiple conditions in one partition? What would that look like?

Answer 1

Juan Diego Echavarria Ramirez 0

Hello,

I dealed with the same problem.

After going through all the comments, nothing worked for me.

I wanted to use some parameters:

FieldName and FilterValue.

The problem is reduced to string formatting.

This worked for me:

The operation that I did is: FieldName = FilterValue

'[[{"FieldName": "' + $FieldName + '","Sign":"I","Option":"EQ","Low": "' + $FilterValue + '"}]]'

Hope this works for you all too.

Greetings from Colombia! :)

YANG Victor -CORPORATE 1 Reputation point

2023-08-22T23:17:22.5833333+00:00

Hi, I don't see FilterValue is a function, can you show me how you put this string into data flow?

Thanks

Share via

How to use in partition conditions dynamic content for SAP CDC?

1 answer

Your answer