Options

Vertica Integration with Hibernate: Connection Guide

shwetanksshwetanks
in General Discussion

I am following this document but not able to find VerticaDialect.jar on the Micro Focus Vertica marketplace. Please provide this jar for vertica-jdbc-9.1.0-0 and vertica-jdbc-7.2.3-0.jar

or Can we use org.hibernate.dialect.PostgreSQLDialect to connect vertica, Is it feasible?

Comments

  • Options
    Jim_KnicelyJim_Knicely - Select Field - Administrator

    Hi,

    Looks like "org.hibernate.dialect.PostgreSQLDialect" will work:

    See:
    https://stackoverflow.com/questions/7571890/does-eclipse-link-work-together-with-vertica

    Also, found this... Patch for Vertica dialect to support Hibernate 5...

    See:
    https://github.com/koteika/vertica-hibernate5-dialect

  • Options
    GianPendolaGianPendola Community Edition User

    Seems like it's broken for Hibernate 6. I'm changing to PostgreSALDialect. But still have a bunch of issues with Spring Security migration :(, so haven't tested it thoroughly.

  • Options
    ajabrolajabrol Vertica Employee Employee
    edited May 2023

    Hi,

    I am working on this issue and will check vertica dialect with Hibernate 6. I will update you accordingly.

  • Options
    ajabrolajabrol Vertica Employee Employee

    Hi,

    Please share me more details about the issues which you have encountered with Hibernate 6 and also provide me the exact version of hibernate which you were use.

    Please let me know if you have any queries.

    Regards,
    Ajay Abrol

  • Options
    GianPendolaGianPendola Community Edition User

    I tried PostgreSQL dialect but it didn't work either. I will post the errors with verticadialect.jar

  • Options
    GianPendolaGianPendola Community Edition User

    My application is working fine with Spring Boot 2.7, but when using Spring Boot 3.0 (after changing all javax.* to jakarta.*), that uses Hibernate 6, I get the following when I start the application:

    2023-05-20T09:36:54.996-05:00[0;39m [31mERROR[0;39m [35m27740[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36mo.s.boot.SpringApplication [0;39m [2m:[0;39m Application run failed

    org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment] due to: Unable to resolve name [org.hibernate.dialect.VerticaDialect] as strategy [org.hibernate.dialect.Dialect]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1770) ~[spring-beans-6.0.9.jar:6.0.9]
    at
    .
    .
    .
    at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49) ~[spring-boot-devtools-3.1.0.jar:3.1.0]
    Caused by: org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment] due to: Unable to resolve name [org.hibernate.dialect.VerticaDialect] as strategy [org.hibernate.dialect.Dialect]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:277) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final]
    .
    .
    .

    Caused by: org.hibernate.boot.registry.selector.spi.StrategySelectionException: Unable to resolve name [org.hibernate.dialect.VerticaDialect] as strategy [org.hibernate.dialect.Dialect]
    at org.hibernate.boot.registry.selector.internal.StrategySelectorImpl.selectStrategyImplementor(StrategySelectorImpl.java:154) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final]
    .
    .
    .
    ... 36 common frames omitted
    Caused by: org.hibernate.boot.registry.classloading.spi.ClassLoadingException: Unable to load class [org.hibernate.dialect.VerticaDialect]
    at org.hibernate.boot.registry.classloading.internal.ClassLoaderServiceImpl.classForName(ClassLoaderServiceImpl.java:126) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final]
    at org.hibernate.boot.registry.selector.internal.StrategySelectorImpl.selectStrategyImplementor(StrategySelectorImpl.java:150) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final]
    ... 44 common frames omitted
    Caused by: java.lang.NoClassDefFoundError: org/hibernate/dialect/function/SQLFunction
    at java.base/java.lang.Class.forName0(Native Method) ~[na:na]
    at java.base/java.lang.Class.forName(Class.java:467) ~[na:na]
    at org.hibernate.boot.registry.classloading.internal.ClassLoaderServiceImpl.classForName(ClassLoaderServiceImpl.java:120) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final]
    ... 45 common frames omitted
    Caused by: java.lang.ClassNotFoundException: org.hibernate.dialect.function.SQLFunction
    at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:641) ~[na:na]
    at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:188) ~[na:na]
    at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:520) ~[na:na]
    ... 48 common frames omitted

    Dependencies for Vertica on Maven are:

        <dependency>
        <groupId>com.vertica</groupId>
        <artifactId>vjdbc12</artifactId>
        <version>12.0.3</version>
        <scope>system</scope>
        <systemPath>/Library/java/Extensions/vertica-jdbc-12.0.3-0.jar</systemPath>
    </dependency>
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>vDialect</artifactId>
        <version>9.0.1</version>
        <scope>system</scope>
        <systemPath>/Library/java/Extensions/VerticaDialect.jar</systemPath>
    </dependency>

    The same application, but on Spring Boot 2.7 (Hibernate 5), starts properly:

    09:43:07.978 [main] INFO com.tecnidatos.nomina.NominaApplication - I0001 INFO Iniciando la aplicación de Nomina - NominaApplication
    09:43:08.081 [Thread-0] DEBUG org.springframework.boot.devtools.restart.classloader.RestartClassLoader - Created RestartClassLoader org.springframework.boot.devtools.restart.classloader.RestartClassLoader@59dde20d
    09:43:08.082 [restartedMain] INFO com.tecnidatos.nomina.NominaApplication - I0001 INFO Iniciando la aplicación de Nomina - NominaApplication

    =========||==============|___/=////
    [32m :: Spring Boot :: [39m [2m (v2.7.12-SNAPSHOT)[0;39m

    [2m2023-05-20 09:43:08.297[0;39m [32m INFO[0;39m [35m27860[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36m.e.DevToolsPropertyDefaultsPostProcessor[0;39m [2m:[0;39m Devtools property defaults active! Set 'spring.devtools.add-properties' to 'false' to disable
    [2m2023-05-20 09:43:08.298[0;39m [32m INFO[0;39m [35m27860[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36m.e.DevToolsPropertyDefaultsPostProcessor[0;39m [2m:[0;39m For additional web related logging consider setting the 'logging.level.web' property to 'DEBUG'
    [2m2023-05-20 09:43:08.794[0;39m [32m INFO[0;39m [35m27860[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36m.s.d.r.c.RepositoryConfigurationDelegate[0;39m [2m:[0;39m Bootstrapping Spring Data JPA repositories in DEFAULT mode.
    [2m2023-05-20 09:43:08.819[0;39m [32m INFO[0;39m [35m27860[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36m.s.d.r.c.RepositoryConfigurationDelegate[0;39m [2m:[0;39m Finished Spring Data repository scanning in 20 ms. Found 1 JPA repository interfaces.
    [2m2023-05-20 09:43:09.153[0;39m [32m INFO[0;39m [35m27860[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36mo.s.b.w.embedded.tomcat.TomcatWebServer [0;39m [2m:[0;39m Tomcat initialized with port(s): 9999 (https)
    [2m2023-05-20 09:43:09.220[0;39m [32m INFO[0;39m [35m27860[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36mw.s.c.ServletWebServerApplicationContext[0;39m [2m:[0;39m Root WebApplicationContext: initialization completed in 922 ms
    [2m2023-05-20 09:43:09.261[0;39m [32mDEBUG[0;39m [35m27860[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36mcom.zaxxer.hikari.HikariConfig [0;39m [2m:[0;39m Driver class com.vertica.jdbc.Driver found in Thread context class loader TomcatEmbeddedWebappClassLoader
    context: ROOT
    delegate: true
    ----------> Parent Classloader:
    org.springframework.boot.devtools.restart.classloader.RestartClassLoader@59dde20d

    .
    .
    .
    [36mcom.zaxxer.hikari.pool.HikariPool [0;39m [2m:[0;39m HikariPool-1 - Added connection com.vertica.jdbc.VerticaJdbc42ConnectionImpl@cf40384
    [2m2023-05-20 09:43:09.552[0;39m [32mDEBUG[0;39m [35m27860[0;39m [2m---[0;39m [2m[onnection adder][0;39m [36mcom.zaxxer.hikari.pool.HikariPool [0;39m [2m:[0;39m HikariPool-1 - After adding stats (total=10, active=0, idle=10, waiting=0)
    [2m2023-05-20 09:43:09.926[0;39m [32m INFO[0;39m [35m27860[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36mj.LocalContainerEntityManagerFactoryBean[0;39m [2m:[0;39m Initialized JPA EntityManagerFactory for persistence unit 'default'
    [2m2023-05-20 09:43:10.116[0;39m [32m INFO[0;39m [35m27860[0;39m [2m---[0;39m [2m[ restartedMain][0;39m
    .
    .
    .
    [2m2023-05-20 09:43:10.732[0;39m [32m INFO[0;39m [35m27860[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36mo.s.b.w.embedded.tomcat.TomcatWebServer [0;39m [2m:[0;39m Tomcat started on port(s): 9999 (https) with context path ''
    [2m2023-05-20 09:43:11.674[0;39m [32m INFO[0;39m [35m27860[0;39m [2m---[0;39m [2m[on(9)-127.0.0.1][0;39m [36mo.s.web.servlet.DispatcherServlet [0;39m [2m:[0;39m Initializing Servlet 'dispatcherServlet'
    [2m2023-05-20 09:43:11.675[0;39m [32m INFO[0;39m [35m27860[0;39m [2m---[0;39m [2m[on(9)-127.0.0.1][0;39m [36mo.s.web.servlet.DispatcherServlet [0;39m [2m:[0;39m Completed initialization in 1 ms
    [2m2023-05-20

    ==================
    It seems like Hibernate 6 is not able to use verticaDialect.jar, whereas Hibernate 5 is using it happily.

  • Options
    GianPendolaGianPendola Community Edition User

    If I use PostgreSQLDialect, I get:

    2023-05-20T13:31:40.830-05:00[0;39m [32m INFO[0;39m [35m29135[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36m.e.DevToolsPropertyDefaultsPostProcessor[0;39m [2m:[0;39m Devtools property defaults active! Set 'spring.devtools.add-properties' to 'false' to disable
    [2m2023-05-20T13:31:40.830-05:00[0;39m [32m INFO[0;39m [35m29135[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36m.e.DevToolsPropertyDefaultsPostProcessor[0;39m [2m:[0;39m For additional web related logging consider setting the 'logging.level.web' property to 'DEBUG'
    [2m2023-05-20T13:31:41.171-05:00[0;39m [32m INFO[0;39m [35m29135[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36m.s.d.r.c.RepositoryConfigurationDelegate[0;39m [2m:[0;39m Bootstrapping Spring Data JPA repositories in DEFAULT mode.
    [2m2023-05-20T13:31:41.203-05:00[0;39m [32m INFO[0;39m [35m29135[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36m.s.d.r.c.RepositoryConfigurationDelegate[0;39m [2m:[0;39m Finished Spring Data repository scanning in 27 ms. Found 1 JPA repository interfaces.
    [2m2023-05-20T13:31:41.754-05:00[0;39m [32m INFO[0;39m [35m29135[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36mo.s.b.w.embedded.tomcat.TomcatWebServer [0;39m [2m:[0;39m Tomcat initialized with port(s): 9999 (https)
    [2m2023-05-20T13:31:41.797-05:00[0;39m [32m INFO[0;39m [35m29135[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36mw.s.c.ServletWebServerApplicationContext[0;39m [2m:[0;39m Root WebApplicationContext: initialization completed in 967 ms
    [2m2023-05-20T13:31:41.837-05:00[0;39m [32mDEBUG[0;39m [35m29135[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36mcom.zaxxer.hikari.HikariConfig [0;39m [2m:[0;39m Driver class com.vertica.jdbc.Driver found in Thread context class loader org.springframework.boot.devtools.restart.classloader.RestartClassLoader@7c9a965d
    [2m2023-05-20T13:31:42.052-05:00[0;39m [32m INFO[0;39m [35m29135[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36mo.s.o.j.p.SpringPersistenceUnitInfo [0;39m [2m:[0;39m No LoadTimeWeaver setup: ignoring JPA class transformer
    [2m2023-05-20T13:31:42.588-05:00[0;39m [31mERROR[0;39m [35m29135[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36mo.h.engine.jdbc.spi.SqlExceptionHelper [0;39m [2m:[0;39m [Vertica]VJDBC ERROR: Schema "information_schema" does not exist
    [2m2023-05-20T13:31:42.594-05:00[0;39m [31mERROR[0;39m [35m29135[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36mj.LocalContainerEntityManagerFactoryBean[0;39m [2m:[0;39m Failed to initialize JPA EntityManagerFactory: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.exception.GenericJDBCException: Unable to build DatabaseInformation [[Vertica]VJDBC ERROR: Schema "information_schema" does not exist] [n/a]**
    [2m2023-05-20T13:31:42.594-05:00[0;39m [33m WARN[0;39m [35m29135[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36mConfigServletWebServerApplicationContext[0;39m [2m:[0;39m Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.exception.GenericJDBCException: Unable to build DatabaseInformation [[Vertica]VJDBC ERROR: Schema "information_schema" does not exist] [n/a]
    [2m2023-05-20T13:31:42.603-05:00[0;39m [32m INFO[0;39m [35m29135[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36m.s.b.a.l.ConditionEvaluationReportLogger[0;39m [2m:[0;39m

    Error starting ApplicationContext. To display the condition evaluation report re-run your application with 'debug' enabled.
    [2m2023-05-20T13:31:42.614-05:00[0;39m [31mERROR[0;39m [35m29135[0;39m [2m---[0;39m [2m[ restartedMain][0;39m [36mo.s.boot.SpringApplication [0;39m [2m:[0;39m Application run failed

    org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in class path resource [org/springframework/boot/autoconfigure/orm/jpa/HibernateJpaConfiguration.class]: [PersistenceUnit: default] Unable to build Hibernate SessionFactory; nested exception is org.hibernate.exception.GenericJDBCException: Unable to build DatabaseInformation [[Vertica]VJDBC ERROR: Schema "information_schema" does not exist] [n/a]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1770) ~[spring-beans-6.0.9.jar:6.0.9]
    .
    .
    .
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:409) ~[spring-orm-6.0.9.jar:6.0.9]
    ... 25 common frames omitted
    Caused by: java.sql.SQLException: [Vertica]VJDBC ERROR: Schema "information_schema" does not exist
    at com.vertica.util.ServerErrorData.buildException(Unknown Source) ~[vertica-jdbc-12.0.3-0.jar:na]
    at com.vertica.io.ProtocolStream.readExpectedMessage(Unknown Source) ~[vertica-jdbc-12.0.3-0.jar:na]
    at com.vertica.dataengine.VDataEngine.prepareImpl(Unknown Source) ~[vertica-jdbc-12.0.3-0.jar:na]
    at com.vertica.dataengine.VDataEngine.prepare(Unknown Source) ~[vertica-jdbc-12.0.3-0.jar:na]
    at com.vertica.dataengine.VDataEngine.prepare(Unknown Source) ~[vertica-jdbc-12.0.3-0.jar:na]
    at com.vertica.jdbc.common.SPreparedStatement.(SPreparedStatement.java:310) ~[vertica-jdbc-12.0.3-0.jar:na]
    at com.vertica.jdbc.jdbc41.S41PreparedStatement.(S41PreparedStatement.java:91) ~[vertica-jdbc-12.0.3-0.jar:na]
    at com.vertica.jdbc.jdbc42.S42PreparedStatement.(S42PreparedStatement.java:77) ~[vertica-jdbc-12.0.3-0.jar:na]
    at com.vertica.jdbc.VerticaJdbc42PreparedStatementImpl.(Unknown Source) ~[vertica-jdbc-12.0.3-0.jar:na]
    at com.vertica.jdbc.VJDBCObjectFactory.createPreparedStatement(Unknown Source) ~[vertica-jdbc-12.0.3-0.jar:na]
    at com.vertica.jdbc.common.SConnection.prepareStatement(SConnection.java:1309) ~[vertica-jdbc-12.0.3-0.jar:na]
    at com.vertica.jdbc.common.SConnection.prepareStatement(SConnection.java:1263) ~[vertica-jdbc-12.0.3-0.jar:na]
    at org.hibernate.tool.schema.extract.spi.ExtractionContext.getQueryResults(ExtractionContext.java:43) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final]
    at org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorLegacyImpl.extractMetadata(SequenceInformationExtractorLegacyImpl.java:39) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final]
    at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.initializeSequences(DatabaseInformationImpl.java:66) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final]
    at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.(DatabaseInformationImpl.java:60) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final]
    at org.hibernate.tool.schema.internal.Helper.buildDatabaseInformation(Helper.java:187) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final]
    ... 38 common frames omitted
    Caused by: com.vertica.support.exceptions.ErrorException: [Vertica]VJDBC ERROR: Schema "information_schema" does not exist
    ... 55 common frames omitted

  • Options
    siddyashsiddyash Vertica Employee Employee

    Hello @GianPendola , Ajay is currently working on it to resolve the issue and provide you a solution soon. Thanks.

  • Options
    GianPendolaGianPendola Community Edition User

    Hello @siddyash, do you have any update on this issue?
    Thanks.

  • Options
    ajabrolajabrol Vertica Employee Employee

    Hi @GlanPendola, I am working on it and will update with my analysis.

    Thanks & Regards,
    Ajay Abrol

  • Options
    ajabrolajabrol Vertica Employee Employee

    Hi @GlanPendola,

    If you want to use postgreSQLDialect then please follow https://github.com/bryanherger/vertica-information-schema. It helps you to create INFORMATION_SCHEMA and other objects on Vertica DB which helps you to connect with Vertica.

    Meantime, I am working on to update VerticaDialect as per Hibernate6 and will update you accordingly.

    Thanks & Regards,
    Ajay Abrol

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Format
SpoilerCodeQuote
Heading 2Heading 1
Emoji
:):D:(;):/:o:s:p:'(:|B):#:*<3o:)>:)
Url
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file
Home • General DiscussionComment As ...