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: 

Forum Posts

Anonymous
by Not applicable
  • 11634 Views
  • 7 replies
  • 11 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...

  • 11634 Views
  • 7 replies
  • 11 kudos
Latest Reply
seans
New Contributor III
  • 11 kudos

I recently ran into this MetadataChangedException. Watching the video @Hubert Dudek​ posted it's pretty clear what is going on: object storage folks not thinking like someone who builds relational database engines built it. That's to be expected. Dat...

  • 11 kudos
6 More Replies
nbakh
by New Contributor II
  • 10217 Views
  • 3 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...

  • 10217 Views
  • 3 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
2 More Replies
Rubens
by New Contributor II
  • 2007 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...

  • 2007 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
  • 2292 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
  • 2292 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
  • 2205 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...

  • 2205 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
  • 2250 Views
  • 1 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 ...

  • 2250 Views
  • 1 replies
  • 1 kudos
Latest Reply
NandiniN
Honored Contributor
  • 1 kudos

Hello @Menaka Murugesan​ ,If you are using the identity column, I believe you would have created the table as below, (starts with value 1 and step 1)CREATE TABLE my_table ( id INT IDENTITY (1, 1) PRIMARY KEY, value STRING )You can insert values i...

  • 1 kudos
kskistad
by New Contributor III
  • 2421 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 ] ) ] 

  • 2421 Views
  • 1 replies
  • 2 kudos
Latest Reply
LaurentLeturgez
Contributor
  • 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
  • 3103 Views
  • 3 replies
  • 5 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...

  • 3103 Views
  • 3 replies
  • 5 kudos
Latest Reply
lizou
Contributor II
  • 5 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 ...

  • 5 kudos
2 More Replies
ramankr48
by Contributor II
  • 17112 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.

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

My DBR

  • 2 kudos
10 More Replies
jasperputs
by New Contributor III
  • 6118 Views
  • 4 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
  • 6118 Views
  • 4 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
3 More Replies
VaDim
by New Contributor III
  • 5737 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.

  • 5737 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
  • 2096 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...

  • 2096 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
  • 9073 Views
  • 5 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
  • 9073 Views
  • 5 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
4 More Replies
Labels