Referencing results of one query in another in Apache Nifi

Question

We are trying to migrate data from Oracle to Elasticsearch using Apache Nifi. We are trying to establish a one to many relationships(represented as multiple tables in Oracle) in a single elastic index. What we are trying to achieve can be summarized as below.

select * from table1. (The primary key of table1 is key1)

For each fetched record, We want to extract data from another table using the key from table 1. Something like

select * from table2 where foreign_key = key1. 

We checked the ExecuteSQLRecord processor which has select query and post query but are unable to figure out how to reference key from table1 in the query to table2

Please let us know if there are any other processors specifically designed for this use case.

Answer 1

There are several ways to achieve this

Creating Views in Oracle

You can create views in Oracle to build the queries that make the relationship primarykey-foreingkey. Thereby you can select directly from the view instead.

A small example

SQL> create table testpk ( c1 number , c2 number );

Table created.

SQL> alter table testpk add primary key ( c1 ) ;

Table altered.

SQL> create table testfk ( c3 number , c4 number );

Table created.

SQL>  alter table testfk add constraint fk_to_testpk FOREIGN KEY (c3) references testpk(c1) ;

Table altered.


SQL> insert into testpk values ( 1 , 1);

1 row created.

SQL> insert into testfk values ( 1 , 2 );

1 row created.

SQL> insert into testpk values ( 2 , 2 );

1 row created.

SQL> insert into testfk values ( 2 , 2 );

1 row created.

SQL> commit;

Commit complete.

SQL> create or replace force view my_test_view as select a.c1 , a.c2 , b.c3 , b.c4
  2  from testpk a join testfk b on ( a.c1 = b.c3 ) ;

View created.

SQL> select * from my_test_view ;

        C1         C2         C3         C4
---------- ---------- ---------- ----------
         1          1          1          2
         2          2          2          2

SQL>

Use queries directly

In your case, you need to run the query to make the relationship against the parent table, therefore you need a join:

select * from table2 inner join table1 where table2_foreingkey = table1_primarykey.

You want all records from table2 where the relationship parent key table 1 - child key table 2 matches, something you can do it with a normal inner join

If you ask me, I would create views to make the process more transparent in elastic search.