cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
cancel
Showing results for 
Search instead for 
Did you mean: 

Forum Posts

nbakh
by New Contributor II
  • 7661 Views
  • 4 replies
  • 4 kudos

insert into a table with an identity column fails

i am trying to insert into a table with an identity column using a select query.However, if i include the identity column or ignore the identity column in my insert it throws errors. Is thee a way to insert into select * from a table if the insert t...

  • 7661 Views
  • 4 replies
  • 4 kudos
Latest Reply
karan_singh
New Contributor II
  • 4 kudos

Hi, Specify insert columns as below %sqlINSERT INTO demo_test (product_type, sales)SELECT product_type, sales FROM demo

  • 4 kudos
3 More Replies
Anonymous
by Not applicable
  • 7469 Views
  • 9 replies
  • 13 kudos

Resolved! MetadataChangedException

A delta lake table is created with identity column and I'm not able to load the data parallelly from four process. i'm getting the metadata exception error.I don't want to load the data in temp table . Need to load directly and parallelly in to delta...

  • 7469 Views
  • 9 replies
  • 13 kudos
Latest Reply
Anonymous
Not applicable
  • 13 kudos

Thanks @Hubert Dudek​ 

  • 13 kudos
8 More Replies
Rubens
by New Contributor II
  • 1294 Views
  • 1 replies
  • 3 kudos

how to alter a column into an IDENTITY column

Here's me use case: I'm migrating out of an old DWH, into Databricks. When moving dimension tables into Databricks, I'd like old SKs (surrogate keys) to be maintained, while creating the SKs column as an IDENTITY column, so new dimension values get a...

  • 1294 Views
  • 1 replies
  • 3 kudos
Latest Reply
Anonymous
Not applicable
  • 3 kudos

Hi @Ronen Levi​ Great to meet you, and thanks for your question! Let's see if your peers in the community have an answer to your question. Thanks.

  • 3 kudos
Leszek
by Contributor
  • 1467 Views
  • 1 replies
  • 1 kudos

IDENTITY column duplication when using BY DEFAULT parameter

Hi, I created delta table with identity column using this syntax:Id BIGINT GENERATED BY DEFAULT AS IDENTITYMy steps:1) Created table with Id using syntax above.2) Added two rows with Id = 1 and Id = 2 (BY DEFAULT allows to do that).3) Run Insert (wit...

image.png
  • 1467 Views
  • 1 replies
  • 1 kudos
Latest Reply
dileep_vikram
New Contributor II
  • 1 kudos

Use below alter command to sync the identity column.alter table table_name change column col_name sync identity

  • 1 kudos
bluesky
by New Contributor II
  • 1422 Views
  • 2 replies
  • 1 kudos

Identity error Spark Sql:not enough data columns;target has 3 but the inserted data has 2, it's the identity column which is missing here

While inserting into target table i am getting an error '"not enough data columns;target has 3 but the inserted data has 2" but it's the identity column which is the 8th column ".insert into table A(col 1,col 2,col3)select col2,col3from table Bjoin t...

  • 1422 Views
  • 2 replies
  • 1 kudos
Latest Reply
Anonymous
Not applicable
  • 1 kudos

Hi @sky blue​ Hope all is well! Just wanted to check in if you were able to resolve your issue and would you be happy to share the solution or mark an answer as best? Else please let us know if you need more help. We'd love to hear from you.Thanks!

  • 1 kudos
1 More Replies
Dataengineer_mm
by New Contributor
  • 1574 Views
  • 2 replies
  • 1 kudos

Surrogate key using identity column.

I want to create a surrogate in the delta table And i used the identity column id-Generated as DefaultCan i insert rows into the delta table using only spark.sql like Insert query ? or i can also use write delta format options? If i use the df.write ...

  • 1574 Views
  • 2 replies
  • 1 kudos
Latest Reply
Kaniz
Community Manager
  • 1 kudos

Hi @Menaka Murugesan​(Customer)​, We haven’t heard from you since the last response from @Nandini N​  (Customer)​, and I was checking back to see if her suggestions helped you.Or else, If you have any solution, please share it with the community, as ...

  • 1 kudos
1 More Replies
kskistad
by New Contributor III
  • 1698 Views
  • 1 replies
  • 2 kudos

Resolved! Identity column in DLT using Python

How would I implement the Identity column in Delta Live Tables using Python syntax?GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( [ START WITH start ] [ INCREMENT BY step ] ) ] 

  • 1698 Views
  • 1 replies
  • 2 kudos
