Unfortunately, it seems that flink currently doesn't support the type UUID for
jdbc conector. I think you can create a new issue on jira and restart the
dicussion about this.
--
Best！
Xuyang
At 2022-06-09 22:40:45, "Aaron Weiss" <aaron.we...@broadcom.com> wrote:
Hey there,
We have just started to use the Flink SQL JDBC connector to do some writes to
Postgres. Our Postgres table has a UUID column we need to write to through
Flink SQL. However, it doesn't appear that the connector supports that type in
any way.
UUID isn't listed in the Postgres datatypes in the Data Type Mapping section
here. We attempted to use VARCHAR and STRING types in Flink SQL for this, but
get errors doing SELECT or INSERT on the UUID column.
We saw this open issue that we think is similar to what we're experiencing.
Any help would be appreciated!
Error Message when Inserting:
flink-jobmanager Caused by: java.sql.BatchUpdateException: Batch entry 0
INSERT INTO users(email, tenant_uid)
VALUES ('aaron.we...@broadcom.com', '76170855-6678-05ed-f913-f581fdc1aa87')
was aborted:
ERROR: column "tenant_uid" is of type uuid but expression is of type character
varying
Flink SQL table DDL:
CREATE TABLE ica.postgresql.users (
email VARCHAR,
tenant_uid VARCHAR
) WITH (
'connector' = 'jdbc',
'url' = 'jdbc:postgresql://ica-postgresql:5432/ica',
'username' = 'XXXX',
'password' = 'XXXX',
'table-name' = 'users'
);
Postgres table DDL:
create table users
(
user_uid uuid default gen_random_uuid() not null,
email text not null,
created_date timestamp_ica default get_now_ica(),
modified_date timestamp_ica default get_now_ica(),
last_detected_date timestamp_ica default get_now_ica(),
tenant_uid uuid not null,
hits bigint default 0 not null,
first_name text,
last_name text,
account_name text,
domain text,
title text,
threat_score integer,
org_unit text,
ldap_dn text,
ldap_memberof text[],
ldap_hierarchical_memberof text[],
source_user_id text,
constraint pk_users
primary key (tenant_uid, user_uid),
constraint uk_users
unique (tenant_uid, email)
)
--
Aaron Weiss
R&D Software Engineer | Information Security Group | Symantec Enterprise
Division
Broadcom
mobile: 404-550-4299
Atlanta, GA (USA)
aaron.we...@broadcom.com | broadcom.com
This electronic communication and the information and any files transmitted
with it, or attached to it, are confidential and are intended solely for the
use of the individual or entity to whom it is addressed and may contain
information that is confidential, legally privileged, protected by privacy
laws, or otherwise restricted from disclosure to anyone else. If you are not
the intended recipient or the person responsible for delivering the e-mail to
the intended recipient, you are hereby notified that any use, copying,
distributing, dissemination, forwarding, printing, or copying of this e-mail is
strictly prohibited. If you received this e-mail in error, please return the
e-mail to the sender, delete it from your computer, and destroy any printed
copy of it.
