This issue will happen only when dropping table in an encrypted zone whcih will remove the table metadata but not its underlying data.   In Hive, there is an option to use purge in the drop table command.   In SAS, when using sql pass through, purge option can be used but what if the user uses proc sql / data step ?   1. Set HIVE properties (skipTrash to true) in libname but that will not make this error go away in an EZ zone. 2. Set auto.purge to true in proc sql - create table TBL PROPERTIES - Still the same outcome - not deleting the hdfs file.   Afaik, there is no other hive option apart from including "purge" keyword for drop table statements but how to this option in data step ?   ... View more

Sure Bruno, I will raise this with SAS support. This limitation means - developers have to rewrite all the SAS jobs using sql pass through and will not be able to use data steps / proc SQL. SQL pass through has its own limitation where analysts to remember hive commands / SAS developers cannot seamlessly migrate existing code base to use Hadoop engine or use data step / proc sql with Hive datasets. Thanks for your help again ! Appreciate it. ... View more

SQL - Pass through ---------------------------- Case 1 : drop table tableName purge -- deletes the Hive metadata as well as the HDFS file. (WORKS !) Case 2 : drop table tableName -- deletes the Hive metadata but not the HDFS file. (NOT WORKING)   Data Step --------------   Case 1 : data hive_lib.table; set sashelp.cars; run;   1. Proc delete  -- Deletes the hive metadata but not the HDFS file.(NOT WORKING) 2. Proc dataset  delete -- Deletes the hive metadata but not the HDFS file.(NOT WORKING) 3. Proc SQL  drop table -- Deletes the hive metadata but not the HDFS file.(NOT WORKING)   Case 2 : proc sql; create table hive_lib.table as select * from sashelp.cars; run;   1. Proc delete  -- Deletes the hive metadata but not the HDFS file.(NOT WORKING) 2. Proc dataset  delete -- Deletes the hive metadata but not the HDFS file.(NOT WORKING) 3. Proc SQL  drop table -- Deletes the hive metadata but not the HDFS file.(NOT WORKING)   Note : In all the above cases, skipTrash is set in hive library.   Summary : In Data step - both in case 1 and 2 - I am not able to delete the underlying HDFS file.  I can suceessfully drop hive table in hive cli / beeline so no permission issue. ... View more

* proc delete / proc dataset - delete / proc sql drop table - none of them are deleting the underlying hdfs file * What I think is, there is an issue whe using proc sql -drop / proc delete / proc dataset with hive tables.   My theory is below.   According to Hive documentation. If you drop an EXTERNAL TABLE, the Hive engine will drop the table metadata and does not delete the hdfs data. If you drop a MANAGED TABLE, the Hive engine will drop the table metadata and deletes the hdfs data.   According to SAS documentation. DBCREATE_TABLE_EXTERNAL=YES -> creates an external table—one that is stored outside of the Hive warehouse. DBCREATE_TABLE_EXTERNAL=NO   -> creates a managed table—one that is managed within the Hive warehouse.  Source : http://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#n0k3b8dw0vz3jxn1jjqouodozcqc.htm   By default the DBCREATE_TABLE_EXTERNAL is NO, which means SAS will create a managed table i.e. Deleting the table should drop both metadata and deletes the hdfs data. But I think this is not the case (at least in my case), the default option is dropping the hive table structure and not the underlying hdfs file using sas procs.   It works using "sql pass through" using "purge" option.   Note : In the libname, I also have hive.warehouse.data.skipTrash to true and also tried setting the DBCREATE_TABLE_EXTERNAL=NO in the data step.   ... View more

Without purge 40           execute ( 41           drop table ext_test_default_ak 42           )by hadoop;   HADOOP_26: Executed: on connection 2 drop table ext_test_default_ak   43          disconnect from hadoop; 44         quit;     With purge   40           execute ( 41           drop table ext_test_default_ak purge 42           )by hadoop;   HADOOP_26: Executed: on connection 2 drop table ext_test_default_ak purge   43          disconnect from hadoop; 44         quit; ... View more

