Getting Started with PySpark for DP-700

[00:00:00]Yeah, great to have you all here. Hello everyone who has said hi in the chat and of course everyone else. Thanks for joining the session. Uh this session will be about pi spark and everything you'll need for passing the DP700 exam.

[00:00:17]So we won't be digging into what happens under the hood in Spark or how the actual processing is done but we are focusing on the practicalities with Spark. So how you can get things done and how to read the syntax and how to ace your exam when it comes to the questions about the pispark syntax there.

[00:00:42]So first of all uh this session will be about 45 minutes hoping to get there. Uh other than that we'll be focusing on the syntax recognition. So basically for the exam you won't be required to read uh write any code. You'll just need to understand the syntax and select the right right options to get the working code there.

[00:01:09]Yeah. Uh there are a couple of different types of questions in the exam. There's of course the multiplechoice one and then there are these you have multiple options and you need to fill in fill in an existing code and we'll be taking a look at both of those kind of kind of quizzes today.

[00:01:32]But let's start. Uh first of all pi spark in fabric notebooks run on spark if it's the selected language. Uh we are currently living inside a notebook because I thought this is the best way to keep on the practical side. And here on the top we can select the different languages and if we select anything under this spark uh area here it will be running on spark but the language will be different.

[00:02:02]There's Scala, there's SQL, and there's even R which I think nobody uses, but it's still there. Then if we choose Python, it will be running on a different engine, a bit lighter notebooks that you can use for well even for data data processes, but I think everything like API calls or whatever kind of maintenance tasks would be nice there.

[00:02:29]And also there's the TSQL that runs on the Polaris engine, but we won't be going too deep into those. And whenever you are have started your session here, it also creates a spark session for you.

[00:02:43]So when you do this outside of fabric for example, uh you need to build those sessions yourself. But here everything's handled for you.

[00:02:53]One important thing is that we are working with data frames. uh and this display command here gives us a rich grid to view the data and we'll be taking a look at that also.

[00:03:07]Uh first of all uh data frames are immutable. So we cannot do any like processes to the data frames or any processing to the data frames. We are always creating a new data frame and we are assigning the changes there. And I'll also show a example of that later on. And the transformations are lazy.

[00:03:32]And this means that even though you would have like billions and billions of rows of data, when you are writing the code and running the cells, if you aren't using something like display or joins or something like that, uh the cells will always be executed in like 1 second because Spark is just creating a plan on how to process it. And when you are doing something like writing the data or displaying the data then spark starts processing. But that's all that we are going to cover on how Spark works.

[00:04:07]First of all, let's take a look at the reading data syntax here. So there are a couple of ways. Uh the most simple way is to just start with the spark read.

[00:04:19]And this is uh just a small snippet that will then take some arguments from us or some options and things like that.

[00:04:30]And I'll check the questions from the chat at some some good points here but yeah I'll check them a bit later. Uh yeah uh we are first of all providing a format and this can be a CSV. For this demo I have created a lakehouse with a little CSV file in the file section. Can actually take a little look here. So it's just a simple there's uh eight rows of data and we have the headers there.

[00:05:08]So we have some order data that we'll see later on in the notebook. Let's just minimize that so we have more space. For example, here we are reading a CSV. We can also use like delta here as a format. If we want to read from delta tables, then we [snorts] have different options. For example, if we are using CSV files, we have the option to specify if we have headers in the file. And as we just saw there where are the headers and we just pass the value true and then we have the dotload that works. We need to first specify the format and then we pass the dot load here and then we give the path. And because we have uh this attached here as our default lakehouse in the notebook, we can use this uh like this shorter path. So we don't need to give the full APFS path for example. So it's just a file section of the default lakehouse and then the file name here.

[00:06:17]If you have folders, they will also be part of this path.

[00:06:21]But anyways, we can also uh for these different if we are not working with delta tables but we are working with other types of files, we can have these a bit shorter versions where we don't pass the format but we have this CSV for example and then we have of course JSON and things like that. So we can do a bit shorter syntax there. And then it takes everything here within this one parentheses and uh delimited by these commas here.

[00:06:59]Or we can read a full table if we have a table already.

