Problem with Hadoop timestamps

DavePrinsloo — Fri, 16 Sep 2022 12:01:40 GMT

I need to extract data from hive tables that contain timestamps. The timestamp values are adjusted by exactly 1 or 2 hours when copying the tables to SAS.

This means that datetime values that should be 31DEC9999:00:00:00 are extracted as 30DEC9999:23:00:00.

How can this be prevented.

The options timezone is blank.

The server is in Germany and I suspect that the difference of 1 or 2 hours depends on whether it is summer (day light savings) time winter.

libname pdoa hadoop uri="jdbc:hive2://datalab-hive-prod.gslb.db.com:10000/pr_pwcc_analysis;principal=hive/datalab-hive-prod.gslb.db.com@DBG.ADS.DB.COM;ssl=true?mapreduce.job.queuename=root.ACE-S-FRA-DATALAB-PROD-PC_PDOA;" server="datalab-hive-prod.gslb.db.com" port=10000 schema="pc_pdoa_analysis_pcb_nemp";


data pdoa;
set pdoa.Eap_pd697h;
where active=1 and partid=4000300;
run;

When the same extract is done using ODBC, then the expected values are extracted.

I can work-around the issue using pass-thru and the from_utc_timestamp fucnction.

proc sql;
connect to hadoop (uri="jdbc:hive2://datalab-hive-prod.gslb.db.com:10000/pr_pwcc_analysis;principal=hive/datalab-hive-prod.gslb.db.com@DBG.ADS.DB.COM;ssl=true?mapreduce.job.queuename=root.PWCC.ACE-S-FRA-DATALAB-PROD-PC_PDOA" server="datalab-hive-prod.gslb.db.com" port=10000 schema="pc_pdoa_analysis_pcb_nemp" READ_METHOD=HDFS);
  create table test_pdoa as select * from connection to hadoop
(   
 SELECT from_utc_timestamp(ts_to,"Europe/Berlin") as ts_berlin,
*
FROM Eap_pd697h
where active=1 and partid=4000300
);
disconnect from hadoop ;
quit;

My question is: Is there a way to adjust the libname statement so that the "from_utc_timestamp" conversion is applied to all timestamp / datetime columns automatically.

Re: Problem with Hadoop timestamps

ChrisNZ — Thu, 22 Sep 2022 06:18:54 GMT

You could look at the timezone option, but i suspect that's a rabbit hole more complex than the workaround you have in place.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.2/nlsref/n0px72paaaqx06n1ozps024j78cl.htm

topic Problem with Hadoop timestamps in SAS Programming

Problem with Hadoop timestamps

Re: Problem with Hadoop timestamps