Questions for reading data from JDBC source in DataStream Flink

user_16632618 0
Viewed 1046

I'm starting a new Flink application to allow my company to perform lots of reporting. We have an existing legacy system with most the data we need held in SQL Server databases. We will need to consume data from these databases initially before starting to consume more data from newly deployed Kafka streams.

I've spent a lot of time reading the Flink book and web pages but I have some simple questions and assumptions I hope you can help with so I can progress.

Firstly, I am wanting to use the DataStream API so we can both consume historic data and also realtime data. I do not think I want to use the DataSet API but I also don't see the point in using the SQL/Table apis as I would prefer to write my functions in Java classes. I need to maintain my own state and it seems DataStream keyed functions are the way to go.

Now I am trying to actually write code against our production databases, I need to be able to read in "streams" of data with SQL queries - there does not appear to be a JDBC source connector so I think I have to make the JDBC call myself and then possibly create a DataSource using env.fromElements(). Obviously this is a "bounded" data set, but how else am I meant to get historic data loaded in? In the future I want to include a Kafka stream as well which will only have a few weeks worth of data, so I imagine I will sometimes need to merge data from a SQL Server/Snowflake database with a live stream from a Kafka stream. What is the best practice for this as I don't see examples discussing this.

With retrieving data from a JDBC source, I have also seen some examples using a StreamingTableEnvironment - am I meant to use this somehow instead to query data from a JDBC connection into my DataStream functions etc? Again, I want to write my functions in Java not some Flink SQL. Is it best practice to use a StreamingTableEnvironment to query JDBC data if I'm only using the DataStream API?

flink-streaming apache-flink apache-kafka jdbc java
1 Answers

Following approaches can be used to read from the database and create a datastream :

  1. You can use RichParallelSourceFunction where you can do a custom query to your database and get the datastream from it. An SQL with JDBC driver can be fired in the extension of RichParallelSourceFunction class.

  2. Using Table DataStream API - It is possible to query a Database by creating a JDBC catalog and then transform it into a stream

  3. An alternative to this, a more expensive solution perhaps - You can use a Flink CDC connectors which provides source connectors for Apache Flink, ingesting changes from different databases using change data capture (CDC)

Then you can add Kafka as source and get a datastream.

So, briefly your your pipeline could look like follows : You have both the sources transformed as a datastreams, you can join these streams using, for example coprocess function which will also give you possibility to maintain a state and use it in your business logic. And finally Sink your final output to either a database, to Kafka or to even AWS S3 buckets using a Sink function.

user_85555410
Related