question Re: insert in one table get the ID and then include that id in next insert in Apache Nifi in Support Questions

insert in one table get the ID and then include that id in next insert in Apache Nifi

Adyant001 — Tue, 13 Aug 2024 12:55:20 GMT

I need to inject data read from elastic in Json format to two different tables having parent child relationship.

i.e

User table

Name, Age, Role
David,35,1

Tay,23,2

John,27,2

Role table:

1,admin

2,user

My requirement is to insert in user table first get the Role ID and then include that id in next insert to dump data in Role table

How can i achieve this thing Apache Nifi?@mburgess

Re: insert in one table get the ID and then include that id in next insert in Apache Nifi

shiva239 — Tue, 13 Aug 2024 19:17:43 GMT

@Adyant001 Can you share sample json structure ?

If source json contains both parent and child information, you can have two PutDatabaseRecord processors connected in series with right "Data Record Path" mentioned.

If child record needs information from parent level, JoltTransformJson processor can be used to create transformed json before passing to PutDatabaseRecord.

Re: insert in one table get the ID and then include that id in next insert in Apache Nifi

Adyant001 — Wed, 14 Aug 2024 07:46:11 GMT

[

{

"name": "sugam",

"age": 30,

"id": 1,

"role": {

"role": "Admin"

}

]

I wanted to put name ,age, and id to user table then get the id from user table and put it in role table with role as admin

Desired out put should be as below

user table:

Name age ID(pk)

sugam 30 1

Role Table:

Id(pk) Role_id(fk) Role_name

1 1 admin

@mburgess @SAMSAL

Re: insert in one table get the ID and then include that id in next insert in Apache Nifi

shiva239 — Wed, 14 Aug 2024 19:52:20 GMT

I would do this way. Please see if this works for you.

1. Use JolttransformJSON processor to alter the input JSON

Spec:

[
{
"operation": "shift",
"spec": {
"id": ["role.role_id", "user.id"],
"name": "user.name",
"age": "user.age",
"role": {
"role": "role.role_name"
}
}
}
]

2. Pass the transformed JSON to first PutDatabaseRecord processor to insert into user table

Set "Data Record Path" = user

3. Add another PutDatabaseRecord processor to insert into role table

Set "Data Record Path" = role

Re: insert in one table get the ID and then include that id in next insert in Apache Nifi

Adyant001 — Fri, 16 Aug 2024 10:30:08 GMT

HI Shiva239.

The solution seems working for me but the child table(role) has an autoincrement PK column.

SO how to insert value in autoincrement column

Role Table:

Id(pk auto increamne) Role_id(fk) Role_name

1 1 admin

Re: insert in one table get the ID and then include that id in next insert in Apache Nifi

shiva239 — Fri, 16 Aug 2024 15:43:00 GMT

@Adyant001

Oracle has auto increment IDENTITY feature as below.

Table definition takes care of setting primary key incremental value. No need to send this value in json payload

I think all other major RDBMS have similar feature to assign auto increment value.

CREATE TABLE roletab
(id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY
(START WITH 1 INCREMENT BY 1 NOCYCLE),
role_id NUMBER,
role_name VARCHAR(100),
PRIMARY KEY (id)
);

INSERT INTO roletab (role_id, role_name) VALUES (10, 'Admin');
INSERT INTO roletab (role_id, role_name) VALUES (20, 'Developer');
commit;

SELECT * FROM roletab;

ID |ROLE_ID|ROLE_NAME|
--+-------+---------+
1 | 10 | Admin |
2 | 20 |Developer|