Sometimes an error occurs when setting ha=true

[deamoncmh]

When I configure like this :
{{ config(materialized='table',ha=true)}}

This error occurs when running batch scheduled tasks every day
Error info:
HIVE_PATH_ALREADY_EXISTS: Target directory for table 'dbt_prod.stg_amazon__dayreportad__ha' already exists: s3://dbt-us/dbt_prod/stg_amazon__dayreportad/80b17007-2212-463c-bf6f-022f84bb7ee5.

But I haven't encountered this situation since running it locally

Does anyone know why this is happening or what solutions are available

I would be very grateful

[nicor88]

This type of error is pretty weird indeed.
When working with hive ha tables, the path used has a unique uuid that is different on every run.
The only reason that comes to my mind is that you have num_retries set to a value bigger than 0 - the first run failed but wrote some data, but the 2nd attempt re-try after failure, and as the run belongs to the same run, it try to write the data to the exact same location.

If that's the case, could you inspect carefully the logs, and eventually post here what you can, to allow to debug further.

Long story short - with ha tables this type of error should be mitigated to the minumu, via the usage of unique location.

[deamoncmh]

Thank you for your reply. We have indeed configured (num_detries: 5). As you mentioned, if it fails to run and tries again, it will be written to the same location. Does this same location refer to the same folder in the same directory?
As shown in the figure, there may be multiple folders in the directory of a single Hive table, and different folders contain different versions of the table
Of course, I also made some attempts, and I speculated that it might be related to these multiple versions, so I configured (version s_toukeep=0), but it doesn't seem to solve this problem
Looking forward to your latest reply

[nicor88]

In case of ha=true, it's normal to have multiple versions, and those are not creating a problem at all - actually they serve as the base for HA behvior - what we do when ha=true, is writing the data into another unique location (hence you see different path in S3), and then do some glue metadata operations.
The error that you receive is about only one of those locations.

Something else that come to my mind is that if you might miss some permissions on S3 that can lead to have this behaviour.
Or as said, the first attempt fails, for whatever reason, and the 2nd attempt try to write to the same location (on then same run) that lead to the behaviour that you see.

In your production environment try to set retries to 0, to isolate the root cause problem, what you saw seems a downside effect of the real root cause problem.

[deamoncmh]

Thank you for your explanation and help. I will make some attempts based on your suggestions, but will setting (num_detries: 0) affect the scheduled tasks?

[deamoncmh]

I am wondering if it is possible to delete files that have already failed before trying again, or if data can be moved to a new folder during the retry. Can you optimize this issue on your end? I would greatly appreciate it if you could do so.
Looking forward to your latest reply, thank you

[nicor88]

It's hard to know what is going on without having access to logs/seeing your setup.
If you are interested in collaborating with me, consider reaching out to me in dbt slack workspace - nicor88, and we can figure out the details.