Pentaho Data Integration(PDI) Ultimate Training for Beginners | Saad Qureshi | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Pentaho Data Integration(PDI) Ultimate Training for Beginners

teacher avatar Saad Qureshi, Online Instructor

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

38 Lessons (5h 60m)
    • 1. Intro Video

      2:41
    • 2. Overview of OLTP SYSTEMS

      3:16
    • 3. Benefits and Limitations of Data Warehouse

      3:35
    • 4. ETL in Data Warehousing

      12:34
    • 5. ETLvsELT

      6:13
    • 6. Pentaho Data Integration Tool Installation Guide

      5:24
    • 7. MYSQL Installation for Pentaho

      12:31
    • 8. Pentaho Components and Steps

      7:12
    • 9. Input and Output Step

      17:03
    • 10. Input and Output Part-02

      17:18
    • 11. Input and Output Part-03

      12:14
    • 12. Transformation Part-01

      17:12
    • 13. Transformation Part-02

      6:29
    • 14. Transformation Part-03

      12:01
    • 15. Transformation Part-04

      4:25
    • 16. Transformation Part-05

      13:21
    • 17. Transformation Part-06

      3:24
    • 18. Transformation Part-07

      6:46
    • 19. Flow Step Part-01

      7:50
    • 20. Flow Step Part-02

      5:19
    • 21. Flow Step Part-03

      13:18
    • 22. Stream Lookup

      6:38
    • 23. Database Lookup

      3:31
    • 24. Database Join

      3:53
    • 25. Data Validation

      8:35
    • 26. Consume RESTAPI

      10:45
    • 27. SOAP API

      4:19
    • 28. SCD Type01 Implementation

      7:06
    • 29. SCD Type-02 Implementation

      10:11
    • 30. CDC Implementation

      19:32
    • 31. CDC TimeStamp Part01

      17:05
    • 32. CDCTimeStamp Part-02

      6:04
    • 33. Job Part01

      12:24
    • 34. Jobs Part02

      20:48
    • 35. Parameters Part01

      11:08
    • 36. Parameters Part02

      4:47
    • 37. Metadata Injection Part01

      12:55
    • 38. Metadata Injection Part02

      9:49
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

36

Students

--

Projects

About This Class

Brief Introduction:

Pentaho Data Integration (PDI) provides the Extract, Transform, and Load (ETL) capabilities.Through this process,data is captured,transformed and stored in a uniform format.

Some of the features of Pentaho data integration tool are mentioned below.

  • Data migration between different databases and applications.

  • Huge volume of data can be loaded from different heterogeneous sources.

  • Data Cleansing with steps ranging from very simple to very complex transformations.

Components of Pentaho:

Below are the components of Penatho data integration tool.

  • Spoon.bat----It is User Interface used to create Jobs and Transformation.

  • Pan.Bat------It is used to run transformation from the command line.

  • Kitchen.Bat---It is used to run Jobs from the command line.

  • Carte.Bat----Execute your Jobs and Transformation on Web Server.

  • Encr.Bat------Helps you to generate encrypted passwords.

Career Perspective:

If you want to pursue a career as an ETL developer,you should definitely consider taking this course.Most importantly,If you learn one ETL tool whether it's Pentaho or Informatica,learning other ETL tools won't be difficult.Also,the demand of ETL developer is keep on increasing as the data size is increasing. 

After this Course:

Once you are done with the course,you will have maximum knowledge of all the transformation steps that are required to clean the data.Furthermore,you can apply all the attained knowledge in the industry.

Cheers..!!

Have a Great Learning..!!!

Meet Your Teacher

Teacher Profile Image

Saad Qureshi

Online Instructor

Teacher

Background:

Computer Scientist with overall 5 years of industry and several years of freelancing experience. Other than this,I am passionate about teaching and guiding students learning programming languages.

Life Philosophy

Do not dwell in the past,do not dream of the future,concentrate the mind on the present moment.

Service to others is the rent you pay for your room here on earth..

 

