Integrating Elastic Search JDBC driver with Hibernate / Spring Boot

Question

I am trying to integrate the Elastic Driver "org.elasticsearch.xpack.sql.jdbc.EsDriver" from

<dependency>
  <groupId>org.elasticsearch.plugin</groupId>
  <artifactId>x-pack-sql-jdbc</artifactId>
  <version>7.10.0</version>
</dependency>

into my spring boot app using Hibernate.

In my spring configuration bean I have the following:

    @Bean
    @ConfigurationProperties(prefix = "db.elastic")
    @Qualifier("elasticDataSource")
    @Primary
    public DataSource elasticDataSource() {
        return DataSourceBuilder.create()
                .build();
    }

    public LocalContainerEntityManagerFactoryBean elasticEntityManagerFactory(
            EntityManagerFactoryBuilder builder) {
        Map<String, Object> properties = new HashMap<>();
        properties.put(AvailableSettings.HBM2DDL_AUTO, "none");
        properties.put(AvailableSettings.HBM2DLL_CREATE_SCHEMAS, "false");
        properties.put(AvailableSettings.DIALECT, org.elasticsearch.xpack.sql.jdbc.EsDriver.class.getName());
        return builder
                .dataSource(elasticDataSource())
                .packages(Issuer.class)
                .persistenceUnit("elastic")
                .properties(properties)
                .build();
    }

However, when I run this code I get the following exception:

Caused by: org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:275)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:237)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:214)
    at org.hibernate.id.factory.internal.DefaultIdentifierGeneratorFactory.injectServices(DefaultIdentifierGeneratorFactory.java:152)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.injectDependencies(AbstractServiceRegistryImpl.java:286)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:243)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:214)
    at org.hibernate.boot.internal.InFlightMetadataCollectorImpl.<init>(InFlightMetadataCollectorImpl.java:179)
    at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:119)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.metadata(EntityManagerFactoryBuilderImpl.java:904)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:935)
    at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:57)
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:390)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:377)
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1837)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1774)
    ... 16 common frames omitted
Caused by: org.hibernate.HibernateException: Unable to construct requested dialect [org.elasticsearch.xpack.sql.jdbc.EsDriver]
    at org.hibernate.engine.jdbc.dialect.internal.DialectFactoryImpl.constructDialect(DialectFactoryImpl.java:84)
    at org.hibernate.engine.jdbc.dialect.internal.DialectFactoryImpl.buildDialect(DialectFactoryImpl.java:51)
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:137)
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:35)
    at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.initiateService(StandardServiceRegistryImpl.java:94)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:263)
    ... 33 common frames omitted
Caused by: java.lang.ClassCastException: org.elasticsearch.xpack.sql.jdbc.EsDriver cannot be cast to org.hibernate.dialect.Dialect
    at org.hibernate.engine.jdbc.dialect.internal.DialectFactoryImpl.constructDialect(DialectFactoryImpl.java:74)
    ... 38 common frames omitted

I assume this is because the driver isn't compatible with hibernate. Am I correct or is there some other configuration that must be done to work around the problem?

There is also a commercially available JDBC driver here: https://www.cdata.com/drivers/elasticsearch/jdbc/

Has anyone got any experience with this driver and it's compatibility with Hibernate?

Answer 1

I have no knowledge of ElasticSearch driver, however I will tell you why you are getting the error.

Your error is:Caused by: java.lang.ClassCastException: org.elasticsearch.xpack.sql.jdbc.EsDriver cannot be cast to org.hibernate.dialect.Dialect

This is because you have a problem in your properties:

properties.put(AvailableSettings.DIALECT, org.elasticsearch.xpack.sql.jdbc.EsDriver.class.getName());

EsDrver.class.getName() is not a Hibernate Dialect.

An example hibernate dialect is: org.hibernate.dialect.MySQL5Dialect

Kindly read https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-jdbc.html

Seeing that you are using Springboot, you might not even have to manually configure the datasource/entitymanager. Simply adding the dependency will autoconfigure it.

Answer 2

There is no dialect for the ElasticSearch SQL dialect, but you can try using org.hibernate.dialect.SQLServerDialect like suggested in https://www.cdata.com/kb/tech/elasticsearch-jdbc-hibernate.rst

In the end, you will probably have to override a few configs in the dialect to fit the SQL dialect that is supported by ES.

Answer 3

So I've found that org.hibernate.dialect.H2Dialect or org.hibernate.dialect.PostgreSQL9Dialect work, but only up to a point.

Firstly you must also include double quotes in entity names:

@Data
@Table(name = "\"mytable\"")
@Entity
public class MyTable {
}

Furthermore I've found that setting maxRows doesn't work. For some reason this value isn't bound to limit in the generated Sql:

18:14:29.320 [http-nio-8081-exec-1] DEBUG org.hibernate.SQL - 
    select
        mytabl_.NAME as col_0_0_ 
    from
        "mytable" mytabl_
    order by
        mytabl_.NAME asc limit ?
18:14:29.651 [http-nio-8081-exec-1] WARN  o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 0, SQLState: null
18:14:29.651 [http-nio-8081-exec-1] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - line 1:108: mismatched input '?' expecting {'ALL', INTEGER_VALUE}
18:14:29.658 [http-nio-8081-exec-1] WARN  g.e.SimpleDataFetcherExceptionHandler - Exception while fetching data (/Issuers) : line 1:108: mismatched input '?' expecting {'ALL', INTEGER_VALUE}
org.hibernate.JDBCException: line 1:108: mismatched input '?' expecting {'ALL', INTEGER_VALUE}

Anyone have any ideas as to why this might be?