Sorry corrected a typo and sql pass through with purge option deleted both metadata and hdfs file.   But using the datastep (without sql pass thorugh) - using proc sql drop / proc delete / proc dataset delete - still the same issue where metadata in hive warehouse is deleted but not the underlying hdfs file.   The replace option in the datastep is not supported either for hive libraries afaik. ... View more

data hivlib.ext_test_default_ak; set sashelp.cars; run; proc sql; connect to hadoop(server="&hive_server" port=&hive_port schema="&hive_schema" properties="hive.warehouse.data.skiptrash=true;" hdfs_tempdir="&hdfs_tmpdir" uri="jdbc:hive2://&hive_server:&hive_port/&hive_schema;transportMode=http;httpPath=cliservice;principal=&principal?hive.fetch.task.conversion=minimal;hive.fetch.task.conversion.threshold=1;hive.exec.scratchdir=&hdfs_tmpdir;"); select * from connection to hadoop ( select count(*) from ext_test_default_ak ); select * from connection to hadoop ( describe formatted ext_test_default_ak ); execute ( drop table ext_test_default_ak purge )by hadoop; disconnect from hadoop; quit; proc datasets lib=hivlib nodetails nolist; delete ext_test_default_ak; quit;   Yes, Same outcome.   I used proc delete / proc dataset - delete / proc sql - drop table / pass through - execute (drop table with purge option). ... View more

Same outcome. I have used the purge option and there is no error / warning. Just a note that "No rows were selected".   I have even set the skipTrash to true in libname properties. ... View more

I have run in 5 minutes interval and still the same issue. Matter of fact it is just 33K. Since the hdfs file is not getting deleted and only the table structure - the next datastep run appends both files into the table.   Please check my last reply with code and screenshots. Thanks. ... View more

data hivlib.ext_test_default_ak; set sashelp.cars; run; proc sql; connect to hadoop(server="&hive_server" port=&hive_port schema="&hive_schema" properties="hive.warehouse.data.skiptrash=true;" hdfs_tempdir="&hdfs_tmpdir" uri="jdbc:hive2://&hive_server:&hive_port/&hive_schema;transportMode=http;httpPath=cliservice;principal=&principal?hive.fetch.task.conversion=minimal;hive.fetch.task.conversion.threshold=1;hive.exec.scratchdir=&hdfs_tmpdir;"); select * from connection to hadoop ( select count(*) from ext_test_default_ak ); /* select * from connection to hadoop (*/ /* dfs -ls="&hive_db_location/ext_test_default_ak"*/ /* );*/ select * from connection to hadoop ( describe formatted ext_test_default_ak ); disconnect from hadoop; quit; proc datasets lib=hivlib nodetails nolist; delete ext_test_default_ak; quit;   Not able to run dfs commands due to permissions but using unix hdfs commands I can see the hdfs file for the table and it is not gettting deleted. Table is dropped fine from the hive metadata.   hdfs dfs -ls -h hdfs://location/ext* Found 3 items -rw-r--r--    39.3 K 2017-06-15 10:05 ext_test_default_ak/sasdata-2017-06-15-10-05-07-268-e-00007.dlv -rw-r--r--    39.3 K 2017-06-15 10:12 ext_test_default_ak/sasdata-2017-06-15-10-12-56-846-e-00008.dlv -rw-r--r--    39.3 K 2017-06-15 10:24 ext_test_default_ak/sasdata-2017-06-15-10-24-08-882-e-00009.dlv   Check the two attachments. First run has 428 observations and even after deletion, the second run (after 5 mins) has 856 observations. ... View more

Can you please replace the describe formatted to select count(*) from the table and note the result from your second run ?   Since you are using the sashelp.cars dataset - the total should always be 428 but from the second run you will see the count getting increased.   In a nutshell, it will be appending the entire saslhelp.cars from the second run since the underlying HDFS file is not deleted. ... View more

As mentioned in the steps above, proc delete / proc dataset - delete / proc sql drop table - none of them are deleting the underlying hdfs file.   ... View more