6 ms·
How Query Engines Work
- slt2021 3y agoCode is very elegant, love kotlin in contrast to java
- iamcreasy 3y agoAt the beginning of the book it says 'The real power of Spark is that this query can be run on a laptop or on a cluster of hundreds of servers with no code changes required.' Is this accurate? I thought the power of Spark is that data stays in memory in each node in the cluster. Being able to run on a cluster in a fault tolerant manner was achieved already by Hadoop ecosystem.
- zjaffee 3y agoThe power of spark is truthfully that it's really good at running sort + aggregate type queries on columnar stored data. Once you start needing to do complicated joins it becomes more about how you structure everything.
- adammarples 3y agoIt was achieved by hadoop first, but it is still a big feature of spark
- Bootvis 3y agoExcept that doing very, very simple stuff, like counting 10 rows, on a laptop is excruciatingly slow.
- qsort 3y agoWell I mean, that's a problem with all those solutions. If your dataset is so small it fits in RAM it's simply ridiculous to reach for those tools, and you don't start seeing real advantages until you have truly massive datasets. Anecdotally using Spark on less than 1TB just means wasting your time, but in Spark's defense, that's not what it's for.
- iamcreasy 3y agoDo you mean 1TB of data? or cluster with total memory capacity less than 1TB?
- qsort 3y agoI mean data size. It's anecdotally the threshold where "normal" ETL methods start having problems. Obviously performance is hard and the only correct answer is "it depends", but I have never seen Spark improve things on datasets that small.
- ramraj07 3y agoIn theory sure in practice I beg to differ. You tend to end up writing different levels of annoyingly intricate spark code as your data volume, processing comolexity (and infra constraints) increase. Anyone who says this is not true hasn't tried to join two 10 TB dataframes on a highly skewed key with a hundred million unique values without spending thousands of dollars and without failing at random instances.
- poorman 3y agoI think in general, the idea is that you can execute SQL using the query engine on Spark.
- refset 3y ago> KQuery does not yet implement the join operator. Whilst I applaud this book writing initiative, completing it could easily become a lifetime's work! It will be a fascinating journey to follow along with in any case. Apache Arrow could (and hopefully will) really shake up the database industry in the years ahead. Whatever eventually supplants Postgres is quite likely going to be based on Arrow - polyglot zero-copy vector processing is the future. Aside: for anyone looking for a more theoretical overview of databases and query languages, this ~free Foundations of Databases book still holds up well http://webdam.inria.fr/Alice/ http://webdam.inria.fr/Alice/
- c0balt 3y agoIdk, but I'd rather see a new iteration of PostgreSQL, similiar to Hydra[0], with a new engine become the upstream instead of a whole new database. There's a lot of experience about db operation and how to approach MVCC encoded in PostgreSQL that shouldn't be underestimated. [0]: https://github.com/hydradatabase/hydra https://github.com/hydradatabase/hydra
- refset 3y agoAnother Postgres-based project in this vein that makes use of Apache Arrow: https://heterodb.github.io/pg-strom/ https://heterodb.github.io/pg-strom/ > PG-Strom is an extension module of PostgreSQL designed for version 11 or later. By utilization of GPU (Graphic Processor Unit) device which has thousands cores per chip, it enables to accelerate SQL workloads for data analytics or batch processing to big data set. > PG-Strom has two storage options. The first one is the heap storage system of PostgreSQL. It is not always optimal for aggregation / analysis workloads because of its row data format, on the other hands, it has an advantage to run aggregation workloads without data transfer from the transactional database. The other one is Apache Arrow files, that have structured columnar format. Even though it is not suitable for update per row basis, it enables to import large amount of data efficiently, and efficiently search / aggregate the data through foreign data wrapper (FDW).
- cmrdporcupine 3y agoThe MVCC implementation inside Postgres is probably not the greatest, in terms of overall performance. This is a good read https://db.cs.cmu.edu/papers/2017/p781-wu.pdf https://db.cs.cmu.edu/papers/2017/p781-wu.pdf "Postgres ... configurations lead to the worst performance, and the major reason is that the use of append-only storage with O2N ordering severely restricts the scalability of the system.... "
- cppluajs 3y agoReally nice! Anyone knows if there is any list of such website books / gitbooks? I try to bookmark these whenever I come across them, but I believe there must be so many great ones I don't know of.
- luguenth 3y agoI'm amused, that sometimes when you begin starting to learn something, you see resources for learning everywhere. (And I just learned it's called Fequency Illusion or baader-meinhof-phenomenon [0]) Thank you so much for publishing this book for free, I'm eager to dive in! Is there any chance you can also link it as ePub? [0]: https://en.wikipedia.org/wiki/Frequency_illusion https://en.wikipedia.org/wiki/Frequency_illusion
- asicsp 3y agoI'm not the author, just sharing it here. Ebook link is mentioned on the home page: >This book is also available for purchase in ePub, MOBI, and PDF format from https://leanpub.com/how-query-engines-work https://leanpub.com/how-query-engines-work
- polishdude20 3y agoYoutube's algorithm really helps in that regard. I search some flight training videos for a day and then just get flooded with more and more (which is nice!).
- robertkoss 3y agoAndy Grove is a great guy, and the book is excellent. This might be interesting as well: https://www.youtube.com/watch?v=NEL6DluUxgw&ab_channel=DeltaLake https://www.youtube.com/watch?v=NEL6DluUxgw&ab_channel=Delta...
- willvarfar 3y agoAn awesome read! Something related that I found out about from HN a few months back is another engine called quokka. It's particularly interesting and applicable how quokka schedules distributed queries to outperform Spark https://github.com/marsupialtail/quokka/blob/master/blog/why.md https://github.com/marsupialtail/quokka/blob/master/blog/why...
- marsupialtail_2 3y agoThanks for the shoutout!
- deleted 3y ago[deleted]
- poorman 3y agoI bought this book back in March 2020 and it was a great read then. Since I've received numerous updates from Leanpub on the additions Andy has written!
- crabbone 3y agoval spark: SparkSession = SparkSession.builder .appName("Example") .master("local[*]") .getOrCreate() val df = spark.read.parquet("/mnt/nyctaxi/parquet") .groupBy("passenger_count") .sum("fare_amount") .orderBy("passenger_count") df.show() That's the first listing in this book. This is a disaster... Query language implemented in a pug-ugly other language which has to rely on two other query languages to function. This is the worst sales pitch in the history of sale pitches. Maybe ever.
- kristjansson 3y agoLooks like it gets quite quickly to logical plans and the actual business of query engines. The surface representation of a query is almost immaterial to the topic of the book.
- polishdude20 3y agoAgreed, I was like, oh cool I get to learn about query engines with basic SQL with postgres or MySQL.
- chrisjc 3y agoThat's the first "dataframe" listing in the book. The first listing is the SQL examples. Both SQL and dataframes have to be prepared for the query engine. You can read the whole book without ever needing to take into consideration the pug-ugly dataframe content. I don't disagree that dataframes are often/most times horrible to look at.
- crabbone 3y ago> The first step in building a query engine is to choose a type system to represent the different types of data that the query engine will be processing. This. Out of nowhere. Why would this be the first step? There's no explanation... Why cannot be this the tenth or the hundredth step?
- mrfox321 3y agobecause they are assuming the reader has already built a query engine and is now re-writing it. first prototype: bottom-up design second pass: top-down design
- crabbone 3y agoSo... what if they wrote a query engine? I want to get some kind of argument for choosing the type system first, but there's none... Imagine you get IKEA assembly instructions for a chair. There might be a few valid ways to start assembling a chair. There will be also ways that make chair assembly impossible. It makes perfect sense in that context to tell you "what the first step should be". I don't see any reason for starting with type system in making a query engine. Things that come to mind as a better first step: select data constructor, or select paradigm imperative vs declarative. Maybe select the level of data consistency you aim for... maybe choose whether querying should be mostly interactive vs mostly non-interactive. There are tons of other decisions to make. This "let's start with types" feels like that Star Wars moment where Vader randomly points at some spot through the cockpit window and tells the captain to head that way. And once the captain enquirers as to the reason this particular direction was chosen, Vader gives some vague answer like "why would you go anywhere else?"
- poorman 3y agoIf you try building a framework on Arrow, you are most likely going to start with thinking about types and how you munge everything you are doing into the Arrow types.
- 3y ago
- crabbone 3y ago> A logical plan represents a relation (a set of tuples) with a known schema. Each logical plan can have zero or more logical plans as inputs. It is convenient for a logical plan to expose its child plans so that a visitor pattern can be used to walk through the plan. This is not a definition. Definition needs at least a modicum of a convincing to do. Like, why did the author decide that a plan represents a relation? Most plans I've seen in my life were grocery shopping list-like, or maybe decision tree-like. I can see no way to get from "plan" to "set of tuples". Also, what visitor pattern has to do with anything? Why is this bit of information even relevant?
- chrisjc 3y agoIsn't the "physical plan" the part that gets the "set of tuples", not the "logical plan"? And isn't that matter of how that gets done more a concern of which physical plan the query optimizer picks, and as a result the query engine that powers that particular data-layer?
- flakiness 3y agoIf you prefer more academic pov, there is a WIP book called "Building Query Compilers" https://pi3.informatik.uni-mannheim.de/~moer/querycompiler.pdf https://pi3.informatik.uni-mannheim.de/~moer/querycompiler.p...
- deleted 3y ago[deleted]