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 create delta table with identity column.

TheOptimizer
Contributor

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

1 ACCEPTED SOLUTION

Accepted Solutions

lucas_marchand
New Contributor III

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

5 REPLIES 5

TheOptimizer
Contributor

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

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

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

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.

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!

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group