topic Re: Unable to access hive tables from SAS in Advanced Programming

Unable to access hive tables from SAS

SASUserRocks — Mon, 30 Aug 2021 09:44:25 GMT

Automated few sas reports sourcing data from hive schema. Programs were running fine till yesterday, Today encountering below error while accessing hive tables through SAS library and I am clueless. I am able to access hive tables and data through Ambari Hive.

219 H_BASE.ADCB_USER_PAGE_VIEWS A WHERE datepart(input(timestamp,anydtdtm.))> "&lastsdte."d ;

HADOOP_1: Prepared: on connection 0
DESCRIBE FORMATTED `db_appldigi`.`ADCB_USER_PAGE_VIEWS`

ERROR: Prepare error: Error while processing statement: FAILED: Execution Error, return code 1 from
org.apache.hadoop.hive.ql.exec.DDLTask. cannot find field adcb_user_page_views from [0:date, 1:segmentname,
2:engagementname, 3:productlabel, 4:channelname, 5:cookie, 6:impressiontimestamp, 7:impressionid, 8:crmid]
SQL statement: DESCRIBE FORMATTED `db_appldigi`.`ADCB_USER_PAGE_VIEWS`

Re: Unable to access hive tables from SAS

LinusH — Mon, 30 Aug 2021 11:02:43 GMT

Sounds like either the view is unavailable/change/damaged or ahve changed permissions.

Can you access the view outside the batch?

Re: Unable to access hive tables from SAS

SASUserRocks — Mon, 30 Aug 2021 14:27:38 GMT

Yes I can access view outside in hive as well as can view data till sas data loader

Re: Unable to access hive tables from SAS

SASUserRocks — Mon, 30 Aug 2021 14:30:00 GMT

Also getting same error while accessing any hive table inside library, each has got different definition

Re: Unable to access hive tables from SAS

SuCheeTay — Mon, 30 Aug 2021 14:36:55 GMT

It may have something to do with the datepart() in the WHERE clause. Try SQL_FUNCTIONS=ALL in order for the SAS/ACCESS engine to pass SAS function to Hadoop.

Re: Unable to access hive tables from SAS

ballardw — Mon, 30 Aug 2021 16:11:24 GMT

Anytime a program works for a period time and then starts throwing errors the "solution" will usually come down to finding what changed.

Were user permissions changed?

Were rules for the connection to the Hive tables changed? New software on that side with different settings?

Were one or more files locations changed?

OR the more obnoxious, did someone change the layout or content of fields in the data?