[
https://issues.apache.org/jira/browse/NIFI-5151?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16571712#comment-16571712
]
Peter Wicks commented on NIFI-5151:
-----------------------------------
Most databases that support UPSERT don't use the UPSERT keyword. The Wikipedia
article shows all of the various ways it's been implemented:
[https://en.wikipedia.org/wiki/Merge_(SQL).]
A few examples:
* MySQL uses `INSERT ... ON DUPLICATE KEY UPDATE`
* SQLite uses `INSERT OR REPLACE INTO`
* IBM DB2 and Teradata have a `WHEN MATCHED` / `WHEN NOT MATCHED`
* Firebird uses `MERGE INTO`
So when I say it would be "database specific", I mean it will work with the one
or two databases that chose to implement UPSERT logic using that very specific
syntax, but won't work with any of the other various ways it's been implemented
over the years in other databases. This is exactly what the DatabaseAdapter's
were built for, database vendor specific logic.
> Patch Nifi with Upsert functions for PutDatabaseRecord processor
> ----------------------------------------------------------------
>
> Key: NIFI-5151
> URL: https://issues.apache.org/jira/browse/NIFI-5151
> Project: Apache NiFi
> Issue Type: Improvement
> Components: Extensions
> Affects Versions: 1.7.0
> Reporter: Karl Amundsson
> Priority: Major
> Labels: Processor
> Attachments:
> 0001-NIFI-5151-Adding-support-for-UPSERT-in-PutDatabaseRe.patch
>
> Original Estimate: 0h
> Remaining Estimate: 0h
>
> Since Phoenix doesn't support the SQL statement INSERT you have to use a
> process like: ConvertAttributesToJSON->ConvertJSONToSQL in Insert
> mode->ReplaceText to replace "INSERT" with "UPSERT" -> PutSQL (See:
> [https://community.hortonworks.com/questions/40561/nifi-phoenix-processor.html)]
> With this patch you can choose to use UPSERT directly.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
