topic Re: Delta Live Tables error pivot in Data Engineering

Delta Live Tables error pivot

mangel — Tue, 10 May 2022 08:54:58 GMT

I'm facing an error in Delta Live Tables when I want to pivot a table. The error is the following:

And the code to replicate the error is the following:

import pandas as pd
import pyspark.sql.functions as F
 
pdf = pd.DataFrame({"A": ["foo", "foo", "foo", "foo", "foo",
                          "bar", "bar", "bar", "bar"],
                    "B": ["one", "one", "one", "two", "two",
                          "one", "one", "two", "two"],
                    "C": ["small", "large", "large", "small",
                          "small", "large", "small", "small",
                          "large"],
                    "D": [1, 2, 2, 3, 3, 4, 5, 6, 7],
                    "E": [2, 4, 5, 5, 6, 6, 8, 9, 9]})
df = spark.createDataFrame(pdf)
df.write.mode('overwrite').saveAsTable('test_table')
 
import dlt
 
@dlt.view
def test_table():
    return spark.read.table('test_table')
 
@dlt.table
def test_table_pivoted():
    return (
        spark.table('LIVE.test_table')
        .groupBy('A', 'B')
        .pivot('C')
        .agg(F.first('D'))
    )

Does anybody know why I can not pivot a table in Delta Live Tables Pipelines?

Re: Delta Live Tables error pivot

ccary — Sat, 14 May 2022 15:41:39 GMT

Can you try passing in the column names as a second argument to the pivot function?

.pivot('C', ["small", "large"])

Re: Delta Live Tables error pivot

mangel — Thu, 07 Jul 2022 13:36:50 GMT

Hi, this would only make the query run faster, thanks for the try. I will post below the solution I found to this issue.

Re: Delta Live Tables error pivot

mangel — Thu, 07 Jul 2022 13:39:07 GMT

The solution seems to add the following configuration to the Delta Live Tables Pipeline:

spark.databricks.delta.schema.autoMerge.enabled: true

It allows "schema evolution" in the pipeline and solves the problem.

Re: Delta Live Tables error pivot

bozhu — Fri, 07 Oct 2022 21:35:55 GMT

According to both SQL and Python DLT documentations, "pivot" is not supported in DLT.

So I wonder what are the complications of using "pivot" in such an unsupported way?

Re: Delta Live Tables error pivot

Khalil — Wed, 19 Apr 2023 13:36:16 GMT

It's said in the DLT documentation that "pivot" is not supported in DLT but I noticed that if you want the pivot function to work you have to do one of the the following things:

apply the pivot in your first a dlt.view + the config "spark.databricks.delta.schema.autoMerge.enabled: true" .
apply pivot outside of dlt decorators then start using the output in dlt.view or dlt.table.

Note: I noticed that this works but you get a warning saying that `GroupedData.pivot` function that will be deprecated soon, you will have the same warning if you use Collect for instance.

Hope that help!

Re: Delta Live Tables error pivot

JackyL — Tue, 27 Aug 2024 14:43:27 GMT

I'm a bit of a muppet, it's implied but took be a second to figure out that you need to write it like this:

spark.conf.set("spark.databricks.delta.schema.autoMerge.enabled", "true")

Re: Delta Live Tables error pivot

Michiel_Povre — Wed, 04 Dec 2024 10:39:05 GMT

Hi,

Was this a specific design choice to not allow Pivots in DLT? I'm under the impression they expect fixed table structures in DLT design for a reason, but I don't understand the reason?
Conceptually, I understand the fixed structures makes lineage & quality checks easier to maintain, but is it really a hard constraint? Does applying the above solution lead to issues in the lineage views?