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;
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
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.
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
Thank you both for the quick response.
I'll give them a try and confirm my findings.
Thanks again.
Jay
You're welcome; glad to help out.
Jim
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.