[00:07:04]We can read a table just pass the table name and we have the again the default lakehouse here which we are pointing as we don't specify any other paths here. Yeah, let's run some code. Here's just a read CSV and we have the path to our file. We have the headers and we are inferring the schema which means that we are specifying data types or spark is doing that for us. We don't have to specify the schema. And here on top, we'll see that Spark has specified some data types here. These are uh integers.

[00:07:42]Then we have a couple of strings. Then we have some decimals. And then we have again some integers and strings. And as we can see, it's just some sales data.

[00:07:53]We have the order number.

[00:07:56]We have the product that has been ordered. We have the category for the product. We have pricing quantity and we have the country of the sales. So this is what we can do with display. We are basically defining this data from this path into a spark data frame and we have named our data frame DF just for quick use here for the demo but please use something that actually makes sense in a actual scenarios but yeah it's a just data frame that we can then start referencing everywhere.

[00:08:35]So here we just do the display and we can see it.

[00:08:40]Uh there was a question in chat. Can you share this notebook? Yes, I can. I will do that later and provide the link somewhere. Thanks for the great question.

[00:08:50]Uh yeah, let's go to do some transformations. So I would call this the big five. So very very important things. I would even say that the two on top are the most important ones. So with select we can select just specific columns for from our data. So as we saw here above we have this six different columns and we have eight different rows. So select would let us select only specific columns from that data. Then we have the filter or where these are pretty much the same. I always use filter. It's just something that I'm used to. And with that you can filter rows from the data. So with selection and filtering you can do very much already. Uh then you can add columns. So you have which you just pass the name of the column you want to create as a string and then you pass some sort of an expression that you want there to be and we'll see examples later.

[00:09:58]Uh we have the with column renamed also uh which allows us to rename a column we already have in our data frame.

[00:10:08]Just giving it the old old column name and the new column name.

[00:10:13]Then we have the order by. This is very useful for some analytics work. Not so much for things like data modeling for silver or gold layers for example. let's say because you don't really care about the ordering in those cases.

[00:10:33]Yeah, those are pretty much the basic things that allow you to do transformations and with those we are pairing these pi spark SQL functions.

[00:10:44]This is the most important library for all your data work with pi spark. So what you have here is these are just a couple of examples. You can import them naming it that we are importing from this library and we are importing these specific functions. For example, call it lets us uh reference to a specific column and sum is used for aggregation, summing up things and so on. You get the idea. But how we actually want to use those? uh we want to use this that we import the library as f. This is the most common way and this lets us take more control. For example, if you just write somewhere that we are using sum, it will always default to the this sum that we have now uh imported here. But if we were to want to like use the Python sum just the basic one, we couldn't do that anymore because it would always default to this one. But if we have the f we can use it in a way that will be shown here that we use the f do call instead of just calling the just the function name like that. But yeah those are the that's the most important library here.

[00:12:11]And how do we select things? Well let's actually run these cells before I forget. We are importing the libraries.

[00:12:19]very important part and what we do here we are calling you always think about it this way when you are reading the syntax when we are for example using display we want to pass the display a data frame.

[00:12:36]So here we are calling a data frame and the data frame has its own methods like the select method and whenever we have called the select method on this data frame this part of the code will still return us with a data frame. So what we are doing now is running the cell we are selecting product and price columns. So that's how we do here and we can see we are displaying this but something that was that we have this immutable data frames.

[00:13:12]So what we did here we didn't assign this anywhere. So if we were to display actually just the data frame it would still have all these all these columns here. So that's something to keep in mind. If we don't assign the data frame to a new data frame, then we cannot have the changes there.

[00:13:37]But let's move on. We have these filters. You can use filters in different ways. We have these four examples here. Uh it doesn't really matter uh which one of these you use, but just know that these all can be used. We have this using this without the f dot. So we are using just the call function here. Uh filtering the price.

[00:14:02]We are using the f call um and it's exactly the same as this one.

[00:14:13]Then we are passing this filter a SQL like expression here. And here we are referencing the data frame with using the df dot price. So we are calling the price column of the data frame here. And if we run it like this, we should have all of these displays here look the same. We have five rows and the same data all across the board.

[00:14:45]Let's move on. Now we are actually assigning the changes to a new data frame. So what we are doing here we are creating a data frame called DF2. We are calling the width column method of the data frame.

[00:15:05]Uh someone's saying we have double voice. Is that correct?

[00:15:11]Is there an issue with the voice? Could someone confirm in the chat?

