topic Re: Need Help with Dynamic Folder Strcuture for Files Loaded to a LakeHouse in Pipelines

Need Help with Dynamic Folder Strcuture for Files Loaded to a LakeHouse

kewaynes333 — Sun, 19 May 2024 20:55:18 GMT

I have a few hundred thousand house records as parquet files in an ALDS container. I want to load these files into a LakeHouse as files. I'm assuming it's best practice to load them into the LH using some sort of structure, like Year/Month of the date of sale.

How could I do this in a data pipeline. I've been trying to use a LookUp and ForEach Activity that contains a Copy Data Acvitity. And then trying to use a dynamic expression for the destination folder. But I haven't had any luck as I just keep getting errors due to my dynamic expressions.

Also, the dateSold column in my data is in Unix timestamp format.

Any thoughts on how I could approach this?

Re: Need Help with Dynamic Folder Strcuture for Files Loaded to a LakeHouse

Anonymous — Mon, 20 May 2024 06:20:00 GMT

Hi kewaynes333
Thanks for using Fabric Community.

Here’s a general approach you can take:

Lookup Activity: Use a Lookup activity to retrieve the list of tables that you want to copy. This could be a list of tables in your source database or a list of files in your ALDS container.
ForEach Activity: Use a ForEach activity to iterate over the list of tables/files retrieved by the Lookup activity. Inside the ForEach activity, you can include a Copy Data activity.
Copy Data Activity: In the Copy Data activity, you can specify the source (your ALDS container) and the destination (your LakeHouse). You can use dynamic content to specify the table/file name in the source and the folder structure in the destination based on the dateSold column.

Here’s an example of how you can specify the folder structure dynamically:

@concat(formatDateTime(convertTimeZone(item().dateSold, 'UTC', 'yyyy/MM'), 'UTC'), '/', item().tableName)

This will create a folder structure like Year/Month/TableName in your LakeHouse for each table/file.

You can refer to this link:
Fabric Lakehouse Loading using Data Pipelines & Notebooks – Inspired by MS End-to-End Tutorials (serverlesssql.com)

Hope this helps. Please let me know if you have any further questions.

Re: Need Help with Dynamic Folder Strcuture for Files Loaded to a LakeHouse

kewaynes333 — Mon, 20 May 2024 22:11:14 GMT

Hi,

Thanks for the response and guidance. I run into an error saying that the convertTimeZone function doesn't accept an integer. I believe this is because my dateSold column is in Unix timestamp format (milliseconds, I believe).

I've tried to account for this:

@concat(formatDateTime(addSeconds('1970-01-01T00:00:00Z', div(item().dateSold, 1000)), 'yyyy/MM'), '/', item().tableName)

But now I get this error:

The expression 'concat(formatDateTime(addSeconds('1970-01-01T00:00:00Z', div(item().dateSold, 1000)), 'yyyy/MM'), '/', item().tableName)' cannot be evaluated because property 'tableName' doesn't exist, available properties are 'dateSold, propertyType, lotAreaValue, address, imgSrc, price, bedrooms, longitude, latitude, listingStatus, zpid, listingSubType, contingentListingType, daysOnZillow, bathrooms, livingArea, country, currency, lotAreaUnit, hasImage'.

Any thoughts?

Re: Need Help with Dynamic Folder Strcuture for Files Loaded to a LakeHouse

Anonymous — Tue, 21 May 2024 16:58:44 GMT

Hi kewaynes333
Error Property ‘tableName’ doesn’t exist:
The error message indicates that the property tableName does not exist in the current context. The available properties are ‘dateSold, propertyType, lotAreaValue, address, imgSrc, price, bedrooms, longitude, latitude, listingStatus, zpid, listingSubType, contingentListingType, daysOnZillow, bathrooms, livingArea, country, currency, lotAreaUnit, hasImage’. If you intended to use one of these existing properties, you should replace tableName with the correct property name.

@concat(formatDateTime(addSeconds('1970-01-01T00:00:00Z', div(item().dateSold, 1000)), 'yyyy/MM'), '/', item().<correct_property_name>)
Replace <correct_property_name> with the actual property you want to use. If you still encounter issues this might require a deeper investigation from our engineering team about your workspace, and the logic behind it to properly understand what might be happening.

Please go ahead and raise a support ticket to reach our support team: https://support.fabric.microsoft.com/support
Please provide the ticket number here as we can keep an eye on it.
Thank you.

Re: Need Help with Dynamic Folder Strcuture for Files Loaded to a LakeHouse

Anonymous — Wed, 22 May 2024 16:52:30 GMT

Hi kewaynes333
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. Otherwise, will respond back with the more details and we will try to help.
Thanks

Re: Need Help with Dynamic Folder Strcuture for Files Loaded to a LakeHouse

Anonymous — Thu, 23 May 2024 17:15:27 GMT

Hi @kewaynes333
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. Otherwise, will respond back with the more details and we will try to help.
Thanks