Руководство по переводу SQL Snowflake

Типы данных

Числовые типы

Пользователи, перемещающие данные между ClickHouse и Snowflake, сразу заметят, что ClickHouse предлагает более детализированную точность при объявлении числовых типов. Например, Snowflake предлагает тип Number для чисел. Это требует от пользователя указания точности (общего количества цифр) и масштаба (цифры справа от запятой) до общего количества 38. Целочисленные объявления синонимичны Number и просто определяют фиксированную точность и масштаб, где диапазон остается тем же. Это удобно, так как изменение точности (масштаб равен 0 для целых чисел) не влияет на размер данных на диске в Snowflake - минимально необходимые байты используются для числового диапазона во время записи на уровне микро-раздела. Однако масштаб влияет на пространство для хранения и компенсируется сжатием. Тип Float64 предлагает широкий диапазон значений с потерей точности.

В отличие от этого, ClickHouse предлагает несколько знаковых и незнаковых точностей как для чисел с плавающей запятой, так и для целых чисел. С их помощью пользователи ClickHouse могут быть четкими в отношении необходимой точности для целых чисел, чтобы оптимизировать использование памяти и дискового пространства. Тип Decimal, эквивалентный типу Number в Snowflake, также предлагает в два раза больше точности и масштаба — до 76 цифр. В дополнение к аналогичному значению Float64, ClickHouse также предоставляет Float32, когда точность менее критична, а сжатие имеет первостепенное значение.

Строки

ClickHouse и Snowflake используют различные подходы к хранению строковых данных. VARCHAR в Snowflake хранит символы Unicode в UTF-8, позволяя пользователю указать максимальную длину. Эта длина не влияет на хранение или производительность, минимальное количество байтов всегда используется для хранения строки, и предоставляет только ограничения, полезные для инструментов нижнего уровня. Другие типы, такие как Text и NChar, являются просто псевдонимами для этого типа. В свою очередь, ClickHouse хранит все строковые данные как необработанные байты с помощью типа String (без необходимости указания длины), передавая кодирование пользователю, с доступными функциями времени запроса для различных кодировок. Мы рекомендуем читателю ознакомится с "Неявным аргументом данных" для понимания мотивации. Таким образом, String в ClickHouse больше сопоставим с типом Binary в Snowflake. Как Snowflake, так и ClickHouse поддерживают "коллацию", позволяя пользователям переопределять, как строки сортируются и сравниваются.

Полуструктурированные типы

Snowflake поддерживает типы VARIANT, OBJECT и ARRAY для полуструктурированных данных.

ClickHouse предлагает эквиваленты Variant, Object (теперь устаревший в пользу нативного типа JSON) и Array. Кроме того, ClickHouse имеет тип JSON, который заменяет теперь устаревший тип Object('json') и особенно эффективен по хранилищу в сравнении с другими нативными типами JSON.

ClickHouse также поддерживает именованные Tuples и массивы кортежей через тип Nested, позволяя пользователям явно отображать вложенные структуры. Это позволяет применять кодеки и оптимизации типов на всем протяжении иерархии, в отличие от Snowflake, который требует, чтобы пользователь использовал типы OBJECT, VARIANT и ARRAY для внешнего объекта и не позволяет явным внутренним типам. Этот внутренний тип также упрощает запросы на вложенные числовые значения в ClickHouse, которые не нуждаются в приведении типов и могут использоваться в определениях индексов.

В ClickHouse кодеки и оптимизированные типы также могут применяться к подструктурам. Это предоставляет дополнительное преимущество в том, что сжатие с вложенными структурами остается отличным и сопоставимым с развернутыми данными. В отличие от этого, из-за необходимости применения конкретных типов к подструктурам, Snowflake рекомендует разворачивать данные для достижения оптимального сжатия. Snowflake также налагает ограничения на размер для этих типов данных.

Справочник типов

Snowflake	ClickHouse	Примечание
NUMBER	Decimal	ClickHouse поддерживает в два раза большую точность и масштаб, чем Snowflake - 76 цифр против 38.
FLOAT, FLOAT4, FLOAT8	Float32, Float64	Все числа с плавающей запятой в Snowflake имеют размер 64 бита.
VARCHAR	String
BINARY	String
BOOLEAN	Bool
DATE	Date, Date32	DATE в Snowflake предлагает более широкий диапазон дат, чем ClickHouse, например мин. для Date32 — 1900-01-01, а для Date — 1970-01-01. Date в ClickHouse обеспечивает более экономичное (двухбайтовое) хранилище.
TIME(N)	Нет прямого аналога, но может быть представлен через DateTime и DateTime64(N).	DateTime64 использует те же концепции точности.
TIMESTAMP - TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ	DateTime и DateTime64	DateTime и DateTime64 могут дополнительно иметь параметр TZ, определенный для колонки. Если он не указан, используется временная зона сервера. Кроме того, доступен параметр --use_client_time_zone для клиента.
VARIANT	JSON, Tuple, Nested	Тип JSON является экспериментальным в ClickHouse. Этот тип выводит типы колонок во время вставки. Также могут использоваться Tuple, Nested и Array для создания явно типизированных структур в качестве альтернативы.
OBJECT	Tuple, Map, JSON	Оба OBJECT и Map аналогичны типу JSON в ClickHouse, где ключи имеют тип String. ClickHouse требует, чтобы значение было согласованным и строго типизированным, тогда как Snowflake использует VARIANT. Это означает, что значения разных ключей могут быть разного типа. Если это необходимо в ClickHouse, явно определите иерархию с помощью Tuple или полагайтесь на тип JSON.
ARRAY	Array, Nested	ARRAY в Snowflake использует VARIANT для элементов - супертип. Напротив, эти элементы имеют строгую типизацию в ClickHouse.
GEOGRAPHY	Point, Ring, Polygon, MultiPolygon	Snowflake налагает систему координат (WGS 84), в то время как ClickHouse применяет это на этапе выполнения запроса.
GEOMETRY	Point, Ring, Polygon, MultiPolygon

Тип ClickHouse	Описание
IPv4 и IPv6	Специфические типы для IP, позволяющие потенциально более эффективное хранение, чем в Snowflake.
FixedString	Позволяет использовать фиксированную длину байтов, что полезно для хэширования.
LowCardinality	Позволяет любому типу использовать кодирование словарей. Полезно, когда ожидается кардинальность < 100k.
Enum	Позволяет эффективно кодировать именованные значения в диапазонах 8 или 16 бит.
UUID	Для эффективного хранения UUID.
Array(Float32)	Векторы могут быть представлены как массив Float32 с поддерживаемыми функциями расстояния.

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