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.
