[jira] [Commented] (FLINK-24512) Allow metadata columns can also be part of primary key

["Jark Wu (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/FLINK-24512?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17427441#comment-17427441 ] 

Jark Wu commented on FLINK-24512:
---------------------------------

What do you think [~twalthr]?

> Allow metadata columns can also be part of primary key
> ------------------------------------------------------
>
>                 Key: FLINK-24512
>                 URL: https://issues.apache.org/jira/browse/FLINK-24512
>             Project: Flink
>          Issue Type: Bug
>          Components: Table SQL / API
>            Reporter: Jark Wu
>            Priority: Major
>
> Currently, primary key can only be defined on physical columns. However, we encountered some cases need to define primary key on metadata columns. 
> For example, there is MySQL sharding tables {{user_01}}, {{user_02}}, ..., {{user_99}} and they all use snowflake algorithm to generate global unique ID as the PK of each table. The user would like to read the sharding tables as one table and load into an OLAP with primary key of table name and ID. A Flink SQL can be following:
> {code}
> CREATE TABLE mysql_users (
>   table_name STRING NOT NULL METADATA,
>   id BIGINT NOT NULL,
>   user_name STRING,
>   address STRING,
>   PRIMARY KEY (table_name, id) NOT ENFORCED
> ) WITH (
>   'connector' = 'mysql-cdc',
>   'database-name' = 'mydb',
>   'table-name' = 'user_.*',
>   'username' = 'xxx',
>   'password' = 'yyy' 
> ); 
> {code}
> IMO,  (non-virtual) metadata columns is similar to physical columns, because it is also some kind **physical** data in database. And our primary key definition is "NOT ENFORCED", which means we simply trust the unique constraint by users. 



--
This message was sent by Atlassian Jira
(v8.3.4#803005)