Skip to main content
  • EN
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!
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.
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
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.
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.
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 😉
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

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

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


Community Stats

30,126
Posts
114,351
Replies
37,758
Members
Terms & ConditionsCookie 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.