Database Reference
In-Depth Information
$
bq query \
'SELECT table_id,
MSEC_TO_TIMESTAMP(last_modified_time)
FROM ch11.__TABLES
__
ORDER BY 2 DESC'
+--------------------+---------------------+
| table_id | f0_ |
+--------------------+---------------------+
| recovered | 2013-11-14 06:44:52 |
| time_lapse | 2013-11-13 22:27:04 |
| devices_multi_type | 2013-11-12 08:56:18 |
| devices | 2013-11-12 08:34:16 |
+--------------------+---------------------+
You can use this table to easily locate tables that meet some criteria, for
example, finding all tables that begin with the prefix “logs” that have been
modified in the last 4 hours.
Dynamic Table Lists
The dataset metatable can be directly queried to simplify administering
tables or supporting more efficient navigation of datasets in user interfaces.
However, when combined with a function that allows it to be used to
construct lists of tables in the
FROM
clause of a query, it becomes
substantially more powerful. The beginning of this chapter discussed that
the BigQuery data management and query pricing models encourage
partitioning tables. The ability to dynamically generate a list of tables in
a query simplifies recombining the partitions when queries need to span
a variable number of partitions based on the value of one or more query
parameters. To explore this feature, start with setting up a couple of tables:
$
DAY_LIST='20131108 20131109 20131110
20131111 20131112 20131113 20131114'
$
for day in ${DAY_LIST}; do
for kind in a b; do
echo $kind $day
bq query --destination_table=ch11.${kind}_${day}
\
