topic Re: SAS HADOOP Date Query Question in SAS Programming

SAS HADOOP Date Query Question

JayS — Wed, 09 Sep 2020 15:01:54 GMT

I'm fairly New to querying HADOOP data from SAS.
The below code returns 0 Obs, and I know there is a InsertDate of '01Aug2020'

Reading up, but have not found the solution as of yet.

I am using this driver... CLASS="com.cloudera.hive.jdbc.HS2Driver"

Do I need to change the data type in order for HADOOP to resolve the string value.

TIA, Jay

proc sql;

*******************************************************************************

*** All the Connect To Hadoop Schema Table info works, I connect ***

*******************************************************************************

CREATE TABLE NewTable AS
SELECT *
FROM CONNECTION TO hadoop
/* file stored in HADOOP */
/*****************************/
( select * from HADOOPData
Where InsertDate = '01Aug2020' );
disconnect from hadoop;
quit;

Re: SAS HADOOP Date Query Question

ballardw — Wed, 09 Sep 2020 16:22:49 GMT

Try using '01Aug2020'd . The d immediately after the value in quotes tells SAS to treat it as a DATE literal, otherwise it would be a character value.

Re: SAS HADOOP Date Query Question

jimbarbour — Wed, 09 Sep 2020 16:36:51 GMT

@JayS,

You're using explicit pass through SQL. Whenever there are phrases like "FROM CONNECTION TO hadoop" or "disconnect from hadoop", explicit pass through SQL is being used. Pardon me if you already know this but just in case: When explicit pass through is used, you're basically telling SAS, just hand this over to the database as is. In other words, SAS will perform no "translation" from SAS SQL to the SQL (or in this case HQL) of the database. Therefore all statements must be coded using the syntax of the database not SAS syntax.

In your case, you need to code the date in the format that Hadoop uses, i.e. '2020-08-01'.

Jim

Re: SAS HADOOP Date Query Question

JayS — Wed, 09 Sep 2020 16:55:33 GMT

Thank you both for the quick response.

I'll give them a try and confirm my findings.

Thanks again.

Jay

Re: SAS HADOOP Date Query Question

JayS — Fri, 11 Sep 2020 12:59:27 GMT

Perfect, thanks Jim....

Re: SAS HADOOP Date Query Question

jimbarbour — Fri, 11 Sep 2020 13:27:51 GMT

You're welcome; glad to help out.

Jim