Real-Time Streaming Data Enrichment with Database CDC | 2/5

Key Takeaways

you might have struggled in the past between keeping the reference data up to date and enriching data streams at speed at the same time mainly because it's hard to enrich the data at Speed without keeping the reference data into the cash and as the data cached you have stale values on the other side if you decide to go directly to the database for the lookup every time it's too slow for realtime analytics welcome to part two of the series where we will be building realtime gaming leaderboard application using APF link over the course of five episodes you will learn all parts of streaming architecture starting from ingestion enrichment and all the way to the visualization you will also learn Advanced analytics techniques like control channel for AB testing handling late arrival of data exactly once archival and On Demand replay of the data in last video I provided you with the overview of a streaming architecture are building and completed the inje portion if you haven't seen part one I highly recommend you to watch it to get yourself familiar with the use case streaming Concepts and overall architecture we are building in this video I will show you how you can enrich a data stream at speed using my SQL database chain data capture we'll also talk about the data freshness you can find the GitHub repo containing everything you need in the description below let's begin so let's understand what is CDC and how we can use this CDC method to update the state which is cached within the Flink so imagine you have a table in the database with two Fields one is the ID and second is the subscription type with the player one and subscription type is free this is called initial setup and as as you have this initial setup CDC connector is going to bring this data into the Flink state in terms of the event and event will be insert in this case now imagine at sometime T2 in future there's a new row which is added and player to join the goal membership you again get the insert event propagated towards the Flink next one player One upgrades the subscription type from fre to 7day trial and that generates a update event this time instead of the insert one finally player two data get deleted and that generates the delete events what is the purpose of generating these events which is in sync with the database these events help us to update the Flink State and hold the most latest up toate value which is exact reflection of what you have in the database how we will use this reflected data of a Flink State into the joining process so imagine now you have the state of a player one with the free membership at time T1 and you receive the event for exit same player which is PED 280 so in this case the joined and reach data will produce player one subscription type free and speed is 280 but when the same data comes in future with let's say time T3 player one has now speed 300 because Flink has the most upto-date data received already from the database and in the cache Flink will say okay again I need to generate the data for player one but this time with subscription 7-Day trial because that's updated and the speed will be 300 what we have built so far is already ingestion portion of the architecture in this case We'll add two more components one is the Lambda function which is again the data generator continuously generating these update delete and insert events into the database and then we connect this database into the Flink now let me give you an overview of the code changes and guide you through the how to deploy cdk changes in your setup so we already finished the injection portion as you see here in this case you start with the branch number two which is CDC enrichment setup what it does for us is creates a network which is basically a VPC with two subnet types first one is the public which allows two directional internet second one is private which allows AWS resources to go out and contact the internet but doesn't allow the reverse traffic to any internet resources to come back and contact AWS resources next it sets up serverless database where you have two options first one is to use the Aurora my SQL if you wish to for the demos we are going with the ec2 based MySQL setup which is essentially mix of a secret manager which holds the credential for the database and the instance which is running the mySQL database for you third one data generator for player which MX the changes into the database in terms of the insert update and delete events let's go ahead now and deploy this so again going back to the terminal I in the branch two and in the infra folder already I'll say cdk deploy when prompted for the changes again you say yes to confirm let's wait for the changes to be deployed if you have any questions about anything in this architecture please leave comment and I will answer it so let me show you where is the code for building the WEA CDC connector so it's in the jar folder you'll already see one jar pre-compiled for you it's a pom.xml which builds it the pom.xml is like the Java way of building the fat jar with all the dependencies together what you will see this pom.xml is I have included two libraries first one is vevera connector for MySQL second one is the MySQL connector Java and then you have a Shar plug in which brings all the dependencies together and builds the fat jar just like this using the cdk that we have it already ships this fat jar to AWS and reference that into a notebook that's how we are using this jar and therefore we are able to use the mascu CDC as a connector type for the tables we already discussed about how we will use we a CDC connector for apach link to connect and stream CDC changes from MySQL now let's understand how we build this connector and use it in Notebook as you see you will have two new outputs appeared in the stack in cloud formation console first one with mySQL credential link and second is the host name of the mySQL database let's go to the K's analytic service and the studio notebook book you will see the new studio notebook appearing here which is quite similar to what you spinned up in the last part now let's hit run to start this notebook and then hit open up a zeppelin to load the Zeppelin you can hit import select the challenges notebook from the same notebook folder the GitHub Rao click open it will load up the new challenges notebook for you let's open this what you already have done is the events table if you haven't created it just make sure that you change the region and run the run it again you can run it a number of times because it always drops the table and recreates if it already exist in my case I am in us east1 so I'm doing that again now going to the players table as you see it uses the myql CDC connector now take the take the host name and the password from the cloud formation you can see the player table schema already here it contains variety of fields like operation Tim stamp player ID elas and other thing operation Tim stamp is the the Tim stamp of a given operation like insert update or delete and the rest of the schema is here now as you see the table is created you will use the player ID column to make a join across the two tables in the event table you also see the event time and event time TZ the mainly difference is event time TZ is time zoned one so the challenge for you here is can you select the data from the player table you can use these two commands to describe the schema the next challenge can you select from player table and plot a pie chart showing player distribution by country next challenge 2. to can you join the player and the event table based on the system time use the event time zone you can refer this link which gives you more information about how the temporal join Works in case of Apache Flink in this video we learn how to keep your data fresh through streaming enrichment in real time if you are enjoying this series please like this video and consider subscribing in the next video I will show you how how to process data calculate leaderboard and create easy to understand visuals stay tuned