Latest Reply
LaurentLeturgez
New Contributor III
  • 2 kudos

Hi @Kory Skistad​ Please find below the table schema definition to use in a python dlt pipeline. You can see it mentions the identity column definition. @dlt.table( comment="Raw data on sales", schema=""" customer_id STRING, customer_name STR...

  • 2 kudos
lizou
by Contributor II
  • 2095 Views
  • 4 replies
  • 6 kudos

Resolved! Identity column definition lost using save as table

I found an issue:For a table with an identity column defined.when the table column is renamed using this method, the identity definition will be removed. That means using an identity column in a table requires extra attention to check whether the ide...

  • 2095 Views
  • 4 replies
  • 6 kudos
Latest Reply
lizou
Contributor II
  • 6 kudos

try to avoid reload table, I found we can upgrade table version, and use rename column commandALTER TABLE test_id2 SET TBLPROPERTIES (  'delta.columnMapping.mode' = 'name',  'delta.minReaderVersion' = '2',  'delta.minWriterVersion' = '6')ALTER TABLE ...

  • 6 kudos
3 More Replies
ramankr48
by Contributor II
  • 11976 Views
  • 11 replies
  • 2 kudos

Resolved! how to add an identity column to an existing table?

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.

  • 11976 Views
  • 11 replies
  • 2 kudos
Latest Reply
PriyaAnanthram
Contributor III
  • 2 kudos

My DBR

  • 2 kudos
10 More Replies
jasperputs
by New Contributor III
  • 4000 Views
  • 6 replies
  • 3 kudos

Resolved! Add Identity Column to Existing Table

Hello everyone. I am working with tables that need an identity column. I currently have a view in which I cast the different columns to the data type that I want. Now I want the result of this view to be inserted or merged into a table. The schema of...

image
  • 4000 Views
  • 6 replies
  • 3 kudos
Latest Reply
ramankr48
Contributor II
  • 3 kudos

Hello @Jasper Puts​ how did you solve this issue of creating a identity column to existing table.I'm also getting the same error as you got.

  • 3 kudos
5 More Replies
VaDim
by New Contributor III
  • 3633 Views
  • 8 replies
  • 3 kudos

Resolved! Are MERGE INTO inserts supported when the delta table has an identity column ?

I can't seem to make it work as I keep getting:DeltaInvariantViolationException: NOT NULL constraint violated for column: dl_id.

  • 3633 Views
  • 8 replies
  • 3 kudos
Latest Reply
byrdman
New Contributor III
  • 3 kudos

if you are using 'delta.columnMapping.mode' = 'name' on your table i could not get it to work, without that line .. for the not matched .. WHEN NOT MATCHED  THEN INSERT (columnname,columnName2) values(columnname,columnName2)WHEN MATCHED Then UPDAT...

  • 3 kudos
7 More Replies
auser85
by New Contributor III
  • 1395 Views
  • 2 replies
  • 1 kudos

How to reset the IDENTITY column count?

After accumulating many updates to a delta table,like,keyExample bigint GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),my identity column values are in the hundreds of millions. Is there any way that I can reset this value through vacuumi...

  • 1395 Views
  • 2 replies
  • 1 kudos
Latest Reply
Anonymous
Not applicable
  • 1 kudos

Hey there @Andrew Fogarty​ Does @Werner Stinckens​'s response answer your question? If yes, would you be happy to mark it as best so that other members can find the solution more quickly? Else please let us know if you need more help. Thanks!

  • 1 kudos
1 More Replies
TheOptimizer
by Contributor
  • 6630 Views
  • 6 replies
  • 8 kudos

Resolved! How to create delta table with identity column.

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

Capture
  • 6630 Views
  • 6 replies
  • 8 kudos
Latest Reply
lucas_marchand
New Contributor III
  • 8 kudos

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.

  • 8 kudos
5 More Replies
Labels