How to Delete Orphaned SAS SPDE tables on HDFS with PROC HADOOP

[prsr]

Hi, 

Seems like I have some orphaned sas spde tables on HDFS preventing me to overwrite those tables with the below error message - 

ERROR: Unexpected lock failure on specified file.

ERROR: File EVAPS.xyz_1_202101.DATA has not been saved because copy could not be completed.

I see a post on sas which reads as below -

https://documentation.sas.com/doc/en/pgmsascdc/v_055/engspdehdfsug/p1gskewwxbo71jn1d7qs9mmmexjo.htm

Deleting Orphaned Lock Files

In most situations, you do not see the lock directory because lock files are deleted when the process completes. However, in some situations, such as an abnormal termination of a SAS session, lock files might not be properly deleted. The leftover lock files could prohibit access to a data set. If this error occurs, you must manually delete the orphan lock file by submitting Hadoop commands. You can use the HADOOP procedure to submit Hadoop commands.

Does anyone know the exact command to delete the orphaned spde tables using PROC HADOOP.

Thanks in advance.

RP

[Patrick]

I've never done it myself but... I guess it's a two step process. First figure out the name of the lock file, then delete the lock file.

The link you've shared indicates that the lock file itself is likely under some /temp directory or wherever the SPDELOCKPATH environment variable points to. 

To delete the file here the docu link: 
https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/n0sksf47t7lheen11ay3w7490o5x.htm#n0nj... 

I found above docu entry very quickly because I first asked Copilot:

Proc hadoop delete file

And got the answer:

To delete a file in Hadoop using the PROC HADOOP procedure in SAS, you can use the HDFS statement with the DELETE option. Here’s an example of how you can do this:

proc hadoop;
   hdfs delete="/path/to/your/file";
run;

Replace "/path/to/your/file" with the actual path of the file you want to delete on the Hadoop Distr...¹.

[prsr]

Thanks Patrick for the reply. 

My table is in hdfs location as below -

hdfs://xxxxxx/warehouse/tablespace/external/hive/schema/table

when I run the below code - 

proc hadoop;
hdfs delete="hdfs://xxxxx/warehouse/tablespace/external/hive/schema/tablename";
run;

Firstly, It asks to mention user name in the format (/user/username), when I do that it prefixes the /user/username to the given path, which results in 

ERROR: message: File /user/xxxxxxx/hdfs://xxxxx/warehouse/tablespace/external/hive/schema/tablename does not exist.

thanks,

RP