Databricks Tutorial | Databricks Free Edition Tutorial with End-to-End Data + AI Project

Key Takeaways

I know many of you clicked on this video because datab bricks is getting popular. In this complete micro course, we are going to first learn the foundations of datab bricks and then we will build end toend data engineering project in e-commerce domain. We will do our data processing through bronze, silver and gold layers and eventually build business insight dashboard. [music] Throughout this course, we will be using datab bricks free edition. This free edition enables students and practitioners to learn professional data and AI tools for free. For people who don't know what data bricks is, it is a leading AI and data platform for enterprises. The reason for its popularity is its ability to simplify their architecture and speed things up at the same time. It has helped many companies like Mercedes-Benz to speed up their datainformed decision-m process by enhancing their business intelligence and at the same time improve their query performance and run ML models on sensor data. We will learn more about distributed computing spark and data bricks in our foundation section. All right, let's jump into our project problem statement straight away and we are going to use some interesting [music] storytelling for this. A toz is a rapidly growing e-commerce company headquartered in the United States. As their business expands, both the volume and velocity of their data have exploded. They currently rely on Python-based ETL [music] pipelines for their analytical workflows. But these pipelines have become difficult to scale, causing performance bottlenecks and delays in dashboards. The company's head of data engineering, Tony [music] Sharma, was called in to fix the issue. He quickly realized this [music] wasn't just a coding problem. It was a scalability crisis. Meanwhile, Bruce Hari, the chief operating officer, had been hearing complaints from business teams. Reports were delayed. Dashboards froze. Marketing couldn't see yesterday's data on time. Tony met Bruce in boardroom. They agreed the system had reached its limit. Python scripts were running out of breath. They needed something built for large scale data processing. That's when Tony proposed data bricks, a managed platform [music] that combines Apache Sparks distributed power with the flexibility of the cloud. Bruce agreed, but instead of a full migration, he wanted a pilot project, a small measurable test. Tony assigned the pilot to Peter Pande, a curious data engineer known for tinkering with every new AI and analytics tool in the market. For Peter, this was more than just a project. It was a chance to learn modern data ST and replace old systems. He started building a pipeline on data bricks to test scalability and performance. So in this project, you will be joining Tony, Bruce, and Peter as they explore how data bricks can [music] solve a TOZ's scaling challenges and learn how cloud data engineering drives real business value. >> Peter, you saw the email from Bruce. We got explore data bricks now and see if it will really fit our needs. >> Yes, I saw that email. I think I'll be able to do it. >> Okay, but how? Do you know data bricks already? Have you worked with it? No, not really. But Tony, you know, nowadays things are easy to learn. You can go to YouTube and find some crash course. >> I hope so. But let me clarify the requirement to you. Okay, I'm reading from the email. So, you have to confirm three things as a part of your exploration, right? The first one is quite obvious. Of course, it should work better than our current ETL process that is given. And the second thing is the tool should be easy to adopt, right? I don't want to spend too much time in training the entire team. You know, it's not going to work and it should be easy to adopt. And number three is it should fit our road map. So you know our road map right? We are looking for three things in our road map. One thing is scalable. We should be able to accommodate any scale of data hypothetically. Number two is it should be agile. So what I mean by agile is we should be able to upgrade or downgrade infrastructure at any point without worrying about the cost. Right? So you got that. And number three is I'm looking for a you know a simplified platform like a unified platform where you know just imagine like our data engineers, data analyst and the AI engineers all working together in one place you know and it should essentially simplify our data workflow all together. So that's the that's the most important thing. So does it sound absolutely clear to you? >> Yes, it's very clear. I am excited to start work on this. >> Great. >> Now that you're clear about problem statement from this point on you have two possible learning path. The first one is you continue from here learn data bricks foundations and then do the project. This is a linear learning path most suitable for those who don't know much about data engineering. The second one is a nonlinear learning path where you go to project straight away start building it and when you want to refer to any foundations you come [music] back. This works best for the people who wants to get into the action straight away. Timestamps are provided in video description. I wish you all the best. Let's go ahead and create an account in datab bricks. All you have to do is click a link down below in the video description and you will come here. This is datab bricks free edition. They have created this free addition so that learning community can benefit and we truly appreciate that afford setting it up is very easy. I'm going to use my Google account. You can use your email ID as well. So click on continue with Google and I will select my Google account. Say continue and here for personal use get free edition. Click on continue and start the puzzle. Okay. So I think this is the right answer. Hit submit. After a few seconds, my account is ready. Let's explore this UI quickly. Here you can upload uh your data. You can browse public data sets. Uh so you can go to marketplace and look at all the data products and data sets. You can also go to catalog where you will see your tables etc. So they have these three layers where at the outside you have something called a catalog. So this is a catalog. Under that what you have is schema. So you have this default schema and right now there is no data. So let me quickly upload the data. So you can go to homepage, click on upload data button or you can go to data injection here and create or modify table. Okay, click on that and I'm going to provide you this movies.csv file. So which you'll be able to download using a link in the video description below. And this is a simple movies database where you have movie name, industry, release year, IMDb rating and all of that information. So let's drag and drop this file here and it is going to create movies table in workspace catalog. See workspace is a catalog schema is default. And here you will see a quick preview of data. All right. So see I have my title and here it shows the data type. So this is string. This is string release here should be number correct. IMDb rating. It is saying string but we will change it to number. Okay cuz it's a double number. So let's change it to double. And if you just decrease your font size you'll be able to see all the columns in one single view. Things are looking good. Click on create table. Now when you hit a refresh button here you will see this movies table. See so here you have workspace catalog default schema and movies is your table. If you go to sample data it will show you the sample data from your table. Details will show you the metadata of this table. So this is a manage table. We'll talk about manage versus external table later on. You have different level of permissions, policies, history, lineage and so on. I clicked on SQL editor option and I will quickly run some queries. So let's say select star from workspace.default dot movies I want to view fire records. And before you run this you have to connect it to a compute. So I'm going to connect it with a serverless compute. So let me talk about that compute. Uh so here I will go compute right click open it in a new tab. Now in a free addition what you get is a serverless compute. See this option is disabled. If you have enterprise account you can create a compute where you can specify your cluster basically. Okay. How many nodes you are going to have in your uh cluster etc. But we are going to use this serverless compute throughout this course. So let's go to SQL editor and run the query and see you see all your results here. Now serverless compute is sort of like AWS Lambda. So see behind the scene they have servers but all those details are hidden from you. So this way you can just focus on your business logic. You don't have to worry about managing your computers on cloud. Then there is this Genie option. So if you click on it, click on new and you can click on your movies and you can create this genie space and you can ask questions like show me top five movies by IMDb rating and this is an AI assistant AI chatbot where it will take a command in natural language and it is internally converting it to a SQL query and see it is showing you the results. So this is very beneficial. So even if you don't know coding etc. you can just give these commands in a natural language and it will show you the results. We also have this option for data injection uh where you can connect with different connectors you know Salesforce SQL server they have so many different connectors. If you click on see all you will see this entire list. So you can connect to all these connectors, bring your data into data bricks and work on data engineering, AI etc. And for AI they have all these different options. Okay. So there is playground experiments, there is model serving and so on. Jobs and pipeline is something where you can create your ETL jobs or injection pipelines. And if you look at workspace here, you'll be able to create a new notebook. So we are going to use all these options. I'm just giving you a quick overview of the UI here. Now we will look at data frame basics. For this I will go to my workspace and create a new notebook. Now if you have uh studied pandas, pandas has a data frame. Similarly spark has a data frame and we are using python here. So the module or the library that we are using is pi spark. Okay. uh Spark is supported in Python, uh Scala, Java, different languages but we are going to be mainly using Python. Now here you will find this object called spark and if you control enter and run it, it will show you this thing. Okay, so uh datab bricks environment gives you this uh variable this object which is a spark session object. If you are not running in data bricks then you have to do something like this. See you you will usually write this kind of code where you will say okay import pispark SQL spark session and create this. But in databicks environment you get this object by default. Now let's create a data frame by calling this function spark dot table. Okay. And here you can specify workspace dot default dot movies and the return value will be a data frame. Now let's just execute this code. See now it is showing you this data frame and make sure you are connected to your uh compute. So here it will be serverless compute. You can also run df do.show show function where let's say I want to display only five rows. Now this is not very well formatted. Display will give you a proper formatted table but sometimes people use this show method because it is very fast. You can run uh df dot print schema count right all of this useful method. So let me run this one by one. So print schema will give you the schema of your table. So title is a string data type. IMDb rating is a double data type and so on. You can then say DF count and also print DF dot columns. Okay. So let me just print the count first. So number of rows I have is 37. Now you can verify it in your SQL editor. You can say select count star from your table. So let's do select count star from this table. And if you run it here also you are saying 37 rows. Then you can do something like df dot columns which will give you the list of columns. Okay, it's a plain python list with all the columns. Then you can run another useful function df.escribe. This is similar to uh if you use again pandas it has this describe function. So let me use this display method so that you can see the nicely formatted output. See here it is showing you the count okay release year count okay per column statistics. So what is min max? So for IMDb rating my minimum rating is 1.9 maximum rating is 9.3 and my average rating in my movies table is 7.8. We have another similar function similar to this df.escribe. It is called summary. And when you print it, it will show you uh similar columns but it will show you additional data such as all these percentiles. Now how do you do column filtering? Let's say I want to select only three columns in my data. So first just like Jupyter notebook you can add markdown cells here. So I will just say column filtering. You can give it headers etc. And here you can say df dot select and you can give a list of columns that you want to select. Let's say these are the columns and uh I can return this into another data frame called dft trimmed. See here these data frames will be unmutable which means you can't modify them. So I'm selecting three columns and returning it to a new data frame. Now in this new data frame once again you can say show three. They have this another parameter called truncate which is by default false. So let me just execute it and show it to you. So see here I'm seeing only three columns. Okay. And when you set truncate equal to false it will show you this entire text. If my truncate is true this text will be trim. See so that you can see the columns in a compact manner. Now let's do row filtering. So I clicked on text and here I am adding a code for row filtering. And let's say I want to see movies between year 2000 and 2010. Okay. Now I can write queries but I noticed this option of using generate with AI. So I'm curious in trying that. Okay. So let's do Ctrl I and then type in your criteria. Show me all the movies released between 2000 to 2010. Hit enter and the AI assistant will write query for you. See this query looks good. So you will say accept. And here as you can see uh you will use this method df.filter where you will give your criteria. Okay. Release years should be greater than equal to 2000 and less than equal to 2010. I will call this DF filtered. Okay. So let's call this DF filter. Control enter. And see now you're seeing all the movies between 2000 and 2010. Now the same thing you can do it using other code as well where you are importing this call function from pispark.sql.function and you can just say column releaser greater than 2000 less than equal to 2010. Okay. So it will do exactly same thing. It's just there are two different ways of doing it. There is third way by the way. So you can say df dot filter and you can say my column release year dot between see 2000 to 2010 and that you can store it in filter and you can say display df filter. Okay. So again this is the third way of doing the same thing. And how do you print all the movies which are released by Marvel. Okay. I love Marvel movies and I want to see all the Marvel movies. Same syntax. Okay. Here in column you will use studio and you will say Marvel studio and here are all your Marvel movies. Now I want to know how many distinct movie industries are there. See here I'm seeing Hollywood but what other movie industries are there? Well, for this you will say DF dot select you will select the column. Okay, industry is a column and then you will say distinct and this you can assign it to a variable called unique industries and then display function is used to display a data frame in a nice tabular format. Okay. So I have only two industries Bollywood and Hollywood. Similarly you can uh select unique languages. Right. See here if you look at this table there is this language column. So it will show you languages for different movies. So I want to know see here it is saying that my movie data set has movies in Hindi, English, Bengali, Canada and Telugu. Now see my table has the budget and revenue column. Naturally, when I'm doing data analytics, I'm interested in knowing the profitability of each of these movies. And profit is nothing but budget, which means this is the amount of money that was spent in making a movie. Okay? And revenue is the revenue that it generated. So if you do revenue minus budget, you will get profit. So here you can create new column by saying DF dot with column and this column will be profit and it is nothing but column revenue minus column budget. Okay. And I will assign this back to the same data frame. In the same data frame essentially I am adding that extra column. So control enter. And here you will see this new column. See profit. So see for this first movie revenue was this much I think 100,000 and 70,000 so profit is 30,000 with column is a function that you will use a lot in pandas if you want to create a new column you will say df then that column and you know if you're creating it using existing column you will say df existing column do apply here the syntax is little different but it's easy to remember right you just say df do with column you specify ify your new column name and here you specify your expression. Now when I look at revenue column I feel like I want to rename it to total revenue. So it shows it's it's a total uh revenue generated by that particular movie. And you can do that easily by saying df do with column rename. See if so here uh I will just change revenue revenue to total revenue assign it back and then say df dot print schema and see here it is total revenue. In the last lecture we did some basic analysis on the movies data set and by the way movies data set you might have seen in our SQL course if you have taken SQL course from code basics. Now we are going to give some name to this particular notebook. So we'll call it basic operation. Now in this lecture we are going to see how we can read and write from CSV file. So we have given this another file called order dot CSV. So if you open that file, it's a different data set. It has some kind of orders from some e-commerce company etc. Right? Country, order ID etc. And we are going to load this data in data bricks. Okay. So we'll go to data injection here and then instead of using create or modify table, we'll upload files to volume. Why? because we don't want to create a table out of this CSV file. We want to upload this file as is here in data bricks. Okay. So click here and drag and drop this order files here. You will see what happens actually. Click workspace default and then you have to create a volume. So create a volume. Let's call it raw data. Okay. And it's a manage volume. Okay. And create. Manage volume means like here we are uploading the data to datab bricks and data bricks is taking care of management. So click on upload. And once it is uploaded go to catalog workspace default and here you see in tables you have movies table but in volumes you have raw data and see the CSV file is asis. We did not convert that to a table. Now let's create another notebook. So I think this notebook was basic operation and this notebook I will call it read write CSV. Okay. And we already have spark object. Right? So you can just say spark dot read dot csv. And here you specify the entire path and then df. Okay. It will take some time to run this code. Okay. So, it read the file. But there is a problem. The header is considered as one of the rows in that table. Okay. So, to tackle that problem, you can supply an option. You can just say option header is true. And when you do that that it will the first row it will take it as a header. Now you will see another problem. See this order ID is integer but here it says the data type is string. See you can verify it by the way by also doing df.tprint schema. By this time you already know about print schema function. It just prints the schema. Everything is string. Okay. So you can infer the schema as one of the options. Okay. So you can supply another option. So this is the syntax spark read dot option in bracket you provide your option. If you want to supply another option you again say option right option dot and here you supply the option and the line is getting bigger. So let me just do this. So this way I can supply different options in different lines. Okay. So here you can just say infer schema is true. You want spark to infer the schema based on the content in that column. So now you can see order id it's integer. Then quantity it is integer. Sometimes instead of letting spark infer the schema you want to specify the gimma. You want to say okay order date is of type date, country is of type integer. Okay. So for doing that you can specify the schema by writing this code. Okay. You don't remember need to remember all this code. You have AI assisted coding. So you can generate this code easily. So here from pi sparksql you are importing functions as f and types as t and then you're creating the schema. Okay. where you're saying okay my order date is of date type my order ID is of integer type and so once you have done that you supply another option called dot schema it's not an option it's it's a method you just say dot schema is this particular CSV schema and when you run that see it will take that particular schema now sometimes you want specific format for a column let's say this is a date right you want it to be in a mm dd y format. So once again there is an option. Okay. So here you will have option called date format and usually date format is like see here I'm saying mm dd y y right so the date format the usually accepted format is y mm and dd and that is what we have already but I'm just showing you so see like you have date you have year then month and then date now whatever we just did. You can do the same thing using a little different syntax also where you will say spark read dot format. Right? We were saying read dot CSV previously. See here we are doing read and then CSV. Instead of that you can say read but in format CSV. Okay. So this will do the same thing. It's just a different syntax. And then I want to write data frame to let's say park format. for that I can you know how you have read function you also have write function see and you can specify your format so let's say I just want to write in in a park format so I will say df dot write dot mode override means whatever file you're writing to if there is already a file present there just override and here you will say park and you specify the path so in the same path I will write this park file orders. So it has returned it. So now let's go to catalog workspace default volumes raw data. And here you see park. So so you see this thing the data is now returned in the park format. We just worked on some basic data frame operations in data bricks. And you might be wondering how is this different from running a plain Python notebook on my local computer. I can use pandas and do the same thing. Well, the key here is distributed computing. Data bricks and spark provides distributed computing. And just in case if you don't know what this is, let me explain this by simple example of my own wedding. In any Indian wedding, food is a key highlight. So in my wedding, we wanted to prepare 2,000 puris and we initially hired one chef. But then we realized that he's going to take four hours and what if he gets sick? You know there is a dependency. So then instead of one we invited four chefs and we gave them 500 puris each and they worked in parallel and we were able to make 2,000 puris in 1 hour. Now this is a common sense concept right? You can make your work uh parallel and get it done in a quicker time. Uh similar thing you can do it with computers. Let's say you have this data set where you have ticker, you have price and EPS and you want to compute PE ratio. You can give it to your computer, right? You can run pandas code and you can compute it. But what if you have 40 million records? Your computer will probably hang, right? So what you can do to solve that problem is you can divide your data into batches and you can give it to different computers and they will do all this computation and you can aggregate these results. Okay. So this approach is called map and reduce. So this is map step and this is a reduce step. So just to make it general, you have a bigger task, you give it to your main computer, your driver computer. It will divide it into multiple task as if you have a team lead and they have four team members and it will divide each of these subtasks to them. Okay. So this entire thing is called cluster. So you have your driver computer and your worker computers. Okay. So this is a driver computer, you have worker computer and this is what is distributed computing. And one other benefit uh other than getting efficiency in speed is that let's say something happens to this computer right it goes down then this driver or team lead can assign that task to somebody else. So you get something called fault problems. It's similar to you're working in a team one of your teammate is sick and your team lead will assign the task to some other team member or they will hire a new person. Okay. So let's say going back to my wedding example, what I can do is if I I want to prepare 2,000 puries. See, previously I said I will hire four chef. But instead of hiring four chef, how about I hire caterer and caterer can have multiple chefs. I don't care. I just talk to Kater. This person is a single point of contact for me. And as an output, I get 2,000 puris. Okay. Now let's say if some chef gets sick or something, Kater will take care of assigning that work to some other chef. they will get new shape etc. So distributed computing is the process of splitting the large computing task in a smaller subtask and executing them in parallel. Okay. So that work can be uh done efficiently and the whole thing acts like a single system. So what is Apache Spark and Hadoop then? Well Hadoop is a distributed computing framework folks. It lets you do the same thing but it is very disk heavy and slow. So when Hadoop was going through all these problems at UC Berkeley AM lab this person Mate Zahera along with other people working at UC Berkeley invented Spark. So Spark is a unified engine for large scale data analytics. It is basically a compute. Okay. Uh and it's an open-source distributed compute engine designed to process large scale data quickly and efficiently. So it solved that problem with Hadoop. Hadoop was very disk heavy and spark does everything in memory and due to that it is very efficient. Okay. So if you look at this particular diagram let's say you want to do this computation you can write a simple code in pi spark. So pi spark is a python module that helps you work with spark. You can write a simple code that you read this stocks dot csv file and you simply divide it right. You can say with column and divide these two columns that you have and internally it will assign it to your computers and all that. You don't have to worry about the details of distributed computing. You focus on your business logic uh where to divide the work how many computers like you know all that details will be taken care of by spark. So essentially you give the code to spark uh and you set up a spark cluster and then this driver program will give the work and you will produce the result. So it is very similar right. So spark is similar to a keter. So look at these two diagrams. It's very similar. Okay. So it's a distributed comput engine that abstracts see this is very important. It abstracts the complexity of parallelism letting developers focus on a business logic and it is very fast compared to Hadoop in memory and simple API. So what is data bricks then see if you want to run Apache Spark you have two option one is self-hosting. So you basically go to the spark website, you download the docker image or you download the uh zip file and you set things up uh on your computer. Okay. So this is like me talking to caterer, photographer, decorator individually and even I will go and uh you know if decorator says okay I need this much stuff I will go and get it from the market. So I'm also involved in this management. Okay. And this management means uh when you're doing self-hosting you have to provision physical and VM nodes you are installing and configuring spark Hadoop yan etc. So all this management of setting up spark cluster and making it functional is involved in self-hosting. Uh instead of that just imagine I hire an event planner and event planner will talk to all these people and she will take care of all of those things. Okay. So there is less headache for me. So this is called manage service. So in manage service you take away the cluster life management burden. You just say give me cluster of X workers and it will do that. Okay. So this manage service is nothing but data bricks. So when you go to data bricks you just create a cluster you create a compute and that's it. Everything else all of these things are taken care of by that. Okay. So this is just a diagram highlighting the difference between the two approaches. Now we are going to run SQL queries in Spark which is something you will do a lot either as a data engineer or as a data analyst. We already have movies data set and if you want to run SQL query on top of that you can do it in two ways. The first way is let me just create a markdown cell SQL using Python API. Okay. So there is a Python API through which you can run this SQL and we have spark object already correct. So on that you can simply run spark.sql select star from workspace dodefault dot movies where studio is equal to marwell studios. Okay. And this is it. Now it will return a data frame. So you can say df marvel and here df marwell dot show. So let's run this code. Okay. I got this error because I have quote here and code here. So you can just use okay you can use a single code here. Okay. Let me do that. So single code single code and then double quote. You have to take care of these quotes in Python. You can also use triple quotes if you have a longer SQL query. See it's wonderful, right? So we used Python function spark.sql and you can specify your query here. The second way is SQL using percentage SQL. Okay. So in the code when you say percentage SQL percentage SQL you can now freely write your query you can say select star from whatever let me just copy it this particular thing here and it will execute it. This sounds more like you're having your database tool your whatever Oracle or uh MySQL workbench and you are directly running your query. See and this result will be stored in underscore SQL DF so that if you want to use it later on you can use it. Now let me create a temporary data frame. So I'm not going to use any movies which is table actually in data bricks. I'm going to create a data frame onfly for my weather data. So I have this Python array. Okay. And in spark you can say spark.create dataf frame data. So you specify your data. Then you specify your schema and that will create your data frame. Okay. And here I'm just doing some uh formatting for my date column. So when you run this it will create a data frame for my weather data. Now I want to run SQL query on top of it. How do I do that? Well, you can do that by creating a temporary view. So you can say df create or replace. If you already have a view with name weather in your session, this will replace it. You can also say create view. But we always use create or replace temp view. Okay. Now when you run this, let me just show you what happens. Now you can say percentage SQL and you can run a query on this view. So let me run this query. Select event and you know average temperature whatever from weather. See from weather. So this weather is this view that you have created and group by event etc. Okay. So now remember that weather is not a table in my data bricks. It's a data frame from which I have created this uh view and the name is weather. And see I'm able to run the query. So this is very cool. Now if you look at other options for the function see it has create global temp view also. So see the scope. So let me say global weather. Okay. So this is session scope. What it means is in my session in my current pi spark session in this notebook this is valid. So if I go to a different notebook and if I say if I run this query it's not going to work. But if I done create global temp view then on global weather I can create a another notebook it should be in the same cluster but another notebook on which I can run my query. Okay so that's the purpose. Now you can run the same query using Python API also. Right? So here we are using percent SQL and for Python API folks it's super easy. Just say spark.sql SQL and your query and that's it. So what we learned in this session is there are two ways to run SQL queries in Spark. One is using Python API which is Spark.SQL and the other one is percentage SQL. As a data engineer or data analyst, you will be running all these queries a lot either for ad hoc analysis or for your pipelining purpose. Let's now look at how you can perform joins in Spark. You can perform joins using SQL query or using Python APIs. So I have created a new notebook and I have two data frames that I have created on fly. First one is customers. Second one is orders. Okay. And here I have specified schemas and then I use spark.create data frame. When I display them they both looks like this. So in customers data frame we have one record where customer ID is null. In orders we have reference to some customer ids which are let's say null and look at this five customer ID it does not exist here so this kind of inconsistencies will help us demonstrate the concept of joins better the APIs look pretty similar to panda's API so here I can just do df orders dot join df customers on some column so how does these two column relate to each other well through customer ID. Okay. So in orders you have customer ID and see customer ID is a primary key in this table and you can join uh both of them using this customer ID to be explicit you can specify the name of the parameter. So you will say on customer ID and how do you want to do it? Well there's a parameter how where you will say left join right join inner join. By default it's inner join. Okay. So, df inner and then display df inner. See it performed a join. Now, inner join means you should have that customer ID in the second table which is your customer table. Otherwise, it you will not see the row. Okay. So if you look at both the tables here uh you will notice that in the orders table this particular row right see this customer ID is not present here also this customer ID is null and you can't say null is matching null here okay null means nothing so this is also not matching so only matching rows are 1 2 3 4 5 and six so you have only six rows so when you perform from inner join these rows with order ID 104 and 106 will not be displayed. See, so here you don't see 104 and 106. Okay, so that's what is inner join. If you do left join, okay, so let me do left join and see what happens. From your SQL concepts, you know that left join will keep all the rows on the left table. So left table is order table. Okay. So order table have order ID 1 0 1 to 108. So it will keep all of that. See 1 0 1 2 3 4 5 6 7 8 it will keep all of that and in a customer table if it doesn't find something right see for this null customer ID it did not find anything. So these are the these three are the columns from customer table and put null here. So similarly you also know like how write join performs and so on. Now one thing that will help us write our queries better or perform our join better is this concept of alias. So when you have this data frame and when you do alias O so this will now this O is nothing but it's just another data frame that you have created it's it's exactly same as this one but this alias O is similar to the alias that we use in SQL queries when you're performing joins also when you're performing join with the same table this kind of alias will be useful. So let me create alias for both our customer and order. Okay. And now with that you can perform the same join that we did here, right? The same one. But you can use now the alias. Okay. So this is pretty kind of simple. But let me show you another query where this is particularly useful. See here when you look at all the columns, the country column is kind of common in both of the tables and this is creating ambiguity. You don't know which table this country column refer to versus this right. So you you want to say okay this is my orders country or shipment country you know from where you are shipping it and this is my customer country. So how do I do that? So the first thing I will do is this. See let me do this and let me create a new data frame called df inner clean. And whenever you want to write multi-line code, you can use this kind of bracket. Okay. So that will help you. Now you can say dot select. And here you can specify the the columns that you want to select. So so I want to have see customer ID, order ID, amount. Okay. See customer ID. Okay. Let me just remove this. What I want to have is order ID then customer ID, amount, country name and VIP. Right? Now these two country columns are are something we need to dismbig. So let me just pull them in the new line. Now I'm going to say f dot call. See it is doing autocomplete. So f dot call order dot country is order country or you can just say ship country. Okay. And do the same thing. Okay. Let me remove it. Do the same thing for this but this time it is customer country right C dot country is cust dot country and then you will say display df inner clean let's run this control enter is the shortcut that you see now you clearly know that this particular column belongs to my order table and this column belongs to my customer table. The value of how is either left join, right join, inner join or full join. So let me just show you the full join here. So full join will show you everything. It's like a union between two sets. Okay. So you can say display this and here I will have full join. So let's see what we get. Okay, we got 10 rows. Why 10 rows? Because it will give me all the eight rows from the order table, right? All these eight rows. And then on the right hand side, if you check there is this row with customer ID four and we don't have any order here, but it will also show that and there is another row with customer ID null. So eight and these two, right? So all eight rows it will show and it will show also these two. Therefore you have 10. So see last two is ghost and Diana. You see Diana with four customer ID and this is ghost. And for this you don't have any order. So these these values are null. See null null null. And for this particular row you don't have any customer information. So you see null. See you you see these three null. And for customer ID five we don't have any customer ID in that table. So here also you are saying this three null values. So that is the behavior you get by performing the full join. Now there is a another kind of join where you say left semi and what it will do is it will show you orders with a known customer. What it means is known customers are only these right 1 2 3 4. So one okay so one okay one then two then three so how many rows get this many right six 1 2 3 4 5 6 so you will get six rows see 1 2 3 4 this is different than left right because if it was a left join you would have also got this five five row but here you don't get it so that's why it's called left semi which is orders with known customers and exactly opposite of This is left and die which means exactly opposite. Basically you get all the records from the right hand side table. Okay. Uh or or rather see customer five is not present in customer ID table. So it will show you that see customer five you see and then it will also show you null this null. So whatever customers which are not present in the customer ID it will show you that. So it's an exactly opposite of left semi. You can also perform join using a composite key or multi key. So let's say I want to perform join using customer ID and country. Okay. So when I do that I get six rows. Okay. So here see customer ID is one and country is this in right. So one and in. So it will perform join with this particular record Asha. Then you have two and for two you have US US. So two US so it will perform that then three you have Canada so three can CN okay Canada or China I don't know what it is but this is using multiple keys to perform the join all right that's all I have you can do much more but this is good enough for the basic tutorial spark is popular because it has lot of internal optimization in this chapter we are going to understand the internals of spark so that we can understand why spark is so widely used in the industry and this understanding will help you with interview questions as well. Okay. So this entire chapter is very important from the interview standpoint. In this particular lecture we are going to talk about reading spark plans with dot explain. If you have used SQL, you know that in SQL, if you want to look at the query plan, you use explain. Similarly, when you are writing queries in Spark, there is this dot explain function that will give you the internal execution plan. I will directly execute the explain method and we'll read that plan. So, I have read the movies table. You know, we have used this table before. And here I'm running a query to get all the movies which has release year greater than 2010. And then I am getting only three columns out of it. And I'm calling that df narrow column. Okay. Now see our data set is small but let's say this data set had millions and millions of records. It's going to execute them on multiple nodes or multiple computers. Okay. So it needs to do some kind of optimization. So here when you run df narrow.explain this method now takes bunch of parameters. See if you don't supply anything it is going to print something called a physical plan. I'll tell you what physical plan means. But if you say extended it will print both logical and physical plan. So that sounds interesting. So let me let me print that and then I will explain what it means. So extended okay and control enter to execute it. So see it printed this particular thing which sounds interesting. It has passed logical plan analyze logical plan optimize logical plan and a physical plan. Okay. So let me explain what this thing is actually. So when you write any query right see we use Python API to write that query but you can also do percent is SQL and write that query. So when you say select ABC from table X what you have is unresolved logical plan and then you will use catalog. So catalog has information on your databases table etc which will create your resolved logical plan. Okay. So here let's say if I say that uh select you know in un unresolved logical plan if I have select a b c columns from table x that is my unresolved logical plan but when I convert to resolve logical plan it will look into catalog to see okay do we have actually x table and if we have that table do we have ABC column if they are not there then it will throw for the error. If they are there then it will uh modify that query plan. I I'll show you what it will do. It will put like ids and stuff to make it more concrete. After that it will create optimize logical plan using logical optimizer. So similar to how you will optimize your SQL queries, you will optimize your result logical plan and I'll show you exactly what it will do underneath. Then you convert that to a physical plan. Physical plan is basically how do I take this query and distribute it to my multiple nodes so that I can get the best results in a minimum amount of time. Okay. So it will come up with two three strategies or or n number of strategies. Okay. That number can be anything. And then you will use cost model to figure out which plan is going to be the best in terms of your performance, your compute cost and so on. And then you get the best physical plan and that best physical plan is something you will send it to your cluster for execution. Now here I have mentioned AQE which is adaptive query execution. We will talk about it later on in this chapter. But just consider this green box as an evaluator which can figure out okay which plan is the best and then it will send that plan to cluster for execution. Now from unresolved logical plan to optimize logical plan there is a component called catalyst query optimizer which will come in play and it will help you create the optimized logical plan. Okay. And when it comes to execution see here we are just creating a plan. We are not executing it. The execution happens here in the last block. At that time we will have photon query engine which is a high performance vectorzed query engine written in C++. It is relatively modern and that will make sure that your query gets executed really fast. If I have to summarize things differently, you have SQL query. So the role of catalyst optimizer is to create optimize plan and the actual execution. See, remember catalyst optimizer doesn't execute. It is just doing you know like optimization. It's like you're sitting in a room and you're trying to figure out okay it's like going to Google maps and figuring out different paths. Okay, from city A to city B these are the three different paths. So catalyst optimizer will just optimize you know it will create the best uh route from A to B. And photon executor is actually you're taking a car and you're going from a A to B. Okay. So I hope you're getting this point. So let's look at past logical plan. Past logical plan is nothing but your unresolved logical plan. And with the help of catalog you are getting resolved logical plan which is this analyze logical plan. Okay. So see here do you are doing filter on release year 2010. Okay. And project means select. Project simply means see whenever you see project it means select. You are selecting only three columns. Okay. Our movies table has many more columns but we are selecting only these three columns. Okay. an unresolved relation which means you are saying workspace default and movies that is unresolved. Okay. So here we don't know whether you have this table or not. Okay. Because we are in a unresolved logical plan. Now with the help of catalog we are creating resolve logical plan which is this. So now we looked into catalog and we said oh yeah there is a table called workspace.default.mov. See, so it found a table and then it assigned all these ids to the columns. See, these are the ids which will identify the actual physical columns and it is in a park format. And then out of all these columns, you are interested in only these three columns, right? See title, studio and IMDb rating. Why do I have the fourth column? Just pause the video and tell me. Well, we have fourth column because we need it for filter purpose. Okay, so that is the reason we have fourth column. And then you are doing a filter. Okay, release year should be greater than 20110. And after doing the filter you are selecting only see again folks project means select. So you are selecting only three columns right? So we had four columns but in the final output we are selecting these three columns. So that is your analyze uh resolve logical plan. Okay analyze logical plan and resolve logical plan is the same thing. Now you're applying optimizations and you create optimized logical plan. So let's see what kind of optimizations we applied. See here what we did is we had this table right. So in this table u we had all these columns we applied filter. So first thing we will do is we will apply filter. Why? Because that way you are reading less data. See if I read all the data and have it in memory and then filter then see eventually I care about filter data. So the extra rows which were movies less than which were released before 2010 I read it in vain right it was not useful. So here we are applying a filter but notice this we are also checking is not null because if it is null then it's not going to be greater than 2010. So we can ignore it. So this is one condition that is added by our optimizer and then you create the best physical plan. So the best physical plan is nothing but this physical plan. Okay. So let me just scroll. Okay. So I hope you can see it. Now let's check this. So you have to always read from bottom. So when when we are looking at this particular thing, read from the bottom. So see it is doing photo scan. Again photon is your actual exeutor. you know it's a vectorzed query executor. So it went to your table where the data is stored in park format and it what did it do? Okay so it will scan all these columns. See only four columns. See it is scanning only four columns. It's not scanning all the columns and then it is applying all these filters. Okay. Format is spark location is see this s3 actual location of the table that you're reading. Okay, it can be table it can be file anything and then then there are bunch of parameters here. All right, so I hope you're getting some idea folks. It might be overwhelming. You get some understanding. Don't worry as you progress as you work with this more you will get better understanding. So I'll encourage you to write some different queries here then print the plan and talk to chat GP if you