sfspark.org: John Leach, Spark in a Hybrid OLTP/OLAP RDBMS

sfspark.org: John Leach, Spark in a Hybrid OLTP/OLAP RDBMS

Recording: sfspark.org: John Leach, Spark in a Hybrid OLTP/OLAP RDBMS

thank you for Tommy welcome to splice machine all right let's make sure I've got this sorted all right cool all right so people always ask me all right splice machine how did this thing come into being and I'm one of the rare people most people talking like a big day to meet up and they talk about their success with big data I'm not like that i actually had a lot of failures about five or six years ago I really couldn't understand how a file system Hadoop could actually solve the problems that businesses were trying to solve it was a file system and they were viewing it conceptually as a database I have tons of data I want to throw it in there and organize it run analytics run applications against it and all I could say to those people were well it's a file system I can't I can't I can't there's no concurrency models there's no understanding of how to interact with data you can't run a marketing for example application against this data so I really struggled with the dupe and that multiple times and from my perspective I came up with the belief that yes there is a distributed computing movement going on and the commoditization of data and that cost is critical but you still need the capabilities of a relational database there that core data management is absolutely critical so when I talk about splice machine we're really about enabling applications by using our database whether its existing packaged applications or applications you're writing to use our database to run whether it's life sciences applications most of our initial use cases are in digital marketing because they're early adopters so people running you know eunuch those type of campaign management systems that people have at scale so that's really what we are we're a relational database now this talk is about spark and I'm going to kind of go through what we tried to do in some of our challenges and why we turn to apache spark if there's any questions during this please raise a hand or just just yell so the challenges our customers and I think generally the ecosystem has is you have pretty complex oltp type systems that are doing a lot of different etl all during the day and they're wanting to do that more and more frequently and when you get this data you generally put it in some sort of operational data store and then you spin off you know data warehouses things where people are running analytical queries or you spend this off and you say well I'm going to run spark or SAS or someone like that against it this is becoming more and more expensive because each of these are separate environments that you're paying for and each of the environments are closed off and that's one of the things I'll talk about what we've done with splice machine in spark being able to open it up to other tools and and and actually spark to run against splice machine so this is the challenge in the space you have your transactional systems ever more expensive operational data stores those need to be spent off into analytics and this is really no longer an adequate approach people are wanting to take in transactional data in real time and the wanting to run analytics against it and as this community is focused on a lot of people in some sense building smart type applications with machine learning so what you're seeing is as the data gets larger and larger nin a quicker and quicker pace this vision of being able to take the data and start moving it all over the place probably isn't realistic so this is in you know kind of why is this what we're doing now it's splice we originally said we're going to solve the rdb mess problem and with our first release of the product that was our focus so everything from constraints foreign Keys primary keys all those core data management issues we tackled but we fell in this problem and the problem is very simple you're wanting to do transactional processing or real-time processing of small events and you're wanting to run an ax type processes which are inherently i call them destructive but there are a lot of computations a lot of memory usage lot of resource management challenges and what happens is and what has happened with traditional databases is this completely bogs it down so if you're trying to run analytics and some sort of real-time 5 10 millisecond delay sort of application against it your transactional processing completely stops behind all of your large analytical analytical processes that are running and to be honest we fell in that at splice machine after we did our first release we found that well you could run a transactional system on it and that would work well and you can run analytics on it but when we tried to do them together that's where we really struggled and our idea was okay you can just say no we're just going to be a transactional system or no we'll just focus on analytics but a splice we realize that applications have components of both they have both transactional requirements or real-time type requirements and they also need to be able to support analytics and I know I've seen use cases with spark where people are trying to access data stores where data is changing frequently so we focused on was workload isolation it's splice machine simply put you have HBase for your transactional processing which it's well equipped for its well equipped for single row lookups things that are going to go against block caches and things like that it's very much optimized for random reads and then if you look at spark spark is a great dataflow engine for analytical processing and that's the focus of that so what we really tried to do is instead of saying we want a single engine architecture we pulled this out and said no we're going to do a dual engine architecture at splice machine this is I wanted to set the stage of like why we hit and some of the challenges we had and then I'll go into some of the things that we did that I think are innovative and interesting for the community at large whether you're using splice or anything else yes determination to aspartate aggregation and reading it's a great question so one of the key things for me yes the question is do you allow data to be basically changed through spark or is it only done through HBase and what we've done is whether you're on the transactional side where you do single inserts and index updates and things of that nature or if you do it through the spark side with big batch in large movements both things will write into HBase and neither are second-class citizens so they still do foreign key constraints check constraints triggers everything those triggers can run actually in spark when you're inserting data like a large insert statement I'll show some examples of that so we wanted both to have the same characteristics so when spark is reading data it reads it consistently it does snapshot isolation so it actually gets a consistent view of the data so both can do that yes we don't use Phoenix and when we access HBase I've got to slide on that if you don't mind I'll talk about that I think that's one of the innovative things that we've done so I'd love to drill down into that and what's the goal of this the goal of this is as OLAP load increases your transactional processing stays the same so if you have people doing random reads or something we'll someone's doing analytical you want that to stay constant and this is kind of what our focus is sequel scale out and speed 3 3 s's that's really what we're doing and our special sauce is that we're bringing transactions to the Hadoop environment that's enough about spliced I'm gonna tell you about what we did and then I will get to your question on how we access HBase so we took I think three very successful open source projects and put them together we started with Apache Derby which was a sequel 99 compliant database was java-based gave us ODBC JDBC compliance then we built on top of Apache HBase and Hadoop has auto sharding high availability it has a proven scalability one hundreds of petabytes and then what we realized that we had to do was moved to apache spark for analytical engine before that we had kind of wrote our own analytical engine and it was a very simplistic analytical engine intermediate results would be written and replicated wherein spark you know you have lineage so you can recompute those without having to write multiple copies we didn't have all the sophistication that spark at as a dataflow engine that's why I was appealing to us to embed it in our analytical processing I don't know how familiar I wanted to do one quick slide on HBase I don't know how familiar everybody is with HBase H basis in LSM tree architecture so immutable files are flushed and written the data stored in sorted order there's a men's store and a right ahead log for durability and this is how your this is a kind of a classic you know cassandra is also an LSM tree but it allows you to have really high right throughput but there's a caveat and that's what i'll talk about of house park salt that caveat for us i want to show you this this is how we just talked to spark about spark to our customers and this should be pretty straightforward we talked about how data sets are broken in perdition's and how how that's working so enough about of this piece by what i really want to talk about so how is spark really making us work well this is one of the things and you're talking about HBase we had challenges with HBase specifically compaction so if you search online Google HBase compaction you'll see 20,000 not very nice message boards communications people saying oh my gosh to slow the system down I don't know what happened when you have large data movements in HBase where you try to read large amounts of data all kinds of problems if you're still trying to do transactional workloads their spark is helping us to do index creation statistics collection imports monitoring imports reporting to us when there's errors and imports so we use accumulators in real time as we're loading data they will actually show what record was invalid so someone can look in the UI and see that we use it as an admin UI and I'll show you that we've customized the spark you I to be able to show query processing which is extremely powerful for our users to actually see how data flows we've added we've done it for analytical price processing for pipelining the data lineage is the big thing over the data flow engine we had before so we don't have to replicate intermediate results which is really really expensive and lastly we're starting to incorporate the machine learning libraries directly into the our DBMS for smart applications okay so let's get to the technology piece of this LSM tree this is HBase Cassandra any of those sorts of things and one of the things I noticed about spark in the usage of spark very much to machine learning machine learning libraries but one of the challenges the Hadoop ecosystem and other key value store or transactional store databases one of the challenges they have is you usually have a whole set of things that are transactional quick range scans things like that but then you also have these kind of background processes like compaction or statistics gathering or things like that and the database teams were working so fast they bundled it all together and that gets back to that problem of trying to do analytical and transactions in the same system so this was what HBase looks like and these spikes or when compaction is going on so it's literally a heart attack to the transactional system and this compaction runs in memory and HBase it actually is loading massive files into the same jvm that you're using to serve block you know doing really quick row lookups block cash lookups things are supposed to be happening really quickly your system starts to have you know garbage collection issues your system then you start having IO issues because you're reading from the same HDFS input stream which blocks so you start to have all these really significant issues and what we thought is okay this is a unique opportunity for spark and one we haven't seen but I think it's extremely valuable what if every time we want to run compaction and HBase instead of running in that JVM and beating the heck out of HBase what if it ran in spark it's Java code you can pretty easily wrap that in a function and call it a spark we can customize the UI there's some nice tooling for that to where we can display hey this compaction is running and it will by the way when you see it running you'll know what table or index is being compacted how many files how big the compaction is going on so we went from a thing that was keeping HBase from being successful in my mind and really the first thing people say is a kind of caution against HBase but by using spark and being using a little different we all of a sudden now have something where you can monitor the compaction is going on you can resource manage those against queries that are going on and other things that are going on in the database and you have they said complete monitoring and isolation of this activity and this your obvious we're talking about spliced but way this was Cassandra similar concept it's an LSM tree it's going to have to do compaction 'he's do you want to do that in heap in your jvm so this is a use case that i think is very different than your normal spark machine learning type use case now to your question on HBase the big problem with HBase is that most people when I say they go through the front door to get the data for analytics what does that mean they open ask and HBase and they start reading from it that's how I've integrates with it generally in other products and the knock against HBase is it's slow because what's happening is the HBase process is doing mostly a short-circuit read from the Linux file system into memory in the Java process and then you've got a remote scan from one Java process to another and you're dumping a ton of data across that's an extremely inefficient way to transfer large amounts of data and that's that's a huge problem and that's where the perception of age faces hey it's slow at splice we said okay let's let's reinvention that because that's not I'm going to be close to performing enough for us so we said look why don't we go directly to the files because they're immutable right and then we know that if somebody's actively writing to it there'll be small Delta's in the men's store of updates that are in flight so what if we just go directly against the files by a spark so it's doing a short circuit read from the Linux file system native read get in the data in and the small Delta's we're going to go ahead and read from a remote scan so instead of if we're reading 100 gigs of data instead of reading 100 gigs out of a java process to another job of process by a remote scan why don't we read 98 are ninety nine point five gigs from the file system directly into memory and then we'll get that little small Delta and merge that together and that's where you get the performance that you need to actually be in my mind kind of coherent as far as the speed you need to get the data back so it avoids the slower HBase API it doesn't put load on HBase because that would be bad as well so when you're reading through HBase you're not really even if you have an analytical process reading from it you're still causing a lot of you know you're killing your block cash you're you know creating objects there that becomes problematic and the other thing is and what's neat about spark is it still follows the MapReduce paradigm the input output formats and if you go underneath there there's still an output committer and that's really important to us so everything we do is transactional so when we read we have a transaction that we're reading as of so if data is in flight will ignore it and changes will will actually put together in a snapshot isolation system and that's absolutely critical for us because spark was smart enough to use that structure but the key thing for us is you don't you're not sacrificing consistency which is the strength of HBase you're not you're not sacrificing that so if you're an application the fact is running in spark you're still going to see a consistent view of the data if you just wrote it you're going to get the data that you wrote back even in an analytical query does that make sense to everybody and I think that's actually an important thing as we look at even other storage layers the question I always have is okay there's a front door for real time but do you have a way to go directly to the file system in a coherent way to do analytical processing and that's something I don't think we think about when we choose real time systems but I think it's absolutely absolutely critical and something will have to kind of crock second thing that we did is whether you're in the transactional system or the analytical system we still do generate bytecode for you know your projections things where you're comparing like you're like type clauses things of that where you actually have bytecode where we're doing those comparisons so we're still using an optimizer and the optimizer does the plan and the coach in the netcode when we have it is shared between flights HBase and spark because i can ship that bytecode over there and then that's what's actually used to do the evaluations like the projections and the filtering and thing of that nature go through our architecture here so for everybody HBase so it has its region servers we're embedded in there we're doing snapshot isolation indexes constraint checking all of those sorts of things and then co-resident on those those servers you have multiple executives so in splice machine we use a spark on yarn so as we need resources it'll dynamically generate executives for us and that's been really really powerful for for what we're doing so as the analytical system needs more and more resources we use yarn to get that understanding of what capacities the cluster has and then I'll go into how even inside the analytical system we have the resource management and kind of how we set up the resource management for spark for our database okay so how does this thing work sequel comes over the wire use the ODBC JDBC you optimize the query so it's using statistics and statistic caches so it's going to figure out well what it's cardinality of the data what are those estimates it's going to push predicates it's going to unroll nested sub queries so if you're like marketing app with a lot of nested sub-queries is going to enroll those make them joins it's going to generate the optimal byte code so it's actually a generate byte code that represents the plan and if that cost and the size of the data is small enough it's on HBase it's going to execute the OTP query directly from the bytecode doesn't serialize anything over uses the block cash in bloom filters to optimize data access this is really for real time and quick access and it's going to return the results okay but what if it's an analytical query what if it's too large and I don't want to affect HBase it's going to generate a spark execution plan so all of our sequel goes into it can become it's a result set basically that can we it explains itself and fundamentally every one of our result sets is an RDD so anytime you have a sequel statement I'm sorry what we do we use our DD right now for where we're at yeah now we customize all the serialization between it and what I mean by that is we're doing our own row serialization of the data because we have data coming back and bites and we don't want to convert it to objects so we have a whole host of optimizations about basically trying to keep things and bites as long as possible and if they need to be transferred we ever convert if you're just doing moving data around you don't want to take the bytes and turn it into a long and then convert it back to a bite just want to keep your bites and ship them over the wire so it's yes no we we came up with a we call it a data set abstraction and what we thought of there is look it's a data set it's big and there's instructions I'm going to supply to it but the key thing for us is whether it's real time or its analytical and those are right now we have a spark and we have the side that runs in HBase we have another data set abstraction for that regardless of whoever's doing that we have those instruction sets to do that so for us we don't use the spark data set we use this concept of a data set and we wrap everything around it so that we could use other data flow engines if we say hey look we want to use data frames we would then create a data set for data frames so we can have different implementations that's really our focus is to wrap around really here the relational algebra instructions that our database is going to be doing and then different data flow engines can handle that those instructions that's our key abstraction that makes sense cool huh yes no so it goes directly to HDFS so when I say hey read the customer table it'll go and it has an input format that points to that and what's special about our import format is instead of going to hbase to do a remote scan it'll say okay here are the files for that region and then I'm going to do Delta's with the men's store for that region for and then it has its own splitting very it uses the indexing of the H files to generate cut points in blocks so that we have you know we don't have regions of different sizes being computed at different timings but it goes directly against the file system and then we have concurrency guarantees while we're running that so if somebody does a compaction the files are fundamentally changing we do compare and swap operations there to make sure that we don't get caught reading files that disappear on us right or a split we make sure that we don't get caught in those sorts of situations yes exactly so remember it's an LSM tree so all of our data is written into HBase and what you have is you have no it's immutable so you're just writing files and when we write data we guarantee transactional integrity so multiple people are writing the same record there's different isolation levels they'll throw basically different exceptions based on your writing of data at the same time so the writing goes through HBase because HBase is the way that you're actually figuring out who's writing what transaction ality is happening and then when you read you're reading directly from the filesystem the HBase files and doing a delta with the men's store hopeful that makes sense so really what we're trying to do is keep HBase from destroying the block cash when we read a lot of data and we're trying to take that that that load off of HBase which it just doesn't do well and that's kind of been proven out on this google search will kind of show you that that's not the way you want to access each base no we wrote our we wrote our own so it actually opens in HBase region so each base has utilities to open regions that just go directly the files and then we use just a mem store only scan from the region and we merge those two together and we have concurrency guarantees on the server side when we make that connection you have to you have to like I said don't get caught don't get caught with the files moving on you make sure that you know your scans don't time out all these sorts of things that you have to kind of the nuts and the nitty-gritty stuff of it we have not open sourced that know a lot of it though to be fair you need a transactional system to make it in my mind coherent because you're really starting to say if I see data in flight you just ignore it because it hasn't been committed depending on your isolation level so it's really I think the concept of this is really driven by you have to have some sort of transactional nature to make it in my mind coherent yeah it does go through HBase for the rights correct and we have our own right pipeline that we've built that's specific for HBase where we have non blocking rights so when we stream data from the thread we break out parallel right pipelines and we even piggyback rights on to region servers not just regions so we're starting to tackle the mini node problem where you're doing a lot of our pc's we start to piggyback cut down the number of our pcs and we'll batch in rows of like 20,000 so instead of doing a right ahead log for every entry we do 20,000 and put that in a single right ahead log and HBase is much more amenable to that so we can do you know a hundred thousand writes per second per node where HBase generally can't get that but what we're doing is we're batching that up and we're making it less disruptive for HBase if that makes sense so we submit the spark plan with the bytecode and when that happens the fair scheduling piece of spark kicks in but most importantly we go through yarn so as a group we may a commitment that we would use yarn so even our developers we use little yarn consoles and that's how we run spark because we really feel like you have to do it in some sort of resource management framework and you can't step all over what else is going on in a Hadoop cluster or environment so that's something that was just a decision we made we think that's there's got it and there's things that we don't like about yarn of course and all those different things but in general that is our strategy for dealing with the classic I've got a lot of things running a lot of people competing for the same resources yep yeah it's a great question so we have statistics so that means we know cardinality size of the data what number of distinct values and all values those sorts of things so based on that plan you have an approximation of the number of rows if there are no statistics which is classic in databases right nobody run statistics it's kind of I don't know what's going on each base is special because the region servers keep track of the store files the size of those store files and you can make a reasonable approximation that I'm going to look at 100 megs of data do I want to look at 100 megs of data in HBase probably probably not the right way to go about it but do I want look at that in spark that seems probably reasonable so we do those approximations based on the plan but it will look at the statistics if they're not there it'll use a heuristics model but it's pretty accurate because it's using the store files and remember the store files have an index so it knows the blocks and the row keys you can get a pretty good approximation at least the amount of data you're going to be scanning so you know whether this is going to be a five rows or you know a million rows it's relatively advanced I think in that I'm sorry--but know it's in HBase as well so we have a dictionary tables so when splice starts up we create a shout out of 25 30 tables and they're managed transactionally just like the other tables so when you create statistics they're written in that table and I think the hardest oblem we've had is the classic distributed computing problem of you think about metadata and statistics it's extremely hard when you have a multi-node environment that stuff has to be on cash basically on every one of those nodes and then the challenge happens is when you update it you got to do some sort of two-phase commit sort of protocol to eject the cache understand that your ongoing ddl transaction going on and be able to clean that out in a transactional reliable way that's been given that's that's a fit for us that's something that we we've worked on over and over to get it right that was no we don't not in total because we need something that's extremely fast like I get I've got to use you know in the case of like a prepared statement you know I've already got the bytecode done I'm back immediately so it's not it's not a low latency or it's not like a high latency type environments really low latency yeah no problem I will be actually unusually analytics ever look at the specific column shirt won't be able to get that kind of performance from royalty because you actually have to scan all the rows which are actually going to be okay so no this is a good question because I spent a lot of time thinking through this so you are right so most OLTP systems are row based however those systems do have some analytical operations even creating an index in those systems in essence is an analytical type operation you're reading a lot of data in so I think there's a need there just for the transactional piece of the system I think in operational data store use cases where you're doing a lot of the business logic of organizations where you're doing triggers where you're doing workflows data management that's still really critical I agree if you remember the diagram I had where you had the transactional systems go to the ODS and then you get these optimized columnar stores for read-only a read-only store I still think that's absolutely critical and I agree with you I think it's in to me it's not even so much more columnar it's really about being cpu efficient and columnar is cpu efficient so i totally agree with you on that i think the system if we look at a system maybe two or three years from now what i'm showing you you'll have what's going on in the on the HBase side and then those regions will have dual representations that's what google has they have a right optimized store right next to a read optimized store so you can imagine as we run this camp compaction I could easily generate and in your case a park a file right right next to it for the analytical to be even faster that's not a reach you know conceptually for anybody but I do agree with you but the key thing is from my perspective applications modify and change data and our gold splices about computing the uncle and always laugh because I'm computer's name in a word but our focus is well think about that the challenge the data that people don't access and they can't analyze and can't do machine learning on is the data that's rapidly changing and that's the data that's in flux that's the data is actually interesting if you're in a hospital or anything else environment that's the data that that is absolutely critical so that's kind of our passion versus the read-only optimized store I think there's a lot of people solving that problem for read-only analytics what's up yeah so if you look at what we're doing it is kind of a lambda architecture i'm reading the store files and doing that piece the challenge with the lambda architecture is it's really for doing really quick analytics in real time and it works in some it's very much like a heavy in my mind a heavy developer approach to getting those analytic numbers more and more accurate right you have the the background process that's getting it close and then you merge in you know your waiting's and everything to come with that ultimate aggregation I think that's going to become more and more important but I think with spark and you start to use caching and other concepts you can start to build things like materialized views and things that are more lamb desk I think that that can become a reality we're not there at splice machine yet just to be honest but I see those things in the future for a lot of people so the big thing for us is we do generate that our DD from the H files in them store and HBase when we wanted to scan the data and we execute query and return the results sounds like you know crazy bytecode everything but you actually looked at the code it's very similar to all the other things it's map funk you know you have your functions your flat maps all the different functions or relational algebra you're applying so all of our operations relational operations have a set of instructions that if you looked at them you'd say hey I recognize that yeah it might be a little different how we do a projection under the covers dude recognizing say I get I get that so that's that's what we're doing under the covers the key thing for us is now these two processes are completely isolated so they can have different nice scores CPU prioritisation these things are are isolated from each other and that's the trick of a transactional system get all the analytical processing off of there what we use heavily is resource management and spark because it's nice to say you have old TP and olap but that's not actually how it works right because inside OLAP you have you know import statements how should they be prioritized and how should the scheduling algorithm pick those tasks to run what about compaction right what about a query what about an administrative process like running statistics how should you wait statistics running versus an import which ones more important and we allow our customers to change this but by doing this all of a sudden you have a system where you can start impacting the behavior of it and you're doing it inside yarn where you're not overwhelming the cluster you can go where there's an existing infrastructure and created a process that functions this is extremely I've been really impressed with with this piece of the of the software it does but the key thing we did is we said we didn't run and kind of act like there weren't problems with HBase and I think I'm saying HBase could be Cassandra whatever your whatever your key value store is the question is can you make your key value store behave with spark and do it in a way where you're not basically creating more problems than it's worth right yeah it's a big it's a big challenge but it's something we focused on so this allows people to change their resource pools which we thought was extremely compelling and the thing that was really exciting for me is I don't know how many people have used this spark you I but you can customize that in an amazing ways we actually put in all of our sequel into the UI so when you run a query you see your sequel there you see your tasks that are executed so each sequel statement is basically a job and all the instructions for that sequel statement even sub queries procedures all these bizarre things are going on is it this basically a spark job gives us monitoring eventing this is a t PCH query with unrolling right so it starts to get pretty interesting and you start to see well these things are executing in parallel and you can see where your dependencies are what is running how many tasks and then all the monitoring of spark becomes a whole lot more interesting now you have a database where you see what tasks are running what their splits are how much data is going through all of that data is coming in and it's coming in in real time as its executing before in splice machine we had a real problem we thought we had really cool technology said oh it's running see how fast it runs great but no one can actually visualize the internals of the database what's actually going on has this finished well how much data did this read how many megabytes how much did is shuffle much to move across the network I can look maybe in Cloudera manager or ambari or some place and see data going across the network but I could never trace it back to the actual query what the heck's going on and I thought this was one of them so you can actually see like i said the visualization stades of merge sort joins table scans projections going on how many rows are going through that and then when you actually go into a stage in the stage we implemented accumulators so you can see the number of rows as they complete the go through for imports we have accumulators that show you the rows that failed so when you're importing data and you're starting to see a few rejections you can actually go into you I and be like oh there's a comma there that's misplaced or that's got to double quotes that's a bad record you can actually see that in real time which i think is amazing through through this UI as he might customize their spark you I at all no I mean I was amazed by what you you can literally put whatever you like and that we put the entire basically explained petplan information in there so that you can actually see how many rows we expected you can see that there's a difference between we expected 100,000 rows and you returned 200,000 no you just yeah there's like job groups but you can directly there was a blog post on this and I can even post some of the code afterwards if people are interested but you can actually set all of these fields directly on it it's a little hook that someone built in the sparks equal guys actually built this hook in because they were trying to do it I think a similar concept and you could hook it directly in so you can put whatever information you would really like there that were may be used to make the decision but it's extremely powerful because then the process that you're doing is actually in your language which I thought was was really critical for us the cool thing relational databases in general don't like to read files from the file system because that's a very expensive memory problem now we have virtual tables so we can use spark that can already read text files and csv files from the file system and we use our system to dynamically type those into different this is of our char this is you know you define that and now all of a sudden I have a relational database that can do joins with files in the file system can do it with the conjoined with a web service you start to get this nice open architecture that you can implement your own virtual table interfaces so this is something was really important to us so you can start querying data directly from s3 you import data you can just run the query against it we wouldn't have been able to do that without spark this this this would have been not possible for us because we wouldn't have felt comfortable saying in our HBase memory space we're just gonna start loading random files in there that would be too dangerous for the performance of HBase yeah from a slice machine you can connect to oracle and yeah yeah because it's just a JDBC we have a JDBC vti so you can connect via JDBC and actually execute a sequel query and Oracle and get the result set and the result set will be dynamically typed based on your query by our parser so even cooler when you start joining the data back you execute your sequel but we're already converting it though that's a var char and Oracle's of our chart here and we're doing joins based on typing yes yes that's what that I think the power of this VT for us was having more of an open architecture getting data quickly from from different systems yes yes correct yeah and then the vti has an implementation of cost so you can implement that however you want so for us for example unlike flat files we do an approximation technique based on what columns you gave us and how big the data is and that's kind of our technique so that we know hey that right side is actually a billion rows and that left sides 20 maybe do a broadcast joined to the right side that's how we go about doing that in the last part and part two is kind of cool to me is since our result sets are in essence instructions for spark you can slap together a quick stored procedure pass in whatever sequel statement you want and the results of that statement it will run multivariate statistics on it and you're not running the sequel in the database and then the analytical engines running on it afterwards it's just a set of instructions and it's just thrown over to spark and it it's doing the sequel it's just a big long processing chain and it's doing the statistical analysis and returning it via sequel to you so this is an example of all of a sudden here's a procedure that does some pretty advanced multivariate statistics that is pretty simplistic to put out there and you can imagine linear regression you can imagine different classification algorithms it's going to be extremely powerful for people to be able to do that all right that's all I had I just wanted to share with you guys kind of some of the things that we did and just to kind of go through those again key value store in your storage never underestimate sparks ability to make those systems more efficient that was one thing we learned second if you're going against a real time type system is changing I would always suggest to think is there a way to go directly against the files versus going through a remote call that's really expensive and really spend your time trying to come up with an input format that's very efficient for doing that I would heavily suggest modify in the UI that's so powerful to see whatever it is you're doing in the language that you're doing it I think that's one of the more more powerful things that we've learned from spark I think the UI is tremendous to be able to see kind of what's going on and with that I'll open it up to questions or concerns or anything yeah yes so it's it's a normal data dictionary so any jdbc or odbc client it can say get me the schemas give me the catalogs give me the tables primary keys it follows all of that model yeah and that's how we that's how you can do you know ERG diagrams all the things that databases do for you that's kind of one of the things so you can plug in any sequel tool to it and it will already show you like the foreign keys and and draw that stuff for you yes hope so right and so can you imagine you know blah gillian flynn's at the spark and abstract you know they memory computing engine I think Monty probably kill me if I start trying to throw it on us no I I mean you see that I think the for me it's you know our people comfortable with it you know we have partners that are running the Hadoop clusters you know you're hortonworks Cloudera map ours and you want to make sure it's supported you want to make sure that no one's going completely off road to where you know you get into a bad situation so for me it's all about is it supported yes are people excited about flink I don't know I mean we've kind of kept track of link at splice I'll be open about that it's on our radar but right now it's its spark and we're really just I mean to be honest we're just cranking and making sure we can do all the our dbms capabilities and execute on this I mean that it's a this is a this is a i think a very audacious goal of what we're doing and i'm excited where we're at with this 20 product and I want to just stay focused on our customers before going to flank or any any other thing oh yeah we're we're capable and we've even started to abstract away the data piece as well so we could use other storage mechanisms but you know we're pretty focused I mean scarily yep yes it's differ different person we run on the three different platforms and then we have different versions to spark on their releases so it's usually like somebody say like I'm running it on Cloudera 541 and we have a build of Cloudera 541 which has a version of spark that's appropriate for 541 so we really try to make sure that because in essence were on and somebody else's architecture in the sense that somebody may have Cloudera on 50 nodes or map are on 50 nodes we have to be able to come into that environment behave run in their resource management framework and do all of that so we're very tightly integrate with those releases yes good question so we target anything that's over probably about half a second so our delay is about 200 milliseconds for the instructions to get around so anything over that then it becomes coherent to do that give you a kind of ballpark that's kind of where we're you know so you think you have a two hour millisecond delay on the you know just getting things scheduled and spark and everything going so that's kind of where we're at right now that keeps getting less and less but that's where we're at right now with 20 yes the support multi tendency the splice machine database so you can run a lot of that is I'm going to be honest the harder part is trying to figure out how you do the the yarn thing and then try to figure out like how are you going to do the resource pools two different places right now we have that where it works for kind of you know we've got it pretty structured about you can change the priorities and stuff we're going to build an interface to that so that based on your connection URL and what you supply then people can write an interface that would say okay the connection URL was this company like say Europe SAS provider those will go to a different resource pool and spark thus you know you can keep track of the resources and weigh them against each other but we haven't got that far yet as far as yeah I mean you can see how we're going to do it but you know we're really like I said pretty maniacally focused on just making sure that every feature in relational database if you have 15 level levels deep of triggers those all work transactionally correct I mean we're really focused on that because that's how people get confidence in your database yes correct what sounds short coming in our database so let's say let's just talk about this directly you have ten columns right and how do you handle that you have you have your ten columns there and if you want to add a column you have an 11th column and we already know how to handle this right we all know protocol buffers and what is the approach of like a protocol buffer and Avro you just increment at the end right and if you see data that you can't understand you ignore it and we're very similar so when we have our data and you want to add more data that's fine and you just keep adding columns and if you drop a column that's just a logical hey position nine is no longer valid in that data set and you just ignore that when you're reading and reassembling the row so you can have a completely flexible schema and you don't have to like an altar table in our system on our 20 product that's like a few millisecond operational you're just updating its dictionary you don't have to physically go in and change the data if you're saying look I'm going to drop a column we don't really drop a column you tell somebody who's reading the data ignore that column in essence from the dictionary likewise when you add a column all you're doing is saying you know it's null now but it may be populated in the future as you populate it you know you populate it you do your lsm merge and put that data back together yes it's still typed the system is still fundamentally typed yes so yeah no I don't discreet you may be stored in Jason's a great approach up there's obviously challenges right you can't apply constraints it's a really poor storage format it's really I oh and efficient so there are other challenges there of being able to do it so I think if the way those two against each other yeah but that's fair yes sir yes so when you do it you just supply the sequel and will return the instructions and then it's lazily evaluated so well the fact that all of our result sets we can return the bytecode to spark so if you're in a scholar client you can return that byte code and then they can actually execute it directly there because there's no cotton the hard part for me conceptually when i was doing this is there's no splice or spark our all of our instructions are in you know we're sending to spark they are completely contained and can can execute themselves because they have all the data they need to be able to do that so even if you're inside spark and your query in it all you have to say is this is the sequel I want return that byte code instructions those byte code instructions can regenerate an RDD which is a result set and that's what i was showing in the procedure is that basically the instructions is this result set so before you iterate on it that can just be converted directly in a pretty poor job of that but it can be converted right to an RDD and then we provide utilities to take those typed like bar char's and everything and most of the machine learning is vectors so we already have conversions from like you know int double stuff like that two vectors so that you can perform you know in essence machine learning so the key thing is our instructions for that sequel are basically an RDD that's similar to spark sequel they do a similar thing where their sequel is actually then instructions so it's not like I picked this out of out of thin air but it's extremely powerful because you're no longer executing your relational database then doing analytics it's just a set of instructions that you you execute yes yes jdbc odbc any of those types of tools yep yeah yeah so HBase is using protocol buffers under the covers we use it for message passing so if messages change or anything has to be persisted you can do that without you know I was having serialization breakage and enrolling upgrades and all that stuff so yes we use protocol buffers just to be open we don't use Avro at least I don't I'm sure there's some product that's bundled in and some lot of jars somewhere it's using it but we don't use it yeah yes so we don't we don't hope so that's what is one thing is when you think about a transactional system a lot of times people think that you keep it all in memory and then you just write it on commit right but we're an optimistic system so we're actually writing it in and then when we commit we then resolve and understand whether that that data is committed or not so if you're going to write a billion records in let's say ten regions in HBase you'll have sub transactions that are writing into each of those regions in HBase that can retry and do small pockets and though right in then the data is going in if you're trying to read you'll ignore it because you know that data is in flight because when you try to resolve it to its transactions it's not committed I mean and I'm at an isolation level that ignores that and then when you say commit and our transactional table is spread across the cluster in HBase because HBase has real level atomicity so once it is committed is committed in one place it's completely atomic so what's that commit happens well everybody is it can resolve to a point in time so that's our kind of what we focus on is making sure that we have those in essence acid properties how do you skate that out yeah so let's say you have a million people that write one row each they're going to get a transactional timestamp and increment so you grab their transactional timestamp and they're going to write it which doesn't really have any contention except right ahead log with its other peers you're going to write that data and then when you commit you're going to write it again to hbase so are all of our transactions are actually persisted in HBase the state of them that ask your question what okay bear any other questions thanks I really appreciate this