topic Re: Issue with inserting multiple rows in Delta table with identity column in Data Engineering

Issue with inserting multiple rows in Delta table with identity column

bb2312 — Tue, 21 Feb 2023 00:44:59 GMT

Running DBR 11.3 / Azure Databricks

Table definition below:

%sql
CREATE OR REPLACE TABLE demo2 (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY,
  product_type STRING,
  sales BIGINT
)
USING DELTA
LOCATION '/folderlocation/'
TBLPROPERTIES (
'delta.columnMapping.mode' = 'name',
   'delta.minReaderVersion' = '2',
   'delta.minWriterVersion' = '5')

Inserting 1 row works fine

%sql
INSERT INTO demo2 (product_type, sales)
VALUES ("cell", 130000);

On attempting to insert a new row I get this error:

"AnalysisException: Column id is not specified in INSERT"

I can insert columns explicitly as follows:

%sql
INSERT INTO demo2 (id,product_type, sales)
VALUES (3,"batt", 130000);

I can even insert duplicate column values like so

I have tried changing the identity definition to "GENERATED ALWAYS AS IDENTITY" however get the same result. Am I doing this incorrectly or missing something?

Re: Issue with inserting multiple rows in Delta table with identity column

-werners- — Tue, 21 Feb 2023 12:28:00 GMT

https://github.com/delta-io/delta/issues/1215

It is an open issue.

You can enter your own values because you use BY DEFAULT instead of ALWAYS.

Re: Issue with inserting multiple rows in Delta table with identity column

bb2312 — Fri, 10 Mar 2023 04:38:49 GMT

Just updating it is possible this issue has now been addressed.

As before working on Azure Databricks 11.3 DBR

Inserting into managed table:

Also appears to be addressed for autoloader insertion into unmanaged table