topic Using current_timestamp as a default value in a delta table in Data Engineering

Using current_timestamp as a default value in a delta table

deng77 — Tue, 17 Jan 2023 19:50:06 GMT

I want to add a column to an existing delta table with a timestamp for when the data was inserted. I know I can do this by including current_timestamp with my SQL statement that inserts into the table.

Is it possible to add a column to an existing delta table with a default value of current_timestamp so I don't have to include the timestamp when writing data to the table? I have tried doing it but it doesn't seem to populate the column when I insert into the table.

Here is what I have tried so far:

ALTER TABLE tableName
ADD COLUMN InsertUTC timestamp DEFAULT current_timestamp

Re: Using current_timestamp as a default value in a delta table

Hubert-Dudek — Tue, 17 Jan 2023 20:15:49 GMT

It was introduced in 11.2, and there is a note:

This behavior is supported for CSV, JSON, Orc, and Parquet data sources.

So it seems not yet for Delta. As for the parquet, I've just tested it, and it is ok. So I hope that support for Delta will be soon added.

Re: Using current_timestamp as a default value in a delta table

deng77 — Tue, 17 Jan 2023 20:46:07 GMT

Thanks for the response Hubert, hopefully the functionality for delta tables will be added soon.

Re: Using current_timestamp as a default value in a delta table

elgeo — Fri, 10 Mar 2023 09:38:58 GMT

Hello - Just an update from the latest office hours. They mentioned that default value is available for Delta tables as well starting from release 12.2 Databricks Runtime 11.3 LTS | Databricks on AWS. However, I was able to set it up only in the following way. It wasn't allowed in the Create statement directly.

Re: Using current_timestamp as a default value in a delta table

pvignesh92 — Fri, 10 Mar 2023 10:38:41 GMT

-- Alter the table to use the GENERATED ALWAYS functionality for the created_at column
ALTER TABLE example_table
ADD COLUMN created_at TIMESTAMP GENERATED ALWAYS AS CURRENT_TIMESTAMP();

@Michael Burch Hi , Did you try using GENERATED ALWAYS feature. That can do satisfy your requirements I believe. With this, you don't need to insert a separate value for this column every time you load data into the table.

Re: Using current_timestamp as a default value in a delta table

deng77 — Fri, 10 Mar 2023 17:55:28 GMT

That doesn't work for me, I get the following error:

[PARSE_SYNTAX_ERROR] Syntax error at or near 'GENERATED'(line 2, pos 32).

It sounds like delta lake generated columns have to be based on other columns in the table:

Delta Lake supports generated columns which are a special type of column whose values are automatically generated based on a user-specified function over other columns in the Delta table.

https://docs.databricks.com/delta/generated-columns.html

Re: Using current_timestamp as a default value in a delta table

deng77 — Fri, 10 Mar 2023 18:11:38 GMT

Thanks for this, I tried it out on my end with a cluster running DBR 12.2 LTS and I was able to get it to work.

CREATE TABLE test (id int, time timestamp);
ALTER TABLE test
SET TBLPROPERTIES('delta.minReaderVersion' = '1', 'delta.minWriterVersion'='7');
ALTER TABLE test SET TBLPROPERTIES('delta.feature.allowColumnDefaults' = 'enabled');
ALTER TABLE test ALTER COLUMN time SET DEFAULT CURRENT_TIMESTAMP()

Re: Using current_timestamp as a default value in a delta table

Danpeco — Fri, 28 Apr 2023 16:05:16 GMT

My time column would have the default value of current_timestamp() if I went to the table properties after doing the identical action in the same DBR 12.2 LTS runtime.

However, it would fail if I attempted to add data to a target table that had one more column (the default time column) than the values I was adding to it : Cannot write to 'spark_catalog.gold.xxxxxxx', not enough data columns; target table has 19 column(s) but the inserted data has 18 column(s).

Do you know why this might be occurring?

Re: Using current_timestamp as a default value in a delta table

deng77 — Fri, 28 Apr 2023 20:05:02 GMT

How are you adding values to the table? I have encountered that issue previously when I didn't specify columns in an INSERT statement where the table I was writing to had an identity column that is automatically generated.

Re: Using current_timestamp as a default value in a delta table

iamkt23 — Wed, 27 Dec 2023 11:16:32 GMT

Hi All,

I am unable to run this
ALTER TABLE TABLE_NAME alter column column_name set default 'value'
as shown in the solution.

I was able to run the previous queries. But here, I am getting an error.

Can someone suggest a solution, I have a table with 20+ columns and I want to set '0' as default for majority of them.

Re: Using current_timestamp as a default value in a delta table

iamkt23 — Fri, 29 Dec 2023 06:39:14 GMT

Did this work for you in a databricks notebook?

Re: Using current_timestamp as a default value in a delta table

Vaibhav1000 — Thu, 30 May 2024 14:22:19 GMT

Can you please provide information on the additional expenses related to using this feature compared to not utilizing it at all?