Scale by the Bay 2018: Jianneng Li: Journey of Building a Modern Data Prep Tool...
Recording: Scale by the Bay 2018: Jianneng Li: Journey of Building a Modern Data Prep Tool...
you I sold my name is Janelle and I work at workday this talk is about building a data prep tool on top of spark first things first anything I say may or may not be true and cannot be held against me so pick your battles wisely that being another way I am at work day work day is a big company though so more specifically I work on a product called workday prism analytics what is worth the prism analytics workday is a big company again and we are a cloud solution for companies HR data and financial data that's very important data of a company but when you have a lot of data and actually you want to make sense out of them you want to perform analytics on top of the data so the goal of prism is to take data you're having workday combine it with data outside workday and make rainbows out of them more concretely we take the data that's inside workday at the financial and human capital management data and also take the operational data from the business for example with data from your CRM your Salesforce blend them inside workday for example by applying transformations doing data cleaning operations applying security and then publish them into data sources to eventually be able to derive insight out of them least in size could be reporting qui reports scorecards it could be even virtualization that analysts would do to eventually perform getting dashboards and charts to show to the executives and for this talk specifically I'll be focusing on the data preparation side of it before we're kind of dive into the more details only give me an example of what prism could be is possibly used for what does it really mean when you say data inside workday and data outside workday so for example we have one customer use case that takes the human capital management data their worker data from their organization do some data munging to get the top four performance after workers and then also take from outside work day the stop by Singh schedule of these workers to basically understand whether their stocks are kind of vesting soon so that they should be awarded more options before they will consider leaving their company before their shares that was set up and another thing to kind of note here is that we are really a solution that allows other people to build data pipelines on top of and this is kind of different from most other talks you might have seen where organizations have their own data solutions and they will write custom link jobs or smart jobs to do this data munging queries for us we build a UI that allows other people to visually express what they want to do with their pipelines so adding another stock I want you to all go away with three points in your head one there are many interesting challenges inside data prep - spark is a pretty good engine for these challenges and three workday is hiring and we would love for you to join us okay so two minutes in and finally I can get into some technical details what is data path that have been keep on talking about well if you know ETL from the traditional business intelligence world it's very similar to that but with a modern twist so traditionally ETL is a process by which business you used to extract raw data and then apply transformations on top of them and then load them into a database for faster querying but traditionally this is very technical it involves data engineering it involves complex sequel queries and is typically done across two different organizations you have the data analysts that want to answer specific questions so they tell the database admin to run some queries and finally the database admin will get the results back put them into a place where the database sorry the data analyst can access and then the kind of iteration process is very very long but as data gets democratized you want to be able to have non-technical people access the same amount of capabilities and that's what dinner profit is really about so it should be intuitive it should be easy to use and it should be also flexible enough to handle all the different possible workplaces and another thing that traditional ETL might not provide you is that traditional ETL are already batch jobs where you run the sequel query and finally you get results back and again the time it takes for this is very long and well we want to reduce the iteration time so as people start to build their pipelines they can quickly see their results and have a feedback loop so this is what data prep looks like in prism we have kind of defined maybe - no transformation you might want to think about it so join a filter group by a union into this concept called transform stage and the list of transform stages is kinda on the left is showing the primary pipeline of the of the pipelines and all the secondary pipeline style kind of join into the primary pipeline they kind of hidden away so that it's easy for you to see a progression of the stages on the right side you can choose the number of samples that you want to sample and finally we will show you the examples in statistics on top of these samples and then if you for example click on editing for one of the stages you have the option of editing the different things you can do for example in this case this is a filter so you can add additional filters you can change the existing filters you can do all the things that you can want to do with the filter and on the right side this data that you see is powered by SPARC and that's what we mean by building a tool on top of SPARC we handle kind of the logical compilation and we kind of put the workhorse as the SPARC engine so why SPARC like I mentioned before SPARC while data path needs to be both interactive and needs to be patched it needs to be able to operate on a sample of the data and also be able to scalable and that sparks something spark is good at it's able to return quickly when it restarts is small and it's also able to crunch very very large numbers spark has a very modular design maybe every single part of spark spark ml or I guess yeah spectrum L and smart streaming their optimizer their core package they're all separated into adult modules and we don't need to use part of them then we don't have to use it spark is very extensible we can take any operator and extend it or we can add additional operators and also in addition adding changes to their optimizer so that we can get what we want to do and finally it's a very active community there are many commits going in and releases the front are quite frequent and honestly having this support from the community is very important so how do we really use spark then so like I mentioned again there's two types of workloads when it's interactive and when it's batch and we kind of launched two different workloads two different applications for each one of them for the interactive side of it we launched a always on always running application that well basically stay on forever and be able to service request live as they come in and it results because they're pretty small or typically just return inside memory and then for batch drops we would execute this over the entire data set and then write the results to HDFS and this is kind of like the load process where we write the results in an efficient format that is easy for query so that when downstream queries come in we can just quickly serve the results going little more - you tell how do we really compile this into something that Sparky understand if you're ever worked with kind of logical plans something pretty simple could be say we have important left we have a joint and we have a filter so it's kind of just nested structure of logical plans that can be translated into something that machine can understand but this is a interactive user interface right if we simply just execute everything for workers workers again could be terabytes in size or maybe not workers but it could be journal lines or something else that could be very very large and therefore we don't want to execute over the entire thing therefore we have to add some additional limits in this plan so that we can keep the result small and also hopefully relevant so in this case we would apply sample operator on top of every single skin in this case the scans are workers and in this case we scan a thousand rows out of however many rows that data set might have and finally because we want to the user it really just asked for a thousand rows so there's really no point returning more rules than asked so in this case we would return also a thousand rows by limiting the number of results to be a thousand okay so this sounds pretty straightforward right what could possibly go wrong well let me show you what could possibly go wrong there are many things that could be traps that you might not realize when you're building a system like this because every time user is involved as an interaction users might be doing something you don't expect or the data in the real world is very nasty it's usually not very clean it's very it's not as structured as everyone would have liked and in this for the rest of this talk basically I will be focusing on some specific challenges that we kind of faced and I'll be focusing on joints because that's sometimes the most painful part of these one challenge so is the results could be too large and for an interactive query we really want the results to return quickly but when you have a very large amount of data it's kind of hard to crunch all these numbers very quickly so we want to keep the queries running fast and for queries run fast we want to have the data sample to be small and relevant so this is again the same plan that I showed you earlier and it shows that you're joining on two data set of workers you're doing a self join this is a pretty contrived example if the join key is very unique like user ID then we're okay because likely you're just to get at most 1000 results back but what if you're joining on something that very like gender and if it just happened that your workers dataset is partitioned such that all the males are on top then the first thousand records might be all of the same gender and in this case you get a cross join and the results would be 1 million and then sure we filter it in the end so that we only get a thousand back but having 1 million intermediate results is really excessive and in this case we're just really sampling a thousand rows whatever is sampling ten thousand rows then we'll have a hundred million results and that's really a lot sometimes it might not even fit in memory and that's gonna cause a lot of performance problems and also CPU overhead so what do we do we would place intermediate limits also on intermediate joints and empirically a hundred thousand rows is kind of the limit it's a pretty good balance between the performance and also the result size you can in the worst case you can probably return in seconds and not minutes which is pretty unacceptable so that's one side of the spectrum the other side of the spectrum which is more common is what if you have no join results because you really you're kind of at the mercy of your sampling algorithm right you have data sitting somewhere and depending on how you sample the data you could be basically getting no results I'd say the most naive case you're only sampling the first say 500 kilobytes of data it might just be the case that the data does not have keys that match in this case we have a very contrived video views' table and a user's table we want to join on user ID and these are all the possible joins but let's say we're only sampling two rows then you see that the first two user IDs are not good enough for us to get a result and getting a result is especially important because we want the user to have a feedback as they're building their pipelines so what do we do the solution we're using to tackle this is something called correlated sampling it's a known technique from the database world where you feed every single joint key into a hash function and given the output of this hash function only keep this role if this hash function result is less than a certain threshold that effectively basically limits reduces the space of possible key values inside your data set and therefore you have a smaller search space and hopefully a larger possibility for getting results practically the way we apply it is we would again hash the result and then keep also scan more data because we're only in the end keeping K rows but that doesn't mean we only have the scan K rows in this case we scan 210 times number of rows asked and we maintain only the number of records as necessary and the eviction kind of policy in this case is we're keeping the one with the smallest hash values so how that works in this case is let's say the hash function is just very simple identity function then for only K rows we're really sampling 20 rows so we're basically sampling everything on the screen and if you look at all the user IDs we're just keeping the smallest ones so in this case we'll have one and three on the left and one and two on the right and from there we're able to get a joint that's a pretty simple case it gets a little more complicated in that that's an extension of this problem sometimes you have many many repeated keys right this is a also a pretty common problem you can't have keys that are just repeated because the records just happened to be next to each other let's say you have a for example a clickstream data you might have the same user ID doing multiple operations continuously so it's something that's very common in the real world and if you're using the naive solution where you're only keeping the smallest hash values the in this case you're only keeping all the tools on the left side and therefore not be able to get any result on the right side the way to tackle this is to use this concept of buckets in short every single key is its own bucket and this bucket we can specify a bucket size for say let's say two if the number of sample rolls you want to keep is now kept is not reached then you can keep on growing the number of entries inside this bucket beyond B but if you're reaching a case where the number of rows you want to keep it's reached and there is a new key coming in you want to start your victim the buckets with the number to beat them with the number of Alex one than be this way you are basically limiting the maximum number of entries you can keep for one bucket and therefore hopefully have more cardinality so in this case let's say B equals to two and again hash function is the same as before as the identity function I won't we're on the sample three rows we take the first three rows and now we have reached K equals to three now for every single new entry here we want to decide whether we want to keep here or not keep it in the first one here the B is already reached and therefore it works just going to skip it second one is the same third one is also the same and then we reach the three three is a new bucket and because we already have a bucket of bucket for for the value equals to two with a value more than we want to keep therefore this should be kept and one entry from the bucket for B for a bucket for two should be evicted so then after sampling and after eviction this is what we end up with and now we have something that both sides has and we can finally get some results back there are many other challenges in dinner prep that I did not touch I was very ambitious when I wrote my abstract and but the reality is I can only fit so much within a 20-minute talk but if you are interested in any of these what these bullets could mean I'm really happy to stay around and talk to you about it in addition we'll have more members from prison actually in the audience today so we can have a talk after that's the end of it thank you if you have any questions I can take them now Wow I explained everything oh okay [Music] so for dinner prep there are two types of work clothes you want to run when so the question is how does spark come to play here right if it's really a small sample why do we want to use spark the you have two things here one is interactive part of it where users want to see results as they go on but ultimately what the user wants is to get resultant overall data and why spark is useful here is you can have the same plan for both the interactive part and the batch part the only difference is for the interactive part you would add the additional operators into the plan to do sampling and to do limits and that's very powerful because you're using the same engine for both workloads and it's because it can all right thank you very much [Applause]