cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
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
Honored Contributor III
Honored Contributor III

@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.

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.