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

Страница о том, как выбирать типы данных в ClickHouse

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

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

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

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

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

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

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

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

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

​

Пример

ClickHouse предлагает встроенные инструменты, упрощающие оптимизацию типов. Например, определение схемы может автоматически выявить исходные типы. Рассмотрим набор данных 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 pattern *.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
Score	Да	-217, 34970	3236	Нет		Int32
ViewCount	Да	2, 13962748	170867	Нет		UInt32
Body	Нет	-	*	Нет		String
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
Tags	Нет	-	*	Нет	Считать Null пустой строкой	String
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	*	Да	Для NULL-значений рассмотрите значение по умолчанию 1970-01-01. Точность до миллисекунд не требуется, используйте 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 столбец (например, Nullable(String)) создаёт отдельный столбец типа UInt8. Этот дополнительный столбец приходится обрабатывать каждый раз при работе со столбцом Nullable. Это требует дополнительного места в хранилище и почти всегда отрицательно сказывается на производительности.

Чтобы избежать столбцов Nullable, задайте для этого столбца значение по умолчанию. Например, вместо:

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

используйте

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

Учитывайте свой сценарий использования; значение по умолчанию может быть неподходящим.