Python Flink Streaming Table 使用RMDB

最近准备将一些Python的数学计算框架使用到flink中，困难重重，很多分类数据等信息存放在关系型数据库中。目前没有发现pyflink提供RMDB的connector，便做了如下尝试，发现还是可行的。 其中使用postgresql数据库存储了数据信息的中文名称和各种分类信息，具体的pyflink环境准备请参考：

https://enjoyment.cool/2019/07/25/Apache%20Flink%20说道系列-%20Python%20Table%20API%20开发环境搭建/

Flink v1.9

python v3.7.5

import os

from pyflink.datastream import StreamExecutionEnvironment, TimeCharacteristic
from pyflink.table import StreamTableEnvironment, CsvTableSink, DataTypes
from pyflink.table.descriptors import Schema, Rowtime, Json, Kafka
from pyflink.table.window import Tumble
import psycopg2

podatabase = "postgres"
pouser = "postgres"
popassword = "postgres"
pohost = "ssdlinux2"
poport = "5432"

if __name__ == '__main__':
    s_env = StreamExecutionEnvironment.get_execution_environment()
    s_env.set_parallelism(1)

    s_env.set_stream_time_characteristic(TimeCharacteristic.EventTime)
    st_env = StreamTableEnvironment.create(s_env)
    result_file = "/Users/mac/flink/tumble_time_window_streaming.csv"
    if os.path.exists(result_file):
        os.remove(result_file)
    # postgres data load
    conn = psycopg2.connect(database=podatabase, user=pouser, password=popassword, host=pohost, port=poport)
    curs = conn.cursor()
    sql = "select distinct a.id, a.node_id,n.node, n.name, n.setvalue, n.unit from alarm_rules a,nodename n " \
          "where a.node_id=n.node "
    arrayData=[]
    try:
        curs.execute(sql)
        rows = curs.fetchall()
        arrayData=rows
    except:
        print("Error: unable to fecth data")
    conn.close()

    colnames=["id","node_id","node","chinesename","setvalue","unit"]
    postgrestable = st_env.from_elements(arrayData, colnames).select("node_id,chinesename")

    st_env \
        .connect(  # declare the external system to connect to
        Kafka()
            .version("0.11")
            .topic("test_topic")
            .start_from_latest()
            .property("zookeeper.connect", "ssdlinux1:2181")
            .property("bootstrap.servers", "ssdlinux1:9092")
    ) \
        .with_format(  # declare a format for this system
        Json()
            .fail_on_missing_field(True)
            # .derive_schema()
            .json_schema(
            "{"
            "  type: 'object',"
            "  properties: {"
            "    name: {"
            "      type: 'string'"
            "    },"
            "   value: {"
            "      type: 'number'"
            "    },"
            "    time: {"
            "      type: 'string'"
            "    }"
            "  }"
            "}"
        )
    ) \
        .with_schema(  # declare the schema of the table
        Schema()
            .field("name", DataTypes.STRING())
            .field("time", DataTypes.TIMESTAMP()).proctime()
            .field("value", DataTypes.DECIMAL(38,12,nullable=True))
    ) \
        .in_append_mode() \
        .register_table_source("source")

    st_env.register_table_sink("result",
                               CsvTableSink(["name", "avgvalue","minvalue","maxvalue","mintime","maxtime","chinesename"],
                                            [DataTypes.STRING(),
                                             DataTypes.DECIMAL(38,12,nullable=True),
                                             DataTypes.DECIMAL(38, 12, nullable=True),
                                             DataTypes.DECIMAL(38, 12, nullable=True),
                                             DataTypes.TIMESTAMP(),
                                             DataTypes.TIMESTAMP(),
                                             DataTypes.STRING()
                                             ],
                                            result_file))

    data=st_env.scan("source").join(postgrestable,"name=node_id").window(Tumble.over("10.seconds").on("time").alias("w")) \
        .group_by("w, name,chinesename") \
        .select("name, avg(value) as avg ,min(value) as min,max(value) as max,w.start as start,w.end as end,chinesename")\
        .insert_into("result")
        
    st_env.execute("KAFKA tumble time window streaming v")

程序能够正常执行，