How to generate Unique, AutoIncremented Id using Hibernate/Spring JPA for Snowflake table?

user_15645930 0
Viewed 827

I am quite a newbie at Snowflake and instead of implementing the Snowflake JDBCDriver, we decided to use the Hibernate/Spring-Data JPA for that as that was more convenient to use. We came through this post: Has anybody built an application using Java Spring Framework that connects to Snowflake on Snowflake Community and checked whether our use-case was getting fulfilled by that or not.

As per our use case, our Model class looks like this and we have kept the Empty Dialect part and other configurations the same as described in the link.


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() {
    }
}

And we created the Table in Snowflake by using this query :

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)   
);

The above code works as expected and generates the auto-incremented id as a primary key. We also tried to run multiple instances of our service and since the unique constraint is not enforced by Snowflake, we ran into an issue of having duplicate id values. (There were multiple sources of Data Insertion for a single table.)

Regarding the dialect, we couldn't find any Hibernate Dialect for Snowflake so we used the same dialect details as described in the reference link. We created the EmptyDialect class and gave its path in the properties file.

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

Properties file:

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

We have tried all the ID generation strategies like IDENTITY, SEQUENCE, AUTO, etc. but received exceptions which might be due to having no separate Snowflake Dialect. Will add the stack trace of the errors if required.

  • Sequence Approach:

We created the sequence through the following query and accordingly also made changes in the Table creation query and annotations.

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 will execute the following query at the time of insertion of Entity:

select next_val as id_val from sequence_note for update

Error Stack Trace :

{"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)

So, is there any way to manage the generation of the Id field(unique,auto-increment, primary key) from the Springboot code itself?

UPDATE ON 03/01/2022

Thanks to Alexey Veleshko's answer, we managed to resolve this Exception by making following changes in our code.

EmptyDialect Class now looks like this :

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;
    }
  
}

Here, we are overriding the methods which will query to get the nextVal from the underlying Database and will generate the AutoIncremented Id for the Table.

But, as per our use-case we want the insertion of entities in a batch and even if multiple instances of a service are running, it should always generate an unique and AutoIncremented value for Id for each entity. In this case, when the application starts, at the time of Insertion of entity, Hibernate will query to fetch the nextVal from sequence. And a batch of entities will be inserted with the generated id values now, for the insertion of another batch, Hibernate will not query in Snowflake sequence for nextVal but will fetch the last value from its local memory(last generated nextVal + no. of inserted entities). Now suppose there are multiple instances of the application running and inserting the entities in Snowflake. As these instances will not query in database for the nextVal on each insert, so these instances might have same nextVal stored in their local memory which would result in duplicacy of ids in database.

spring-data-jpa snowflake-cloud-data-platform auto-increment hibernate java
2 Answers
user_164135120

I guess the gist of the issue is that you want to perform batch inserts through multiple application instances on the same snowflake table having unique and auto-incremented primary key(id).

Snowflake uses SEQUENCE strategy to auto increment the primary key id.

Now as mentioned by you and Alexey Veleshko, we can manipulate sequence query to "select sequence_note.nextVal" in Dialect class by overriding some of the methods. But as this query will be fired by your application only once at bootup, it won't solve the issue while there are multiple application instances that are trying to insert batch of data into the same table.

So, what we need here is to execute:

"select sequence_note.nextVal"

query before each insert call to have the latest "id" value for each row. We can achieve this kind of behaviour by executing it manually.

entitiesToSave.stream().filter(Objects::nonNull).forEach(entity -> {
    int nextVal = sequenceRepo.getNextVal();
    entity.setId(Long.valueOf(nextVal));
    entityRepo.save(entity);
});



 @Query(nativeQuery = true, value = "select sequence_note.nextVal")
    int getNextVal();

I know this isn't a optimal solution, as you would need to execute an extra query before every insert call but looking at your use-case I think this might be your last resort. Another solution can be to use below query:

insert into note (user_id, content) values (1, "testContent");

As your table generation query is:

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)   
);

It will directly manage the id generation for you (However if you strictly wants to use Spring JPA this would not be appropriate for you to use)

user_178372220
Related