[00:15:37]Okay, thank you.

[00:15:39]Uh so we have this with column method here and that lets us add a column. So we are now adding a total column here to our data frame. We are using this calling these columns. So we are using this call function here and selecting the price column and we are multiplying it by the quantity. So we get the sales total here and because we are now defining it as DF2, we are going to also display this DF2. So this new column will be visible here on the right side.

[00:16:15]And you can see that it has been now added here. It's not added in the DF, but we are defining it here for the DF2.

[00:16:24]So that's a very important one. Also, it's quite common to use the width column as well.

[00:16:34]But how the exam code or actual spark code will look like in your projects and the exam uh you are chaining these these code snippets. So basically when you do this df do with column it ends here and you are returned a data frame with the new column. Then you can imagine that the data frame was now here but it just has the new column with it.

[00:17:05]Then you apply the filter and as well uh it ends here and you again have like this data frame here and now it has the column and it has also been filtered and so on. Everything happens always you apply a method and you are returned a data frame with the method applied and then you are doing something more to it. uh after that order matters. If you are selecting for example here on the top we would select only the product and the total and we would try to add this with column here uh using the price column as well. Our data frame wouldn't have that column anymore if we selected it away. So keep that in mind. It's very important to follow a specific order when doing these things.

[00:17:56]If you are selecting something away, you cannot use it later anymore.

[00:18:02]Uh also as we are using Python very important here when you are doing comparison you want something for example category to be specifically electronics you need to use two of these equal signs. Uh one is for specifying something and then two are for comparison.

[00:18:25]And then you have this order by which is very handy when you are looking for some specific like maximum minimum values or something out of the ordinary that would stand out.

[00:18:40]And by default it will be in ascending order. That's also important for the exam and also for any analytics work you are doing. So ascending by default but you can also uh change it. For example, this is just a function that we have imported. So we are passing this function a column total and it will instead order it in a descending order. But if we were to like just remove this part here then we would be seeing it in ascending order.

[00:19:20]We have a question. Can we use in if the category is more than one? Uh you mean we would select multiple categories?

[00:19:29]Maybe we can do that. uh we won't be going too deep into that but there's uh for when we are calling a column it has its own methods and we can for example use this is in method which then takes a list of things. So we can have electronics, we can have whatever the other categories were here and then that would be the condition there. But this is just a simple comparison.

[00:20:01]And as we can see now uh we only have the electronic products. We have laptop, monitor, keyboard and the total of the sales is ordered by descending order.

[00:20:13]And we also have just the product and the total columns here. So looks like it works as it should.

[00:20:22]So first quiz uh and for these quizzes uh I say it's not as important to just try to look for the right answer right out of the bat but rather look for the answers that are absolutely incorrect so you can scratch those out and you can be certain that the one that is left uh will be the correct one. So we want to have this kind of uh data in our result. Let's just look at what we have here to begin with. So first of all I would look that we have this total column that we have been using before.

[00:21:07]So we need to create a column probably using the width column here. Uh then we have just if we look at these sales uh we have like mouse and we have a price of 25 and quantity 10 it will be probably this.

[00:21:31]It will be equal to this one here. And if we kind of look at the data and try to find some patterns, we can see that keyboard, desk, mouse are the ones that we want to be selecting. And there's only one of each here. So mouse seems to be electronics. Yeah. But also laptop is electronics. So we won't be probably uh filtering by category.

[00:21:58]Uh then if we look at this, we have mouse, desk, and keyboard. and all are from country Finland here and also that we have the total. So it would look like uh we want to filter by country being Finland and we want to calculate the pricing uh the total price. So price per quantity or price multiplied by quantity and we also want to select the product there. So we have a couple of options here. Uh because we are working in fabric, this one will instantly show that this index is actually incorrect.

[00:22:41]But do you notice what is the actual thing that is incorrect here? I'll just give you like a couple of seconds to think about it and you can give your answer in the chat if you notice what is wrong there. I think this underline here gives some some sort of a hint there.

[00:23:07]Yeah. Uh if we look at the second one, we are starting by creating a column total. So I think we are going in a great direction. We are uh selecting the price column and multiplying it by the quantity. So we are getting the we are getting the totals there. Then we are applying filter by country. I don't think it's wrong at this point to do the filter. Then we are selecting the product and the total which were the ones that were requested here. So that makes sense.

