data.bythebay.io: Greg Rahn, Taming JSON with SQL: From Raw to Results
Recording: data.bythebay.io: Greg Rahn, Taming JSON with SQL: From Raw to Results
so I should mention my name is Greg I'm going to talk today about using a bunch of tools to interact with Jason via the sequel language and just briefly about me and kind of this actually is a dual-purpose slide it's both in about me as well as the foundation for all the examples in the talk so kind of a little bit of humor embedded in it in a text slide to kind of ease the it anyway so Greg Ron's my name my twitter handle is at Greg Ron fourth yes no always so I've spent the last three companies I've worked at on database and data processing systems I spent eight years at Oracle working and development they're doing performance in benchmarking on the Oracle database I spent a year and a half working at Cloudera on the Impala sequel engine and then I'm working currently at a company called snowflake computing where we've built an analytical date abilities purposely built for the cloud that supports kind of both the relational flat data as well as Jason or semi structured data so one of the questions that kind of keeps coming up is is what is was it with Jason these days it definitely has game popular and why is it that way well if you ask me there's a number of different reasons right you know to use mark and driessen's play on software is eating world you could you could probably say javascript is eating the world and on a related note for those of you who have ever used stack overflow and are familiar with Jeff Atwood number of years back he coined kind of a fictitious law he called that Woods law which stated that any application that could be written in JavaScript will eventually be written in JavaScript so it's more and more applications are getting written in JavaScript and you know just kind of a techie geek thing is like you know the games that you used to play on Windows 95 now can run in JavaScript in a browser on a modern operating system so clearly the power of javascript is is quite a lot I think the other thing though that really is driving this is that how we are in or where we're getting our data from has changed a lot right we used to work a lot on flat files and FTP and probably some organizations still are doing that but the source of data has really kind of started to come from REST API is right and and rest api is often produce their payload in a Jason document or even streaming new lined eliminate Jason right for example like the Twitter fire hose if any of you've ever played with this right this is streaming new line delimited Jason and additionally we're putting more and more devices on the internet and those devices have to be able to transmit data to each other back and forth so Internet of Things or as some people say internet of anything you know and mobile devices as well are transmitting data to and from systems in a JSON format and for those of you who do application logging right use something like log stash or elasticsearch you know we've kind of evolved from the days of using facilities like syslog which is essentially print line right to something that's more structured and self-defining formats right so it's much easier to output data that is readable both by humans and computers and able to be identified by computers and and by that I mean that is self-defined rather than applying our ninja skills with awk and grep and regular expressions to try and pull out pieces of information out of one single log line right and kind of jokingly has anyone actually worked with XML which is another self-defining data type or data format right not exactly fun and you know it's it's it's very convoluted complicated and it's really hard to understand from the human interface perspective right you can't open up an XML document very well and look at it and read it like it's text or code right it's embedded in tags and sometimes you know nested and trees and very complicated of sorts in addition self-describing data formats are are super popular because it makes data very portable right if you go to any of the websites that are kind of sharing data these days and kind of an open data format type perspective oftentimes you'll find that more and more of these data sets are coming in JSON format and one of the nice things is that is it makes it really easy to bring in that data into any system that you want to work on or wrangle that data right you don't have to worry about what if the fields are in the same order across output files right you know or the header is there or not right these are the things that you bump into when you're using things like CSV right so kind of going with a theme of the rise of self-defined Damon data I lifted this from the MongoDB website that i found kind of interesting and just to kind of read it here you know in summary is that craigslist you know and obviously being from the bay everyone probably knows craigslist had stated that they couldn't add columns to one of the largest tables in my sequel because it would take them a month to actually have that operation complete so their motivation was to move to something like MongoDB where they claim that they have this flexible schema that they can use right now perception is reality obviously here the one thing I'll point out with this is that this is not really a problem with the relational model per se which is one of the arguments sometimes that people use between like flat files or or scalar types and nested or or complex structure data representation because databases like Oracle and db2 have been able to add a column to a table in sub second time for over a decade right unfortunately my sequel doesn't actually do it this way and what they do is they create a new table and they move all the rows from the old table to the new table right which is obviously going to be slow and scale with the amount of data they have in the table so that said you know I like the fact that and the engineers are thinking about easiest way to stay flexible but the point kind of this slide is that it's not necessarily the tool that causes the problem vs the data model or the data type itself meeting Jason or CSP okay so data systems have been drifting toward this jason or self-defining data format for a while and you know as I was kind of mentioning earlier I think it relaxes the restrictions on developers and applications that create data right you can now add and remove attributes without kind of breaking things that are necessarily ingesting them at least not by a hard break what I mean right so think of it like if you expect there to be a certain ordering to the data and it's not there you know your ingest is going to break right I think another thing that's kind of come up with regards to Jason data is that your kind of moving data around and you don't necessarily have a single centralized system that's managing this data anymore right if you look at kind of the Hadoop environment % all right you know who do means kind of a lot of things these days but you could have one system or one tool that produces data a second place actually stores the data and a third tool that accesses that same data right and none of those might know anything about each other right point being there's not necessarily like a database system like Oracle or my sequel a central metadata repository that contains all the information about all the columns and table names etc right so that's one of the big benefits of something like Jason or self-defining data however you know this kind of model and this interaction doesn't really come without any trade-offs right I guess I kind of use this this term a scheme attacks all right kind of what i mean by that is how many of you guys are saving for retirement have a 401 k plan right yeah most probably everybody right and you're probably familiar that there's two types of 401 k plans there's the regular traditional and then there's the raw now the main difference between these two is one of them you pay the tax when you put the money in and you take it out forever textfree no matter what happens the other one you you pray the tax when you take the money out and it's free to put it in right I think the same scenario or paradigm exists with schema management right if you have rigid upfront checking you know you pay that tax initially but you are pretty much guaranteed that your data will be pristine for all systems that access it right on the other hand you can allow self-defining data to go in and if you change the attribute name or something the application using it might not find it but it probably just gets a null back then right which may break your data processing but probably doesn't break the tool and interaction with the data itself right so net-net i would say we're kind of moving toward a produce or consume first model and organize and analyze later right so that's what I've been seeing out there okay so now we're kind of going to get into the meat of the presentation about sequel and Jason right and today there's quite a number of different options that are out there and for this talk we're going to go into six different tools that you can use to operate on data and talk with them and use this example through each of the the tools so i would characterize this talk as more of a wide and shallow talk versus a narrow and deep talk in that sense and we're really kind of focusing on the point of view from the data wrangler in terms of agility and time from that you get the data set to the time you can actually get your working results and get answers to the questions that you're seeking from your data okay so the first one we're going to go into is hive so i'm sure everyone's probably pretty familiar with hive these days you know came out of facebook was a sequel to MapReduce translation layer and when you're working with Jason and sequel typically you have your files in s3 or HDFS some distributed file system right and typically what I've seen is that most people will define a table with a single column and that column will be of type string so essentially you have new lined eliminate Jason in a file then you're reading that back into a single column and then you're operating on it with some fun that's right and high offers what they call hive ql which is kind of a subset of the ansi standard of sequel and they provide a few functions that i will highlight here in terms of working with jason right and the first is the get json object function as well as the latter the lateral view syntax using Jason tuple now alternatively you can actually use a 30 with hive but this is kind of a little bit more of a programmatic approach it basically is a shim between the data and hive itself so you kind of programmatically project and transform the input into the columns that you know the table wants to consume also highlighting that there is an explode function to work with repeated fields but it only takes an input as a array or map and when you're working with text you have a textual representation of an array or map we actually don't have in a rare map itself so it doesn't kind of work in the way one might hope for this kind of case so here we'll just kind of jump into our example on hive so we're going to create an external table that has a single column of string and then you can kind of see the syntax here we're going to use the lateral view Jason tuple and then we're going to specify the attribute names that we want to pull out right and as you can see from the result here we essentially have kind of a two-line result if you will we have the first line is the three scalar fields that we pulled out first name last name and Twitter handle and then the fourth column here is actually a textual representation of the array right now as I mentioned earlier the explode function doesn't work on this text but ideally it would or we could hand roll all you have to do so and some people over at clout have done just so they have actually a little bit of a library to kind of interact with Jason so obviously something that might be interesting for those of you who are working with high oven Jason text okay so the next system we're going to talk about is Apache drill and apache drill can operate on Jason files directly without a predefined schema so actually you can specify in the from clause of your sequel statement a path to a directory or file right which is kind of cool so you can essentially say like select star from this file right now the downside of this is that because there is no metadata in the data dictionary if you will you can't actually describe the table itself because the table is not really table it's actually just the file right however drill offers what's known as schema inference so when it reads the data from the file it can kind of determine what are the field names and types and you can actually kind of see that with just the limit klase you know like select star from file limit n and for that you'll see the columns projected out and kind of like the typical format you would think from a query engine right a flat relational structure in that sense however the kind of gotcha with this is because you're using a limit and you don't want to spool gazillions of rows back to your terminal screen only attributes that appear in those n rows will actually show up in your result so there may be you know cuz its self defining data if the schema is not consistent across every record there may be attributes that are hidden from your result set so just something to kind of be aware of but you know for people who are just getting into a data set it works kind of well drill uses ansi sequel as its base as well as what i will refer to commonly in for those of you work with javascript or or this type of data is that notation right and they also provide a handful of function is to operate on the json text as well so they have a flattened function which allows you to repeat a the left side if you will for each value in the right side so think of it like the example we're working for I had one first last name and Twitter handle but I had an array with three companies I worked right so we'll show you on the next slide what that looks like but allows you to repeat that kind of parent record for each child record so you get out of a tabular output from that there's also kv Jen which lets you work with kind of nee unnamed array value pairs right so you can have pulled these out in two columns without actually explicitly knowing the column name kinda lets you iterate over them and then there's two array functions that let you return how many elements are in array and if they given value is actually present in an array or not which is kind of use you know just as a boolean type okay so here's our drill example and I use a CTE or a common table expression or with claws instead of an inline view because I just as a personal preference but you can do either here and so you'll see that i'm using the flattened function on the company worked companies worked field right so this was the repeated field and so we're selecting directly from a text file here containing Jason as you can see in the front claws and then the subsequent sequel statement we actually kind of reach into that flattened array and we're going to pull out the named values right you'll see here that I have teed companies work duck company and that's kind of the path if you will t being the table alias here in this case now one thing just to kind of point out with drill here is you'll notice for the first three columns I didn't specify the table alias because there's only one table right but for the array columns that I'm pulling out I had to specify the table alias even though there's only one because drill doesn't know how to determine between table name column name and dot notation path of the array element that I'm pulling out right so you always have to preface these with the table name just kind of something be aware so this is kind of on the bottom here the example that will really be working toward is this three row five column tabular display right okay so next on the list is spark so spark can also operate on Jason files directly without any predefined schema you can have data in HDFS s3 or even on your local file system spark also supports scheme inference from reading the metadata inside the files and this is kind of provided by the sequel context API in addition sequel context provides you a couple of really nice functions one of them print schema so actually after you register and create a data frame you can actually then use print schema to show you what it looks like right and it prints out kind of a tree format and I'll show you on whether the next slides what that looks like additionally if you want to use sparks equal then you can use the register temp table function which essentially converts your data frame into that allows you to access it via the spark sequel API or you can just directly use the create temporary table syntax that spark offers and again similar to drill spark also uses the dot notation for paths so it was kind of attribute name dotsub attribute name etc they also provide an explode function that allows you to unroll arrays so we'll use that in our example as well okay so here's kind of a code on screen example from the spark sequel so first we import our sequel functions and then we define a data frame tell it it's a JSON file and then give a path to it in this case I'm just reading this from my local hard drive right and then we call data frame dot print schema and you can see here that it lists kind of entry format tells you that which fields are scalar types which ones are repeated and and what the repeated elements in that is right so in this case you can see for companies works companies worked it tells you it's array each element in the array is a struct and eat straw contains two attributes company name and number of years and then from there we can actually call our register temp table command and now we can actually build a sequel statement and just kind of call it with sequel so here I just called this sequel statement using very similar syntax as the drill example again using a common table expression but you'll notice here that we don't actually need to preface the table name for the array columns we can just actually call them exact directly so we we use explode in the common table expression to repeat again the left side for each value of the array and then we can just select them directly what so again we have our five column three row example in scalar format okay so next on the list is bigquery so bigquery is Google's query engine for the cloud it supports the sequel like language although it's not the ANSI sequel standard they allow you to import csv jason and avril files into the tables there but they do require you to have a schema on one of two events either on the table creation or when you're loading the data so that allows you to kind of evolve schemas if you will if if the data input changes and you can specify a different schema when you're doing that and that specification is done via actually a file written in Jason and I'll have an example of that on one of the next slides additionally they provide a handful of Jason functions to operate so you can pull out Jason extract or jason skyler or flatten from there in addition there's a within clause to kind of use with arrays and aggregation okay so our next example here is using the bigquery tools so first we just kind of make a working space and then we load our new line delimited file in there and here you can see that sample that Greg is the table name Greg that Jason is the input file and Greg schema Jason is the schema file which I'll have on the next slide and then we can simply just select the values directly and you'll notice that for the repeated fields here I'm actually not using the flattened function right and this is kind of a nice feature of Google bigquery if you're only doing one level flattening so to speak it does it implicitly right you don't actually have to use the flat function or specify however if you have more than one level then you can use it in the table clause to unroll it right so here's kind of the example of the schema basically here we have first name last name and Twitter as the scalar types and then the company's worked as a repeated type field okay so additionally bigquery lets you show the schema as well so there's a show command that you can give it your table your table name and then it will give you the tree like display as well okay so next on the list we're kind of moving toward the relational world redshift so redshift is Amazon's data warehouse solution it supports Jason in the sense that you can place it in or as a var char field in a table right they use anti sequel based on a kind of a parasail which is their point in time source code that they bought years back and you can load these in as either scalar values or just Jason text via their copy command they provide a handful of functions allow you to operate on that Jason as well you can use the jason rae length we extract array element text and the extract path text functions however just kind of one copy out here and and this is literally right from the documentation of redshift they state such we recommend using Jason sparingly Jason is not a good choice for storing large datasets because by storing data in a disparate single column Jason does not leverage the Amazon redshift columnstore architecture right so this is kind of like while you can you know asterisk I would say and I think if you just read around on blogs in the internet you can kind of feel see people talking about this right okay so a quick example here from redshift we basically make a table here in this case we have just the four fields because the company's work field here is actually going to contain the textual representation of the array the Jason text if you will they offer a copy command which you can just copy from your file in s3 give it your credentials give it the Jason keyword and then in this case you'll see I have this Jason path Jason this is a way to tell the copy command what attribute names in what order you want to project out from the input file right so you may actually have Jason that you don't care about all the tributes you just want to pull out some and in this case you have to you can use this file in it the lower part of the screen is actually the contents of that file right so here you can see that I'm pulling out these path names explicitly redshift also has an auto option but one caveat that I ran into is that postgres uses lowercase identifier by default so your column names like when you say show tables or select our lower case in the dictionary if you will if you're Jason contains mixed case the auto won't work because it has to match identical right so then you have to create a table that has quoted identifiers right and that means you always have case sensitive always requiring quotes table names right kind of a little bit of a challenge okay so here we're going to do a little bit of sequel gymnastics right we're going to use this little helper table that i have here called seq which has just three integer values in it in this case because it's only three because i have three elements in my array but this could be a million elements and work for all the Rays and when I use a band join and a band join here as you can see in my where clause actually have a less than sign so the reason that I need to do this is that there isn't a function to give me a given element from the array but I don't have a way to say it erate from 1 to N for all values of that Ray like flattened gave us in some of the previous examples so in the lower part here you can kind of see what i get when i use that right so i get the textual representation of the Jason but I get my three rows right so now I'm going to combine the Jason text Rock path text with the Jason extract array element text and use the same kind of sequel gymnastics here and you can see then that I can actually pull out the name values from that array I'm using this now just as a caveat some of you who know postgres or redshift there is a generate series function this doesn't work in this case because it's a leader node only function and technically it falls under the unsupported partially supported postgres functions of redshift so just common FYI okay so next I quickly going to jump into snowflake which is where I've been spending my time the last few years so snowflake is a cloud-based analytical data warehouse solution as a service if you are database as a service and it has a variant sequel data type that has native support for Jason or Admiral type complex types right and you can load this into this column and the interesting thing that snowflake does that most other systems do not is that when you load this data in it actually interrogate the json text itself or the object representation and shreds the common attributes into their own columns much in the same sense that a column store stores scalar values in its own column right so what this allows snowflake to do is approach the performance of a flat relational table with that of a structured table or semi structured table if you will right so you can keep the flexibility of the self-defined object but get the performance of kind of like the relational side so snowflake is the ANSI sequel compliant database uses extensions again the dot notation syntax and here you can see we use the variant column and then use a semicolon and then the path to the object that we want to pull out there's a number of different functions you can list kind of the common ones here we have some validation functions the flattened table function and then some array functions as well okay so here's a quick example of the in snowflake so we create our table we give it a single column a variant type we run a copy command very similar to redshift we tell it that the file format is Jason and then you can see in our select statement here we use the column name that we're projecting out which is c1 and then we give it the Jason path and in this case I'm actually using postgres casting syntax which is the double semicolon and string so we're actually casting us because internally it's an object even though it's a string it's still an object and then you'll also see from the the from clause I have this flattened function and I'm passing into it the company's worked array right so one of the peculiar things that I found with some of the other systems is with explode is that you have three input records or we have one input record and you select with explode and you get three output records but you really still only have three so to me that's kind of something from a from clause or a table function versus something in a regular function okay so quickly rack being up here mastering sequel on Jason in my opinion I think self-describing data is very prevalent and probably the new norm and you might actually also say Jason is the new CSV right even with self-describing data however schema definitions may be required on some systems and you know in essence are necessarily a bad thing different systems offer different levels of functionality they have different functions the standard for Jason in sequel is still evolving so all of these vendors and all these projects have different syntax but the standards kind of yet to be decided on and so be interesting to see kind of what happens in the future they're also kind of something to be mindful of is that analytics on Jason or any text format is never efficient right so from a data wrangler perspective if you're doing something once probably you know no need to convert that into something more performing like you know parque or etc but you know if you're going to iterate over a thousand times and give it out to all your users probably you something you would want to you likewise storing Jason as a string isn't super efficient either and obviously our redshift documentation kind of highlights that as well so with that like to say thanks and if there's any questions be happy to take some sure so heavily nested is probably not such a problem in itself unless there is a extremely large amount of attributes right so with snowflake there's the entity of kind of like sub column because variant contains like the JSON payload and so the sub column is to like Jason as regular comm is to that and as long as you have you know not tens of thousands of columns it probably works pretty well if you have like super big tex blobs you know like approaching you know 20 megabytes just of text obviously you know this does become challenging right any other questions performance point of view and storage you mentioned something the numbers no I mean for this talker is more focused on the the data wrangler perspective not necessarily an agility and functionality versus you know and then performance type things you know and it's a fair question but also sometimes the most performing format might take you longer n to n if you actually insert it into your pipeline whereas you know if you just need to do something right now you know I got a new day data said I need to look at it it might be quicker even just to kind of suffer through it so to speak so good question though anything else yeah okay so schema inference is kind of a little trick to essentially read in the column or the attribute name and try to determine what the tribute type is like is it a string is it afloat is it a structure right and so in order to do that you have to read the payload right so in order to get it all you have to read it all right so that if you actually have a very large file you know you can take spark on your laptop and you know give yourself a hundred megabyte file give yourself a one megabyte file and you'll see it takes you know that much longer to actually go through and get it right so again the the trade-off here is agility versus performance right you can if you get it you can bring it in you know obviously the first thing you could do is create table park a table I select from Jason but now you've already have now you have a sunken investment in that right so again back to the same comment and made without this question is like sometimes even though it's not the most performing way it might be the quickest way and to end anything else sure if Scala is obviously more expressive in the sense that you can programmatically insert stuff also you can explicitly tell you can define your plan so to speak where sequel is I ask you a question you figure out the best way to give me the answer right whereas programmatically you're saying here's how I want you to go find the answer right it's kind of a little bit like asking directions from you know Apple or Google Maps right you know like no use this way I know it's faster or just tell me what you think's the fastest one anything else all right thanks for everybody for attending