bigquery

Позволяет выполнять запросы SELECT и INSERT к таблице в Google BigQuery, включая общедоступные датасеты. Структура таблицы автоматически определяется по схеме таблицы BigQuery.

Для чтения используется REST API BigQuery (tabledata.list), поэтому доступны только нативные таблицы (представления, materialized view и внешние таблицы не поддерживаются). Для записи используются потоковые вставки (tabledata.insertAll), для которых в проекте должен быть включен биллинг.

bigquery(project, dataset, table[, access_token][, key = value, ...])
bigquery(named_collection[, key = value, ...])

Аргументы project, dataset, table и access_token также можно задавать в форме ключ = значение; позиционные аргументы заполняют эти позиции в указанном порядке. Указание аргумента одновременно как позиционного и как ключа (или одного и того же ключа дважды) приводит к ошибке.

Следующие аргументы можно задавать в форме ключ = значение (или в качестве ключей именованной коллекции):

Необходимо указать ровно один метод аутентификации. BigQuery не поддерживает анонимный доступ, поэтому учетные данные требуются даже для общедоступных датасетов.

1. Токен доступа. Любой действительный токен доступа OAuth 2.0, например полученный с помощью gcloud auth print-access-token. Срок действия токенов быстро истекает (обычно через час), поэтому этот метод лучше всего подходит для интерактивного использования.
2. Ключ сервисного аккаунта (рекомендуется для серверов). Передайте содержимое файла ключа, созданного в Google Cloud IAM, в аргументе service_account_key. ClickHouse подписывает JWT этим ключом и обменивает его на токен доступа, автоматически обновляя последний.
3. Токен обновления. Передайте client_id, client_secret и refresh_token, например из файла ~/.config/gcloud/application_default_credentials.json, созданного после выполнения gcloud auth application-default login.

Храните учетные данные в именованной коллекции, чтобы не указывать их в каждом запросе. Постоянная таблица, созданная на основе именованной коллекции (с движком таблицы BigQuery или с помощью CREATE TABLE ... AS bigquery(...)), регистрируется как зависимость этой коллекции, поэтому DROP NAMED COLLECTION блокируется, пока существует таблица.

Примечания:

• DATETIME в BigQuery не имеет часового пояса; он сопоставляется с DateTime64(6, 'UTC'), чтобы отображаемое значение не зависело от часового пояса сервера.
• NULLABLE RECORD сопоставляется с Nullable(Tuple(...)), поэтому NULL для всей записи сохраняется как NULL, а не схлопывается в Tuple из значений по умолчанию. Массив со значением NULL (или пустой массив) становится пустым массивом, поскольку Array не может находиться внутри Nullable в ClickHouse. Массив BigQuery не может содержать элементы NULL (ARRAY<T> эквивалентен ARRAY<T NOT NULL>), поэтому тип элемента поля REPEATED не является Nullable (Array(T) или Array(Tuple(...)) для элемента RECORD); элемент NULL в ответе tabledata.list отклоняется как некорректные входные данные.
• Чтение и запись столбцов Nullable(Tuple(...)) через табличную функцию bigquery работают без дополнительных настроек. Для создания постоянной таблицы с движком BigQuery, содержащей такой столбец (независимо от того, определяется ли структура автоматически или объявляется явно), требуется настройка enable_nullable_tuple_type, как и для любого столбца Nullable(Tuple). При явном объявлении столбцов поле RECORD можно вместо этого объявить как обычный Tuple(...), чтобы избежать этой настройки, ценой приведения NULL всей записи к кортежу по умолчанию; единственное допустимое отличие от автоматически определённого типа — удалить Nullable, оборачивающий Tuple поля RECORD, и только для этой же записи: nullable нельзя перенести на другую запись — внутреннюю или внешнюю.
• GEOGRAPHY сопоставляется с Geometry. BigQuery передаёт значение GEOGRAPHY в виде текста WKT, который при чтении разбирается в соответствующую альтернативу Geometry (Variant из Point, MultiPoint, Ring, LineString, MultiLineString, Polygon и MultiPolygon), а при записи сериализуется обратно в WKT. Для GEOMETRYCOLLECTION и пустой геометрии (например, POINT EMPTY) нет соответствия в Geometry, поэтому чтение строки, содержащей такое значение, вызывает ошибку. Поскольку Variant может самостоятельно хранить NULL, поле GEOGRAPHY с NULLABLE сопоставляется с Geometry, а не с Nullable(Geometry), и NULL всё равно сохраняется при записи и последующем чтении.
• JSON сопоставляется с String, а не с типом данных JSON, поскольку тип JSON в ClickHouse принимает на верхнем уровне только объект ({...}), тогда как значение JSON в BigQuery может быть любым значением JSON — скаляром, массивом или null — поэтому таблицу, содержащую такие значения, нельзя было бы прочитать. Кроме того, JSON нельзя обернуть в Nullable, поэтому SQL NULL в столбце NULLABLE не сохранялся бы. Сопоставление с String не приводит к потере данных; объекты верхнего уровня можно преобразовать с помощью CAST(value AS JSON).
• Значения BIGNUMERIC, содержащие более 38 цифр в целой части, не помещаются в Decimal(76, 38) и вызывают ошибку.
• Значения TIMESTAMP и DATE вне диапазона DateTime64/Date32 (годы 1900–2299) не поддерживаются.
• Столбцы RANGE доступны только для чтения. tabledata.insertAll ожидает значение RANGE<T> в виде структурированного объекта {start, end}, который нельзя восстановить из сопоставления с String, поэтому вставка в столбец RANGE вызывает ошибку.
• Значения INT64 отправляются в tabledata.insertAll как десятичные строки, поскольку API разбирает числа JSON как числа двойной точности и иначе повреждал бы значения вне диапазона [-2^53 + 1, 2^53 - 1].

