Issue
I am creating a service which writes directly to a snowflake database. I am having a lot of trouble trying to get spring data jpa to work effectively with Snowflake. My main issue is that I am unable to save an entity to the Snowflake DB through Jpa Repository interface Save method. Because this application is being used to dump data into Snowflake, being able to leverage JPA would make life a lot easier.
I would prefer not to have to roll my own native queries so my question is whether it's possible to leverage Hibernate when working with Snowflake.
The main thing I want to be able to do is persist entities using the Jpa Repositories inbuild Save method.
Below is my current configuration. Any ideas on what could be improved in the configuration to get this working would be appreciated, or also any opinion on whether it is possible or not.
spring:
profiles:
active: local
application:
name: Service
datasource:
driverClassName: net.snowflake.client.jdbc.SnowflakeDriver
url: ${SPRING_DATASOURCE_URL}
username: ${SPRING_DATASOURCE_USERNAME}
password: ${SPRING_DATASOURCE_PASSWORD}
flyway:
locations: classpath:db/migration/common,classpath:db/migration/snowflake
jpa:
properties:
hibernate:
dialect: org.hibernate.dialect.SQLServerDialect
order_inserts: true
create sequence award_event_id_seq;
create table award_event
(
id INT NOT NULL DEFAULT award_event_id_seq.nextval PRIMARY KEY,
event_source_system varchar not null,
event_trigger VARCHAR NOT NULL,
event_triggered_by VARCHAR NOT NULL,
event_timestamp TIMESTAMP NOT NULL
)
@Entity(name = "award_event")
@SequenceGenerator(name = "award_event_id_seq", sequenceName = "award_event_id_seq", allocationSize = 1)
data class AwardEvent(
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
val id: Int = -1,
val eventTrigger: String,
val eventTriggeredBy: String,
val eventTimestamp: LocalDateTime,
val eventSourceSystem: String
)
override fun receiveMessage(message: String) {
logger.info("Receiving award event: $message")
val awardEvent: AwardEventMessage = message.toObject()
// This Save method does not work and throws an error specified below
awardEventRepository.save(awardEvent.toAwardEvent())
}
2021-01-08 10:49:28.163 ERROR 3239 --- [nio-9106-exec-1] o.hibernate.id.enhanced.TableStructure : could not read a hi value
net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:
syntax error line 1 at position 50 unexpected 'with'.
syntax error line 1 at position 72 unexpected ')'.
at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionSub(SnowflakeUtil.java:124)
at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:64)
at net.snowflake.client.core.StmtUtil.pollForOutput(StmtUtil.java:434)
at net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:338)
at net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:506)
at net.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:233)
at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:171)
at net.snowflake.client.core.SFStatement.execute(SFStatement.java:754)
at net.snowflake.client.jdbc.SnowflakeStatementV1.executeQueryInternal(SnowflakeStatementV1.java:245)
at net.snowflake.client.jdbc.SnowflakePreparedStatementV1.executeQuery(SnowflakePreparedStatementV1.java:117)
Solution
Just as a follow up, I was unable to get the application up and running using the approach I outlined above. I am still unsure why but think it may have been to do with a lack of support for snowflake sequences as the generation type for the primary key in spring.
I changed the generation type to UUID and the application started to work as expected in turn. There was no requirements for what type of primary key was needed so this approach was satisfactory.
create sequence award_event_id_seq;
create table award_event
(
id varchar not null constraint award_event_pkey primary key,
event_source_system varchar not null,
event_trigger varchar not null,
event_triggered_by varchar not null,
event_timestamp timestamp not null
)
@Entity(name = "award_event")
data class AwardEvent(
@Id
@GeneratedValue
@Type(type = "uuid-char")
val id: UUID = UUID.randomUUID(),
val eventTrigger: String,
val eventTriggeredBy: String,
val eventTimestamp: LocalDateTime,
val eventSourceSystem: String
)
Answered By - Russell Brady