Analytics in 15: Simplify Data Discovery with Amazon Redshift Query Editor- AWS Analytics in 15

Key Takeaways

foreign [Music] and thank you for joining this session where we'll talk about how you can simplify data Discovery with redshift query editor my name is and I am a senior analytics specialist Solutions architect at AWS so let's first level set and talk about what Amazon redshift is Amazon redshift is a fully managed petabyte scale cloud data warehouse it helps you analyze all your data so your log files your transactional data your click stream data semi-structured or even nested data it is the most affordable cloud data warehouse that delivers Better Price performance relative to other cloud data warehouses and it's also easy secure and reliable and you do not have to worry about managing infrastructure now customers usually tell us that they want to promote self-service analytics by empowering their end users such as data analysts and business analysts to load data into their analytics platform for this you need to make sure that you democratize access while making sure that you are following the principle of least privilege it is also important to reduce time to Insight by ensuring analysts can run SQL queries to analyze and visualize the data as well as run machine learning based predictions using just SQL commands finally once they have done the analysis you need to give them the ability to share it with their teammates so that the teammates can review or reuse those analysis where possible so this is where query editor version 2 comes in as it accelerates self-service analytics by enabling users to create tables load data with simple Wizards run analysis share those analysis with their teammates and even more capabilities are there with query editor version 2 you can browse schema and tables that you have access to load data from S3 and even from your local file system using a UI based interface and visualize query results with charts right from the query editor you can create notebooks run multiple queries in the notebook annotate them and easily collaborate with your teammates by sharing those notebooks for accessing the query editor you can integrate it with an identity provider this means the analysts do not need to access the AWS console but can directly connect to the query editor and run queries now before jumping to the demo I wanted to quickly talk about one new feature uh that was recently launched and that is the ability to load data into redshift from your local file system uh and you can do that using query editor as well uh so with this you can load files directly from your computer into a redshift table do quick analysis and create charts uh and then once you create those charts you can share it with your teammates and you don't need to wait for a central ETL team or ETL developer to load the data into the data warehouse and then you get access to it so you can do this in a self-service way all right so enough talk uh let's see this in action and let's go into the demo so uh this is uh the Amazon redshift console in the AWS console uh to access the query editor version two uh you can click on this which brings you to the query editor tab uh some I've already had the tab open so I'm going to close this and go here so uh here you see that I have a dark background but if you prefer a lighter black background you can toggle it with this particular button here I'll keep the dark background because I prefer that now here you can see or you have access to the different clusters uh the database within the cluster the schema uh the tables and if you want to like you know look at the table to see what the fields are the data types all of that is available from within uh this UI interface to connect to a database or to want to connect to a cluster uh you can I'm already connected but I'll show you you have different options uh so you can use a Federated user option you can use temporary credentials uh you can even do it using a database username and password or a Secrets manager I'm using the temporary credentials so I'll just save it so it's going to reconnect to the database so now uh we're connected to the database uh now let's see how you can load uh data into this into into a table so uh first step is to create a table right so you can use this particular drop down and there are different options where you can create a database a schema a table or a function uh we'll create a table so we need to First provide it with the schema and the name of the table so I'm going to name this table as Nation and then you can add Fields uh manually by clicking this but uh I I'll actually load it from a CSV file so you can load from a CSV file and you can browse to that file and it automatically takes the headers of that file and and creates uh The Columns for this table and you have the option to change the data type or even the encoding option I'll keep it as default for now and then you can also you know uh make a column as not now or make it a primary key or a unique key and you know different options here so for now we'll just keep everything as default and click on create tables right so the table is created uh so let's just quickly do or select star from Nation and just see what the result looks like and as expected it doesn't return any rows because all we have done is created a table we have not loaded any data so let's some load some data into it so to load data you can either use S3 or loaded from a local file if it's S3 you can just provide it with the S3 path you can browse I'll just choose a local file because this is a new feature that was that that was recently launched uh so I'll browse uh go back to the file on my computer uh you have the file option where you can select the delimiter or the code character uh you can select a compression type and there are a few different configurations that you can do uh here I'm going to choose to ignore the header rows because my file has a header row and I don't want the header row to be loaded as a row inside my database or inside my table so I'm going to say I'm happy with this and I'm going to select the schema and then the table so this is the nation table that we created just now and we'll load the data right so it says the loading data was successful so if I come back here and run the query uh the the data is available so you saw how easy it is to create a table uh from the UI and you know load data into it and do some analysis on on the data now the next thing is uh creating an explain plan right so uh here you have the option to run an explain plan on your data so basically you can toggle the explain option here and then run this query and it actually showing you uh what the explain plan for your particular query would look like and how it is going to be executed uh you can also see this in a graphical interface where it actually gives you a graphical representation of the explain plan and the steps that go behind running this particular query now once that is done if you want to when you're if you want to run your queries uh you uh can run multiple queries at the same time so here I have already written two queries one is uh the first one is actually getting the quantity sold by month and the second one is getting the quantity sold by City And I want to run both the queries at the same time so I just ran both queries uh the first tab here uh gives me the quantity sold by month and the second tab gives me the quantity sold by City now let's say if I want to save this for future use I can go and save this particular query by clicking that button there and then let's name it sales queries and then I'll save it so if I go into the queries uh tab right here you will see that my queries you see this particular query that I had just saved but if I go into the shared by me or shared to my team it's actually empty that's because uh this query is stored from it's I saved it for myself for use in the future but I have not shared it with any of my team members but I can do it by going into this uh button here and then just say share with my team so once you do that and if you go back here now it tells stop sharing with my team right so I don't want to stop sharing so I'll go back to the queries tab here and now you see uh shared by me the query shows up and shared to my team this query shows up as well now if uh somebody else on my team wrote a query and they shared it with the team those queries will show up here which I can use to to do analysis on it all right now uh let's go into uh creating charts so uh another capability of query editor is you can actually create uh visualizations right from uh the query editor here so here uh I have a query where again I'm doing a total quantity sold by calendar date so as you can see I mean you have some data for individual days uh it's not very uh friendly to see it in a table format right it's easier to see it in a chart format so we'll go to this toggle button here and I'll create a chart and uh we will create a line chart and we'll change this to calendar date we'll change this to quantity sold and then we'll go into the transforms and I want to sort this data by uh calendar date because I want the calendar date to show up in an ascending order so if I clean this up you see that I have a data now from January through December and I have this line chart that is created and then you can choose different chart formats all right if you want a bar chart or a histogram or a funnel chart you have those options here and then this chart can be saved as well so we can save it call it my chart and save it uh you can also export it export this particular chart as a PNG or jpeg uh the saved chart will show up here under my charts okay uh another thing that you can do with query editor is a run parameterized queries so here I have uh two tables that I'm joining but the venue city is a parameter so when I run this query It'll ask me for uh a value so let's say I say Boston because I am in Boston I'll run the query and you'll see that uh this has been filtered for Boston right so you're kind of giving it a filter at runtime so let's change this let's just say Chicago and then run it again and now you have all of the data for Chicago okay uh now another way that you can run queries here is with a Notebook based experience so a new then the notebook interface enables the users to author queries more easily by organizing multiple SQL queries and annotations in a single document so uh to create a notebook you click on the plus button here uh and this interface that you see is the editor interface so we'll just click on Notebook and uh now I want to do some analysis as an analyst right so first I'll create a markdown cell and I'll say I'm doing a quantity sold by month analysis and then I will add a new cell which is a SQL cell and I'll run this run this particular query all right so uh we got the results back for for this particular query now uh as I look at the results I see that December and January sales are much lower than expected so as an analyst I want to dig deep into it to figure out what's going on right so I'm going to add another markdown cell here and say number of sales in December is lower than expected and we want to dive deep into it and this this markdown date markdown cell uh that's optional but you uh it's you add it so that when somebody else is looking at a notebook they know what what exactly you're doing so we'll add a SQL based cell here and here this particular query is just diving deep into the November and December sales right so we'll run this and just like we saw before uh this is better represented in a chart so we'll create a chart uh we'll make this as a calendar date we'll make this as quantity sold and we'll make this a bar chart and again we'll sort it by calendar date all right so now we have the data for November and December and we see a downward Trend right here right so this is useful information which our marketing team maybe can use to uh uh you know create some marketing campaigns to improve sales so you can annotate uh here and say that you know drop in sales start starting here or something like that and then this data this can be uh shared with with your a team or your Executives or a marketing team so for now what I'm going to do is you know this analysis is very useful so I'm going to go in and save a version of this notebook and I'm also going to share it with my team so now if I go into the notebooks tab you will see that my notebook is saved I could uh rename it to sales analysis uh and this has also been shared with my team and uh you know anybody else in my team can come in they can look at the version history so right now you only have one version and then they can create a brand new notebook from this version as well all right uh so with this I'm going to go back into the presentation uh right here so yeah so in this session we discussed how you can use Query Editor to quickly load data into a redshift table and quickly analyze it by running queries and creating charts and sharing them with your teammates uh I would you know suggest you try this out for yourself and see how redshift query editor can be leveraged for your use cases uh with that I would like to thank you for your time uh I have shared my LinkedIn profile here so please reach out if you would like to connect or if you have any questions thanks again and have a wonderful day everyone [Music] [Music] thank you