flink1.20 连接 postgres

原创已于 2024-11-01 13:39:15 修改 · 792 阅读

4 ·

CC 4.0 BY-SA版权

我们不生产知识，我们只是互联网的搬运工

文章标签：

#flink #postgresql #CATALOG

于 2024-10-25 16:56:57 首次发布

在Flink中，你可以使用Catalog来管理数据库和表的元数据，从而避免在每次创建表时都写出所有字段。Flink支持多种Catalog类型，包括JDBC Catalog。

CREATE CATALOG mypg WITH(
    'type' = 'jdbc',
    'default-database' = 'postgres',
    'username' = 'postgres',
    'password' = '',
    'base-url' = 'jdbc:postgresql://10.50.108.42:5432'
);

create table mytable (
WATERMARK FOR eventtime AS eventtime - INTERVAL '5' SECOND
)
LIKE `mypg`.`postgres`.`public.source`;

CREATE TABLE sink_pg
LIKE `mypg`.`postgres`.`public.sink`;;


-- 改成全部小写
insert into sink_pg
select
    '' as username,
    ceil(eventtime to hour) as eventtime,
    address as ip,
    '' as baseline,
    false
from mytable
where eventtime >= '2024-10-16'
and eventtime < '2024-10-27'
and address <> ''
limit 100;

踩坑

flink 水印字段要求 timestamp(0-3)

postgres字段类型 timestamp 默认 timestamp(6)，需要转换后才能作为水印字段

ALTER TABLE public.mytable
ALTER COLUMN eventtime TYPE TIMESTAMP(3);

postgresql 大小写不敏感

pg 中查询大写字段需要加双引号，但是flink sql 不支持引号，所以用flinksql查询pg大写字段会报错，见参考链接。

Caused by: java.lang.IllegalArgumentException: open() failed.ERROR: column "eventtime" does not exist
  Hint: Perhaps you meant to reference the column "mytable.eventTime".

解决方案，把pg中字段改成全小写

参考

https://issues.apache.org/jira/browse/FLINK-23324
https://stackoverflow.com/questions/77383157/flink-postgres-jdbc-source-connector-read-uppercase-field-failed