Once you start working with Google BigQuery for a while, you pick up some tips and tricks along the way, and you save yourself some time and some heartache. I've gotten together my list of say, top five or top 10 tricks to help you out along the way when you just start working with BigQuery. So, I would like to think of BigQuery itself as not just a tool, but the tool which is only as good as the data that powers a binder. The good news is, if you don't have your own dataset to play with, there's a whole host of BigQuery public datasets that you can access, including sample queries. One of the datasets we need for this course, the eCommerce status zone, is provided within that public dataset, but I just want to give you a general feel for where you can find other public datasets, so even outside this course, you can continue to experiment with practice queries and other new data should you see fit. So, here's the BigQuery Web UI. In the left hand side you see your qwiklabs project, if you're already logged in there. You see we have no datasets associated with our project. In future courses, we're going to show you how you can actually create your own dataset and store query results as tables. But you will also see these public datasets here, there's only a few. So, I'd like you to do, inside a Google browser, just type in Google BigQuery public datasets and click on the first result. Likely, it's going to take you to this page. This is the program, there's a special team at Google that manages access, and works with other different providers that have these great datasets like NOAA for the weather data, the NCAA for American basketball data, and a bunch of these other datasets you see here all over in the left and I'm quickly scrolling through. So, one of the things I like to do, is just take for example, the Chicago Taxi Trips dataset, click on that and you'll get a whole host of metadata about the data set, how to query the dataset, but what I'm most interested in to see is what data is included into the dataset, and can I run a sample query against it and what are some tips and tricks along the way for writing a sample query. So, here we've got taxi cab data from 2013 to present. Again this datasets are regularly maintained, and what I really like is, they'll provide you with some of the sample queries that you can work with, and it'll give you a nice refresher for how to do some of the common functions and SQL functions like, filtering or deparsing and extracting. So, here we're going to use the WEB UI for the majority of this course. So, you can see here is an example query, and what this does, as I slowly scroll back up, is it says, what are the maximum, minimum, and average taxi fares for rides that are lasting 10 minutes or more for Chicago taxi cab? So, I'm just going to copy and paste this entire query here, a faster way to do it, in the right hand corner, you just say click to copy. Go back to your BigQuery WEB UI and paste it in here. Now, before you run your very first query, and as you're working with a public dataset inside of the Web, what I want you to do is take a look at some of the common really useful UI elements of the BigQuery Web UI. As we're going to talk about a lot in this course, the validator, this green checker is going to be your best friend. It tells you whether or not your SQL was actually valid, and it's useful if you click on it. It'll tell you how much data you're actually going to be processing. The BigQuery, since you're not buying a hardware or the server space, you are actually just paying per use. You're charged for the amount of bytes that are actually processed, and you get five terabytes free per month, even for your own personal use on your own personal accounts. So, this takes us two gigabytes to run, and one of the critical things as at the time of this writing is, we're still defaulting to legacy SQL. So, in the options, click on "Show Options" and "Toggle off". Use legacy SQL. Click on "High options", don't worry about the rest of those options, we're going to be going over the rest of those as different parts of this course in the future courses in the specialization, and you'll see that you can then run the query. Now again, before you run it, and I promise you we're going to run it, a couple more different other things to note, is the #standardSQL at the top, if you forgot to uncheck legacy SQL or more likely you're sharing your query with somebody else and you can't be certain whether or not they've got that checkbox toggle on or off, you can specify this #standardSQL at the top which actually most comments are ignored by the SQL engine that processes it, except for this one, this is unique. If you have this, it enforces standard SQL which is just the latest and greatest dialect used by the query. So, a couple more things we want to do, I'm actually going to save this query before I run it. Taxi cabs. Keep it, it's meaningful for you. You have different levels that you can share it, you can share it to anyone else who has access to your project. Your project again is this entire string right here, not just BigQuery but all the other products and services that you're using, or you can actually share it with the public which is completely wide open. So, most of my queries are part of this course and will publicly be available, but for you, you might want to use private or project level. So, we'll just say project level for these. Then I'll actually give you a link that you can share it with others and it names your query. Good to name your queries. So, we're going to go ahead and execute this, see how fast it runs. It ran about 0.7 seconds, and the trick here is, I've actually ran this query before, so I'm kind of tricking you all, is it actually pulls from cash. So, cash is on-a-per-user basis. So, if you've ran the same query before down to the last piece of syntax, it'll actually pull it from the results that have been stored in temporary storage. So, here's the results and you'll see the minimum-maximum fares. Amazingly high maximum fares, so you may have some data quality issues or some very expensive cab rides. So, that goes into this fact that you shouldn't take your data at face value and total number of rides that you have. So, if you're wondering about- all right, where I've got my data results here, how do I quickly flip back to the data table. Now, one of the things that I like to do is my favorite hotkey or tip and trick is holding down the command or in the Windows keyboards, the Windows key and if you actually click on and once you hold that down, it'll highlight all the datasets that you're pulling it from and if you were to click on the BigQuery public data, your dataset right here, actually won't take you to the dataset yet and that's because it's not loaded here on the left sidebar. It's a little bit counter-intuitive but there's actually two projects, there's a public datasets and then there's this actual verbatim BigQuery public data. Now, how we get to the rest of the BigQuery public data that you saw on the website is we're actually going to access it via URL and then load that project into here. Again, public datasets a little bit counter-intuitive, is a separate dataset than BigQuery public data. So, I'm going to provide you this URL. Pretty self explanatory bigquery.cloud.google.com/dataset/ thet project and then the dataset within that project but it'll also load all the rest of the datasets that are there on the website. Now, it's again a good idea to have save that query before because once you actually access this, this will take you directly to that Chicago taxi trips table. Now, collapsing this you'll see that we have both public datasets, which is just a very small shortlist, and BigQuery public data with the hyphens and that is a much longer list. So, once you have this, you can play around these datasets to your heart's content in a separate tab. Have that public dataset documentation for experimentation. If you want to access the query that you ran before, if you go to compose query it's no longer there. Good thing that we saved it but even if we didn't save the query, you can go to the query history and you can find that here is the query that we ran and you can actually click on "Open query" or if you want your saved queries or project queries, in this particular case we saved it as a project, you can click on this query that we ran before. Edit query brings it all the way back, make sure you're not losing your great work here. Now, for the magic, I'm going to hold open this panel here. Now finally, if I hold down command or hold down option then I can actually click on the dataset and say you had a long query with 15 different datasets and you want to know what available fields you have, you can click over here and immediately this schema for that particular table pops up. Super useful, so you can switch between the results panel and the data schema. For tables and other useful tip if you want to include different fields and you are worried about mistyping things, which is one of the most common SQL errors, you can actually click on each of these fields in the schema and you notice without any typing, it's adding them comma-separated to the create Window up top. Couple more brief things and we'll close out. If your query is getting ugly, you have a long horizontal scroll bar that immediately makes SQL gurus wins. One of the things that you can do is, click on the format query and that'll bring all of your commas and all of your columns that you've selected into a nice and neat order here. So, couple of brief things to review. Accessing a data set directly. You can do it through the URL, normally that's provided to you by the person that you're accessing the data set from. Also you can just hunt for that data online in this BigQuery public datasets view and you can click to open the query in the Web UI and then run it from there. So, there's a couple different ways to access that public data. Now, in addition to the BigQuery public datasets that you see here, we have a special project called dated insights associated with this particular course. This is the URL, I'll provide this to you as well. In there and actually loads up a dated insights project, so now we have four different projects on the left-hand side. You've got your own Qwiklabs sandbox and we'll show you again in future course how to create your own datasets, here you've got the create public data which is where the taxi cab data came from, the public datasets, which is that very much smaller list and then you've got your course dataset which is data-to-insights. For this particular course, in specialization, we're going to be focusing almost exclusively on revenue transactions. So, in advance of your other labs, if you just want to take a look at this table, and see what ecommerce data might be interesting to you, you can click on Query table. Again, making sure to disable legacy SQL if that's turned on, and you'll notice that if you query that table with legacy SQL, it'll actually have your table inside of legacy SQL format with brackets instead of back ticks. So, if you click query table again it'll automatically change those to the proper syntax for back ticks there. Now, while you can do select star from that table limit of a thousand to get a preview of that data. There is actually a preview button next to the schema. Not the details, click on preview, you can actually see what a lot of that data looks like immediately without running any SQL. For additional information, you can actually hover over the column of the dataset that's returned, and you can get the data type. Of course, you can also find that within the schema that you see here. So, lots of different ways within the Web UI to explore all the different values that are part of your data table, and get some familiarity around what the SQL syntax is, whether or not it's valid or not using the query validator, and toggling between your results in the data table that you're pulling these pieces of data from. So, you are going to learn more practice troubleshooting a lot of these query errors and working with how to create your new datasets throughout the rest of this specialization, so let's continue on with the course.
