SBTB FinagleCon 2015: Justin Coffey, Presenting Vizatra

Recording: SBTB FinagleCon 2015: Justin Coffey, Presenting Vizatra

hi everybody so I'm Justin call i'm going to give you guys a little talk about a visualization tool we've been working on i'm the dev lead for the analytics infrastructure team at créteil so a quick little overview of who creature is I think nobody knows who we are so we tend to think that we're the biggest tech company nobody's ever heard of basically we do banner ads we do performance based advertising I won't give you the whole the whole spiel our scale is a little bit interesting though we've got we serve about 3 billion ads a day we process about 50 billion display requests every day and we generate about twenty five terabytes of machine data so my team is the analytics infrastructure team so we own the analytics stack it's an internal facing stack it's for internal customers and that's about the size of it so we have our twenty five terabytes ingested a day we run about a thousand jobs per day on it we support about a hundred analysts the engineers numbers a little bit low we're about 300 now our final users data consumers you know sales those those sorts of people is about a thousand of them and we have about 5 petabytes under management this is the scale of our rooftop deck we're located in Paris so you get some nice nice nice sites that's the the deck is much bigger than that actually it's a little bit difficult to photograph and then because we are recruiting the scale of our vacation so okay so now the serious stuff so just a quick overview of our analytic stack it looks like this basically today so it's kind of classic I think I don't think there's any big surprise here on the bottom we have lots of Hadoop we have cascading scalding and hive doing data transformations and Alexa said no etl I don't know if we do etl or not but we transform a lot of data and then we expose that in hive and vertica so hive is of course you know the old standby that's where r 5 petabytes it's and then we have Vertica it that hosts about 50 terabytes of data and we expose that typically through a tableau or a cube to our you know standard data consumers of course analysts you know attack it like all over the place they can do it all they want so you know they have at it today I'm going to talk to you about Zara which is this new thing we've written it's why are we right why am I talking about visitor visitor is there because at one point the VP of analytics came to me and said hey we've got a new so he said to me he said hey we've got a new new little problem to deal with we have this big customer portal that has all our operational data on it all our customers take a look at it and as it so happens all of our internal sales staff all of our internal technical support staff all of our publishing staff everybody basically uses this tools or internal reference point and it's crumbling so we have two options when we can try to make this thing work except the business says that it no longer responds to sort of the market needs its decrepit it's missing ten million different features so they're going to build something new so either you get to maintain this thing for internal users or you can provide some sort of new solution so of course you know going back to a little slide I showed you we already had tableau and we said hey we'll just build in tableau tableaus great anybody here know what tableau is or use it hands yeah who thinks tableau is a good tool for that right so that's what we found out relatively quickly so we decided to take a look at building our own tool so of course there are other dashboarding tools out there so why did we just go directly to building our own basically because if you look at what other dashboard tools are doing they're doing exactly what tableaus doing they're building queries and you know i'll explain doing a little bit why I think this statement is true that basically all query builders are naive so sort of a case in point let's take a look at a very simple query building expression in tableau you know it's just maybe a little bit difficult to see but basically there's a two fact tables being joined on time ID later on I'll add a constraint so that's not a cross join and then it it basically builds this nice sequel 101 statement with an inner join and you know nobody's going to complain about this about the sequel statement it's totally totally fine so here start your stopwatch is this is how long this query takes to run so by now everybody's left right so that's not very good so you know this was running as a vertical cluster and I did my darndest to make it make it behave properly and I couldn't really get anywhere with projections all kinds of other fancy vertica stuff you can do and so ultimately I had some domain-specific knowledge about that query and I basically rewrote it as two sub selects working it's very optimized tables and all this stuff does a streaming group by its very fast and so but it's kind of a hideous query right like your DBA would tell you to take a flying leap if you if you saw this query so but let's take a look at how fast this career in start your stop watches Wow one second are we happy so let me explain to you why in this specific case that was that was so much more efficient so there's of course big crate plans here on either side but but the the fact of the matter is is that the join that was being performed by the naive query so so-called knife grade had the materialized 170 million rows whereas my not so naive query only had to materialize 8,000 rules so there's your run time difference and so so of course you know i go back to sort of my central conceit and that's that no query builder could have really figured that out now the database maybe you should have been able to figure that out right but the query builder it has no way of knowing that so again going back to the project we were working on right we're working on a project for operational dashboarding we have to have the dashboard showing up in like you know maximum of a second or two so our clients not only in there I so laser they're demanding that actually the thing is up and running but there's you know some sort of qbs constraint involves as well and so do that right we have to obviously we have to start building optimized views of the underlying data and to better build these views it's a little bit it's a good thing to understand the queries that are going to be coming in you know and then sometimes as we've just seen even knowing what the queries are and even optimizing the schema is insufficient sometimes we have to actually modify those queries to make the database behave properly and then and then there's a second problem and that is that you know you've got a 50 terabyte data warehouse you can't always just change the schema right like that's sometimes complicated to do and then this isn't really germane to the problem at hand but we started thinking about this this problem and then we realized you know we have a lot of analysts we have a hundred or so and there's some sort of breakdown in that group of people who just want to do drag and drop you know analytics and then there's other people who actually like writing queries and working sort of more directly with the data and the database and and I so have I believe that you know the best dsl ever written to extract data from a database has been written it's called SQL right so we like to think that we should just let them write it if if that's what they want also as an aside you know even the worst analyst technically speaking you know they can be trained on on what good SQL is you can give them templates of SQL to use to modify so you know even even you know in these cases we can we can provide them with the SQL tour to run which is better often times and again going back and trying to change the whole database structure okay so fine so I think now you guys all believe me that hand coded SQL is the best thing ever so you know let's let's see how we can now but going back to our use case right it's a it's basically an OLAP work workload right nobody just wants a static representation of data where you've got one query you know a couple of different visualizations on top of it and like hey everybody's happy no people want to click on that chart they want to click on numbers and they want to go look at what's behind it so this is like sort of an OLAP work loop so how are we going to be able to to model that with queries without a query builder right that's why tableau has a query builder so first thing we can do is we can hand code those queries so you know given our you know our little exercise here given four dimensions time advertiser publisher and device how many queries you think we need to write to mimic an cube for right no not really for so and generally speaking there's a lot more dimensions than for right so in this case time you know time decomposes into you know four or five periods that people want to look at in our hierarchy advertisers you know there's a whole hierarchy there there's probably three or four dimensions and publisher the same and device well typically we only we only look at device along one dimension but so in other words this this for could easily be 12 or 15 right so obviously this is not this is not feasible and then I'm not even talking about sort of predicates that we might want to add on to that so so that's no good so we did eventually you know after banging our heads against so we'll have a little bit of a light bulb moment here we're still against query building but we have to do something there has to be some some programmatic effort here so we have our super analysts maybe some of them are ourselves and what if we had them write the optimal lower bound with respect to granularity query and then deconstruct that for the roll-ups right so sort of reverse the problem the idea there is that maybe now we can plan we can know what the worst case query is right this is this lowest granularity career that's attacking the most data that's that's that's reducing it the least amount and then we can infer that all the derivative queries are going to be faster or at least as fast so that's the idea so we tried to formalize this a little bit and something we call visit ql sorry for the marketing ease but it's really at the core of is otra so if we take this very simple query which has no joins know anything is just very simple it's our four dimensions how can we make this guy OLAP compatible so first thing we want to do is we're going to add a predicate in this case is simple equality and then we're going to parameterize a little bit give it some names so we can we can be a little bit explicit about what we're parameterizing and then we're going to start annotating it so this is sort of our first effort here and we've created these this little sequel comment bay annotation scheme where you can see that I'm sorry it's in like light gray here but you can see we're telling the parser what dimensions are what the metrics are and then again the group by clause with corresponding dimensions are and then we've talked and then we've we've annotated the filters and then you see that there's something called a parameter the only difference between a parameter here and in filters is that parameters are required the nice thing about requiring some things is that you can narrow obviously the the slice of data that you're looking at and optimize against those parameters so now let's let's use that now that we have that we can hey let's let's let's queer this thing so now we get a query that comes in for the full set of advertiser dimensions for a given time range so basically we take everything else off that we didn't need we keep our metrics that we could have taken off them if we just want the cost we could just take away metric as well and we've and then we're left with with this this query to this crater run so you know what we're trying to what we think is that because the keurig is less complex is we actually use it in real life nobody ever runs the query that's got the lowest level of granularity right but that's there's too many data points we infer that its runtime will never increase in practice so so that's sort of the court of the thing and then and this is this is our effectively what we support today we have a relatively rich SQL support we're targeting sequel 99 that doesn't really mean anything it just means that we're you know doing things like with clauses and group by roll up and stuff like that we're able to also par sub queries to reduce complexity in sub queries so like going back to my query that i showed you before we could very easily have annotated that and use that that query we're also able to call joins so we can annotate joins and say that you know certain fields are coming from this joint clause and if that Joe if those fields aren't referenced in the in the incoming query would just take that that joint claws-out also d normalizing is a big deal in datawarehousing and OLAP workloads if you can remove all possible joins that's really good if you can reduce what your query into simple numerics you know whole integers and things like that then it gets even really a lot faster you can take specially in column a column in order it ordered databases and so we have our own little hash join support in visa in visit row and then we also do results I'd caching sort of trivial results at catching so I can I'm going to go through this really quickly I feel like I've hopefully justified enough why we did this but here's sort of a way to take a look at this with respect to Vertica which is the database underline this tool and you can see how how we've adapted what visitor is doing so that we can take advantage of sort of the advanced features in Vertica so first of all vertica is a has a really really really advanced analytic SQL dialect so it's got its own dialect career builders can't I mean you could always write a query builder that's adapted to specific dialect but you know then you want to wear another query builder that's adapted to the dialect of another database and another database in another database and that just seems silly to me like you know and so we're you know able to support the range of queries that we're currently throwing against against visit Rome also vertica structures its data via projection so you can order things in a certain way you can distribute the data amongst the nodes in a certain way and this allows you to get very optimal operations like a pipeline grouping and merge joins and things like that the only thing is is that column order becomes very important in being able to to make those operations work and the nice thing about a query builder is that no matter how you've arranged your you know when you're actually running the query the the query processor will maintain order of the column so if you've ordered your base level query if that's all ordered in line with your projection order well then that you're you're pretty much guaranteed to hit those operations and again you know d normalizing everything is great it really it really flies I think most databases are that way you know if you can just take out joins right things go much faster and forget has no curry cash and so we've added our little resultset caching it's a very very trivial on heat Bell are you cash you know to guava whatever we're studying distributed caching but I think as most people know that's a pretty hard problem so with that let me give you a quick overview of how this thing looks so we're using fenêtre to do this the front end is a sort of a full j/s app on the front end it's all angular and pure d3 the backend is there to serve up metadata so the dashboard can bootstrap itself I'll show you that show you guys that in a second and then for the data sets and all that other good stuff that's all handled of course through the fenêtre sort of restful ish endpoints whatever you can sort of see the call stack there it's more or less right some some things to note the JavaScript dependencies are very light and we've basically hand you know written everything ourselves we didn't want to depend on you know all these different modules that you could pull in we're using parsing Combinator's to do the visits ql parsing we use hoe Kon type-safe config because it's nice and fun this whole thing you write a config file you deploy as a self executable jar you can run it locally we have metrics that we send the graphite so that you can kind of get an idea of what's going on and as an aside this is the first crece o app that's making it out to mezzos so we're also kind of excited about that so why did we use fenêtre and not something else actually we did use something else at the beginning when you play dammit sorry that'll come back so we did use something else we use play at the beginning and I kind of personally got frustrated with play it's a little bit heavy-handed and I come I'm like sort of the guy who hates our ends who hates frameworks who's you know rewritten all that stuff himself ten in different times because you know I didn't like the complexity of the framework and said you know I could do it better and actually I started looking at finish was like hey this doesn't really do much it does exactly what I want and no more and so that's really why we're using fenêtre afterwards we sort of found out you know wow there's this cool finagle finagle ecosystem out there and infinito has really been the gateway drug at creech EO into the Twitter stack so we have some colleagues over I have some colleagues over here who are trying to get finagle out at larger scale at Crete EO and I think we're gonna have some success with that so anyways so so that's why fenêtre and now if I have the time i'm going to do a very very very fast demo and so prepare for the demo effect and I'm sorry that's my only meme so whatever alright so let's okay so I have this little dashboard built it's all running locally this is running against a little mysql database that I found online that it's layman's basketball or baseball stats so here we've got our little navigation of sort of our aggregated data you can see over here we have groupings that we can select we have the various metrics that I've exposed and there's some filtering capabilities over here so I've already done some stuff let's actually change the player let's I kind of like the handedness dimension stuff so let's pull that and so we can see that this is look this is looking at these you know right-handed left-handed switch hitter and unknown stats since sort of the beginning of baseball time I'm going to go ahead and get us into the modern era hair let's take a look at like say from the 60s refresh that and then I need to do two hands here hang on two minutes oh and I could also just select you know a few metrics I can there's different ways of sort of selection routines but I'm liking to go into that actually let's so now we have that data sort of exploded over time and you can you know you can kind of play around with it we can take little take certain groups out take them back in since I'm a little short on time I had a sort of a whole little analytic thing here prepared for you guys but I'm going to skip it what you can see is that one of the interesting design decisions that we made here too is that I just want to touch on is that we call this sort of our data navigational matrix I'm not sure if we're innovating here for stupid but what I always found interesting was that when you know when you're designing a dashboard a lot of times you know you have dropdowns with your groups or your dimensional values and you want to give them some weight right and a typical thing you'll do is like a count 0 and just give a frequency count of those of those groups and associate that to the drop-down well here what we said was it he screw screw that let's uh let's give the full weight of all the metrics that we're going to look at and let's aggregate those guys and then you can sort of you know order them and play with them and and and sort of navigationally explore your data based on the relevance of the metrics that you're interested in and then once you have that then you can go ahead and explode them into sort of the time series analysis so I'm over time so I'm going to leave it there so yeah questions it's not really related to language but I'm curious scallop are surfers how good are the the guy who wrote it is standing right over there maybe he can reply i was at the learning curves a little bit tough and debugging is not the easiest thing in the world but so if you do when i add a little yeah the question was how are the error messages and how is it to work with that using also a community where it becomes illegal like requires tough to handle all the cases will basically be handled some cases of a party nervous not all of them and we try to improve it by you like hiding test a sheriff Andy Taylor that stable we shall give you try to separate our errors out bugs in the pasta with never provided like the users for example someone when you write a query if I we can find examples it's very very thin say I can be defined errors from the user from we have generally switch cases with with errors which we miss if we have for example morissette to the current oh to get only everything they are to put it by the all the time and also I can just add one more thing on that the the parsha that we wrote doesn't create a full AST of this equal right we abandoned that idea so we we parse it just to the point that we need to understand sort of the scope that we're in so we're like in a select scope or we're in a sub-query scope or which then has a select scope or join scope but we didn't really try to get a full understanding of the SQL we tried that and basically we failed and then reverted to this more simplistic parsing model more either something really most Pacific to what we've done basically when the arrow we handle takes it to the one coming from our notation from our annotations so if the allegations are wrong then the error is easy to get it is related to the sequel sequel query itself then it's basically little nuts but for innovation it's basically ecs for example if you don't know if you do not break dimension you write something else then we know that you cannot pass it and cannot do anything with it it's related to see very recently for example generator but where you can happen thank you very much