topic Re: Are there any plans to add functions on the partition by fields of a delta table definition such as day() ? A similar capability exists in iceberg. in Data Engineering

Are there any plans to add functions on the partition by fields of a delta table definition such as day() ? A similar capability exists in iceberg.

RyanHager — Thu, 28 Apr 2022 19:15:29 GMT

Benefit: This will help simplify the where clauses of the consumers of the tables? Just query on the main date field if I need all the data for a day. Not an extra day field we had to make.

Re: Are there any plans to add functions on the partition by fields of a delta table definition such as day() ? A similar capability exists in iceberg.

Hubert-Dudek — Sat, 07 May 2022 17:35:55 GMT

@Ryan Hager , yes it is possible using AUTO GENERATED COLUMNS since delta lake 1.2

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.

(DeltaTable.create(spark)
.tableName("default.people10m") 
 .addColumn("id", "INT") 
 .addColumn("birthDate", "TIMESTAMP") 
 .addColumn("dateOfBirth", DateType(), generatedAlwaysAs="CAST(birthDate AS DATE)") 
 .partitionedBy("dateOfBirth") 
 .execute())

Re: Are there any plans to add functions on the partition by fields of a delta table definition such as day() ? A similar capability exists in iceberg.

RyanHager — Fri, 13 May 2022 13:44:46 GMT

Does this mean the execution plan for the following query that uses the original timestamp column will only scan 3 partitions and we don't have to use the dateOfBirth column in the where clause?

select id,birthDate from default.people10m
where birthDate  > cast('2022-05-01 08:00:00.000000 America/Chicago' as timestamp)
and birthDate  < cast('2022-05-03 08:00:00.000000 America/Chicago' as timestamp)

Re: Are there any plans to add functions on the partition by fields of a delta table definition such as day() ? A similar capability exists in iceberg.

RyanHager — Tue, 12 Jul 2022 00:45:15 GMT

Clarification is still open.

Re: Are there any plans to add functions on the partition by fields of a delta table definition such as day() ? A similar capability exists in iceberg.

RyanHager — Fri, 19 Aug 2022 16:26:32 GMT

@Kaniz Fatma Can you help me get clarification on this?

Re: Are there any plans to add functions on the partition by fields of a delta table definition such as day() ? A similar capability exists in iceberg.

RyanHager — Mon, 27 Feb 2023 14:59:47 GMT

Just to update the post, this does work:

You have a timestamp column
Generate a date column from the timestamp column
Partition on that generated date column
Write a query that filters on the original timestamp column
- Databricks will only scan partitions within the date range.