Прочитайте общедоступный датасет с помощью токена из gcloud:

SELECT word, sum(word_count) AS c
FROM bigquery('bigquery-public-data', 'samples', 'shakespeare', '<access token>')
GROUP BY word
ORDER BY c DESC
LIMIT 5;

Прочитайте закрытую таблицу с помощью файла ключа сервисного аккаунта:

SELECT count()
FROM bigquery('my-project', 'my_dataset', 'my_table',
              service_account_key = '{"type": "service_account", "private_key": "...", "client_email": "...", ...}');

Вставка данных (потоковая вставка; требуется включить биллинг):

INSERT INTO FUNCTION bigquery('my-project', 'my_dataset', 'my_table', '<access token>')
SELECT number AS id, toString(number) AS name FROM numbers(10);

Используйте именованную коллекцию:

<clickhouse>
    <named_collections>
        <my_bigquery>
            <project>my-project</project>
            <dataset>my_dataset</dataset>
            <service_account_key><![CDATA[{"type": "service_account", ...}]]></service_account_key>
        </my_bigquery>
    </named_collections>
</clickhouse>

SELECT * FROM bigquery(my_bigquery, table = 'my_table');

• Можно читать только собственные таблицы BigQuery. Для представлений и внешних таблиц требуется запуск задания запроса BigQuery, чего эта функция не делает.
• Столбцы RANGE можно читать (как String), но нельзя записывать в них: вставка в столбец RANGE вызывает ошибку.
• Значение GEOGRAPHY, представляющее собой GEOMETRYCOLLECTION или пустую геометрию, невозможно представить типом Geometry, поэтому чтение строки с таким значением вызывает ошибку. Запись значения Geometry, равного NULL, в обязательное поле GEOGRAPHY (REQUIRED) или в качестве элемента повторяемого поля GEOGRAPHY (REPEATED) отклоняется, поскольку BigQuery не допускает там NULL.
• Предикаты не проталкиваются: tabledata.list только перечисляет строки таблицы и не имеет параметра фильтрации (он принимает параметры пагинации, выбора столбцов и формата), а для фильтрации потребовался бы запуск задания запроса BigQuery, чего эта функция не делает. Поэтому условие WHERE применяется в ClickHouse после загрузки строк; используйте выбор столбцов, чтобы уменьшить объём передаваемых данных.
• LIMIT, напротив, уменьшает объём считываемых данных. Страницы запрашиваются по мере необходимости, при этом maxResults устанавливается в max_block_size, а после получения запросом достаточного количества строк новые страницы не запрашиваются. Для простого LIMIT n (без WHERE, GROUP BY, ORDER BY и при n меньше max_block_size) ClickHouse уменьшает max_block_size до n, поэтому выполняется ровно один запрос ровно на n строк; в противном случае чтение останавливается на первой границе страницы после лимита, превышая его менее чем на одну страницу.
• Чтение привязывается к схеме, видимой на этапе анализа запроса, путём передачи явного списка столбцов в tabledata.list. Для очень широкого чтения, при котором список столбцов превышает ограничение на длину URL запроса (например, SELECT * из таблицы с тысячами столбцов), запрос отклоняется, а не выполняется без такой привязки (чтение без привязки может быть нарушено одновременным изменением схемы); выберите меньше столбцов, чтобы список поместился. То же ограничение длины URL проверяется перед каждым постраничным запросом (каждая страница содержит непрозрачный pageToken), поэтому чтение, для которого последующие страницы не помещаются в это ограничение, отклоняется с той же ошибкой вместо сбоя в середине выполнения.
• Если таблица BigQuery изменена после чтения её схемы, запрос отклоняется вместо того, чтобы незаметно вернуть или записать несоответствующие данные: актуальная схема повторно запрашивается и сравнивается со схемой, использованной при анализе, непосредственно перед чтением, а также перед тем, как INSERT передаст первую строку. Оставшееся окно (изменение схемы между этой проверкой и следующими за ней запросами) нельзя устранить, поскольку схема и данные запрашиваются отдельными REST-запросами.
• Сравнение выполняется со снимком схемы, использованным при анализе запроса: он создаётся, когда табличная функция определяет свою структуру, или, для постоянной таблицы (таблицы с движком BigQuery либо таблицы, созданной с помощью CREATE TABLE ... AS bigquery(...), которая аналогично сохраняет свои столбцы), при первом чтении или записи после CREATE, ATTACH либо перезапуска сервера. В метаданных таблицы сохраняются сопоставленные столбцы ClickHouse, а не схема BigQuery, поэтому изменение схемы, выполненное, пока таблица была отсоединена (или сервер был выключен), принимается следующим запросом, а не отклоняется: объявленные столбцы по-прежнему проверяются по актуальной схеме, а строки декодируются с её помощью, поэтому изменение, сохраняющее сопоставленные типы ClickHouse (например, с STRING на BYTES), читается по правилам нового типа при том же типе столбца.
• Строки, записанные потоковыми вставками, попадают в потоковый буфер BigQuery и могут появиться в последующих чтениях не сразу.
• Большой INSERT отправляется в tabledata.insertAll пакетами: не более 500 строк в запросе; кроме того, данные разбиваются так, чтобы каждый запрос не превышал ограничение BigQuery на размер запроса в 10 МБ (одна строка, превышающая это ограничение, отклоняется с понятной ошибкой).
• Операции записи не являются атомарными, и отдельный запрос tabledata.insertAll может выполниться лишь частично: BigQuery может зафиксировать часть строк запроса, отклонив остальные с insertErrors. Запросы также фиксируются независимо друг от друга, поэтому более поздний батч может быть отклонён уже после принятия предыдущих. В обоих случаях запрос возвращает ошибку, но уже зафиксированные строки остаются в BigQuery. Чтобы ограничить дублирование, каждая строка отправляется со стабильным insertId, сформированным из идентификатора запроса и порядкового номера строки в потоке; BigQuery использует его для дедупликации по мере возможности в пределах окна потоковой вставки. query_id, превышающий ограничение BigQuery в 128 символов для insertId, хешируется в префикс фиксированной длины, который остаётся стабильным для данного query_id. Поскольку insertId зависит от порядкового номера, дедупликация надёжна только при повторном запуске, возвращающем строки в том же порядке: повторная попытка передачи батча всегда безопасна, а повторный запуск того же INSERT с тем же query_id обеспечивает дедупликацию только при передаче строк в том же порядке (например, при однопоточной вставке или при ином детерминированном порядке — задайте max_threads = 1 и max_insert_threads = 1 для параллельного INSERT ... SELECT, порядок фрагментов которого иначе может меняться между попытками).

• Движок таблицы BigQuery