Выбор типов данных

Одной из ключевых причин высокой производительности запросов в ClickHouse является эффективное сжатие данных. Меньший объём данных на диске приводит к более быстрым запросам и вставкам за счёт минимизации накладных расходов на ввод-вывод. Колонко-ориентированная архитектура ClickHouse естественным образом располагает схожие данные рядом, что позволяет алгоритмам и кодекам сжатия значительно уменьшать размер данных. Чтобы максимально использовать преимущества сжатия, важно тщательно подбирать подходящие типы данных.

Эффективность сжатия в ClickHouse в основном зависит от трёх факторов: ключа сортировки, типов данных и кодеков, которые все задаются через схему таблицы. Выбор оптимальных типов данных даёт немедленные улучшения как по объёму хранения, так и по производительности запросов.

Некоторые простые рекомендации могут существенно улучшить схему:

• Используйте строгие типы: Всегда выбирайте корректный тип данных для столбцов. Числовые и «датные» поля должны использовать соответствующие числовые и типы даты/времени, а не универсальные типы String. Это обеспечивает корректную семантику для фильтрации и агрегаций.

• Избегайте nullable-столбцов: Nullable-столбцы добавляют накладные расходы за счёт поддержки отдельного столбца для отслеживания null-значений. Используйте Nullable только в том случае, если явно требуется различать пустые и null-состояния. В противном случае обычно достаточно значений по умолчанию или нулевых эквивалентов. Дополнительную информацию о том, почему этот тип следует избегать, если он не нужен, см. в разделе Избегайте nullable-столбцов.

• Минимизируйте точность чисел: Выбирайте числовые типы с минимальной шириной в битах, которые при этом покрывают ожидаемый диапазон данных. Например, предпочитайте UInt16 вместо Int32, если отрицательные значения не нужны и диапазон укладывается в 0–65535.

• Оптимизируйте точность дат и времени: Выбирайте наиболее грубый тип Date или DateTime, который удовлетворяет требованиям запросов. Используйте Date или Date32 для полей, содержащих только дату, и предпочитайте DateTime вместо DateTime64, если точность до миллисекунд или выше не является критически важной.

• Используйте LowCardinality и специализированные типы: Для столбцов с количеством уникальных значений менее примерно 10 000 используйте типы LowCardinality, чтобы значительно сократить объём хранения за счёт словарного кодирования. Аналогично, используйте FixedString только тогда, когда значения столбца представляют собой строго фиксированные по длине строки (например, коды стран или валют), а для столбцов с конечным набором возможных значений предпочитайте типы Enum, чтобы обеспечить эффективное хранение и встроенную проверку данных.

• Enum для проверки данных: Тип Enum можно использовать для эффективного кодирования перечислимых типов. Enum может занимать 8 или 16 бит в зависимости от количества уникальных значений, которые требуется хранить. Рассмотрите его использование, если вам нужна либо связанная с ним проверка при вставке (необъявленные значения будут отклонены), либо вы хотите выполнять запросы, использующие естественный порядок значений Enum, например представьте столбец обратной связи, содержащий пользовательские ответы Enum(':(' = 1, ':|' = 2, ':)' = 3).

Пример

ClickHouse предлагает встроенные инструменты для упрощения оптимизации типов. Например, автоматическое определение схемы (schema inference) может автоматически определить исходные типы. Рассмотрим набор данных Stack Overflow, общедоступный в формате Parquet. Выполнив простое автоматическое определение схемы с помощью команды DESCRIBE, можно получить исходную неоптимизированную схему.

Примечание

По умолчанию ClickHouse сопоставляет их с эквивалентными типами Nullable. Это предпочтительно, поскольку схема строится только на основе выборки строк.

DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/*.parquet')
SETTINGS describe_compact_output = 1

┌─name───────────────────────┬─type──────────────────────────────┐
│ Id                         │ Nullable(Int64)                   │
│ PostTypeId                 │ Nullable(Int64)                   │
│ AcceptedAnswerId           │ Nullable(Int64)                   │
│ CreationDate               │ Nullable(DateTime64(3, 'UTC'))    │
│ Score                      │ Nullable(Int64)                   │
│ ViewCount                  │ Nullable(Int64)                   │
│ Body                       │ Nullable(String)                  │
│ OwnerUserId                │ Nullable(Int64)                   │
│ OwnerDisplayName           │ Nullable(String)                  │
│ LastEditorUserId           │ Nullable(Int64)                   │
│ LastEditorDisplayName      │ Nullable(String)                  │
│ LastEditDate               │ Nullable(DateTime64(3, 'UTC'))    │
│ LastActivityDate           │ Nullable(DateTime64(3, 'UTC'))    │
│ Title                      │ Nullable(String)                  │
│ Tags                       │ Nullable(String)                  │
│ AnswerCount                │ Nullable(Int64)                   │
│ CommentCount               │ Nullable(Int64)                   │
│ FavoriteCount              │ Nullable(Int64)                   │
│ ContentLicense             │ Nullable(String)                  │
│ ParentId                   │ Nullable(String)                  │
│ CommunityOwnedDate         │ Nullable(DateTime64(3, 'UTC'))    │
│ ClosedDate                 │ Nullable(DateTime64(3, 'UTC'))    │
└────────────────────────────┴───────────────────────────────────┘

22 rows in set. Elapsed: 0.130 sec.

Примечание

Обратите внимание, что ниже мы используем шаблон glob *.parquet для чтения всех файлов в папке stackoverflow/parquet/posts.

Применяя наши простые правила, сформулированные ранее, к таблице posts, мы можем определить оптимальный тип для каждого столбца:

Столбец	Числовой тип	Мин., Макс.	Уникальные значения	Значения NULL	Комментарий	Оптимизированный тип
PostTypeId	Да	1, 8	8	Нет		Enum('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8)
AcceptedAnswerId	Да	0, 78285170	12282094	Да	Отличать NULL от значения 0	UInt32
CreationDate	Нет	2008-07-31 21:42:52.667000000, 2024-03-31 23:59:17.697000000	*	Нет	Миллисекундная точность не нужна, используйте DateTime	DateTime
Оценка	Да	-217, 34970	3236	Нет		Int32
ViewCount	Да	2, 13962748	170867	Нет		UInt32
Body	Нет	-	*	Нет		Строка
OwnerUserId	Да	-1, 4056915	6256237	Да		Int32
OwnerDisplayName	Нет	-	181251	Да	Рассматривать NULL как пустую строку	String
LastEditorUserId	Да	-1, 9999993	1104694	Да	0 — неиспользуемое значение, которое можно использовать для NULL	Int32
LastEditorDisplayName	Нет	*	70952	Да	Считайте Null пустой строкой. Тип LowCardinality протестирован, преимуществ не выявлено	String
LastEditDate	Нет	2008-08-01 13:24:35.051000000, 2024-04-06 21:01:22.697000000	-	Нет	Миллисекундная точность не нужна, используйте DateTime	DateTime
LastActivityDate	Нет	2008-08-01 12:19:17.417000000, 2024-04-06 21:01:22.697000000	*	Нет	Если точность до миллисекунд не требуется, используйте DateTime	DateTime
Title	Нет	-	*	Нет	Считать значение Null пустой строкой	String
Теги	Нет	-	*	Нет	Рассматривать Null как пустую строку	Строка
AnswerCount	Да	0, 518	216	Нет	Считать Null и 0 одинаковыми	UInt16
CommentCount	Да	0, 135	100	Нет	Считать Null и 0 равнозначными	UInt8
FavoriteCount	Да	0, 225	6	Да	Считать Null и 0 равнозначными	UInt8
ContentLicense	Нет	-	3	Нет	LowCardinality работает быстрее, чем FixedString	LowCardinality(String)
ParentId	Нет	*	20696028	Да	Рассматривать Null как пустую строку	String
CommunityOwnedDate	Нет	2008-08-12 04:59:35.017000000, 2024-04-01 05:36:41.380000000	-	Да	Для значений NULL используйте значение по умолчанию 1970-01-01. Миллисекундная точность не требуется, используйте тип DateTime.	DateTime
ClosedDate	Нет	2008-09-04 20:56:44, 2024-04-06 18:49:25.393000000	*	Да	Используйте по умолчанию значение 1970-01-01 для NULL-значений. Точность до миллисекунд не требуется, используйте DateTime.	DateTime

Совет

Определение типа для столбца зависит от понимания его числового диапазона и количества уникальных значений. Чтобы найти диапазон для всех столбцов и количество различных значений, пользователи могут использовать простой запрос SELECT * APPLY min, * APPLY max, * APPLY uniq FROM table FORMAT Vertical. Мы рекомендуем выполнять его на меньшем подмножестве данных, так как это может быть ресурсоёмкой операцией.

В результате получается следующая оптимизированная схема (с точки зрения типов):

CREATE TABLE posts
(
   Id Int32,
   PostTypeId Enum('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 
   'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
   AcceptedAnswerId UInt32,
   CreationDate DateTime,
   Score Int32,
   ViewCount UInt32,
   Body String,
   OwnerUserId Int32,
   OwnerDisplayName String,
   LastEditorUserId Int32,
   LastEditorDisplayName String,
   LastEditDate DateTime,
   LastActivityDate DateTime,
   Title String,
   Tags String,
   AnswerCount UInt16,
   CommentCount UInt8,
   FavoriteCount UInt8,
   ContentLicense LowCardinality(String),
   ParentId String,
   CommunityOwnedDate DateTime,
   ClosedDate DateTime
)
ENGINE = MergeTree
ORDER BY tuple()

Избегайте использования столбцов с типом Nullable

Nullable column (e.g. Nullable(String)) creates a separate column of UInt8 type. This additional column has to be processed every time a user works with a Nullable column. This leads to additional storage space used and almost always negatively affects performance.

To avoid Nullable columns, consider setting a default value for that column. For example, instead of:

CREATE TABLE default.sample
(
    `x` Int8,
    -- highlight-next-line
    `y` Nullable(Int8)
)
ENGINE = MergeTree
ORDER BY x

use

CREATE TABLE default.sample2
(
    `x` Int8,
    -- highlight-next-line
    `y` Int8 DEFAULT 0
)
ENGINE = MergeTree
ORDER BY x

Consider your use case; a default value may be inappropriate.