Most of you don't need Spark. Large-scale data management on a budget with Python

Speaker 1 [00:02]

Okay, so most of you don't need Spark. This is a kind of unconventional tutorial. You're not expected to use a laptop, you're basically expected to participate. It's unconventional because I will be trying to make questions to you and challenge your previous knowledge, so please be brave. I mean, this is way more fun if you answer, even if you're wrong. If no one answers, I mean, I will just point someone and force force him or her to answer. So I will kind of force this dynamics in which this is a conversation. First of all, about me, my name is Guillem Burrell-Nugueras, I'm Spanish, I have a PhD in space engineering, I come from research, and then I moved to consulting some years ago and now I'm at BCGX, former BCG Gamma, and we're doing very, very cool stuff, okay? It's surprising what, by the way, who has here ever worked in management consulting? Okay, how was the experience? So it's curious because it's, many people think that management consulting has misconceptions on management consulting. Management consulting is super diverse. By the way, who knows what the big three means? Big three? McKinsey, Boston Consulting Group. No, no, wrong, wrong. And Bain. We are the first B of the MBB. And BCGX is kind of the tech branch of that. Okay. wild guess, how many engineers and data scientists do you think that work at BCGX? 50. I mean, wrong by orders of magnitude, guys. More. Around 2,000. So we're a large team, we're a large group, and we can do very interesting things, and we're a large community, okay? in Berlin, there are three offices. One of them is like across Alexanderplatz. So we have, and this is more or less to understand how BCG works, like we have more on the poshy area of the city. We have the classic consultants, the business consultants. Close to Alexanderplatz we have digital ventures, which is like cooler people. And then we have Platinium down on the right, which are mostly IT engineers, so they don't need to be closer to the centre the city, I guess. So we have three offices in Berlin alone. They told us that they will merge and they will be in the largest office in Europe. So yeah, the whole tech space is growing and it's also growing for management consulting, okay? And of course, I mean, that doesn't make us any different. Everyone is hiring. We are hiring, too. And we're nice people, really. Yeah. You have to take my work for it, but we're nice people. Really, come talk to us. It's Guillem, then we have Jörg and Basti here. Yeah, say hi, guys. Stand up. Stand up, stand up, come on. Yeah, say hi. They're German. I'm not, of course. So don't be afraid of management consulting. You can try it at least once in your lifetime. By the way, originally this tutorial was aimed to be run entirely on Raspberry Pi 4, okay? And I have one. I got one. I got one. How many of here has a Raspberry Pi 4? How many of you wanted to get one and didn't get one? Okay. Yeah. Chip shortage, yeah. So okay. So I have a Raspberry Pi 4, but have you ever tried to build Python packages there? And how was the experience? Have you ever tried to compile large packages? I tried that, and it took like two hours. And I just, I tried, I mean, this is my personal space, and here if you want packages, like scientific packages for Raspberry Pi 4, you can ask me because I have the wheels, but I gave up after like three days, okay? So Raspberry Pi seems a good idea, but weird architectures are a bad idea. So what do you think that I did? I mean, I want something on a budget. Assume that you wanted a Raspberry Pi, you couldn't get one, what would be the alternative? Yeah, right. Go to Amazon, and you get something that I think that's in fact cheaper than a Raspberry Pi 4. It's second hand. You can get it in Amazon very quick, it's one of those refurbished. And this is literally sitting at my home by my couch. So this is kind of my personal computing space, and this tutorial will run in this machine at my home. No AWS large instance in the background. This is literally running on a box of my home. It cost about 100 euros. Some background. When I explain what I do to colleagues, sometimes to clients, most of the times they have this trade-off. You can build or you can buy, okay? And this is something that every time that you start a project, you have to think about and be introspective. Do you want to buy or do you want to build? I'd like to know which Which of you have been in fully built, sorry, by projects? Like it's a company, said we want to use everything on Azure, AWS, whatever. Fully software, you didn't have to touch or you had to code very little. Who has been in this situation here? So all of you have built everything from scratch. I just don't believe you. That's not what you asked. What's been on that experience? has been like, hey, here's Databricks, you only have to use it. Or here's Azure ML. Like, yeah, that's your playground. Oh, but it's fine. I mean, how was the experience? Yeah. I think in the beginning I didn't like it, the development environment and everything, but now I started to like it. It's easy. Okay. Assume that you had to build a business on top of that. Okay? Not like quick and dirty analysis, like you find some idea and you say, okay, I will use that for my business. Would you do it? Like for a startup, it's too expensive. Why is it expensive? First is the business. Second, it has huge overhead because it has to work for everyone. Not only for you, not for corner case. I mean, this is a business. So the, the largest amount of users that it's, uh, that can use that the of profit for the company that offers a service. So I sometimes explain the trade-off between buy and build with optimizations. You don't need to optimize. You can use buy. But at some point, you will want to optimize. And then you have to push yourself to a more build. And this is something that I usually explain like in three principles. So functionality Optimality and implementation cannot be separated. There's no such thing as a silver bullet, there's something that sounds like cliché, but everyone that has some experience at building things understands that's almost the law. And as I said, there's a trade-off between optimality and generality. The more general you are, the less optimal you are, and vice versa, okay? Use Databricks, you have a beautiful Spark cluster, but yeah, maybe you don't want Spark. And finally, and this is something that, I mean, I'm kind of old, you see, white bird, white hair, and something that you realize is that you build a business, the most optimal solution is always the best in the long term. It's our task as engineers to make that sustainable. But the optimal solution always has way more benefits in context, like cost, maintainability. Maybe the provider just, you know, runs out of business and that service that I needed no longer exists. So this kind of thing. So in the long term, yeah, maybe the build is more interesting. So this talk is also about the tricks that you can use to build better stuff. Because yeah, I mean, we have a Spark cluster with a couple of clicks. Everyone can spin up a Spark cluster in a couple of clicks nowadays, okay? So I will use a JupyterHub server that literally runs on that small machine at my home, besides my couch, okay? So it has four really, really, really, really bad cores. And 32GB goes very, very, very, I think it's still DDR3. So it's something, I mean, you've had a laptop with DDR3, you would go to your company and say, can I please get a new one? So this is also to illustrate how optimal we can make things just by thinking a little how to apply these tricks. I like to frame talks in a kind of problem statement. How many of you have had to work on a very, very, very constrained environment? Like I go to a client, they have a very crappy server, and that's all they have. I mean, can I get a bigger server? No. to get a cloud connection? No. Who has had that experience? Okay, just the last one just to move things around. What happened then? So you did not do anything. The project died there. Sorry, I couldn't do anything. Okay, at some point it stopped. And did the goals be, where the goals met? Anyone else that can also give the experience? But that's not a constraint, I mean, that's a true and complete language, I mean, unless you're forced to work, like, with brain fuck. It's true, but I mean, the development needed to flow out, but it had to get around, like, into the bank. Mm-hmm. There was someone else raising hand in front of you? Yeah, I worked in defense, and Excel happened. Excel happened. I work with business consultants. My work in this case was an inductive work thing, and they were very strict there, and there were isolated environments, and we had to install everything from here. We had to download files, and then upload it, letting them upload it here, so it was So, I guess that you find in these situations and you improvise. That's what you have to do, you improvise. It's very... Sometimes you can define your development environment and your productive environment, and sometimes you can't. And this is a kind of problem statement to see that this is an example. It's not a real example, but that is realistic. So assume that there's an European Commission that oversees online games, okay? And a whistleblower tells them, this casino, this online casino is tampering with random numbers. And there's a reason that they make much money than what they should. The good thing about these kind of games is they're pure statistics. If you have that much players and you have that much, for instance, roulette games, you must earn that much money. You cannot cheat large numbers. But for some reason, that casino is winning slightly more money than expected. So the first audit shows that the rate of wins versus bets in the flat French roulette is slightly higher than expected, okay? So there's some suspicion. So the casino, casinos online says we're innocent. It's just that our customers are unlucky. Yeah, kind of. Sounds like an excuse, but of course, I mean, the regulator never finds jokes any funny, so never tell jokes to a regulator, please, don't do that. And the whole thing ends in court, of course, not because of the joke. And assume that you have been chosen as auditors of that. So you have the audit, the casino. So the casino provides the data, they leave it in an S3 bucket, but of course the regulators forget to add in the regulation that you have to provide the data, and they have to provide the means to analyze the data, so what they do is that they go to AWS, they choose the smallest intents possible, and that's what you get. Assume that that's in the situation. So something that has less computing power than your laptop. Who has ever played a roulette? Not many. Do you know that there are two kinds of roulettes? Don't you? No? That's the French roulette, this is the French roulette, and that's the American roulette that has zero and double zero so that casino gets more money. Yeah, the Americans, you know. So if it's online and you play American roulette, you're just stupid. But if you are a casino and wanted to make more money out of every roulette game, what would you do? if you want you start tampering with the game what would you do i would analyze which numbers are that problem which analyze sorry this is i mean that's a good point but this is complex because in the end entropy it's everything and the the bets kind of spread quite evenly between so that's been papers about how to work with that methodologies like this are good ideas don't work otherwise casinos would use it extensively the thing is that really Also, the betting patterns are very random as well. So but that's a good idea. You would increase the probability of the zero. What's the issue of that? That it's easier to spot. But that is easy to spot as well. You only have to aggregate more data. Okay? So there are ways of tinkering with. You have to choose something that is not obvious enough so that you make the problem complex enough so that it is NP-hard. So you can make... You can hide this enough so that a human being would take... Do you know NP versus NP? Who knows about this? Can someone explain to me, like I was five, what NP versus NP stand? . Yeah. . . . . . . . . Exactly. a once mathematician that gave me a very good analogy for that. You cannot model luck. So yeah, you may be lucky and find the solution of every problem in two days. It wouldn't take you the age of the universe, but you were just lucky, okay? So it means that you can hide problems or you make some once you you can validate that someone has tampered with the roulette but finding a strategy of so making an algorithm that finds that strategy is super complex so once you find it you verify it and yeah i mean that took it took you half a second but there's no procedure to find any kind of manipulation possible okay so we have lots of data we have a very hard problem and we have no hardware yeah great okay and what they say is so the first thing that that we have to think ourselves and this is something that I tell to my colleagues data engineers is that the first question that they must make themselves is how much data do we need? Okay? Because you can, you may work with a subset of data. Okay? How many of you here are data engineers? So you work with data. This is mostly what you do. You load data, transform data, store it somewhere else. Can you please raise your hand. Who here is kind of a data scientist? Okay, so kind of half and half, okay. The guys who raised their hand as engineers, do you think that you need to know statistics? Why? How do I know what engineering I need to apply without knowing what the other guys are doing? Okay. Next question. Does that question, is that question related to statistics? Exactly, now that's great. Now only engineers are allowed to answer. If I want to tamper, if I want to check if the roulette has been tampered with, which statistical test do we have to use? No. Close, but no. So first of all, lots of it, okay? I mean, there's lots of... I mean, roulettes are very entropic, so... And many games are very, very entropic, so if you want to check that there's been some kind of manipulation, You really need lots of data. And in fact, the casino presents this fact as a defense. They say, okay, we have analyzed 15 million games. And we see that we got 418,845 zeros. Which test can I do with these two numbers? Anyone? Come on, not even the data scientist? Binomial test? Who knows what the binomial test tests? Come on! You can test whether the probability is like of your event, Exactly. You know which is the base probability of each event in a roulette game. You know if you put the games down to infinity, which would be the number. So you should compare that to the number that you're getting. And if these two numbers are off by... deviate enough, then you have the proof that someone is manipulating the roulette. But you need statistics for that. So are these numbers enough? Fifteen million games. I mean, do you think that those numbers are enough? Small pause. Data engineers should know some statistics, and every time that the data engineer comes in and doesn't know basic statistics, I get very angry, okay? And you just need the basics. So binomial, you want, it tests if values are distributed according to an expected ratio. Like do, is that, is the yield of that manufacturing process the expected? Yes, no. How many pieces do I have to sample to know that? the binomial test tells you also how much parts you have to sample so that you get enough accuracy with your test, so on and so forth, okay? T test, the T test, it checks if two populations are different. Not so much proportions, it tells you, I mean, particularly the average of those two populations are different. test, test if the data follows a normal distribution, ANOVA, means if n population is different, t square if two variables are in a content, okay, you name it, okay? But and it's something that I always, always, always mention, statistical properties of past data are good data quality tests. So if you collect your data and sample some properties and you save them somewhere and and the new batch of data with some of these tests is not similar statistically, then you have something weird going on in your pipeline. Okay? So as I said, I think that data engineers should know at least basic statistics and know how to apply it. Okay. We have a test for that. And we have to know how to interpret it. Because usually the business consultants or the judge that asked us to audit the casino, they don't know about statistics. We do. They don't. Okay? So assume that I put that in the binomial test, okay? And I want to know if the proportion of zeros is greater than expected. And I know that the proportion of zeros has to be 1 over 37. And here what I do is, okay, let's change that a little bit, and let's see if the proportion is 1,000% higher, okay? So let's put the baseline, like, let's give it a little delta, like, is it 1,000% higher than what I expect? And let's put the two numbers there, and let's run a binomial test. And it tells me that there's a 96.4% confidence that the rate of zeros that the casino get is not higher than a one per thousandth. So kind of that's a strong evidence. Is that? It is not? So good luck explaining that to a judge. So what happens many times is that you have to design things in a way that there's no discussion. And statistical tests are funny and make them convert sometimes requires lots of data. Which is one of these lines? What those lines represent? This is the horizontal axis is the proportion by which the additional number of zeros that I can have. So this is that really the two proportions are the same. The more I go to the right, okay, it means that I can prove that I need more delta to to, okay, so the more I go to the right, the more I go to the right, the less I'm stressing the test, okay? So I go so much to the right, and I say, okay, these numbers have to be so much off to have some evidence, okay? And the blue line is 10,000 games. So 10,000 games give me a lot of confidence, which is the vertical access, only if I assume that those two datasets can be very different. But as soon as I want to give absolutely no doubt that those two sets are different, so I have to go to the left, I need more games, to the point that the red line, which is basically a step, for that I need 100 million games. So I need 100 million games to prove to a judge that these guys are tampering with the casino, with a French roulette. And this is the kind of test that you have to run, because maybe, maybe, you just need 10,000 games, okay? Was that useful? Yes? No? I see. It's nodding. Okay. So... But, yeah, I mean, this is kind of good evidence, okay? How do we solve this? And this seems strong evidence in favor of the casino, like, that it's very unlikely that they're not adding more than one additional zero for every thousand games. So this is something that, you know, the universe can be conspiring to make them get more money. Why not? So it means that we have to crunch hundreds of millions of French roulette games with the computing power of a modern phone. So yeah, doesn't look very, very, okay, I mean, of course all this is synthetic, no one's going to jail, at least yet. And there are some things that I'd like to show you. Because of course I had to simulate these games, I had to store the data somewhere, and only simulating games, generating hundreds of millions of games and storing somewhere, it's very useful. Sorry, it requires some technologies that I want to talk to you about, okay? Who here has ever used a data lake? It should be all of you. I don't believe you. I mean, you're not raising your hand. Come on. Okay. What do you store in data? I mean, again, who raised their hand? Who has ever stored a data lake? Don't be shy. You. How would you store in a data lake? . Files. Okay, you store files. So how would you save 200 million games of French roulette? Good question. Make a wild guess. We're here to play, not roulette. . That CSV of Parquet files, would you store the information as CSV of Parquet? You made a very good point. It will take so much memory, why? Roulette game is very easy. You have game, player, what they chose, and what was the result. So very narrow column, lots of games, not many data about the game. That's not really much you have to store per event. You can put a timestamp there, but not many data, I mean, roulette games and casino games are simple. Yeah. Exactly. Yeah, it's not really wide. You have a hash for the player if you want, like a player ID. You can have multiple boards, so you need a board ID. And there's a bed. You can have multiple beds. But the data item is the bed. Each bed had one assumption, and that bed has one outcome. If the assumption is the same as the outcome, depending on the bed, you will get something back or not. Okay? So it's a lot of events, not much data about the events. Would you store that in a Parquet or CSV file? Yes or no? I'm not sure about it, but it feels like Columbia, I'm really not an expert, but it feels like Columbia is quite good because there are so many options. Yeah? So you have to record like 100 million games, not many relations. I don't know the game, but like 30, 40 options, I don't remember, 380. Bets are complicated. have about 1370 something. I don't remember the number of options for bits. But that's an ID, I mean. And how would you store that on a data lake? Sorry? That's a very good option. Okay, if you were me and you had to set up all all this in a crappy server besides your couch, what would you use? Sorry? It's a data lake. No. That's out of question. That thing has... no. would you create your private data lake? How? It seems that, I mean, there are tons of technologies to create your private data lake. Come on. Okay. Who, HDFS, MinIO, Swift, NFS, and Glossary RFS. Who knows at least one of these things? Two? Three? The guy in the mask, sorry. Which which one you don't know who knows about set what is set isn't it the most awesome thing that you ever saw technologically speaking i don't i mean So, the thing is that you can build your own data lakes, tune them, making it yours. And here we'll use MinIO. MinIO is very interesting. So it's super simple. It's a data lake on a single go binary. And it has server and client. And the client is super handy. I mean, every time that someone starts working with data lakes that use the S3 protocol, And there are many of them, like, Seth can use the S3 protocol, Swift uses the S3 protocol, and MinIO uses the S3 protocol. So that's, in the end, S3 has become the protocol for data lakes, okay? So MinIO has a client, and it's something that I'd like to show you. Because it's... Not this one, sorry. Because it's super, super handy. I don't know. This is the Minio. Can you see the screen? Someone have a second? I can make it bigger. Is that better? Can you see the screen now? Who has here ever worked with S3, I mean, AWS S3? I mean, who found the Python API easy to use, super handy, I enjoyed the experience? But is it for masochism or...? A little. Okay. So MC, that's the MinIO client. And it's curious that this is super interesting, I think you should have it in your tool set. MC can connect to many endpoints. It calls them aliases. You can set them. I can lease them. This is the literally min IO storage that I have on that small server. I can browse that using the command line, like MC, alias, local. Here I have a data warehouse and some testing. data warehouse, data set, oh, and here I have the simulated games, like Stree, so I have six boards where I simulated the games. Let me copy that. And I have a lot of folders here. And I have partitions. Who recognizes these weird folders? is that? Sorry? I partitioned the data set. Okay? When kind of said how would you store millions and millions and millions of roulette games, one valid answer is okay, I'll use a partition per get data set. Okay? Let me go back to the presentation. Okay? So this This is, as you've seen, this is super handy. I can... The same way I have an ls, I have a get. Sorry, get mc. Was it get or copy? Sorry. And yeah, I just downloaded one of the partitions. So it's super convenient. So if you don't, if you have hated the experience of using one data lake, maybe that very, very tiny executable can help you, okay? Let me... So long story short, I created my own data lake and I stored all this data as a Parquet file straight to the data lake. How... How... Who here has ever written Parquet files from data? Not get Parquet files, but generated Parquet files. Okay. So how was the process? I mean, do you have any previous data and you had to translate that to Parquet? How was it? How did it work? Yeah. I was doing it to Pandas DataFrame. So you took Pandas DataFrame. In which format was data? The data before the Parquet? Yeah, normal stuff. That's a command question. Yeah, please. That's what I'm going to. How many people here have had to spend huge amounts of CO2 translating CSVs to Parquet? So it seems that we are spending a significant amount of time moving from CSV to per case. Why do we do that? Because most query engines can query CSV. Is per case more efficient than compressing those CSVs? The schema, more things than that, because you can assume that the CSVs are super homogeneous, so I can write six lines of the first file and I can infer the schema. There's something else. You can partition CSVs easily. You can? Sorry? Why? Because you can query CSVs as well. And you need to always read the whole row and get all the columns, basically. You need to write the entire file, why? Why is different? Why is parquet different from CSV? I have to write all the CSV and not all the parquet. There are sorts of weird parking errors with CSV sometimes. I assume that someone, I mean, it was not the sociopath who wrote the CSV. That's a good point. But we have very, very super fast CSV readers. So you can have, like, a factor of two, but not a factor of a hundred. And here I'm looking for a factor of a hundred. Exactly. So that's super important. So Parquet is not equivalent to a compressed CSV, by far. And if everyone says ever that to you, just, I don't know, I mean, fire him or her, okay? And I don't know, do you know Sam Law? Sam Law is super smart and he has a company and a product that has, I think, time series analytics. He's super smart. And you can follow him on Twitter. But the most important thing is that Parquet was designed as a distributed data set database data format. So Parquet was designed for databases in mind. CSV was designed to store data somehow in a kind of convenient way. But Parquet was literally designed to store data for databases. And we want to query that data. We want to analyze the data of the roulette games. And this is something that... An idea that you can have in your mind for your next projects. Quering is more about metadata than it is about data. So the gentleman here said it stores statistics. So metadata, so statistics is just a form of metadata, okay? So what we will do as an exercise, okay, by the way, so first thing, parquet metadata. So parquet is more convenient than CSV. Second idea. And here Sean makes a good point. Assume that I only have to read data once. It's not that I want to query it many times. I want to read data just once, okay? Then what makes CSV and Parquet different? I just need to read it once. If you have metadata, so you have to go from CSV to Parquet, you have to read the entire data set, okay? And store it as Parquet. But assume that you would read that data set only once. Would you do it anyways? That's a good point. But there's even a better point. Maybe that should have never been a CSV. And it's something that I see in many pipelines that some engineers and data scientists create is that they have the data, they store it in some kind of intermediate format, usually CSV, and then we reprocess that to something that we can query. And what the immediate conclusions I get from that is that people are so afraid of streaming data. I mean, we can run a process for every small bit of data that lands our system and turn it into a file. Why do we need intermediate states of data? And who knows about streams? No one? Really? This is the... Yeah? Given this is about cheating, it's fine. So streams is a not very fast, very convenient library to stream data. And you can use the friendly Python APIs to create a stream, do something at the beginning of the stream, process the data somehow, and just, you know, release the result. And the good thing of stream is that it's super easy to use. It's not super fast, definitely, but it's very easy to use. And it was, it's the, I call it the less known Dask cousin. Who knows Dask? Curiously, no one knows Streams. By the same guy. And it's really useful. Here what you have is, oh, by the way. It leveraged iterators so, so, so much. here was on big data in the year 2010, 11? Yes, no? Maybe for the older folks here. Did you ever heard anyone telling you it will be functional languages that will dominate big data in the future? Did you hear that ever? Which languages they were talking about? Haskell, yeah. I wouldn't ask your age, sorry. So it seems that you've been in this domain for a long time. I always say that I survived Haskell because I had many, many functional guys, functional programming people telling me you're wrong using Python. Python is the wrong technology. Haskell is the future. And, I mean, from 2010 to 2014, more or less, that was the motto. And then Spark came and made things even worse. Because it was implemented in a language that supported functional traits. Then you look at the Spark code and there's no functional anywhere. But everyone told us, yeah, Haskell is the future. Why are you using Python? You're on the wrong side. And at some point that I made myself a T-shirt that you could read in it, I survived Haskell. But I keep telling them, Python is a great language for streaming, because we have the iterator protocol. And you can stream data very efficiently, and the interpreter does that very well. Because it is able to chunk data very efficiently, okay? So streams is also fully synchronous, and it can all the streaming operations that you would do on data, buffering, mapping, partitioning, it's made super simple. This bunch of code is generating the present. This bunch of code is generating the data from the simulation to the Parquet partition file. It generates Parquet partitions. Not Parquet files, Parquet partitions. Okay? One, two, three, four, five, six, seven, yeah, you name it. Ten lines of code. So you have the... An object above. By the way, who recognizes this method color thing? What is this method color? From which Python module you get the method color function? Come on. Someone has to know. Operator. Go, I mean, as soon as you leave this talk, go to the Python documentation and look up the operator module, please. stop using lambdas everywhere. Someone would say, yeah, no, method color is faster than the lambda. No. No, lambdas are very fast. It's not for speed. It's because lambdas just destroy the readability of your code. Please don't use lambdas. Read the documentation of the operator model. It's full of gems. Please use it. Okay? You see, I map, partition, again and write the data in a stream, okay? Assume what would you have to how how many lines of code would have been doing that using a Python iterator. This uses iterators under the hood, so it's the same thing, but the API is super nice. Okay, so please learn about the operator model, learn about streams, don't write intermediate files that are useless, you will only read them once. If something is a CSV file, think maybe that should have never been a file. So let me do this exercise. I'll go back to one of these Parquet files. I think that's from Arrow. Give me a second. D, D, D, D, D. From Arrow. I think I have it here. Yeah, it's this line of code. It's Parquet from Arrigal. I don't remember which was the module, give me a second, pq, yeah, import arrow, pyarrow forecast PQ. And I will make PQ, ddd, it was below here, here, and let's change the file with metadata. Control C, control V, and I have the file around. I left it here. And I think that we're done. Let's go back to metadata. What am I seeing here? Who set statistics? If you want to design a query engine and you want to add statistics that are relevant to a file, which things would add to the metadata? Which is the most basic operation you do with a table? Operation. I mean, that's an aggregation. Comparison is equivalent to a filter. You want to filter your data. You may not need all your data. So what you want to add to the metadata of your files is things that save you from reading all your data. If the comparison of my filter has a value that's not between these two values, I can ignore that file. I only have to preload the metadata of all my data set, leave it somewhere in memory, and first I query the metadata in my indexes. If I don't need this file, I just don't read it. Let's try another column. I don't remember which one was that. I think that's the earnings Sometimes, I mean, it's not super handy because that's an ID. But you would never filter by ID. This one corollary, a very important corollary thing here. This is why if you ever have ever anyone here try to write a data frame library, I did. Don't try it. It's super hard. You get to nowhere. But one of the things that you realize when you try to develop a data frame library yourself is that indices are equivalent to sorting your data. If your data is sorted, you don't need an index. column that is sorted works like an index. So if the min and max assume that that was for all the files that was literally alphabetically ordered, okay, you wouldn't need and you had the minimum and maximum, just by giving an ID, you could pinpoint the file that you need. You only have to make sure that the minimum and maximum IDs of that file don't overlap. If you're sure of that, if you want to filter by ID, that data set, you would only need to read a file, a single one, from millions. So think about how Parquet is a format that's designed to work for databases. The goal here is to build a database. And this is why we have this metadata here. Okay? Who learned something new? Great. Okay. Let's keep diving in. Who has ever had to fight that argument here? the one in the title. Blame text is better. Sorry. Who has ever had to fight the argument that CSV is better because you can just pick the files? You can just open a file and read what it contains? No one. Yes? How did you answer back? I investigated it and I found a Perquet viewer that they couldn't open the file, so it was for people who are not techy. Exactly. So you said they were saying that I couldn't open the Perquet file, you just found the tool. If there's a complexity, something is hard or not hard, depending on the tooling. If you have all the tooling you need, everything is simple. It's just that we have tooling that's ready for text files. And many of us don't have the tooling for binary files, and Perky is a binary file. And this is something that I did. It's a pet project of mine, which is a Rust binary that just lets you open Perky files and get some statistics, print it on screen, convert it to... And took me, like, one day to write. So if someone ever tells you plain text is better than binary, just say, I mean, that's because you like the tooling. Go find something that fixes the problem for you. But don't assume that something is hard or easy just because you're not familiar with the tooling, okay? So now we get to the middle thing. Who knows about DuckDB? I expected more hands open by now. DuckDB. I mean, who knows about pollers? I didn't expect that difference. I assumed that the same people who knows about pollers would know about DuckDB. Oh, that's a good point. I found that very puzzling. Because essentially, these two technologies are awesome. And DuckDB can both pollers and DuckDB can save your life in low memory, needing high performance situations. I think that DuckDB predates pollers. And if you look at benchmarks, I mean, they're struggling to know which is faster. So yeah. I think that DuckDB and by extension, Polar are the single most important thing that happened to the ecosystem this decade. Okay? Technologically speaking, they're awesome. I encourage you to read the articles by the DuckDB developers. are probably the guys who know most about analytical databases right now. They're researchers in the Netherlands. And what they do is awesome, okay? And the thing that... The reason why I'm puzzled... So I'm puzzled that you know Polars but you don't know is that DataFrame libraries and database engines are so similar. Who has ever used Spark? You only have two APIs. It's the same thing. So why do we make a difference? You shouldn't. It's the underlying technology is super similar. It's only the syntax. So You can think about, in fact, I don't know, do you know that Polar has a SQL engine? You can query Polar's data frame with SQL? Did you know that? Who knew that? It's kind of hidden in the documentation, but you have a Polar data frame, you can send it SQL statement, and it will work. So it's very, very similar, okay? But I think that DuckDB and Polar have been an eye-opener about how slow and memory-hungry pandas is. Who here has transitioned from pandas to polars yet? One, two, three. Why is polars faster than pandas? Type system. This is too broad, maybe. Can you narrow it down? It's not data storage. In fact, who knows how Pandas data storage was... Pandas stores data in a very weird way. It has a set of arrays. It puts columns together in the same multidimensional arrays. That's very weird. But the typing system is not the reason. Who else is replacing Python with sorry, Pandas with Polars? Why is it Polars faster? Polar is faster. I mean, the most immediate thing you see is that it's much faster than using multiple cores. And I think there's points of being lazily evaluated, which may help you optimize more. But again, not so sure. I mean, multi-core, yes and no. Because in the end, it's very hard when you write a SQL engine or a database engine to efficiently use multiple cores. This is super hard. So by adding more cores of the thing, you may get a speedup of three, four. At most, five. Getting more than that is, I mean, people write a paper and fund a startup when they get past five. And then you said it's lazy. What is lazy evaluation really different than eager evaluation? That is not exactly it. I mean, you're mentioning streaming. So many databases are streaming engines. So they know the pieces that they have to fetch from the disk. They know how to have to plan data ingestion. They know how to manage memory. They know how to manage cache. But that's the concept of streaming data. Not so much the concept of, you know... Exactly. You can apply planning. And you can optimize your query plan. But that's an optimization. You may not get a factor of 3, 10, 100. If you're very, very bad at writing SQL, it saves you from shooting yourself in the foot. But assume that you're very good at SQL and you make a query that doesn't require a lot of planning and optimisation. There was a hand raised in the bottom. It's relevant. I mean, all of that that you've said contributes, but it's not the major contributor. Why is it Python is lower than C? C? But what do you need to precompile? I mean, what do you need to fully create a binary from your program? Exactly. You need to run, you need to infer the types of every single of your operations down to the end of your pipeline. And lazy evaluation needs that. Before you run a single command on your data, it must know how the outcome looks like. Okay? So that it can know the data, know the types at each step, know the shape of the data at each step. Then it can optimize the query and optimize the data fetches so it knows which piece of data to get, how to store it in memory, how to process it in cache, and how to generate the result. It's about types, sorry. Can I get some more work, please? Sorry. Technical stuff. So it's about optimization and types. That's why Polar's is faster. By the way, so Pandas is like the Python of DataFrame libraries. And Polar is like the Rust of DataFrame libraries. So Pandas is written in Python, optimized in Cython mostly. And Polar's is written in Rust. They're conceptually each one similar to the language they're written on, okay? For instance, can you give me an example of one application, one operation that you cannot, that masks completely the types and the layout of the result? Apply. No, no. Apply, why is it apply? What if it's typed and what if it's atomic? Assume that even with atomic and typed functions. I'll give you an example, because this is a hard question. Pivot. If you try to transpose something, the shape of the resulting data frame depends on the data. So you want a pivot table, you really have to read the entire data frame, check all the types, and then you can pivot the table. This is something that Excel does very well, Pandas does very well, but if you want to This morning, York told me that, what's that, a pipeline breaker? They call that kind of operation pipeline breakers, that at some point, the query engine has to stop, check all the data, see how it looks like, store all data in memory, and then go on from that step. There's no way in which you can optimize the entire execution plan. At some point, you have to stop, look at all your data, data, have it in memory, memory is very slow, cache is fast, memory is low, and then go on from there. This is why if you use, who here has ever used Snowflake? Snowflake supports pivot. You can create pivot tables with Snowflake, but yeah, I mean, maybe it takes you half your life because yeah, the application is super expensive. Okay. So we're saying now we have super efficient type query engines that load data very fast. They can optimise the entire query execution, okay? But how fast are they really, okay? And how do I use it? And since it's good, because since you all know about pullers, I will do all the examples in on DuckDB. Yay! So the way DuckDB works is super simple. DuckDB, as I said, it's an in-memory embedded query engine. It's very similar to SQLite, in fact. Where's the query? Sorry. this one, and this one. So, what am I doing here? I'm connecting this notebook to my data lake. Not this one. Yeah, this one. So, I'm connecting and, again, who has ever used the S3 API, you need to give a key, you need to give a secret. I'm running my own endpoint, so I have to give it the endpoint. Otherwise, it points to Amazon. Okay? And look at these two lines. What is it happening here? I am creating a data set from a folder. What is going on here? And let's look at boards. Let's look at what boards is. Who knows about Arrow, PyArrow? PyArrow, I mean, if I said that DuckDB and Polars are probably the best thing that happened in the last decade about in the ecosystem, Arrow was one of the things that made all that available. So it's that underlying super well coded library in C++ that deals with all the tiny little details about working with datasets. So this simple command here turned this structure of files that I just showed you, so this nightmare, let me clear, turn this nightmare of nested folders into something that is a data set, okay? So it took all my storage and turned it into something that I can work with and database engines can work with. Because it collects all the metadata in a single place, so DuckDB can fetch all the metadata it needs and work from there. Okay? I mean, this is important. Any question about that? This is crucial. So is this puzzling in any way? Who finds that impressive? I don't need that, that's a very good question. Who recognized this format of partitioning? How traditionally called that style of partitioning? I mean, that's one of the details it takes care of. It recognizes that that's a hive partition data set and immediately sees that it's partitioned by board and by month. I mean, it's in the folder structure. Why would you need to tell that that's partitioned by month and board? You don't have to do even that. It figures out, oh, that's a high partitioned data set, great, thanks. Which is yet another impressive thing. turns this nightmare of files, of hundreds of files, into this. Okay. This. Oh, by the way, here I have examples in polars, by the way. I'm saying data, I'm saying, I'm talking about, I'm talking about DuckDB, but polars can also write Parquet data sets provided by Arrow. So I can, sorry, PL boards, and I can ask pollers to do the same, like, please load all the data set, and streaming collect the results. It takes some time. It's a large, very, very large, hundreds of millions of games. And it's something that usually takes about a minute, but, I mean, think about that. Hundreds of millions of games or tens of millions of games, I don't know the size of the data to do this crunching right now, but with a data set that is way bigger than the memory of this machine, and do I have an outer memory? Do I have something crashing? No, because as I said, this is a main memory query engine and can stream the data, knows everything about the data set, because Arrow is telling Polus everything about... Oh, it's not. Polar, by the way, DuckDB doesn't crash. So if I can try to get the same in DuckDB, I mean, this is Polar 16, I didn't install the latest version, and about until very, very recently, DuckDB is way more memory efficient than Polar. Let me load things again, this, this, this, .db, .db, boards .db connect. This operation takes some time, .db connect, and for instance, if I want to do very, very, very, very simple, simple applications, I can do con execute, good, select, star from board, limit 10. So question. How many files does this operation need to load from the data set? one, should that be fast or slow? I mean, 333 milliseconds. Probably spend way more time looking at metadata than actually doing the query. But hey, there's a very, very large data set there. Okay? Uh-uh. So I'm gonna execute. I don't know. Data set, let me... It's easy to know. Okay. This will take a while, probably 40 seconds. . No. Oh, sorry. Probably print the whole thing. I hope this one doesn't prove me wrong, but I shouldn't. For interest of time, since only ten minutes, I will expedite that. So yeah, it's not a count star, it literally has to read a lot of things. The issue is you're opening every single file, but it's still metadata. Yeah, you have to open hundreds and hundreds of files, so it takes some time, okay? Once you're able to load all metadata, you can make complex queries about, you see, millions of games in, like, 40 seconds. of memory is like three, four gigs. And that's I think it's a data set that's 100 gigabytes. So it's something that you can do very efficiently. Dr. B streams data very efficiently. By the way, how would you hide this? I mean, how would you tinker with the roulette? Let's go back to the roulette. What would you do to get rich? Very good. It's this query here. So it's a relatively complex query. I mean, first I have to group by game. And I have to see, I have to count the outcomes and I have to count the number of players. So this is a relatively convoluted query. And DuckDB can do that, and you can see that, yeah, the number of zeros, so toss, the number of zeros when the number of players is the maximum possible is off. So this is the toss, like what happens when the count of bets that have the result of zero and the number of players I have playing at that moment. And I see that, yeah, if you have a count, so this 26 should be slightly off from 25, but not two times the ones that I get with 25. So this is the culprit. I can go back to the judge, show these two numbers, and say, yeah, they have to go to jail. Sorry for that. This is not playing Monopoly. You have to literally go to jail. And so, yeah, I mean, this is something that you can run in a very crappy computer with a data set much larger than the amount of memory and even having computing constraints. So this is why this is all revolutionary. By the way, you can also run that with Spark. I mean, it's fine. So running Spark is very, very, very similar. Spark is very efficient. It also takes 22 seconds, so it's also very efficient. But my point here is that it's important to know a good amount of technologies, because many times you will not use what you want. Sometimes you will use what you need and what's best suited for the job. So yeah, this is a query complex that's a common table expression running on a single multi-core node, putting a billion of records using the resources of a phone. Big data is a myth. Big data is a myth if you don't know how to optimize what you do. You may be in a big data problem. Maybe you have two petabytes of data, then you're in big data, but think twice. If you have about one terabyte of data, think twice. there are technologies that are able to crunch those numbers without paying I don't know 10k a month I can skip that is this spark yet no it spark offers more things spark offers resiliency I mean you can really literally crunch a hundred petabytes of data and since will it will snapshot the data in all intermediate steps it will get to an end so it will not crash so the resiliency that Spark offers, all these tools that aren't offered yet. But yeah, maybe. Someday. You can use ClickHouse. ClickHouse can query the same, can query data sets, can query S3 storage. From all the things that I tested on this data set, ClickHouse was the fastest. Okay? So there are many technologies that you can use that can reduce the amount of, basically, money, you need to get to your solution. And that's it. Two minutes left. Any final minute, question? If we have still time left, I can ask some of the Slido questions. So will your Notebooks presentation be accessible after the workshop? It's already accessible. It's on GitHub, right? Yeah. Yeah. And then from the beginning, someone asked, why not use hidden Markov models for checking whether the casino is fair? Because statistical tests are easy, and they're all on Sci-Py. OK, that's it from the question side. OK, there's one more. One more. Sorry, everyone. I know you're hungry. Just bear with me for a second. I'm really tempted to now on Monday go in the office and rewrite all of our Spark stuff and pull up something. But what do I do if I realize, oh, it actually does fit in one machine? Is there an option to then scale up later with using Jack and Theo for this? Not yet. But it will come very, very soon. I'm sure of that. The same that now you have the Pandas API in Spark. This is something that happened in the last two years. But it will get industrialized. I'm sure of that. I mean, the difference, many technologies of columnar databases are very similar to relational databases. The only difference is that some are optimized for relational information, others are optimized for analytical information, but the underlying operations is very similar. There's around 202 orders of magnitude in performance between ClickHouse and Postgres. The only difference is the case they're optimizing from, the case they're optimizing for. That's it. Both are awesome. Both are incredible pieces of technology, but they're optimizing for different use cases. And all this is just bringing old-school technologies for query engines to memory to crunch data sets. That's it.