ETL Pipelines in Google Cloud Platform

Key Takeaways

welcome everyone to another session in the data series we are thrilled to be here with you for an Interactive Learning session part of the data science team at analytics Vidya will be the moderator for today's session for those who have joined us for the first time a brief introduction about the data sessions simply saying data is nothing but one are dedicated to data with the intent to make data science learning more engaging to the community analytics without begin with its new initiative the data so it's a series of sessions conducted by us we are leading players in the data science domain teach and democratize data science knowledge and which will ultimately help us to enable deeper discussions and conversations with our community and we can understand and learn the concepts in a fun way so now on to our session today which is ETL pipeline in gcp so Google cloud data pipeline is a platform used to automate the transfer process and the transformation of data between source and the target Repository it enhances the speed of the data analysis process and hence a huge practical application so in this data are we will talk about Google cloud and building Big Data ATL pipelines in Google Cloud platform and additionally what are the big data services offered by Google Cloud so in this session Vignesh will also explain how data processing Frameworks uh work and Bot services in gcp are involved for building ETL Pipelines so before we kick things off and I hand it over to our speaker a quick recap of the housekeeping items so we are recording the session and will make the recording available in few days on our YouTube Channel please use the qme section for asking any questions you might have during the session and we will do our best to answer them as the data progresses toward the end also we will share a feedback call towards the end of session which I request you all to participate in so now on to our speaker in the session of data we have Vignesh second sujata with us Vignesh is currently uh working as cloud data engineer and data scientist with Deloitte he has a five plus here of it experience in designing and implementing various machine learning models ETL data pipelines data analysis statistical analysis development testing and ml models and data pipelines and has worked in the domains of healthcare detail and Airline with a vision to expand the data science Community he also helps pressure and experience candidates in the data science Journey with their resume and interview preparation including people preparing for cloud exam in GCB and Microsoft Azure related to data scientists and data engineering so I will be dropping a link in chat for the means through which you can connect with a Vignesh at LinkedIn so over to you Vignesh the virtual stage is all yours um thank you that was so sweet of you so yeah so we will see uh how to build an edl pipeline in in Google Cloud platform using big data services in gcp as well so I'll be sharing my screen yeah can I share my screen yes yes foreign [Music] yeah can you close the chat box I'm not I'm able to see the chat box can you help me like in closing the chat window song for me and I'm not able to view the screen properly okay okay yeah so is my screen yesterday so in today's session we are going to see what is ETL how to implement ETL and what is the use cases which we'll be using for this particular purpose and we'll be building ETL pipeline in Google Cloud platform using the services which is offered by Google Cloud so before starting a little about me so yeah I say select I'm currently working as Sun Cloud data engineer and data scientist and I'm targeting for architect role so I have completed uh like up I have completed some few certifications in Google Cloud as well as Microsoft Azure so my primary job is to build uh Big Data ETL pipelines and build ml models and do ml Ops kind of works in both Azure as well as TCP platform so yeah so the agenda for today session is first we will be seeing what is ETL followed by what is elt and we will understand what is the difference between ETL and elt and what are the products and services in gcp which we'll be using to build this ETL pipelines and finally we will be seeing the Practical implementation on how to construct an edl pipeline using gcp services in Google Cloud platform that will be more or less a real-time scenario so this will this is the agenda for today's session so the first three topics we take water CPL what is elt difference between so these are like uh it will be a kind of theoretical but we'll skip like we'll try to I'll try to complete it with first 30 minutes I'll try to complete it that first four products and for demo we will have half an hour so that uh we'll try to complete wrap it up by one hour and we'll have some few questions answer for 10 minutes and then we will find the session so this is the plan and this is the agenda for uh this particular session so first what is ETL so ETL stands for extract transform and Loan and it is a traditionally accepted way for organizations to combine data from multiple systems into single database data store and data virus if you see in this particular picture right like the first step is um we are getting data from multiple so if you see in the first step now we are getting data from multiple sources like from databases like rdbms databases oltp databases and even in few cases we'll be getting data from nosql databases as well so different databases different files like XML files Json files and and few applications like web applications where we will be getting those data saskatches so we have to collect all this data do some Transformations out of it unload it into a single database or data warehouse basically so ETL can be used to store Legacy data or assets more typical today aggregate data to analyze and drive business decisions so previously uh so before that ETL is in ID platform ETL is for uh it is there for more than two decades now so previously what they used to do is they used to store the Legacy data they used to collect the Legacy data Legacy data in the sense like the data which is to a particular application the particular a company or a particular organization or to a particular service you could say so that was previously what they were doing is they used to get the data um like five years data 10 years data two years data one year data they used to get that and they used to do some Transformations and they used to store it in two uh data warehouse and after that they will do some uh oil like did they will do some analyticals on top of collected data and they will get some business values out of it that was happening like two years back like few years back but nowadays what they wanted is uh even at that time you know when doing the transformation itself we wanted to see the aggregated data basically how many people what is the value how many products sold so all those aggregated values so what is the maximum group of people engaged in that particular poll so who who are they from like where are they from which country like what is the experience so like it is very similar to whatever the poll which we just filled it like few minutes back now so it is very similar to that poll itself like nowadays CDL pipeline comes with that feature as well like we ordered to do the aggregation of aggregation level at the data and then we have to get that uh we have to get the results out of it basically all the results everything now will help us in doing the business uh will help us to get the meaningful interest out of that particular data okay foreign Solutions must scope like organizations have been using ETL for decades but nowadays since the cloud platform all the business all the business organization like different organizations everyone are moving towards the cloud platform because of various reasons because of cost because of the services they have to monitor and maintain and all the extra costs for um for maintaining those servers home because of various other reasons as well like all the organizations now they are moving it into uh cloud like public Cloud platforms like AWS Azure and gcp so today's model etn Solutions must cope up with accelerating volume and speed of the data as you know now like as you know um there is a lot of data available everywhere like more all the applications like even our mobile phones everything like is generates uh terabytes or extra byte of data across the globe in a deep so the volume of that data it is getting uh then data is like it's two it's like the volume is in great height and the speed we need to access that rate uh it also be um it also to be very speed and very like the processing's power all those things so according to the volume generation we have to increase that speed also and like that feature that capacity is just provided in any Cloud platforms like particularly in gcp so those things uh how we will see how to construct an ETL pipeline what are the service what are the features of that service all those things we will be seeing it and the ability to ingest enrich unmanage transactions and support both structured and unstructured data in real time from any Source whether on-premise or in the cloud so irrespective of the data where it is deciding if it is residing a non-premise or even if it is reciting any Cloud platform like Cloud platform services like cloud storage or Google big cloud bigquery so bigquery is a data warehouse solution in gcp so where wherever the data is this person from cloud storage or from on-premise or from any particular data center or from different Cloud we have to get the data we have to do like ingest enrich and manage transactions transactions in the sense basically any Transformations all those things we have to do at that volume of the speed like volume of the data which we get and and also we will be getting the um like the processing power the computation power of that particular service whatever we are going to use it so everything will happen in real time near real time batch processing or dock processing all those things we will be able to do it using ETL pipeline in gcp Cloud platform so as I said earlier so we are we'll be getting data from different uh sources like databases files web applications web application in the sense like whatever you feel like like that drives and web application like survey result all those things it basically comes from that web applications so all those results we will be taking it we will be doing some Transformations this transformation is a kind of Transformations is this is a kind of um kind of uh like for example Transformations like data clinics in like removing unwanted characters removing null values removing special characters or there will be any uh null values also because of various reasons and there will be some duplicate values all those things like there is a different Transformations available so or else not not only transformation like as I said earlier aggregations as well like we have to do aggregation at the a particular column or um like we wanted to see what is the max group of people involved so all those things we will be doing the transformation and finally we will be loading this into Data barrows and we will be analyzing using any data analytics platform or any visualization tools like power bi tab blue or blue curator so the three operations happening in ETL is extract transform unload we are extracting data from the open sources like databases files and we are making transformation this includes our business logic and finally we are loading it into Data Warehouse like Google cloud bigquery or any Data Solutions and we'll be using that analytics platform so to give you a real-time example uh basically we'll talk about this example itself like before they saw before the session begins you are asked to create one poll right like there was one poll and you were asked to fill that role right basically what analytics we generation analytics which I will do is they will collect this data like for 10 uh one year or let's say for two years they will collect the data and they will collect the data that data is in the form of web application that is in zooms so they will collect the data and after one year or two year they will do some Transformations on top of it so how many sessions totally happen like how many sessions were in ETL how many sessions were in machine learning how many sessions were in uh different other data Sciences data science branches so they will do uh how what is the duration whether how many participants participated in particular location uh how many years the experience all those things they will come they will aggregate those data and they will see it in the visualization like they will store it in any data warehouse and finally they will use any visualization tool to understand it to present it to the business to get in whatever the insights which they got no they will present it and they accordingly they will make some business decisions so we based on that like we can say like we'll conduct that particular session only in weekdays because weekdays is where most of the people are attending or in week then weekend weekends so this is the location so all those details everything is uh uh like every all the data will will help them to improve their Vision improve their business decisions improve improve their business uh points and business values so that is one example there are a lot of examples we can give like there are a lot of examples we we can we can associate with this ETL pipeline so the first is extraction extraction is the process of retrieving data from one or more resources that is online on premises Legacy applications or SAS or others after retrieval or extract action is complete the data is loaded into the staging area basically staging areas where the transformation will be done so what the transformation look like is it involves taking that data so whatever the data which we collected from that extraction it will take that data and it will do some Transformations out of it like cleaning it putting into a common format like defining the schema or defining the data type so whatever we wanted to do the transformation we'll do it and we will be storing it to any particular data database or data store or data warehouse as well so cleaning cleaning thing is typically involves taking out duplicates incomplete or obviously errorless records so duplicate records or some records may be incomplete or some records no by seeing itself without any business hours by seeing itself we can see that it is an error data so those type of data or we will be able to clean it or we will be able to transform it as part of this transformation process and finally we'll be loading so whatever the data which we formatted which we transform we will be loading it to any data warehouse solution so data viral solution is is a particular Concept in data engineering where we will be where the data the clean the data the form the data it will be stored in data warehouse the reason is it will it isn't readily available data we business or even for building ml models we will take us we will take the data to build our model to train our model so the data will be cleaned it will be in the um ready to use stage so that is what we will be loading it to data virus and we will be using it for different use cases for building ml models for connecting it to power bi Tableau or any visualization tools to see the um to see that to get the insights out of it so what is what does what is the data is telling us so there is one particular problem like not all like um not every data will be very clean but all all the data will have some meaningful or some useful information out of it so uh even the data is the raws the raw structure of any data will not help us but all it will always uh gets get us some meaningful Insight output we'll be able to see some value out of the data so what is the advantage of vtl so the maturity as I said earlier like it is ETL is in the industry or in the ah it is in the industry for more than two decades so there are many tools many tools out there to support it and there are many readily available Tools in cloud-based Solutions even there are tools just like drag and drop you have to just drag and drop and you have to establish the connection just everything will be taken care by the respective two literature so there are many uh available tools on the route mostly four types of ETL tools are available we will be seeing that also um so the maturity is there and the next is complaint so the data as I told you earlier the data is in the form of clean and the transformed state so it is readily available we can use the data and we can use it for any purpose like for training our model uh for business insights getting data out of it like uh meaning meaningful Insight out of that particular data it will have some certain uh regulations also like gdpre like gdpr for example for healthcare we'll be using fire database for uh there are other for retail there will be certain set of data so those things it will be readily available and there will be predefined schema the data type everything will be predefined we have to just take the data we have to query it or we have to um get basically we have to get the Insight out of data so but the one solution like one problem is the data will not be updated it will be like one year or three months four months past it only we'll be able to get that past data the data will be not readily like up to date data will not be ready available because of the transformation the Logics business logic everything no considering those aspects the data will not be up to date but it will be readily available for other use cases like for building models all those things the disadvantages of ETL is we have to maintain it because the transformation all those things right it is according to our business requirements right so we that there will be a frequent maintenance of that particular ETL tool or the code which you have written there will be a often maintenance of it and and we have to invest a lot the cost for this it is high because of the transformation and the time taking taken for the trade also it is uh it is equally High also because if the data volume is too huge if we are dealing about a terabyte of data then the computation power involved for the transformation cleaning all those things it will be high and also the scaling and the time taken for the Tran time taken also it will be high so there is a lot of cost involved in building an ETL pipeline so when we talk about edl pipeline uh so when we talk about edl pipeline uh we have to talk about the other service that is elt like extract load and transform so this is a very recent approach uh this has been introduced very recently only like five six years back and this is the fast forward like all the um all the organizations now they are going towards this approximately elt because the main reason why we are going with elt is we don't need to transform like not all the use cases whatever we are dealing in the real time not all the all the use cases all the business requires to transform the data while leading loading into a data warehouse whatever the data which we get the raw data so when I say raw data is nothing but um ah the data which has some uh some uh information in it but we have to do some additional steps additional processors to get that meaningful insights out of the data for example uh yeah so uh we consider this case as well in miles or in a big big shopping malls I don't know we used to fill forms right we used to fill forms like what our feedback all those things so all those feedbacks will be in the form of unstructured data like if you will be in the form of um few will be in the form of images fuel being the form of applications like mobiles mobile data mobile applications and few will be in the form of uh like just clicking button or green red all those things so those things are unstructured data we have to do like in order to get some meaningful Insight out of it we have to do some Transformations or else we'll be not able to get what is what the data tells will not know those things so that is the raw data raw data is something which has some meaningful Insight out of it to get that Insight out of that data we have to do some additional steps that is what raw data is so ELD is an acromion for extract load and transform and it also described the three stages only like whatever we on exact transform and load we will be seeing the same in extract load and transform the first step of extra ETL elt is it will extract the data so it will extract the data from databases files and web applications and instead of doing the transformation um we'll instead of doing the transformation we will be directly writing it to a data lake so data lake is again um again a concept in data engineering only like in data warehouse we have a structural we have a syntax we have a predefined schema like this particular column will will follow only this data type and like similarly we can see like it will be easily um it will be the form of rows and columns like in the table format whereas in elt right yeah whereas in elt we don't have that it will be in the data Lake data Lake data warehouse where it will not form any it will not uh do any follow any predefined scheme or predefined a data type all the data we can add it and whatever the data which we wanted we can get we can query that particular data we can do the Transformations and we can get the meaningful Insight out of that particular data alone so that is what elt is in real time not all the data needs to do the transformation only a part of the data portion of the data we need to do transformation that is what we can do elt as well and ELD supports both unstructured data structured data and semi-structured data whereas etls for now ETL support structure and semi structured data it can also go with unstructured data but the computation and the Machine configuration everything will be high and it will be huge because if you are getting terabyte of data no it is not necessary we need to do the transformation for the terabyte of data we can take a portion of the data for example let's say 1GB 2GB of data we can do some transformation we can do some POC we can understand okay this data is very useful for our business decision so based on that also we can build our ETL pipeline based on that so for all those things we'll be using ELD basically ELD supports structure semi-structure and unstructured data as well so ETL it is for small datas only like small data I said only like GPS on but elt is for huge data like terabytes of data or even petabyte or extra byte of data as well it supports larger structure and unstructured data set as well and the timelines is important like timelines it doesn't snow I I told you earlier in ETL or in the data virus we will not have up-to-date data we will have data which is like three months four months or one year two year old data whereas in elt or in data like no it will have up-to-date data yesterday loaded it also we can get we can query it from data Lake so that is how elt is in this process in this process data gets leveraged via data warehouse in order to do a basic transformation that means there's no need for data staging as I told you earlier there is a the data staging step skipped in ELD we don't need to do we don't need to do any particular Transformations or any business logic we have to implement at that data staging area now that step is skipped will be directly loading it into Data lake or data warehouse and to a portion of the data portion of the data or only to few set of the data we can do transformation so we can do transformation to that entire data and we can analyze it using uh using data analytics or using uh visualization tools as well so elt uses all different types of data including structured unstructured semi-structured and even raw data types as well like the raw data adapts and this the images which we collect ammo we can get all those data we can do Transformations on top of that as well so acetone data transform is still necessary before analyzing the data without business intelligent platform however data clean engine and transformation occur after loading the data into a data lake so at this point I have already covered so we don't need to do the transformation or we want don't need to implement the business logic uh to the data itself so that uh full volume of the data we can directly load it into Data lake or data warehouse as I told you already we can take some GP of data we can do some transformation we can present it to the business once they are happy or once they are satisfied with okay that whatever we are about to investment invest rate it makes sense then we can build an ETL pipeline or we can Implement that logic inside our pipeline as well so it also includes three steps extract load and transform so the first thing is it will extract and it will directly load into Data Warehouse or data Lake after that we will be doing some Transformations on few set of data it is not necessarily required that should be only a few volume of the data you if your business is okay if your business is ready to go with approach right you can do it for the entire volume of the data as well so for that the computation the scaling all those things no we have to uh pick the accordingly Services we have to pick necessary service according to our requirement so the same thing extraction or extracting data from databases files web applications loading it into um the extracted data the raw data it will be directly loaded into Data Lake and Transformations will be like data transition like data cleaning or uh like duplicates all those things we'll be doing in the Raw data on the transformation level so the advantage of PLT is as you know like ETL is like something uh where we it takes a lot of time it takes a lot of time and it takes lot of computation and scaling whereas there is no problem in ELD will directly load the data so the time will be the speed and time will be very minimal and the speed will be very high so ELD allows for all the data to go into the system immediately and from there users can determine the exact data they need to both transform and analyze so at that point I have already covered and the next is flexibility so in ETL right we have to define a schema we have to Define data type for that particular data all those things whereas in elt we don't need to worry about all those things like if it is float if it is string if it is integer if it is the um real so whatever the data type is we are just going to append it to any particular in the data lake so we don't need to worry about the schema or data type control we have just that flexibility is this available in data Lake which for which comes under Eld and the lower cost so as I told you I don't know like the we are we are not doing any transformation right we are just appending the data to data Lake we are just writing it to the data lake so there is no uh scaling or there is no computations of systems available there is all those schemes or will be removing from that particular use cases so the lower cost and it is flexible and it is it comes it is basically in high speed the disadvantages like there will be some security gaps security gaps in the sense like not third party will come and access your data it's not like something like that data will be secure data will be stored somewhere very safe only you don't need to worry about that that security that security Gap is nothing but um the moment you add all the data to your data liquid like other teams also will be able to view our data if your data is supposed to be confidential but since it is in data like people who have access to data Lake right they can easily view our data but that is not the case it will happen in a r dbms or any cloud SQL databases the next is increased latency so as the volume of the data increases when you query or when you do the transformation from this particular point there will be fuel there you can see according to that the volume of the data you can see the latency involved there so the latency will be basically it will be increased and the next thing is we will be seeing what is the difference between ETL and elt so whatever I we have seen till now now we are just going to see the different set like difference between ETL and elt the first thing is process so data is transformed at the staging server and then transfer to Data Warehouse so when the process itself when we are transforming it you must be as you know like there will be computation of any particular service like if you are using compute engine or if you are compute engine is nothing but what is the storage of what is the ram of that particular system what is the storage or what is the computational power like how many CPUs involved Master note worker node all those things now we will be seeing that in this process like data's transformed like at the staging server and the scalability of that application uh scalability like Mode work Master node worker node so all those things it is involved in ETL whereas we can skip all those steps in ELD we don't need to worry about any Transformations it will their data will be directly returned into this data warehouse or data Lake mostly in ELD will be data Lake in ETL it will be data warehouse or database Source data support storing structured data from input sources structure or semi-structured data whereas in elt structured unstructured uneven semi-structured data types also it will be supported time load is it will take lot of time whereas in elt the time is very faster it will take very minimal time and the flexibility so ETL has very low flexibility only whereas CLT know if the flexibility is very high because it doesn't worry about the data types and all it will directly we'll be writing it to the data Lake then the scalability so scalability is like for ETL we need uh there is a dependency on the scalability so it can be low whereas in ELD there is no scalability like we can just wall we can write the volume of the data directly to the data lake or data warehouse and support for data warehouse so it can edl models used for on-premises relational and structured data whereas in elt this for uh like you can use cloud infrastructure as well on-premise as well and it's suppose both structure unstructured uh on semi structured data sources as well so data Lake supported for currently for ETL we like data lake is not supported but elt data lake is a must because we are dealing with unstructured data as well the cost is it is high cost for small and medium businesses because the Transformations the computation power involved the scaling of the applications and services all those things leads to higher cost for ETL and elt the the cost is there but still when comparatively when we compare with ETL no elt is very minimal but the cost is there components with security protocols so with security protocol sizes I know like data type predefined schema and uh like tables access all those things we can restrict in ETL whereas in elt tests of in a state like people with that access they can view your data as well like not with the um not that is not the case in data warehouse because in data virus for example if we talk about bigquery which is a data warehouse solution offered by Google Cloud platform right we'll be giving access to particular table level as well so in that uh even though people have access to your project even though people have access your data set if they don't have access to your table like they can't view your data but that is not that will not happen in Eld so the next is maturity since this is for more than two decades there are lot of talents out there so there are a lot of talent there are a lot of public edl open source pipelines like open source all those things are there but ELD this relatively new complex and the people with uh knowledge on black people with uh knowledge on data warehouse with five six five plus years of experience now like that is related you know and it is like complex to implement as well we have to understand in and out that data like how um how to build out extra how the flow will be all those things so it is bit complex but it can be implemented but that experience and that experience and the planning involved no it will take some time which is not the case in ETL but whereas in ELD it will be ETL also there will be some discussions on what is the data type transformation all those things but it will be like minimal con like um the meat will be like very minimal only whereas in elt will be like uh complex level and the storage types can be used for on-premises or cloud storage whereas in elt it is for more into cloud-based solution only like elt concept test is more into cloud and there are different services in Google Cloud itself to do this Eda elt and edl pipelines to implement those things so this is what the difference between ETL and elt the next thing is we will be seeing what are the types of edl tools and there are basically four types we are not going to Deep dive into those types and all uh since we are more concerned about the Google Cloud platformer we will be directly jumping into what are the services all those things so enterprise software ETL tools open source ETL tools cloud-based ETL tools custom ETL tools so Enterprise level in the sense like each company will have their own pipeline built for that particular ETL process so that will be Enterprise level and that will be not generic one it will be particular to any Enterprise level and then open source ETL tools like Informatica edl tool but all those things and cloud-based retail tool and cloud-based ETL tool for example in AWS we will call it as AWS glue in Azure the services also data Factory in gcp we have cloud data flow and cloud data proc so cloud data proc comes with uh cloud data proc Edition Big Data service but still it works on Big Data Technologies whereas cloud data for it works on another another um package itself like it is called Apache beam so we will be seeing what this cloud data for cloud data proc all those things but in gcp if you wanted to implement ETL you can go with the data flow data proc or data Fusion for Azure it is azure data Factory for AWS it is AWS glue and some of the frame like most famous use cases are Data Warehouse in like getting data from your on-premise from your multi-cloud from your the same Cloud to a data solution basically to bigquery when we talk about Google Cloud platformer so if your data is residing in on-premise if your data is residing on AWS S3 storage and if your data is residing on Azure blob storage you wanted to get all the storage in one place you wanted to get all the data in one place that is in gcp's bigquery so Data Warehouse in solution for building machine learning and artificial intelligence problems and for marketing data integration Marketing in the sense basically what we did know like that poll and all those things with the data itself now they can get they can generate insights out of it like what is the month week week day all those data like marketing data based on that we can use this ETL pipeline to build basically for cloud migration whatever I said for data virus anymore if we test the data it is an on-premise alone if you wanted to migrate it into gcp bigquery or Cloud series or S3 storage or Azure blob storage we can use this Cloud migration for cloud migration basically we need to use ETL Pipeline and that comes under hybrid model hybrid model in the sense when your Cloud platform is dependent on on-premise it is called hybrid multi-cloud is when your uh when your ETL pipeline is dependent on other services in other Cloud platform also that is multi-cloud concept so the next 30 data integration so if you wanted to take a look if you wanted to look into you learn more about this ETL right I have given you this link this is official link from uh Google itself you can take a look at it so the next is we'll be seeing what are the products and services in gcp for building edl pipeline that extract transform and load and ELD as well so since we are mainly focused on ETL we'll be seeing what are the services and how we are going to implement it so for building ETL we have three services in Google Cloud one is cloud data Fusion data flow data proc so cloud data Fusion is nothing but it's a fully managed Cloud native data integration service that helps users efficiently build and manage ETL and elt data pipelines cloud data Fusion is nothing but it's simple drag and drop kind of service you have to just drag and drop establish the connection and you basically you don't need to write code like python Java SQL or go you don't need to write any code it is just an UA based service just drag and drop establish the connection trigger the pipeline all those things whereas the next is data flow so it is Unified stream and batch data processing that serverless fast and cost effective so data flow is nothing but it isn't big data ETL pipeline service and gcp it follows a unified programming model unified programming model in essence it follows Apache beam so it supports both stream as well as batch processing bubbling streaming in the sense like near real time real time and batch processing like monthly weekly uh yearly or quarterly or daily all those things we will be able to do it or implement it using data flow so for now data flow comes with three top languages one is Java go and python basically it is written in um Java and the SDK is available on Java SDK sorry Python and go so we will be using for our use case for our demo purpose we will be using data flow I'll be showing you the code which I written in Python uh the next is data proc data proc makes open source data and analytic processing fast easy and more secure in the cloud so if you wanted to use Big Data Technologies like Hadoop High Pi Sparks Park uh I have picked all those things so you have to use this data prompt so data proc is a managed service manage service in the sense it is some server based service so basically in Cloud platform and we are talking right there are two two concepts like Oneness server and the other one is serverless so data procession server based service data flow is an serverless based service again cloud data fusionation server based service only so in our practical demo we will be see we'll be using data flow with cloud storage and Cloud bigquery we will be building an ETL pipeline which ingest or which gets data from cloud storage do some transformation using Apache beam that is using the Python's Apache beam and then we will be writing it into our writing or loading it into bigquery so this is the ETL pipeline which we are going to see in our demo purpose and the next is data Pro I have already explained it up to so yeah I'll explain three slides after that now it takes I'll take questions I'll try to answer your questions as soon before uh before starting with practical implementation I'll take questions I'll try to answer your questions so cloud data Fusion so cloud data Fusion is a fully managed Cloud native Enterprise data integration service for quickly building and managing data pipelines like ETL and ELD pipelines so cloud data Fusion is basically an UI based service it allows you to build scalable data integration solutions to clean we can clean prepare blend transfer and transform data without having to manage the infrastructure without writing code and without writing any piece of code you can do all those things we can clean the data you can prepare the data prepare the data in the sense like Excel characters removal all those things you can do and cloud data Fusion is powered by the open source project called cdap and it is a code free as I told you earlier like it is a drag and drop tool and in cloud data Fusion it has around 150 plus connectors and configurations and Transformations available as of now and if you wanted to make any Transformations or any I wanted to integrate or if you wanted to do it right you can use this command line tool command line tool using gcloud the command line tone is nothing but like very similar to command prompt I'll just give you a gist of all those things what is command line tool what are the big data services in gcp I will show you all those also so the next is cloud data proc so not cloud data data cloud data proc so it is a managed spark on Adobe service that lets you take advantage of Open Source data tools for batch processing querying streaming and machine learning it is a managed cluster service in gcp basically it is an server based service in gcp it is basically for a big data Technologies like Hadoop and Spark so data proc automation so uh the the one key feature of using this the dataproc service now in in your on-premise system if you want to set up that uh Big Data ecosystem right it will take around six to eight hours or it will take around days also like one or two days also it will take but in gcp you know it will take less than like it will take less than two minutes like within 90 seconds you can configure your big data ecosystem completely if you wanted to use Hive you can use it if you wanted to use hbase you can use it if you wanted to use have pick you can configure the cluster within 90 seconds like less than two minutes so basically there are three different types of clusters in cloud data Pro one is single node cluster it comes with only one master node and zero worker node uh if you are using free trial that you will be able to create single node lesson only the standard cluster is one master node and respective worker nodes the I availability cluster is nothing but this cluster is where we will be using it in production based so three Master nodes and respective worker known and these are the different jobs available in data spark or Sparks equal Presto spark Hive pick and price bar so these are the different Big Data Technologies and different jobs available here I'll try I'll show you what I can show you this I can't show you this like this the next is cloud data flow so this is the one service which we'll be using which we'll be using in our in our demo as well so data flow is a managed service for executing a variety of data processing which supports both batch processing as well as uh streaming processing as well like real time near real-time all those things so it is written on um cloud data flows work on top of Apache beam so it is a serverless application and it is fully managed by Google Cloud platform and jobs Creator know we there is predefined template as well I'll show you that also there is a predefined template like from different services or if you wanted to use streaming you know like streaming will be done using Pub sub 2 cloud storage or Pub sub to bigquery or if it is batch processing cloud storage to bigquery so we can you do that predefined template as well and if you wanted you can create a notebook instance and you can do that as well so write data pipelines jobs in Java Python and SQL if you if you are writing quoted it will be in like you will be writing beam pipeline beam code but it will be in Java python or super so cloud data flow it falls under horizontal Auto scaling of worker resources for Optimum throughout results in better overall price to Performance so basically there are two types of scaling Oneness vertical scaling the other one is horizontal scaling vertical scaling is nothing but you have to manually edit the system configuration like configure compute power of that mission for example you are created One mission which has 16 GB RAM and 16 digital CPUs now if you wanted to extend expand it right you have to manually edit like instead of 24 you will modify it to 48 GB RAM and from 16 CPUs to 246 CPU switches this is what vertical scaling means whereas the horizontal scaling is the same machine configuration like 16 GB RAM and uh 16 virtual CPS now it will get um based on the load of the data it will create the duplicate of that machine so that is what horizontal scaling is and API is like Apache beam it follows unified API for both batch and streaming processing we don't need to handle batch processing and streaming processing separately we can handle both using this Apache beam and respective Transformations and functions provided by it so we'll be using this Apache beam Cloud version of Apache BMS BMS nothing but this B belongs to batch and eam belongs to steam so that is how the beam got created batch plus string right and it is an open source which is open source by Apache so we are like that is how it is known as Apache beam and unified an advanced unified programming model to implement batch and streaming data processing jobs that runs on various execution engine Runner so uh the one biggest advantage of using cloud data flow right cloud data flow or Apache beams it is independent to the runner you can run it if you're running the piece of the code which you develop more in Google Cloud if you want to run you can run it in uh run it using cloud data flowrender or if you are running the same piece of code if you want run it to using Apache Flink you can use Flink Runner you can use Spark Run the same way you can run it it is independent to the runner once the code is written it can run on anywhere like if you are running on Google Cloud you can use cloud data flow Runner we will be using the cloud data flow Runner only and we'll um if you are running in your on premise you can use whatever the Flink or Splunk all those things different Runner you can use so pipeline so Apache beam follows a certain set of rule which is different from the Big Data Technologies and which is different from all using it follows a pipeline a p collection and the Transformations so we like I'll just give you a gist of what it is pipeline is a unit which collects data process the data and produce the output so pipeline is nothing but the whole process everything it will run in the pipeline P collection is you can build that pipe like you can break that pipeline into multiple number of steps that is p collection and basically the Transformations will be done on top of that P collection whatever the transformation which you're applying with it will be applicable to the entire data I'll explain you this piece of code when we are a um when we are explaining like uh when we are seeing the actual python so enough of theory but like we will just we will see how to implement it practically so so this is my Google cloud like console Google Cloud console um here if you can navigate click on this navigate all right you can go into this big data services foreign tics under analytics you can find all the services which is Big Data we will be using this bigquery data flow and cloud storage so we'll be using that cloud storage to do like wheel view um we'll be using three different Services here so cloud storage is where we'll be ingesting our piping location will happen from cloud storage and data flow will take that data from cloud service data from Storage it will do transformation like minimal time is small transformation and finally inside like loaded to bigquery load so injection that is extraction transformation and bigquery will be uh loading we'll be loading it into bigquery so before that I'll just give you a few examples on what is bigquery so first you will see what this bigquery excuse me so I'll just give you a small impression like big variation date of Arrow solution it is basically a serverless solution so you can scale up to petabyte of data as well so it will be in the form of rows and columns only like it will be in the form of table tables only so this is the project ID and these are the these are the data set so under the data set you will be having different tables like under data set you can have hundreds of tables as well like data set you can give you can give permission or you can restrict you can give permission to this data set and you can restrict access to not query on this table as well so if I click on this names 2020 right these are the field name and these are the type and these are the mode like schema all those things it is already predefined if you wanted to query on it you have to just click on this query and enjoy this that's it so this is what the date of arrows will look like you can see uh the data on what is the job information basically it is on serverless so who's the user so all those details you can see it here and the respective schema Json file also you can see so all those things data as you can see and the execution details like how many time like what is the time it took to execute it by shuffled all those things or like records rate records written so basically we are writing only 10 records and there are totally Thirty One Thousand four fifty three records so you can save results you can explore data or else you can directly connect because once that data is loaded into bigquery right you can use this uh uh you can use this ba engine ba engine is something but using a big date like um visualization tools like look Tableau all those things you can connect with this be using this ba engine and you can query on it so whatever the query or whatever the transformation which you're doing it in your visualization tool that it will directly integrate it with bigquery and you can see the results directly from bigquery so this is like bigquery is a big question so basically