Delta Lake generated columns
Important
This feature is in Public Preview.
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 Azure 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.
Create a table with generated columns
The following example shows how to create a table with generated columns:
SQL
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
)
Python
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()
Scala
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.
Delta Lake can generate partition filters for a query whenever a partition column is defined by one of the following expressions:
Note
Photon is required in Databricks Runtime 10.4 LTS and below. Photon is not required in Databricks Runtime 11.3 LTS and above.
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
.
- You can only use date formats with the following patterns:
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 assigns unique values for each record inserted into a table. The following example shows the basic syntax for declaring an identity column during a create table statement:
CREATE TABLE table_name (
identity_col BIGINT GENERATED BY DEFAULT AS IDENTITY,
other_column ...)
To see all syntax options for creating tables with identity columns, see CREATE TABLE [USING].
You can optionally specify the following:
- A starting value.
- A step size, which can be positive or negative.
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. For example, with a starting value of 0
and a step size of 2
, all values are positive even numbers but some even numbers might be skipped.
When using the clause GENERATED BY DEFAULT AS IDENTITY
, insert operations can specify values for the identity column. Modify the clause to be GENERATED ALWAYS AS IDENTITY
to override the ability to manually set values.
Identity columns only support the BIGINT
type, and operations fail if the assigned value exceeds the range supported by BIGINT
.
To learn about syncing identity column values with data, see ALTER TABLE.
CTAS and identity columns
You cannot define schema, identity column constraints, or any other table specifications when using a CREATE TABLE table_name AS SELECT
(CTAS) statement.
To create a new table with an identity column and populate it with existing data, do the following:
- Create a table with the correct schema, including the identity column definition and other table properties.
- Run an
INSERT
operation.
The following example uses the DEFAULT
keyword to define the identity column. If data inserted into the table includes valid values for the identity column, these values are used.
CREATE OR REPLACE TABLE new_table (
id BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 5),
event_date DATE,
some_value BIGINT
);
-- Inserts records including existing IDs
INSERT INTO new_table
SELECT id, event_date, some_value FROM old_table;
-- Insert records and generate new IDs
INSERT INTO new_table
SELECT event_date, some_value FROM new_records;
Identity column limitations
The following limitations exist when working with identity columns:
- Concurrent transactions are not supported on tables with identity columns enabled.
- You cannot partition a table by an identity column.
- You cannot use
ALTER TABLE
toADD
,REPLACE
, orCHANGE
an identity column. - You cannot update the value of an identity column for an existing record.
Note
To change the IDENTITY
value for an existing record, you must delete the record and INSERT
it as a new record.