[00:23:48]And then we also have this sort here.

[00:23:51]And we are sorting in the descending order. And if we go take a look then I think it looks pretty good that this is in descending order. So first glance on the option B would look like it would give us pretty much what we want.

[00:24:11]If we take a look at then the option C.

[00:24:16]We are first selecting the columns product and total and after that we are creating a column called total using a price and quantity columns here.

[00:24:31]Then we are filtering by the country.

[00:24:32]Finland looks correct. And then we are ordering by in descending order. So there's something in the order of operations that I would say doesn't look quite right. I'll let you think about it for a second.

[00:24:49]And then we have the option D. Here we are creating a column uh total using the price and the quantity. We are filtering by country. Finland looks correct. And we are selecting product and total as we wanted to.

[00:25:08]And then we are ordering by the total.

[00:25:11]So, this looks pretty close to what we want. But there's also one thing that doesn't really add up.

[00:25:23]So, let's start running the cells. If we run the ASEL, it uh fails instantly. And as you can see, we have just one equal mark when we should have two. That's the first one.

[00:25:36]And it will just fail.

[00:25:39]uh when we run the option B it looks at exactly what we want. So we have the keyboard desk and mouse we have them from the descending order from the top. So 400 350 250.

[00:25:54]Uh if we run this one you can notice that we are first selecting the product and total columns and then we are trying to reference these price and quantity columns that don't exist anymore. So we should also yeah unresolved column with suggestion uh name total cannot be resolved. So that's that's a problem there with the C and for the D it is very close but as mentioned earlier order by will use the ascending order uh by default. So this will be in different order starting from mouse going to desk and keyboard.

[00:26:37]Yeah, that was hope you were able to follow follow along and of course this will be uh recorded as well so you can come back to this and I will share the notebook as well somewhere. I'm not exactly sure yet where but I will do it.

[00:26:58]Yeah. Uh let's hop into aggregations. So this is just something uh quite fast also uh when we are creating aggregations. So let's say we want to like get the total revenue by category.

[00:27:15]For example, we want to group by category.

[00:27:18]So our data frame has the method group by. If we do not then pass it any aggregations after that, it won't be a data frame because this group by will just return us with a grouped data uh type of variable or an object. So it doesn't really give us anything at that point. So we need to first use this group by method that will return us with a uh grouped data object that we can then uh pass an aggregation. So here we are using the dot aggregate and the sum price and using the naming the column this total. So passing the alias, we will also see what the alias does there in the example.

[00:28:14]But what this does is takes the aggregated sum of price for a for each category and we can see it here. We are first creating this summary data frame. We are using the width column creating the total uh from the price and quantity. We have seen this before. Then we are grouping by category. And to actually see a data frame again we need to pass it this aggregate. And what we do now is we are using the f sum total. So we are creating the uh the whole revenue here. Then we are getting the average price per category.

[00:29:01]So what is the average price of a specific order there or an item and then we are just taking it count of order ids uh taking the count of specific column versus taking the count of like star is the difference uh the difference is there that if we are just taking the taking this order id and we have nulls in order ids those won't be in the count so we won't be getting the full account for the categories there. But yeah, then we are ordering by revenue. So nothing new there either. Uh now what we can see here we have group by categories. We had two categories just two. And as we are using these aliases there uh we are naming this second column revenue the third one average price and so on. And as we are using this group by category it will always be the first uh first column here. We can also pass it other other columns as well and the grouping will be done on more than one column but that's not what we want here.

[00:30:18]But let's just uh for example, I'll just comment out this average price. And here's why you want to use the alias. If you are not using alias, you will end up with column names like this. So you will have the function you are using and the column name you are passing there. And if you are passing there something even more than just this one column, it will look horrible there. So use the alias to make everything look nice.

[00:30:51]Then uh joins I think if you want to div dive deeper into joints there are a lot of good resources for that. We are now just looking at how the joints work in Spark. So the default join type is inner and if you do not know what different joint types are just in short inner join is just a when you are joining two data frames together for example based on one column you will only end up with the rows that find a match on the other other data frame. So let's say we have this this other data frame here that we are creating just manually here and this data frame has the country code paired with the country name. So we want to join the country names to our current uh sales data frame that only has the country codes. So we want to see the country names because it looks a bit nicer in reporting. Let's say for example uh we are creating a new data frame called joint. We are calling this data frames join method here. And what we first need to pass it we are passing it the data frame we want to join. So as we created this uh data frame called countries. We are first passing it here.

