ClickHouse: создание таблицы

Как и в остальных СУБД (систем управления базами данных), в ClickHouse есть много функций для работы с таблицами. Но перед тем, как работать с ними, сначала нужно создать базу данных, в которой эти таблицы будут храниться (это не касается временных таблиц, о которых речь также пойдет ниже).

Для создания базы данных в ClickHouse используется команда CREATE DATABASE, которая создает новую БД в хранилище ClickHouse. Для выбора уже созданной БД используется команда USE. Выглядят эти команды так (имена БД даны просто для примера):

CREATE DATABASE my_new_database
USE my_old_database

А в панели Timeweb Cloud база данных ClickHouse создается буквально в пару кликов:

• Зарегистрируйтесь или залогиньтесь на сайте и перейдите на страницу Базы данных, после чего нажмите на кнопку Создать.

• На следующей странице выберите в пункте Тип базы данных ClickHouse, заполните нужные параметры ниже и нажмите на кнопку Заказать справа.
  

Теперь можно переходить к работе с таблицами.

Создание таблиц в ClickHouse

Для создания таблиц используется команда CREATE TABLE, после которой вводится имя таблицы, а затем идут параметры столбцов. Давайте сразу перейдем к практике, а попутно будем объяснять встречающиеся команды другого типа.

Пример №1

Начнем с примера создания таблицы для хранения данных о моделях какой-нибудь популярной автомобильной марки:

CREATE TABLE autobrand_models (
    model_id UInt32,
    model_name String,
    release_year UInt16,
    engine_type String,
    horsepower UInt16,
    price Decimal(10, 2)
) ENGINE = MergeTree()
ORDER BY model_id;

В этом примере создается таблица autobrand_models с полями: model_id (идентификатор модели), model_name (название модели), release_year (год выпуска), engine_type (тип двигателя), horsepower (мощность двигателя в л.с.) и price (цена). 

Поле model_id имеет тип UInt32, что подразумевает использование 32-битного целого числа (то есть без дополнительных знаков). Поле price имеет тип Decimal(10, 2), что означает использование десятичного числа с общим размером до 10 цифр и 2 цифрами после запятой. Поля model_name и engine_type принимают строковые значения (String), а release_year и horsepower — 16-битные целые значения (UInt16). Таким образом, данная таблица позволяет хранить информацию о моделях автомобилей определенного бренда, включая их идентификатор, название, год выпуска, тип двигателя, его мощность и цену авто.

Также обратите внимание на две нижние строчки:

• ENGINE = MergeTree() — это означает, что в данном случае используется движок MergeTree, который является одним из наиболее распространенных для хранения данных в ClickHouse. Используемый движок определяет, как и где хранятся данные, какие запросы поддерживаются, а также реплицируются ли данные. Для ClickHouse разработано множество движков, но для простых таблиц, как в примере выше, очевидным выбором будет именно MergeTree.

• ORDER BY model_id; — в этой строке указывается, что данные будут упорядочены по столбцу с идентификаторами моделей.

Пример №2

Теперь о том, как использовать первичные ключи. Их можно указать по-разному: внутри списка столбцов или вне его.

В первом случае первичные ключи прописываются так:

CREATE TABLE db.new_table
(
   name01 type01, name02 type02, ...,
   PRIMARY KEY(expr01[, expr02,...])
)

Если же их нужно указать за пределами таблицы, то это будет выглядеть так:

CREATE TABLE db.new_table
(
   name01 type01, name02 type02, ...
)
PRIMARY KEY(expr01[, expr02,...]);

Как видим, разница в коде заключается в положении закрывающей скобки: во втором случае первичные ключи вынесены за ее пределы, то есть за пределы самой таблицы.

Давайте приведем пример конкретного применения первичных ключей:

CREATE TABLE shop.sales_by_category
(
category_id UInt32,
product_name String,
sale_date Date,
quantity UInt16,
price Decimal(10, 2),
revenue Decimal(10, 2),
PRIMARY KEY(category_id, sale_date)
)
ENGINE = MergeTree()
ORDER BY (category_id, sale_date);

Здесь:

• category_id — id категории в формате 32-битного целого числа (UInt32);

