Support Questions

Find answers, ask questions, and share your expertise

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

avatar
Explorer

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

1 ACCEPTED SOLUTION

avatar
Contributor

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

 

 

View solution in original post

5 REPLIES 5

avatar
Contributor

@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.

avatar
Explorer
[
{
"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   
 
 
 

avatar
Contributor

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

 

 

avatar
Explorer

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   
 

avatar
Contributor

@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 |
 | 20    |Developer|