Lab Review: Examining Billing Data with BigQuery
1. Lab Review: Examining Billing Data with BigQuery
In this lab, you imported billing data into BigQuery that had been exported as a CSV file. You first ran a simple query on that data. Next, you accessed a shared dataset containing more than 22,000 records of billing information, You then ran a variety of queries on that data to explore how you can use BigQuery to gain insight into your resources' billing consumption. If you use BigQuery on a regular basis, you'll start to develop your own queries for searching out where resources are being consumed in your application. You can also monitor changes in resource consumption over time. This kind of analysis is an input to capacity planning and can help you determine how to scale up your application to meet growth or scale down your application for efficiency. Welcome to the walk-through of the lab Examining Billing Data with BigQuery. At this point in the lab, I have logged in with the username and password that Qwiklabs has provided me from the lab. So the first task is to use BigQuery to import data. So what I did is as the Billing Administrator, I exported my billing data and put it in a bucket. So I am going to go into BigQuery and I'm going to import some stuff. So BigQuery? Yes. The Cloud Council. Thank you. Make sure that you are logged in to BigQuery and have the correct Qwiklabs project ID selected at the top. So I'm going to go here and I'm going to click Create dataset and I am going to call it imported billing data. My data location is in the US and I want it to expire one day afterwards. I'm going to hit Create dataset. You can see my dataset is created and I should see it right here. There it is. So now I'm going to create a table in that dataset. Table. For the source, I'm going to use Cloud Storage, I'm going to copy and paste the bucket location from the lab and it is in CSV format. For destination, I'm using that and native table and I am going to call it sampleinfotable. Under schema, I I'm going to hit auto detect so it detects a schema and input parameters from the dataset. I'm going to open up the advance and I am going to specify that I want to skip one row because that's the headers, and then I'm going to hit Create table. So this is a point where you can check the progress in your lab. If you click check my progress, it's going to check that you have dataset at a table and that you have imported that data into that table, and you should get five points for that. So Task 2, you are going to examine the data that you've just input. So I'm going to click on my table and it's going to, by default, show the schema. I can click Details and it's going to tell me a little bit more information about number of rows. You can see it has 44 rows, it's a pretty small table. I can hit Preview and it's going to show me a couple of the first rows of the table. So it now wants me in the lab. There are some formative questions that are going to ask you just to make sure that you're understanding the learning. So I'm not going to go over that in the walk-through because that is more and to make sure that you're understanding what you're doing. So I'm going to go to Task 3 where we're composing a simple query. A couple of cool things about BigQuery, if you are in a table by default, if you click Query table, it's going to auto-populate the query editor with the project dataset table for you and then you just specify what you want to look at. So I do select star and I'm doing this. Oops. Where cost is greater than zero. So I just want to see in this table how much of it. I only want to see the rows where the cost is more than zero. You can see right here it's validating that my SQL or my SQL is right. I'm going to hit Run. Here are my query results and you can see out of a table that has 44, there are 20 rows in this table that actually have costs that are more than zero. So again, there are a couple more questions that you can answer and you can also check your progress that you've run this query. So if you run the query, then it's going to give you another five points. At this point, you're actually done with the points that are awarded in the lab, but you still have another task to go into a little more complex query. So I'm going to go ahead and copy the query from Task 4, and I'm going to erase this and paste it in. It's a valid query here. I'm going to hit Run, then I'm going to verify that the result has what my lab is telling me is, supposed to return 22,537 lines of billing data. I can see right here, that is correct. Let's say I wanted to find the latest 100 records where the charges were greater than zero. So I'm going to copy paste the query that's provided to me, and make sure it's valid. Always good to check that your SQL is valid. Hit Run, and it is going to show me the last 100 records where charges were greater than zero. Let's say I wanted to find all of the charges that were more than $3, the next query shows you that. You can feel free to click through each one of these more complex queries and feel free to try out some queries of your own. If you wanted to just peruse the data and figure out maybe the last two days of billing anything that was over $10. Any kind of question that you might need to provide data for to your senior leadership about your billing of resource usage in GCP. After all of these complex queries, in review, you imported billing data that was technically exported for you from a billing admin into BigQuery, and then you run a simple query and then you ran some more complex queries. I hope you enjoyed the lab. Thank you.2. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.