Non-deterministic functions like current_date() or current_timestamp().

In fact, according to the documentation, Generated Column cannot be used with the following functions: ) # Creating a dummy case statement to populate null values for certain rows and valid timestamp for restĭf = spark.sql('''select customer_id, first_name, last_name, customer_email ,ĬASE WHEN customer_id%2 = 0 THEN current_timestamp () ELSE cast(null as timestamp) END as insert_timestampįrom demo_mo_schema.customer''')ĭf.write.format('delta').mode('append').saveAsTable('demo_mo_schema.customer_gc_scenario2')

Insert_date DATE GENERATED ALWAYS AS (DATE (insert_timestamp)) It may not be the correct application for the insert timestamp use case, but let’s assume that some of the rows of the columns on which you base your Generated Column have null values.ĬREATE TABLE IF NOT EXISTS demo_mo_schema.customer_gc_scenario2 ( It’s pretty common for data to have null values.

Scenario 2: How do Generated Columns respond to null values? Delta Lake considers this a constraint violation and fails the transaction. Delta Lake is not allowing this to happen. df.write.format('delta').mode('append').saveAsTable('demo_mo_schema.customer_gc_with_partition_example')Įxactly. Let’s now load the data into the partitioned delta table we created earlier. Please note that I did not use the partitionBy clause when writing to the table in the write action above.ģ. df = spark.sql('select customer_id, first_name, last_name, customer_email, gender, customer_date_of_birth, customer_phone_number, current_timestamp() as insert_timestamp from demo_mo_schema.customer') To load data into the new table I created, I am using a sample customer table. Month INTEGER GENERATED ALWAYS AS (month (insert_timestamp))Ģ. Year INTEGER GENERATED ALWAYS AS (year (insert_timestamp)), Insert_date DATE GENERATED ALWAYS AS (DATE (insert_timestamp)),ĬAST (CONCAT (YEAR (insert_timestamp), LPAD (MONTH (insert_timestamp), 2, '0')) as INT) To begin with, let’s create a delta table containing derived partition columns - ‘year’ and ‘month’ĬREATE TABLE IF NOT EXISTS demo_mo_schema.customer_gc_with_partition_example (.Let us take this use case and see how Generated Columns can help. It is most common that the partition columns in a table are derived from the timestamp column in the data. Let us understand this with a small example: You define a column as a generated column in your table definition, and any writes to the table will automatically populate the column with values when the table is loaded.These are special types of columns provided by Delta Lake that will be generated automatically based on the function logic that we provide over other columns in the table.Generated Columns are suitable for handling this situation. These column values are computed when an action is called on this DataFrame.įor example, if the requirement is to capture audit timestamps only when the data is written to the table and not before, we should avoid calling any action, such as count or display, before the write operation is completed. Using appropriate logic, derive the newly added columns from our code.

Read the contents of the input file or the input table.Incorporating an audit timestamp column into the table to track when records were added or updated → current_timestamp.Creating partition columns dynamically based on the current_timestamp column.Calculating age using the column “date of birth“ → age = current_year - date_of_birth.Creating new columns by concatenating existing columns in the data → Ex.A couple of scenarios where this could be useful:

Lakehouse with delta lake deep dive