0:00
Your best friend in all of this,
you can see me or maybe you can see on your own screen there, is the validator.
This red exclamation mark is both your best friend and your worst enemy.
If you're spending hours late at night trying to get
a broken query to fix, then it's honestly,
it's wonderful to get from one error to
the next error because that means you're
moving on up towards actually running a successful query.
So, me in the query validator and you in
the query validator are going to be best friends by the end of this lab.
So, the query validator, you can actually open it up.
So, even before you run a query,
you can see if you have syntax problems.
So, right now it's telling me the error message,
"Unexpected FROM," that's because after the select statement,
I have nothing that I'm actually selecting.
So, if I want to select to answer the question,
how many visitors reached the checkout confirmation page?
So, I'm going to copy in the questionnaire.
I like inundating my code with comments.
I'm going to bring back standard SQL here in
case for whatever reason I'm sharing this with somebody else in the future.
I want to make sure that it's executing standard SQL mode.
So, I've got an error, "Unexpected FROM," it's because I'm not selecting any columns.
So, let's take a look at the columns I want to bring in.
Let's bring them indirectly from our schema.
So, I've got how many visitors?
So, let's use FullVisitorId.
Immediately, you see as soon as we added a column there,
the query validator says,
"Great, this is a valid query."
So, as soon as you see green,
you get that little endorphin rush and basically say, "Great, it's a valid query."
That means, just because it's valid doesn't mean
it's returning what you want it to return.
So, that's on you. Just because a query can
execute doesn't mean it's going to return you the results that you want.
Another great thing that we're going to cover more so in the pricing aspect of
this is the amount of data that's actually being processed behind the scenes.
So, big query will give you a couple of
terabytes free to process even in your own personal accounts.
I think, currently, right now,
the time of this recording, it's five terabytes per month.
Right now, we've got a very small fraction like a one megabyte to run.
So, when we get to the pricing section later,
you're going to see that that's a great way to see how much
a query is going to cost based on the amount of data that's going to process.
But right now, we're focused on errors.
So, we got a fullVisitorId,
and what other field do you think we need to get to see
whether or not the page is the Checkout Confirmation?
So, let's do hits_page_page.
I honestly use the find,
Ctrl + F, in the browser if you're searching through a schema.
That's pretty fast, and you can just look for a particular keywords.
I'm going to bring that in and close the finder.
Here's a really interesting part.
So, this is a valid query.
I'm pulling the visitor ID and I'm pulling in the page title.
I'm going to execute this,
and you get a very interesting result.
So, I selected two columns and in the results panel here,
I only have one column.
Why do you think that is? That's really weird.
So, I've got page titles out of these numeric values,
and the page title here is apparently a string. Very strange.
So, this is a common SQL error especially when you're writing SQL very, very fast.
One of the things that I like to do to help me
mentally see what's going on is I'll format
the query first to bring all the different SQL clauses like select from,
limit on new lines,
and it'll help make things a little bit easier.
Because when you format a query,
it will generally break apart multiple different columns onto different new lines,
and that is right where our error is.
So, if you notice and you're probably already looking ahead,
is these are visitor IDs but this is the page title column.
So, what will be done is so we'd actually unintentionally aliased
the fullVisitorId column here with this string value hits page.
This is exactly the same thing as doing this.
The AS keyword is actually optional inside of SQL.
So, we're running this, you're looking at this, well,
how on earth would you do full visitor ID in alias that doesn't other comments?
Incredibly confusing, and it's a common error.
Just because the query validator doesn't pick it up,
it'll allow you to rename this column as another column,
even though that's a hyper confusing.
So, what's the easiest way to solve this?
Easiest way to solve this is break apart multiple different columns.
How you do that? Of course, again, is with the comma.
So, the number one most common error that I see,
especially in my own SQL and other SQL of my colleagues that I'm reviewing,
is making sure that all of your fields have commas separating them in the select clause.
So, now, when we actually run this,
you'll see as we expected,
you have a fullVisitorId and the page title of the confirmation that they've received.
Another common SQL error that you get,
and you'll see it's a great debate online.
Fully Alpha of the developer relations team here,
did an amazing query to see across all GitHub code do users
frequently end their columns with a comma or start their queries,
their next column with a comma,
and that is just personal preference.
So, generally, what I like to do is maybe I'll started just what is very,
very apparent, where the commas are, where the commas aren't.
But I'll put the link to his article,
and it's actually a pretty fun read because it
queries the public GitHub database, as well.
So, you can share that with your friends.
Google Cloud Training
