Hive external partitions using SAS access to Hadoop interface

[nagayamini]

Hello,

In my current project, we have a requirement to load SAS datasets into HDFS using SAS access to Hadoop.

In this, we are supposed to create external partitions (as we have in HIVEQL) using SAS access.

Please help me with the options if any to create external partitions and during a reload we are supposed to drop those partitions as well.

I have seen that we can use DB_CREATE_TABLE_OPTS and use an existing partition but my requirement is to add an external partition which is not existing already in the table.

Please let me know if any other details are required.

Quick response would be highly appreciated.

Best Regards

Yamini

[JBailey · Accepted solution]

Hi @nagayamini

You may find the following HiveQL statements helpful:

ALTER TABLE myschema.table1 ADD IF NOT EXISTS PARTITION (mydate='2018-07-04') 
   LOCATION 'hdfs://some/hdfs/filesystem/months/mydate=2018-07-04'

ALTER TABLE myschema.table1 DROP PARTITION (mydate='2018-07-04');

These statements can be placed in SAS EXECUTE(...) BY HADOOP statements.

Update - example EXECUTE statements...

LIBNAME myHDP HADOOP ... some valid LIBNAME statement;

PROC SQL;
CONNECT USING myHDP;
EXECUTE(ALTER TABLE myschema.table1 ADD IF NOT EXISTS PARTITION (mydate='2018-07-04') 
   LOCATION 'hdfs://some/hdfs/filesystem/months/mydate=2018-07-04') BY myHDP;

EXECUTE(ALTER TABLE myschema.table1 DROP PARTITION 
 (mydate='2018-07-04') ) BY myHDP;
QUIT;

Best wishes,
Jeff

[nagayamini]

Thanks for the solution Jeff, but I can see that merge and alter statements are not supported in SAS Execute as per below link.

I even tried using merge syntax of HIVE in this execute statement but it is giving an error.

Can you please advise other alternative for this scenario.

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#execute.html

[JBailey]

Hi @nagayamini

I have updated my response. Your link is bad...

Best wishes, 

Jeff

[nagayamini]

Thank you Jeff, it worked. 

[JBailey]

HI @nagayamini

My pleasure!

Best wishes,

Jeff

[ChrisNZ]

Do you want dynamic or static partitions?

If the table is created with option PARTITIONED BY it will be partitioned.

You can then add dynamic partitions with something like:

ALTER TABLE test ADD PARTITION (dt='2014-03-05')

and you can insert data with:

INSERT INTO TABLE test PARTITION(dt='2014-03-05') SELECT a,b,c,d from table

[nagayamini]

Hi Chriz,

I would like to add dynamic partitions using SAS Access to Hadoop interface in which I can't directly use alter command which you mentioned.

As stated by Jeff, need to use execute in order to use DBMS specific queries.

[ChrisNZ]

need to use execute in order to use DBMS specific queries. I can't directly use alter command which you mentioned.

Of course you can.

libname HAD001 hadoop ... ;

proc sql;

  connect using HAD001;

  execute by HAD001 ( alter table ...  ) ;