Database Reference
In-Depth Information
TIMESTAMP("2013-12-13")
LIMIT 10
This query does the same date computation twice; moreover, if you want to
change it, you have to remember to keep it in sync in both places. A cleaner
way to write the query would be with a subquery:
SELECT pst
FROM (
SELECT DATE_ADD(ts, -8, "HOUR") AS pst
FROM [bigquery-e2e:ch10.sample_data])
WHERE pst > TIMESTAMP("2013-12-13")
LIMIT 10
This formulation of the query will likely run faster but also will be easier to
maintain.
Working around Quirks in the Query Language
There are, admittedly, a couple of cases where BigQuery doesn't allow
certain standard query formulations and requires you to revise your query
accordingly. Usually, a good error message is given that can help you rewrite
your query. For example,
JOIN
queries require the fields in the
ON
clause to
be the same type, so if you have an integer value and a string value, the join
will fail. For example, consider the following query:
SELECT t1.vInt
FROM (SELECT 17 AS vInt) AS t1
JOIN (SELECT "17" AS vString) AS t2
ON t1.vInt = t2.vString
If you run this, you'll get the error
Cannot compare fields as join
attributes (Incompatible types. 'vInt' : TYPE_INT32
'vString' : TYPE_STRING)
. This error message is a bit cryptic and
exposes internal names for data types, but it should suffice to let you figure
out that you need to coerce the values before comparing them. To fix the
query, you can use a subquery again:
SELECT t1.vString
FROM (
SELECT STRING(vInt) as vString
