Доступ к ClickHouse с помощью JDBC

Привет Хабр! Не так давно я имел удовольствие посетить встречу PyData Moscow на площадке Яндекса. Я не могу назвать себя python разрабочиком, но имею интересы в области аналитики и анализа данных. Посетив данное мероприятие, я узнал о существовании СУБД ClickHouse, разработанной в Яндексе и выложенной на GitHub под открытой лицензией. Колоночная SQL СУБД с отечественными корнями пробудила во мне интерес. В этой статье я поделюсь опытом установки и настройки ClickHouse, а также попыткой доступа к ней из Spring приложения с помощью Hibernate.

Сборка и установка

Знакомство с СУБД я начал со страницы документации.

Весьма удивили рекомендации по использованию оперативной памяти. Разработки советуют использовать столько же памяти, сколько у вас данных (во всяком случае пока объем данных меньше 200 Гб). Не совсем ясно, идет ли речь о суммарной памяти всех машин в кластере или о памяти каждой машины. В любом случае, я смог довольствоваться лишь 8 гб памяти своей виртуальной машины. При этом размер тестового набора данных составлял больше 60 ГБ в несжатом виде.

Первая сложность, с которой мне пришлось стокнуться, – это рекомнедация использовать Ubuntu и остуствие rpm пакетов (UPD: позже я обнаружил вот этот репозиторий). Я предпочитаю rpm системы и принял решения собрать СУБД из исходных кодов под Centos 7.

Ребята из Яндекса приготовили нам инструкции для сборки. Здесь есть несколько любопытных моментов:

• Нам понадобиться gcc аж 6 версии, когда в centos из коробки идет только 4.8. Любопытно, с чем связано это ограничение? Я не тратил время на изучение кода clickHouse, возможно используется новый стандарт C++, который не поддерживается в 4ой версии. Так или иначе, GCC 6 пришлось тоже собирать из исходных кодов. В инструкции по сборке clickhouse описан процесс сборки gcc, однако он опять-таки применим прежде всего к Ubuntu. Помогла следующая статья. Сборка компилятора заняла у меня 1.5 часа.
  
  
• Второй интересной особенностью сборки оказалось зависимость clickhouse от libmysqlclient. Интересно, как и зачем в clikchouse используется функции клиента MySQL?

Сборка самой СУБД заняла порядка 40 минут. Настройка clickhouse в моем случае заключалась в правке конфигурационного /usr/local/etc/clickhouse-server/config.xml, так как я решил хранить базу данных на отдельном разделе. Запусить сервер я смог с помощью sudo /usr/local/bin/clickhouse-server --config /usr/local/etc/clickhouse-server/config.xml

Проверить работу СУБД я решил на пример из этого поста, загрузив данные об авиаперелётах в США с 1987 по 2015 год. Процесс загрузки и примеры запросов приведены выше. Загрузка у меня заняла 24 минуты, размер базы данных на диске составил 14 Гб при объеме загруженных данных в 61.6 Гб.
Время выполнения тестовых запросов из статьи выше на моей машине составило:

Запрос	Время, с
какие направления были самыми популярными в 2015 году	2.067
из каких городов отправляется больше рейсов	3.744
из каких городов можно улететь по максимальному количеству направлений	7.012
как зависит задержка вылета рейсов от дня недели	3.195
из каких городов, самолёты чаще задерживаются с вылетом более чем на час	3.392
какие наиболее длинные рейсы	12.466
распределение времени задержки прилёта, по авиакомпаниям	4.596
какие авиакомпании прекратили перелёты	1.653
в какие города стали больше летать в 2015 году	0.380
перелёты в какие города больше зависят от сезонности	8.806

Еще раз отмечу, что объем памяти моей машины составлял всего 8 ГБ, что почти в 8 раз меньше, чем рекомендуется разработчиками. В связи с этим цифры выше – не более чем очень грубая прикидка того, как clickhouse будет себя вести на ноутбуке разработчика, нежели в production. В целом СУБД работала стабильно и, на мой взгляд, достаточно быстро. В анализируемой таблице было 166 миллионов записей.

