topic Re: SAS/Access columns could have a length in SAS of 32767 extract 0 rows in SAS Data Management

SAS/Access columns could have a length in SAS of 32767 extract 0 rows

lydiawawa — Fri, 28 Feb 2020 19:30:18 GMT

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!

Re: SAS/Access columns could have a length in SAS of 32767 extract 0 rows

Patrick — Sat, 29 Feb 2020 15:12:51 GMT

To get to the bottom of this I'd start with a row count of your source tables. Something like below:

select * from connection to cen20
(select count(*) as cnt from deno3);

If you've got rows in your source table then I'd try to create the temporary tables with the correct column attributes right from start.

I'm not sure if below syntax is correct but I'm pretty sure that Hive SQL will allow you to define the column types in the query.

execute 
  (
    create temporary table deno4 as
      select 
        name as name varchar(50),
         CASE
            WHEN STATEFP <> '' THEN STATEFP
            ELSE 'missing'
         END STATEFP varchar(10)
      from deno3
  ) 
  by serv20;

Then may-be issue another select count(*) against the temporary table you've just created to check if you'll get actually data into SAS.

Not sure how your connect statement looks like but may-be investigate how option CONNECTION influences what you get.

And just as a side note:

I'm personally moving more and more to defining database connections via the Libname statement and then for explicit pass-through SQL I'm using syntax: CONNECTION USING <libref> .

Re: SAS/Access columns could have a length in SAS of 32767 extract 0 rows

lydiawawa — Sat, 29 Feb 2020 18:47:52 GMT

thank you so so so much. I will test it out on Monday. This is life-saving.

Re: SAS/Access columns could have a length in SAS of 32767 extract 0 rows

lydiawawa — Mon, 02 Mar 2020 15:47:13 GMT

The syntex do have error. It is saying ERROR missing EOF at name varchar. Maybe adding the alter statement for each string variable is the only solution?

Re: SAS/Access columns could have a length in SAS of 32767 extract 0 rows

lydiawawa — Mon, 02 Mar 2020 16:03:45 GMT

the count statement also generated an error:

ERROR: Prepare error: org.apache.thrift.transport.TTransportException: HTTP Response code: 500
SQL statement: select count(*) as cnt from deno3

Re: SAS/Access columns could have a length in SAS of 32767 extract 0 rows

reprui — Mon, 02 Mar 2020 16:37:29 GMT

Hi! I'm not sure this will answer your specific question, but it may offer an alternate path if all you are trying to do is read STRING formatted column data in Hive with SAS...

Suggestion:

Try using a LIBNAME statement to connect to your Hadoop session first (implicit pass-through method). When doing so, use DBMAX_TEXT= LIBNAME Option (set to whatever "x" length you want - Also, you can use DBSASTYPE= Data Set Option to fix columns one at a time).

This will substring all STRING columns to "x" bytes upon "READ" by SAS. Here's an example:

LIBNAME hdp HADOOP
port=10000
schema=default
host="sasserver.demo.sas.com" /* replace with your server info and other connection values you are using in your CONNECT TO configuration (explicit pass-through method). */
sql_functions=all
dbmax_text=100 /* execute the LIBNAME statement with DBMAX_TEXT= option */
;

proc contents data=hdp.deno3; run; /*similar to SHOW TABLES in Hive so you can see which STRING items got truncated.*/

Proc Freq Data=hdp.deno3; /* This will deliver your counts - Watch out for high cardinality - Other procs for count might be better suited depending on the column values (I.e. PROC MEANS for numeric data */

Table

name

STATEFP

COUNTYFP

;

run;

Good Luck!