[
https://issues.apache.org/jira/browse/NIFI-14869?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
adel wageeh updated NIFI-14869:
-------------------------------
Description:
*Problem:*
The {{PutDatabaseRecord}} processor in NiFi 2.2.0 does not support the UPSERT
statement type for MS SQL Server dialects (2008 or 2012+), resulting in a
validation error: "UPSERT not supported with Database Dialect". Although MS SQL
Server does not have a native UPSERT statement, equivalent functionality is
available via the MERGE statement. This limitation restricts dynamic workflows,
such as data masking integrations, where upsert operations are required without
custom scripts or workarounds.
*Solution:*
To address this limitation, new services were implemented:
{{MSSQLDatabaseDialectService}} and {{{}MSSQL2008DatabaseDialectService{}}},
both conforming to the {{DatabaseDialectService}} API. These services:
* Indicate support for UPSERT operations by including UPSERT in the set of
supported statement types.
* Generate MERGE statements for UPSERT operations, utilizing table and column
definitions as well as primary key information to ensure correct matching and
updating/inserting of records.
* Integrate with NiFi processors, such as {{{}PutDatabaseRecord{}}}, enabling
direct upsert operations for MS SQL Server without the need for custom scripts.
* Align with NiFi’s architectural direction by replacing the legacy
{{DatabaseAdapter}} approach with the new {{DatabaseDialectService}} API,
providing a more maintainable and extensible solution for database dialect
support.
was:
Problem:
The PutDatabaseRecord processor in NiFi 2.2.0 does not support the UPSERT
statement type for MS SQL Server dialects (2008 or 2012+), resulting in a
validation error: "UPSERT not supported with Database Dialect". MS SQL Server
lacks native UPSERT but supports equivalent functionality via the MERGE
statement (available since 2008).
This limits dynamic workflows, such as data masking integrations, where upsert
operations are needed without custom scripts or workarounds.
Proposed Solution
Extend the MSSQLDatabaseAdapter to:
- Return true for supportsUpsert().
- Implement getUpsertStatement() to generate a MERGE query using the Update
Keys property for matching.
Example generated MERGE statement:
sql
MERGE INTO table_name AS target
USING (VALUES (?, ?, ?)) AS source (field1, field2, field3)
ON target.key = source.key
WHEN MATCHED THEN
UPDATE SET field2 = source.field2, field3 = source.field3
WHEN NOT MATCHED THEN
INSERT (field1, field2, field3)
VALUES (source.field1, source.field2, source.field3);
> Add UPSERT support for MS SQL Server using MERGE in PutDatabaseRecord
> ---------------------------------------------------------------------
>
> Key: NIFI-14869
> URL: https://issues.apache.org/jira/browse/NIFI-14869
> Project: Apache NiFi
> Issue Type: Improvement
> Components: Extensions
> Affects Versions: 2.2.0
> Environment: Windows 11, Java 21.0.5, NiFi 2.2.0, MS SQL Server 2012+
> Reporter: adel wageeh
> Priority: Major
> Labels: MSSql, putdatabaserecord, upsert
> Original Estimate: 168h
> Remaining Estimate: 168h
>
> *Problem:*
> The {{PutDatabaseRecord}} processor in NiFi 2.2.0 does not support the UPSERT
> statement type for MS SQL Server dialects (2008 or 2012+), resulting in a
> validation error: "UPSERT not supported with Database Dialect". Although MS
> SQL Server does not have a native UPSERT statement, equivalent functionality
> is available via the MERGE statement. This limitation restricts dynamic
> workflows, such as data masking integrations, where upsert operations are
> required without custom scripts or workarounds.
> *Solution:*
> To address this limitation, new services were implemented:
> {{MSSQLDatabaseDialectService}} and {{{}MSSQL2008DatabaseDialectService{}}},
> both conforming to the {{DatabaseDialectService}} API. These services:
> * Indicate support for UPSERT operations by including UPSERT in the set of
> supported statement types.
> * Generate MERGE statements for UPSERT operations, utilizing table and
> column definitions as well as primary key information to ensure correct
> matching and updating/inserting of records.
> * Integrate with NiFi processors, such as {{{}PutDatabaseRecord{}}},
> enabling direct upsert operations for MS SQL Server without the need for
> custom scripts.
> * Align with NiFi’s architectural direction by replacing the legacy
> {{DatabaseAdapter}} approach with the new {{DatabaseDialectService}} API,
> providing a more maintainable and extensible solution for database dialect
> support.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