Дружим ClickHouse и Java

Следующий шаг в моем изучении clickhouse – попытка доступа к нему из Java. Ребята из Яндекса выложили jdbc драйвер.

Подключить его к maven проекту очень просто:

        <dependency>
            <groupId>ru.yandex.clickhouse</groupId>
            <artifactId>clickhouse-jdbc</artifactId>
            <version>${clickhouse-jdbc-version}</version>
        </dependency>

При использовании есть всего несколько моментов, которые по незнанию могут вызвать сложности. Прежде всего, clikchouse поддерживает несколько протоколов. По-умолчанию родной бинарный протокол использует порт 9000, а http протокол – 8123. JDBC драйвер умеет работать только с http, поэтому указываем порт 8123. Название БД по-умолчиню – default. Имя пользователя и пароль по-умлочанию задавать не нужно. Чтобы clickhouse разрешал подключения с удаленных машин, в конфигурационном файле следует добавить строку <listen_host>::</listen_host>.

Далее все как в обычном JDBC:

   private static final String DB_URL = "jdbc:clickhouse://localhost:8123/default";

    private final Connection conn;

    /**
     * Creates new instance
     * @throws SQLException in case of connection issue
     */
    public ClickHouseJDBCDemo() throws SQLException {
        conn = DriverManager.getConnection(DB_URL);
    }

    /**
     * Queries db to get most popular flight route for ths given year
     * @param year year to query
     * @throws SQLException in case of query issue
     */
    public void popularYearRoutes(int year) throws SQLException {
        String query = "SELECT " +
                "    OriginCityName, " +
                "    DestCityName, " +
                "    count(*) AS flights, " +
                "    bar(flights, 0, 20000, 40) AS bar " +
                "FROM ontime WHERE Year = ? GROUP BY OriginCityName, DestCityName ORDER BY flights DESC LIMIT 20";
        long time = System.currentTimeMillis();
        try (PreparedStatement statement = conn.prepareStatement(query)) {
            statement.setInt(1, year);
            try (ResultSet rs = statement.executeQuery()) {
                Util.printRs(rs);
            }
        }
        System.out.println("Time: " + (System.currentTimeMillis() - time) +" ms");
    }

Как видим, ничего необычного. Полный код примера доступен здесь.

А что с Hibernate?

Следующим моим шагом стала попытка подключить ORM. Наивно полагая, что раз есть JDBC, то будет и ORM, я потянулся к Spring Data JPA. Но, к сожалению, я столкнулся здесь с рядом проблем. Прежде всего, при подключении к БД необходимо указать SQL Dialect. Реализованного диалекта для ClickHouse мне найти не удалось.

В результате я просто создал потомок стандартного org.hibernate.dialect.Dialect:

public class ClickHouseDialect extends Dialect {
    
}

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

SELECT alias.column from tablename alias

для clickHouse не является допустимым.

Проблема в том, что судя по всем hibernate не позволяет отключить генерацию alias для таблиц путем определения SQL Dialect. Во всяком случае мне не удалось найти, как этого добиться.

Немного покопавшись в коде hibernate я начал приходить к выводу, что эта функциональность зашита довольно глубоко в его ядре. Впрочем, мой опыт использования hibernate не так уж велик. Буду рад, если кто-либо поделиться известным способом обойти подобное ограничение.

Итог

Исследовать clickhouse было увлекательным занятием. СУБД показала себя с хорошей стороны. Удалось собрать ее из исходных кодов, выполнить запросы с помощью консольного клиента и jdbc. К сожалению, неполная поддержка конструкций ANSI SQL не позволила легко подключить Hibernate. Впрочем, следует учитывать и назначение СУБД. Главная ниша clickhouse — сложные аналитические запросы, большинство из которых все равно придется полностью или частично писать руками. Хотя иметь возможность простого просмотра и выборки данных «из коробки» при помощи ORM было бы не лишним.

Полагаю, будет крайне интересно наблюдать за развитием проекта, особенно учитывая растущее внимание к clikchouse в сообществе последнее время и активную поддержку проекта со стороны Яндекса.