You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@flink.apache.org by Aaron Weiss <aa...@broadcom.com> on 2022/06/09 14:40:45 UTC
Flink SQL JDBC connector for Postgres can't handle UUID datatype
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
<https://nightlies.apache.org/flink/flink-docs-master/docs/connectors/table/jdbc/>.
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 <https://issues.apache.org/jira/browse/FLINK-19869> 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.weiss@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.weiss@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.
Re: Flink SQL JDBC connector for Postgres can't handle UUID datatype
Posted by Martijn Visser <ma...@apache.org>.
Hi Aaron,
There's currently no support in Flink indeed to insert an UUID data type
into Postgres. The Jira ticket you've included [1] is indeed the same
issue. It's just that the solution is most likely not to map it as a RAW
type, but use a STRING type instead. Is this something where you might want
to help out with a contribution?
Best regards,
Martijn
[1] https://issues.apache.org/jira/browse/FLINK-19869
Op vr 10 jun. 2022 om 08:37 schreef Xuyang <xy...@163.com>:
> 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" <aa...@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
> <https://nightlies.apache.org/flink/flink-docs-master/docs/connectors/table/jdbc/>.
> 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 <https://issues.apache.org/jira/browse/FLINK-19869> 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.weiss@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.weiss@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.
>
>
Re:Flink SQL JDBC connector for Postgres can't handle UUID datatype
Posted by Xuyang <xy...@163.com>.
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" <aa...@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.weiss@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.weiss@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.