Send Looker-on Mark with airSlate SignNow

Send looker-on mark

everyone thank you for attending our webinar where we'll be talking about modernizing your data infrastructure with looker and Amazon Web Services we're really excited be here because we're looking to sharing we're looking forward to sharing through exciting new strategies to get more out of your Adria instance in air support so today what we're going to be doing is going over three areas of focus specifically for typical age of us admin the first is database optimization specifically on Amazon redshift database we'll be talking through first and foremost what's optimized and then how to optimize this also we talking through cost and usage optimization and security and monitoring with a twist cloud prologue but before we get to that we're just going to give a couple of introductions about ourselves so my name is Kenneth Penniman and I'm a product marketing analytics manager here at looker I'm also a dated with experts and the producer of liquors pop guide to databases which is available at database' smokers calm and I'll be talking to you guys redshift optimization specifically today great thanks to that my name is Dylan Morrison I'm a product manager on our data platform side I oversee our blocks program which is what we're going to be talking about a bit today after release AWS awesome great um so before we get into talking about Eider us we want to talk a little bit about the core data platform so many of you guys attending the webinar today already have and the massive database probably built on the edge of us or you're thinking about building you know your database and knew your data structure to AWS you probably use this database to centralize you know disparate data in your organization coming from cloud services ad hoc reports and maybe also transaction databases what loader is essentially is a data platform that sits on top of that database that makes it easy for animals to do organizations on to model and govern access to that data in this centralized database so users can easily access that and build insights from that so by essentially abstracting all the relationships in your database and surfacing it to users form of tables charts - and dashboards what we've really found is that you know that abstraction this relationships contain to the database copies can really surface that data that was previously locked down to greater numbers greater numbers of end-users in the form of operations Ashworth ad-hoc reports scheduling alerting or even data delivery to other systems and lookers powerful self-service capabilities really allow users any users in your in your organization you know whether they're technical and non-technical to really easily dig into these reports and dashboards by really slicing dicing that data to learn more themselves and we'll show you a little more about how that process works specifically with some of these use cases with a uf7 to talk to you later today so in some cases you know the data coming from the individual data source is so templated that we're able to write an analytical workflow and analytical patterns that anyone was look or instance can use in the poi that work for their own data set so we package these analytical patterns and workflows into a set of the foible code that we call worker block and really the three things that we see what four blocks doing is really taking the analyst workflows from some of the best data experts in the world and dropping those workflows right into your own workflow so they really help accelerate your analytics you know taking taking some workflows that might have taken you hours months days or you know or even years and dropping them in in minutes into your workflow so you know in some instances we've actually collaborated with the experts that created the datasets themselves as is the case with you know the redshift optimization block the security and monitoring block and the copy music slots that we've collaborated with specifically with Amazon on and so really what this means is that you're really getting the the most relevant analyst and dicken product experts in the world and data experts in the world to help you get more value out of your data set so because you're sort of relying on the expertise of people that are really intimately familiar with that data that you're using that really enables you to reuse their analysis so that you don't have to do you know that upfront work yourself and you can really focus on tailoring that code directly to your use case so we really look for blocks what your first able to do is really amplify your team you know allowing anyone that really needs to be given access and you know essentially specifically for the agent console which is kind of which is locked down and only available to admins what look or blocks allow you to do is really allow anyone that needs to be able to access and troubleshoot Riteish's performance or cost and usage or David security to be able to do that and have access to that information I mean everything fine-grained self-service way without having to either be given access to the a to this console or go through you know administrators that that might have access to the APIs console so it really reduces those bottlenecks second it's really one unified interface that provides value to everyone that allows you to kind of simplify and distill your workflow you know when when you're going back and forth between multiple contexts and your context shuffling you know that's actually kind of a pain point in the sense that you're going back and forth between multiple applications and you're using one application to maybe perform some diagnostic analysis and another dracs we take action with look for what you're able to do is really have that one unit by the interface it allows you to diagnose and also prescribes value and then allows you to take action on the same platform third and finally is that you're able to really prioritize and focus your optimization efforts by using looker and in self-service technology to slice and dice your address data in a way that again is very simple and allows you to ask better and deeper questions before so with that in mind we're going to talk about how to optimize redshift using specifically the redshift optimization block but it's really also important to note that you know across this webinar there are going to be strategies that we are going to be talking about that are going to be using the looker blocks in this webinar as a framework to to guide those decisions but it's important to note that even if you're not a looker customer if these are strategies that you can apply in your day to day and so we're making sure to ensure that you know this is value that you can take out of the webinar but also if you want to apply as looker you can you understand how that workflow functions so in the case of the right shift lock we built an analytical work flow right from with the richest system tables so just these tables are standardized across all instances of redshift this block me influenced her quickly so I've been planted this block myself and it's taken me about three minutes and you know that includes a couple of very custom-built patterns from monitoring network patterns joint distributions and things like that all of which I would have had to build myself if this if we if I didn't have this blog available and so it's important to note that there are no real customizations for this walk that are needed beyond specific specifying the connection and connecting your database to this block and so as I just alluded to you know one of the major benefits of the redshift lock is that it will really help you focus your optimization efforts and know what exactly needs to be optimized you know across your across your data warehouse in this case really knowing what the optimizes is pretty much half the battle so in terms of this you know I think we want to talk about the first by seeing you know some of the common pitfalls that that really befall a lot of people now which are you know definitely practices that probably do a lot more harm than good and obviously I think a lot of these probably are all you notice some of these and understand to avoid them but it you know it's still testing sometimes to do these in practice so obviously you know you don't want to optimize tables or schema in a vacuum what you do want to do especially you know given the nature of the functionality of Amazon redshift is optimizing these based on actual career patterns you really want to understand how your users are using your your different your data and really co-locate an optimize your tables based on their usage rather than you know trying to attempt to understand how they're going to use it before you and so again really what this is is a waterfall approach right where you're trying to assess all your user needs beforehand and what we're we're saying is that this is really a constant process that you want to do when your regimen to consistently iterate on your performance and understand how your users are we were in your database so that you know you can make this flight increment of tweets that provide a lot of value and let me produce a lot of bottlenecks on the back end and so obviously you know you don't want to prematurely optimize your data you really want to respect that that of your own time that is our colleagues which of course means that you know you don't want to spend all this upfront effort if that's not necessarily going to be used so really you know what we want to do now is get into a little bit more specific so how does the redshift law really help you officially iterate on actual query patterns so what we've actually built with looker is inside looker is this redshift orange dashboard that's available this block and this dashboard is really your go-to and home base that you can use this starting point when proactively working on register optimization and so there are several tiles on this - word that show patterns and cohorts of queries always be on the Left you have you know a query time histogram that it shows you you know a level you know which players are going to where where you know your overall performance is lies with your righteousness and obviously this you want something that looks a little more like the one that we have showcase which is the vast amount of queries are going to be taking you know sub 1 seconds to 5 seconds and you only a small amount of queries you're going to be taking you know half you know more and which would declining you know return times as you as you get in up into you know the minute two minute mark you also have a you know codes or queries and you know such as the top ten running queries right and so this is actually incredibly useful on when you're trying to look at specific queries that people might be running consistently that are taking a really long time to run and so so really what we've tried to do is make this flop both descriptive but also prescriptive to give you an idea of where to prioritize efficient workflow you know moving down the block there's also two patterns that we think are particularly useful which are the patterns that are talking about network distribution so you know these two patterns on the Left we see you first have this pie graph that shows the overall share of network district redistribution activities needed to perform joins in your cluster and so everyone that's that is a wretched app and knows that you know I'm sorry why default the block really considers you know queries that are only running the tax day so and on the right you know what we do is we have we filter for you know specifically bad join distribution and and break them out by their joint condition and so this is really useful for two reasons the first being is that and if we're getting to this limit later which is that network redistribution is a very common and very significant issue that can really bottlenecks performance and red shift warehouse if you don't necessarily know how to approach it in the right way but the second reason you know is that it's even more important which is you know when you're running queries based on looker um looker really tends to produce very consistent join patterns and so when your users are using the looker data platform what that allows you to do is really standardize exactly how those join patterns are getting implemented and as a result understand really how it gives you a lot better about idea of exactly that usage of your data warehouse rate so talking now about the tricky half which is diagnosing the problem and selecting the appropriate corrective action so really the first one is you know once you find a particular joint pattern or problematic query that you really want to know more about you can really drill in the Booker which takes you you know you can drill an off to this top-level dashboard and this takes you to a further dashboard that we call the query infection dashboard and really this is where we believe that look are trying to shine it really it brings together all of the clearances of information from your rich chip system table and brings it together in a really comprehensive view and obviously this is available for any queries that you decide to drill into from the something larger dashboard that shows the aggregate amount of queries that you're running Prosser is this and so really what we're going to do is rather rather than go into too deep on one sub desk we're going to talk about how you know two distinct problems that could fall a lot of regice evans when attempting to optimize their network activity or yeah optimize their usage on redshift one of course is going to be something that a lot of people are familiar with and the other is something that not add on but has a lot of has a lot of very powerful implications so going from the first one which is excessive network activity the query inspection - we're going to really highlight this really well for you right it's going to show how many megabytes were distributed or broadcast across the network for a particular query and so if you see a very high amount for an individual query you know but that one really requires a lot of network activity sort of shuffle data across nodes in your cluster and this is really the most common issue because register will by default set distribution sizes even across your cluster and so you know everything by default is going to have a lot of movement across your database and this is something that as a righteous admin you have the power and control over to optimize as you go forward and learn more about your usage habits and it's important because this is a huge sizeable information impact so let's just clear all these ok great so the way that you can really identify this is looking for you know across the in the adjugate number the a great dashboard looking for a high number of distributed or broadcast bytes in the top level metric but also looking for you know distribution both which is both types of both sides of the table are shuffled to produce to join distribution outer right which is the bigger of the two tables actually moved around the network or broadcast inner which means that the smaller of the two tables needs to be copied to every node in your cluster to the larger table so in terms of presses action what this allows you to do is really identify those network patterns and apply those distribution keeps those tables to allow both you know it in terms of tables that are going to be used very frequently co-locating those tables on the same cluster and obviously for smaller tables distribution all might be better but again it's not a silver bullet because again that comes with the penalty to scanning so we're going to go really quickly and through the large net that we've joined really what these are is just a join condition that causes the database to sort of brute force approach to join these two tables so things like in all of the joins are going to produce these or text matching when the relationship between the two tables when you don't have relationships in between two tables so you look for an insight of a larger text field you can like parameter for example the like loss or even or logical queries that you know we're not necessarily constraining as to things being equal but what really saying something either this or this can happen and this really shows up in our experience section dashboard it's highlighted in red and when we march that nested loop so even if this is less common this actually has a huge impact on performance so this is something that when we do see it pop up we want to make this a number one priority to solve and so you know one of the one of the cup of two of the ways that you can do to solve this is you know really refactoring you know larger nets that we've joins into smaller nested loop children's like this is really you can really do this effectively by partitioning based on dates for example so rather using a large nest we've run into joint on two tables that contain all dates in the current you know certain series what you you want to do is constrain that they feel maybe you know ten dates that you're really interesting at and so you know obviously reducing the reducing the size of those tables is going to produce you know even if the data tested through for set that that joins it's going to produce you know the tables the results in far less time and secondly some inequalities much better to write a win dysfunction and third even if you do have this brute force that table definitely persist that table and persist that relationships you don't have to execute that lies every single time so really for those of you that want to more both of these answers are documented in our liquor developer community discourse and we're happy to followup with you you know over a call or demo if you guys interested to learn more as well so with that what we're going to do is turn this over to Dylan Morrison who's going to talk more about eighty AWS specific blocks awesome they can does really great overview of sort of the administration optimization of performance which is one of the critical elements of any cloud deployments and the other two elements that often come up are your cost your usage so how much is all this costing you and then lastly our at security and monitoring so making sure that your project flame is locked down knowing that seeing things they should be able to see all your data secure that so first one word if you look at its cost and usage so here's a quick overview of the data pipeline we assembled to analyze your cops needs to say that there's actually a report that AWS produces it just called the cops and usage report so what we're doing is just dumping that into s3 and then we're using a Cena to query that information and plopping a looker block on top which gives us a premade data model around this and we're using a Cena here instead of redshift because billing data can get quite large we've seen some customers you know bring up that tens of millions of rows per day and so sometimes which is easier to handle using asita so for crossing we actually work with the teen at AWS to identify the three most popular cost-saving strategy now each each company is different so these might not hold true for everyone but they should for the majority of users the first lever is reserved instances and this probably gonna be the biggest lever of most companies the important thing to know is that reserved instances are usually around seventy-five percent cheaper than on-demand instances so naturally we want to maximize our use of reserved instances to be the most cost effective to do this it makes sense to just forecast the amount of instance hours we'll need each month and tried words are close to that amount on the flip side you want you don't want to overestimate the amount of hours you'll need otherwise you let paid too much and you can lose your excess capacity at the end of the term so is an interim process to get up there but at the end of the day most sophisticated companies end up at about 80% reserved instance coverage so once you get in that ballpark here you're pretty good shape another obvious but important thing to remember here is that unless you're you're you're using a server list service like lambda and nearly all AWS tools are using at hosted easy to instance so our you're using redshift RDS EMI EMR all of those are running on you detail the second lever is data transfers so one of the great things about AWS is how easy and cheap it is to store data it's what you start actually moving that data around that things can get a little bit more pricey so generally speaking the farther you're moving the data the more cost it's going to be transferring data between different ats regions is the most pricey let's say from Oregon to Ohio my cost say two cents a gigabyte or something like that Oregon to Europe might be around nine cents I think currently and then I Oregon just more like Singapore is about twenty-seven cents per gigabyte so pretty significant increase there I trying to bring data within the same regions but between different availability zones is the cheapest at about one set four goodbye therefore to save costs you should try to look lies your AWS services within the same region as much as possible and the other factor besides how far the data moves is how that data moves transferring data between AWS users and services within the same network it's relatively cheap and often even free at transferring data over TTIP however to the open web can get pretty costly at about 9 cents per gigabyte so to the extent possible you should minimize the amount of data flowing over the open web or through external web applications the third lever is using custom allocations so this is far as the most flexible method of cost savings ABS allows you to apply custom tags to resources which allows you to just add an extra Bute extra and extra attribute for analysis and so this one is going to be super contextual based on your business but be quite powerful and effective so what's great about the looker platform is that not only can we see all this information and perform all this analysis in one place we go out and take action on that data as well so if you notice some of your instances haven't been used in the last week or month you can say father terminate those instances using data actions directly from the liquor UI you can also launch a new instance or reserved instance if you need to plus you always you'll get all the benefits of our scheduler and automated alerting the next thing we talk about is security and monitoring with AWS and we have a pretty similar pipeline here to what we assemble for cross usage the only different things that a cloud trail delivers logs to s3 in JSON and rather than CSV so Cloud trail is a really great service from AWS that provides security and DevOps personnel with a whole suite of information to perform their jobs better so what do enable cloud trail in your AWS environment you'll receive logs of each action taken by a user or AWS service these laws include information such as who took the action where when the action was taken and what resources were involved so when you're a heavy AWS user there's huge volumes of actions initiated by users or services such as ec2 PPC I am wretched etc and so that means every time someone queries the database logs in today to this console console it's an error or takes any other action it's recorded as an event in cloud trail and that makes it or especially useful for monitoring the type of use of each AWS service and the youth in your users it also makes it great for governance so you can ensure that there aren't any loopholes in your permissioning system so that you're always taking the appropriate corrective actions I can help us compliance so meeting any regulatory requirements such as data location movement AWS actually has a list of how you could use cloud trail to meet regulatory needs across a number of different industries which is really helpful and then finally for auditing so Quadro can help you on and across the board you can track down and need troublesome user or services you can see where the most errors or being Kurd and should look for things like modifications to security groups and other important commissioning aspects and so since the cloud trail provides so much information it can be used very flexibly for a host of use pieces to match your business needs and so the next two minutes here I'll just walk really quickly through some of these column examples so one common use case is looking at errors throughout your AWS environment we can look at errors at a user or service level and answer questions such as you know which users or sort services are taking actions that are resulting in errors are those end-user errors or those things I need to fix so on the left here we can see a few examples and the user who has had you failed login attempts and maybe that means that they're trying to get into a certain red ship instance or ec2 instance that they're not allowed to so maybe you will want to give them slap on the wrist another use case is looking at all the various yeah errors across your entire environment and so this has quite a few examples that were running up on time on so I'll sort of brush through but this is just showing errors cross your whole your whole cloud deployment what can be really helpful here is looking at trouble with logins so because potentially hundreds of logins are occurring every day it's important to identify those that sees the outside the normal course of business some key components to examine with respect to logins are logins that occur outside of our normal network so to do this we can examine logins across all IPs and look for any irregularities such as repeated failed logins from an unknown IP which could be an indication of someone attempting to penetrate your security settings next we can look at geolocation and this is this is really easy to interpret visually if you're seeing logins from geographic regions where you don't have operations or virtual clouds that login activity is probably suspect and worth investigating and then finally we have logins without using multi-factor off so this is looking at the number of failed attempts to log in using MFA next actually what it takes of course then why is Emma fake failing to these users Stepford liquor we can drill down into all of those individual events and look up which users were corresponding to those failed attempts very very easily so we're just about at the time here and so with that we will just let you know that we have a host of resources available on our website at liquor calm that goes into detail about each of these use cases in much more detail and we also have a great offer with AWS to provide a thousand dollars in free credits towards your AWS data warehouse and so that's the thing you're looking to get started with able you this is a really great entry point and if you're interested please just head over to the URL you're seeing at the bottom there discover logger comm head start AWS and we can get you rolled with the credit and get you any resources you need to help better manage your project limit thank you