SBTB 2023: Shea Hawkins, Open-Source OLAP on Open-Source Data Warehouses.

SBTB 2023: Shea Hawkins, Open-Source OLAP on Open-Source Data Warehouses.

Recording: SBTB 2023: Shea Hawkins, Open-Source OLAP on Open-Source Data Warehouses.

so um this is a a series of observations about the way that two systems work synergistically with one another uh in a way that can honestly they complement the strengths of we and weaknesses of each other and they extremely effective ways to get data into the models so this is open source olap on open source data Lakes um my name is Shay Shay Hawkins I am a principal software engineer at demandbase uh we do businessto business marketing so in this sort of quest to give um models and users uh really the best data that they could get uh you have two two components compon that uh or really two different audiences to think of uh you have the data scientist audience which is going to be taking the data in order to train a model usually um and then you're going to have the other use case which is like the user and fast latency systems and so the question is how do you want your data scientists and how do you want your users to be interacting with your data and hopefully it all works together in a way that makes sense um and so data scientists uh the biggest factor that differentiates them from other users uh this is an oldie but a goodie but it's also a new new implementation which is that the data scientist excuses now that my model is training um and usually you can get away with a good few hour breaks doing that um but this is a reason why data scientists are the ones that they don't really care about how long these things take uh not it's they're not powering a UI they're not impatient for record retrieval like a user would be um so yeah they have a higher tolerance for query time um they like first class streaming or batch streaming so uh whatever technology makes that easy to work with they often export huge amounts of data across many many partitions um and so this makes their use case a bit specific and this is where Iceberg and other data Lakes come into play uh iceburg is mostly a partition-based S3 system for organizing data into schemas it's kind of like an indexing service for paret um and then there's also uh D Delta Lake and uh hoodi or hoodie um excuse me on the pronunciation on that but uh regardless there are a couple of other competitors uh iceberg is kind of the one that open sourced it first and they were really um intent on being decoupled from any specific Service uh to put it um gently uh and so Delta lake is the data bricks offering um it's almost always faster than iceberg is uh and they've been adapting a greater amount of Open Source me intent essentially so uh honestly they'll probably be very competitive with each other but Delta Lake did start as a data bricks sort of coupled system so if you weren't using data bricks you kind of were self- selected against but Iceberg uh and these data Lake systems are durable they're usually quite cheap and they're acid compliant so you can run transactions against them they also decouple the comp compute from Storage so if you're comfortable with that the disc cost then you can just leave it in the cloud and it doesn't really cost that much in comparison to most things and uh you know it's slow so that's one of the main problems with it is that it's just a uh you know it's too slow to serve users kind of like Snowflake and so here's a performance comparison between Iceberg and Delta Lake uh you can see that Delta lake is probably 10 to 30% faster in most scenarios um though you know it's just a matter of where your values are uh and Delta Lake I think is going to become a very very competitive tool as well so they're probably both great choices why not snowflake so we had the Unicorn that uh recently got released and uh you know why don't you choose Snowflake and honestly this is gpt's best attempt at saying no no open source for snowflake uh it just doesn't have much uh like they are trying to interrupt with Iceberg but they're dragging their feet on it because they know that hey this S3 based open- Source package does a lot of the data storage stuff that we do then you attach spark on top of it and you know those two things Union together is just snowflake more or less so anyway those are the two battling it out a little bit right now but snowflake it will offer some good products I'm sure users so users on the other hand they're going to requery the same set of data over and over again the first time it doesn't work because they want it now and so they will kill the systems themselves but also it's you know usually users would like something that retrieves data quickly even in the purpose of an LL like in a you know rag you're trying to retrieve documents you probably want it to run fairly quickly because actual paying people often times will expect fast performance so that's where olap comes into play and olap is like click housee and star rocks and other systems that uh you know they their purpose is to aggregate data they're extremely well tuned to columnar based aggregations so that means that they can do group buys real quick and so that's useful for analytic style dashboards and things like that now as you give users more and more complex abilities when it comes to like writing their own queries uh they will use it as much as possible they'll they'll make as complex of a query as they could possibly they'll chain together 30 different nested Loops of lists like they just do very very creative and like honestly impressive things when you give them the ability to build their own queries and so anyway olap is fast at things like that uh if you tried to do some of this work in iceberg you'd be looking at like 5 to 10 minutes to get an export which just isn't really a great experience so olap on the other hand very fast but their rights are just unreliable um the the problem is they're not actually really Atomic uh a right it will become eventually consistent but depending on the tool that you're using it's not totally reliable always reliable at doing that um and then usually you couple the storage along with the compute so uh you know if you design your system well you can always make it so that you can Auto scale your pool nodes depending on load and also Auto scale your size it's a good way way to cut cost data storage so I breezed past it but there are there's one key similarity between these two systems that's worth paying attention to so data Lakes usually sorry to tease you that will be the end I gu uh usually compatibility with Native parket or similar storage so like it's it's all just open-source columnar data formats uh that's what data Lakes they're just an indexing service on parquet files again uh they decouple compute and storage the nice thing that they they're cheap as they rest uh olap on the other hand often times comes with some sort of Preparatory St storage for format that's geared towards performance uh that's the way that they're racing so their storage formats will often times be specific to the way that their engine queries data uh and they couple storage and compute but the main similarity between these two the one that really makes them work together well is partition Centric storage so partition Centric storage kind of was adopted in postgress though it's not its main use case and there obviously you ever Ed snowflake they use partitions too uh but that's what makes them a pretty good union so the symbi symbiosis between these two systems um olap weaknesses inconsistent rights it's bad at give me the universe style exports that plays directly into Delta lakes or data Lake strengths uh data Lakes are asset compliant they're great exporting via spark so you can dump tons of data out you can batch it into streams um which even you know most of these you can get a stream of a query result from an OAB engine but it's not going to be that happy about it uh I mean it could page through it at a kind of slow rate uh but honestly you probably want to batch your um batch your messages from spark and S3 instead so if you're trying to get a stream of information to train a system it's a pretty good source data likee weak weaknesses is that it's just slow and it can be bad at concurrent reads it depends um if you're well parti your S3 keys won't get too hot but uh you know if you try to actually use S3 as a backing on a lot of concurrent reads and you don't cash well you'll you will hit rate limits they'll slow you down on that um olap strengths is damn Fest and it's no Storage level read competition meaning like yeah you get you're technically doing a bunch of iops against the disc so that's why it's worthwhile to provision extra iops onto these these these click house drives but otherwise there's no real competition when it comes to scanning through data uh and then aggregating is a different thing entirely so they paired together well and it's easier than it's ever been the main thing that's happened over the last couple of I guess maybe four months is that these two systems have truly and really adopted Iceberg as a native standard into the uh into the engines themselves and so that means that you get to do some really cool things like you just get to create a new Iceberg table in Click house or in star rock and then you instantiated a cache for it so star rock is a head of the game on this because they are actually making it so you can cash your partitions locally on disk click house on the other hand mostly just caches intermediary data sets or the final data set so uh star rocks in general has been producing a um they're producing a very awesome feature with this Iceberg catalog so um this makes it instant like literally the only thing if you want to do is run aggregations over your data set set up your catalog put on some I don't know pretty decently fast storage drives um provision a large cache and then uh run your queries and you know if your cash is set up so that it never expires uh you'll just always come back to fast queries depending on what data set you are working with now users might or might not be accepting of that uh it's a kind of ux problem to figure out hey how do you time a user's behavior and let them know that this cach is heating up so you know um that's great systems because it's fixed cost um and simple like you define your partitioning strategy wants you barely even have to like this thing will inherit the schema of the Upstream so it will just migrate along with the data lake or data warehouse if whatever you want to call it um so yeah the different cache setups for the two of these systems um pretty basic but um something that's not captured here is just that the click house caches are really just final result caches so it only works if your query is exactly the same um whereas the star star rock one is um essentially the raw data uh now click house might store an intermediary but I I would really probably encourage them to start to adopt Iceberg at a greater rate um and so more complex cases uh there's not a lot of reasons why you have to have a more complex system um it does sometimes happen when you're really optimizing for specific specific use cases sometimes you want your partitions that are in your olap engine to be a bit different than the partitions that are in your Warehouse or lake um so this partition sync thing um it's it's a great design for like a really simple system that listens to um directories inside of S3 and then you can send them all as sqs messages into a pub sub um and and you just listen to the keys and the keys have the partition IDs in them so you r x out part X part y uh then you use that in order to trigger partition updates on whatever data sore like lower data store you have or like specifically optimized data store um you know the the use is a little narrow uh essentially this is a system for hot loading data um that's the best way I could describe it it's probably pretty wasteful because of this ux problem that I previously stated but you know it's functional um and and you know sometimes you will get into these backend like cor Corner cases where you have no choice but to change the data structure or partitioning structure to match the queries um and so you've got these two different systems you've got the iceberg partitioning scheme in the left and then you've got the uh click house or olap engine partitioning scheme in the right and essentially there's just a hierarchical relationship between these partitions where like if you have one part here uh you want to subdivide it further on click house or this is actually a metaphor for skip indices um there and actually click house or most olap engines can do better without that much partitioning uh so don't overly partition your data and yeah anyway so you listen to changing to S3 keys um you just set up an sqs every time file gets updated uh you can put a filter on it so every time the metadata. Json gets updated uh it will trigger a downstream partition update and refresh your caches uh and the nice thing is that in order to talk to Iceberg this is unique amongst it um it it doesn't need really any AWS resource like this is python it just needs both photo access to S3 uh you know this isn't fast it's not like SP booting up a spark cluster to run some queries over the data set uh but it's pretty lean and it is fast so if you're just doing like sort of file based operations or inspecting the schema or those sorts of things uh this Iceberg python library is great uh it's a friendly little project and I think it will get bigger so that's the first link here Iceberg python um um and then we've got star Rock's documentation again their data cache system is impeccably designed it's exactly what you would want out of it and they're also great at data updates uh it's not something that we've stress tested yet but I think that we're going to get there um click house documentation for the equivalent Iceberg and then finally I'm here as part of demandbase we're a busino business um marketing company and we have a pretty sharp group people with us um I think that we have made a lot of good decisions and we do hire so if there's any Talent out there that would like to join I'm would love to talk to you honestly um then otherwise uh you know I've got a I've got a GitHub it's way fairer but I am planning on making it more active and then um yeah any questions