topic Re: Appending data to a partitioned table in the Lakehouse in Pipelines

Appending data to a partitioned table in the Lakehouse

teeceegee — Thu, 24 Aug 2023 07:46:41 GMT

I have been able to create a partitioned table in a Lakehouse usign a pipeline where the data is read from a CSV file and partitioned on the YEAR_MONTH field (all data in the file is of the same YEAR_MONTH), and then sub-partitioned on another two fields in the file. However, as new data is realeased on a monthly basis I want to be able to append it to this table. At the moment this is not possible using the same method as the original table was created. It seems you can paritition a table when using OVERWRITE but not when using APPEND (even though adding the new data will create an additional top level parition and not add data to any of the exisiting partitions).

I am missing something obvious? Is this a preview restriction? Are there other ways of achieving the same thing?

Re: Appending data to a partitioned table in the Lakehouse

mrojze — Thu, 24 Aug 2023 12:51:03 GMT

May I ask you how you created the Delta Lake table? Directly from a Copy? Can you share a screenshot?

Re: Appending data to a partitioned table in the Lakehouse

teeceegee — Thu, 24 Aug 2023 13:22:30 GMT

The original table was created in the lakehouse with the following parameters

But when you select the append option the fields for enabling paritioning disappear and the execution of the pipeline fails.

Re: Appending data to a partitioned table in the Lakehouse

mrojze — Thu, 24 Aug 2023 13:43:48 GMT

Thanks for the info.

I'll try this myself, but I wonder if the overwrite partition clause is just to define the table.

Once you have to append, the table is already defined. You just go ahead and append. Data should flow into the proper partitions

Re: Appending data to a partitioned table in the Lakehouse

teeceegee — Thu, 24 Aug 2023 13:49:30 GMT

Agreed, that was my expectation but, as I say, it errors when run.

Re: Appending data to a partitioned table in the Lakehouse

mrojze — Thu, 24 Aug 2023 14:12:33 GMT

Oh well, I was not expecting this:

I am starting to think that our only option today is to go to a staging table with ADF and they do the insert into the partitioned using a notebook script.

Re: Appending data to a partitioned table in the Lakehouse

mrojze — Thu, 24 Aug 2023 15:23:30 GMT

I made it work. Is there a better solution to this, very likely.

First, I created a regular table using the overwrite option in ADF

2nd, I created an empty delta-partitioned table in a notebook

3rd I inserted into the partitioned table from the staging table created in the first step.

Re: Appending data to a partitioned table in the Lakehouse

mrojze — Thu, 24 Aug 2023 15:24:05 GMT

Sorry I missed the last screenshot

Re: Appending data to a partitioned table in the Lakehouse

teeceegee — Tue, 29 Aug 2023 07:22:24 GMT

Thanks, I will try this out today. 🙂