Как использовать функцию TABLE_QUERY() в BigQuery?

Ответ 1

Функция TABLE_QUERY() позволяет вам написать предложение SQL WHERE, которое оценивается, чтобы найти, какие таблицы запускать запрос. Например, вы можете запустить следующий запрос для подсчета строк во всех таблицах набора данных publicdata:samples, которые старше 7 дней:

SELECT count(*)
FROM TABLE_QUERY(publicdata:samples,
    "MSEC_TO_TIMESTAMP(creation_time) < "
    + "DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY')")

Или вы можете запустить это для запроса по всем таблицам с git в имени (которые являются таблицами github_timeline и github_nested) и найти наиболее распространенные URL-адреса:

SELECT url, COUNT(*)
FROM TABLE_QUERY(publicdata:samples, "table_id CONTAINS 'git'")
GROUP EACH BY url
ORDER BY url DESC
LIMIT 100

Несмотря на то, что он очень мощный, TABLE_QUERY() может быть сложно использовать. Предложение WHERE должно быть указано как строка, которая может быть немного неудобной. Более того, это может быть трудно отлаживать, поскольку, когда есть проблема, вы получаете только ошибку "Ошибка оценки вспомогательного запроса", что не всегда полезно.

Как это работает:

TABLE_QUERY() выполняет два запроса. Когда вы запускаете TABLE_QUERY(<dataset>, <table_query>), BigQuery выполняет SELECT table_id FROM <dataset>.__TABLES_SUMMARY__ WHERE <table_query>, чтобы получить список идентификаторов таблиц для запуска запроса, затем он выполняет ваш фактический запрос по этим таблицам.

Часть этого __TABLES__ может выглядеть незнакомой. __TABLES_SUMMARY__ - это мета-таблица, содержащая информацию о таблицах в наборе данных. Вы можете использовать этот мета-стол самостоятельно. Например, запрос SELECT * FROM publicdata:samples.__TABLES_SUMMARY__ будет возвращать метаданные о таблицах в наборе данных publicdata:samples.

Доступные поля:

Поля метатеги __TABLES_SUMMARY__ (все доступные в запросе TABLE_QUERY) включают в себя:

• table_id: имя таблицы.
• creation_time: время, в миллисекундах с 01.01.1970 UTC, что таблица была создана. Это то же самое, что и поле creation_time в таблице.
• type: является ли это представлением (2) или обычной таблицей (1).

Следующие поля недоступны в TABLE_QUERY(), поскольку они являются членами __TABLES__, но не __TABLES_SUMMARY__. Они хранятся здесь для исторического интереса и частично документируют метатеги __TABLES__:

• last_modified_time: время, в миллисекундах с 01.01.1970 UTC, обновлена ​​таблица (либо метаданные, либо содержимое таблицы). Обратите внимание, что если вы используете записи tabledata.insertAll() для потоковой передачи в свою таблицу, это может быть несколько минут устаревшим.
• row_count: количество строк в таблице.
• size_bytes: общий размер в байтах таблицы.

Отладка

Чтобы отладить ваши запросы TABLE_QUERY(), вы можете сделать то же самое, что и BigQuery; то есть вы можете запускать метатежный запрос самостоятельно. Например:

SELECT * FROM publicdata:samples.__TABLES_SUMMARY__ 
WHERE MSEC_TO_TIMESTAMP(creation_time)  < 
   DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY')

позволяет не только отлаживать ваш запрос, но также видеть, какие таблицы будут возвращены при запуске функции TABLE_QUERY. После того как вы отлаживаете внутренний запрос, вы можете собрать его вместе со своим полным запросом по этим таблицам.

Ответ 2

Альтернативный ответ для тех, кто переходит к стандартному SQL:

• Стандарт BigQuery Standard SQL не поддерживает TABLE_QUERY, но поддерживает * расширение для имен таблиц.
• При расширении имен таблиц * вы можете использовать мета-столбец _TABLE_SUFFIX для сужения выделения.
• Расширение таблицы с помощью * работает только тогда, когда все таблицы имеют совместимые схемы.

Например, чтобы получить среднюю глобальную температуру NOAA GSOD в период между 2010 и 2014 годами:

#standardSQL
SELECT AVG(temp) avg_temp, _TABLE_SUFFIX y
FROM `bigquery-public-data.noaa.gsod_20*` #every year that starts with "20"
WHERE _TABLE_SUFFIX BETWEEN "10" AND "14" #only years between 2010 and 2014
GROUP BY y
ORDER BY y