Use Delta Lake generated columns
Preview
This feature is in Public Preview.
Note
This feature is available on Databricks Runtime 8.3 and above.
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. When you write to a table with generated columns and you do not explicitly provide values for them, Delta Lake automatically computes the values. For example, you can automatically generate a date column (for partitioning the table by date) from the timestamp column; any writes into the table need only specify the data for the timestamp column. However, if you explicitly provide values for them, the values must satisfy the constraint (<value> <=> <generation expression>) IS TRUE
or the write will fail with an error.
Important
Tables created with generated columns have a higher table writer protocol version than the default. See How does Databricks manage Delta Lake feature compatibility? to understand table protocol versioning and what it means to have a higher version of a table protocol version.
Learn how to use generated columns
The following example shows how to create a table with generated columns:
CREATE TABLE default.people10m (
id INT,
firstName STRING,
middleName STRING,
lastName STRING,
gender STRING,
birthDate TIMESTAMP,
dateOfBirth DATE GENERATED ALWAYS AS (CAST(birthDate AS DATE)),
ssn STRING,
salary INT
)
DeltaTable.create(spark) \
.tableName("default.people10m") \
.addColumn("id", "INT") \
.addColumn("firstName", "STRING") \
.addColumn("middleName", "STRING") \
.addColumn("lastName", "STRING", comment = "surname") \
.addColumn("gender", "STRING") \
.addColumn("birthDate", "TIMESTAMP") \
.addColumn("dateOfBirth", DateType(), generatedAlwaysAs="CAST(birthDate AS DATE)") \
.addColumn("ssn", "STRING") \
.addColumn("salary", "INT") \
.execute()
DeltaTable.create(spark)
.tableName("default.people10m")
.addColumn("id", "INT")
.addColumn("firstName", "STRING")
.addColumn("middleName", "STRING")
.addColumn(
DeltaTable.columnBuilder("lastName")
.dataType("STRING")
.comment("surname")
.build())
.addColumn("lastName", "STRING", comment = "surname")
.addColumn("gender", "STRING")
.addColumn("birthDate", "TIMESTAMP")
.addColumn(
DeltaTable.columnBuilder("dateOfBirth")
.dataType(DateType)
.generatedAlwaysAs("CAST(dateOfBirth AS DATE)")
.build())
.addColumn("ssn", "STRING")
.addColumn("salary", "INT")
.execute()
Generated columns are stored as if they were normal columns. That is, they occupy storage.
The following restrictions apply to generated columns:
A generation expression can use any SQL functions in Spark that always return the same result when given the same argument values, except the following types of functions:
User-defined functions.
Aggregate functions.
Window functions.
Functions returning multiple rows.
For Databricks Runtime 9.1 and above,
MERGE
operations support generated columns when you setspark.databricks.delta.schema.autoMerge.enabled
to true.
In Databricks Runtime 8.4 and above with Photon support, Delta Lake can generate partition filters for a query whenever a partition column is defined by one of the following expressions:
CAST(col AS DATE)
and the type ofcol
isTIMESTAMP
.YEAR(col)
and the type ofcol
isTIMESTAMP
.Two partition columns defined by
YEAR(col), MONTH(col)
and the type ofcol
isTIMESTAMP
.Three partition columns defined by
YEAR(col), MONTH(col), DAY(col)
and the type ofcol
isTIMESTAMP
.Four partition columns defined by
YEAR(col), MONTH(col), DAY(col), HOUR(col)
and the type ofcol
isTIMESTAMP
.SUBSTRING(col, pos, len)
and the type ofcol
isSTRING
DATE_FORMAT(col, format)
and the type ofcol
isTIMESTAMP
.You can only use date formats with the following patterns:
yyyy-MM
andyyyy-MM-dd-HH
.In Databricks Runtime 10.4 LTS and above, you can also use the following pattern:
yyyy-MM-dd
.
If a partition column is defined by one of the preceding expressions, and a query filters data using the underlying base column of a generation expression, Delta Lake looks at the relationship between the base column and the generated column, and populates partition filters based on the generated partition column if possible. For example, given the following table:
CREATE TABLE events(
eventId BIGINT,
data STRING,
eventType STRING,
eventTime TIMESTAMP,
eventDate date GENERATED ALWAYS AS (CAST(eventTime AS DATE))
)
PARTITIONED BY (eventType, eventDate)
If you then run the following query:
SELECT * FROM events
WHERE eventTime >= "2020-10-01 00:00:00" <= "2020-10-01 12:00:00"
Delta Lake automatically generates a partition filter so that the preceding query only reads the data in partition date=2020-10-01
even if a partition filter is not specified.
As another example, given the following table:
CREATE TABLE events(
eventId BIGINT,
data STRING,
eventType STRING,
eventTime TIMESTAMP,
year INT GENERATED ALWAYS AS (YEAR(eventTime)),
month INT GENERATED ALWAYS AS (MONTH(eventTime)),
day INT GENERATED ALWAYS AS (DAY(eventTime))
)
PARTITIONED BY (eventType, year, month, day)
If you then run the following query:
SELECT * FROM events
WHERE eventTime >= "2020-10-01 00:00:00" <= "2020-10-01 12:00:00"
Delta Lake automatically generates a partition filter so that the preceding query only reads the data in partition year=2020/month=10/day=01
even if a partition filter is not specified.
You can use an EXPLAIN clause and check the provided plan to see whether Delta Lake automatically generates any partition filters.
Use identity columns in Delta Lake
Important
Declaring an identity column on a Delta table disables concurrent transactions. Only use identity columns in use cases where concurrent writes to the target table are not required.
Delta Lake identity columns are a type of generated column that assign unique values for each record inserted to a table. You can optionally specify a starting value and a step size, which can include both positive and negative values. Identity columns only support the BIGINT
type, and operations fail if the assigned value exceeds the range supported by BIGINT
.
Values assigned by identity columns are unique and increment in the direction of the specified step, and in multiples of the specified step size, but are not guaranteed to be contiguous.
Users can optionally specify values for identity columns during insert operations, but this can be disabled by using the ALWAYS
keyword during identity column declaration.
Tables cannot be partitioned by an identity column, and you cannot perform update operations on identity columns.
You cannot add identity columns to an existing table. To create a table with identity columns, see CREATE TABLE [USING].
To learn about syncing identity column values with data, see ALTER TABLE.