Spark DataFrames for Data Munging

scala.bythebay.io: (Susan) Xinh Huynh, Spark DataFrames for Data Munging

Recording: scala.bythebay.io: (Susan) Xinh Huynh, Spark DataFrames for Data Munging

you um so I guess I'll talk a lot more about data munging and you guys already know spark and how many of you have used smart data frames before Oh a lot of you already have okay um well hopefully you'll be able to pick up something new from this talk alright so here's the outline of what I'm going to be talking about I'll start with motivation and then I'll go over some of the highlights of the data frame API I'll do the live coding demo and finally talk a little bit about life beyond data munging all right so here's a scenario that I don't know if this is how ever happened to you let's say your boss comes to you one day and says I've got 100 gigabytes of data someone else collected and it's log data and I'd like you to analyze it and if you look at the data and it kind of looks like this it you know that it's JSON format but you don't really know exactly what's in it so what do you do first question you might ask is what exactly did I mean by analyze you could interpret that a bunch of ways and what I really want to talk about today is data munging and people have different names for this some people call this data cleaning or etl and I came up with my own acronym p ETS and I briefly thought about calling it p EST but this pts is better so what that stands for is parse explore transform and summarize so parsing is you want to be able to parse different input formats then you might want to explore what's in the data set because even if you know what is in a high level you might not know what the ranges are values etc etc you probably want to transform at the data in some way to make it more useful and finally you probably if you're starting with a very big data set you'll want to summarize it in some way so that can be consumed by the end user and finally you might want to automate all of this using a data pipeline okay so so I hope this is clear what I'm talking about when I say data munging and so now you understand what the job is so you want to pick the best tool for this job ok so if let's say if you're a data scientist then the first thing that might come up to come to your mind is data frames that's great for working with data data scientists have used this very successfully in our and in pandas library and Python on the other hand remember it was like 100 gigabytes of data so on the other hand you might want to find a more scalable tool such as one of the sequel query engines like hive or Impala and so in my opinion one of the best tools for this job is spark data frames and it kind of brings the best of both worlds you have the concept of data frames and you're also combining it with the scalability of some of these big data query engines okay so just a little bit about why spark I guess you guys have probably already heard a lot about spark it sounds like you're almost all of your spark users so maybe I don't need to convince you to use spark some of the highlights of why would you use spark is one it's open source it's very scalable to large clusters and machines and it's very fast for answering ad-hoc queries which is something that comes up a lot when you're doing data munging exploration more specifically why use a spark data frame so in spark the original data structure was the RDD which you may all be familiar with and data frame is really a more high level API and this goes back to you remember the acronym ki ETS all of those tasks that come about in data munging spark makes it really easy so it makes it easy to parce both structured and semi-structured data sets their formats such as JSON and for exploration it has this concept of the data frame which I'll talk more about later and then for transforming and summarizing you have a couple of options so you can either do kind of a declarative type of queries with sequel or you can do more of the procedural type of processing that you might be more familiar with when you're just programming in Scala and finally there are some convenient utilities for working with math string and date time functions which also come up a lot when you're doing data munging and last but not least is that you can do all of this purely in Scala ok ok so now I'll go through each of these in a little bit more detail and tie it into the data frame API so first you want to parse the data so let's say that you had that JSON data set that you started with in the beginning 100 gigabytes of JSON data let's start with spark so that is a variable of type sparks session and this is your entry point into working with sparks equal how many of you have used spark sessions before ok great so it's familiar to some of you I think its new in spark 20 and then once you have spark you can do spar tree JSON passing in the data file path as can return you a data frame object and I store it in a Val DF and if you're not familiar with data frames then you can just think of it as a table very big table with many many rows and also many columns in that those rows alright so those parsing now let's move on to exploring so if you are not sure what's in the data set the first thing you might want to do is print out the schema of the data frame and before I go into this let me just add that that one line of reading in the data that also automatically can infer the JSON schema for you so that you don't have to pass that in ahead of time it's inferring it based on what's in the data set okay so calling print schema on your data frame is going to list all the columns in a data frame and so you can see there is a coordinates column of type struct it's a nested data structure there is a created at column that's type string there's a language column etc etc you can see all the column names and their data types so now that you know the schema you might want to zoom in on a particular subset of the data set because obviously if it's a big data set you can't look at every single row so that's easy with data frames you can filter by rows or columns so the first line there is taking the data frame and filter by only the rows for which the column coordinates is not know and then you can filter you can select only the two columns that you're interested in coordinates and created at and then show the results and what you can see is coordinates is what you would expect it's a nested it's a struct that has both latitude and longitude and then created at you can see is the type of time stamp okay so in this way you can look at what's in your data and there's a whole bunch of other data frame operations that you can call to explore the data further so like count describe to summarize what's in the each column creating new columns there's a lot more okay but that's enough about exploring now let's talk about transform and summarize so as I mentioned before there are two ways that you can do this one way is more of declarative queries using sequel like API so I can start with my data frame and I can say select the language column where the column language is not know I can group by the language do a count over each group and then order by count descending so it's kind of like a sequel query but it's using the Scala API and you can see the results here the top language is English followed by Spanish Portuguese etc and so that's kind of the declarative way to do it but you can also do procedural processing which you're more familiar with in the scholar collections API for instance so we can take this language count resulting data frame and we can call a map on it and then passing in a map function where it will iterate calling this function on each row of the data frame there's also flat map filter and some of these other functions that you're familiar with with Scala collections so two different ways that you can mix and match whatever you feel is more comfortable for you ok finally I just want to mention the math string and daytime functions that's very easy with a data frame so for instance let's say that I had this timestamp column and I wanted to create a new column based on this column that only takes the first three characters and get the day of the week so I would do that with the with column method I create a new column called day of week and I populate it with taking the original created at column and calling the substr convenience function and these are what the results look like and there are a ton of these convenience functions available in spark there's math function sine cosine exponential string functions and then working with date and time stamps and it's just really convenient like whenever you're manipulating data you often come across the same problem of having to parse time stamps for instance and beside the inconvenient it's also very efficient internally spark does some code generation to make it very fast to call this on every single row okay well that was a very brief high-level overview of data frames are there any questions so far so yes Phyllis wide there was a substring function that when the column is now it produces now right if the data I say that when created and is now the week is yes right so is this behavior built into substrate fashion yes I think you're yeah you're asking why did it just not crash or have a no pointer exception and yet it's built in its kind of the default behavior is that your kind of exploring the data and so it's not being very careful about type safety I mean it's it's allowing you to not worry too much about types is it built into this possum particularly to everyone oh I think it's built into all the functions yes okay sure I think he said that that's it's behaving the same as sequel would so that you're not confused let me go on with the demo and that I can answer more questions later okay so let me try to do this is this big enough ah I might be able to just hold it yeah okay can everyone see this or should I make it bigger okay all right someone in the back and see it so great okay so since we're at Twitter headquarters I thought that we could analyze some tweets so what I have here is 8 gigabytes of tweet data and it's has spark running on my laptop but you can imagine that this could also run on a cluster of machines but right now it's just running on my laptop and this is a Zeppelin notebook back by spark so I'm just going to demo you know the different steps of data munging so the first thing you might want to do is read in the data so this is that read dot JSON method that i showed earlier so reason the data into a data frame i actually did this earlier because it takes about 47 seconds to load okay and then as I said you can explore the data set by printing the schema so I'll do that and you can see that these tweets are actually very complicated objects they have I don't know probably hundreds of columns in this schema so it's great that spark is able to automatically infer the JSON schema for you you wouldn't want to input all this by yourself alright and there's about two and a half million rows in this particular data set so now you might want to zoom in on a particular column and see what's in there so let's look at the language and text columns so that's just using the Select method on the data frame and so here's a typical tweet the language is English on the first one and it's saying have I mentioned that I make instant coffee with cream and butter it's like a typical tweet right it's not very important but they just felt like tweeting it okay so let's get a little bit more interesting let's look for tweets that mention the term iphone so to do that it's very easy you select again just these two columns and then filter for text that contains the term iphone let's see what we get let's see what we get okay so it doesn't actually do anything here until I call an action so now I show what's in the results and you can see the first one is oh yeah iphone 7 by the way I'm not sure what that means and well maybe you're just exploring this data set and you notice hey there's some tweets in Japanese I wonder what is the language breakdown of the tweets so we can do that with a sequel query so I here i have an example of a sequel type of query where i'm doing i'm taking the results of the iphone query and i want to group by language do account and then order by count descending and show the results so here we go okay so it looks like the top language is English as you might expect the second one is in fact Japanese so maybe there are a lot of Japanese iphone tweeters out there who knows all right and then the final thing is it's very convenient when you're using notebooks to plot the results so I'm just going to plot here and you can see the results as a bar plot English Japanese etc ok any questions about this demo no all right so let me just finish up here in the slides ok so my wax section is talking about what what about life after data munging so in the whole data science pipeline or in your analytics pipeline you don't just do data munging there's usually some other data tasks that you'll have like machine learning or putting your data pipeline in production or working with instead of batch data sets are streaming data set to get real-time analytics so this is all very well supported with data frames in spark 20 and for instance for machine learning in spark 20 we have there's a new data frame based machine learning API so you can just take the results of the ETL and use the same data frames for putting the data pipelines in production there's a new data structure called data sets and that's very similar to data frames except that it has very good type safety which you will want in production and the final thing is moving to streaming data working with streaming data there's a new API called structured streaming which again the fundamental data structure there is data frames so once you start working with data frames you can use it for all these other different tasks okay you have a question so much semi-structured data or a small tiny percentage of the cases can cause so much of the complaint so save presented this huge 100 gigabytes by your boss yeah and 101 percent of the records have complexities razon oh and the rest of it is actually fairly straightforward yeah I want to get to that is there did you find in some ways that you were able to using that Otto schema featured you know you see where I'm getting at but maybe you could strip away the complex cases and get back to the simpler discover what the simpler is somehow yeah I think ok ok the question is sometimes the when you have semi-structured data like Jason it's only a few rows have all of the columns populated most rose maybe have only 10 columns populated so how do you deal with that and I guess one way to deal with that is to bypass the automatic inference and just you know use your own schema so don't make you no don't ask bark to do it because it's not very customized you know put if you're only interested in those ten columns then parse it yourself and pass in the schema yourself to tell it to just use those columns so that's one way to attack that problem alright I think we're out of time so thank you very much for listening and if you have more questions we come up with a couple [Applause] you