Hi fellows,
I'm planning a flow to feed data from a relational database into
elasticsearch and would like to ask for some advice.
The database has an entity Person with 1:N relation to Email and Phone
(email and phone are objects, not just scalar values).
The flow needs to feed ES with JSON object representing the Person object
and his Emails and Phones (like below) and when any table record gets
updated the flow needs to "refresh" the JSON entity to update corresponding
ES document.
-- Person.json
{
name: 'Eric',
age: 43,
phones:[{number: '11-9999-9999', qualified: false, updatedAt: '2017-11-29
10:01:13'}],
emails: [{address: 'e...@domain.org', qualified: true, updatedAt:
'2017-11-10 09:12:35'}],
updatedAt: '2017-11-30 17:29:53'
}
My first thought was to write a flow that monitors changes on the Person,
Email and Phone tables using QueryDatabaseTable processor and once a change
is detected to route the record (which always contains a PersonId) to a
processor that performs multiple queries to mount the Person JSON. So far
the only way I could find to do it is trough script processors.
Given that there is a lot of new Record based processors with schema
supports I was wondering if there a better approach or an idiomatic way of
performing this kind of "sql lookups" in NiFi.
What do you think?
Cheers,
Eric
