java - 如何使用 Hibernate/Spring JPA 为 Snowflake 表生成唯一的 AutoIncremented Id？

Question

我是 Snowflake 的新手，我们决定使用 Hibernate/Spring-Data JPA，而不是实现 Snowflake JDBCDriver，因为它使用起来更方便。我们是通过这篇文章来的:Has anybody built an application using Java Spring Framework that connects to Snowflake在 Snowflake Community 上检查我们的用例是否得到满足。

根据我们的用例，我们的模型类看起来像这样，并且我们保持空方言部分和其他配置与链接中描述的相同。

import org.hibernate.annotations.GenericGenerator;

import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;

@Entity
@Table(name = "note")
public class Note implements Serializable {

    @Id
    @GenericGenerator(name = "id_generator", strategy = "increment")
    @GeneratedValue(generator = "id_generator")
    private Long id;

    @Basic(optional = false)
    @Column(name = "user_id")
    private String userId;

    @Basic(optional = false)
    @Column(name = "content")
    private String content;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getUserId() {
        return userId;
    }

    public void setUserId(String userId) {
        this.userId = userId;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

    public Note(String userId, String content) {
        this.userId = userId;
        this.content = content;
    }

    public Note() {
    }
}

然后我们使用以下查询在 Snowflake 中创建了表:

CREATE OR REPLACE TABLE "WAREHOUSE"."SCHEMA".note (
 id INT NOT NULL AUTOINCREMENT UNIQUE,
 user_id STRING NOT NULL, 
 content STRING NOT NULL, 
 PRIMARY KEY (id)   
);

以上代码按预期工作并生成自动递增的 id 作为主键。我们还尝试运行我们服务的多个实例，并且由于 Snowflake 未强制执行唯一约束，因此我们遇到了具有重复 id 值的问题。 (单个表有多个数据插入源。)

关于方言，我们找不到任何适用于 Snowflake 的 Hibernate 方言，因此我们使用了引用链接中描述的相同方言细节。我们创建了 EmptyDialect 类并在属性文件中给出了它的路径。

public class EmptyDialect extends org.hibernate.dialect.Dialect {}

属性文件:

spring.jpa.properties.hibernate.dialect= absolute path of the EmptyDialect class 

我们已经尝试了所有 ID 生成策略，如 IDENTITY、SEQUENCE、AUTO 等，但收到的异常可能是由于没有单独的 Snowflake Dialect。如果需要，将添加错误的堆栈跟踪。

• 顺序方法:

我们通过以下查询创建了序列，并相应地对表创建查询和注释进行了更改。

create or replace sequence "Warehouse"."Schema".sequence_note start = 1 increment = 1;

CREATE OR REPLACE TABLE "Warehouse"."Schema".note (
 id INT NOT NULL DEFAULT "Warehouse"."Schema".SEQUENCE_NOTE.nextval UNIQUE,
 user_id STRING NOT NULL, 
 content STRING NOT NULL, 
 PRIMARY KEY (id)   
);

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequence_note")
private Long id;

Hibernate 将在插入实体时执行以下查询:

select next_val as id_val from sequence_note for update

错误堆栈跟踪:

{"time":"2021-12-20T06:11:07.335+00:00","@version":1,"message":"SQL Error: 1003, SQLState: 42000","logger_name":"org.hibernate.engine.jdbc.spi.SqlExceptionHelper","thread_name":"http-nio-8080-exec-2","level":"WARN","caller_class_name":"org.hibernate.engine.jdbc.spi.SqlExceptionHelper","caller_method_name":"logExceptions","caller_file_name":"SqlExceptionHelper.java","caller_line_number":137}
{"time":"2021-12-20T06:11:07.337+00:00","@version":1,"message":"SQL compilation error:
syntax error line 1 at position 45 unexpected 'for'.","logger_name":"org.hibernate.engine.jdbc.spi.SqlExceptionHelper","thread_name":"http-nio-8080-exec-2","level":"ERROR","caller_class_name":"org.hibernate.engine.jdbc.spi.SqlExceptionHelper","caller_method_name":"logExceptions","caller_file_name":"SqlExceptionHelper.java","caller_line_number":142}
{"time":"2021-12-20T06:11:12.428+00:00","@version":1,"message":"Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: error performing isolated work; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: error performing isolated work] with root cause","logger_name":"org.apache.catalina.core.ContainerBase.[Tomcat].[localhost].[/].[dispatcherServlet]","thread_name":"http-nio-8080-exec-2","level":"ERROR","stack_trace":"net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:
syntax error line 1 at position 45 unexpected 'for'.
at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionSub(SnowflakeUtil.java:127)
at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:67)
at net.snowflake.client.core.StmtUtil.pollForOutput(StmtUtil.java:442)
at net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:345)
at net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:487)
at net.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:198)
at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:135)
at net.snowflake.client.core.SFStatement.execute(SFStatement.java:781)
at net.snowflake.client.core.SFStatement.execute(SFStatement.java:677)
at net.snowflake.client.jdbc.SnowflakeStatementV1.executeQueryInternal(SnowflakeStatementV1.java:238)
at net.snowflake.client.jdbc.SnowflakePreparedStatementV1.executeQuery(SnowflakePreparedStatementV1.java:117)

那么，有什么方法可以从Springboot代码本身管理Id字段(唯一，自增，主键)的生成吗？

2022 年 3 月 1 日更新

感谢 Alexey Veleshko 的回答，我们通过对代码进行以下更改设法解决了此异常。

EmptyDialect 类现在看起来像这样:

public class EmptyDialect extends org.hibernate.dialect.Dialect {
     
    @Override
    public String getSelectSequenceNextValString(String sequenceName) {
        return sequenceName + ".nextVal";
    }

    @Override
    public String getSequenceNextValString(String sequenceName) {
        return "select " + getSelectSequenceNextValString(sequenceName);
    }

    @Override
    public boolean supportsSequences() {
        return true;
    }

    @Override
    public boolean supportsPooledSequences() {
        return true;
    }
  
}

在这里，我们覆盖了将查询以从中获取 nextVal 的方法底层数据库并将生成表的自动增量 ID。

但是，根据我们的用例，我们希望批量插入实体，即使服务的多个实例正在运行，它也应该始终为每个实体的 Id 生成一个唯一且自动递增的值。在这种情况下，当应用程序启动时，在插入实体时，Hibernate 将查询以从序列中获取 nextVal。现在将使用生成的 id 值插入一批实体，为了插入另一批，Hibernate 不会在 Snowflake 序列中查询 nextVal，而是会从其本地内存中获取最后一个值(最后生成的 nextVal + 插入数实体)。现在假设有多个应用程序实例正在运行并在 Snowflake 中插入实体。由于这些实例不会在每次插入时在数据库中查询 nextVal，因此这些实例可能在其本地内存中存储了相同的 nextVal，这将导致数据库中的 id 重复。

Answer 1

您可以使用序列而不是使用自动增量功能，然后您可以使用它们自己的 native 属性对其进行操作。

您可以在此处阅读有关序列及其建议用法的文档:

CREATE SEQUENCE — Snowflake Documentation

Using Sequences — Snowflake Documentation