Hi, When executing code on Hive within a SAS environment, I ran into trouble extracting hive table to SAS server. Before altering the variable format to varchar, I received the following warning message: WARNING: The following columns could have a length in SAS of 32767. If so, SAS performance is impacted. See SAS/ACCESS documentation for details. The columns read from Hive followed by the maximum length observed were: name:0, bcustatefp:0, bcucountyfp:0 Because of this warning, I extracted 0 rows and only the column names from hive to SAS server. I learned from google that I should alter column format to charvar to fix the warning, but after the change I still extracted 0 rows with just the column names when there is clearly data in the extracted table. ( I was able to extract the tables with the same exact code on another server on the copied dataset) Log: 261 create table work.deno1 as 262 select * from connection to serv20 263 (select * from deno4); HADOOP_212: Prepared: on connection 1 select * from deno4 NOTE: Table WORK.DENO1 created, with 0 rows and 7 columns. 264 265 create table work.num1 as 266 select * from connection to serv20 267 (select * from num3); HADOOP_213: Prepared: on connection 1 select * from num3 NOTE: Table WORK.NUM1 created, with 0 rows and 7 columns. Part of my code that generated variables that do not match SAS server format: execute (create temporary table deno4 as
select name, nifi_date,
CASE
WHEN STATEFP <> '' THEN STATEFP
ELSE 'missing'
END STATEFP,
CASE
WHEN COUNTYFP <> '' THEN COUNTYFP
ELSE 'missing'
END COUNTYFP,
sum(count(*)) over (partition by nifi_date,name) as freq_all_deno,
sum(count(name)) over (partition by nifi_date, name, STATEFP) as freq_state_deno,
count(name) as freq_county_deno
from deno3
group by nifi_date, name,STATEFP, COUNTYFP
) by serv20;
execute (alter table deno4 CHANGE name name varchar(50)) by serv20;
execute (alter table deno4 CHANGE STATEFP STATEFP varchar(10)) by serv20;
execute (alter table deno4 CHANGE COUNTYFP COUNTYFP varchar(10)) by serv20;
execute (create temporary table num3 as
select name, nifi_date,
CASE
WHEN STATEFP <> '' THEN STATEFP
ELSE 'missing'
END STATEFP,
CASE
WHEN COUNTYFP <> '' THEN COUNTYFP
ELSE 'missing'
END COUNTYFP,
sum(count(*)) over (partition by nifi_date,name) as freq_all_num,
sum(count(name)) over (partition by nifi_date,name,STATEFP) as freq_state_num,
count(name) as freq_county_num
from num2
group by nifi_date, name,STATEFP, COUNTYFP
) by serv20;
execute (alter table num3 CHANGE name name varchar(50)) by serv20;
execute (alter table num3 CHANGE STATEFP STATEFP varchar(10)) by serv20;
execute (alter table num3 CHANGE COUNTYFP COUNTYFP varchar(10)) by serv20;
create table work.deno1 as
select * from connection to cen20
(select * from deno4);
create table work.num1 as
select * from connection to cen20
(select * from num3);
Thank you!
... View more
