Skip to main content
  • EN

Hi.

What is the best way to generate values for new numeric identy fields in Redshift which not exist in source structure of tables and bind this new values with other data?

For example there is table

IDPERSONTHING1JhonCar2AdamCar3JhonPen

I want to split this table into three tables:

1) PESRONS

IDPERSON1Jhon2Adam

2) THINGS

IDTHING1Car2Pen

3) LINKS

IDPERSON_IDTHING_ID111221312

So, I need to negerate ID for tables PESRONS and THINGS, and bind persons and things by this new identities in table LINKS.

How I can do that in FME desktop?

Thanks.

Hi @useytkaziev, maybe this is a plain way.

  1. Branch the data flow into two streams.

  2. On a stream, create 'PERSONS' features. AttributeRemover: remove 'THING' -> DuplicateFilter (called DuplicateRemover in FME 2015 and ealier) : remove duplicate 'PERSON' -> Counter: overwrite 'ID' with new 1-based sequential number.

  3. On another stream, create 'THINGS' features in the same way.

  4. FeatureMerger: merge the 'PERSONS' to the original features using 'PERSON' as the join key. Here you can add the prefix 'PERSON_' to attributes of 'PERSONS' features with the parameter setting for the FeatureMerger (Accumulation Mode: Prefix Supplier).
  5. FeatureMerger: merge the 'THINGS' to the features output from the Merged port of the first FeatureMerger using 'THING' as the join key, adding prefix 'THING_' to supplier attributes.

The features output from the Merged port of the second FeatureMerger are the 'LINKS' features.

Hi @useytkaziev, maybe this is a plain way.

  1. Branch the data flow into two streams.

  2. On a stream, create 'PERSONS' features. AttributeRemover: remove 'THING' -> DuplicateFilter (called DuplicateRemover in FME 2015 and ealier) : remove duplicate 'PERSON' -> Counter: overwrite 'ID' with new 1-based sequential number.

  3. On another stream, create 'THINGS' features in the same way.

  4. FeatureMerger: merge the 'PERSONS' to the original features using 'PERSON' as the join key. Here you can add the prefix 'PERSON_' to attributes of 'PERSONS' features with the parameter setting for the FeatureMerger (Accumulation Mode: Prefix Supplier).
  5. FeatureMerger: merge the 'THINGS' to the features output from the Merged port of the first FeatureMerger using 'THING' as the join key, adding prefix 'THING_' to supplier attributes.

The features output from the Merged port of the second FeatureMerger are the 'LINKS' features.

Hi, takashi.

Thanks for your reply. I will try this method.

Hi @useytkaziev, maybe this is a plain way.

  1. Branch the data flow into two streams.

  2. On a stream, create 'PERSONS' features. AttributeRemover: remove 'THING' -> DuplicateFilter (called DuplicateRemover in FME 2015 and ealier) : remove duplicate 'PERSON' -> Counter: overwrite 'ID' with new 1-based sequential number.

  3. On another stream, create 'THINGS' features in the same way.

  4. FeatureMerger: merge the 'PERSONS' to the original features using 'PERSON' as the join key. Here you can add the prefix 'PERSON_' to attributes of 'PERSONS' features with the parameter setting for the FeatureMerger (Accumulation Mode: Prefix Supplier).
  5. FeatureMerger: merge the 'THINGS' to the features output from the Merged port of the first FeatureMerger using 'THING' as the join key, adding prefix 'THING_' to supplier attributes.

The features output from the Merged port of the second FeatureMerger are the 'LINKS' features.

I wondered if I could do this any simpler, but I don't think so. The one thing I did have to be careful of, was to set a different Counter Name for each of the two Counter transformers. Other than that, Takashi's solution works like a charm.

I've attached the workspace I created below...

tablesplitter.fmw

Hi @useytkaziev, maybe this is a plain  way.

  1. Branch the data flow into two streams.

  2. On a stream, create 'PERSONS' features. AttributeRemover: remove 'THING' -> DuplicateFilter (called DuplicateRemover in FME 2015 and ealier) : remove duplicate 'PERSON' -> Counter: overwrite 'ID' with new 1-based sequential number.

  3. On another stream, create 'THINGS' features in the same way.

  4. FeatureMerger: merge the 'PERSONS' to the original features using 'PERSON' as the join key. Here you can add the prefix 'PERSON_' to attributes of 'PERSONS' features with the parameter setting for the FeatureMerger (Accumulation Mode: Prefix Supplier).
  5. FeatureMerger: merge the 'THINGS' to the features output from the Merged port of the first FeatureMerger using 'THING' as the join key, adding prefix 'THING_' to supplier attributes.

The features output from the Merged port of the second FeatureMerger are the 'LINKS' features.

As @mark2catsafe mentioned, there may not be any simpler way, but if you are familiar with SQL, the InlineQuerier could also be used effectively. e.g.

0684Q00000ArMEAQA3.png

SQL Statements

1. Links

select a.ID as ID, b.ID as Person_ID, c.ID as Thing_ID
from Source as a
inner join Persons as b on b.Person = a.Person
inner join Things as c on c.Thing = a.Thing
order by a.ID

2. Persons

select ID, Person from Persons order by ID

3. Things

select ID, Thing from Things order by ID

Reply


Community Stats

30,121
Posts
114,321
Replies
37,749
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.