Guide to Grafana 101: Getting Started with (awesome) Visualizations

this is part of our guide to grief on our 101 series this is actually the first webinar in a series of three technical sessions and we're going to be doing so you're actually the first to know that you know this is not the last one out there some quick introductions about me before we delve into the technical content so my name is Akhtar I'm from South Africa I'm super interested in how to use technology to empower people that's why I really enjoy my job as a developer advocate timescale and I also learn new things on a daily basis in this in this profession and so I write about it on Twitter and on my website Ofcom so if you want to follow me you can do so in those places and in today's session we're gonna do four things but before I outline them I just want to remind everyone that this is an interactive session so please feel free to ask questions using the Q&A feature in zoom so there's two different buttons one for Q&A once a chat you can just chat in the chat if you'd like to but if you want to ask a question please use a Q&A feature and I'll taking your team of dashboarding and Griffin our experts are on hand to answer them at the end of today's session and just also a quick reminder that you'll receive a recording of today's session afterwards along with links to code the slide deck that I'm using and other helpful resources so don't worry if you can't take notes or you happen to miss something you can always go back in and check it out later okay so in today's session we're gonna do four things the first one is chat a little bit about why use crow fauna and this wire-like refiner for those who are new to grow fauna in timescale secondly I'll do some orientation and setup where I'll show you how to connect to a data source and just quickly orient everyone I realize and everyone is coming out with different levels experiences and some familiarity so on orient everyone in part two and then get straight to the best part which is creating the visuals so in the third part of today's session we're going to create six visualizations to get that you can use and I'll be doing that in three common use cases namely DevOps IOT and public data and that's get you getting you excited for you know what the bulk of today's session is going to be about and then in the part 4 I'm going to leave you with some resources for how you can apply what we've kind of what are we going to be talking about your own work and answer your questions so please please do use a Q&A feature when you have a question ok so let's get it straight into part one which is why use graph honor so I realized as I mentioned some folks may be familiar with graph on already but for those who want this is just a quick refresher so we're all on the same page crow fauna is a open source of visualization tool it's really great for monitoring but it's also useful as a general-purpose visualization tool as I'm going to show you with some of the other use cases and data sets that are demoed today you'll probably recognize it from it's really distinctive you why it's that's this nice dark team going on and you know you can see that in the screenshot and from my experience there's a couple of really great reasons to use the katana just from talking to people using graph on a timescale time scale users and in my own experience building dashboards for various projects that I'm doing here the first one is that it's open source so it's cheaper than having a proprietary tool or at least as a cheaper alternative the second one is that it's really great because it has a lot of support for many different types of visualizations I'm going to show you in today's session how we can make a variety of different things to show to to sort of demonstrate and convey the information that you want and you're not restricted to just having a graph or a pie chart or something like that and it also has support for alerting which is something I'm gonna cover in a in a future webinar the last thing is that it has support for many different data sources so things like Postgres Prometheus AWS cloud watch all the things that we use in our workflow as engineers whether it's in monitoring or just databases their support for that and you can also combine data from multiple different cases in one dashboard and I'd also like to hear from you if you just wanna type in the chat what do you use graph on the fall what's your use case you know what sort of reason and what kind of data are you visualizing in there this is that maybe I can tie in some examples as I go through the content today okay one thing though is that if you you've got if you have used co-founder before you probably know it can be really tricky at first there's so many features and you might not know when or how or why to use different things you know there's things like graphs and stats and gauges and world maps so it's quite a quite a lot of features and we might not know how to make the best of it so today I'm gonna actually take you through creating six common visualizations that you need for dashboards in a variety of different use cases and I'm going to illustrate that with three different data sets as I mentioned DevOps IOT and geospatial data and the last one just being a public data set and I'll also leave you with some Doc's and tutorials that you can follow and you can actually customize them afterwards to get started really quickly okay so that brings us to the second part which is around orientation and setup of a data source in this section I'm gonna quickly take you through a basic mental model for thinking about core fauna this is again this will be a refresher for those of you already using grow fauna will come with dashboards panels and data sources and I'll walk you through how to set up a data source for time scale since that's something that you'll probably want to do if using time scale okay so a good way to think about how graph fana works is that the most high-level unit in core fauna is the instance so you can't have one or more than four finite instances and these can either be in the cloud on your local machine now each instance houses multiple dashboards so the dashboard is usually something that you build for a specific purpose so you might have a dashboard for monitoring a certain product or deployment these are sort of the the units of sharing in infauna where you have a dashboard that houses many visuals these individual visuals are called panels so dashboards are compromised of panels which are individual visuals so things like graphs and gauges and maps and these visuals can actually be powered by one or more data sources so here's some examples of the data sources that I will be using in today's demo these are all time scale databases unsurprisingly but they house a variety of different things and the other good thing about kevanna is that in the dashboards you can have a dashboard connected to multiple different data sources and in this case what I have is I've got several different panels connected to different data sources as you can see by the the color coding that I have going on here and this is just a toy example that I put together for today's webinar you know I've got a gauge going on you're about from a monitoring data set I've got a graph showing covert cases and another graph showing buses so it's a mishmash of things but what I want to illustrate to you is that this actually translates to being a really good tool for doing custom dashboarding for stakeholders looking at a variety of data sources so say for example you want to have a dashboard that gives you an overview of multiple products in your company they all have data in different databases and coming from different places you can actually pull the dashboard that shows you that data all in one place the same goes for if you in a role that requires you to look at data from a variety of different parts of the organization maybe like a CTO you're looking at data from DevOps and products in sales you can just have that all in one dashboard from a variety of different data sources ok so that's a quick overview if that was too fast for anyone please do ask questions in the Q&A panel or the Q&A thing in zoom and now we're going to get into the first demo of the day which is how do you a time scale data sauce in grow fauna okay so let me exit out of this presentation and open my graph on instance so I've got a hosted Ravana instance going on yup and what I'm gonna do is show you how to set up a new time scale data so I'm gonna go to set it configuration and click on data sources and add data source now for those of you are new to time scale time scale is a open source scalable time series database built on top of Postgres so what I'm gonna do is select the Postgres data source also if you knew the time scale welcome to the time school community that's a good introduction to time scale on the various you know benefits that you can get from it as powering visuals so I've got this new Postgres data source set up and I'm gonna call it taxi DB so you can give it a name in this case it's gonna have data from taxis that I'm gonna use in the demo later on and in this case I'm going to find all of these just data based database information because my database is actually also hosted in the cloud I am just gonna pop over to my time scale cloud overview and take all the information and copy and paste it into the right places so I'm gonna host and then I also need to put in the port number afterwards because there's no specific box here for the port so that's a pro tip to be aware of is you know you've got to put in the host and your port number database name is called default TB so I'm gonna copy that in there so you know if you're using Postgres there's a variety of defaults for these if you're using a hosted time scale instance or there's a hosted database instance you'll find these in your like overview panels and a password it's going to be the secret password right here so it's super simple to connect a data source to a fauna the SSL is require okay and then I'm going to leave this connection limits default for now and then one thing to note when you're connecting a time scale instance is you want to select the latest version of Postgres possible I'll choose 10 and then you want to enable time scale DBM and if I click the Help button it tells me that you know time see time scale DB is a timesheet database on top of Postgres and if you enable this feature it will use time scale functions under the hood in graph honor so in this case Pravana will use time bucket in the time group macro and display time scale D be specific aggregate functions in the Crayola so basically what that means is that you will actually run time scales optimized sequel in order to make your dashboards run a bit faster or much faster in in many cases okay so we're gonna save and test and when you get the green that shows you that that connection is okay so we've successfully set up a time scale data source in your fauna this is how you can set up many different data sources in this case there's a Postgres database but also applies to other data sources and now that we've got this basic mental model for how graph fana works and we've seen how data sources works let's get into the next section which is the the main portion of today's section holding 6 awesome visualizations a quick reminder if you have any questions please use a Q&A feature in soon and we'll answer your questions at the end of today's session ok so in today's demo we're gonna use three different data sets to one demonstrate the versatility of using prefer now for this variety of different use cases so the first one we're going to use is a devops data set so this is monitoring a production database using Prometheus the second one is an IOT data set and it also has geospatial data a geospatial component in it and that's monitoring the live locations of buses in New York City which is where time scale is headquartered or everyone is at home right now but that's why office was and the third one is a public data set which is monitoring the culvert 19 spread in the USA coming from the New York Times so I'll just use a variety of different data sets to show you that you can whatever you're working on doesn't have to be monitoring you can use graph honor for it and I've chosen these data sets and these visuals that I'm going to show you because these are the things that I've seen time still users use in their dashboards and I'm also going to show you some tips and tricks I found useful in building dashboards we're gonna cover things like variables and series override that's more Pro tips for you if you're ready you know the basics of graph honor okay so getting to this first data set just to give you a little bit about that the data that the data source and into the structure of the data that's going on I'm going to focus more on building the visuals if you have any questions on any of the sequel syntax and any of the you know structure of the data please do ask but you know that is covered very well in the resources section at the end of todays at the end of today's session and you have links to that that you can go and get your questions answered as well but I just want to put that out there okay so this data set it comes from monitoring a time scale database using Prometheus so what I've been doing is monitoring a database and then using primitives to scrape metrics from that database so things like performance disk usage and like cache hits and things like that and I'm storing those in time scale I'm using time scale as a remote read and write for Prometheus so what I'm gonna build today is show you how to build these four different visuals these range from basic to more complex so the first one is just using a single stat single statistic to show the time scale version that's running on the database somewhere monitoring the second one is engaged to show the disk usage we're going to build this gauge right here with the various thresholds and the numbers here the third one is a graph to show the cache hit ratio going on in the database this is more about performance metric and the third one is also going to be about the cash head ratio but I'm gonna show you how to just use a single stat to display the latest cash hit ratio or the most current ratio if you don't want a historical time series like the previous one okay so let's get into our graph on instance here and I'm going to go to my dashboards and I created a blank dashboard for today's session so the first thing that we're going to do is create the single stat for the time scale version so what I'm going to do is add a panel and choose the single stat visualization and single stats are really great if you just want to visualize like one number rather than visualizing a whole trend over time and these are also good for things like version numbers or you know things that don't necessarily need historical context so in this case the database that I'm monitoring is the one that I just set up for taxi DB so I'm going to select that as the data source for the query and then I'm just going to have a sequel query that shows me the version of time scale that I'm using in this database so here's the query that I'm using it's just selecting the extension version from the PG extensions table where the extension name is times VB okay so and also another thing in this case you want to format it as a table so that it doesn't expect Co fauna has all these constraints around you know time series data you gonna have a time column an order by and group by but in this case we're just having a table and so that gives me the the number but we can do a bit better in terms of actually modifying the visual so in this case you'd want something like this under the values section to have quite a large font size so I'm putting 200% so I can see it you can also put like prefixes and suffixes so maybe you know you can put timescale in front of it just so that we know this is the time scale version of it I'm going to take that since I'll probably put that in the title but that's but that's the types of things that you can do in order if you just want to display a single number I'm going to give the panel a title and call it time scale version and then description time scale version on taxi DB DB and this is just practices for that you know if someone else is looking at your dashboard and wondering what's going on they have a description of what's actually happening so I'm gonna save here and that's our first visual just using a single step to show the time scale version that I'm using on this dashboard and it's actually you know can be pretty important because in this case you know I'm using one point four point two it might be the case that certain features are only available on certain versions of the software that I'm using or certain versions of the database so it's always good to keep in mind these kinds of things this is something that you know you'd want to monitor about your database okay so the next visual that we're going to do is a gauge to show the disk usage as I mentioned so we're gonna build this gauge showing the percentage of disk usage so let's do that the we're gonna do a new new panel and choose the visualization as a gauge so the first thing I'm going to do here is go and select my data source so the data source that I use is called monitoring DB this is where I have all my Prometheus metrics about the taxi database that I'm monitoring and the gauge here is very nice for something like this usage because you want to use a gauge when you want to know the latest value of something and you want to know whether it's in within an acceptable range or not so in this case I don't know you know is my disk getting full do I need to provision and you this do I need to upgrade in my cloud database provider so yeah so that's why the gauge is to be useful for stuff like this and in this case I've already selected one Ching DB as the data source so what I'm going to do is just copy and paste a small sequel query in there that shows you that we'll get the disk usage so what I've done in this query is as I mentioned in the database there's Prometheus matrix being stored in the database so I have a view call metrics and that view I'm selecting from it the time column and I'm using the time group alias here to bucket these values in one minute buckets I've actually set the scrape interval in my Prometheus to scrape every 10 seconds so what we do we're just getting one minute buckets from that and I'm selecting the average value as the percentage of disk use from this matrix view and then I've got this time filter macro in qivana here and you need to specify the time column this allows me to use this time filter at the top here in Cortana so that you know they know I don't have to rewrite sequel queries I can just use the UI to change the time and then here I know that my metric name is called disc used percent so I just say name like this use percent and that gives me the metric that I'm looking for and then I'm grouping by and ordering by one which can be a weird thing in graph honor for those you haven't seen it before one is basically just the first thing you're selecting so in this case one is the time so I'm grouping by an ordering by time and this is actually pretty important in graph on ax because group by an order by is necessary because go fana just plots the points or plots the data in the order that it receives it so if you don't group by an order by for your time series data it might not necessarily be accurate so this is just to you know ensure that I have my data in the order that I want it and okay so that's the query that I'm actually going to use as I mentioned a via for this time group alias this is actually using time scales time bucket function under the hood to actually give us these aggregates of the disk usage in one intervals rather than 10-second intervals like they were originally scrape okay now let's go into the modification of the visual so in this case I want to under the display section I want to show a calculation but I don't want to show the mean I want to show the last value that's being the last value in our in our data set so this ensures that we're seeing the latest disk utilization so it ensures you're reading the laser the most recent value and then what we're gonna do is now make this gauge more useful right now it's just all green that doesn't really tell us tell us much so what I'm going to do is give it a some thresholds in order to show the acceptable levels of disk usage so what I'm going to do is in this case graph owner has this thresholds function so we can associate a color with a sudden range so I'm going to say that Green is the base one I'm going to make the next one fifty I'm gonna make a following 175 and the following one ninety so I just did that by adding a new threshold levels there and in this case I want the colors to sort of match match the severity level so I'm just changing the colors to green yellow orange and red and Ravana has this nice thing where say for example I change this to 66 it'll automatically re-order the the the threshold values so that human you don't have to reorder them yourself but in this case I'll change this back to 90 it keeps the same color so I suppose it doesn't really know what each color means in this case I'm using it for like severity levels so that's this and in this case I'm going to give my Gator title of this usage percent and the unit that I'm going to choose is percent zero to hundred and there you have it so now that we've told Griffin you know what units were behaving in it actually divides up this gauge according to the thresholds that we just set here so I could change this and I could make the sixty and you can see the yellow pot then becomes a little bit smaller it basically just divides it up according to the thresholds that we've said over here and I'm not gonna mess with min and Max and another thing I'm gonna do is enable the labels so this actually gives you the values that you've divided up this gauge according to so that you know people don't have to I it and you might not know okay I see this thing is yellow on what does yellow actually mean these labels are a good way to remove any confusion from that one pet peeve of mine is that this text is a bit big as you might notice it doesn't really look that nice so you know maybe hopefully Griffin I can change that in the next iteration another thing I'm going to show you is say for example my disk usage is at 25 percent right now if I change the orange threshold to be like 15 or something then currently I can see the color then changes to yellow so that didn't tells me you know whether my current value which threshold is actually fitting into but in this case I'm okay with the yellow being at 50 that's okay so this is actually super useful because it'll tell you what you know whether or not you need to take action where the things are in acceptable ranges or not this is a simple example of disk usage but you can use it for a variety of other things for fixed resources that you need to understand the utilization of so I'm just going to call it this usage in taxi TB and description I'm just going to be lazy and use the same thing for the description you'd probably want to give a more thorough explanation but that's how you make a gauge and how you customize it to be useful to people who are reading it especially if you know you're the person who is going to be reading the dashboard isn't the one who made it okay so the next one we're going to go to is this graph of a cache hit ratio so because I'm monitoring a database I'm going to look for these kinds of metrics that are common in databases one thing you want to know is this metric called the cache hit ratio which is the ratio of blocks hit in the cache to the blocks that were actually had to be read from the database that weren't found in the cache so in this case I'm gonna stick with the normal graph this is the most common or the default visual ingre fauna and once again I'm going to choose monitoring DB as my data source now for this query I'm actually gonna show you that it's it's a more complex query because you know unlike the previous two we're not just monitoring one matrix so I'm not just like selecting one primitives metric I actually want to plot a ratio between two different metrics so what I'm going to show you is just a generally useful pattern for graphing ratios in graph on I using sequel and so in this case as I mentioned I'm doing the cache hit ratio so let me show you the query that we're gonna use in and I'll explain it line by line so in the comments over here the cached ratio is basically this calculation that we're doing here let's wait for this to highlight which is the sum of the blocks hit so in this case whenever I say hit it means the number of disk blocks found ready in the buffer such that I read was not needed so this is in the cache you know common computer science concept and read is the disk blocks read by the database so I've used these as aliases throughout the calculation just to make it a bit easier to understand and so the the key here is when you're plotting a ratios you want to do what's called a inner join to make sure that you're only the inner join is in in sequel is different from a regular join because the inner join is basically taking the intersection of two sets so basically what I'm saying here is I'm joining this matrix values table where I have times for both the hits and the reads because if you have time for one but not the other this calculation that we're doing here that relies on both of them both of their values fails so we all we want to take that intersection that's really the secret to plotting these ratios in go fauna so to take you through the rest of the query basically what I'm doing here is selecting the time of the cache hits and then calculating the cache cache hit ratio which is the value of the hits over the sum of the value of the hits and the value of the reads so we want to take total hits plus reads and use that as the denominator and I'm gonna call that cache hit rate and then what I'm doing is I'm querying this matrix values table but I'm aliasing it as hit and then I'm joining it on itself so I'm joining it on matrix values again but I'm aliasing this matrix values as weeds and I'm joining it on this time column such that I only want the values where we have values for hit and the values for read so as I said we take an intersection and then over here we're selecting the label IDs so each of these primitive metrics is associated with a label ID in my database beforehand I went and found these at 312 and 314 312 for hit three 14 for read and then I'm grouping by the hips time just because that's the first that's the first table that I'm wearing from and I'm also ordering by and hit time and so basically once you've done that you get this nice line graph that shows you the catch cached ratio this shows you for the last six hours if I do it for like the last 15 minutes you can actually see I have this database I'm not really clearing it that much in the ratio is fairly constant you can also play around with things like having points on your graph that shows you what where the actual data points were calculated at and you can play around with things like the full and things like full gradient in order to make your graph graphs look a bit better and things like line width in order to make sure that you know you can see the lines and the graph has accurate or acceptable usability in terms of like people being able to read it let me actually just put this line width back to two so that it's not too thick and basically what I've just shown you is this pattern for doing ratios in graph honor there's nothing more than I'm gonna modify on this graph so I'm gonna call this cash hit ratio or taxi DB and again I'm just gonna use the same thing as a description and save it now this is good if you want to look at how something has changed over time but if you just want the latest value I suppose you could you know look at this graph and then look at where the last data point is and read it but I want a single stat I just want a panel that tells me you know what is the last value that I'm looking at in terms of my cash feed ratio so that's where this next visual that we're gonna create comes in which is creating a cached ratio but using a single stat so this is vacation of the query that we use yes what I'm gonna do is just create a new panel choose the single stat Sodra fauna has that nice such but Singleton's single step okay single step and I'm actually just gonna copy and paste the same query so I have that in my scratch paper scratch paper here the data sauce that I'm going to use it's called monitoring DB and the query that I'm using is the exact same query and in this case what I'm going to do here is all the magic happens in this vision part of the query editor so we're gonna keep the format as time-series you could put it as a table I guess and what we want to show is instead of selecting average we select current so this actually selects the last value that is being returned in the time series data that that that the data source returns I'm gonna make this font 200 again so it's nice and big and in this case four percent because it's a ratio we are for the unit sorry not percent because it's a ratio we want to choose percentage between zero and one because your ratio is always going to be between zero and one and so in this case because I've chosen that percent it actually converts it now to say okay cached ratio most current 98 point eight one percent I can change this say if I want five decimal places for some reason it'll give me five decimal places you can play around with that well it's also pretty cool is that you can use the same sort of thresholds trick that we did before in order to show whether this cached ratio is within an acceptable range so for example I then go and select background and I turn it on and I might have a certain threshold over here that tells me that okay say for example if my acceptable threshold is 0.95 and I have another one at 0.99 so there should be a comma here come on 0.99 then you can see okay this value that we're currently seeing at 0.98 it's not green but it's not red either so it's actually just orange because it falls in between those two values in real life you probably want to just keep something like zero point nine five that's a very good threshold that should change and in this case what I'm going to do is invert this cause currently what's happening is that because the thresholds function such that anything lower than zero point nine fact that I put you take screen I want to invert this and such that we shouldn't really have three Coliseum I'm just going to put another color here 0.9 0.9 and five such that this 8% that I'm seeing here is actually a good you know cash a ratio that I'm seeing and again for the threshold values these need to be in the units that your output is in so I can't put something like 95 or 98 I have to put it between 0 and 1 because that's the unit that I've specified yeah so there's small things to be aware of you know things that might trip you up as you try and implement this yourself I'm gonna call this current cash feed ratio and current s-shaped ratio or XE t be okay so that actually takes us to the end of the first part of today's demo which is showing the how you can use these visualizations on a monitoring data set this is actually I've shown you a few different ways where you know you might want to have a dashboard that focuses on okay show me the historical performance of my system where you'd use a lot of these time series graphs and you'd have a lot of things that maybe roll up so I agree gates and you might also want to have a dashboard that shows you the current real-time or latest version of what's going on in your system where you probably want to use things like gauges or single stats should just show you the latest numbers so these are the tools that you can use to then create more complex dashboards so now that we've done with the DevOps use case the next two use cases I'm just gonna have one visual each so the next use case we're going to go into on the next data set is going to be the IOT one and if you have any questions about what we just did please use the Q&A feature in the chat and if you have any questions while the DevOps one we can answer them at the end of today's session okay so in the IOT use case what I'm actually looking at here is a geospatial data set that's monitoring the location of buses in New York City coming from the MTA so the MTA is just a Metropolitan Transport Authority they are the people who are in charge of buses in New York City and all public transport actually and I've got the data from there and what I'm gonna do is build a world map that shows me the live location of buses in New York City and I'm gonna show you how to use variables in Go fauna where's my mouse in order to show the different routes that the buses might be going on so this is just again a toy example that I'm playing with you but if you have an IOT fleet of devices you might want to show the real-time status of them and also segment them by different types so you can actually translate what I'm doing there onto your IOT use case okay so now that I've dealt with that overview let's actually build this visualization so as I mentioned we're gonna use a world map so I may choose visual and then such world map panel okay and then what I'm gonna do is let's save the configuration a configuring Phyllida and I'm going to select at the data source MTA bus DB so that's just where I have my bus data in and the the query that I'm gonna use let me show it you right now is one that actually makes use of another Postgres extension that's called post GIS so let me make this red error go away and then I'll explain why I did that just now okay so in this query what I'm doing is I'm using this thing called post GIS which is the geospatial extension for post Chris and I'm using it alongside time scale so what I have over here is both time series and geospatial data in the same database that allows me to do things like what I'm doing right now when I'm plotting location but the latest time so on under the current location or something you can do these very powerful things when you use databases like time scale okay so what I'm doing here is that I'm selecting the latest time that I've seen that I have for the buses so that's like the last time that I have half of these buses the vehicle ID it's just an idea associated each bus and something called the route ID you can think of this something that like segments the buses in two different types and then what I'm doing is that I'm creating this kind of like placeholder variable in sequel where I'm saying okay if the route ID starts with an M then we're going to assign it one so I'm this variable is actually going to be the color that the buses show up on a map so if the bus ID if the route ID starts with em when I color it color number one if it starts with B it's gonna be color number two and Q is gonna be 3 and s is 4 so M be Q and s these are just the different routes that they take they stand for different regions in New York City the different parts of the city and that's going to be the thing that we elias has color then what I'm doing here and those of you who have used Post chairs before we recognize it I have this column called Geum this is just something that the post Jas extension requires you to put your data in and I'm isolating the X and y values of that and calling that longitude and latitude respectfully and then so I selected all those things and I'm wearing it from my empty database called MTA and I'm selecting it where the time is greater than 1.5 minutes ago so I want like the most recent data and I'm going to group by the vehicle ID the route ID and this Cheam column and then on order by 1 which as you remember is gonna be the time so again this order by 1 ingre fauna is very important because of the fact that you want your data to be in time order ok so that's where the query ends and the last step of via when using the world map with time scale on time series data is you want to format things as a table and this is important because when we go into the visualization section we want to tell graph on ax if we go to the map data options that our result is in the format of a table and the aggregation is going to be the current because we're planning that the current location of the busses then we look at this very helpful message that kevanna shows us so it basically tells us that you know we need to indicate to it how our data that's being output it is being formatted so the table query format that we use isn't in geo hash it's actually in coordinates cuz we call it latitude and longitude and once you tell it you know probably how your data is being format you see something actually shows up on the map now we're going to actually zoom in in and see what shows up just now but I need to do a few more things in the configuration so what I'm going to do is call the location field the route ID this is the name of the location name column is used to label each circle on a map then the latitude and longitude fields are just a name of the latitude and longitude columns we address them latitude and longitude previously as you can see a St X as latitude sty as oh sorry longitude first and then latitude so whatever you a leus them you just gotta tell it you know what your alias is here and the metric field in this case I'm gonna put as color because that's what I want to differentiate my different buses okay now that we've basically configured the data we've told Gravano how our output is being configurated now we configure the visuals so the one thing that I'm going to do here is just select a custom center of the map so I did some research beforehand and found a place in North America that would be a good vantage point for looking at the buses in New York this is just off the coast the East Coast in the USA you can also choose one of the presets values such as like North America or something if you're looking at something on a more global scale and then we want to play with this thing called the initial zoom so this will zoom us in from this you know global view that we have going on I'm gonna choose eleven which is 12 let's see where that takes us maybe two too much in this 211 ah so you can see as I scroll down a little bit on the map I am seeing a lot of big circles these currently are representing my buses these circles are really big right now so I'm gonna do is select this main circle size to be true and the Mac circle size to be one and that should change hopefully as you can see that the circles became a lot smaller and then what I'm going to use here is I'm gonna show this legend and use a mouse wheel to zoom this is just so that you can like zoom in and out put your mouse and then what we're gonna do is color the buses according to the colors that we've assigned them so remember in the previous step we have these colors 1 2 3 & 4 this is going to be like a hack of the graph onna thresholds options somebody's going to set thresholds 1 2 3 & 4 and what would happen now is I'm gonna assign colors to be to these different numbers so say for buses that are less than 1 I'm gonna keep it red because these buses are not the ones we want to look at then for one I'm going to assign it blue but true I'm gonna sign it yellow for 3 I'm gonna sign it purple and for 4 I'll keep it as green and as you can see as I selected these threshold colors the buses based on whether they value based on the color value get colored accordingly on the map I don't know why these orange things are still there maybe if i refresh they'll go away but basically that this threshold value as you can see this is why enable the legend it actually tells you okay buses that are colored number one are going to be blue two is yellow three is purple and four plus is going to be green the way the thresholds working grow fauna is that they're inclusive of the lower bound but exclusive of the upper bound so that's actually something to keep in mind if you have any trouble with thresholds that's tripped me up a few times okay so we've actually got this graph down showing us the real-time locations of the buses in New York City I'm going to call this NYC buses real time won't put a description for now save the dashboard and go back to our main dashboard okay so as I refresh that the green not the green the the orange circles went away and as you can see I can see the real-time location of my buses right now based on the colors but this way we're gonna use some variables to actually make this visualization even better I want to make this more interactive because right now I can basically zoom what if I only want to see buses of a particular type say I'm you know doing some investigations and I only want to monitor buses of a certain type right now I have to go into my sequel code and you know modify put in the way statement over here to filter out buses of this type and if I wanna wanted a different type later then I need to change it so all I'm gonna do is use this feature in graph on a called variables in order to help me make this dashboard a bit more interactive so what I just did there so it does a bit quick I'm going to dashboard settings and go into variables and I'm going to add a new variable that shows the different routes that are able to be picked from the bus routes that we have so labels in Griffin are not labels variables in Griffin I work just like variables in other programming languages you just define a variable and then you can reference it in your queries so what I'm going to do here is called as a variable route because that's the thing that it's about and then under the label you can actually give it a real human readable name so that when you're selecting it in the dashboard you can actually see okay this is about New York City bus routes okay that I know what I'm selecting this is important especially if you're you know in a monitoring context or something you want to stay away from symbols and give human readable names where possible then we're gonna select the data source I'm going to select MTA bus DB this is cool and the other thing is that the query the type of the variable is going to be query these are cool if you want to actually populate your variables based on a query returned from some database in this case I'm gonna define this query showing the different types of buses that I have so in this case what I'm doing is actually this is a hack that I learned from a teammate of mine at time scale who actually used to be a co-founder contributor he basically said a way to do this is you want to have both symbols in this case M B Q and s because that's actually the values I'm using in my query but I also want human readable names in this case Manhattan Bronx and Brooklyn this is what you know MB ns it's Stanfill so the hack around it is you don't just select the the symbols but you define these key and values in this case FK being text and I have V being a value and I've defined these key value pairs under the values in this values set over here and then the end I basically say okay the way that my values are set up is key first and then the value and you can see over here at the bottom the preview of the values I'm getting actually the human readable names but they correspond to MD Q and s and you can see how that comes together but later in our query in the selection options what I'm going to do is select multi value because I might want to monitor multiple of these bus routes at the same time and I'm gonna include an all options such that all of them are selected I'm not going to use this experimental feature for now we might do it in another webinar but you can actually see these are the values and I must select because I want to know these different routes and I'm gonna select add and I have my variable right here now right now you can see our bus the variable we can select but if we select stuff it doesn't really change anything so let's actually modify our query to make use of this new variable that we've just introduced yeah so what I'm going to do here is because because I'm using Postgres and time scale the way that we make use of these variables is in the where Clause of our sequel statements so what I'm gonna do here is in the where Clause I've added a statement that says okay if the substring of the root idea if the first letter of the route ID substring one one's saying started the first letter stop of the first letter is in the set of allowed routes defined by this route variable remember we defined route as being these groups of key value pairs so I wanna know if the first letter is in the set then show it on the map and you can see wallah I've shown Manhattan over here and I can only see buses that are that are from from Manhattan right now which are blue if I expand this allowed set so this set right now just says Manhattan if I expand it to Bronx and queens as well then those buses will start to show up so essentially I've just shown you how to make your dashboards interactive so it's a really good way you can have variables for different things and in this case I can also select all one and that shows me all the buses that are that are going on right now that's just a include all option okay so we have successfully created this awesome monitoring visualization using variables and as you can see this variable also shows up at the top in your your dashboard you don't have to just go to the panel to look at it and you can select you know which variables you want and your queries will actually change in real time and your visuals will actually update as well this is good because if you're making dashboard for different stakeholders you can actually help them to explore without you modifying the code over and over again this saves a lot of time and it saved me a lot of time as well okay so that takes us to the end of the the geospatial data set and IOT data set the final thing in our demo that we're going to do today is going to be using this public data set of monitoring covert 19 I've gotten this data from the New York Times and what we're gonna do is build this really quick visualization of the total cases and total deaths but using this hack of using two Y accesses in it I'm series graph okay so let's get started on that so you know one of the reasons why I chose this is because graph on ax is open source and so it makes it really great for using you know public datasets with because you know you don't have to pay to use it or anything and in this case the query that I'm gonna use first of all I'm gonna select graph because that's what I want and then the query then I'm gonna the database I'm requiring query is called the covert 19 data and the last thing I'm gonna do is just write the query in that's going to just it's a very very simple sequel query I'm just selecting the time the sum of the total cases and I'm calling that total cases and as sum of the total deaths as total deaths from the state's table so I have a basically a table sharing data by States group by time and order by time very simple you notice here I get this data outside time range thing because my current time range is set to 15 minutes let me set that to 30 days and I actually get this graph that shows me the total cases and the total deaths according to covert in the USA according to New York I'm sorry from Co mid-nineteen in the USA and you can see how that changes over time now the issue here is you know if you see this any newspaper some people might look at this graph and say okay this this cases are improving are increasing over time rather but the death stay looking fairly flat you know maybe this thing is not that serious maybe they haven't you know read the other information about you know what's going on and so this graph can be a little bit leading in terms of the growth rate of these two different variables and that's because of the fact that they're being crowded on the same y-axis over here and the scale of these total cases is actually distorting how you can see the the growth rate of the the total that's going on yeah so we're gonna fix this by doing something called a series overriding Tofana so I'm gonna click add series override and I'm just like the alias and the aliases that you defined will pop ups in this case I'm gonna select total debt and in this case I'm gonna basically create another y-axis I'm gonna scroll down to my axis and select y-axis too and then you can already see that okay I have a second y-axis right now and this y-axis actually because I've plotted total cases using the Left y-axis and Tolan it's using the right y-axis I can actually see okay now so the decks are actually growing a lot more than I initially thought when I actually product on an axis that shows me the the accurate scale so in this case what I'm gonna also do is modify this visual to make it you know look a bit more useful and make it more useful for people looking at it so I have the actual data points if you want to look at data on a set ending and then what I'm also going to do is change the colors of the series so in this case we just click on the color I can select total cases is being yellow and then I'm the select again clicking on the color total deaths being red and in this case I've actually plotted these two quantities on the same graph they're being selected in the same query but I can actually have them correspond to different y-axis years and that's super important especially when you're dealing with two quantities that are just different magnitude of values this can be a super useful trick to making sure that the growth rate stay in perspective perspective so I'm gonna call this Koba 19 spread in the USA and over 19 in the USA and I'm going to save that and that's how you can also you know we built this line graph over here but that's actually a more advanced version if you're plotting to series on the same graph and you want to use different accesses in order to keep things in better perspective and you know make these graphs more accurate in terms of the people looking at them more easily comprehending the growth rates okay so that brings us to the end of this demo thank you so much for for joining us for today's webinar hopefully you learnt quite a bit I've taken you through these three different data sets I'm doing very quickly just give you a recap of what we've talked about and leave you with some next steps and resources that you can get started on your own ok so what do we talk about today we looked at why should you use Pro fauna I talked about how it's low-cost support for a variety of different visuals and data sources I showed you how to set up a graph on a data source that one being time scale we both six different visualizations for three different scenarios DevOps IOT and geospatial data as well as a public data set of tracking covert 19 and we both things like a single static age at grass and a world map and then lastly I showed you some pro tips using variables series override and threshold to make your graphs more usable and and you know get more value out of them that's what we did today hopefully you found a lot of value out of it has a next step check out the graph on a tutorial that we put together which is basically what I showed you here but in document form you know it takes you to step by step all the queries that we used and the next thing that you can do is join the time scale develop a sec you know if you have any issues any problems there has a community of developers not just people like me who work at time scale all the time still founders but also other times the users who help each other out if they face any problems and thirdly if you're looking for a way to get started with time scale and you don't want to manage and you don't want to host it please start a free time scale cloud trial we're giving you three hundred dollars in credits to start and then lastly if you're inspired by anything that I come and if you incorporate any of the knowledge it'll be cool if you can share it with us to let you know that to let us know that you're using this stuff share your creations on Twitter using the hashtag my time star dashboard hopefully we'll get a few of you doing that lastly I'm just going to put I put some bonus resources here just related to some of the things that I covered in the demo so the first one is the first use case that I said it was in the dev ops use case we've actually that was using this prometheus connector that time scale has developed we've put out two things that you can follow up if you're a dev ops if you have a DevOps use case the first one is observability design dock we were actually taking public comments on our new Prometheus connector and then last one the second one sorry is helm charts that you can then go test out and and start using in this github repo that's linked here and then if you enjoyed the public data part of today's demo we actually host a virtual Meetup called data pub it's all about public data and that happens on the third Tuesday of every month you can go to this link to sign up for the next one and then I've left some links to the JSON of the demo dashboard that I put together so this dashboard that I just created right now you can actually get the finished version as a JSON in JSON you can just copy and paste that into a new graph on a dashboard and start you don't have to necessarily follow along and then I've left you a link to grow fond of community dashboards if you can also use and then lastly just an appendix on inner joins if anyone was confused about that you can come and take a look at it and that brings us to the end of today's session thank you so much for joining us hopefully you learned a lot and I'm going to take your questions now in the Q&A part in the Q&A panel so thank you again and do put your questions if you haven't yet and we'll answer them one by one

Channel: TimescaleDB

Views: 34,326

Rating: 4.9220057 out of 5

Keywords: time series, data, database, timescale, timescaledb

Id: oPumWaoNw5s

Length: 62min 50sec (3770 seconds)

Published: Thu Apr 23 2020