Created on 08-13-2024 02:24 AM - edited 08-13-2024 05:55 AM
I need to inject data read from elastic in Json format to two different tables having parent child relationship.
i.e
Name, Age, Role
David,35,1 Tay,23,2 John,27,2
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
Created 08-14-2024 12:52 PM
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
Created 08-13-2024 12:17 PM
@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.
Created on 08-13-2024 09:37 PM - edited 08-14-2024 12:46 AM
Created 08-14-2024 12:52 PM
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
Created 08-16-2024 03:30 AM
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
Created 08-16-2024 08:43 AM
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|