topic Re: how to add an identity column to an existing table? in Data Engineering

how to add an identity column to an existing table?

ramankr48 — Tue, 13 Sep 2022 07:21:45 GMT

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.

Re: how to add an identity column to an existing table?

-werners- — Tue, 13 Sep 2022 08:57:24 GMT

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;

Re: how to add an identity column to an existing table?

ramankr48 — Tue, 13 Sep 2022 11:29:23 GMT

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

Re: how to add an identity column to an existing table?

-werners- — Tue, 13 Sep 2022 11:41:12 GMT

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)

Re: how to add an identity column to an existing table?

ramankr48 — Tue, 13 Sep 2022 11:44:27 GMT

I'm using community version

Re: how to add an identity column to an existing table?

-werners- — Tue, 13 Sep 2022 11:54:10 GMT

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

Re: how to add an identity column to an existing table?

ramankr48 — Wed, 14 Sep 2022 07:46:07 GMT

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: blocker5

Re: how to add an identity column to an existing table?

ramankr48 — Wed, 14 Sep 2022 09:03:10 GMT

above error is from community version.

and in azure version also it is showing the error.

i.e :

org.apache.spark.SparkException: Job aborted.

Re: how to add an identity column to an existing table?

PriyaAnanthram — Wed, 14 Sep 2022 22:58:16 GMT

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

Also check DESCRIBE to see where the delta table is saved

I have a feeling its the version of the DBR that your using

Could you use the latest and see if that helps you

Re: how to add an identity column to an existing table?

PriyaAnanthram — Wed, 14 Sep 2022 23:46:17 GMT

My DBR

Re: how to add an identity column to an existing table?

ramankr48 — Thu, 15 Sep 2022 06:44:47 GMT

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.

Re: how to add an identity column to an existing table?

shan_chandra — Fri, 23 Sep 2022 21:46:28 GMT

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