提一个建议，create view as select也可以支持一下

[Lakers-repo]

No description provided.

[Lakers-repo]

CREATE TABLE if not exists ods_mysql_users_haxi (
id BIGINT NOT NULL,
name VARCHAR(2147483647),
birthday TIMESTAMP(3),
ts TIMESTAMP(3),
proc_time AS PROCTIME(),
CONSTRAINT PK_3386 PRIMARY KEY (id) NOT ENFORCED
) WITH (
'hostname' = '127.0.0.1',
'password' = 'xxx',
'connector' = 'mysql-cdc',
'port' = '3306',
'database-name' = 'demo',
'server-time-zone' = 'Asia/Shanghai',
'table-name' = 'users',
'username' = 'root'
);

CREATE table if not exists dwd_hudi_users_haxi (
id BIGINT NOT NULL,
name VARCHAR(2147483647),
company_name VARCHAR(2147483647),
birthday TIMESTAMP(3),
ts TIMESTAMP(3),
partition VARCHAR(20),
CONSTRAINT PK_3386 PRIMARY KEY (id) NOT ENFORCED
) PARTITIONED BY (partition)
WITH (
'read.streaming.enabled' = 'true',
'read.streaming.check-interval' = '1',
'path' = '/hudi/users',
'table.type' = 'COPY_ON_WRITE',
'connector' = 'hudi'
);

create view ods_mysql_users_haxi_dedup as
select
id,
name,
birthday,
ts,
proc_time
from
(
select
id,
name,
birthday,
ts,
proc_time,
row_number() over (partition by id order by proc_time desc) as rn
from ods_mysql_users_haxi
) tmp
where tmp.rn = 1;

INSERT INTO
dwd_hudi_users_haxi
SELECT
a.id as id1,
CONCAT(a.name,b.company_name),
b.company_name,
a.birthday,
a.ts,
DATE_FORMAT(a.birthday, 'yyyyMMdd') as p
FROM
ods_mysql_users_haxi_dedup AS a
JOIN
dim_mysql_company FOR SYSTEM_TIME AS OF a.proc_time AS b
ON a.id = b.user_id;

[Lakers-repo]