Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Get duplicate records in MySql

avatar
Explorer

I'm using "PutDatabaseRecord (NiFi 1.25.0)" for data insert/update purposes in MySql.
Imagine, 
I have a table called "Customer" which has running primary key called "Id" and the another field call "id_from_core". The only mentioned "Id" field as auto generated primary key in "Customer" table. 
But when data insert through the NiFi flow, I mentioned "id_from_core" field as "Update Key" property in "PutDatabaseRecord".

Thar11027_0-1718272973115.png

Here is the "Customer" table,

CREATE TABLE `Customer` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`created_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`created_date` datetime(0) NULL DEFAULT NULL,
`record_status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`surname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`id_from_core` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 175 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

My problem is,  update action is not work properly, while inserting same  values in "id_from_core" field data, it should be a update, but it's not updated. Insert as a new record. Could you please anyone know the reason for that matter and what is the solution for that.

Thanks in advance.



1 ACCEPTED SOLUTION

avatar
hide-solution

This problem has been solved!

Want to get a detailed solution you have to login/registered on the community

Register/Login
3 REPLIES 3

avatar

Hi @Thar11027 ,

I dont think there is an UPSERT statement in MySQL if Im not wrong. I think its treating it as regular insert and hence you are seeing duplicate entries. If you want to use PUTDatabaseRecord processor then you have to create two: one for insert and another for update and to decide which one you need to run you have to do Lookup to see if the customer with the same core id exists or not. For that you can use Lookup Record  (refer to : https://community.cloudera.com/t5/Community-Articles/Data-flow-enrichment-with-NiFi-part-1-LookupRec... )processor  to enrich your data with the customer core Id if exists, then you check if the record is found (meaning id exist) you route to Update otherwise you route to Insert.

 

Hope that helps. If it does please accept solution.

Thanks

avatar
Explorer

Thanks @SAMSAL for your quick response.

But I have another "PutDatabaseRecord" in work flow referring database table called "Transaction". It includes Primary key called "transaction_id" without auto incrementing   and "transaction_id" is the value of "Update key" in "PutDatabaseRecord".
In here, update and insert process are working properly. What is the reason for difference.   

avatar
hide-solution

This problem has been solved!

Want to get a detailed solution you have to login/registered on the community

Register/Login