How to create delta table with identity column.

TheOptimizer
Contributor

‎06-01-2022 11:10 AM

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'
;

Capture

Preview file
33 KB

TheOptimizer
Contributor

‎06-01-2022 11:58 AM

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.

anu1
New Contributor II
In response to TheOptimizer

‎07-02-2022 09:49 AM

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

0 Kudos

TheOptimizer
Contributor
In response to anu1

‎07-02-2022 10:04 AM

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.

lucas_marchand
New Contributor III

‎06-30-2022 11:03 AM

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.

View solution in original post

TheOptimizer
Contributor
In response to lucas_marchand

‎07-02-2022 09:59 AM

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!