使用Snowflake和Spring Boot/JPA/Hibernate是否可行？

Question

我正在创建一个直接写入雪花数据库的服务。我很困扰，试图让Spring Data JPA与Snowflake有效地协作。我的主要问题是，我无法通过Jpa Repository接口中的Save方法将实体保存到Snowflake DB。因为此应用程序被用于将数据转储到Snowflake中，利用JPA将使生活变得更加轻松。

我不想自己编写本地查询，所以我的问题是，在使用Snowflake时是否可能利用Hibernate。

我希望能够使用JPA Repositories内置的Save方法持久化实体。

以下是我的当前配置。如果您有任何想法可以改进配置以让它正常工作，或者对它是否可行有任何意见，都会非常感激。

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)

- Russell Brady

---

我建议您启用JDBC连接器日志记录并使用DEBUG模式，因为这样可能会显示更多信息。要启用日志记录，请查看此处。 - Sergiu

Answer 1

作为跟进，我无法使用我上面概述的方法启动应用程序。我仍然不确定原因，但认为可能与Spring中缺乏对snowflake序列作为主键生成类型的支持有关。

我将生成类型更改为UUID，应用程序开始按预期工作。没有要求需要什么类型的主键，所以这种方法是可以接受的。

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
)

- Russell Brady