topic Re: How to create delta table with identity column. in Data Engineering

How to create delta table with identity column.

TheOptimizer — Wed, 01 Jun 2022 18:10:35 GMT

I'm sure this is probably some oversight on my part, but I don't see it. I'm trying to create a delta table with an identity column. I've tried every combination of the syntax I can think of.

%sql
create or replace table IDS.picklist
( picklist_id            BIGINT not null GENERATED ALWAYS AS IDENTITY,
  picklist_Name          string not null comment 'The unique name of the picklist.  Should be indicative of source and specific to the data requested.',
)
using delta
location 'dbfs:/mnt/dl_ids/databricks_database/picklist.delta'
;

Re: How to create delta table with identity column.

TheOptimizer — Wed, 01 Jun 2022 18:58:25 GMT

I figured it out but am still somewhat confused. I was originally trying to create the table using sql magic commend in a notebook. It worked when I copied the sql into a databricks sql query.

Re: How to create delta table with identity column.

lucas_marchand — Thu, 30 Jun 2022 18:03:28 GMT

I was also having this same error and my cluster was running Databricks Runtime Version 9.1 so I changed it to 11.0 and it worked.

Re: How to create delta table with identity column.

anu1 — Sat, 02 Jul 2022 16:49:44 GMT

Could you please explain clearly on " It worked when I copied the sql into a databricks sql query." how this is done.

Re: How to create delta table with identity column.

TheOptimizer — Sat, 02 Jul 2022 16:59:58 GMT

Interesting. I would then guess that what I experienced was a bug, and that the upgrade fixed this. I tend to stay on the LTS, so I am currently running 9.1 DBR. Thanks for the good news!

Re: How to create delta table with identity column.

TheOptimizer — Sat, 02 Jul 2022 17:04:23 GMT

Sure thing. Originally, I was trying to run my creation in a %sql magic cell of a notebook. The 2nd attemp (which worked) I pasted the DDL in the SQL environment of databricks. Instead of being in Data and Engineering envinronment, select SQL. Hope this helps, best I have.