topic Re: How to Delete Orphaned SAS SPDE tables on HDFS with PROC HADOOP in SAS Procedures

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

prsr — Mon, 30 Sep 2024 16:37:12 GMT

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.

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

Patrick — Mon, 30 Sep 2024 21:20:16 GMT

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#n0nj9w2t8wjxcwn19fp75ec5c3km

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 Distributed File System (HDFS)¹.

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

prsr — Fri, 04 Oct 2024 09:59:40 GMT

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,