• product_name — название продукта, строковое значение (String);

• sale_date — дата продажи в формате даты (Date);

• quantity — количество в формате 16-битного целого числа (UInt16);

• price — цена в десятичном формате с 2 знаками после запятой Decimal(10, 2);

• revenue — общий доход, также в формате Decimal(10, 2);

• PRIMARY KEY — первичные ключи, в данном случае они внутри списка и включают значения category_id и sale_date;

• ORDER BY (category_id, sale_date) — определяет порядок сортировки данных в таблице. Данные могут быть отсортированы по столбцам category_id и sale_date.

Мы познакомились с типичной структурой таблиц ClickHouse, а теперь перейдем к некоторым специфическим табличным функциям.

Добавление комментариев к таблицам

Это делается при помощи команды COMMENT. Приведем пример добавления комментария с его последующим выводом. Добавить комментарий можно, например, так:

CREATE TABLE countdown (x String) ENGINE = Memory COMMENT 'Временная таблица для отчета';

А вот команда для его вывода:

SELECT name, comment FROM system.tables WHERE name = 'countdown';

В результате ClickHouse выведет нам в табличном виде следующее:

name         | comment
-------------|------------------------------------------------
countdown    | Временная таблица для отчета

Временные таблицы в ClickHouse

В ClickHouse есть возможность создавать временные таблицы, которые актуальны только для текущего запроса и создаются вне базы данных. После завершения сеанса или закрытия соединения временная таблица автоматически удаляется. Чтобы создать временную таблицу в ClickHouse, используется команда CREATE TEMPORARY TABLE. Рассмотрим простейшую структуру такой таблицы:

CREATE TEMPORARY TABLE temp_table (
    id UInt32,
    name String
) ENGINE = Memory;

В данном примере создается временная таблица temp_table с полями id (идентификатор) и name (название). Тип движка таблицы указан как Memory, что означает, что данные будут храниться в оперативной памяти. Такая временная таблица полезна, когда нужно быстро сохранить и использовать данные в рамках текущего сеанса без необходимости их дальнейшего хранения.

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

CREATE TEMPORARY TABLE temp_sales_data AS
SELECT 
    products.product_id,
    products.product_name,
    sales.sale_date,
    sales.sale_amount
FROM products
JOIN sales ON products.product_id = sales.product_id
WHERE sales.sale_date BETWEEN '2024-01-01' AND '2024-01-31';

В этом примере мы создаем временную таблицу temp_sales_data, в которую помещаем данные о продуктах и продажах за январь 2024 года. Мы объединяем таблицы products и sales по идентификатору продукта и выбираем только те записи, у которых дата продажи входит в указанный период.

После создания временной таблицы temp_sales_data, мы можем выполнять различные аналитические запросы к этим данным, например, подсчитывать общую сумму продаж за месяц или находить самые популярные товары. Но обратите внимание, что временная таблица temp_sales_data будет автоматически удалена при закрытии сеанса подключения к базе данных. Это удобно для аналитики, так как у специалиста есть возможность быстро получить нужные данные для отчета, и при этом не требуется впоследствии удалять эту таблицу вручную, ведь иначе подобные отчеты засоряли бы базу данных. Добавим также, что если имя временной таблицы будет совпадать с именем другой таблицы без указания имени БД, в приоритете будет временная таблица.

Изменение структуры таблиц

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

Предположим, у нас есть таблица myBigData с данными, и мы хотим заменить все записи в этой таблице, где CounterID меньше определенного значения. В ClickHouse можно сделать это буквально одной строкой: 

REPLACE TABLE myBigData SELECT * FROM myBigData WHERE CounterID < 5356;

Этот запрос заменит все записи в таблице myBigData, удовлетворяющие условию CounterID < 5356, на результат выборки из этой же таблицы. Таким образом, благодаря команде REPLACE в ClickHouse можно эффективно обновлять данные в таблице на основе выборки из той же таблицы.

Итак, мы познакомились с обычными и временными таблицами в ClickHouse и узнали, как изменять данные в них и добавлять комментарии. А о продвинутых возможностях работы с таблицами ClickHouse смотрите другие наши статьи и документацию разработчиков.