How to use bigquery standard SQL ? (instead of the legacy SQL called by google)


#1

I was using this kind of queries, it works well until I am researching some advanced functions I see available from the reference called Query Reference (Standard SQL), but this query is actually (the Legacy SQL) (called by Google doc), there are some function differences and difference on table reference as well, like this [httparchive:runs.latest_pages] is not recognized in the standard SQL mode, when running this code, if click Show Options, click “Uncheck Legacy SQL” then it reported some errors on table reference, [httparchive:runs.latest_pages] is invalid, I tried change to “httparchive:runs.latest_pages” in double quote, not working either

SELECT DOMAIN(pages.url), pages.url,
      pages.rank AS rank
FROM [httparchive:runs.latest_pages] pages
JOIN [httparchive:runs.latest_requests] requests
ON pages.pageid = requests.pageid 
WHERE rank IS NOT NULL AND ( ... );

https://cloud.google.com/bigquery/sql-reference/query-syntax
https://cloud.google.com/bigquery/query-reference (the Legacy SQL)


#2
SELECT * FROM  `httparchive.runs.2016_08_15_requests` LIMIT 10

The above should do the trick. Note that if you’re using the BigQuery web console, it often provides helpful feedback in the error section for likely problems with syntax, etc. E.g…


#3

thank you for the swift reply, I’ve also just found it the Known Issues and FAQ; and actually even that backquote is not needed, it just doesn’t recognize the colon, after change it to dot, code like this runs perfectly fine

FROM httparchive.runs.2016_09_01_pages AS pages
JOIN httparchive.runs.2016_09_01_requests AS requests
ON pages.pageid = requests.pageid
...

https://cloud.google.com/bigquery/sql-reference/known-issues-and-faq#syntax-error-unexpected-

But another question is the latest_pages latest_requests alieas seems not usable, I have to write in the actual date 09_01 to query the latest