Databricks truncate delta table restart identity 1

Question

We are created SQL notebook in Databricks and we are trying to develop onetime script. we have to truncate and load the data every time and the table sequence id generated always start with 1. if we do truncate and load the data. the sequence of id taking last insert value, means not able to restart 1. how do we restart 1 value if we try to insert the data after truncate command.

actual table created,

CREATE TABLE IF NOT EXISTS schema.tablename(
SerialNo BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) COMMENT "SerialNo - auto generated sequence id",
adlsSourceMountPath STRING COMMENT "location",
adlsTargetMountPath STRING COMMENT "location",
tableSchemaName STRING COMMENT "tableSchemaName - adls target delta table schema name",
DescriptionDetails STRING COMMENT "DescriptionDetails",
CreateDate DATE COMMENT "CreateDate - generate current date",
Active BOOLEAN COMMENT "Active - check active status of connections"
)USING DELTA
LOCATION "location/path"
COMMENT "descri"

we tried below command but throwing error.

TRUNCAT TABLE TABLENAME RESTART IDENTITY;

please suggest.

TRUNCATE TABLE TABLENAME RESTART IDENTITY; -- this is right syntax ? — Developer KE, Jan 06 '23 at 08:11
error com.databricks.backend.common.rpc.DatabricksExceptions$SQLExecutionException: org.apache.spark.sql.catalyst.parser.ParseException: mismatched input 'RESTART' expecting {, ';'}(line 1, pos 39) — Developer KE, Jan 06 '23 at 08:13
please advice if i use TRUNCATE TABLE TABLENAME RESTART IDENTITY; — Developer KE, Jan 06 '23 at 08:13
Does this answer your question? [DataBricks: Any way to reset the Generated IDENTITY column?](https://stackoverflow.com/questions/72400397/databricks-any-way-to-reset-the-generated-identity-column) — Alex Ott, Jan 06 '23 at 09:52

score 0 · Answer 1 · answered Jan 06 '23 at 09:56

There is no such thing as "restart identity" in the truncate command - you can check documentation for it.

If you just have a normal Delta Lake table, then you can either use restore to version 0 or use "create or replace table" as it's described in the following answers.

If you're using Delta Live Tables (looking into tags), then you may need to perform a refresh that should reset identity column as well.

Databricks truncate delta table restart identity 1

1 Answers1