cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

how to add an identity column to an existing table?

ramankr48
Contributor II

I have created a database called retail and inside database a table is there called sales_order. I want to create an identity column in the sales_order table, but while creating it I am getting an error.

1 ACCEPTED SOLUTION

Accepted Solutions

Thanks @Priya Ananthram​ , issue got solved

I used the same DBR

my job aborted issue got solved by using "try_cast" function while inserting the data from one table to the delta table. I used try_cast function for every column I was inserting.

View solution in original post

11 REPLIES 11

-werners-
Esteemed Contributor III

That is because you can't add an id column to an existing table.

Instead create a table from scratch and copy data:

CREATE TABLE tname_ (

<tname columns>,

id BIGINT GENERATED BY DEFAULT AS IDENTITY

);

INSERT INTO tname_ (<tname columns>) SELECT * FROM tname;

DROP TABLE tname;

ALTER TABLE tname_ RENAME TO tname;

while creating a new table I'm getting an error like this:

Error in SQL statement: AnalysisException: Cannot create table ('`spark_catalog`.`retaildb`.`sales_order1`'). The associated location ('dbfs:/user/hive/warehouse/retaildb.db/sales_order1') is not empty but it's not a Delta table

-werners-
Esteemed Contributor III

what databricks version do you use?

Only as from a certain version on, delta is the default format. So if you use an older release you should add 'using delta' (and I doubt in that case if the ID column will work)

I'm using community version

-werners-
Esteemed Contributor III

It is possible that it is not supported with the CE.

shan_chandra
Esteemed Contributor
Esteemed Contributor

@Raman Gupta​  - For the error mentioned

Error in SQL statement: AnalysisException: Cannot create table ('`spark_catalog`.`retaildb`.`sales_order1`'). The associated location ('dbfs:/user/hive/warehouse/retaildb.db/sales_order1') is not empty but it's not a Delta table

could you please manually clean up the data directory specified in the error message? using the below command and then try to create a table.

%scala 
dbutils.fs.rm("<path-to-directory>", true)

Can you please tell me why it is showing job aborted ? same identity column issue.

I'm able to create table but while inserting the data it's saying :

Error in SQL statement: SparkException: Job aborted.

error file is attached named: blocker5image.png

above error is from community version.

and in azure version also it is showing the error.

i.e :

org.apache.spark.SparkException: Job aborted.

PriyaAnanthram
Contributor III

I just did this on the community cluster

%sql

create or replace table picklist

( picklist_id      BIGINT not null GENERATED ALWAYS AS IDENTITY(start with 1 increment by 1),

 picklist_Name     string 

)

insert into picklist(picklist_Name) values('a');

insert into picklist(picklist_Name) values('b');

insert into picklist(picklist_Name) values('c');

select * from picklist

imageAlso check DESCRIBE to see where the delta table is saved

image.pngI have a feeling its the version of the DBR that your using

Could you use the latest and see if that helps you

PriyaAnanthram
Contributor III

My DBR image

Thanks @Priya Ananthram​ , issue got solved

I used the same DBR

my job aborted issue got solved by using "try_cast" function while inserting the data from one table to the delta table. I used try_cast function for every column I was inserting.

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!