bigquery

Позволяет выполнять SELECT- и INSERT-запросы к таблице Google 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	Проект Google Cloud, которому принадлежит датасет. Для общедоступных датасетов это проект датасета, например bigquery-public-data.
dataset	Имя датасета.
table	Имя таблицы.
access_token	Токен доступа OAuth 2.0 (необязательный позиционный аргумент; см. Аутентификация).

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

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

Ключ	Описание
access_token	Токен доступа OAuth 2.0.
service_account_key	Содержимое файла ключа сервисного аккаунта Google в формате JSON.
client_id	Идентификатор клиента OAuth 2.0 (используется вместе с client_secret и refresh_token).
client_secret	Секрет клиента OAuth 2.0.
refresh_token	Токен обновления OAuth 2.0.
billing_project	Необязательный проект, для которого учитываются квоты и биллинг (отправляется в заголовке X-Goog-User-Project).
base_url	Конечная точка API; по умолчанию — https://bigquery.googleapis.com. Может быть изменена для тестов и эмуляторов.
token_url	Переопределение конечной точки OAuth-токенов для тестов и эмуляторов. По умолчанию используется token_uri ключа сервисного аккаунта или https://oauth2.googleapis.com/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 блокируется, пока существует таблица.

​

Сопоставление типов данных

Тип BigQuery	Тип ClickHouse
STRING	String
BYTES	String (необработанные байты)
INTEGER / INT64	Int64
FLOAT / FLOAT64	Float64
BOOLEAN / BOOL	Bool
TIMESTAMP	DateTime64(6, ‘UTC’)
DATE	Date32
TIME	Time64(6)
DATETIME	DateTime64(6, ‘UTC’)
NUMERIC / DECIMAL	Decimal(38, 9) или Decimal(P, S) при параметризации
BIGNUMERIC	Decimal(76, 38) или Decimal(P, S) при параметризации
GEOGRAPHY	Geometry (разбирается из WKT)
JSON	String
INTERVAL	String
RANGE	String (только для чтения)
RECORD / STRUCT	Tuple или Nullable(Tuple) в режиме NULLABLE
Режим REPEATED	Array с не-Nullable типом элементов (Array(Tuple(...)) для элемента RECORD), поскольку массив BigQuery не может содержать элементы NULL
Режим NULLABLE	Nullable (кроме GEOGRAPHY, тип Geometry которого может самостоятельно содержать NULL)

Примечания:

• 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