[00:32:19]Then after the first comma we are passing it which uh columns we are comparing. So in our data frame when we have the country column that we can see somewhere here we have the country column that has the name country and it has these country codes there.

[00:32:42]And in this data frame we are creating uh the name for the codes is code. So we need to compare this country column from the original data frame to this code column from the countries data frame.

[00:33:02]And we wouldn't need to pass this inner here because it's by default. But it's always clearer if you just write it there. So it's also faster to just then change to a left join for example. But what inner join does is that we can see that in this data frame that we are creating manually uh we are also adding United States.

[00:33:26]And if we just run this we can see that because our original data frame didn't have us uh for any rows we don't see us here either. But if we were to use outer join, this is uh not that useful in many cases. There are some sometimes when you want to use it, but quite rarely. And it will take this United States row as it's found in the other data frame, but there's no data in the left side. So you will just see this null row with the United States there. But yeah, inner one is most often very clean, but you just lose some data that doesn't have a match in the other data frame.

[00:34:18]Yeah. Uh quick quiz then uh revenue per country.

[00:34:23]So we want to group by at least and show only countries with over 1,000 in sales.

[00:34:29]and expected result will be just one row where we have the Germany with the total revenue of 3,400.

[00:34:39]Uh we don't know how we end up here yet.

[00:34:43]We just want to do these these steps here. So revenue per country. So only countries with 1,000 in sales. So if we want to first start looking uh we are creating a column called revenue uh based on the price and quantity.

[00:35:01]Then we are grouping by country and aggregating the sum of revenue and naming it revenue and then we are filtering it. I think it looks at least pretty close. So we can move forward.

[00:35:18]Then we are filtering the data frame first uh by using the price multiplied by quantity. So basically revenue and we are filtering everything out that doesn't have more than 1,000.

[00:35:34]And then we are grouping it by the sum price uh grouping it by country and summing it by price. Uh and the last one we are grouping it by country. we are aggregating it and using the sum price times quantity there and then using the alias and then filtering.

[00:35:59]So any any guesses what are at least incorrect or correct think we can start running the option C first. So this is uh very hard to notice if you're not familiar with spark but here when we are passing this fs sum these two column names but we are not using the call functions here. If you remember, we can do this fol. And now it would look more correct. But currently, we are just passing it a couple of strings here instead of columns. So, it doesn't know how to resolve them. So, it will fail.

[00:36:53]Someone in the chat said B is not correct. And you would be correct, sir.

[00:36:59]Uh it's quite there. It has a some idea. But what this does right now is before the grouping, it will go through every row and if the revenue for that row is not above 1,000, it will drop the row. So we will end up losing data before we do the group by. So now we have just lost some orders. We have the Germany here like we wanted to, but the revenue is off because it's lower. So when we go and run a, we end up with the right result.

[00:37:39]So we have the total sales for Germany.

[00:37:42]And we have, if we were to comment out the filter, we would see that for these other countries, the revenue in fact is not over 1,000. So we have correctly filtered those out.

[00:38:02]Yeah. Uh fast writing some data pretty close to what we did when we read but for reading we use the spark read.

[00:38:12]Now we are using the data frame we want to write dot write and then giving the format. We can also here use different formats. We can write as par, we can write as CSV or whatever.

[00:38:28]But here we want to use the delta format. And we have different modes that you can see here. Overwrite replaces everything that is in the current table if the table already exists. Append is adding rows to that table. So not overwriting anything, just adding what your data frame currently has.

[00:38:49]ignore is that it will skip if the table already exists and error by default will show a failure if the table already exists. So these are the different modes there. No need to go deeper into those right now but let's just we are now creating a data frame called DF to save because we want to save this data frame. We are adding the total column here. So again on top of our original data frame we are adding a column total and then we are calling for this data frame that we just created we are calling the write dot format delta and overwriting and saving it in the tables area under the gold under the gold schema and in a table sales and then we are if you remember from the beginning using the spark read table and we We are reading the gold dots sales and only showing five five rows there. So this will take a couple of seconds as this is now actually doing things. It's writing data and it's also reading and displaying data. So it will take a moment but not that long. And now if we go and uh check out our default lakehouse. Let's just refresh the tables area. We have now created a schema called code and we have the sales table under there and we are reading from that table and we can see that there's now the data we already had there and then uh we have this total total column there.