See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Intro Video: Hello, everyone. Welcome to my course on Penta Ho Data integration et al two. So, in this course, first off all, we're going to have a brief overview off that other house concepts. Then we're going to learn the Penta hotel tie integration. It'll toe let me show you what we're going to learn in this. Do it. So first of all, we're going to learn the different steps Starter used for transformation. So this is the transformation step here. So do either different sections off transformation step. So first of all, we're going to learn how to extract data from different head pro genius sources. So here in the import section, I have the different input sources. I can get better from CSE file import. I can get that stuff from Excel file. I can get that from a database with either different sources. So the other different sections step. We're going toe cover Deezer transformation step. So the majority of the section which are most commonly used in industry, were going toe cover. So next we are going to have a job. So this job also contains different steps. We're also going to cover all these steps also we're going to see the difference between a job and transformation. So this is going to be a DEGALE course on paint our data in congressional killed. Now let's look at the career perspective off E t. A. Dude. So if you learn any deal tool, you can have a career in different of data barrels as an expert. Or you can be deal developer so that every salary off a needle developer is 89,000 barrier . Okay, so Louis Celery 69,000 the highest salary is one leg $16,000. Every salary is $89,000. So furthermore, if you learn funny girl tool learning a dairy kill tools are not very difficult. So now let's look at who can take the schools. Anyone having basic knowledge of database concepts in a skewer can definitely take this course, so you don't need pride that of their house for any Gail experience to take this good. So don't waste any time. Enroll yourself in the course and build your career at the detail developer, thank you so much and have a great learning 2. Overview of OLTP SYSTEMS: Hello. Everyone before explained the concept of data Warehouse. Let me first give you a brief overview off a database. What test database? In how our database is different from a debt of Errol's. So in a database which is also known as well tp well, keep e stand for online transactional processing systems. Well, DP Okay, well, BP, this database is also known as well compete. Well, DP systems contain the day to day operational information. OK, so let me give you an example. Off bank customers. Goto bank did create an account. Did deposit withdraw money from the bank. Okay. With either day to day operations that customers perform whenever they go to bank. So these types off information are stored in a well DP systems day to day operations. Wealthy piston for online transactional processing systems. Okay, so what about a data warehouse? How Our data warehouse is different from weighty persistence. So our data warehouse contains the historical information So true historical information. I can do an analysis zone on the data. OK, so data warehouse contains the historical information off the customer. For example, I can easily get the last five years off customers data in a deck of Errol's. Okay, so DW yet? So what is the source off? Well, tp system. So the source off. Well, keep e systems are well tp applications. Okay, lets I'm going to have the banking application. Online booking application. These are sources off. Well, TP systems. Okay, so these applications will generate the gator, so they're savages in the oil tippy system will be day to day operational letter. Vera's in the dw Etch that. I will be historical letter for the space off. Well, keep it. Systems will be not as large. Er's the w it systems. Okay, so DW it systems are also known as de assis decision support systems. Okay. And the force off dw it systems are where is deeply systems. Okay. Through the process off et in, we're going to lure data in the debt of Errol's et and extract transform Lord. So in the later part off the schools were going toe learn all these technologies. Okay, so that's the basic difference between well keeping systems in dw. It systems hope you're industrial this concept, so thank you so much and have a great learning 3. Benefits and Limitations of Data Warehouse: Hello, everyone. In this lecture, we're going to study some advantages and limitations off a gate of Errol system. So what are different advantages off a date of Errol system? A get away routes system provides the historical intelligence. So, as we know, in a data warehouse system, we're going tohave the historical information. So let me give you an example here. Let's say a company wants to know the worst performing product in last five years. So what they're going to do, they're going to use a data warehouse system to get the debt off last five years. Okay. Based on the information retrieved from a data warehouse system, they will find out the worst performing product. OK, so data burial system increases Grady and system performance. Okay, so data payroll systems are de normalized, meaning we're goingto have less number of joints to get the data. So normally, when you use joints, it takes time. So since data warehouse systems are de normalized, we're going to have less number of joints. So we're going to get the data in much quicker time so it will increase the Canadian system persimmons. I get a warehouse generates the high return on investment. So I get a Vero Systems are costly, so it takes around 2 to 3 years to get your investment back. OK, so that is I get away routes, systems generate high return on investment. A date of Eros saves time. So integrator warehouse, you're going tohave debt off different sources. Meaning you're going to have a debt off different department. So in orderto access to data, it will take less time because you don't have to quit either Data from different sources. You will have a central repository there. You can easily get the debtor in a quicker time. Enhanced data quality and consistency. So in a data warehouse that has come from different multiple heterogeneous sources so that I'm different sources will have different former. They are saved indifferently, but vendor digitizing data warehouse, it will have a single form. It data will be generalized. Okay, so we're not going to compromise on data quality inconsistency in the data where l systems . So what are the limitations off debt of Eros. So the major disadvantage is that a data warehouse can be costly to maintain, and that becomes a problem if the debt of arouses underutilized So my next point is, since that that I stored in a single repository, there may be a concerns off ownership. So in this case, organizations must ensure that get our ownership should be given only toe trusted individuals and users. So we have to make sure that ownership of data should be given only to trusted individuals and users. OK, so these are some of the advantages and limitations on for debt of around systems. So hope you understood this concept, Thank you so much and have a great learning. 4. ETL in Data Warehousing: Hello, everyone. In this lecture, we're going to study the concept off GTL in data warehouse. So it'll stand for extract. Transform in load. So ideal is a process off extracting data from different hydrogen. Your sources. Then we apply different transformation techniques on the data. Monster data eyes cleaned and transform. We finally lured the detainee, get a warehouse system. OK, d either Different sources. So, in between data warehouse system and the source systems, there's going to be another system which is called a mediation system. Okay, there will be another system which will be mediation system. So what mediation system does It collects the data from different sources systems and delivered it to get away routes. So mediation system delivers to data any data warehouse over. Okay, there's going to be a server called dw etc. Okay. Dw yet server. So mediation system collects the data from different sources systems and delivers it to DW . It's ever for data of it. Using DW etcetera is derided. So from here, the it'll process runs et al jobs run extraction transformation load from here. These it'll process runs. Okay, So what is the benefit off having a mediation system in between source system and get a payroll system. So, basically, in gates off any data loss, we can always reach to mediation team to get the lost better. So mediation system retains deep back up. Normally, the back of policy is off six months. So intimidation system. We normally have a debt off six months. In case off any data loss, we can always reach toe mediation system. So what is the drawback off having imitation system? So basically, a mediation system collects the data from different sources systems and thanks to debt of air ounces from the problem is if the mediation system is down, I've been not able to get the data, so the whole system will be down. Okay, so I'm not going to get data from any source system. So let's say if there is no mediation system in between my get a very houses connected toe , different source systems individually letter. In this case, if one of the system is down at the least, I can get the data from other source systems. Okay, But when it comes to mediation system, if mediation system is down, I will not able to get the data from all the source systems. Okay. Mediation system is a single reporter tree veer. It collects the data from all source systems. Okay, for desta brought back off having a mediation system in place. So now let's look at the data extraction strategies. So there are two types of data barrels extraction methods. Logical extraction mattered in physical extraction method. So first, we are going to study logical extraction method. So logical extraction mattered has actually two methods. Okay, full extraction and incremental extraction. So when you're loading that at the first time, you will Jews de full extraction method in this matter that is completely extracted from source systems. Okay, since it is complete extraction, so no need to track source system for changes. Okay, so in this matter, data is completely extracted from source systems. So when it comes to incremental extraction, if there is a changing that there are some new details I did. We used the incremental extraction mattered. So if there is a change, the new chain that I will be extracted from source system. Okay, So this approach is known as incremental extraction. So the changes in the source that I need to be tracked. Okay, so we used the times. Time Call them to track the changes. Okay. No, let's look at the physical extraction methods. So physical extraction has two methods online extraction, offline extraction. So, in this method extraction process directly connect to the sewer systems and extract the source. That Okay, in case off offline extraction, we get the data from files, flat files, dumb files. OK, so you can consider the falling common structuring offline extraction. Flat trails are dumb fires. So this is what we have from physical extraction. So now we're going to look at the ideal transformation techniques. What is the meaning? Off transformation data is extracted in a staging server is in a row, former and needs to be cleaned and transformed. Transformation prepares the data for analysis. Here are some of the most common types off transformations. So first, we're going to look at the basic transformation techniques. So the first technique we have is cleaning. So in case we have the null value in the data, we're going to change it to zero in case. So let's say in some source file we have this mail in some source. I'll we have em So we're going to generalize it to M. Okay, we will. SEC de gender. Call him. Tow em. We'll get to have a single former. So, like, Vice Weaken said this gender column female to f to have a single forming date. Former consistency. So we're going to have a generic date for all the files. All the get okay, de duplication. So, in case off any duplicate values were going toe first identified the duplicate records in the data and then we're going to remove the duplicate records. Okay. Former trivia in case of data type convenient. June it off my retirement conversion. So, in case there is a column in which the values are in meter, there is another column in which the values are in sancti meter. We are going to have it one for make. So if the values in a centimeter, if you want it to in meter, we're going to convert Centimeter two meter. Okay, So this is what the my human conveyor unit. Also, let's say if we have a decimal value off four digits, so what we're going to do? We're going toe convert the decimal value. Two digits. Okay. To have a genetic value. So daytime conversion. So also were going toe convert the date time for my tok etcetera. Sochi restructuring. So, in a data, we're going to have a key Dekel former relationship across cables. Okay, so we can have a primary key or a sorrow gate key. So some advance transformation techniques. So there are some other techniques that we normally use it in. Get a Vero system daddy vision. Okay. So we can also create a derive column to do a particular task. For example, creating a revenue metric dead subtracts texas. Okay, so we can have a separate column off revenue so we can collect. We can subtract Texas from the this revenue metric. Okay. Filtering. So selecting only certain growth and columns. Okay, This is the concept of filtering joining linking data from multiple sources. For example, linking their test searchers, Google AdWords, Facebook, Kurds. Okay, splitting. So splitting columns. Splitting a single column into multiple columns like customer name. So we can splitting into first and last name. Okay. Data relegation. Simple or complex. Data validation. For example, If the 1st 3 columns in a row are empty, then we consider we do not consider this particular. We will reject the rule for processing. Okay, Summaries ations for values are some rise to obtain the total figures so which are calculated and stored at multiple levels as business metrics. Okay, so did awesome advanced transformation technique strictly using that arouse. So now let's look at the process off. Get a loading in. Get away, Rose. So data lording is the last step off. It'll so monster that is cleaned and transform. We're going to load that their tiny debt of Errol System. So, in a typical letter of arouse, a huge volume of data needs to be loaded in there. Short period of time. Hence, the Lord process should be optimized for performance. So, in a data warehouse system, their ties loaded at night time. So we have to make sure this thing that day minus one data should be successfully load again a data warehouse system before morning before early morning because reporting process is done in a morning time. Okay, so the manager has to present it. Toe senior manager. The reporting has to be presented to senior manager. Okay for business. Youth types off loading. So there are three types off loading, initial lording, populating all the data warehouse tables. So when you're loading for the first time, you will use this mattered initial lording, incremental loading. So, in case off any changes or in case off any data loss, indi data warehouse we can use this incremental Lord mattered, applying ongoing changes as one needed periodically. So what test if full refreshment, What is the purpose? Off Full refresh. And when we used this full refresh loading process, the first understand the full refreshment it so in full refresh, mattered all that the kind the tables are deleted. Were going toe again. Reload a debt. Okay, so this is the full refresh Matic. So when we are going toe used the full refreshment it so basically during the process, off loading. So let's say if 30% of the details loaded, then we are encountering an error. So what we're going to do, we're going to delete all the 30% of their time. We're going to start the full refreshment It Okay, let's say if 80% of the Caretas loaded and then we are encountering in other, then what we're going to do Since the data size large we can't load the whole letter again . OK, so we're going to use the agreement tell mattered so that 20 remaining 20% off their travel below did through incremental mattered. Okay, so, in a get of a house, we have to make sure this thing that day minus one data should be completely loaded in a data warehouse system before early morning. OK, so now let's look at different popular et al toes. So we have a IBM data stage Informatica Panta Ho telling. So these are some of the tools that we use it for evil purposes. Okay, Hope you're industrial. The concept, Uh, thank you so much and have a great learning. 5. ETLvsELT: Hello, everyone in that other house, there are two approaches that are most commonly used. Et l and plt detail stand for extract, Transform and load. You lt stand for extract Lord entrance. Um, so now let's discuss these two approaches. So first, we're going to discuss et il approach. So when we talk about detail uproot, get, are from different sources are first extracted in a separate it'll staging server. So this is the separate it'll staging server or you call it. It feels to stating, Sarah. Okay. Their taste first extracted here in this server. Then we are going toe. Apply the transformation techniques on this detto here in the it'll stating several monster that is transformed and clean. We finally going to Lord that a tiny target gwh database. Okay, so this is the ideal approach, conventional approach and the new approaches e lt which stands for extract Lord and transform. So when we talk about a healthy approach, data from different sources are first extracted in a d wh database. Not in a separate. It'll stating through. Okay. It will be extracted in a d wh database in the staging. That abyss from here, it will Lord that attain a target database of once The data is loaded. We're going toe. Apply the transformation technique on the D wh target database. Okay, so this is the difference between ET al any 80. So transformations are applied on D wh? Target database there s in this. It'll approach. Transformation are applied on et il stating centered. Okay, so when we talk about ideal approach, this is high cost. So the reason it will have high costs because the transformation is applied on eating, staging several, which is the detail tool server. So it is so normally for detail operation we used tools like I am that estate informatica frente. Hold that I integration tool. Okay, these are details, tools. So this approach may impact the performance off detail tool. Also, it can impact system performance, memory utilization. It will increase memory utilization. It can Ingres CPU utilization. So dis approach is not as good as lt approach. Okay, This is high costs and can impact the system. Okay, So when we talk about lt approach, transformation is done on deep target database. Not only separate et al stating several. Okay, so this will give me the better performance. So also with the detail approach, there's going to be a communication between 80 s staging server Andy Target database. Okay, so this may take some time. This is time consuming. Various. Here, the target database. We're going to apply the transformation on the target database. Okay, so this approach is better, so there's not going to be any communication. So let's look at more difference between et al any lt So that transformed at staging through and then transferred toe Data warehouse. Okay, look transformed. Hearing the staging several. Then move toe Data warehouse. Okay, database that I remains. Indy database off the debt of a house. Okay, transformations are done in the eagle staging Thera. So in this approach, transformation are done in hair in the detail stating third transformations are performed in the target system here. We're going to apply the transformation on the target system here, get a first loaded into staging and later loaded into Target's system, which is time consuming. Doctor first going to extracted here. Can Sandra. Then we're going to load the data here in the target database. This is time consuming. Okay, because there's going to be communication between two separate servers. Okay, the eternal stating. Several in this DW database. Okay, get a load again. Good target system only once, Which is faster. It'll process needs to wait for transformation toe complete as data size. Gru's transformation time increases. Okay, so this DW target database has to vit for data Toby transformed in clean. Okay, so one study it s transformed unclean. Then it will be passed toe. Regrettably, Etch Target database. Okay, so it has to wait for the data. So in detail process, speed is never dependent on the size off that. Okay, It needs high maintenance, as you need to select better to lord and transform. Okay, So this approach needs high maintenance. Low maintenance asked. That is always available for just the main difference between detail and dlt idealism. Conventional approach dlt is a new uproot. Nowadays, we are using lt approach in a debt of aero systems. Okay, So hope you understood the difference between these two approaches. Thank you so much and have a great landing 6. Pentaho Data Integration Tool Installation Guide: Hello, everyone. In this lecture, we're going to learn how to download and install Penta Ho data integration. It'll toe. So first of all, I'm going to open the chrome browser down Lord Bank Tahoe, Data integration. I will open this link. Okay, download. You're down. Lord will start shortly. OK, download process has been started. Look, 1.1 db file. Okay, so since I already have it in my system so I'm going to cancel this download process. Okay? Cancel this. Look, I already have it in my gig directory. So let me show it to you. This there to integration. So this is I already have it in my system. So next time going to download and install Javadi DK. Okay, so go girl down. Lord, do you geeky Okay, clicked on this link. So download article Dogg, Except I will downloaded for Windows Machine. Okay, I will the sexy file. So since I already have it So I'm not going to download this ivory, cancel their download process, can sell it. Look, I already have it in my directory. Gigi gave this disorder So first I will download Penta. Hold that integration it will toe then we're going to download and install this J D. K. Okay, so we're going to install this decay Indeed, directly. So next we are going to sec the environment Variable if I will go here. Systems working system setting view Advanced system affecting severe Guan system setting environment variable. But okay. I did so de their tank aggression. No, dear DK Been so I will give this part. Okay. Here, in pure this part New. Okay, no check again. Look. Okay. This part next we are going to system variable new. Okay. Java underscored home here. I will give this part. This bottle gay. Okay. Okay. Okay. No, check again if all the setting is correct or not to give you advance system sitting, but fine. Okay. Java underscore. Home g younger score. Okay, this do decay. But okay. Okay. Next, we are going to open the tool. Be get the integration. So here I will open spoon dot Bad file. Okay. Spoon dart back so it will open. It creates some time. Bye. Take will open. It can sell this. Cancel this. - So this is how you configure Penta ho data integration et al to learn your system. Okay, So thank you so much and have a great landing 7. MYSQL Installation for Pentaho: Hello, everyone. In this lecture, we're going to learn how to install my SQL on your computer system. So once toe my after databases installed, we're going to configure went out there to integration toe with my espirit. So first off, all I'm going to open the grown brother. So here I write down Lord my s cured. So I will click this link. My Skrill community downloads deaf dark. My skrill dot com slash back slash downloads. Okay, I will click this link. So here I really click this link. My SQL communities Take care, Gordon. So my SQL communities are 8.0 dark 17 Okay, My skull installer for windows. Goto down Lord page. So here I will have two options, so I will select this option. Windows accepted, 6 32 bit. Emma. Science stolen. Okay, so click download Britain. No tanks Just start my download. Okay, So dollars process has been started. Look, so I'm not going toe down order since I already have this file in my system. OK, I will cancel the download process concealer, so I already have it in my system. My SQL underscore slash. So I've placed this file here in the fourth. Okay, I will click this. Yes. Okay. Here I will have different options. The well, a perversion server only client only. Full custom. So I was selected. Custom option. Next. Okay. My SQL server. Select this. Okay. My s school workbench 8.0. Select this option. So my skin connector Select this. Okay, so I have selected the three options next. Okay. I will place death in my d directory. De. I knew my SQL. Okay, so you already have it. Yes. This Do you, my sq. Okay, De Maya's doing here, so I will install my escalated in this folder. Ok, next. Still have warnings? Are you sure you want to continue? Yes, Executive. So the extradition process has been started. It may take 2 to 3 minutes to install, so installation has been completed. Click Next button. Next. Okay, standard My school's over. Next. Next. Next. So my school. Good. Bufford, Give the monster, dear. So this is my boss furred. Next. So the 60 Windows Service name my Skrill 80. Okay, Next executive finish next finish. So my Estelle connection, this is welcome. Toe my escort work bench. So the city geoeye interface. So in this workbench, I'm going to write SQL Queries here in this bench. I have all my database. Okay. Local instance. My Esther Beauty. Give the password here, so no, I will create a debt a bit. Create. Get a base project. Okay. So, executable this Grady. So Kuwaiti has been successfully executed. Database says me created for the name of databases Project. Now I'm going toe open the Penta ho director integration Toe de directory. They're going condition spoon dark back fight. Ron F. Administrator. Yes. So now next step is to establish the database connection. But before I need to check. Very Maya school services are running on, so I really services. I will search my SQL services. Okay. My s 12 80 status running. Okay. Now I will establish database connection with Penta Hotel authentication. I've been playing this. Okay, so you have to select the final option file. He knew transformation. Okay, so now you've established the database connection tools visit, create database connection. Okay, So death is the name of database correction database. Name is my SQL. Okay. Name off. Database connection test. Next. Whose name? Off database. Server. Local. Okay. Name off. database project. The project is the dental based name. So this database Okay, next user name the road password. So you have to provide a password, which you have sec. Why? Installing the my SQL database? Okay, I will give my posture here. So now you re investigated this connection test database connection. So look, what if they rendered So the owner is my SQL Dark jar finalist missing. Okay, so I have to download my SQL connector Dark jar. Fine. Okay, so this my SQL connector Dark jar file will help Penta hold data integration to communicate with my school database. Okay, so now I will download this My SQL connector, John. Okay. Don't look. Connector door. John, fight. I will go here. So this platform independent So I download the zip version off this by looking download, click, download. Go. No tanks. Just start my donor. Okay? Sure. Sure. Informed extractive. My SQL gonna daughter. Okay, so what I'm going to do, I'm going toe completist my skull connector Java Ben Dodge are fine. I will competence, fight and based indie here in this directory. Go integration library. Okay, so this is the directory. Were my went out better integration Tool is president. OK, so I will go here, get the integration. This library folder. So here I will face this. Fine. OK, so now I will try to establish the database connection. Okay, so I have to restart this paint the whole data integration to okay to now. Cancel this Jensen against this, do you? Okay. Open their integration. Spoon got back. Okay. Run that Agnes tricked. So now I will click five new transformation. Do with Viser. Could get Get up this connection. Deft. Okay, My s school. Next, who was named? Local host. Projective detector based name. Next route 50. You The name test turned up with connection. Okay, look, connection toe test was successful. Okay, Now the connection has been established. OK, Finished. So this is how you establish that of his connection. So hope you invested the concept in the next lecture. We're going to learn more about Penta hotel integration. Thank you so much and have a great landing 8. Pentaho Components and Steps: Hello, everyone. In this lecture, we are going to learn different components. Softbank Tahoe Data integration to locate. So this is my directory. Were my pen Tahoe data integration to list present. Okay, inside this directory, I have different files. So the first company and get they're going to discuss it is spoon dart back, spoon dark back. So basically, it is a user interface. It is a user interface. Okay, which is used to create job and transformation, which is used to create Job Inc transformation. So what is the difference between transformation in job? So let me show it to you. The difference. The transformation is collection off steps which do data transmission. So data transformation is converting data from one form to another. Okay, so when we talk about transformation, all steps executing parent So what this job and how a job is different from a transformation. So job is also collection of steps, but the difference between job and transformation is in job. Each step execute one by one. Okay, Very is in transformation. We can execute each step in Petrit. Okay? Our transformation as many possible start places in all steps, executing better if a step has a step before it. It will take their time there in Judit. So a job has one start place and execute one step at a time with one flow through the steps . Okay, so that's the main difference between a job and transformation. So when you open went out that integration tool, you will have two options. So file new, you can create a job. Our transformation. Okay, let's say transformation. So here you will have different options. Input section output section. So when I opened in protection, it could have different options. So you can take an input from CFE file. Okay, the there different sources from where you can take input. Okay, so this is the Allport stream. Okay? So you can output data in different streams, so in excel file in in table. OK, text, try table output. Okay. Dither, different sources. We're going to discuss mostly all these sources. OK, Not all source it, but mostly all sources. OK, so also here we have different options. So we're going to discuss all these options one by one. So now let's get back to components Off went out that integration to look a the second component is Bang dart back Ban dark bed. So this is my second component. So basically, it is used to run confirmation from the command line. Okay. It is used to run consummation from command line. Okay, so this is the second component off Penta ho detto integration to third component. If kitchen dark bed, kitchen, dark back. Okay, so this is my third component. Dis component is used to run jobs from the command line. Okay. It is used used do run jobs from the command line. Okay, Come on line. So this is my third company. My four component is carted art back C A r d Dark beg. This is my food component. So basically carted or bet Execute your jobs and transformations on Web server Cartier is a embedded web server. So let me give you an example. Let's say I'm running a particular job and transformation on my system, but I have limited resources on my system, so I would request carted dart, bad server toe, take some load off jobs and transformations, so basically discarded. Got bad server. It's used for lord balancing. Okay, So executive executes your jobs and transformations on its separate Web server transmissions. Oh, embedded Web server. Okay, so my fifth company taste encryption. Dark bed E N c. Odd. Dark bet. So basically discomfort me helps you to generate encrypted password. Okay. Helps you to generate to generate encrypted passwords. Okay, so let's say so. Let's say if you want to use a password in somewhere So what you are going to do you are going to use dis component to generate the in cryptic pastored. Okay, So that it is not visible for everyone. Okay, so these are some of the components off when Tahoe data integration food. Okay, so if you open the directory here, you will get all these fine spooled aren't bad file. Second component is banged or bad file. Okay, which is this which is used to run transformation from command line. Okay. Kitchen door, bad file, which is used to run job from the command line. So next Iscar Kadar bet. So it helps you to execute your job and transformations on a separate web server. So let me show it to you. Carter, dart back. Okay. So I have any other company. This is encryption dot Back helps you to generate the encrypted password. E N t r. This file. Okay, so hope you are industrial different components. Charles, point out that the integration to thank you so much and have a great landing. 9. Input and Output Step: Hello everyone. In this lecture we're going to see simple example off transformation Step in Penta Hold that integration tool. So when you open Penta, hold that integration tool You will have two options You will either create a transformation or a job. So what s the difference between our transformation and a job? So this thing we have already discussed in the previous lecture So basically transformation contains different steps all steps in transformation executing pattern various. When it comes to job, it also contains different steps. But these steps executes one moment. Okay, So also in job, we're going to have only one starting point, whereas in transformation we can have multiple starting points. So there's some main difference between a job and transformation. So in this example, we're going to create a transformation. Step new, find new transformation. So on my left side, I'm going to have different options Input output inside the import option, I will have different in put options. Okay, I can take an input from a C three file. I can take input from table OK or lap import. These are different input sources. Ok, let's open this output section. Okay. In the output section. I have different output files. Okay, so I can out protector in Microsoft Access Excel. Okay, table, I can output that time table text file. We have different options. So here I've taken input from a CSU. Fine. Just drag and drop here. Okay. Double click. Step name. Give the name Thor CSE file. Okay, CS 35 Finally give the part here. Browse, imply. So this is my part. So let me open the employees CSP fight. Okay, just copy this based here. So this is this year sniff file, which we're going to extract it. Okay, So imply I d first name, last name joining a department. So this is my CSE file, which is located in this folder. Okay, De Lemaitre, this is coma separated file. Okay, enclosed with this double quotation mark. Okay, but first, before scientists check as 50,000. Okay. So you can also increase the capacity off buffer cider. Okay, so it all depends on your rams, I So if your ram sizes large enough, you can increase the capacity of buffer size, Otherwise it will degrade your performance. So here I have said the buffer size 50,000 So Okay, now running in parallel. So if you check this option So let's say if your data sources large enough you're getting the better in a large volume, then you can take this option running in better. So Penta hard data integration to will read your source file in federal. Okay, Each record in peril. So now, since my CSP file is not large enough, so I'm not going toe check this option. Okay, Now get field review. So here 100 number off lines to sample. Let's 100 simple file. So Okay. Implied number. Employee number. First name, last name, data type format lent. Okay, currency. Leave it as it is. Okay, remove this. Lent is sect for employee number. Lent effect 15. So you can change it. Has the local at 6 20 Okay, get form It Is this. So let me preview this debt. Okay, look. So this is how your data looks like. Okay. Now blew us. Okay, No. Next I'm going toe output. I'll put section here stable output. So double click table output. Name, off table outboard. Let's say employees connection. Let's say if I know I have to create a new connection here. Okay? visitor My SQL db d connection Name my Escalades. Ddb name Next. So first I have to open the my school work. Bench my school work Bench check If the services are running or not, my school services are running or not. I have to check this first. My SQL Services are running status running. Okay, No, give the passer there. Close this. You know who's named? Local host. Get a base Name is Project OK next, You the name route. Give your posture there. Test database connection. Okay, finish next. A Stargate schema. I believe it as it is. Target table. So browse this. There's no target. Terrible table. Okay, let's so here I will create a one. Imply is the target table. So in order to create this imply table first I have to connect so or seriously filed with this stable output. Okay, click dis option. This arrow okay. Connected with this stable output. Okay, Meinel, put off step now. Double click it. Get field big this option. Target table employees. Okay, get free. So now I will get this wheels implying number Extreme. Feel implying number. First name, last name joining a department. Okay, now SQL create table employees, so execute. Okay, so now table has been created in the project database here. Okay. Select star from Project Dark employees. Look, table has been created. Execute. Look, Currently, there is no value in the in this imply table. No. Okay. CSP if I now, I will execute this whole transformation step for to execute this whole step, you have to click this option. Run. Okay, Don't run. You have to save this in some folder. So transformation. I will save this file. Indy in this folder first transformation. Okay, save this. Look. It has been successfully executed. Look, this green tick marks shows that it is successfully executed. Transformation has finished. So this is the logging tap, and this is the step metrics. So this is important. So once you execute the transformation step, you have to look at this step metrics. So look, step named source. EEC file 10 records have been returning the table. OK, input. It has taken input. 11 records. So one record is the hunger. 10 is the values and one is the header. Okay? It has returned 10 records in the table. So active Finished time 0.0 2nd speed. 1100 records per second. OK, what about table output? Read 10 records DE Stable output Rec 10 records written. 10 records. Okay, Finished. Active status. Finished time? 0.1 2nd speed 1 67 records per second. OK, so did this Is logging section logging tap. So this logging tap will help you identify any other In case you have encountered any error . You can always go ahead in the logging section so you can check. What is the error from this logging camp? Okay, so what I have done here is we have just simply extracted the data and loaded indie table. So new note E and transformation, trance information extract. And Lord. Okay, this is extract and Lord process. Now, you can also Ah, this is the file output. I'll put that tiny ah, text file. Okay, just a row. Click this. This here main or put off step. Okay, copy. You want to co operate nor distributed copy? Double click here. Text file name. Let's say I'll put five Imply underscore. Output is deep name here. You will give the part. So this is the part. Okay. Fine. For the 60 part. Okay. Now content here. A pink. So what is the meaning of discipline? If you run the same transformation, step again and again and you will not select this a prank box. You don't select this option so it is going toe override the content. So if you check this upend option, it will not overwrite it. It will appendix. Okay. Here. I will not take this option. Okay. Separator this vilest coma separated. This is going to be semi colon separate league. Let's I want Toby Comma. Separated now for Mitt Off the for you. Okay. By default compression. So if you wanted in zip form so you can select this in compressed form. Split every rule. So what is the meaning? Off split every Ruth. So let me explain this concept to you. Let's say if you're getting 1000 records from the sore side. So if I write 100 here, So what point our integration tool will do it will divide my data in 10 different files, meaning every file will have 100 records. Okay. Total will have 1000 records, but every file will have 100 records and in total were going toe have 10 files. OK, so this is what split every rules does. Okay, So feels here. I can easily get the field get filled. So imply number indeed. Er, this is the farm. It This is the land. OK, so Okay. Okay. Now check if the file is no B be here. Data integration here. What is the name of file? Name of file is for Name off. I live. Let me check this. That integration file five. This is the file. Okay, look. Coma, separated file. Let's see. Tab separated. Five. Uh, corn tank, Remove this insert tab. Okay. Now. Okay. No executor. This look, it has successfully executed. No check. Look. Space. Okay. No, wait for a second. Going tank. Let's say Header executed. This the ball click. Look, Hager, it has included the head there as well. Okay, No. One more thing. Ah, let's say in compressed form, let's save the file sizes large so we can give the their time. Compressed fun file. Look. Compressed form. Okay. Five. One more thing. Janjic against in this ah content I got okay. Fine. No compressed from none. So here you can look include date and file name. Yes. Include time in file name. Okay. Now Okay. Executor death. Look, it has successfully executed Look, sewer side. So you have to read these this step metrics. Okay, in order to confirm that you're get is completely loaded in another table or in textile are other files. Okay, so now look, file with date and time for Mitt. Okay, look. So this is the small example in which we have extracted and loaded their time table and text file. Okay. Hope your industry The concept. Thank you so much and have a great learning. 10. Input and Output Part-02: Hello, everyone. In this lecture, we are going to explore some more transformation steps. So we're going to go here in the import folder. So last time we have discussed extracting their tough from CSE. For now, we're going to get data from XML Source So Dragon drop here, let me show you the XML file. So this is the simple XML file. I will open in north back. So this is these exemplify. Looks like we have different tags. So inside different tags we have different values. OK, surprised tag here. Value name, tag value, no cancer. So ah example file, then. Well, I'll put ah books section table output text file. Mm. Eggs l output be different output sources. So Okay, shift look. Press shift, then click. Joining men off. Put off step shift. No join shift. This the may not put of step copy. Open this double click on this. Get data from exemplified growth. So this is the directory where my example file is present, so I have to give simple, dark examine eg. So, look, a directory has been added here. Selected files, show file names. This is the only filename simple dark examine preview rules. Please specify the part. Good. Looping element. Ok, ok, ok, ok, so content here I have to specify the pot. Get export nodes. So I will specify this part. Food. Okay, No fields get feels. So I will get all these wheels. Name, prize description, calories. Corn Tank Now Review. Ruth, so did either my Ruth. So these are my rules from example file. This is the name of food price description in the calories. Close this. Okay, now I will set this table output now Connection. No existing connection. I have to create a new connection. Air my SQL Gaby, my SQL Next local host project is D Name of database. OK, next the route. Give the posture here. Test database connection. Okay, fine. Finish. So this is my script. Gaby is the name of connection. Target table. Wait. Get a base field. So the name off tables, I will create a new table in the mire school. That abyss sort of creator table. I have to specify the a target table name off table. Let's say I'm specifying the target table name food. Okay. Now specified. That off base. Feel OK? Check this. Get feels sq. In So I am creating a table which is called Food Food Table Indie, my SQL database. So execute this will create table in my my SQL database table has been created Now closed this Fine. Okay, this table has been created. Now text file our out book text file output filename file. Okay, here. I have to specify Depart. So the party is this This is the part. Let me show to you. Ah, go to integration. This is the part. Okay? No. So specify here. Month giddy here. No going tank. So I don't want toe a penguin separator. No. Insert tap. Remove this semi colon insert tab. Look, Deb. Mm. Okay, fine. Get Quills file. Okay, now Microsoft excel a put. Ah, Victor de here. Okay, that the integration safe. So this will create the excel file in this directory. Content custom or low. Okay, get feels. String description. Calories indeed. Your name string. Okay. Now I will execute this transformation step. Execute this transformation. I will have to click this run back and run. Run. I have to say this first consummation de transformation. Get data examine. Save this. So this has been successfully executed. Now check the table output First select start from project is the database name Dort. Food is the table executing this query. Look, look, I've got the result. Okay? Now check the text file output so the text file output is in in this directory Text file. This this 1957 AM? No, it's not this. It's this 8 43 being 8 44 PM So district bidder finally. Look, now the text file Microsoft Excel output. So check this Xel file. This is the director. Okay, this filed out excel. Look. So what I have done here is so I have extracted data from example file. Then I have loaded their time table text file in Microsoft Excel. Okay. In three different sources, I have loaded data, so no, we're going to explore some more step. Ah, open this input section file new transformation in port, so ah, generate root generate root discipline. And this Death 100. So these two ah, utility right to look. So first, I'm going to discuss this data grid. So basically, district a great in district agreed We're going toe store static data or damage data, which is basically useful. Their stink purpose So here I've been specified the dummy data. So, Data, Let's I'm going to specify the we gotta employees. I d type is in danger. Implying name, Name, string. Ah, Imply department department strength. Age, indeed. Your expedience Decca. Here I will specify the data values for four employees. Let's have 123 right to four vine to five. Name Lex John. His department at job Dick Marketing and sick going Tito 20 three 28 one. So food. So I can save data for references or for testing Berbers in the data grid. So review. Look. Last value. Thinner value. Okay. Okay. No. So what if the concept off generate Ruth? So this is also used for testing or reference purpose. So let me open this. Generate Ruth. So name is, let's imply I d Name it. Expedience guy. Ah, string each include your experience in danger. I dealer t strength values. 123 So changing. Doing teacher name Alex. Uh, going to Terry experienced. Ah, do so Limit then. So this will repeat these content 10 times. Okay. I will have their time imply I gain name, age experience these values 10 times. So let me show it to you. Look, Thank times. 123 Alex Trinket Reto. So here, I can change. Do review. Look two times. So if I have to repeat the content, so I will use generator. Russo, if I have to repeat the content multiple times, I will use generator Ruth. So if I have to use the dummy content so I will use data, Greg, So this dummy content can further be used for testing. Book was so no right to lock shift this. Ah, I would have to drag this right to log right toe. Ok. Ah, utility Right to log. So this No execute this run. Mm. Saved this side first. Mm distinct. Decca it has successfully executed. So what? Right to log. Does it displaced the result in the logging section? Ok, so I will get the result in the logging. Tabuk. Look now explore some more input steps. Input Drager So what? We can get a wreath Get file's broken toe. So? So it's greatest later row count off files in the right to log. So utility this. So now I'm going to give the part here, so I very give b So e directory Ah, get a sec. Script this movie Meta data eg so e he get a sect working the script. Ah, faster example. Select me. Open this style first. So it's a big fight. It's a number off. Records are so 5044 five little 44 Ah, Show file Amos movie underscore matter that are dot CSE If the preview rules 1000 growth, I have toe so 5044 is deep row count gone. Tank row count, rule count. Okay, now execute. This is on run, so I will get the row count and the logging type broken equals to find 04 for So this is like count function likely using SQL. So? So it will not include the Hager Only include the records, Indy, if I'd exclude the Hager. So these ism of the transformation step step, we normally use it in the next lecture. We're going to explore some more transformation steps 11. Input and Output Part-03: Hello, everyone. In this lecture, we are going to explore some more input steps. Okay, so I'm going to open this input section here. I will get this, Get fine names. So I will explain the concept off. Get filings. So Dragon drop here, I'll put so get filings. So let's suppose if you have multiple files and if you want to Lourdes multiple files in a cable, you will use the concept. Get filings here if you open it. So you will just give the directory name where you're multiple files are present. So let me show it to you. De So this is the directory. They're my multiple files are present employees. Uh, underscore 10. So this file contains only 10 implies nine implies this lie contends 15 place. Look, de the columns are same. All files contained. Same columns. I d. First name. Last name, gender, country It let me open. This file contains 100 records. Now, what I'm doing here is I'm going to Lourdes all these files in a table, so I will lord these files in a separate table. So I will open this file or directory. So here I will give the part off the files. Copied ISS Biest hair eg So part has been addict show filenames. Look, these are defines employees under score 1000 Close. No filter nothing. Okay, so see a three file input. So here you will give the columns. So my first column is let me open one file. So my first column is I d get a type is in teacher second column is first earned. A good name debtor type is drink last underscore name data type a string gender, country it gender string country It country is also a string ages and indeed so lent So again, first name 20. Last name. 20 gender. Let's sit. And country, Let's it 20 each day. Okay, No running in Better review 100. It seemed that is another live it as a test. Okay, so connect this. Get file claims that the fear See input. Ah, connect this. Okay, main output of step. So now open this running in parallel. Okay, five. Name field. Give this. Finally select this option filing. Otherwise you will get an error if you do not select. Finally. Okay. Now table output. Ah, I have to create a new connection. There's no available connection in desert. My SQL DB is the name of connection. My SQL is to get a based name Next local host project is due Database name next route by Thursday. Okay. Yes. Database connection. Fine. Finish Get a best fields specify. Get the best field. Jack this option get fields now. SQL Victor Target table. Let's it imply his tree table employees underscored History is dick table. So we're going toe create this table Employees underscore history so I will click this SQL create. So this is my SQL query. Creating this for creating employees underscore is treatable. So if I execute execute button here, this will create imply underscored history Table in my my school database table has been created. Clothes? No. Okay, so now I will execute this transformation step to execute every prestes run button Run! Save this The name should be. Get fined, limbs get file Name saved This it has successfully executed. So now I will Kuwaiti imply underscored his treatable. Okay, so select start from Project Dark employees underscored history Good. This look records have been populated in the employee underscored is treatable. Now take the step metrics Si es three file input. Four files have been dragged. 11. 59 records have been written. Protest taken 11 63 Input table output 11. 59. So this is the table output metrics. Okay, table 11 59 records wrecked. Written 11. 59 output 11. 59. So this step metrics can be useful Re conciliation purpose of whatever you have wrecked. You have record in the table. So let me show you wanting more off. Click this if you have to change the data type here, let's say I have warned in teacher Toby number. So what you're going to do? Number click. OK, now you will double click this option database feel Get feels you have tow update this clear and exhibits. Okay, this will be updated now. SQL altar table Employ underscored history. Mortify I d toe this double distilled or if there d data type. Execute this. Okay, Now the data type has been mortified. Okay, Now execute this. Run this look fine. D transformation has finished. So now let me show you a few more things. Transformation in port so you can use the ah get sub folder names get a blames. No utility right to look right to log. So if I opened this input section so we have mostly covered everything. So whatever be using industry, we have covered everything, mostly in industry. We get the data in the foremost CS Reviled source provides us their time Deform off CSE Excel file. So now let's explore these two things. Okay, Get some folder name Get table limbs, connect to this men or put step connected. This double click this directory. Ah e get a sect. Just go. Pittis paced over here. Eg review rules. Okay, so these are directories, so get self folder names so right to log, get table names. So this will give me the table lamp so I will have toe create the connection My SQL they be my SQL next local host projective database name. So I will get to know all the tables that existing product database the route. Give the password here. Test database connection. OK, finish. OK all. OK, so include catalogue includes human include tables, views, procedures. Okay, synonyms. So it will display all these tables. Use procedures project existing project database. So right now, in project database, there's going to be only tables. Okay, we have only tables Now execute this run information saved this. It has been successfully executed. Now tables look food. So instead I can savor it. Any text file Also output text file. Okay. Table names may not put copy. Browse, fight. Okay, corn, think fine. Get these. Fine. Now execute death Run now I will get the table names in the text file. Okay? No, de that the integration This is defiled. Look. So these are the table steak Existing project that a bit. So now let me open this. So there's so many things. So mostly we have covered everything. So whatever we using real world we have covered everything. So in industry we normally get that attend the farm off either in the form off text file Excel file CS refiled These are the former step for using real industry. So hope you're industry the concept Thank you so much and have a great learning 12. Transformation Part-01: Hello, everyone In this lecture, we're going to study this transform section. Okay, so in this section, we are going toe first or the air constant. So let me just drag and drop here, Okay? Air constants. So this data great contains two dominator. So let me just first open the state a great for look, a student I d name Department age. Okay, this is dessert columns. Okay, so these are Ruth values actual values. So this is my dummy data in the data grid. Okay, Student I d name Department Age Office student physics marks. Okay. Alex has got 60 marks in physics. Okay. Biology, maths, chemistry. Okay, so tribute. Okay, Okay. Close. So this egg constant hilarity constant. Well, you OK? Let me just open it. So look, let's see. Since it is a university, that s so I'm going to add a university. OK, so name University column name? Okay. University types drink value. So this is the constant really university value of Cambridge. Okay, this value will remain constant throughout. Okay? No, joined this. Okay. At gone. Stern. Mm. Now right to log. Vaid utility Right to log Dragon Drop pair at Constant. Okay, so let me execute this. Okay, So a constant value has been added. OK, look, get feels. Look. University so valuable remained constant throughout Cambridge. So university IHS Cambridge. It will remain constant. So if you have to add the constant value so you will used at Constant So what can be the other constant value here we can use for student table? So university address. Okay. Ah, a constant address. Ah, string. Mm. England. Okay. Okay. So let me execute death. So it has been successfully executed. Reviewed data address England. Okay, it's not No, just remove this. Okay? Previewed Greater. So next we're going to study the sequence number. Okay. Ah, transform here in this section eg sequence. So this will air the sequence to each. Okay. Let me give you an example. Let me show do you a deer? OK. Do you want to spread the hop? Yes. No ex sequence. So each room will get a sequence number. So let me give you an example here at the sequins name of value sequence number. Okay. Is the name of value use db to get a sequence. So if you check this option, your database will generate a sequence. Okay, so if you use this youth counter toe calculator secrets, if you check this option spent a whole data integration tool will generator sequence. So the sequence will start from VAN will you OK, start from one. So every room will be incriminated by one. So what? So OK, now a sequence has been added. Each group will get a sequence execute this run review. Look. 123456756 Okay, each room will get a sequence. Okay, so no egg value feels changing sequence for a red hair. OK, Do you want to split up? Okay. Yes. Okay. So what is the concept off eg value field? So this will generate a sequence of particular value. So let me, Let's say if I give department here if I give the department column so it will generate a sequence for CS social Science art philosophy. Okay. For department C s, the sequence number will be one for electric. The sequence number will be one in tow for social science sequence. Number will be one then for our sequence number will be one for philosophy sequence. Number will be one. So when a new department guns, so it will again start from one. Okay, so let me give you an example. Ah, sequence. Feel sequence. So feel. Let's say I want to give the sequence for department department. Get real. Okay. Sorry. Delete, delete. I just want Department Department. Okay. Okay. No. So equal. Generate sequence for department. Okay. For CS, it will be one because it is coming only single time. Look for CS. It will be one for electrical. Look for electrical department. It will be one that again, electric department. It will be too. So it it is too. Okay, then. When the new department comes, let's social science here. So it will have value one OK for arts. It is a new department here. So von philosophy one lets it if I repeat the philosophy again, so it will be one in tow. So let's say if I repeat philosophy three times so it will be vine to entry. Okay, so egg value fields will be for particular column. Okay. Here we have given the department so I can also give multiple columns Now. Next, we're going to study calculators, Soquel collectible, perform certain calculations. So let's see what calculations this calculator can perform. Select me at her. Oh, yes. So let me remove the sequence number. Okay. Aired. She concerned this These two sequence. Okay, so the ball click on this calculator for double click this calculations so it can perform. It can act values it can subtract values for other than these operations it came get day from date. Hear from date month from date. Okay, I was so again removed basis from a string Returned the length off history. Okay, so it can perform various calculations. So look, so date a plus B yours date a minus did be so it can subtract. Oh, there it's OK so it can perform various calculation. So here I will first convert my string to upper case. Okay, uppercase off stringing for field day. A few days I've are name So string age. This a for you. Okay, uppercase off string. So here I will give the name uppercase for the Sistine. Aim full. Okay. Okay. So look, so let me just execute this look previewed at a per case. Alexe Bob, Join Mike bread George. So let's egg physics marks and biology marks. Okay, uh, physics bless biology. Uh, e Bless Be so. Physics is E figgis. Physics marks be filled with biology marks. OK, biology, various biology for six in biology marks this one. Okay, now execute this. Review physics plus biology. Okay, so let me just double click so I can add a plus B plus C but I cannot do a plus B plus C plus d. OK, so in orderto do a plus B plus C plus deep, I will have to first add a plus B plus c then So I will add the result off a plus B plus c in the Okay, so let me Sure do you So first, I will have toe egg a place deep Lasi. He's so vic here I will. It will be a plus B so since I cannot do a plus B plus C plus d. So here I will do a plus B plus c. Okay. No three marks. OK, head physics. Biology marks. Okay, just remove this part. Gamma stream marks. Okay, Matt Marks, Ok, No. So I'm adding these three marks. OK, so I cannot egg the fort marks, which is ah, biology, chemistry, math, physics marks here. Okay, so in order to do it. NEC daughter. Okay, here I will. E plus b So is this a plus B plus UK? The result off a plus B plus c is a here. Blessed be here three months. Three marks is de three months. Bless. Ah, physics marks. OK, no. So now I can get the results next door to lookie here. Mm Ah, Remove. So I don't want to show this three Marks column in the result. Okay, so I will write No. No. So I would have to show this mark this field new total. So for this I will write. Yes. Okay. Now execute this. It seems that is annettor so Ah, but it's later. Okay, here I have toe updated get feels clear in their dole. Okay, now, executor Death debtor type ISS So let me just specified character type in didja Intelligent indeed. Jeff Indeed. You okay? Ah. Okay. Okay. So no, execute this. Okay, fine. It has successfully executed. Actually, I haven't defined the data type. OK, so let me show you the result review. Look, total three marks. Tell collector they remove. No, remove? No. Okay, so I want to remove this. Yes, this is No remove? No. Okay, Okay. Now it will show here. Three marks will be gets a break. Execute this. Run a bit like this. Get field. Okay. No, run this. It will be fine. OK, so look Knecht. Orders to 65 to 99 to 92. Okay, so physics, biology, mats, chemistry All marks have been added. The neck total Is this OK? So with calculator, you can perform various other calculations. So let me just open this so you can explore the different calculations there. So if you have the gate column, you can extract day from date here from gate. OK? You can use different options. So in a string you can remove, it speaks. Okay. You can get the length off a string. You can do various different things. Okay. Hope you understood these concepts. Thank you so much and have a great learning. 13. Transformation Part-02: Hello, everyone. In this lecture, we're going to explore some more steps in the transform Sexual. Okay, we have already discussed this calculator. So now we're going to see the Koncak feels so basically this contractual issues, too, uh, combined different strings together. So let me give you an example here and here. Yes. Do you want to spread all PS double click, So step name. Concoct three. Target. Really? Name can get. Okay. Separated. So here. I'm going to provide the feels, so I want to can get in a two fields. So department and the name Name. First name in department. Okay, so in between these two fields, we're going to have a semi colon. So a glance Okay? Ah, yes. So name is Alex. Department. Is this department OK? So we're going to concoct in air these two fields and in between name and department, we're going to have a semi colon. So let me just execute this review data. Look, Alex, semi colon between Election department CS. Okay, name in department. In between days, there's a semi colon. So let's gender toe space in certain app now. OK? Execute this run review who Lechmere day Separate Okay. No executor, this We've run review space. No executor. This review. So there's a space here. Okay, so now Ah, next we're going to discuss another step. It is known as number range. So let me add there. So let me give you an example off a number range. So basically, number range provides to the facility toe, get a grace. Different feels Let's say I want to get a grace physics mark. So let me build a scenario here. Let's see if marks drinker than 80 it will be cut a grave, Tess. Excellent marks. OK, excellent. So if marks less than 80 in greater than 70 it will be Get the grazed us Good marks. Okay, so if marks greater than 70 So if Mark's greater than 60 and less than 70 equal be categorized as average months. Okay, so if marks less than 60 it will be categorized as bag mark. So I'm going toe implement this scenario using this step number range. Okay, so double click number range Marx category A foot feed mocks get degree. So I'm going toe important religious physics marks. OK, so I'm going to implement this scenario. Own physics marks so upper bound. Five less than five. So if I place value in the upper bound, it will be less than if I place value in the lower bunk. It will be greater than Okay, so now So here it will be in between. So so my first scenario is, if Mark's greater than 80 Okay, I will put value. Here it is. Okay. Greater than 80. It will be excellent marks. Excellent. If marks less donating in grated and 70 empty here. Less than 80. Okay. Greater than 70. Less than Nikki. Okay, if Mark's greater than 60 in less than 70 so 60 in less than 70. Ah, last is if marks less than 60. So I went put value here less than 60. So it will be categorized as a bag marks. Excellent. This will be categorized as a good marks. Good marks, every marks. Okay. And bad marks. Okay, so this is the number range, So marks get Agree. Physics Mark. So I have applied the number range on this column. Physics marks. OK, let me just execute this run. Look. Preview Look. Marks get agree. Average for 60 Every marks. 75 good marks. 80 90 is excellent marks. OK, 70 73 good marks. Ok, so this is the use off number range 14. Transformation Part-03: Hello. Everyone in this sector, we're going to explore some more transformation steps. So first off, all I'm going to look at this. The place in strength. Step. Okay, let me at this step here. The place in string. Okay. Yes. Double click. So? So this replace in string step will replace a particular string letter I've owned. Do department search CS replace with computer science. So ah, department cert Electrical engineering Electrical. Okay, Replace electrical engineering. Okay. Engine knitting. So? Ok, so department C is so C s will be replaced by computer science. Electrical will be replaced by electrical engineering. Okay, No, execute this, then. Review director. Look, computer vit. Computer science, not computer. Okay, No execute review. Computer science and electrical has been replaced with electrical engineering. So let's look at end of death function off replacing string. Ok, uh, just remove this. Okay? Replace with feed. So here. Name replace with field department out. Steam field, new department. So basically, I'm goingto have a another column, which will be new department. So in the new department, I'm going to have a string called name and before name and after name, I'm going to have a values off department. Okay. Okay. Oven. Moting, Hold word. Okay, Execute this preview data. So I'm going to have a separate column. New department. So this is name ing before name and after name. I'm going to have the values off department CS before election after election. Electrical before Bob Electrical after Bob. OK, so this is the second juice case off. Replace in strength. So let's remove this. Okay, men are put a step run. Okay. Review data. Fine. So now we are going to explore another step. Ah, which will be select values? Just egg here. Yes, for let me open this select value step. So here, I'm going to select those options which I want to be part of my results. Okay. Ah, select Removed metadata. So I'm going to discuss one by one. Selected get feels to select. So these all fields will be part off my results if I click Ok, this all feels will be part off My result. Okay. Now I want only few feels I got want hold to again free looking So I asked for my requirement. I've want toe filter the feels so I want student i d name department each I gone for physics? Biology, math marks, OK or chemistry? University Uppercase neck total can care marks category. Okay, so I want only these nine feels I will click. Ok, now I will execute it. We run, so I will have to update her. Get feels clear and aired on so nine feels okay. Run Review only nine Full Verne 23456789 So this is the first part off select in order. So in the remove section. So let's remove all these. No. So in the removed step, I will select only door seals which I don't want in my results. Okay, let's get fields to remove. So let's say I want to move this physics mouth. Biology marks mad chemistry. I want only these feels to be removed. Okay, four field. So other than these pills, all other fields will be part of my results. Okay, run Now let's look at another step Metadata. So what matter? That is so in matter Data. Let's see a name. Ah, let's see. Let me show you next total. So the debtor type off neck Total ISS number. I want to change the data type of this field net total. So what I'm going to do, I'm going toe type. Ah, in cages. I can also rename the this feel. Okay, let's say NEC total marks. So let's I want to rename it to this. Okay? In teacher, I can also more defied the lead so feels to alter the metadata. So in this section I can alter the data type I can rename a field. I can change the land. OK, so now Okay, look. So currently Neck total has a numeric getter type Look to 65.0. So now if I execute this transformation step so get that I will be changed. It will be in integer value, so I will not have this dark video at the end. So no, execute this So let me just a gator get feel clearing at all. Okay, execute this run. Fine. Look to 65 to 99. So basically we have changed the debtor type off this net total. Now feel name is also change neck total marks. So this is the select values. So sec field value. So let me just open it. Set field. Well, you feel name Replace by value from food. So let's it So now I want to include this. Ah, for eviction. Okay. I want to include all this values. OK, execute this. Run. Ah, Victor. Yes. Sexually value. So physics marks physics marks for six. Marc Newfield. Okay. Mm. Matt Marks. OK, so FedEx marks will be replaced by Matt Marks. So Okay. Okay. So physics marks will be replaced by Matt Marks. Look, physics marks and Matt marks will be equal, so physics marks will be replaced by mad months. Okay, if you want to replace any value, you can use SEC free value. Okay. So, no, via clear with sexual value. So now we're going to explore sec, feel valued toe a constant this in circles. So let me add here, these two steps sort Roy simple. It'll sort the field either in ascending or descending order. Okay. And here, yes, sec. Feel valued toe a constant. So let me open this step. So feel name replaced by value. So few name is Let's a university. So I want to replace this university field with the constant while you let's say Oxford. So currently the value of universities Cambridge, I want to replace it with Oxford. The constant value So all fields will get a constant value. Oxford. Okay, No, execute this. We run. Look, Oxford University. Oxford. Okay, No next sort truth. So this sort, oval, sort defeat I didn't Ascending or descending order. It's really simple. It's just like order by clause in SQL. Okay? Feel name. Ah, let's I want to sort agent. I want to sort age ascending. Yes. Vie for yes, I want to sorting, ascending or the age Ah, get feels OK. Clear and and or Okay, double iffy. So run. Okay, fine. Going to one ascending order now tended toe descending order. No, it will be in descending order each. So run review. Look 30 to 28. Okay, So the sister concept in the next lecture, we're going to export some more transformation steps, split field to rose plate, feel some or transformation steps. Thank you so much and have a great learning 15. Transformation Part-04: Hello, everyone In this lecture, we're going to explore some more transformation steps. Okay, so here we are going to see Ah, split freely Studios. So let me give you a scenario here, so I will end Joined this utility right to log. Okay, so get a great select me. Just explain this concept category. So let's say I have a data like this. E semicolon b femi Golan. See? Ah, the semi colon e semicolon f de it. I e Okay, I b you are x by Hank the So let's say I have a field called category and indicated refill . I have values like this. OK, so using split Phil to Ruth, my output will be like So let me show you how much output will be like eso Let me just explain this for let's say I have value. Alex salary literally 30,000 is the cistern manager. So this is the category field e a semicolon b semi colon. See? Okay, so now I'm going to apply this concept Split filled. Oh, so my output will be like this. Copy this based here and licks. So e b and C. So this rubble split like this so it will be the wagging three growth first rule will be assigned a category. A second row will be saying category B turtle will be assigned. Get the greasy. Okay, so don't. If so, let me just open this semi colon field to split. Get degree, get agree, Feel live. Get agree. Okay. Right to look. Get the OK. Now execute this. Look. Ex Vie nz. Look at this room. This route XVIII NZ. Okay, it tests Look X ray and then peak you are Speak your this. Okay. 1 27 weren't going to think this through. Look Ah e g a e g I Okay, so let me preview it. Review Quick launch. Look X Y z this Rule X ray z Okay. These Ruth Ah x rays He beat you are. Look at this on ABC. So the additional is assigned category a first, then be then see Likewise. Above rose will be a sign. Different Get degrees. Okay, so this is the concept off plate free. Do both 16. Transformation Part-05: So the next step we're going to discuss it is Blakefield. So basically, this split field step will divide my single field in different feels. Okay, so let me give you an example here, so just dragon drop here. Yes, Let's see. I have a column called name Alex. Sammy Golan. Here, Bob Semi Colon Voted my John Sina Mike. I see Brickley George King. So this is my name. So this really, really divide this name in different fields. So I'm going to have the first name where I'm going to have better. Alex, I'm going to have a last name where I'm going tohave here. Okay, so let me show it to you how we can do this. Double click, Frito Split name. Okay. Ah, Separated bike. Semi colon Newfield. So first name and I school name. Last name. Okay. Ah, type string String. Ok, so in the first name column, I'm going to have first name in the last name column. I'm going to have a last name, so Ah, this field days Semi colon separated. So name is semi colon. In between name, there's a semi colon. That is why I have mentioned a semicolon here Okay. No leave as they gave. Okay, look. Executed death. Let me just get Feels clear. And air door. Okay, Now execute this. Fine. Look. First name George King George King Bradley. Okay, so lets him off. This Blake feels no run. Okay, Run. Look. First name, last name fine string operations. So just drag and drop here. So string operation will perform operations related to string. So, like in stream here, I can give de first name so it can convert toe. Apparently lower case, Let's say uppercase. So all first name will be converted to uppercase spreading. You can define left or right reading or none. Bag lent. Here you can define a bad lend, innit? Gap. So in it, guy basically means the first character off a string will be capital. All other characters will be in small Lakers. Okay, No liver. Desert it. Okay, so our extreme. So if you want another field where you want all these operations, so in a separate file, you will have did it. Okay, So if you want to define a separately, you can give a name here, so you can also dream in case of space. You can also dream left, right or both. Okay, so remove special characters. So sometimes in a string Ah, we can have a special characters. So sometimes carriage return. So sometimes this carriage return character is not visible in a string. So you can also remove this carriage return character carriage return in line, feed horizontal tap space. So these special characters are sometimes not visible in a string, so you can ah remove it by using this function. Okay? No, just okay. Run. So, look, first name is in capital, so you can do various other things. String toe Kirk. So string toe Curtis Basically a sub string function like in s Trail. So let's see. It is not connected. That's the gifts. Let's say in Philly's first name, So zero So start corrected a zero back to do so. Zero first. No, I will get g for to know you Negro and Unique Row has sick. So basically, if you just drag and drop here, so do you want to Yes. Unique grew. So basically you Negro. This step is just like distinct function in SQL. So it will return is unique group So you need grew Let's see each each So if the incoming that is not sorted on, only consecutive will become severely commend. Starting the incoming that of it. Indy transformation. Okay, for the difference between unique Ruth and unique Rose has set is unique. Ruth will first ask you toe sort that out of areas when it comes to unique Rose headset went out, that integration tool will create a hash bucket. Okay. From hash bucket equal. Get the unique group. So now Okay, age so close this. So let me add another value here. Data grid. Tito. So it's Turkey to is duplicate. Really so unique. Roble removed it duplicate 40 it. Okay, so let's see a semicolon b. We weren't going to nine heart 90 it d 70 the deer Okay, now execute this. So first. Now look. Look. All rules will be unique. You Negro. Each of it blues get please Clearing the air Gold Uh unique it fine. Now execute this Look. All roads will be unique. 32 28 24 23 21. Okay, so you need growth. Headset is also the scene. So I have already explained the difference between unique rules and unique Rose had set for the basically Penta hold that integration tool will internally create the hash bucket. So from the hash bucket, it will get the unique value. So that's the main difference. Oh, yes. No need to explain. So as the functionality is the same so value mapper for Let me explain this concept, Value member. So let's say in a data set, I have he feels called. Ah, Jenga. Okay, so in the gender column, I have values like female zero male. Okay, female like this. Ah, mail fun and zero. Okay. No, I have data in different form. It likes email. F E Emily for female zero for male one. Okay, female F indifferent. Former. So gender column is in different form it, so I have to make it stand. Arise. So I will use this value member. So how liking do it value member feel to use, um Dingers Cola. Okay. No sourcing. Target. Female. Ah, if, uh, zero female. One male. Okay, I m I am if f okay. No, the gender column is in the form of em in. If so, let me just execute this. Look. Get feels clearing at all. Okay, Now execute Jenga. Look, am I am F mail value member target for the name. Gender value. Over. Right. Okay, over. Right for now. Executive this Look. Junior value male with a male em made and okay, get is I didn't know value here. Okay, Now execute this. So I will have a separate value where I will have diesel m am fame. So the formative am or Fok male Or if so, this is the concept of Value Member. So hope you're industrial these concepts, Thank you so much and have a great landing. 17. Transformation Part-06: Hello, everyone. In this lecture, we're going to explore the utility section in printout data integration toe. OK, so first of all, we're going to explore this utility run a PSA such command. So if you have shell script and you want to execute it, you can do it using this utility. So here you can provide the server name, several port user name and password. Private key proxy settings. Okay. Here in the setting tab, you have the commands in this section. You will provide the shell script. Okay, response field names. So this is the output field. So in case off any other So this is the editor feel OK? So you can provide You can execute the shell script using this, uh ssh. Kamar your ability. So just remove this. So now I'm going to show you if feel values knowledge ability. Okay, let me just drag and drop in, so connect this. So let me just open this data. Great. So, in the data section, So let me just physics mark zero. Uh, no. Okay, so So let's say if you have a data, an injured data, there are some null values. So So you want to replace it with any value. So what, you're going to do a double take? Ah select feels lik dis option get feels so Physics marks Okay, replaced by value. So the place null value by minus one. Okay, so let me just double click this So this field has no values in it, OK? Ah, this will be replaced by minus on. Just execute this. No look, no value. This will be replaced short. Very. Please Look, it has been replaced. Okay? No, let me just double click this dicta for Let's say if you have no values there Indeed. Biology here in the chemistry field in the mad mark. So now what? You're going to do the bulk click uncheck this option just Ah no uncheck this option replaced by value minus one. So now wherever there is in null value, it will be replaced by minus on. So no, it is not freely specific. Ok, no. Execute this run Look so not value has been replaced by minus one 18. Transformation Part-07: So here in this utility section, we're going to explore some more utilities. So now we're going to look at this utility now? If so, so not only futile a table. Replace value with enough. So let me explain this concept. First. I'm going to join this. Let this men output of step for DoubleClick. Get, please. Okay. So, Victor Ah, so this is my data. So in my data, I have a field called department. So in the department field, I want to replace this particular feet CS. So I want to replace it with knowledge value. So how I can do it? Let me show it to you. The belt click. So I want department here. Okay. Delete all other feels so value toe turn. Tono CS. Okay, let me just execute this. It will replace this yes department retinal value. Look, See, his department has been replaced with Not very. So let's department. Mm. Electrical. So now see, it's an electrical department will be replaced with knowledge. Really? Okay, execute this. Look. Electrical. Okay, So this is did utility called now lift. Just remove this. Ah, Run. Fine. So now we're going to look at this clone room. So this clone row utility will make a duplicate copy off here. Okay, let me give you an example there. Yes, Double click. So how many duplicate rules you want? Like I want to duplicate truth. Okay, execute this. Look, this is the original Rove. It is present in my data. It has made two copies off. Disparage inal. First copy, second copy. So this is the original row. First copy. Second copy. Okay, so let me just revered preview. Ah, blonde row. Quit launch. Sure. Look. So this is the original, though It has made two copies off the original first and checking Original Low first and second. Okay, so let's change it. Toe blown row. Let's change it to one. So now it will make a single copy off a roof. Execute, so review. Quick note. Look, this is the original, though it has made a single copy off the original. Okay, so this is the clonal utility. So what about district ality delay row? So just dragon dropper delayed. Oh, so basically, dis utility will give her delay in the execution, so let's I want tohave a delay off 12 2nd Okay, Okay. So after the execution off this step. There's going to be a delay off 12 seconds. Then this step is going to be executed. So let me give you a use case off this utility delayed. Oh, so let's suppose if you have to table stable A and table B. So what you've on, Kate is So you've born. You don't want toe insert values in table be until all the rules are in circuiting table E . So you're going to use this delay row utility? No, Delerue, 12 seconds. So now execute it. Look, look, this is stopping. Okay, Look, dying 18 seconds. Look, So this is dealing the process, okay? Because I have said the timer. 12 seconds. Step metrics de Lareau wreck Four records written three. Okay, he said, uh, three records written entry. Look, this delay row utilities dealing the process. Okay, so now ceremony this. Stop the running consummation. Okay, so this is the concept off delay row utility. So the most commonly used utilities are now I live in a field values now, so these two utilities are most commonly used, so table compares, so if you weren't compared toa tables and give back list of differences right to log. We have already discussed zip file, so file aims will be extracted from incoming stream. So? So it will compress the define looking this zip file utility. Okay, so mostly we have covered everything. So most commonly used utilities are now I live in a field values now, so let me just remove this. So hope you understood the concept. Thank you so much and have a great learning for Let me just join this step. Okay? Executed death. So I don't always your dialogue under on Take this, Odette. It should not come again and again. Run now It will not come again. Okay, so look, preview data. 19. Flow Step Part-01: Hello, everyone In this lecture, we're going to explore this slow step. Okay, so we're done with utility step. Now we're going to explore distant. So inside this lost if we have a step called Upend Stream So now we're going to look at this step eso this apparent stream step a parent's two streams. Okay, so let me give you an example here. Let's say I have in port and the door input. So concave get very transform, John som section can get feel Okay May know port of step here with it Calculator So this is my first dream. This is the second stream. Okay, Here I will use this flow a pen difference team So this apparent stream will appear in different scenes. Two streams. So no more than two streams are allowed Only two streams. Vic here, all pride file name. Let's say 100. Okay, get feed. Okay. Review. So de their 100 records. Okay, so OK, so death CSE five in Porto Broth 1000 records. So in total, I'm goingto have 1100 records. Okay, review We would get feels okay. Review Dealer 1000 records. Okay, Okay. No, uh, concoct field. Let's it are get Feel less full. Name first name, Last name. Last name String. Ah, string. Drink. Okay, this is fine. Calculator. So let's say I want to convert string a tow. Uppercase new filled days Get picked in. Mm. Ah, First name. Okay. First name is definitely a okay now. Okay, let me just execute this. Always know. Okay, Victor, let me first execute this step. Okay? Review. Preview. Quick gland, Look. So the their total 100 records Okay, No close. So I'm going toe have and at this team. So this dream will taken input from 1000 records. Fine. Get feels ok. Review. Okay. These are 1000 records I'm going to use. Ah, this apparent stream. So basically a prince team will combine two strings together. Both streams should have same number of columns. So combined this. Okay, fine. Combined. This may not put us look, but problem here. Is this feel a this dream And this stream this stream, both streams don't have same number of columns. So let me you select statement here, so I have to use select statement care. So here I have to use select statement. Mm. Tofail goody Bluth Transform. Select. Yes. The ball click. Ah, get ready to select full name. So I don't want this full name and capital. I only want all these different idea. First name, last name, gender, Country it Salad e Okay. No, let me review enabled. No. Okay, now preview head Hope CSE select value is the head delis, see a three file input. Now both streams have same number of columns executed this Fine. Look, if I have to take this review Greg Lawnd Look, I d First name, last name Gender, Country eight. Salary board feels have same number of columns. So, Vic here I will use this flow. Uh, just dummy. Then we do nothing. So this step will do nothing. Look, this is for testing purposes. So now we're just ah, doing a testing here, okay? No, this is my first dream. This is my second team. Okay, so I'm combining two streams together, so let me just execute this. Eso looks Look at the step metrics Select value, So look 100 records. CSD, csd file s 100 records this year. See file input to has 1000 records for total. I'm goingto have here 1100 records. 1100 records have been drinking this step doesn't do anything. This is just for testing purposes. Okay, look, So, uh, you cannot a paying more than two streams? Okay, so if you have three streams, you will use another a bank stream. Okay, you will combine 1st 2 streams. Then you will combine other stream okay with another pence team, so 20. Flow Step Part-02: Hello, everyone. In this lecture, we're going to look at another flow step blogged this step until steps finish. OK, so let me give you an example off the step. Ah, new transformation. So here. We're going to look a generator tomorrow. Okay. So name I d name salad E. Okay, type string. I dont jer, this is engaging. Okay, So limited. 100,000. Very idea. 123 name and lex Celery. Let's see. $10. Okay. Just duplicated this. The bullets late. Let's say running for Bob during t 1000. So 200. So let me just give you a scenario off this step bookie generate Ruth, so I'll put table output. Okay. And table output. Mm. Flow. Don't me do nothing May know little step. So basically, the scenario is I don't want to insert values in this particular table until all rules are inserted here in this first table. OK, so I want to first populate this table completely. Then I want to insert values in the stable. So what I'm going to do, I'm going to use this step, blogged the step until step finished, so just drag and drop here. Yes. Double click step the stable output. So I did. All records are inserted in this table. Table output. No need to insert values in this table. OK, once all the loser in circuit here in this particular table So then the values are going to be inserted here in this table. OK, so no, just configure this visit My SQL Ah, my It's good. Next local host project Next route. Dense database connection. Fine. Finish. So stargate table. So my target table is simple. Okay. Gagged feels okay. Uh, SQL create dis table in the database. Okay. No table has been created. Okay. Ah, same table, simple table. Same applicable. Okay, get food. So, 1st April, insert all values in this stable output. Once the values are inserted, then this step is going to be executed. Okay? Don't know. Execute this. Run. Run. Okay. Save simple. Look, this is executing. This step is not finished yet. Okay, so this step is blocked. No record is inserted in the stable. Okay, Until this step is finished, look, Able output to no record is inserted. Okay? Nothing happening. So this step is running and this step Look, once this step is completed, then it has inserted values in this table. OK? 21. Flow Step Part-03: Hello, everyone In this lecture, we're going to explore some more steps here in the flow section. Okay, so first off, all we're going to look at this step filter, Ruth. So basically, this step is just like a fence treatment. So let me just give you an example off this step so that a great contains They're Toki looked student i d name department. So this is my doctor. So let me just connected with this step. Ah, Okay. Ed here. Don't me do nothing. So double click step named Filter Roath. True. Okay, Victor, this result is true. Come here. If result is false, come here. Okay. Result is falls now specified the if else statements or the ball clay care. So these are my conditions. So you can also specify multiple conditions field. So let's say age Well, click. Ah, here you can have different options. Less than greater than equal to less than equal toe is no, like true. Okay, you're going tohave different operators here. Okay, so let's say each greater than value. So if you want to compare two different feels, you have to specify the field there. Okay, so now I'm going toe compare value here. I will mention the value value is let's say 28 flying. So OK, so this is single condition. So let me just execute this So I'm going tohave. OK, do nothing. So if the value is true, I'm goingto have How many rules? Let's check it out. So I'm going to have only to Ruth. So the condition is each greater than continued. So I'm going tohave review quick loans. Look aged 20 greater than 2030 to 32. Okay, only to Ruth. So here I will have so many rules as Greek loans here It's less than continued. Okay, Already quit less than equal to 20. Ok, 28 24 23 22 21. So this is the false condition. This is the true condition. So, like you can specify multiple conditions. So look h equals toe. Let's say 24 uh, age equals 24 Victor. So it lets age equal to 21 in department tickled to see us. So I'm going to fetch this particular so eight equal to 21. Uh, another condition. This department value CS. Okay. Now I am going to have a single condition here. So this is my condition eight equal to 20. Even department tickled to see us. So let me just execute this look for this particular conditions. A preview. Quick launch. Look, I will have only a single room. Where Age equal to current. Even in department. It'll it'll see us. No, it greater than 21. Okay. And field name like value. Ah, OK. Value e. So name Star said collectivity. So this is my condition. So I'm going to have to conditions each greater than 21 in name. Contains correct area at the start. Okay, e percent. This is my condition. So remove this condition and department because gets here to remove this condition. I will. Right. Click delete condition. Ok, now I'm going to have only two conditions. So let's just execute this look. This step will have only this age greater than 21. Name started directory. Okay. Is this review Ah mm. Name starts it directory. So what about this? Great cloned? So there is no other over there. Dis condition satisfies. Look, age 22 which is greater than 21 but name doesn't start with directory. Okay, So only one particular very each greater than 21. Unnamed Starfleet directory. So you can specify multiple conditions here. Okay, bless. So this is the concept off filter, Ruth. So now I'm going to show you the concept off switch kiss. Data grid combine. Okay, so which case? Field booth, which So I very department um do it, dummy. Wills to me one do to the UK Victor Ah, feeling department fee if starg etc. Don't me do nothing, okay? Electrical. Ah, CS electrical and art. So let me show you. So if department equals to see us then Victor, the lead department it goes to C is then go here. Target value for C s. Okay, if department look, if department equals two electrical engineering, then go here. Electrical. Okay, go here. If department equals toe arts, then go here. So there's going to be another dummy. So this is the default. So all other values will go here in the in this part. Default target step. Okay, so this is the data, so other than CS electrical an art. Uh, all other values will go here in the dummy. Nothing. Six Okay. In the default. So this is the concept of switch kiss always should in North executor death. Look, look at this step. Where department It goes to see us. Quick, launch CS. So this is preview. Great blonde. This is Department of Girls to electrical. Okay, so this is different. Preview. Quick launch. Okay, this is different. Social science and philosophy will go here. Okay, so we're done with these two steps. So now we're going to explore the PSA prioritize dream. So basically, paradise stream will prioritize my ah, different streams. Okay, Let's instead let me just drag and drop here. Prioritized. So this is my first dream. This is the second stream. Okay. Ah, main or put off step. Okay, so this paradise stream will prayer guys different streams. Okay, Step name Select where you will have high priority. CS file in Porto Valley Will have low priority. Okay. No, this left. This will have high party discipline left. The second oxen will have less priority, so execute. Okay, so now a bark We're going to discuss this stream about it's a bark. Then aboard the transformation, a border transformation, this will about whole transformation. So basically the use case off this. If so, let me give you a use case of this. A bart step. So let's say if you're running an ideal off a bank, So any bank, you have a bank amount, Let's say injured ETA if the bank amount comes less than zero, meaning bank among values coming in negative values. So which is not possible? Okay, your balance amount cannot be less than zero. So you can use this a bart steptoe apart, the whole transformation. You God warned better too. Move forward with the incorrect. Really? Okay. So normally, we used this about step in the error handling. Also be you think with the first arose step. Okay, so now let me just give you a brief example off it. Let's see, uh, combine it. The Mr Very Dummy step. Uh, just don't misstep come by. Okay, so a bar, the running consummation a bart special. So zero means that it will read the first row, and it will aboard the whole transformation. So if I write five, it will greet five growth, and then it will abort the transformation. So let's say it will read the first. Okay. So you can also use the steps of Barton log as another stop input processing. Okay, dealer. Different options with about. So now we are going to use deception. First option. Okay, So once the transformation Regency, it will aboard the transformation. So it now execute this run. Look. Step metrics. Ah, a Bart von Direct. So as I said, threshold of zero equal. Read Evandro and den. It will aboard the whole process. Okay, so the most commonly used flow steps are a pen stream aboard Figaro's such gays. Okay, The so either These are most commonly used floor steps. Also this block step until steps in it. OK, so this blocking Steffy Hardly. You've really used So 99% of the time view the steps. So hope you understood the concept. Thank you so much and have a great landing. 22. Stream Lookup: Hello, everyone. In this lecture, we're going toe. Understand the concept off stream. Look up in Panta Ho Data integration tool. So basically using this step, I'm going to look up values coming from an address stream in the transformation. OK, so I'm going to just drag and drop this chap. So this is my stream. Look up. Step. This is to de targets in district A Great. I have a data employee. I d name department, die goat number. So I'm going to look up this die court in the other stream, So here, I'm going to have an address stream table, input, table input. So in my database, I have a K berry called Project Dark Country. So in this stable, I have country court and country name. So I'm going to look up the country court Indy country table here in the Okay, So, uh, country Good. I will get it from this from air. Okay. And I'm going to look up this dial code here in the country table. You think this step stream look up. So let's join it. Stream. Look up. Giant death. Okay, dummy, do nothing during this. Okay, so now I'm going to configure this table in port connection. I have to make a new connection. Here, Give me my SQL Next. Local host project is detector base. Next the route. Okay, finish. Get a school. Select statement. Okay, but countries do table. Ok, ok. Okay. Ah, Review. This is D that averages present in the country table country name and code. So I'm going to look up this country court there in the country table. Okay? No, the bulk click. So look up, step. So I'm going to have a cabling for this. Okay, this, uh, feel Ah, Victor, get look up. Fills field here. Di gold for dial Gorgeous. Present here in the data grid this day record. So I'm going to look up this die good here in the brought in the project our country cable . So look up, phase good. This gorgeous present here in the country cable this court. Okay, so? So I'm going to get against discord. I'm going to get the country name, okay? No. Okay, fine. Don't me do nothing. Now execute this. It's run. Look. Ah, look. Country name. Okay, against this style. Good. Let's say I have a imply i e d this and licks. Ah, okay. Department lets the sales So let's say 5000 is Did I call? So this dial code is not present Indeed. In the country table. Ok, now here. Let this. Okay, now here in this step default value. If value doesn't match, it will give the default. Really? Let's say doesn't match. I'm going to get this default value doesn't match. Okay, so this value So this value. So this really doesn't match this 5000 so I will get default. Value doesn't match. Okay, So in case if I do not provide any value, I will get another value. Now execute this so I will get in Al. Really? Here look preview no value. So I can also do the same step using much company. So the difference between the merge component and stream Look up. It's basically in lookups. There are two types of joint inner joint and left outer join. Meanwhile, in the merge joint component, there are inner join left outer join right outer joint and full outer doing in stream. Look up! It only performs left out of doing okay stream look up Only performs left outer down Also in stream. Look up! The whole details first. So in stream Look up the whole database. First corporate here in the cash in memory. So monster details cop it then it is going toe perform the look up step. Okay, so stream look up is better when it comes to small Get a sec. So because in case of a large data set huge volume off that I will be corporate in the cache memory and get could hinder the performance. Okay, so it is not advisable to ustream Look up when you have the large data set So merge joined Component is better when you have lied Data set. Okay. It is good for small data sick So I hope you understood the concept off stream Look up Thank you so much and have a great learning 23. Database Lookup: Hello, everyone. In this lecture, we're going to understand the concept off database. Look up. So in that abyss, look up. We look up values in a database. Using field value varies when it comes to stream. Look up. We look up values coming from an industry. Okay, so that's the main difference between their two best look up and stream. Look up. Also in stream. Look up! That is first copied in the cache memory. Then the look up is performed. So let me just drag and drop here. Get the best look up control. See? So I'm going to use this data. Great. Okay, join this step. Okay, fine. So this is my data set, employee I d name department that I could number, so get a base. Look up. Okay, connection is this Look up table s country. So this is my look up table. Okay. This is my look up, turban. So get look up. Feels fine. Get feels so here. I will use this the keys to look up to the value table field. So good. So here, Field one di e code. So Dai court. So this die gorgeous present in my diff data grid. Okay, this is the diet. Good. Okay, decision. I'm going to match this tree with discord. Feet. So this core dizzy able feed this good? Okay, equals Oh, so I can use other operators. So here I will use equal toe. So if you have to match multiple fields, you can use this little field to option. Okay, So the value I'm going to return here is country name, so I'm going to match scored with di court, so I'm going to return this really country name. Okay, so so OK, now, you dummy. So connect this dummy. Men are put off. So now execute this look. So this is the output, so I can preview it. Look, this is the null value, so ah, default is not okay. Doesn't match, so you can specify D default value. So executed this reveal Doesn't matter. Okay, so this is the database. Look up in database. Look up. You look values from the database rather than looking up values from the other streams. Okay, So hope you're industrial the concept. Thank you so much and have a great landing 24. Database Join: Hello, everyone In this lecture, we're going to understand the concept off database during so basically database join executes T database query using stream value as a perimeter. So let me give you the example off district of restaurants. So first of all, I'm going toe drag this step here in the panel. Okay. Here in the input section. CSP. Okay, dummy. Ah, Okay. Now connect these steps. Okay, fine, men, I'll put off step go like this. Men are blockbuster. Book it. No. Here. I'm going to give the part. Ah, floor. So I will get the data from the employee table. OK, so this is going to be my directory employees. Dorsey easy. So let me just open this side for you. So in the employee dark. Seriously? Fine. I have these values employing number Name, job manager. I d hire date, celery dip. Department number. Okay. During tive. Ah, get fields. So let me just review it. Okay. So this is my doctor. Okay? No. Here in the ghetto. Best join. So let me show. So this is my department table, so I'm going to join department department number with employees department number. Okay. This department so I'm going to join this department number. Wait. This department number. Okay? Using this data base joint step. Okay, so now here I am going to provide the SQL Kuwaiti Select Start from department. Very depth number equal to question mark here. I will provide the perimeter depth number. So this is D in creativity data type. Okay, so this is the perimeter off this step. Input step. Okay, this is the input step perimeter. So department number equals two question marks. So question mark, is this Okay? This department, so in this, Say you can do a database train. Okay, Now execute this step. Look, it has successfully executed, so now review. Look, uh, review. Great cloned look. So look, this is so so this is how you doing? So one more thing. So you can also do the outer joint, So check this so you can also perform the left right outer join or full auto joy. So now execute this review. Look, now I'm going to have no value in the data, since all values are matching, so there's no none really? Okay, in case there's a miss matter. In that case, I'm goingto have enough value so I can have no value in the left side. Goran. Right side 25. Data Validation: Hello, everyone. In this lecture, you're going to do data validation in Penta ho data integration tool. So, for this, I'm going to use this data validator. So this is my data. Great. So here I have the diminutive. So let's say Distort is coming from this whole system. Student I d name department. Each office student marks total marks and gender. Okay, so I'm going toe, apply these rules on this particular data OK, on this data. So my first rule is department value cannot be known. So let me show you my data. So the sister department said this department value cannot be no. So first, I'm going to combine this with this turtle validator step. Okay, so here I will use dummy Step Gummi. Okay, uh, men output off step. So this will be my men are part of step. This will be added handling. Okay, so So if my data doesn't set aside the truth, my data will come here. If data satisfies these rules, my data will come here. Okay, so now I'm going toe define these rules one by one. So first I'm going to define this rule. Department value cannot be known. Open this new validation Give the unique name department underscored. Now. Uh okay, so the Bartman name of the field. So I'm going to apply this particle ruler department field. No, no allowed. Take s So I'm not going toe have enough value in this department, so I'm going to have this. Okay, So what is the error? Enter description. Enter. Good. No. Let your other goodies. No. 01 error description is department value Cannot be no. Okay. Not allowed. Uncheck this option only null value. Galo. Okay, fine. Okay, fine. Rest is okay. So my first rule has been applied. Okay, so let's say marks cannot be greater than 100 or less than zero. So I'm like vice. I'm going to define another rule. New marks value. Physics marks. Are you okay? So name of the field. Physics marks enter good marks. 01 So physics marks. Physics mocks cannot be greater then 100 or less than zeros. Total marks is 100 so it cannot be greater than 100. Also, it cannot be less than zero. So here I am going to specify deep range. Maximum value is 100. Minimum value is zero. OK, so this is my second rule Mark's value. Physics Marksville. Okay, No. So these two rules have been defined. So let me just run this. So, first off, all I'm going to just change this so department Well, you cannot be Not So let's change it to non value. So now So let's change physics marks toe more than 100 Van Zito today. So this values null and this is more than 100. Okay, No executor. Death run! Look at it. It seemed it is annettor vit. Ah, debtor type antigens. So here I have to define the data type department. Debtor type is string. Okay, Now execute this. Fine. Look here. I will have the this here in this dummy field industry me section I'm going tohave the problematic data. So this is the problematic data department values now in Mark's greater than 100. So this data will north move forward in the process. Okay, this is going to stop here. So whenever the it'll process is completed, we're going to check this turtle and see what is the problem in the data. Ok, so once the data is checked, we can easily tell the source system that there's a problem in the data. Okay. We can easily, uh, check an informed the four system. That is a problem in the data department. Values null or marks values greater than 100. Okay, So, like rice, you can define more rules, get a validator new validation. So my next rule is total marks cannot be greater or less than hundreds of total marks is 100. So it cannot be greater than or less than 100 New validation. Dotel marks value. Okay, hotel marks. Ah, maximum value. Maximum value 100. Minimum value 100. Okay, so it cannot be more or less than 100. Okay. Data type is in. Teacher. Fine. So this is my turtle work about it. Each cannot be greater than 30. So let's define this rule. New age rule. Okay. Age. Ah, get a type not allowed. Not it cannot be. No. Second rule is cannot be greater than cannot be greater than 30. Some maximum limit is 30. So it cannot be 30. So in the same maximum string gland string minimum string glands of maximum value. 30. Okay, fine. So this is my fourth rule. So now I have defined all these four rules. Um, each cannot be to Let's say hair ages 32. So this is my problematic area. OK, so I will get all problem. I take values here in this gummy section. OK, so this section will not move forward in the detail process. So it seems dated Another Marx. Okay, in major totaling teacher, fine each. Let me define the data type now it will be fine in cager. No Fine. So this time it will contain the problematic better destructor. Will Norc go forward in detail process not value Age is greater than 30 Marx greater than 100. Okay, so the the rules state we have defined in our it'll process So now cancel this so hope you're industry the concept Thank you so much and have a great land. 26. Consume RESTAPI: Hello, everyone In this particular lecture, we're going to learn how to consume arrests are with in Penta Ho. So first of all, I will select a step which I'm going to use to consume arrested with, So I will select the rest kind step So first, let me just explain the difference between extractive declined s treaty peoples and arrest Frank Step So let me just drag and drop here. So you used the STP client step Whenever you have toe, get the data from the A p I from the Web service you will use the STP client mattered step . So what is the difference between this step in this particular step? Rest kind step The rest blind step gives you different estate. IPI matter adoptions. Like, if you want to get the data from the a p a, you will select the get mattered. So if you want, if you want to create a record, you will use the post matter. If you want to update a record, you will use d Put matter here. If you want to delete any record you used, the delete mattered so it gives you different restrictive we methods. Okay, options different test, it'd be options. So another important thing is, if you are required to provide the SSL security, then you will use the rest. Client mattered. Okay. Restaurant step. So when it comes to extra tp client mattered, no extra security is required. You're not required to provide the SSL security. Okay, so But when you're going to use the rest client option If you are required to provide the assesses security, you can use this mattered. Okay, Here you can provide the SSL security. Fine. OK? No. Okay. What is this matter? So if you are using the specifically both mattered toe create the record, you will use this particular step. You can use this particular step Now let me just use the rest. Client matter toe consume arrest service. So let me just deluded if no so first step, I will use degenerate rule. Step double click here. I'm going to provide the u N. Name you Erin. Ah di pissed Drink. So which ap I am going to consume. So I'm going toe consume this particular a p a. This is the Ural. Okay, this is the compute. You are loved this a p a. Okay, so if you weren't? No, I don't want this. Select me. Just get a single employee data. So I want only a single. Okay, so if you want a single implied that I You will use this epi I you are in. So if you want some other employees so you can use this urine So I'm going to use this particular you are. So you are in value. If you are released this so limit I will select only one value. Click So preview those click Ok, now I'm going toe connect the step with this rest clients step So connect disturbed with this step Okay you are No I'm going toe passe You are really Ural feel name this you are in So look this you are okay. Click Ok, you are in okay board e select the body you earl border feel application type gets on result. I'm goingto get the value in this particular feet result. Okay, fine name No fine click. Ok, next. What I'm going to do, I'm going to use another step. Jason input. He used the Jason input men select Okay. Sources from previous steps I'm going to get well is from the previous step Which value? Result of value. Fine Feels here. I'm going toe select Different. Really? So in my debt, I have I d imply name salary. Let me select all these fields. I d fields. So first values I d. So first, let me just select all fields. So second contributors, employees and the school name Certain third attribute is implying the score salary 4 to 1 is then last but not least pro fi underscore image. Okay, Next time what I'm going to do, I'm going to select this particular so I d. So how I'm goingto get this particular value So first dollar sign Dollar signs represent the route Dark, dark I d dollar dark, dark imply name imply salary imply age So let me just Okay, fine Feels fine. Click. Ok, so this is how you get digest on import Next. What I'm going to do I'm going toe used this select values so select release. So now using this particular step I'm going to select really so visually I want to select I don't want to select these fields. I only want to select idee imply name salary, employee age Not desperate Clearfield. Fine rename. I didn't find I don't want to rename this click. OK, next. What I'm going to do, I'm going to use the cable. Uh, out. But so I'm going to insert doctor in my table, which is die. Start, Jason, underscore out. Let me just first created. So table has been created. Let me just select right now. There is no order tiene it. So I'm going to select the Connect this with this step main output of step here. I'm going to provide the connection details, so I have to create finish. Okay, target table table Amis. Let me just provide the cable aim here, Jason and score output is the table name fields. I have to specify the fields here. Get feels. Okay, I d Fine click. OK, now, let me just revealed this good launch. Uh, Victor, you are so here. I have to select the get mattered. Okay, Find your away. Okay, fine. Jason. Application type is Jason. Result free. Les mis result. So in this particular field, I will get deep. I will get the data from the a p a from the capsule with So this is develops a With from there. I'm going to get their data. So I'm consuming this particular that service by providing the you are now. Let me just say this first I have to save this Can zoom rest Now execute this. So it has been executed successfully. Now look, look at my target table. Look, it has inserted. They're trying my target table in my Jason. And that's good Out portable. So this is how you consume the that service using this particular step? Rest, climb step so you can use some other matters like you can use The post mattered if you want to create a record, If you want to update a record, you will used the port matter deleting by dilating a record. If you want to delete a record, you will used to dilate matter. OK, so s so if you are required to provide the assesses security, you can go here in the Cecil tap and here you can provide the file trust or find. Okay. You will get this particular find from the a. P a. Owner ap A owner will provide you d trust. Fine. OK, No. Okay. So hope you understood the concept. Thank you so much and have a great learning 27. SOAP API: Hello, everyone in this particular lecture, we're going to look at the example off consuming soaps or with in print Tahoe. So, first of all, I'm going to select developed service. So my Web service will be calculators Web service. So this will be the soap service, which I'm going to consume. So this is my web service. So this web service performs four operations a divide, multiply, subtract. So in order to get the Ural, I will click service description. So this is the U. N, which I'm going to provide in orderto consume this soup service. So, first of all, what I'm going to do, I'm going to go in the look up section here. I will select the web service. Look up. So for my first step will be generate root. So here I am goingto provide value. So Valley one and value to in value. One very one type is in teacher type. Value to type is in teacher. Okay, value. Let's it 10. 15 is devalue. Preview one. Now reveal Dan and 15 click. Ok, so next I'm going to connect this with this web service. Look up at the end. I'm going to use the right too long step. Connect this step with right to log. Step Fine. Get fields I'm going to get Okay. First of all, let me just configure the step here. I'm going to provide the Ural. This particular you are supplied this particular urine here. Then you have to click the Lord Option Lord. Then you have to select the operation. Which operation you want to perform? Let's say first time Want to perform aired operation. So I will go in the in section. Here. Here I will provide value one input first import really in value to a second import value book. A Web service name Rep. Service name is NT in. Bees develop service. Name type in teacher type. Okay, These are indeed your type. Add result gets it. Name is at result Web service. Name is this Web service type isn't Egypt? So type is in teacher here. I'm selecting the integer type and return. I'm going to get the indeed er type value. OK, so I'm going to pass to really? So I'm passing to values. And in return, uh, I'm going to get the result. I'm going to get the result. Okay. Clear and all. I'm going to get the air Desert irrigation result. Now Save this and execute this. So the result is 25 10 plus 15 25. Now let's change the operation. So let's change it to, let's say, First Lord, then let's change it to alert, say, multiply in value one value toe. Okay, get feel. Multiply Web service line. Really one value toe right to log a result for safe side clearing a goal find. Save this. No, I'm going to get the result. I'm going to get the multiplication result, which is 1 50 Review 1 50 Very 1 10 Value to 15. So this is how you consume the soaps and with 28. SCD Type01 Implementation: and everyone in this particular lecture. We're going to implement a civic type one in print. Tahoe. So when we talk about s a G type one, we do not maintain history when there is a change. So, in order to implement acidic type one, first of all, I'm going to create a table in my database where I'm going toe insert values. OK, so first of all, I've cleared this table Customer underscored em table. So in this particular table, I have a customer, I d customer name and state. So state is the field is the is the column That can be changed. Now, let me first execute this. So right now I don't have any daytime my table. So customer underscored name table. So it is already there. It is already there in my database. Now what I'm going to do, I'm going to use the debt a great step here. So I will pass. Um, de mediator in this particular dead hundreds step here. I'm going to create three fields. So customer I d customer name and state. So I will pass the starter here in the delta. Great step. So first feel will be the customer. I d underscore i d. Customer name is the second field. Underscore name turd for years. Stick debt. Islets of one Division one once a visitor to 1003 Customer name. Let's say Allen, Bob, George Ellen George State, New York, Chicago in Italy Review Destructor Close. Okay, so second step, which I'm going to use Give the name here. That's a source tractor. So let's above. District is coming from source. So let's say this is my source data. No next step will be the insert flesh of debt step. So connection type. I'm going to create a new connection. Click next local host. So database name is dice. So the city database Name where I'm going toe where I'm storing my table customer and just called them table. So now the next youth and name finish Next, I'm going to deploy the table limit Growth table name is just a matter underscored Game. Okay. Do not perform any. Yes, I want to perform in you update. So my look up, Fearless customer. I d equal toe equal toe customer. Ready. So the city customer ready which is coming from this particular table. So the safety customer idea of this customer and discard them table. And this is the customer idea which is coming from my source. Stable source data. Okay. Which is coming from this particular step? This is the source. Start accustom variety. This one, This is the sort starter Custom variety. Now I'm going to select fields. Yes, if there is attained update customer name, abject state and update the I d. Okay, click. OK, so everything is OK. So right now I don't have any their time. My table. I am inserting. Get tough for the first time. First of all, I'm going to execute this the transformation step. Save this First s a G. So it has been executed successfully. Look, data has been inserted in my table. Now what I'm going to do, I'm going to do some changes in my data. So let's say customer name is Ellen. So from New York, Alan moves to let's say Ellen moves to Chicago. So Allen has moved to Chicago from New York. So I'm going to change my data here, so let's Ah, now, Alan, let's move from New York to let's say not Chicagoland. Alaska. Ellen has moved to Alaska now I'm changing. Did that in my state field. So how the STD type one is going to Gator that change. So sed type one will not maintain any history. So what it is going to do? It is going toe update at that time. My table. So instead, off New York, I'm goingto get the value. Alaska here. OK, value will be updated in my table. Now let's execute the transformation. Look, 1001 It was New York. Now I'm going to execute. It is going to be Alaska Now it is Alaska. Let's change the value toe off Chicago. Let's change the value, Let's say from Chicago to let's say no Chicago Bob was living in Chicago. Now execute Valley will be updated. It has been executed 63 now. Well, we will be updated in my table. Alaska. OK, Alaska and Texas Bob is living in Texas State. So this is how you implement S a G type 01 in Pento. Hope you understood the concept. Thank you so much and have a great learning 29. SCD Type-02 Implementation: Hello, everyone. In this particular lecture, we're going to implement a city type zero toe in Penta Ho. So with a city type zero to we maintain the history when there is a change. Let me just give you an example. Here. Let's suppose this is my daughter customer, I d. Customer name and state. So George was living in Los Angeles. Let's suppose George decide it to move from Los Angeles to New York. Then George decided to move from New York to Chicago. Look, we half maintained a history off. George, Look, He's been living in Los Angeles, has been living in New York from 2005 10 10 7 now currently living in Chicago. So look, we have maintained history off George. Now I'm going to implement the SED type zero doing print town. So first of all, I'm going to select get system in four. So this is going to give me the system date. Then I'm going to select this step dimension. Look up. Flash update step. This is my data customer. I d. Customer name state indeed. A string string customer. I d. These are the values. Now I'm going to connect. Disturbed with this particular step. So this is going to give me the current date system? Did click OK here. I'm going to connect. Disturbed with this step, this is Dimension. Look up slash update step. Okay. Connection type my SQL target table here. I'm going to select my target table. My target table is first of all, let me just create my table in my database. Die starter Best. So did I start customer A city type 02 is the table i d customer I d. Customer name State started, ended in diversion. So these are the default. We'll lose default. Okay, Now execute this particular query as it is. Table has been created right now. I don't have any daytime my table. So, first of all, I have to select Dick table here. I'm going to select which table table name is a city type zero to it has been selected. Next step is to generate the feels technical field. Will be i d. This is the segregated. This is the prime Ricky off this particular table customer. I d is not a primary t customer. I d the primary key at the source level. This is not the primary key of this particular table. Primary keys. I D. No. Next version. Feed version Fielders version. Fine. This is diversion for me. Stream data feed stream that affiliates the current date which is coming from the previous step Data range start. We'll start dead in the end It So when the first time you're going toe insert their time the table you're going to predict a mediator, so you're limited 1900 maximum limit days 2200 22 under specified a maximum limit. So these academy values that you're going to provide when the first time you will insert religion their table. So let's the bus Your business started Ah, on this year and it is going toe remain Tell this year. So I'm providing the dummy values. So let's use get reels customer ideas. You look up field. That's tumor State removed this and removed this particle of me diamond chain feel Is the field off my table Customer and disk or I d if d dimension table fields. So this is the table field, and this is the fee off my source status of my source. Start off released customer and excoriated the 50 feet, which is coming from the previous step. So remove the current date. Karanka underscored a tree so that these these two feels we're going to insert now. This is my look up free customer Radian, Custom ready started. Okay, Corin. Date Version. So this is the effective date. This is the current native the effective date. So I will explain the concept of just date. Click. OK, now I'm going toe execute. Run the okay. It has been a dedicated successfully. Now execute this particular table execute, Execute the select statement. Look, when the first time you will insert values, version three will be one. So this is the started. This is the ended. The other dumb idiots. Chicago, Los Angeles, George 1001100 to 1 of the three ideas deep primary key off the stable. Look, let me select. This is the I d. Used a table maximum. Plus one. Now I'm going to explain you the concept. Now, let's suppose George from Los Angeles. Let's suppose George moves to New York now. I'm going to provide the now I'm going to change my doctor. So, George, from Los Angeles, we moved to let's say New York, he moves to New York. So this is the current gig. So how a new road will be inserted. So how it is going to work? So let me just copy. Pesters George. George, is this torpedo Okay? This is the started, and this is the end It Now you will see the concept of distinct discover ended. Now execute this. Okay, executed this. You will see the concept off currently. Now I'm going to have in a dero for George. Where is George? There customer ID equals two 100 Terry, look. So this is my first road. Original room started. 1900. Look at this particular n date. Today, Jordan moves to Jordan, moves to very Jordan's to Jordan, moves to here Los Angeles. No, George moves to New York. Look at this particular date and get its current date, which I'm getting from this. Ah, which I'm getting from this step here. I'm going toe place. The current did so. This is the effective date. Effective date. Okay, now look. This is the effective date he remains in Los Angeles. Tell lifted now. Start date. Is this effective data? This now New York and the engages This look okay. And it is the final date. And it now let me just turned one more thing. No, let's suppose from New York. He moves to some other state now instead of this law. No, Bob. Let's is from Chicago. He moves to Alaska. Alaska executed. This version is too. Alaska Bob, I did this. This is not a primary key. This is the primary key values five. So, as I said, look. Maximum table. Really? Maximum table row count ISS a death in 44 plus on five 102 So with STD type 02 women came the history. Look, we have maintained history off. George George was living in Chicago from 1900 till Death State and he's been is no living in Alaska are Okay, So this is how you implement a city type zero to it? A city type 02 women in the history. So I hope you and after the concept, thank you so much and have a great learning 30. CDC Implementation: Hello, everyone. In this particular lecture, we're going to implement CDC in print. Tahoe, CDC, stand for change that are captured. So what is the concept of ST Dizzy? So let me just give you an example here. So this is my source stable. Die start source and to skirt. Employee, this is my source stable. And this is my target table. Destination table. Let me just execute right now. Source and my target table bore tables have seemed that there is no change. So the concept of CDC is that whenever there is a change in my source data, whenever there is a change in the source data debt, June should also appear in my target table in the destination table. OK, let's say if there is a change in this particular table in my source data, the change should also appear in my target table as it now what I'm going to do, I'm going to implement this logic. No. First of all, I'm going to Booth used the table input step able import table input table input. So step name is sore, stable connection here. I'm going to provide the connection detail. So since I don't have a connection, so I will create a new connection. My SQL is did it the best name click Next test database connection. Okay, fine. So next I will get select statement table. Aim is so sore. Stables. The sore stable name is Di Start source underscored employees is the source table in source underscoring play. So I'm going to sort data on the basis off customer I d underscore idea. Fine click. Ok, so this is my target table. Target table limits destined is going broke. Yes. Order by customer Raidi, click. Ok, so next I'm going to use emerge Rule step marginal much George, if join the source table toe this step joining this to disturb summer General, this much rule step is going to find out the difference on the basis off I d. So here. First of all, I'm going to provide the reference room so the reference row is is goingto be deep destination to target table. So this is going to be my target table. MMA target table lists distant target table. Okay. Compare Rose again. This is my source table, so target table reference row will be referenced. Rule region will be detargeting now Okay, this is my source. Stupid flag field. OK, Keys to match. Ah, I will select the customer. Underscore i d I will match on the business off customer I d values to compare which values I'm going to compare. I'm going toe compare these two feels customer underscore name and customer state Some matches goingto happen on the basis off customer i d But the fields which we're going to compare our customer name and state now here I'm going to select get feels so exclude the customer i d No customer name and state. These two fields will be compared on the basis off customer I d. Fine. Everything is okay. So next I'm going to use the which step federal fig. No, I felt terrible Then next I'm going to use the D League again. I'm going to use different euro step fairly thorough. Then I'm going to use the insert slash object. I will explain every concept. Why am using these steps? Okay, now, first of all here, I'm going to specify the condition. So here there is a flea called flags. So if sourcing target table have same debtor, then the flag field will be identical. If source in target will have different data. Let's suppose source and target will have different data. Then it is going to be flattery will get a grave there the new free Then if there is a change If any change happens, let's say if I update any value, then flak for it will be abducted. So okay, first what I'm going to do? I'm going toe. Apply a condition Flag Fig So first Okay. Flag fig equals to value. Deleted, Deleted. If this condition is true goto this step which step this step? Dilute step If this condition is true Goto this step The result is true. True false If condition is false If this condition is not true Goto the fell zero step. Okay, the this step false will go to the filter roof toe then if this so now next I'm going to combine different if the first let me just dummy selected the Mr Doing disturbed disturbed booking. So the flag three equals two. Let's say flag free equals toe identical. Then goto the Mr the Mr Otherwise goto the insert slash update step. Okay, here. I'm going to configure this The first of all let me just ah, configure this. So look up table. Very beady target table. My target table is Ah, this table get feels okay, We're here. I'm goingto get only I'm going to select customer. I d equals two customer Raidi. Delete. Okay, so this is the third. This customer idea is different coming from the previous step. This customer ideas de table for real. Okay, now here. I'm going to configure the insert flesh object. So target table is destination underscoring plight. Fine, get field. So I will select only. Which means do you difference customer i d keys to look up on the basis of customer Raidi here. I'm going to select a dick mapping customer I d ed Customer name and state name Ad Click. Ok, customer, I d I don't want to update customer. I didn't know leg. No object feels we don't update customer ready. Customer name when there is a change in the customer name. Yes, we're going toe Abdic. We're live. Yes. When there is a change in the customer in the state? Yes. We want to update to the valley. Respect Why? Why? Means yes, click. Ok, so this is configured this step is also configured. What about the step? This step is also configured false. Then goto the insert slash object. Otherwise goto the dummy. So save the step 1st 1st ofall save the savers CDC implementation No. Right now Source and target table. Both these tables have same field, so flag will be certain Identical. So what? It is going to happen? How this is going to how this is going to be executed. So here in dummy here in this particular step, I'm going to get the identical field. No exit your disturb executive dis mapping. Look. Go ahead, click. Just the Mr Preview Look. Identical flag field is identical. All fields are identical. Look merged rodef will identify difference on the basis off identified difference between difference on the basis off customer i d. If source and customers sourcing just source and target table have seemed that then flags will be identical. Okay, look Identical. Identical that I will go here. Okay, now next. What I'm going to do This is my source, Erica, I'm going toe insert in new ruin my table Insert new row in my source table. Now there is a change in certain to this particular table table name is this values customer. I'd even 004 customer name. Let's say Brock he's living in lead, sir. Exists now execute this particular Kuwaiti so quickly have been successfully executed. Executed this now in my source that I have one extra flee. This is my target. See in my target SRE target table in my target table. I I don't have this particular group this particular okay, Sore stable has one extra rue. Now let me just execute the step. So here reviewed data. So one new feed. These four fields are identical. There is one new field Phil. Daryl felt there. Oh, no. Felt Thoreau here. I'm going to get a new field identical and new field. Then from here, identical Rose will go here in the day. Mr In the new road will go indeed. Insert slash update step. Okay, the this insert slash update step will insert in new row in my target table, which is desk underscore Implied. No, Execute this. You will get this particular room now. Next, what I'm going to do. I'm going to some changes in my source table. So let's say instead of Chicago, let's say, Alan, let me just end the state from Chicago to, let's say, Alaska or New York or some other street update. Let me just get this particular table sourced sick stared equals to, let's say, Florida Vera customer. And of course I d equals to one the residue, Terry. So I'm getting this entered. You're using the safe update, Mort, and you try to abduct a table. So now I'm going to change the safe updates setting. So I will execute this particular query first. So let me just execute this particular Grady. Okay? Now I'm going to object. This particular okay? It has been objected. Now execute the source. Execute the source table. Select. Start from the source. Stable now. This has been objected to Florida 1003 Ellen Ellen is as Ellen Status. Update it to Florida now in the destination table. What is the state distant in the destinations? Table status. Chicago. Ellen is living. And Chicago. Now I'm going to execute this particular transformation step. Okay, Abject feel would will go here in the insert now. Preview Look changed. This is my chain that are now here. Majuro Identical changed. Okay. Identical looking rules. Figaro. Identical rules will go here in dummy. And here. Insert slur in the insert slash Update step. I will get the I will get detained. Should change group. Okay, This particular road. Now, let me just execute this particular query destination. Look, it has been updated to Florida now. Next, what I'm going to do. Look. Source in my target table. That same better now? What? What I'm going to do? I'm going to do. I'm going to delete a row from source data from source Stable. Now How? I'm going to delete this. I was used to delete command here. So which row I'm going to delete. I'm going to delete the first room. George, delete Delete from able sores there. Customer Underscore I d equals two. Wanted another one. It has been deleted for safe site. Execute the commit operation. No. Let me just execute my source. Tabor, I don't have 1001 Customer I d. 1001 Where is the destination cable? This is my target table in my target table. I have this particular route with customer writing. 1001 Now what I'm going to do, I'm going to execute this particular transformation step. Now fill. Tero will take the deleted grow here in the dilated step. No executor, this look at the filter. Oh, so first look at the debt off Phil. Terror Identical. Deleted. Now here. I'm going to get this particular row here. Deleted. Deleted. Look, if flags really Quito dilated. Sing, uh, toe through. Better to step. Does Billy Intersection. Okay, so this will have deleted. So this road will goto this particular step. Then rest off. Rest all Rose will goto this step here. Filter rule. This particular filter or step will pass the identical rose hearing me identical Rose will go here now in the deleted step, it is going to delete only this particular room. Okay, 1001 So customer ID equals to 1001 So this step is going toe Delete this particular roof from my target table now it has been deleted. Look, it has been deleted from target table from the destination table. Now my source and target table That same Brecher. Okay, so in this say you can implement CDC in Penta ho Hope your district the concept. Thank you so much and have a great learning 31. CDC TimeStamp Part01: Hello, everyone. In this particular lecture, we're going toe implement CDC with times. Time mattered. So with this times the matter You have to have a times time field in your table, so otherwise this mattered will not work. So let's implement the logic. So now I'm going toe. First of all, I'm going to take the get system in four step get system in for then. So first of all, I'm going to build a logic. Then I will explain the concept, then table input, then insert slash update step. Okay. Get system in for so I will select value start dead and the ended in the Stargate. I will. You started range transformation in the end gate. I will use the end it range transformation this particular value. Okay, so the first Valley Stargate will be d dummy value 1900 the second value in the second value. It will take the current times time. Let me show you Said the first times temple used in 1900 which is dummy value the second time. Stan, Value is the current time Stem value. Close this click. Ok, next I'm going toe. Connect this with the table input step So this is my table input. Step here. I'm going to provide the SQL Kuwaiti. So first of all, let me just create tables so to table. So first I will create the sore stable. Let me just copy paste this. Okay, create tables. I start customer underscore CDC customer I d customer name State Lord time. So this lead is important. So this lead means that this is the This is a time then that is inserted. Okay, this is the time Lord time create table for table has been created successfully. Next I'm going toe create my destination table Create this table destination table. Okay. Board tables have been created successfully. Okay. Next, I'm going to, uh right the SQL query here. So first I will create a new connection My SQL my SQL So ok, next local host dices Did database name finish. So here I am goingto provide dick Weighty. So what is my Kuwaiti? So select star From this day I start customer, So I will provide or lady uh, name there. Lord Underscore time. Great. That then question mark and Lord in good score time Less than equal to question Mark. So let me just copy paste the Kuwaiti here. Well or time greater than question mark and lower time Less than equal to question Mark. So this question months I was like replace variable in a script in script. I will select this option and then I will select insert data from this step which step revista get system in four steps. So in my first question mark, I will get this started in my second question mark, I will get the end. It will you. So look, start dick and ended here. I will get the started and the ending. So this means that replace variable in a script. So in the question mark with questionable, I will replace variable. OK, now click. OK, now next I'm going toe connect this with this booking now my SQL is the database connection . Look up. Which table to look up. I will look up my target table. Target table name is just a matter of discord. Destin, issue this No customer. Erin Driscoll, Destination this table. Fine. Get feels I will select only the customer. I d on the basis off customer idea. I want to perform the look up abjectly and it mapping customer i d. Customer name stayed lord time. So customer I devalue really are no object. No, we don't update the customer. I d ok, column. So now So customer idea will be the unique value. We don't update the customer. Raidi, click. OK, find table in port. Fine. Okay, Now next What I'm going to do, I'm going to double click. So I will place my cursor here in this canvas. Then I will DoubleClick DoubleClick go to the logging section. Transformation. Okay, fine. Log table, low connection. Which connection I want to select. I want to select the my skill connection lock table. I will create a lot Table slog, underscore. Table c dif e. I'm just good log. Underscore table. So this table I we create this table is not present in my database, so I will create this table from Penn Tahoe, so Okay, fine. No, All other fields are Okay. So in order to create this CDC lockable, I take a clit s. Well, So these are the attributes that are present in my CDs and Daskalaki about the seals I d badge, analyze All these feels okay now, in order to create this table I will click the execute button. Execute Click. OK, so table has been created successfully. Now let me just check the table. CDC underscore locked. So in this particular table, these two fields are important. Started and ended. So we're going toe capture these two fields. So now let me just quickly this remove this inquiry this right now it is empty. Okay, fine. One more important thing. So from here I will remove the dice because I have already created the database connection and indeed, database connection. I have mentioned the database name, so no need to provide the dice database a database name here. Okay, Everything is everything things to Okay, now what I'm going to do So right now in my customer, underscore this table. CDC table. I don't have any data. So what I'm going to do, I'm going to insert some values in my table. So right now, this customer underscore CDC table? I don't have any data. Fine. Let me just Okay, this is this is my destination Board tables are empty. I will insert two rules first. So here, instead of disciple, provided current timestamp value discovered in time in the Lord Time So this is the kind of thing that I inserted. Indeed, Table. This current time, this is the kind and destroys inserted. Desperate chloral. Let me just execute this. Uh, Vondra has been inserted. Elect Majestic Roy is inserted at this particular time. 16 0 to look for little 2 p.m. No. Next I'm going toe insert the next, will you next to let me just check. Look at the time stem 16 0 to 18 16 0 to 3 seconds. This is 18 seconds now what I'm going to do, I'm going toe execute. So, first of all, I will save this C D. C. It has been saved. Next execute this. It has been successfully executed for two rows have been inserted in my destination table as they look. This is my destination to post. Two rows have been inserted. So next what I'm going to do, I'm going to insert one more. Oh, this particular So I'm going toe first. Insert my better in the customer. Underscore CDC table. So here I will provide the current time. Instead of this time I will provide the current time. Okay, But before I will show you the lock table now when the first time you are going toe execute the transformation step started will be will be 1900. So we have said that started Value looks target. Really? Waas 1900 In the end, it really will be the current times time value. So this will be the current times time Very of 16 0 to look Okay, No, the next time I'm going to execute my it'll process this process This entangle We will become my start date value in the currents time Current time stand will become my end time and date Okay, Now let me just use this particular query in orderto explain the concept now. Next, when I'm going toe Wonder have been inserted. Look, Wandera has been inserted, I guess. Wonder I have inserted Okay, No. Let me just insert this particular role. Current time. Okay. I have to remove the bracket from there. Now execute. This Wandera has been inserted. No CDC in my source stay, but I have to redos target table have toe test to rules. No, how destroy is going to be inserted in my targeted. But now let's understand the logic. No. When the first time I'm going to execute the transformation. Stargate will become this in 1900 will be 1900 end. It will be the current times time. So next time I have inserted that kind my source table. Now, if I'm going to execute the process transformation process this ended will become my started. Now let's look at the query here. So here I will write. I will give thee this particular day like me. Just copy. Copy fee. Okay. Ah started greater than started. This is the started less than equal to deep end get which is the current time stamps. So this logic those particular lodge it in the question mark, I will get this started in the in this question Mark, I will get the ended. So this is the Stargate value and this is the end. A trellis of the Stargate really will be this. Now, Next time now which rule will be inserted? Only this particular road will be inserted from this CDC tables customer and discourse. City city table now execute. Look, only one row is inserted. No, look at the destination table. Now look at the destination table. Vondra is inserted. Look, John Alaska Now what I'm going to do. I'm going toe update a record in my table in my source table. So now let's update a record. So first of all, here I have to executor this particular set. SQL Okay, now object. Okay, SEC equals two. Let me just update the value in my source table. So now, instead, off instead off Alaska, I will update the value to New York. And why set state equipped and why? Also, I have to provide the guarantee in stem along with the state. Well, you I will have to pry Dick. I will have toe update the current times time value. Okay, at this particular time, stem the values updated. So I also have to update the Lord Underscore time value. Okay, Now they're customer. I d goes to 1 26 set state equals to New York up there, this particular or no, execute this. This will become It has been updated. No, let me just execute this Kuwaiti. It has become to New York now how this is going to work. And now let me just first query the table lock table. So let me show you how this is going to work. Let me just quickly deductible. Now look at the second room. Okay? Now this road will become my star dead. This road will become sorry. This end. It will become my started and govern Timestamp, will you executive only one drawer. So now this road will be updated in my final target table. So my final target table is this so New York, Alaska will be objected. Now execute the transformation. It has been updated now. Alaska. Okay, now this really will be updated by the inserts slash Object. So now, destination Alaska will be very quick. Quick. Mm. New York. John to New York. Should I perform the comet? Let me just perform the comet. Okay? It has been updated. 32. CDCTimeStamp Part-02: so hello, everyone. In this particular lecture, we're going to continue the example off CDC with times, time matter. But this time around instead, off current time. So what we're going to do, we're going to use the maximum Lord underscore time. Okay, so now what we were doing in the previous lecture we were using the ended and in the end, it we were using the current times temporarily. Okay, current times, time value Here the quake Iwas Lord time less than equal to the current time stamp. So now what we're going to do we instead of current time, we will use the maximum Lord underscore time The reason we will do the maximum Lord underscore time. Because with current time, there is a possibility off time zone difference. So this skin give us incorrect result. So, in orderto a white this kind of situation, we can use the maximum lower time and this is the best practice. Okay, Now what we will do here, I will do some changes. The bike click did, uh, database connection. My SQL table name is CDC. Just a modern discourse. He deceived the table name. Met the Max Max date feed so get real This lower time Lord, Underscore time. So this is going to give me the maximum Lord time. So in this particular table, maximum lord time is this 16 56. So, a vile ago I inserted one row in my source table in my customer Underscore CDC table. This Roy is not present in my destination table. OK, only three does. And in my store stable. I have one extra toe. So what I'm going to do? Let me just first created the lock table. Select star from lock table. Okay, so ended. So this is the time when this transformation is executed. So when the third time this transformation is executed this wasa current time stem at that particular time. Okay, 16 0 it so this will be my This will be my start date. Well, you okay? Let me just throw. I d start it. Will. You and my ended really will be the maximum Lord time off my source table. So my source table Maximum Lord, time will be this 16 56. Maximum day time Here I will provide the maximum day time, So let me first copy this. So this is the time. Then this particular Ruiz inserted. This is the time. Okay, provide the day care. So this time around, it is going to insert. So it has given me only one particular which I'm going to insert in my destination table in this particular table. Now let me just execute, Okay? First of all, I have to perform the commit operation in orderto If you're doing some changes, you have tow performed the comet operation. Make sure you have performed the comet operation. Now execute. So it has been successfully executed. Tim, Texas. Now let me just show you the lock table. So in my lockable one row will be inserted. And look this Let me destination table. Let me just perform the commit operation again. Lock table? No! Okay, so look, Vondra has been inserted. Now look at the time stem 16. 56. So here I am getting the maximum day 12 Maximum Lord, underscore time. So in this particular table, the maximum lord time was the 16. 56 here. I'm getting in the log table. I'm getting the maximum Lord times legally, which is dish and started really will become this. This was the inject will you Now This time around, the Stargate will become this when the first time the transformation is executed started. Really Waas 1900 the index? Well Iwas the current times time value when the process was executed then the second time this will become I started and in the end it the value will be the current times time vendor processors executed likewise for the third step. But for the last step, the end it really will be instead off current times temporally the end equally will be d and it really will be the maximum Lord Underscore time feeling really Which is this? Ok, so this is the best practice because you may end up getting the problem while using the current times time because there is a possibility off time zone difference. So hope you understood the concept. Thank you so much and have a great learning 33. Job Part01: Hello, everyone. In this lecture, we're going to understand the concept off jobs in Penta Hotel Integration tool. Also, we're going to look at the difference between a job and transformation. So to create a job, we're going to first go in the file section. New job. Okay, So what is the difference between a job and transformation in transformation? Every step executes in federal various. When it comes to a job, Vance, the step it successfully executed, then the next step is going to be executed. The next step is dependent upon the previous step. Okay, so let me just give you an example here, so file open. So neither some of deep transformation steps I have already created. So get filed, uh, get generator with writing generated. So let's create our transformation step just to give you an example. Here. Uh, generate. Okay. Right to look. Mm. And Knicks string. Okay. No, these two steps will Working barrel. Okay, let me just execute this, um, desk drawn. So, Misha, look at the step metrics. Look, these two steps are executing in peril writing and reading. So this right to log reading and this is writing here. Okay, these two steps will work in pattern. This is the steps off transformation. So, in transformation, every step works in federal. Okay, so now let's look at a job. So what is the concept off job in Penta Hoda tank aggression called. Basically a job contains different transformation steps. Also, it contains a job, so let me give you an example. Ah, job will have a starting point. Okay. Also, it it can have multiple transformation steps. This is transformation. Okay, so here I am going to give the part off their transformation. So let's say I have created few transformation steps here in this part. Consummation. These are some of their transformations I have created. Uh, okay, so I'm going toe refer the transformation. Okay, let's I want to refer it this row count. Okay, this shift breast shift, right. Click copy s, but okay, paste here. So this is my first transformation, so I'm just giving the part off this transformation, okay? No, just connect this consummation step. Okay? Right. Click open reference object. So this will open the transformation. So this is the transformation. Get files. Row camp. Okay, this will get that Ah, number of rules off the a number of rules in a Xel file. Okay, No, this is going to be another transformation. So here, I'm going to just give the partof transformation. So do either transformations, which I have already created. So let's say I want to give the let's it this data validated transformation step copious part Biest here. Okay, Now, at the end, I can give the successful step. So this is nothing. So this success is the last step off transformation. So it means that it's a successfully completed once the a job step riches here in the success part. So now I'm going to have do transformation steps. Okay, First transformation, second transformation. Also, I can have a job it. Okay, let's say if I have a job here in the job. So this is the job. I can also and the job. Okay. Ah, job contains different transformation steps. Also, it can contain a job, multiple jobs or a single job. Okay. Here, I will give the part of this job okay. Copious part. I'm just really offering a job it. Okay, so this is how you Okay, so basically the benefit off having a job is benefit off creating a job is you are basically dividing your transformation steps into multiple transformation step so you can create multiple transformation steps and add different transformations steps in a job. Okay, so this is the benefit debt. You are basically dividing your bigger problem into smaller problems. You are dividing bigger task in tow, Multiple smaller tasks. Okay, so in this way, you can easily manage different transformation steps. Okay, It can help system performance, because if you execute one big transformation steps, it can consume more memory compared toa if you have different transformations, steps in a job. Okay, so that's the benefit off having a job. That's a benefit off. Creating a job in Penta ho. Data integration. Okay, look, starting point in the last step is success. Okay? Look, no. So now execute this job. So job 01 This is running. This step is executing. Look, maybe you can remove it. Executed find Now, execute this. No, it works perfectly fine. Okay. In order to view this transformation, you go. It opened reference object transformation. Okay, so this is how you can view the transformation. Look open transformation. Okay, Dr. Validator transformation step. So death, Some main difference between a job and transformation in a job. Once this step is completed, then it will go here in the next step. Okay, so otherwise it will disturb. Will wait for this Steptoe complete. Okay, It will wait for this Steptoe complete. Then it will execute the step So a job is in job. This step is going to be dependent upon this step. Okay, so likewise, this step is going to be dependent on this step. OK, now let's look at different folders there. Okay? In journal, we ever starting point dummy with doesn't do anything a job. You can have a job inside a job. Okay, sec variables sick. We will discuss it later so you can put the success step at the end off every job. Okay, Transformation, this transformation step will referee transformation Any transformation? OK, which you have saved in any folder. So this is the journal step SEC. Variables able. Discuss it later. So let's look at the male section. So in the male section, we have this mill sang an email, get mails, male validator. So let me give you a use case off this male step, So let's let me just go drag and drop. Okay. Ah, connect this. Okay, so let's say if you're running any dear, let's say if you're et al process fills. Okay? So what you are going to do, you can send an error log error message toe. It'll administrator via this meal. Okay, you can send an email, and in the email you can attack the error lock so you can use this mills utility to send an email to et like Miss Trigger. So let me just open this here. You can mention the destination address. Si, si. OK. Send their names and their address. Replied tok contact person contact person. There's a single information. This is the destination information where you want to saying animal destination address. This is the server part s empty server. Which server you're using Port. You're going to define the port number here. So in case you want to define this authentication, you can define a authentication step in the email section, you will provide the demon. So if the email is ah high priority, you can mention the high priority importance. Okay. Sensitivity on leasing youth. Okay. If you want to use the html format in the male body. You can check this option if you want to include the 18. The message you can included attached file. So here in this section, you can have a different options like you can attach. Like you can attach a locked file after file warning fi lot and identified. So you're going to select ZIP files to single archive, so here you can name off the park, so hey, you can provide the part. Okay, so basically this meal step, it's used to sing an email to detail. Administrator informed them that day is an error in the it'll process. You can send the error message to Anita administrator. Okay. Via email. So this is the mail step here? Indeed. File management. We going toe have different steps related to fight. So if you're dealing with files, you can create a copier files, create a folder, create file, compare different folders, compare different files. Okay, convert fights to different formats. Egg file aims to result. Okay, so in the next lecture, we're going to discuss these steps 34. Jobs Part02: Hello, everyone. In this lecture, we're going to explore some other sections. OK, so first off, all I'm going to look at this section File management. So in defile management section, I have this step egg file names to result. So let me just dragon door pair. So let me remove these steps. Okay? Remove these steps here, so egg file aims to reserve. So basically, this egg file named result will past. If I limbs, it will read the filings from the four year. Very. We're going to specify the part. Okay, so it will read the file names, and it will pass it to a transformation. So let me just add your transformation step here. Transformation who? Okay, so just connect this. Connect this and connect this. Okay? Mm. File management. Okay, so here I will provide the part, so I'm going to go into my D directory. Ah, source. Okay, files. So I'm going to provide this part. Okay. Inside this part, I have four files, So, uh, eg fight. Okay, EG parts will be added. Hero came. So here. Ever give the regular expression read all files. Dork. This star backslash darts start. Okay, Okay. So it will read all files, an equal plastic toe, This transformation step. So now I'm going to create a new transformation. Okay, I'm going to create a new transformation. I'm going toe pass deep part off their transformation here. Okay, so so here. I'm going to go here in the job section, get files from result. Just drag and drop here. Right to log. Ah. Okay. Um, output text file out, but Okay. So connected. Steptoe, this OK? Text file. Ah, get fields. I will get all the fields here. I will just mention defiling, so I want only file him. Okay, depart is this is the part file name? 01 Okay, so this is my part, okay? For basically exile limbs. Steph will read files from this spot, and it will pass it to the transformation step. Okay, so here, when I go here in the transformation step open consummation? No, it's not this in this step. So this get files from result will read. Defiles Okay. And then it will right toe text file. Okay. No, save this. I have to save this first safe. Ah, save it in a job for you. So let's say this is best. Okay, this is the test test part here. I will go here in the job section job this shift press shift, right. Click copy s part. Okay, So I will provide the part off this transformation here. So this is my transformation. Okay? Now execute this, uh, run. So it has successfully executed. Now, look, the directory. Uh, this fight has been generated, so it has given the file names. Okay, So let me just give your youth gifts off this get filed claims for Let's say, if a thought system has placed file here in this directory where I have placed my files, let so this So let's say so. System has sent me four file. So sore system are claiming that we have thank five files, not four files, but we have received only four files. So what we can do here is we can use the step. Okay? Vacant. We can use this step. Get the file limbs, pass it to the transformation step. Okay. From here, we can weaken right into a text file. Look, we can write it to a text fight from here. We can pass it. We can sink it. Toady source system Deck. We have received four files, not five friends. You better check your starts. Okay? So we can egg and other step here. Ah, where is the utility? Okay. Mail Millstone. Okay. Email message. Input this this meal step. Okay. So I can send an email to soar system that we have received only four files, not for five files. So you can better check your stats. Okay. You can better check out urine. So here, I can connect it to mill. So here I can provide the different information destination address here in the attached file. I can provide the file name. Okay, Depart. Okay. This text try like employed districts. Well, deck, these are the files step we have received from urine. But you're claiming you have given us five files. So we have received only four fights, so this could be one use case. Look, now let's look at some other steps, OK? File management compare for this so you can use this step toe. Compare two different folders for Let's say, if you are archiving data, let's if you're copping, get a form a folder, a tow folder B. OK, you can use this Steptoe, compare if you have corporate all your files successfully or not, Let me just open neck compare file says if the compare size file size are equal, then there's no problem. You have successfully corporate your file here. You can provide the folder Name one. You can provide the folder name destination. Okay, you can compare Do four years with this step. Okay. Can work file between windows and UNIX. If you have to convert your file in different formers in windows and UNIX or Mert, you can use distance So these are very simple steps Here. Look. Look source file So you will provide the sore side. Okay, lets it let me just but okay, act but but will be added here. So if you want to convert from windows to UNIX you will use this option. If you have to convert you next of Windows, you will use this option. Okay? In the advanced setting design option called success conditions. So there's going to be success if all files were processed. Okay, this is my first condition. So the success criteria is if all files are processed. Second option is if success at least, exiles are processed Okay, let's If 50 files are process, then it is a success. Great idea. There's going to be another criteria successful in number off a number off error files. Lesser than, let's say, 10. So, in case of 1,000,000 files, less than 10 files doesn't matter. Okay, so this is my sexist, great idea. Eso No. So let's so Kate Anna devane copy files. So if you want to copy files, you will use this option. This is very simple. Creator for leisure To create a four label dis option, create a file. You will use this delete file. Delete file names from result. Okay, so you can use this option after reading the file. Um, someone stuff. I limbs direct. You can delete file limbs. Okay. Delete files daily forgers, fi compare eso if you have to compare two different files, so you will use def option. Okay, let me just drag and drop here. Here. Filed bonfire to AEG filed to result, so you can easily compare it with this option. Ah, move files. So if you want to move files, you will use this option. So here you will provide the source file destination folder, where you want to move files. OK, create destination for her destiny. So if there's no destination for the you will create it, take a day to file. You can specify various different things. So in the advanced step, you're going to have this success condition. Success when all work sign Okay, success than at least X number of fires moved. Let's say if you have 100 files Legend 99 fives moved. Then it is going to be this success criteria a successful number off errors. So let's say if you have, uh, 100 files and there's going to be only successful number of Federer's lesson toe. If there's going to be less than five number off errors, OK, then it is going to be to success criteria so you can do various different things related to file management process result File names Okay, on zip file, wait for file. So let me just drag and drop it right to fight. So basically, in this step, you can specify defy limb Here you can specify it. The part here you will provide the text you want to write, and if I look a so look successfully processed, all files OK, so this can be used for maintaining logs. Okay. Successfully processed all 100 fights. Okay, this is very simple. So this is my file management section. These are very, very simple to us. You can do it by yourself. Okay, so now let's look at this condition, section check. Database connection. So vile executing the job, you're going to first check the database connection. If the database connection is up and running, you have to take this thing. Okay. Here, you will use this step. Okay. April. Check if the database connection is up in running or not get connections. So right now, there's no connection. Okay, so let's locate another step. Jack Files locked. Basically, this step is used to check if the fights are lock or not. Basically, let's say if you are, if you're et il is processing some files, you have to make sure that your file is not open, okay? Because it may in contact an error. Vile executing the file if the file is opened. So you have to check first Jack fires locked, so it will check of the files are locked or not. If the files are locked, it will aboard the job here in the utility section, there's going to be the aboard job. Okay. Ah, otherwise, Ah, look, this is the failure condition in GIF. Files are north in case files are not locked. It is going to go toe next type. OK, so this is the purpose off this files. Check locking. No, Uh, check if a folder is empty or not. This step is used to check if the folder is empty or not. So let's suppose if you have moved some files from for Larry to folder B. Now you're going to check a full aerated all file or this folder is empty or not. If it is empty, it means that all files are successfully moved. Check Web services availability. Check. If the files exist, you can also check the files. OK, column exist in our table. So if you have to check this specific column in a table that it exists in a table or not, you can use this step. Okay, file exist. Okay, this is off. I like this table exist. You can also check the table exist, owner. So these are some of deep steps that you can use inside this condition section Now we're going toe See the scripting. So in Penta Ho data integration toe you can use these different scripting like you can use the scale shell scripting JavaScript scripting here If you're going toe, let me. So if you're using the Lennox machine, you're going to write the shell script. Okay, here in the script section, you're going to provide all your scripts. Okay, Uh, insert script. Now you're going to insert your shell script air inside Defection. Okay. Working directory. Okay. Logging setting name of log file. Hey, will provide all the log files in the part. Where you going to generate the lawful. Okay, so this is basically used for writing shell scripting. This is used for writing a school query. You have to establish the database connection first. Here you will write your Eskil Kuwaiti. So this is the javascript. So in case you have to write javascript in, it'll process or in the job, you will use this utility this type. Okay. Writing javascript for executing the JavaScript ball floating. Basically bulk load from M s s schooling toe file. Bulk load into MSs school. So basically, when you're dealing with large volume of data or you are in a debtor migration project. You can use this bull floating utility so you can lure that are from my SQL ing to find. Also, you can Lord that I into my still database, so there's going to be different options available for both floating so big data, we're not going to use it. Examine. So if you are, if you have examined files, you can use these different options related to examine files you can Execs Immel jacket example file is well formed. Okay, You can perform different operations, utilities, a border job. Okay, a border job is basically used to abort a particular job. Okay, you can specify the condition air. Let's save if database connection is not up in running a border. Particular job. Okay, fine. Being a host. So if you're connected, if you're communicating with a dosa with you can use this particular utility to check if the other several is communicating video server or other servers up and running or not. Okay, you can use this thing. Ah, host utility so right to log. I've already explained this here. You can provide the log message for logging purpose truncate tables so before loading data in a staging tables. If you want to truncate any tables, you can use this options to truncate a table. Now these are obstinate. It we hardly used. Okay, repository, check if connected to deposit You basically the concept off reports of three years If you have a distributed system, let's say if you are sitting in a UK and your friend or your project members are sitting in USA, Sweden, Norway. Okay, so basically et al files et il the transformation steps and jobs, they all are saved in a repository. Okay, so you have to connect it to the depository to get the detail transformation are to run detail transformation or to run the jobs. So first, you're going to check if you are connected to repository or not. So this is going to be your first step. Then you're going to establish stick, get a base connection. Okay. Here. You going toe, Jake, specify depository. Okay, repository name. Then you're going to connect it. Okay, this is the repository step with fight cancer section. You going to use different protocols? The file transfer Normally, we use FTP protocol heavy going toe, have different options FTP s sftp ftp do either different particles off white transit. So if you want to get a file from a December, you can use this. Get a file with FTP. Okay, let me just drag and drop here. Here, you can provide the i p address. Poor Jews a name password. So instead of writing the whole FTP script, you're going to use this utility. Okay? This advance success if all work fine. So you're going to provide all these credentials? Okay, in order to get all files parks, where you want to get the file remote remote directly. Okay, partner, You want to get defies Target directory where you want to place the file. Okay, these are different. Option. Get a file with FTP fs. So if you want toe get a file with FTP fs particle, you will use this utility. Okay, upload file to have two ps porter file with Okay, differ. Different protocols are fine transfer. Now there's going to be another section file encryption. So, basically, for file encryption, we use a program called BDP, so PDB through PDB program, we encrypt or decrypt files so we can use this steptoe encrypt or decrypt file for security purpose. So thank you so much and have a great learning 35. Parameters Part01: Hello, everyone. In this lecture, we're going to talk about perimeter and valuable. So what is the difference between a perimeter invariable? So a perimeter. Once the values defined for a perimeter, it will ever change. So when it comes to a variable, variable will change its value. Okay, So value off the perimeter will remain constant throughout the job or transformation. Okay, so now we're going to discuss how to define a variable and how toe pass available in it. Consummation. So first, they're going to discuss this. Okay. No design tap here. I'm going to go here in the journal section. Start. Okay, sec variables. I'm going to drag and drop this spark sec variables. Okay, so I'm going to pass it to a transformation. So this will be my transformation step. I'm going to connect it with this transformation step so sec variable. So I'm going to double click it here. I'm going to define a variable. Let's say variable name is country value, which I'm going to let a definer value U S. C. So I have defined a value U S A. For this particular valuable country. So now look at this scoop Variable school type. So here I am goingto have four different options. The first option is valid in the Java virtual machine. So let's say if you select this option valid in the Java virtual machine. And let's say if you have multiple jobs and they are running in parent so you can use this variable in multiple job. Okay, So you have to be very careful, vile, defining the variable school. Because if you select this option, you can use this variable in different jobs. Okay, so let's suppose in some in a job. We have a variable called country, so I can use this variable there. Okay, so you have to be very careful, Violet, using this option. So the next option is very late in the current job. So this is my current job. If I select this option, this is my current job. So in this, the job name is job 01 is the current job. Okay. In this particular job, I can have another job. I can have multiple jobs, so this will be my current job. So this particular value will be very late in the current job, which is job 01 So if I select this option. Okay, for the next option is valid in the parent job. Okay? Lets it This job has a patron job. Okay, So I can have a pattern. Job lets it. This is the job which I'm going to connect it. Okay, lets I have another job. So the parent job is so the patron job of this job. Is this ok? Better job of this job. Is this particular job Okay, so if I select this option, it will be valid in the parent job also. Okay, It will be valid in the current job. Also, it will be valid in the barren job. No work about the next option. Valid In the root of your first job will be rude jobs. If I select this option, let's if if I have multiple jobs. So if I select this option, this variable will be valid in the rule job. Your first job, your first job will be rude job. Okay, this is the lady in the Let me explain this again relic in the Java virtual machine. So if I select this option, this particular variable will be valid across multiple jobs. Okay, Let's I have Dobbs 01 job zero to Okay, So the other different options scoop type, scope, options you can define. Let's have one toe have valid in the current jobs. I want to keep this variable valid in this particular job. This is my current jobs in the current job. I have the sub job. Okay. Further sub jobs. Okay. Different dogs. So now remove this. So here I will provide the transformation. So now this is sec. Variable country value is us. A variable is valid in the current job. Now I'm goingto have a transformation step. Okay, indeed. Consummation step. I'm going to go ahead in the job section sec variable, get variable. So this get variable will step will get this particular variable from the job. Okay, set variable here. I'm going to Let's say define a name variable. So the variable name is Let me just copy it from a variable amis country. Just copy this B is too Double click variable amis country. So this is how you define a valuable. So now dollar this bracket and this curly brackets. Okay, so this is how you define a variable belly. Okay, Now, this is the value off this country. Okay, Now I can have a name. Country name. So this is like this. Select me, Just give you an example. Let's say ink X equals Took 10. No end via equals, two x. So I'm doing like this. So I'm defining another valuable okay, defining another field. And I'm giving the value off X. Okay, so here I'm defining get variable country name. I'm defining another field and I'm giving value off country very big toe differently. Okay. Country name for die Pissed string. So this is the get variable now, right to look so right to look. Um okay. Ah, get fields. Country name. This is my variable country. Okay, I'm passing it. So this now, save the transformation. Save s job drawn 01 Okay, now, just copy the part shift. Rightly copy s but no. Here. I'm going to provide the part of this transformation. So this is the part off this transformation. Click. Ok, Okay. Now, executor death. This is going to work. Fine. Country name. It's USC. Okay, so now we're going to define a perimeter. So in order to define a perimeter, we're going to go in the view section here. I'm going toe the bike like this job 01 Okay, so this is my job here. I'm going to go in the perimeter section here. I'm going to define it. Better me. Okay, let's say if I defined perimeter perimeter default value. Let's say Bangkok. Okay, this is my para meter. The value off a perimeter is Bangkok. Name off. Parameters. Perimeter Bangkok. Now this value is valid. This is the global value. This will be valid across all my job steps. Okay, So I can use it in the transformation I can use everywhere. So no. Right here. Okay, No, Get variables here. I'm going toe. Okay. Sorry. Just remove this. So the first name is country name. Variable dollars country. Okay, so the perimeter name is city, so I'm giving fear. Lee City on the variable is dollar of eqt. I have to give the ball flick bad. Are we done here? Just copy this. So this is how you pass a perimeter to if really so I'm passing it toe disused city. Okay. Ah, you can You can I don't define like this or you can write like this, but think my scent and percent percent. Okay? You can also define like this by time. I'm going to define like this dollar curly brackets. Okay, so the city is Bangkok now, save this. So execute this particular job. Now I'm going tohave here. Country name? Vic Here, let me get food Clearing their door. Okay, fine. Save save this job now I'm going toe executor This particular look country named USA City Bangkok So this is how you define impassive perimeter in Penta ho detto integration toe ok . 36. Parameters Part02: So now we are going to look at how toe overwrite a perimeter value. So we know the fact that one stop value for a perimeter is defined. It will ever change, but we continue the value of a perimeter within certain scope. Let's say I want to attain the value of a perimeter here in this transformation step so I can change it. So, Vince, deep in Tahoe, that integration cool comes out of this particular step. Transformation step. The value will remain the same. The value will be the original one. Okay, so the original value is view. The original value is Bangkok perimeter. Now I want to change it toe, Let's say here I will define perimeter. Change it to London. So then now the value off a perimeter is London, so it will remain valid here in this particular school. It will be valid only for this particular step. Transformation step. Okay, so let's say I have another step. Consummation connect this to this. I'm going tohave another step transformation head job. Get variables. Okay. Right to lock connected. This. Okay, No. Get variable or give it, uh, para meter view bad and we don't copy this transformation step. Okay, Better make dollar. This 30 01 Okay, types, drink. Get real City 01 Fine. Steve. Dis transformation step. He's either too. Okay, Now here the value here in this particular transformation step the value off a perimeter is London seven Penta Hotel Reacher. The value will become the original one. Okay, the value will be Bangkok. No, execute this. I think I need to save this particular right to log right Gulag, Save this. Save this. Get variable. Fine. Uh, fine. Get variable. Fine Job saved this. Hey, what is the value? I need to change it. Okay, now change it here. I don't want to know. Here. The value is Batam in London Here, value is nothing. OK, so this transformation step should give me the value off. Ah, better meter, which is Bangkok now? Execute Saved this Now, executor, this Now I should have look U s a city. Bangkok. Okay, look. Country name USA City London So once depend out that I integration tool reaches here, the value will become the original one, which is Bangkok. Okay, So the global perimeter I can override the global perimeter, but equal remain in the particular scoop, the value will be changed in the particular school hit up A clear scope Is this transformation step? Want Penta hotel Come out of this step will get out off the step The value will become the original one So the original value is Ah Bangkok The value will be original Okay, So hope you understood the concept in the difference between variables in perimeters. 37. Metadata Injection Part01: Hello, everyone In this particular lecture, we're going to discuss the details matter. Data injection step. So what is the importance off this part of your step? So let me just give you a scenario. Let's suppose if you're reading data from multiple different tables and you want to insert that time different target table So what you are going to do, you are going to create multiple different confirmations. But if you're using, it'll meta data injection step, you will create only one transformation. Okay, You don't need to create multiple transformations. So now let's look at the example off. PPL matter that the injection step. So So you will go in the floor section here, you get the detail matter data injection step. So now what I'm going to do, I'm going to first Giusti debt a great step. Get a great step indeed. It's a good step. So let me first show you my cable. So this is my first table, which I'm going to read their tough from. And this is my second table, which I'm going to read their tough from. So first off, all I'm going to use the that a great step. Then I will use the equal matter doctor injection step. But first I'm going to create another transformation. In this particular transformation, I will create the empty template. So able input Celexa. If I use the table in good step, then I will use alert the text input, text file output, connect these two steps. So here I'm not going to provide any configuration empty. I'm not here. I'm going to provide the database connection only my skill. Finish click. OK, so I'm going toe only pride the database connection text violin put empty. I will not provide any configuration. No matter. Data details will be provided in this particular template. Saved this. So this will be the matter that a template transformation meta data inflict. So this is my et al matter, data injection step. So let me just open this fecal matter that the injection step. So here I am goingto provide the part of this template. So this template let me just provide the part matter that injection metadata template. Look, these are the properties off my template. So look, table input. The 1st 1 is the table input properties. This is the table file output property. So These are the properties off steps which are present in the template metadata template. So table input. I have two steps table input in text file output. Look at the properties here. So table in port table textile output. Is that the properties which I'm going to configure now click OK, data. Great. Here. I'm going to provide the escalate quickly. So first I'm going to provide the disc. Weighty. OK, select star from die Start employees. So now open the debt. A great. So here I'm going to provide Kuwaiti name is Kuwaiti type is drink data Kuwaiti. So second method that I will probably file part for file part file in this court part data So select star from dies dot Imply file part. So this is let me show you defile part here. So I'm going toe place the I'm going toe first read that letter from the employee table Then I'm going to write data in this particular soldier. So here I'm going to provide the part where I want to write my data so imply folders so phailin will be implied So this will be the final him And this is the part. So now Let me just give my second grade here. I'm trying to explain you. So this will be my second Kuwaiti and the part off my this will be the part where find will be created. Okay, this will be the part customer Will be the name of file. So two values five part string equity in the file parts equity in the file part. So this is the part where find will be created with doctor and this is the second part where file will be created. Customer file will be created with this user. Underscore table Better leg. OK, now, in that metadata section, I will set the properties off table input step in text file output step which these two steps are present in this particular template. Deaver. Empty templates, no configuration. Okay, No configuration. So I will configure in the details matter that time. Jackson step. So now SQL, which is still I want to put a gift. Uh, sweetie, get a great query source will quickly. Okay, sore step is this data grid step. The city source step and source will just quitting now. Next five name file, but select source. Really this file in this car park. Sore step is dead. Good. No. Fine. So no next time going toe. So first I'm going to execute this. So one important thing I want to explain, which is very important. Let me first exit, save this and execute this General explained to you so this will be the detail matter. Data injection step meta data. So injection step. So one more important point. I want to tell it's dead when I'm going to execute this particular transformation debt off this particular query is going to be injected in this particular template. Okay, this Kuwaiti debt off this query will not be injected. Okay, if you want that off this query, Toby injected dazzle what you're going to do, you are going toe create a job. Okay, which will discuss later in the next lecture. So now let me just execute this. You will see it has been executed successfully, uh, unable to get feels from the previous step because often era, whatever. I'm getting filing this court part. Okay? Okay. This is the error. Now, this has been successfully executed. Okay? Now, let me just show you defiled So one file has been created, OK, But this fight customer file is not created because get off This particular query is not injected Tau the next step. So now just remove this. So we haven't discussed just later in a lecture. So no, save this. So next what we're going to do so from this letter in this particular file, I have I d first name, last name, gender, country. But I don't want this each column. I only want I d first name. Last name. Gender, Country. I don't want Age column. So what I'm going to do? I'm going toe Use another data. Great stuff. Debt. A great user data grid here. I'm going to provide the column. Name, Column name Second. I will use the Dr Type String String column. Name I D for this name. Gender in country. No space. Fine. Make sure there is no space debtor type integer string. Okay, now click. Ok, so now I'm going toe Ah, past these details to this particular text file output steps. So here I am going to configure these details first. So which section okay are put? Feel So what? Where I'm going to go. I'm going toe. I'm going to go in the configurations off the text file Output, method it off. Textile output. These are the matter. Okay. Matter that are details now are put. Fish name. So here. I'm going to select the Phelim column names. It is coming from the debt. A great to get a great toe find. Next, I'm going to select the rich fig data types the open field. So debtor type debtor type click. Ok, so here I'm certain the these feels okay. I'm I'm configuring these reels using the step off using the detail matter that the injection step saved. This also saved this. Now you can see execute this. It has been executed successfully. Now let's look at implied or text fight now. Country I d. First name last name gender in country. There is no last column each in my text file. Okay? 38. Metadata Injection Part02: Hello, everyone in this particular lecture, we're going to look at another example off et al. Matter. Dr. Injection Step. This time around, we're going to read that are from multiple tables. My first table is this and we're going toe insert their time Two different files. So my first while will be created in this particular soldier with name E M. P. And my second file will be created in desperate clear folder with name user underscore Table. So now this is my metadata template. Right now there is no configuration. Okay, So you think the et al matter, Dr. Injection step, I'm going toe configure. There's something to supply the matter that are toe this particular template. Okay, now, first of all, I'm going to create a transformation. Transformations look, First transformation. In my first transformation, I will use to get Ruth, get Ruth from result. Then I will Giusti you deal matter debtor injection step. I will combine this tow this weekend matter that the injection step in this particular transformation I'm going to use the data grid. Then I'm going to use the copy Ruth the result. So this copy rose to result is going toe past Enter toe this transformation when I'm going to use this transformation in a job. Okay, so now I'm going to create a job. A job? Okay, So that that great hair I'm going toe give named Kuwaiti in the fight, but underscored part Kuwaiti strength diapers Drink, file Buck, string the data here I'm goingto provide to equities. So my first question is this. And my second Grady, is this no need to provide the get a base name. Okay, five part so ah, file will be created here in this particular part. Second file will be name off. I will be employees. Second file will be created here in this particular, but let's in. Name off file will be user safe. Let me just open again. Fine. Everything is okay. Fine. Now copy does to result. Okay, uh, so let me just see if this see if death data injection it has been saved. This one this is see if Okay, now, indeed. Decter injection step here. I am goingto provide to values Kuwaiti in the file, but so I will get these two values from this when I'm going to use it in a job. OK? Now, you tell metadata injection step. I will provide the template which template I'm using. I'm using the template Cordy met. Get on the score template. So I will supply the metadata details. So this particular template meta data underscore template. Okay, able input text file output. So SQL for equity Select source. Early equity Get growth from the result. Okay, now, next important point is Oh, put feels name. So here, I'm going to select the file Part fight, but okay, click. Ok, save this. See if this step no job start then consummation. My first transformation, my second consummation. So with the help off, let me show you one more thing. So with the help of this, we're if, with the help off copy rose to result. I've been ableto passed after. Took my next transformation. This transformation in a job. Okay, no, here. I'm going toe provide the part of data passed through transformation. So parties let me just provide the party. So the elect meanest again provide part data pastoral click. Ok, so here I'm going to provide the data injection step. But for data injection step Is this my target? The injection click. Ok, safe. Make sure you have to save this first. No. Save your job. Execute your job. Okay, One more important thing. So click this. So you have to make sure. Deck, you have to click this particular option. Execute every import row in orderto in orderto pass multiple data. OK, so if you want to. If you want to pass multiple data, multiple queries get off multiple queries. You will have to select that particular option. So now you will able to pass that off these two queries. Okay, so you have to make sure that this option is checked. Execute every input. So this will execute every input each and every import. Okay, Now execute. Couldn't open night or text. It seems there is an error here. So what is the unable to get fields from the previous two step? Because off another. Okay, file underscore part filing of core pot here. That a great filing underscore part. Now save this. Save this now, Richard. Brooke. So where does that matter? Ah, let me just see Diarrhea. Okay. Filled matter. Doctor injection step field. So let me just check the matter that the injection step get row from the result. Query Okay, so let me just remove this. So no removed this on. Now here. We're going to provide the file in value fight, but file part click. OK, Saved this. Now it should work now. It is successfully executed. Look. Okay. Now let me just show you. Defiles imply Gortex fight. It has been executed successfully. Now let's look at the other files, user. And of course, table file. All data is daring in the text file. Okay, so this is how you execute multiple input values. You have to check this particular option.