SAS - Hive Data step delete issue

1
votes

When we drop a managed table , Hive deletes the data in the table is my understanding. By default, SAS data step option DBCREATE_EXTERNAL is set to NO which means SAS data step using hive libraries like below creates a “managed table”.

enter image description here

When using proc sql - drop table / proc delete / proc dataset - delete, the hive metadata is deleted i.e. table structure is dropped from the schema but the underlying HDFS file is not. While running the same data step again (after deletion), i,e. creating the same table in the schema - the number of records ingested is incorrect.

Steps

  1. Create a hive table using SAS data step and note the no of rows.
  2. Drop the table using proc sql delete / proc dataset delete.
  3. Run the create table step again.
  4. Count the number of rows.

Source : http://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#n12r2tbfrrrsgdn1fa4ufw8vb79f.htm

Thanks.

1
hivesasmanageddatastep

1 Answers

0
votes

The issue occurs because of an alternate syntax requirement for the DROP TABLE procedure when the table data resides in an HDFS encryption zone.

Hotfix : http://support.sas.com/kb/58/727.html