Skip to main content
Question

Can you use SchemaMapper or BulkAttributeRenamer with a dynamic schema writer?

  • December 11, 2014
  • 8 replies
  • 40 views
M
I'm trying to write data from one database format to another. The columns in the source tables have generic names, such as ATT-01, ATT-02, etc. A separate table contains the aliases for the columns, such as RoadName for ATT-01, BuildingName for ATT-02.

 

 

Because I have hundreds of tables to process, many with more than 20 columns, I wanted to use dynamic schema with my writer dataset to quickly reproduce the database in the destination format with the appropriate structure for each table (i.e., I don't want any unnecessary columns in each of my tables). Unfortunately, I can't find a way to rename the columns and still use the schema from the source database.

 

 

I don't think that FME can handle both renaming attributes and dynamic schema in a single flow. I was wondering if anyone had any ideas, or to just let me know that I'm wasting my time.

 

 

Thanks!

8 replies

M
  • melvin
  • Author
  • December 11, 2014
For now, I'm going to use system tables and the lookup table to create a new database to add as a workspace resource. If anyone has any ideas on how to encapsulate this into a single process, I'm all ears.
takashi
Contributor
Forum|alt.badge.img+19
  • takashiContributor
  • Contributor
  • December 12, 2014
Hi,

 

 

In such a case, I create a common table for the SchemaMapper (Attribute Mapping) and a Dynamic Schema writer feature type.

 

I posted an article concerning it to my blog several months ago.

 

Common Table for SchemaMapper and Dynamic Schema (http://fme-memorandum-takashi.blogspot.com/2014/02/common-table-for-schemamapper-and.html)

 

I think the trick can also be applied to your case.

 

Hope this helps.

 

 

Takashi
takashi
Contributor
Forum|alt.badge.img+19
  • takashiContributor
  • Contributor
  • December 12, 2014
In addition, Schema (Any Format) reader -> ListExploder -> CSV writer can be used to create a table that contains source field names and FME data type names.

 

You can then modify it to the common table.
M
  • melvin
  • Author
  • December 12, 2014
Thanks! Your first suggestion was pretty much what I was going to do, but I didn't think of merging the 2 tables into one. And I didn't think of using ListExploder to expose the reader schema -- great suggestion. That should cut down on some time creating data types.
takashi
Contributor
Forum|alt.badge.img+19
  • takashiContributor
  • Contributor
  • December 12, 2014
Yes, the data flow from the Schema reader to the CSV writer will save your time to create the table. Additionally, if you already have a table that defines the relations between each generic name and its alias, you can add a process that merges aliases to the schema features to the data flow. It saves more time and effort.

 

wisdom of a lazy person who don't want move hands ;)
gio
Contributor
Forum|alt.badge.img+15
  • gioContributor
  • Contributor
  • December 12, 2014
You could also start with SQL-reader by reading usertable table_columnnames

 

Then->attrbitue explode->map to your newcolumnnames if exist->write to csv/txt->initiate workspacecaller->read csv/txt = shema with new attrbutenames
taojunabc
Participant
Forum|alt.badge.img+6
  • taojunabcParticipant
  • Participant
  • November 6, 2016

If you use SchemaSetter (a custom transformer in FME Hub), you will only need to have a table that contains the original field name and the name of the field after the change, you can achieve the desired purpose.

schemamapper-dynamic.zip

taojunabc
Participant
Forum|alt.badge.img+6
  • taojunabcParticipant
  • Participant
  • November 6, 2016

Sorry, in the example above, schemasetter will have some additional attributes from SchemaMapper output, and I found that SchemaSetter can not be obtained according to the original attribute of sequential access to schema feature, so I also constructed a working space. It does not use SchemaSetter.

schemamapper-dynamic2.zip

 

Reply


Helpful Members This Week

Community Stats

30,942
Posts
117,252
Replies
38,742
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.