Skip to main content
Solved

schema mapper and a dynamic where clause

  • November 15, 2017
  • 5 replies
  • 93 views
arthy
Contributor
Forum|alt.badge.img+8
  • arthyContributor
  • Contributor

Hi,

I have one table that I'm using for several schema mapping depending on the object.

To do that my schema mapping is a combination of a test filter with several outports and each of these outport is connected a schema mapper (see below). All the schema mappers are reading the same table but their where clause is related to the the condition used in the Test filter.

Is there a way to dynamically change the where clause inside the schema mapper transformer so that instead of having this complex custom transformer, I can use a simple single schema mapper transformer?

 

 

Thanks

Best answer by mark2atsafe

That's an excellent question, because whenever you duplicate transformers like that there is normally a better way to do things.

However, I think this case is the exception to that. A different where clause would effectively give a different lookup table per feature, and that isn't something the transformer is designed to handle.

Interestingly... the SchemaMapper appears to be feature based; a feature that enters then emerges immediately, not waiting for other features. So it might be possible to create a workaround.

I would suggest trying:

  • Create a second output connection from the Input port to a FeatureReader
  • Use the FeatureReader to read the data from the database table using the required where clause (which can be defined as an attribute).
  • Use a FeatureWriter to write that data to a plain lookup file (like CSV or text)
  • Then have the SchemaMapper point to that CSV file as the lookup source
  • Finally set the connection runtime order so the FeatureReader/Writer are triggered first

What I hope happens is that each incoming feature goes to the SchemaMapper, but by the time it arrives the CSV data has been overwritten. That way it gets the unique lookup table you want.

I don't guarantee that will work (it might just cache the lookup of the first feature), and you might not consider it worth the effort trying to set it up (it's a fair effort for little payoff), but if you're inquisitive then that's what I would try first,

Hope this helps

Mark

View original
Did this help you find an answer to your question?

5 replies

mark2atsafe
Safer
Forum|alt.badge.img+43
  • mark2atsafeSafer
  • Safer
  • Best Answer
  • November 15, 2017

That's an excellent question, because whenever you duplicate transformers like that there is normally a better way to do things.

However, I think this case is the exception to that. A different where clause would effectively give a different lookup table per feature, and that isn't something the transformer is designed to handle.

Interestingly... the SchemaMapper appears to be feature based; a feature that enters then emerges immediately, not waiting for other features. So it might be possible to create a workaround.

I would suggest trying:

  • Create a second output connection from the Input port to a FeatureReader
  • Use the FeatureReader to read the data from the database table using the required where clause (which can be defined as an attribute).
  • Use a FeatureWriter to write that data to a plain lookup file (like CSV or text)
  • Then have the SchemaMapper point to that CSV file as the lookup source
  • Finally set the connection runtime order so the FeatureReader/Writer are triggered first

What I hope happens is that each incoming feature goes to the SchemaMapper, but by the time it arrives the CSV data has been overwritten. That way it gets the unique lookup table you want.

I don't guarantee that will work (it might just cache the lookup of the first feature), and you might not consider it worth the effort trying to set it up (it's a fair effort for little payoff), but if you're inquisitive then that's what I would try first,

Hope this helps

Mark

mark2atsafe
Safer
Forum|alt.badge.img+43
  • mark2atsafeSafer
  • Safer
  • November 15, 2017
mark2atsafe wrote:

That's an excellent question, because whenever you duplicate transformers like that there is normally a better way to do things.

However, I think this case is the exception to that. A different where clause would effectively give a different lookup table per feature, and that isn't something the transformer is designed to handle.

Interestingly... the SchemaMapper appears to be feature based; a feature that enters then emerges immediately, not waiting for other features. So it might be possible to create a workaround.

I would suggest trying:

  • Create a second output connection from the Input port to a FeatureReader
  • Use the FeatureReader to read the data from the database table using the required where clause (which can be defined as an attribute).
  • Use a FeatureWriter to write that data to a plain lookup file (like CSV or text)
  • Then have the SchemaMapper point to that CSV file as the lookup source
  • Finally set the connection runtime order so the FeatureReader/Writer are triggered first

What I hope happens is that each incoming feature goes to the SchemaMapper, but by the time it arrives the CSV data has been overwritten. That way it gets the unique lookup table you want.

I don't guarantee that will work (it might just cache the lookup of the first feature), and you might not consider it worth the effort trying to set it up (it's a fair effort for little payoff), but if you're inquisitive then that's what I would try first,

Hope this helps

Mark

In fact thinking about it the FeatureWriter might hold the output connection open longer than we'd want, so the AttributeFileWriter might be a better choice there.

 

P
Forum|alt.badge.img
  • peterx
  • May 15, 2018
mark2atsafe wrote:

That's an excellent question, because whenever you duplicate transformers like that there is normally a better way to do things.

However, I think this case is the exception to that. A different where clause would effectively give a different lookup table per feature, and that isn't something the transformer is designed to handle.

Interestingly... the SchemaMapper appears to be feature based; a feature that enters then emerges immediately, not waiting for other features. So it might be possible to create a workaround.

I would suggest trying:

  • Create a second output connection from the Input port to a FeatureReader
  • Use the FeatureReader to read the data from the database table using the required where clause (which can be defined as an attribute).
  • Use a FeatureWriter to write that data to a plain lookup file (like CSV or text)
  • Then have the SchemaMapper point to that CSV file as the lookup source
  • Finally set the connection runtime order so the FeatureReader/Writer are triggered first

What I hope happens is that each incoming feature goes to the SchemaMapper, but by the time it arrives the CSV data has been overwritten. That way it gets the unique lookup table you want.

I don't guarantee that will work (it might just cache the lookup of the first feature), and you might not consider it worth the effort trying to set it up (it's a fair effort for little payoff), but if you're inquisitive then that's what I would try first,

Hope this helps

Mark

I'm struggling with this same problem and while I appreciate this solution (I'd considered something similar) I think this approach is too unreliable to take a punt on. It's disappointing as a real solution to this, could be so powerful.

 

 

Edit: I think have a working solution below.
P
Forum|alt.badge.img
  • peterx
  • May 15, 2018

I've managed to implement this using a FeatureMerger and a PythonCaller - basically:

1. Assume you have a list of dynamic mapping fields (source and destination attribute names) based on a Type or Category or other condition

2. Feature Merge your Feature with the list of Fields using this Type/Category/Condition - ensure you choose "Generate List" so you still only have 1 row per Feature.

3. You may need to pre-declare the list of possible destination Attributes and expose them in the PythonCaller (I'm never really sure when this is needed) 

4. Configure the following Python to suit your needs:

Source = feature.getAttribute('Mapping{}.Source')
Destination = feature.getAttribute('Mapping{}.Destination')

for i, (a, b) in enumerate(zip(Source, Destination)):           
	sourceValue = feature.getAttribute(a) 	if sourceValue:
		feature.setAttribute(b, sourceValue )
<br>
brown3k
Contributor
Forum|alt.badge.img+4
  • brown3kContributor
  • Contributor
  • September 18, 2018

@peterx I really like the PythonCaller solution. My data sits in a cloud-based database that I can extract into FME using an HTTPCaller and I wanted a way to map the attributes without having to write the mappings into a file just for the SchemaMapper.

Reply


Helpful Members This Week

Community Stats

30,921
Posts
117,179
Replies
38,725
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.