SBTB 2023: Lei Gao & Jeff Xu, Harnessing the Power of Dual Query Engines.

SBTB 2023: Lei Gao & Jeff Xu, Harnessing the Power of Dual Query Engines.

Recording: SBTB 2023: Lei Gao & Jeff Xu, Harnessing the Power of Dual Query Engines.

and U thanks a lot um for attending our session and I will talk um you know I will work with my colleague Jeff and presenting some uh dual quy engine uh no it's a long title but basically we are building the Dual qu engine and so both interactive and advanced so you can think about machine learning or data science and bch pro prising okay and here's the today's agenda you I will have a quick introduction talking about what the business problem we try to resolve and uh and the drive will talk about more details including you know um generally what the Dual qu engine we provided and a lot of technical details and license we learned and through this dual quy engine okay and firstly introduce myself and my name is leay and I'm working at work day and leading this team to build the internal data platform and Jeff and he will present later and he's our principal engineer he will share more technical details so before talking about the cor engine quick introduction to workday just in case some people and that don't know it um workday is a leading provider for the Enterprise Cloud application including Finance HR and planning so uh firstly uh I will talk about um you know why we want to provide the duqu engine uh because from the technical World always you know simple is best better right and single if single qu engine can resolve all the issues no reason we provide tools so but give a generally context and we've build this uh query engine and over the last three years and we are migrating our data platform from our private data center to the A and it's a big Mega program and along this journey a lot of um business question we want to answer or resolve one is for example scalability and reliability how we can serve the whole company and you know with the um Kindle of the expected performance and another one definitely moving to the cloud cost is another you know Factor you always need to consider oh how we can reduce our cost but provide better performance and last but not least uh usability and during our data center we build our data platform I think maybe 10 years ago and we are using we are using Hive and lat we we are using spark a bunch of analytics and machine learning tools and along this journey definitely we want to modernize and the platform make sure and the workday of the engineer can use the you know most update to date technology and for for the work uh but try to uh along this journey and definitely a lot of uh challenge we try to um resolve and but today we want to uh focus on the um data accessibility and people potentially think oh it's pretty simple people can carry the data that's way result the problem but if you look at the company the the the challenge is very uh complex and I will not talk about the detail and I will share the the the the the presentation later you can see all the details but what the table presents is the complexity of uh addressing the accessibility at work day or even in any company if at there two Dimension complexities and if you look at the the the columns is that people want to get access to data or cred data from different place we have people want to quate the get you know answer from their laptop definitely and but we also have some very complex data pipeline running in our data center and even in the public Cloud how we can make sure people can access the data in different place that's a lot of challenge a lot of security and privacy question and the technology definitely and if you look at the rows and we have different use case and simple enough like the people want to get on you know ask some ad how questions and how they get get the answer quickly and we also have some reporting and the dashboarding how they can make sure the dashboard refreshed you know as expected and quickly and also we have some people want to publish or sharing some data and even some data scientist want to build a you know more complex data pipeline or even machine learning so you know address different dimension and there are different requirements uh I just want to present you know try to address this question is not pretty easy so this reason um we want to address this question using the Dual quy engine and this one is a very high level abstraction in how we address this problem definitely Jeff will provide the technical details and but from this uh chart you can see and we are using price to it's pretty good for some uh C uh kind of stuff and similar to Snowflake and uh you know people and can use to answer ad hog questions build dashboard and Reporting and but also we have provide spark and people can use build some data pipelines and some data engineer build you know complex ml pipelines or even some data science models the the challenging thing is that how we can UniFi the API when you talk about UniFi you talk about a secure and the unified API and on the top level is really like a people can access this API from different place using a unified secure model so I know this one is too abstract and uh so this reason je is here and provide more detail how we we can achieve this goal and you know welcome to J talking about the the the cor engines thank you Le thank you everybody so yeah I will talk a little bit dive a little bit uh deep into uh how our journey goes and what the system looks the platform looks like so these are the two years ago when we started this journey uh we have run this for almost 10 years in our data center a very laxy system and when we need to modernize it into the public cloud and this where we stand and we we we exactly know that spark you just anybody who will count data you know it's something you have to have because you got to have issues that you have to use code to solve the issue and uh so but spark is really I have full respect to the whole spark Community but spark does not really have a good SQL interfaces from API perspective uh from JDP you can use uh the uh what's that Services we have been running um f I forgot the one of the term but there are some workarounds there but there I really don't like Sparks architecture of having a driver when you collect something there and the uh when you are collecting and when you have lots of tools running on top of you a single collect can crash the whole driver and messed up the whole thing so yeah um spark is great have uh develop apis but from the access perspective it's it's just something you have to have but you there's another part have to be solved also Spar does not really deliver the uh interactive speed uh typically in our old days people submit a query and people go away not just having a coffee they can go away having a bathroom break and uh even take a shower walk and coming back the answer is not there yet so very very in in inefficient in most ofar thing but in workday we believe that we investigated the whole platform we have thousands of users and the SQL is still our dominant uh use case we believe 80% of the our business questions is can be found by SQL but the other 20% having business significance is also have to so we have have two piece of things we have to deliver so so we really need a a great SQL engine so this diagram presenting uh is uh our interpretation of currently what we have in workday um at the very bottom is uh uh is our data itself it's in the high meta store plus dating S3 secure by C and uh uh all the workday service around Global having a uh centralized we have a data pipeline continuing pumping up this uh observability data the event into the into this as thousands of tables and the one the biggest box in in the middle is something we call data access services that we created that we don't have before and this were the unified API and services L just mentioned it's not a sexy name but the name reflect what it does it's providing a unified data access through the apis and on top of them on top of our hosted analytics suit so including uh catalog super I believe pretty these are pretty standard analytic uh Stacks that uh super set for visualization you have notebook and also have pipelines and within this data access service if you look at here we can have we have now instead of having we used have the our spark stack there we also have the I use trino and the Presto in randomly but they are essentially the same thing also we have some platform service and accs SC will give us ability to authenticate so the most important abstraction is the apis that is providing uh seal spark and AG apis so these are the general architecture in a single diagram now I dive a little bit deep into each of the uh the actual the whole things we from inception to put into action took about year and a half what we have done through this so these are the most important things we have done in to to customize to customize the open source trino to with all the Enterprise Integration so the you can bring up a trino pretty easily probably in half a day a day with the current public Club but having all the right Enterprise ination done is really is not easy so this are the for example the um I I try to highlight some important uh integration of down for example authentication we use both covers and also the JWT authentication so we can so we authenticate in both impersonated service users or the end user directly um the governance part we have query limits we I talk a little bit more on the Lessons Learned uh resource groups so this how we regulate the users uh rbsc Rob BX control we use the tro native uh robas control customizations uh connectors uh we have high uh myo also use system and jmax for observability so these are the customer ation uh trino is a uh if you are not familiar Presto trino is a uh food Java application so all the plug-in customization is down through uh customized uh plugin so we also have the group subsystem we customize to integrating with our uh adab services and also we have a customized uh query um query lock plugins so these are give us a very deep insight into what the what the user are doing what system are doing so so yeah these are the about the customization of the how we integrate the Trin services and uh the next slide is about uh the this box stack we heavily uh Leverage The Adas EMR already having most of the had components here and also we have we put addition of the Apachi nox on top of the um if we look at from bottom top it's going to be the standard Hadoop stack that is hdf cams yarn then spark on running on top of it and we use Livy as the open source Livy as the API uh front end for the spark and also we heavily leverage if you look at a spark if you want to run Sparks through a pure API it's not enough you have to leverage you have to put your program all the resources onto the cluster from the user client to the cluster so we have leverage web hdfs and also the uh young to for off to Young apis and the nox give us ability to uh to do the uh centralized uh J JWT authentication and basically bring the user from user end us identity to the Cur world and also it's proxy actually nox proxy the whole Hadoop apis pretty nicely so yeah and and based on this we actually build our own spark CLE spark batch all the our customer Solutions pretty nicely so here so this is actually the uh we developed this for about half uh one and a half year and having a six month bring to migrate all user from data center to new platform this how the whole landscape looks like today so I mark them all as blue color as all the platform components everything else is done through the user because we providing uh selfservice user model we don't re we don't tell user what to do we give them the ability to to do their works so so we have uh migrated thousands of users so Ste underneath is the data set the data access services and we also develop our our own command line interface just like dat CRI so we call C it started as a hobby site project has become really really essential part of the whole platform to because if you look at all the apis there are very low level some of the very low level rest API Dev and uh we use our command line interfaces to bring Bridge all them together to making the user Centric and maybe if I have time I can do a little bit query a little bit demo later and on top of our hosted analytics s and our user right now have the full ability because open apis they can do their own develop environment doing all various kinds of Integrations very easily they can run tablet desktop they can run Jupiter and just directly use a l CIS or through the apis also the workday applications can integrate with the API directly with the actually we are very very happy with uh the Presto SQL apis with it lots of integration happen just uh uh just naturally um Lessons Learned uh we share a few lessons and best practice we have done this um I will cover them just going by one by one first is make best use of the two engines now we have two stacks to give us full ability but they each have their own strength and also weakness uh trino is very very strong on quering data you give them different joining the data querying data set so we basically we have we drive all most our query use cases to on the Cho site it's require a little bit migration study and learning all the things but it wors it because we generally observe three to 10 times FAS in general it's very general this a estimation faster on TR when you run same query versus similar amount of spark uh spark with similar amount of resources if you get three times faster basically means you can one third of money you spend right in public Cloud C cost is one thing you constantly bother you so also but but spark the strength is on the meta met metadata metas store itself because CH doesn't lots of things to you so it's generally is for query purpose you try to bring the data together but we have some cases the 20% important cases that you need to write the data and these are the things we highly highly utilize spark because spark give you whole lot of flexibility in write all the operations so uh governances um we are a open platform to our thousands of internal workday users so governance is extremely important user doing lots of use if you look at from platform perspective lots of dumb things happening every day and the runaway query we have seen queries running for the worst qu thing running for two days on a system a large cluster so extremely important uh you put enough governance and uh to keep platform healthy and ensure the fairness uh for example we reinforcing partition column in other the filter you have to have a partition columns because otherwise a food table scan it can't scanning it can scanning paryt of data if we have some sometime a business user come in they have no idea what's par have no idea they just do select star this happens every actually every day pretty much and uh on chel side uh we do uh some of the general query Pro query based limits like max memory Max m per note uh we also very important we apply a Max execution time to enforcing you cannot have a wrong way quer is there or if you have a query running for let's say exceeding the the maximum ex time you have to optimiz your query so also we use Resource Group to limiting per user or system how many in parallelism you can have and Spark we did something similar so these are the price actually we are paying for having two to anything you you get something you pay this are the price we pay here so you figure out two qu engines and sometimes their configuration are quite different um another important learning is that uh separation of ad hoc and the pipeline uh workloads we have learned very hard lesson from our Alex system we mix everything together in a single huge cluster is so bad that you cannot regulate things easily so sometimes a giant ingestion workload there can pausing the whole basically blocking making the whole cluster unusable for 15 minutes I'm so so here is the one we design it from ground this a new system we completely separate them from at the hardware level at the physical level we we were using different cluster with different regulations one for ad hoc you have ad hoc is is meant to be your development purposes you run some small queries development figing out what data is testing your code and when you run the pipeline you do Mones of aggregating all things you want to put into a different cluster so these are we have but two completely separate separation of different solutions the each Hook is different cluster and proven to be much easier to regulate it's it's like you when you design object oriented you at subass level you are very clear have a very good context this is exactly what we do and this are exactly regulations we um also uh I don't know if anybody run pressor trinal uh as a heavy user but this are something I we read it before before we Deo it we read from like um Uber lift they all having some kind of weekly restart because chin are very very heavy is is jvm and especially if if you look at this one we are running thousands of query every day very heavy query basically this jvm being hammered hammered really very badly and uh lift having a weekly side finally we figure out that we want to actually daily restart if you look at this one of the one of the uh CPU usage of 24 hour the top top one if you look at some of this window are 100% constantly for like three four hours I believe these are the Deep the jvm uh deep uh Fu GC starting kick here and it's just cannot get our system getting slower and everything getting slower and finally we say we we we experimented again and we figure out that we do actually uh a daily a daily one low traic time on the pipeline side we having a retri mechanism basically we every day we restart the whole service without introducing without bringing down the cost of anything one or two minutes these are what the new I actually really like this spiky pattern if you look at here this this mean there quite some qu but these are really healthy keep things healthy basically perform really well when we found that a tral never restart for 5 days it can be 10 times slower than supposed to be can be more than sometimes you can just see the io is slower everything is slower just you used to have a query returning back 10 minutes now it's take an hour or even longer so these are what the uh practice we have been doing uh what we still have time okay yeah the final one uh the page I want have is the is the observability um we quite a lot we believe it's actually we spend we invest a lot in avability and and uh all the operational so OB in my uh mind we have run this for six month and basically you know what us are doing I system find out the extremes the the extremes and figure out why unreasonable figure out a way to Che them out and also figure out how your system being utilized if your system being underutilized you want to making your because we we we have been continuing hunting on the cost we are over budget and uh uh been pushing to uh cutting down cost but you want to cut it at the right place so you always relying on the obility data to tell you where to cut instead of blindly cut things so we have been doing quite a lot of this and uh even before the meeting I told L that for example one of our I can give an example of the uh the uh very I hope this window is still open yeah this is one of the obility data this is our ad hoc we have 20 noes here uh 20 models note here but here you can here this one week you can crear here where the where the weekend is and also we have very very cyclical pattern of of the uh we having two User Group a majority of Workforce in the in the presentant side on us we also have the Europe having some of the user using but if you look at if you zoom into in a day or something you can clear see the US starts to work and Europe starting pick up some work so so if you look at here and it's it's fairly certain you can do certain optimization so that's why I'm trying to convey in this um oops I think yeah I probably in the it's uh full screen mode anyway I will use this one yeah just uh um I I won't talk too much detail on the how because observability generally means you capture enough information through uh metrics through locks query locks these are all the important important you can do with your system and uh keep it operating healthy and uh uh we are we right about time any quick questions we can and also we will be in the after this meeting we'll be uh after this we'll be in the um yeah I won't go through this one so we'll be in the Q&A sessions if you have any yeah let us know anything from you five minutes five minutes yeah any quick questions or any comments um anybody yeah so maybe I can do a quick very very quick demo of the of the client uh of the of the C I have we found it I didn't add into the slide so it's like the uh um because our our platform is called oops because our our platform is called uh feros so it's it's essentially if you look at here is like um just like uh uh the uh AWS so ACI so we have a command we have Services we have uh we have six so most importantly we have the C services so we can so we can do we can run a SQL we can bring your own data to the platform we can do bring your own data bring your SQL to the platform all the various things so wrapping everything as uh and we we even actually uh Leverage the uh the open source C framework so everything the usage is like so for on the spark side spark is more complicate actually each of the command wrapping multiple uh Hadoop all the uh API to make it work so so these are just uh and and this is one of the screenshots the one I just run before the one I do I run this run everything through our Sparky and getting an very simple aggregation back through uh Json so we just learn from the what adress does with the CI so you give a command you give your options and the output is a Jon and as you can do lots of automations a user can do very easy automations through here it's become starting with a side project and become a very essential parts of the whole platform actually yep that's uh that's about it thank you a