1

Trying to add a Delta Lake generated column using SQL, and then partition by it. I am following the Databricks documentation which provide the following example:

CREATE TABLE events(
eventId BIGINT,
data STRING,
eventType STRING,
eventTime TIMESTAMP,
eventDate date GENERATED ALWAYS AS (CAST(eventTime AS DATE))
)
PARTITIONED BY (eventType, eventDate)

My question is: how can I alter this code for an existing table? What I want to accomplish is to do something like GENERATED ALWAYS AS (CAST(eventTime AS DATE)) to an existing table and then partition by this generated column.

This question is related, but does not focus on using GENERATED.

Samuel
  • 2,895
  • 4
  • 30
  • 45
  • i don't follow, you need additional columns? or you want to ALTER TABLE – nbk Nov 17 '22 at 17:02
  • I want to generate a new column to an existing table using the technique provided in the Databricks doc. – Samuel Nov 17 '22 at 17:05
  • 1
    Databricks doesn't support generated on an alter table, so why not make a new table and copy all data – nbk Nov 17 '22 at 17:17
  • I don't care about ALTER TABLE, I just want to generate a new column using DATE() and HOUR() functions, likely involving CAST(), but I'm not sure. – Samuel Nov 17 '22 at 17:22
  • 1
    the generated column feature is only in a preview and so will still take some time till it reaches production status – nbk Nov 17 '22 at 17:24
  • That doesn't matter for my use case. – Samuel Nov 17 '22 at 17:24
  • @nbk - You say *"Databricks doesn't support generated on an alter table"* above, could you link some documentation regarding the limits of generated columns? The official databricks docs I can find are very sparse on the subject. – iamdave Jan 09 '23 at 09:31
  • you have to see the documentation https://docs.databricks.com/sql/language-manual/sql-ref-syntax-ddl-alter-table.html it shows you all supported commands if there is no generated in the documentation , it is not supported – nbk Jan 09 '23 at 13:16

0 Answers0