Эволюция H2 — оконные функции, CTE, JSON/XML во встраиваемой базе данных

    Есть open source проекты которые стали коммерчески успешным мейнстримом, как например PostgreSQL/Elasticsearch. Другие, к примеру RethinkDB, проиграли на рынке и остановили разработку. А проект H2 database встраиваемой базы данных, написанной на языке java, развивается и здравствует в своей нише.


    Для демонстрации функционала SonarQube, Jira, Confluence при первом запуске используют H2 database. H2 является базой для запуска SQL тестов в памяти, почти в любом JVM проекте. Есть пример применения менее известный пользователям — это использование H2 в распределенном ignite-sql и это уже production ready сценарий использования встраиваемой базы данных как части другого решения. Меньше месяца назад вышла версия 1.4.199 в которой теперь можно писать достаточно сложные SQL запросы.

    В проектах я никогда не полагался на H2, как на полноценную базу данных с сохранением данных на диск. Скорее, как модуль трансформации данных в памяти JVM, с неплохой поддержкой SQL. Но для этого применения его сильно ограничивало отсутствие оконных функций. И вот спустя более чем пол года с начала разработки функционала, теперь H2database догнал SQLite. И в этом огромная заслуга Евгения Рязанова из Иркутска — таких темпов разработки как у него, раньше не видел в некоммерческих open source проектах. Также в репозитарии проекта регулярно появляется комиты других рускоговорящих контрибьюторов. А в моменты релиза — основателя проекта Thomas Mueller.

    В H2 есть поддержка рекурсивных запросов (CTE). Это стандартный в SQL способ работы с иерархическими данными в таблицах и декомпозиции запросов(тут можно наступить на грабли планировщика). Про рекурсивные запросы доступно рассказано в публикации с примерами.

    Для работы со слабо структурированными данными в будущей версии появится реализация SQL/JSON стандарта. А пока для своих нужд расширил H2 с помощью пользовательской функции XQuery 3.1 процессором, на основе BaseX. Код доступен в github проекте H2XQueryAdapter. Это табличная функция, которая из XML или JSON формата с помощью XQuery может извлекать данные и проверять для возвращаемых функцией значений тип и ограничение not null. Дальше доступна вся мощь SQL выражений для трансформации результата XQuery преобразования в памяти JVM процесса.

    Табличная функция xquery() перегружена и имеет два варианта — с одним параметром xQuery запрос и вариант с xQuery запросом и вторым — строкой sql запроса для формирования параметров для самого xQuery.

    Подход с подобными трансформациями отлично показал себя в проекте по обработке петабайтного объема «сырых» данных в проекте хранилища биомедицинских данных.

    create table xresult (GR VARCHAR(500) not null,AR varchar, VER VARCHAR(50)) as 
     select * from xquery('declare variable $getHeader as xs:boolean external := false(); declare variable $getData as xs:boolean external := true(); 
    <csv>
    {
    if($getHeader) then( 
        <record><mavengr>VARCHAR(500) not null</mavengr><artifactname>varchar</artifactname><versionname>VARCHAR(50)</versionname></record>
     ),
     if($getData) then((
        for $row in doc("http://central.maven.org/maven2/org/springframework/spring-context/5.1.4.RELEASE/spring-context-5.1.4.RELEASE.pom")//*:dependency
        return 
        <record><mavengr>{$row/*:groupId/text()}</mavengr><artifactname>{$row/*:artifactId/text()}</artifactname><versionname>{$row/*:version/text()}</versionname></record>
     ))
    }
    </csv>')

    У данной реализации есть ограничения на формат xquery запроса.

    • Во первых, необходимо объявить две внешних переменных getHeader и getData — это нужно чтобы табличная функция не вызывала несколько раз трансформацию данных, отбрасывая ненужные результаты, чтобы получить лишь названия и тип колонок. Множественный вызов функции — это особенности работы H2 базы с java функциями возвращающими список значений.
    • Во вторых, формат результата должен быть как для csv сериализации

      <csv>
          <record><колонка1>тип данных</колонка1>...<колонкаN>тип данных</колонкаN></record>
          <record><колонка1>значение</колонка1>...<колонкаN>значение</колонкаN></record>
      </csv>
    • В третьих, необходимо объявить тип данных, размерность и обязательность поля, к примеру: decimal(20,4) not null

    С парсингом типа данных колонки я пытался переиспользовать парсер SQL из H2, но он оказался настолько сильно связан с другими объектами базы данных, что отказывался работать без создания базы данных и сессии. Утешаю себя мыслью, что разработчики сделали так, чтобы упростить дизайн приложения и не делать парсер на все случаи жизни для любых BNF грамматик.

    Можете запустить этот пример в режиме java отладки. Новые правила трансформации можно разрабатывать в привычном вам xquery редакторе или в open source BaseX GUI.

    Есть возможность превращать практически любую java коллекцию или POJO в виртуальную таблицу H2. Код проекта H2POJOTable на github. В примере по ссылке платформенный MemoryManagerMXBeans превращается в табличную функцию H2. Возможно, подход станет небольшим утешением тем, кто грустит по отсутствию LINQ и поддержки операций над множествами в Java.

    try (Statement statement = connection.createStatement()) {
        String pojoTableAlias = "create alias MemoryManagerMXBeans as $$ \n" +
                "import java.lang.management.ManagementFactory;\n" +
                "import java.lang.management.MemoryManagerMXBean;\n" +
                "import org.h2.expression.function.pojo.*;\n" +
                "import java.sql.*;\n" +
                "import java.util.Collections;\n" +
                "@CODE\n" +
                "    ResultSet getRuntimeStat(Connection connection) throws Exception{\n" +
                "        return H2PojoAdapter.toTable(connection, new CollectionWraper<>(MemoryManagerMXBean.class," +
                "           ManagementFactory::getMemoryManagerMXBeans, Collections.emptyMap()));\n" +
                "    }\n" +
                "\n$$";
        statement.executeUpdate(pojoTableAlias);
    }
    try (Statement statement = connection.createStatement()) {
        try (ResultSet resultSet = statement.executeQuery("select * from MemoryManagerMXBeans()")) {
            int columnCount = assertResultSet(resultSet, new String[]{"memoryPoolNames", "name", "valid"});
            assertThat(columnCount).isGreaterThan(1);
        }
    }

    Иногда старый функционал в H2 ломается и не столь востребован пользователями. Как пример, в моих рабочих проектах программы читают данные из AWS S3 URL. Поэтому я надеюсь, известную ошибку все же починят, приняв мой pull request. На пути к исправлению этой ошибки стоят нестабильные тесты для TLS, которые к тому же не работают под Java 11.

    H2 позволяет использовать ODBC драйвер PostgreSQL эмулируя подмножество его сетевого протокола. Что так же теоретически позволяет связывать его через FDW в PostgreSQL.

    Кроме самой базы данных в поставку H2 также входит минималистская web консоль с поддержкой автодополнения при редактировании, сервлетом или standalone вариантом запуска. H2 выглядит как «швейцарский нож» для разработчиков — компактный и универсальный инструмент, если ваш проект уже использует JVM. При попытке использовать эту консоль с «кривым» jdbc драйвером СУБД Redshift сделал свой первый красноглазый pull request в проект. Noel Grandin, один из участников проекта, помог мне с code review и принял исправления.

    Если нужен аналог Berkeley DB Java Edition — в проекте есть MVStore — персистентное хранилище для данных ключ-значение и по совместительству MVCC «движок» по-умолчанию в последних версиях базы данных. Поражает что в БД есть даже базовая поддержка геофункций и полнотекстового поиска.

    Cпасибо контрибьюторам H2database, всем использует эту базу и сообщает об ошибках! H2 database с 2005 года развивается, а теперь поддерживает оконные функции, рекурсивные запросы, является одним из наиболее мощных SQL «движков» для обработки данных в памяти JVM и расширяется табличными функциями для работы со слабо структурированными данными.
    Share post

    Similar posts

    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 10

      0
      H2 действительно очень удобен, когда нужно что-то по быстрому продемонстрировать, сделать небольшое хранилище, однако интересно, есть ли кейсы, когда H2 использовался в проде при больших объёмах данных и какого это было? Или никто даже не пытался пробовать?
        0
        Как я понимаю, используют в проде Сбербанка, как основную часть технологии SQL запросов в кластерах IMDG Gridgain.
        0

        Все-таки H2 больше для тестовых нужд, нужно понимать, что это все-таки не полноценный движок. Он хорош тем, что семантически поддерживает SQLи разных диалектов и может корректно их выполнять.
        Мы пробовали использовать H2 1.4.196 в проде в embedded-режиме, и хоть задача и была достаточно простой, но даже на ней стали вылезать проблемы.


        • сразу же нактнулись на deadlock при выполнении: разобрались, засабмитили баг и вроде бы как в следующей версии пофиксили
        • Thead.interrupt() внезапно прибивал сразу весь datasource, если тред в этот момент выполнял запрос
        • странно интегрирован full-text search: старая версия Lucene, один индекс на все таблицы, который полностью перстраивается после каждого alter/create/drop
        • движок SQL более чем примитивный. По сути он в состоянии выполнять только запросы по одной таблице, либо если все лежит в памяти. Если же ваш запрос содержит JOIN или тупо не умещается в памяти (MAX_MEMORY_ROWS), H2 каждый раз создает временную таблицу с промежуточными результатами (а иногда и не одну), с полным набором данных, даже если тебе нужна только одна запись. А при отсутствии индексов этот же запрос может выполняться часами.
        • учитывая вышесказанное limit/offset никак не влияют на выполнение запроса и служат тупо для обрезания финального resultset
        • несмотря на заявленный MVCC, при выполнении "тяжелого" запроса (того, что требует временную таблицу) таблицы жестко блокируются

        Мы мигрировали на Postgres за пару часов и с тех пор все тепло и сухо. А вот в тестбенчах и юнитах до сих пор используем H2.

          0
          Согласен, что H2 не замена PostgreSQL, планировщик, статистика и алгоритмы сортировок не те! Но для трансформации данных в процессе подходит отлично. Есть особенность — сериализация всех полей, что может быть критично по аллокации временных объектов. С другой стороны SQL самый распространенный DSL обработки данных и «дешевый» для реализации в приложении с описаным подходом. Не надо учить ему команду и аналитиков.
            0
            По сути он в состоянии выполнять только запросы по одной таблице, либо если все лежит в памяти. Если же ваш запрос содержит JOIN или тупо не умещается в памяти (MAX_MEMORY_ROWS), H2 каждый раз создает временную таблицу с промежуточными результатами (а иногда и не одну), с полным набором данных, даже если тебе нужна только одна запись.
            А есть доказательства, что всё действительно настолько плохо?
              0

              Все доказательства у вас полезут в продакше, когда размер таблиц заметно подрастет.
              Вот например баг по поводу дедлока:
              https://groups.google.com/forum/#!searchin/h2-database/deadlock%7Csort:date/h2-database/wPR5gztLU34/EHMoEEpqBgAJ
              Из стектрейса видно, что на больших resultset движок создает на диске временную таблицу, в которую сваливает результаты, а после закрытия resultset ее дропает.
              Если интересно, поиграйтесь с солидными (50k+) табличками, джоинами и аналитическими запросами. Много всего интересного повылазит, например неоптимальное использование индексов планировщиком, которое никак не поменять.

            +1
            Хорошая база, вроде как самая быстрая из pure java, юзал в свое время, пока не ушел в javascript.
              0

              Xodus быстрее говорят. Если конечно не нужно SQL любой ценой. А для тестов использую postgres в контейнере. Благо докеры почти везде стали стандартом.

              0
              >Поражает что в БД есть даже базовая поддержка геофункций
              Я бы все-таки на нее не рассчитывал. Приятнее, что есть возможность написать расширение, и оно написано — H2GIS, вот его и стоит использовать. Даже не потому, что базовая плохая, а скорее потому, что плохо описана, а проводить опыты на кошках, чтобы понять, как оно будет работать — себе дороже.
                0
                Отличный совет, спасибо за H2GIS. Просто не перестаю удивляться сколько функций в таком крошечном проекте!

              Only users with full accounts can post comments. Log in, please.