kafka-connect-jdbc icon indicating copy to clipboard operation
kafka-connect-jdbc copied to clipboard

Add upsert mode for the Vertica dialect

Open sauliusvl opened this issue 7 years ago • 2 comments
trafficstars

When in upsert mode generate MERGE statements for the Vertica dialect of the form

MERGE INTO "table" AS target USING (
  SELECT ?::INT AS "id", ?::VARCHAR AS "name"
) AS incoming 
ON (target."id"=incoming."id")
WHEN MATCHED THEN UPDATE SET "name"=incoming."name","id"=incoming."id"
WHEN NOT MATCHED THEN INSERT ("name","id")
VALUES (incoming."name",incoming."id")

Unfortunately omitting the types from the SELECT statement and having non-varchar values makes Vertica error out with

com.vertica.support.exceptions.ErrorException: 
[Vertica][VJDBC](3376) ERROR: Failed to find conversion function from unknown to int

thus I had to somehow get the column type information passed to getUpsertQuery() - I'm open to suggestions as to how to do it differently (should I maybe rebase this on top of #333? or wait until it's merged?).

Casting to un-sized types since the destination table might be pre-created with differently sized types compared to the default ones, thus casting to explicit sizes might truncate values.

Note that the WHEN MATCHED branch includes key columns as advised by the Vertica documentation.

sauliusvl avatar May 14 '18 19:05 sauliusvl

@sauliusvl, thanks for submitting. Do you want to rebase this on top of the changes from #333 that were merged a few months back?

rhauch avatar Nov 09 '18 22:11 rhauch

CLA assistant check
Thank you for your submission! We really appreciate it. Like many open source projects, we ask that you sign our Contributor License Agreement before we can accept your contribution.
You have signed the CLA already but the status is still pending? Let us recheck it.

cla-assistant[bot] avatar Aug 01 '23 23:08 cla-assistant[bot]