topic Re: How to delete hive tables using datastep in SAS Programming

How to delete hive tables using datastep

AK23 — Wed, 14 Jun 2017 11:04:25 GMT

Hi,

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”.

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

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

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

Thanks.

Re: How to delete hive tables using datastep

Oligolas — Wed, 14 Jun 2017 13:38:21 GMT

Hi,

I have no knowledge about hive.

Nevertheless I would try different ways of deleting that table and see if it resolves the issue:

proc sql;

drop table hive_lib.ext_test_default;

quit;

proc datasets lib=hive_lib nolist;

delete ext_test_default;

run;quit;

or: looks like you only need to empty your table and not deleting it entirely right?

data hive_lib.ext_test_default;

if 0 then set hive_lib.ext_test_default;

delete;

run;

Re: How to delete hive tables using datastep

AK23 — Wed, 14 Jun 2017 14:22:20 GMT

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

Re: How to delete hive tables using datastep

BrunoMueller — Wed, 14 Jun 2017 14:26:42 GMT

By default, tables created in Hive are managed tables. so if you delete a table from SAS using the methods you mentioned also the actual data in the HDFS is deleted.

Here is a code sample that will create a Hive table, show the metadata information from Hive, list the contents of the HDFS directory, delete, show HDFS directory info. You need to set the appropriate macro variables.

LIBNAME ahd HADOOP PORT=&hd_port SERVER="&hd_server" SCHEMA=&hd_schema;

proc delete data=ahd.mycars;
run;

data ahd.mycars;
  set sashelp.cars;
run;

proc sql;
  connect to hadoop (
    PORT=&hd_port SERVER="&hd_server" SCHEMA=&hd_schema
  );

  select * from connection to hadoop (
    describe formatted mycars
  );
  disconnect from hadoop;
quit;

options set=SAS_HADOOP_RESTFUL 1;
proc hadoop;
  hdfs ls="&hive_db_location/mycars" ;
run;
options set=SAS_HADOOP_RESTFUL 0;

proc delete data=ahd.mycars;
run;

options set=SAS_HADOOP_RESTFUL 1;
proc hadoop;
  hdfs ls="&hive_db_location" ;
run;
options set=SAS_HADOOP_RESTFUL 0;

Re: How to delete hive tables using datastep

AK23 — Wed, 14 Jun 2017 15:56:37 GMT

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.

Re: How to delete hive tables using datastep

BrunoMueller — Wed, 14 Jun 2017 16:13:04 GMT

In my sample code the Hive table mycars is deleted in Hive metadata as well as in the HDFS.

Please show the output of the "describe formatted tableName" after you created the Hive table using the DATA Step

Also the output of the two HDFS LS= should show, that the actual data in HDFS gets deleted.

Re: How to delete hive tables using datastep

ChrisNZ — Thu, 15 Jun 2017 04:12:03 GMT

The Official documentation of Hadoop reads :

The deletion of a file causes the blocks associated with the file to be freed. Note that there could be an appreciable time delay between the time a file is deleted by a user and the time of the corresponding increase in free space in HDFS.

Re: How to delete hive tables using datastep

AK23 — Thu, 15 Jun 2017 09:33:07 GMT

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.

Re: How to delete hive tables using datastep

AK23 — Thu, 15 Jun 2017 09:21:43 GMT

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.

Re: How to delete hive tables using datastep

BrunoMueller — Thu, 15 Jun 2017 09:32:40 GMT

Looks strange what happens if you execute the drop table directly through SQL passthrough using

drop table <tablename> purge

Re: How to delete hive tables using datastep

AK23 — Thu, 15 Jun 2017 09:43:36 GMT

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.

Re: How to delete hive tables using datastep

BrunoMueller — Thu, 15 Jun 2017 09:50:47 GMT

did you use

execute (drop table ...) by hadoop

Re: How to delete hive tables using datastep

AK23 — Thu, 15 Jun 2017 10:01:20 GMT

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).

Re: How to delete hive tables using datastep

AK23 — Thu, 15 Jun 2017 10:09:03 GMT

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.

Re: How to delete hive tables using datastep

BrunoMueller — Fri, 16 Jun 2017 07:19:07 GMT

What happens if you do the drop table without the purge? Again using SQL Passthrough

You can use the follwoing options to see which HiveQL is passed from to SAS to Hive:

options
  sastrace=",,,d"
  sastraceloc=saslog
  nostsuffix
;

Re: How to delete hive tables using datastep

AK23 — Fri, 16 Jun 2017 10:13:40 GMT

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;

Re: How to delete hive tables using datastep

BrunoMueller — Fri, 16 Jun 2017 11:47:29 GMT

Thanks for the feedback, since you use SQL pass through, we see exactly the same statement from the trace as the one you have written.

What impact did the "drop table ..." without the purge option have on the HDFS file, was it deleted as well?

When you use the Proc DELETE data=, a DROP TABLE statement will be passed to Hive.

Re: How to delete hive tables using datastep

AK23 — Fri, 16 Jun 2017 15:43:33 GMT

* 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.

Re: How to delete hive tables using datastep

BrunoMueller — Mon, 19 Jun 2017 07:25:14 GMT

So you are saying:

drop table tableName purge, does delete the Hive metadata as well as the HDFS file
drop table tableName, does delete the Hive metadata, BUT NOT the HDFS file

correct?

Re: How to delete hive tables using datastep

AK23 — Mon, 19 Jun 2017 13:56:45 GMT

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.