topic Hive external partitions using SAS access to Hadoop interface in SAS Programming

Hive external partitions using SAS access to Hadoop interface

nagayamini — Thu, 02 Aug 2018 09:18:40 GMT

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

Re: Hive external partitions using SAS access to Hadoop interface

JBailey — Wed, 08 Aug 2018 19:16:31 GMT

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

Re: Hive external partitions using SAS access to Hadoop interface

ChrisNZ — Thu, 02 Aug 2018 23:46:27 GMT

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

Re: Hive external partitions using SAS access to Hadoop interface

nagayamini — Wed, 08 Aug 2018 11:25:00 GMT

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

Re: Hive external partitions using SAS access to Hadoop interface

nagayamini — Wed, 08 Aug 2018 11:27:56 GMT

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.

Re: Hive external partitions using SAS access to Hadoop interface

ChrisNZ — Wed, 08 Aug 2018 11:51:34 GMT

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

Re: Hive external partitions using SAS access to Hadoop interface

JBailey — Wed, 08 Aug 2018 19:17:36 GMT

Hi @nagayamini

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

Best wishes,

Jeff

Re: Hive external partitions using SAS access to Hadoop interface

nagayamini — Thu, 30 Aug 2018 08:32:58 GMT

Thank you Jeff, it worked.

Re: Hive external partitions using SAS access to Hadoop interface

JBailey — Thu, 30 Aug 2018 12:38:12 GMT

HI @nagayamini

My pleasure!

Best wishes,

Jeff