DataBricks: Any way to reset the Generated IDENTITY column?

Question

Delta tables have the capability to generate an identity column, like so:

CREATE TABLE TestMe (
  pKey bigint GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
  id bigint,
  commentary string
  )
 USING DELTA
 OPTIONS (PATH "/mnt/Delta/Testing/TestMe")

However, there seems to be no way to reset the counter without manually editing the changelog files which seems risky.

If I want to truncate the table and place a new set of data in there, how can I do that without slowly accumulating an identity column in the hundreds of millions / billions (every data insert the counter just goes up and never resets)?

Sorry, I'm understanding your actual requirement. Why do you want reset the identify value? — Ganesh Chandrasekaran, May 29 '22 at 13:49
Lets say I truncate the table or vacuum it to remove all the data. When I add fresh data to it, why would I want the identity column to start at 1 billion (or whatever it was last at)? — John Stud, May 29 '22 at 18:23

score 2 · Accepted Answer · answered May 29 '22 at 19:46

2

Please try this way

CREATE TABLE TestMe (
  pKey bigint GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
  id bigint,
  commentary string
);
  
insert into TestMe (id,commentary) values (1,'a'),(2,'b'),(3,'c');
  
select * from TestMe;
  
truncate table TestMe;

describe history TestMe;
  
Restore Table TestMe to version as of 0;

insert into TestMe (id,commentary) values (1,'a'),(2,'b'),(3,'c');

answered May 29 '22 at 19:46

Ganesh Chandrasekaran

1,578
12
17

Brilliant! I did not try to restore and start over! – John Stud May 29 '22 at 20:09

score 1 · Answer 2 · answered May 31 '22 at 14:31

1

Found another way to solve

CREATE or REPLACE TABLE TestMe (
  pKey bigint GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
  id bigint,
  commentary string
);
  
insert into TestMe (id,commentary) values (1,'a'),(2,'b'),(3,'c');

select * from TestMe;

-- Rerun Create or Replace resets the identity

CREATE or REPLACE TABLE TestMe (
  pKey bigint GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
  id bigint,
  commentary string
);

answered May 31 '22 at 14:31

Ganesh Chandrasekaran

1,578
12
17

it's better to add this to your accepted answer - just edit it & add as an alternative – Alex Ott May 31 '22 at 14:49
This is probably the better long run solution. I am running into issues now with the first answer where version0 no longer exists in my table history, likely due to vacuum defaults? – John Stud Jul 03 '22 at 11:25

DataBricks: Any way to reset the Generated IDENTITY column?

2 Answers2

Linked