topic Re: Maintenance on Lakehouse Tables in Data Engineering

Maintenance on Lakehouse Tables

rtolsma — Tue, 29 Oct 2024 22:42:16 GMT

Hi,

I'm trying to run Maintenance on Lakehouse tables that are in OneLake. However, when I try and run Maintenance on the table with only the Optimize and V-Order flag selected I receive the following error:

I have even tried to run a Spark SQL notebook and the operation failed with a similar error. When I put the full URL in for the OneLake and workspace it ended up erroring again saying that the operation could not be completed on Dataverse tables.

Please let me know how this can be resolved.

Thanks,

Russ

Re: Maintenance on Lakehouse Tables

Anonymous — Wed, 30 Oct 2024 02:05:37 GMT

Hi @rtolsma,

The error message notice this issue seems related to the permission. I'd like to suggest you check these settings if they meet to the error messages.

BTW, can you please share some more detail information about this issue? They should help us clarify your scenario and test to troubleshoot. Have you applied any change on the credentials that you used in the ADLS Gen2 connection?

Regards,

Xiaoxin Sheng

Re: Maintenance on Lakehouse Tables

frithjof_v — Wed, 30 Oct 2024 06:15:27 GMT

You mention Dataverse tables.

Perhaps it is not possible to do Lakehouse maintenance on Dataverse tables?

(Perhaps those tables are fully managed by the Dataverse integration mechanism).

Re: Maintenance on Lakehouse Tables

AndyDDC — Wed, 30 Oct 2024 09:46:09 GMT

Hi @rtolsma how have the lakehouse tables been created?

Re: Maintenance on Lakehouse Tables

richbenmintz — Wed, 30 Oct 2024 12:37:00 GMT

Hi @rtolsma,

Are you able to provide screenshots of the Lakehouse Folder Structure and where the table you are trying to run maintenance on?

would be helpful for some context as you are mentioning Dataverse and the error is referencing ADLS Gen 2

Re: Maintenance on Lakehouse Tables

rtolsma — Wed, 30 Oct 2024 15:23:19 GMT

The Lakehouse is a OneLake lakehouse and was created via Fabric Link. When I tried to run Optimize on a single table in the Lakehouse using Spark SQL it errored indicating that tables in the Dataverse cannot be maintained. My issue is that my client is trying to run a single query to drop a view and it ran for 5 minutes without completing. Since then performance has degraded for any query. When I inspected one of the highly transacted tables from D365 FnO that has been exported I could see that there were hundreds of 10MB files, I could see how this would cause performance issues. I want to optimize the table but this is failing either through the Fabric Maintenance operation or through a Notebook. I hope this provides clarity. Ultimately, I'm trying to optimize the client's experience with Fabric, as of right now they have an F8 sku and are seeing poor performance on querying and a high CU usage in the background for data coming from Fabric Link.

Re: Maintenance on Lakehouse Tables

rtolsma — Wed, 30 Oct 2024 15:25:53 GMT

Perhaps it isn't, but I can't find any documentation to confirm this. If it is then I have to say that its quite frustrating to see this failing and experiencing this type of performance. I need to provide a solution or it almost becomes unusable.

Re: Maintenance on Lakehouse Tables

rtolsma — Wed, 30 Oct 2024 15:28:36 GMT

This is a OneLake lakehouse, the user is a Workspace admin and a Fabric SKU admin. I cannot find any other location to manage permissions and given that this is a Fabric operation when you click on the Maintenance menu item for the delta lake table it would seem to me that the permissions should not be an issue. If it is then the Maintenance menu should be disabled for a user who cannot perform the Optimize and Vacuum maintenance activities on a Lakehouse.

Re: Maintenance on Lakehouse Tables

richbenmintz — Wed, 30 Oct 2024 15:32:04 GMT

Hi @rtolsma ,

I believe the issue is that the Dataverse data is not actually managed by Fabric, tables are pointers to your dataverse environment, the error while annoying is correct.

Re: Maintenance on Lakehouse Tables

rtolsma — Wed, 30 Oct 2024 15:38:26 GMT

Interesting, so then why do I see storage consumption in Fabric? When I look at the Fabric Capacity Metrics App I see consumption continuing to grow. The OneLake storage for this workspace is at 1.5 TB there needs to be some way to maintain this?

Re: Maintenance on Lakehouse Tables

richbenmintz — Wed, 30 Oct 2024 15:43:25 GMT

That is very odd, when you click on the table and view source files, what does it show you?

Re: Maintenance on Lakehouse Tables

rtolsma — Wed, 30 Oct 2024 15:47:22 GMT

I see several hundred 9-10MB files as per the screenshot below:

Re: Maintenance on Lakehouse Tables

richbenmintz — Wed, 30 Oct 2024 15:51:01 GMT

Can you right click at show the path of a single file?

Is there a delta_log folder in the directory?

Re: Maintenance on Lakehouse Tables

rtolsma — Wed, 30 Oct 2024 15:53:09 GMT

Yes there is and it contains approximately 130+ .json files with some .checkpoint.parquet files in it.

Re: Maintenance on Lakehouse Tables

richbenmintz — Wed, 30 Oct 2024 16:02:11 GMT

Crazy idea,

Find the location of the Delta Table in OneLake and create a new table, from that path, then try to vacuum and optimize.

Re: Maintenance on Lakehouse Tables

rtolsma — Wed, 30 Oct 2024 16:42:06 GMT

Thanks for the idea, seems like I'm just going to have to copy data as you suggested -- likely to a warehouse or to another lakehouse and then optimize for queries from there. Thanks!

Re: Maintenance on Lakehouse Tables

richbenmintz — Wed, 30 Oct 2024 16:55:32 GMT

The Create table should not duplicate the data,

spark.sql(f"""create table {table_name} using delta location '{delta_folder_path}'""" )

as it is pointing to an existing delta location, should simply create a metadata entry into the Fabric Lakehouse Catalog, could you humor me and show me the properties of one of the tables, would look something like

Re: Maintenance on Lakehouse Tables

rtolsma — Wed, 30 Oct 2024 17:18:29 GMT

Here are the properties for this table -- definitely similar.

Re: Maintenance on Lakehouse Tables

richbenmintz — Wed, 30 Oct 2024 17:21:41 GMT

It is a Shortcut, would be the only difference

Re: Maintenance on Lakehouse Tables

rtolsma — Wed, 30 Oct 2024 17:30:32 GMT

Yup makes sense that this is a short cut into an ADLS Gen 2 storage facility that the Fabric instance doesn't have access to. So impossible to maintain, but I didn't realize that shortcuts would cause you to also increase your OneLake storage...seems like I'm doing exactly what was told us was the benefit, no data duplication but it appears that I'm consuming Dataverse storage and OneLake storage as well.