I have a following SQL script in Java application:
INSERT INTO web.ga_report (metric_value, account_login,metric_name,account_view_id)
VALUES (:metricValue,:accountLogin,:metricName,:accountViewId)
ON CONFLICT (account_login,metric_name,account_view_id)
DO UPDATE SET (metric_value) = (EXCLUDED.metric_value);
All values were generated dynamically by Spring JDBC NamedParameterJdbcTemplate.
Now I want to do same things with Apache NiFi.
My JSON looks like:
[ {
"metric_name" : "ga:sessions",
"metric_value" : "0",
"account_login" : "[email protected]",
"account_view_id" : "10453ads71"
}, {
"metric_name" : "ga:sessionDuration",
"metric_value" : "0.0",
"account_login" : "[email protected]",
"account_view_id" : "12781"
}]
I'm using PutDatabaseRecord processor with UPSERT statement. But i got the error:
ON CONFLICT DO UPDATE command cannot affect row a second time
HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.
So, UPSERT doesn't work. How to use my SQL script in Apache NiFi? I need to dynamically insert values (and solve conflicts) from JSON INTO VALUES like this:
INSERT INTO web.ga_report (metric_value, account_login,metric_name,account_view_id)
VALUES (0,[email protected],ga:sessions,10453ads71)
ON CONFLICT (account_login,metric_name,account_view_id)
DO UPDATE SET (metric_value) = (EXCLUDED.metric_value);
I thought about attributes and ReplaceText, but with attributes I'll have same attributes values for all JSON's in my "big" JSON.
So, how to apply SQL script above to Apache NiFi?