[00:40:33]We can also use this when you saw this we had earlier in the beginning we had this dot load uh paired with this dot format if we didn't have this default lakehouse in place and couldn't use this like shorter version of the path we would use this ABFS path so you need to kind of remember this format that this path is in. So you don't need to remember every every slash there is, but it will start with our current or the workspace name where your lakehouse is. Then there's some some of this default stuff there.

[00:41:24]And then it will have your lakehouse name and this dot lakehouse then slash and we go to the tables area. Then we have the schema and then we have the table. So you don't need to remember everything but just remember it starts with the workspace name. Then it has the lakehouse name and then it has the schema name and the table name and some other stuff there as well. And if we just try to run this, we are now reading the whole table there. And we can see that it was successful. If you want to use this spark SQL, so we don't some people uh prefer not to use pi spark per se, but well you are basically technically using pi spark, but you are not using pispark functions. You can create a temporary view using this create or replace temp view. And what this does is creates for our current session a temporary view that we can then query.

[00:42:33]So you would always like create a temporary view based on what you have done to your data frame. Then you would query do some other things and create another temporary view. I don't like this approach but it's something that you need to know as it is used somewhere at least but here we are using our basic data frame creating a temporary view uh then getting the uh top what we are doing here order by revenue yeah we are getting the revenue and or the total revenue and ordering it in a descending order. So we can use the spark.sql and write basic SQL there and this will create a data frame that we can then display for example. So this will create the view then we are using spark.sql and reading from the view we just created. We named it v sales and then we display it and it looks correct. Uh we still have well minus 2 minutes left but let's just go through this final quiz.

[00:43:50]Just trying to get everything here. So this is a type of ex uh type of question that you will run into in the exam as well. Uh this is just well not this exactly but something like this. You have these options that we can use there.

[00:44:09]And the goal is to just read a CSV file uh filter that we only have electronics orders worth more than 100 and then saving the revenue per country as delta table replacing if it exists. So if we want to just start doing something I'll just uh persist this here. If we want to start doing something easy, uh the thing was we want to replace if it exists. So we have the writing here and in our options what would match there is the overwrite because we want to overwrite what we just did. Then other thing uh we are reading a CSV file. So what would be the arguments that this CSV dot CSV method here takes and that would be the header equals true. You cannot put like group by or partthe by or whatever. You just throw it in there.

[00:45:19]Uh then we start to look we want to create a wid column revenue and we have already done this multiple times. So we want to use the price and quantity and we can find them here. So not really this is a incorrect syntax then we have some group by and something that makes absolutely no sense there. So we just throw it there. Then we have a filter and what we would like to put there is something that is a comparison.

[00:45:49]So if we just scan here or the only comparison is the one here with the double equal signs we draw it in there.

[00:46:00]Then we have something that we pass a name of a column. This could be of course well partition by technically. Yes, but not in this context. Doesn't really make sense. And the others don't make sense as well. So, we want to select the group by and just throw it in there and then run the cell and see if we were successful.

[00:46:27]But this is something that you will run into in the exam. So, just always think what does this part of the code need. If we are calling the for example here we are calling the data frame and passing it a column we cannot like do a filter here because if we are just throwing one string for the filter it doesn't really make sense in any way.

[00:46:55]But yeah this command executed successfully. And if we want to even take a look, let's just refresh this quickly.

[00:47:05]And we have this. Yeah, here in the DPO schema we have the country revenue and we have the country and the total revenue column. So it was successful.

[00:47:21]Yeah, we have run out of time. I have a another session starting. Thanks everyone for tuning in. If you are interested in the data communities, especially the fabric, PowerBI, and SQL for Microsoft data and AI, I really recommend checking out the upcoming session.

[00:47:45]But yeah, thanks a lot for tuning in. If you have any questions, you can contact me on LinkedIn, for example, or Fabric Forge is a great place as well.

[00:47:56]Yeah, thanks a lot everyone. Really appreciate you being here. Hopefully you learned something and see you next time.

[00:48:03]Bye.