Skip to main content
Adyant001
Adyant001
Silver ⭐️⭐️
August 13, 2024
Solved

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

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

    shiva239
    shiva239Answer
    Gold ⭐️
    August 14, 2024

    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 in thread

    5 replies

    shiva239
    shiva239
    Gold ⭐️
    August 13, 2024

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

    Translate
    Adyant001
    Adyant001Author
    Silver ⭐️⭐️
    August 14, 2024
    [
    {
    "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 
    Translate
      shiva239
      shiva239Answer
      Gold ⭐️
      August 14, 2024

      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

       

       

      Translate
        Adyant001
        Adyant001Author
        Silver ⭐️⭐️
        August 16, 2024

        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   
         
        Translate
          shiva239
          shiva239
          Gold ⭐️
          August 16, 2024

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

          Translate
          Terms & ConditionsAccessibility statement