Database Reference
In-Depth Information
# Run a query over the list of tables.
resp = bq.jobs().query(
projectId=PROJECT_ID,
body={'query':('SELECT kind, COUNT(day) FROM %s '
'GROUP BY 1' % tables)}
).execute()
We have listed this code for two reasons. First, it clarifies the semantics
of the function that will be introduced below and second, it illustrates the
boilerplate and extra operation you can avoid by using it. The function
that simulates this table listing behavior is
TABLE_QUERY
, and it has the
following signature:
(TABLE_QUERY(
<dataset>
,
"
<conditional expression>
")
It should appear in the
FROM
clause of a query just like you would use a
subselect expression. Just as with subselects it must appear in parentheses.
The conditional expression can be any expression that would be legal in the
WHERE
clause of a query over the dataset metatable. With this function the
script we wrote could be replaced with:
$
(INPUT="a"
bq query "SELECT kind, count(day) [count]
FROM (TABLE_QUERY(
ch11, 'LEFT(table_id, 2) =
\"${INPUT}_\"'))
GROUP BY 1")
+------+-------+
| kind | count |
+------+-------+
| a | 7 |
+------+-------+
The conditions in the
WHERE
clause of the first query in the script are passed
as a string to the
TABLE_QUERY
function. The function effectively simulates
the operations performed by the script, but more efficiently because it can
all happen within the service. Keep in mind that the limit on the total
number of tables referenced in a single query still applies. It is easiest to
