topic Re: GeneratedAlwaysAs' along with dataframe.write in Data Engineering

GeneratedAlwaysAs' along with dataframe.write

thushar — Mon, 23 Jan 2023 08:41:21 GMT

Is it possible to use a calculated column (as like in the delta table using generatedAlwaysAs) definition while writing the data frame as a delta file like df.write.format("delta").

Any options are there with the dataframe.write method to achieve this ?

Re: GeneratedAlwaysAs' along with dataframe.write

Ajay-Pandey — Mon, 23 Jan 2023 11:05:12 GMT

Hi @Thushar R in my knowledge currently we don't have any option in databricks.

Re: GeneratedAlwaysAs' along with dataframe.write

Hubert-Dudek — Mon, 23 Jan 2023 13:17:37 GMT

Yes, you can as GeneratedAlwaysAs is in delta schema, for example, write to the table (which is registered delta as the table).

df.write.format("delta").mode('append').saveAsTable('TableName')

some more examples are there https://docs.delta.io/latest/delta-batch.html there are a lot of ways to achieve that

DeltaTable.create(spark) \
  .tableName("default.events") \
  .addColumn("eventId", "BIGINT") \
  .addColumn("data", "STRING") \
  .addColumn("eventType", "STRING") \
  .addColumn("eventTime", "TIMESTAMP") \
  .addColumn("year", "INT", generatedAlwaysAs="YEAR(eventTime)") \
  .addColumn("month", "INT", generatedAlwaysAs="MONTH(eventTime)") \
  .addColumn("day", "INT", generatedAlwaysAs="DAY(eventTime)") \
  .partitionedBy("eventType", "year", "month", "day") \
  .execute()

Re: GeneratedAlwaysAs' along with dataframe.write

thushar — Mon, 23 Jan 2023 13:29:01 GMT

Thanks, but my ask is whether we have the option to mention the clause 'generatedAlwaysAs' with dataframe.write method?

Re: GeneratedAlwaysAs' along with dataframe.write

Hubert-Dudek — Mon, 23 Jan 2023 13:43:05 GMT

Maybe try something like

df.createOrReplaceTempView("my_table")
 
spark.sql("CREATE TABLE rectangles(
  a INT,
  b INT,
  area INT GENERATED ALWAYS AS (a * b)
);
INSERT INTO rectangles (a, b)
select
  a,
  b
FROM
  my_table)

Re: GeneratedAlwaysAs' along with dataframe.write

jose_gonzalez — Fri, 24 Feb 2023 23:43:11 GMT

Hi @Thushar R,

Just a friendly follow-up. Did any of the responses help you to resolve your question? if it did, please mark it as best. Otherwise, please let us know if you still need help.

Re: GeneratedAlwaysAs' along with dataframe.write

pvignesh92 — Thu, 09 Mar 2023 14:27:58 GMT

Hi @Thushar R ,

This option is not a part of Dataframe write API as GeneratedAlwaysAs feature is only applicable to Delta format and df.write is a common API to handle writes for all formats.

If you to achieve this programmatically, you can still use DeltaTable API to create the table first as below

DeltaTable.create(spark) \

.tableName("default.events") \

.addColumn("eventId", "BIGINT") \

.addColumn("data", "STRING") \

.addColumn("eventType", "STRING") \

.addColumn("eventTime", "TIMESTAMP") \

.addColumn("year", "INT", generatedAlwaysAs="YEAR(eventTime)") \

.addColumn("month", "INT", generatedAlwaysAs="MONTH(eventTime)") \

.addColumn("day", "INT", generatedAlwaysAs="DAY(eventTime)") \

.partitionedBy("eventType", "year", "month", "day") \

.execute()