Руководство по переводу SQL из Amazon Redshift в ClickHouse

Справочник по переводу SQL из Amazon Redshift в ClickHouse

​

Типы данных

Пользователи, переносящие данные между ClickHouse и Redshift, сразу заметят, что ClickHouse предлагает более широкий набор типов, причем они менее ограничены. Если в Redshift нужно указывать возможную длину строк, даже переменную, то в ClickHouse этого ограничения и лишней нагрузки для пользователя нет: строки хранятся как байты без кодирования. Поэтому тип String в ClickHouse не имеет ограничений и не требует указания длины.

Кроме того, вы можете использовать типы Array, Tuple и Enum, которых нет в Redshift как полноценных сущностей (хотя Arrays/Structs можно имитировать с помощью SUPER). Это одно из частых неудобств для пользователей. ClickHouse также позволяет сохранять состояния агрегации — как во время выполнения запроса, так и даже в таблице. Это дает возможность заранее агрегировать данные, обычно с помощью materialized view, и может существенно улучшить производительность часто выполняемых запросов.

Ниже мы сопоставим эквивалентный тип ClickHouse для каждого типа Redshift:

Redshift	ClickHouse
SMALLINT	Int8 *
INTEGER	Int32 *
BIGINT	Int64 *
DECIMAL	UInt128, UInt256, Int128, Int256, Decimal(P, S), Decimal32(S), Decimal64(S), Decimal128(S), Decimal256(S) - (возможны высокая точность и широкий диапазон значений)
REAL	Float32
DOUBLE PRECISION	Float64
BOOLEAN	Bool
CHAR	String, FixedString
VARCHAR **	String
DATE	Date32
TIMESTAMP	DateTime, DateTime64
TIMESTAMPTZ	DateTime, DateTime64
GEOMETRY	гео-типы данных
GEOGRAPHY	Гео-типы данных (развиты меньше, например без систем координат — это можно эмулировать с помощью функций)
HLLSKETCH	AggregateFunction(uniqHLL12, X)
SUPER	Tuple, Nested, Array, JSON, Map
TIME	DateTime, DateTime64
TIMETZ	DateTime, DateTime64
VARBYTE **	String в сочетании с функциями Bit и Encoding

_{* ClickHouse также поддерживает беззнаковые целые числа с более широкими диапазонами, а именно UInt8, UInt32, UInt32 и UInt64.
**Тип String в ClickHouse по умолчанию не имеет ограничений, но его длину можно ограничить с помощью ограничений.}

​

Синтаксис DDL

​

Ключи сортировки

И в ClickHouse, и в Redshift есть понятие «ключ сортировки», которое определяет, как данные упорядочиваются при хранении. В Redshift ключ сортировки задаётся с помощью выражения SORTKEY:

CREATE TABLE some_table(...) SORTKEY (column1, column2)

Для сравнения, в ClickHouse для задания порядка сортировки используется предложение ORDER BY:

CREATE TABLE some_table(...) ENGINE = MergeTree ORDER BY (column1, column2)

В большинстве случаев в ClickHouse можно использовать те же столбцы ключа сортировки и тот же порядок, что и в Redshift, если используется тип COMPOUND по умолчанию. Когда данные добавляются в Redshift, следует выполнять команды VACUUM и ANALYZE, чтобы повторно отсортировать недавно добавленные данные и обновить статистику для планировщика запросов — иначе объём неотсортированных данных растёт. Для ClickHouse такой процесс не требуется.

Redshift поддерживает пару удобных возможностей, связанных с ключами сортировки. Первая — это автоматические ключи сортировки (с использованием SORTKEY AUTO). Хотя это может быть уместно на начальном этапе, явные ключи сортировки обеспечивают наилучшую производительность и эффективность хранения, если ключ сортировки выбран оптимально. Вторая — это ключ сортировки INTERLEAVED, который придаёт одинаковый вес подмножеству столбцов в ключе сортировки, чтобы повысить производительность, когда запрос использует один или несколько вторичных столбцов сортировки. ClickHouse поддерживает явные проекции, которые позволяют добиться того же результата при немного иной настройке.

Следует учитывать, что понятие «первичный ключ» в ClickHouse и Redshift означает разные вещи. В Redshift первичный ключ похож на традиционное понятие в СУБД и предназначен для обеспечения ограничений. Однако в Redshift они не применяются строго, а вместо этого служат подсказками для планировщика запросов и распределения данных между узлами. В ClickHouse первичный ключ обозначает столбцы, используемые для построения разреженного первичного индекса, который обеспечивает упорядоченность данных на диске, максимальное сжатие, а также помогает избежать засорения первичного индекса и неэффективного расходования памяти.