topic Re: SAS Reading Hive/HADOOP ~ Variable Length in SAS Programming

SAS Reading Hive/HADOOP ~ Variable Length

JayS — Thu, 06 Sep 2018 18:04:50 GMT

I have been able to connect my SAS EG to an instance of HADOOP / Hive (just figuring out all this HADOOP stuff as I go)

However when I read in the data, ALL the character variables are 32,767 in length, and have formats?

I have a routine that checks the max length and see that at MOST a length is maybe 250.

I assume this is a HADOOP thing not a SAS Thing?

I have coded up a manual workaround to assign much more realistic lengths, but it's manual and not a solution.

My Questions

1) Did our 'HADOOP guys' just not take the time to assign lengths?

2) Is this standard, and everyone has to deal with it

2) Any nice way to handle it?

TIA, Jay

Re: SAS Reading Hive/HADOOP ~ Variable Length

ChrisNZ — Thu, 06 Sep 2018 21:36:29 GMT

How do you read the Hadoop data?

Hadoop storage is very basic in terms of metadata, but normally column length is known.

Re: SAS Reading Hive/HADOOP ~ Variable Length

LinusH — Fri, 07 Sep 2018 08:52:55 GMT

1) Probably yes. Hadoop "guys" in my experience doesn't care about string lenghts, perhaps enforcing some kind of "schema on read" feeling.

2) Don't know, up to the site to its standard I guess.

3) Define nice ;-).

I can tink of two ways:

Use the DBMAX_TEXT libname option. Even if that is kinda blunt, but it's better to have 500 rather than 32K columns.
ACCESS to Hadoop (Hive) honors the SASFMT attribute. If you in some way can generate a script that can generate the code based on any source schema this can quite easily be adopted by the Hadoop people. If not, this can be quite cumbersome, depending on the no of columns you wish to specify. This can be doneeither through SQL explicit pass-through from SAS (https://communities.sas.com/t5/SAS-Data-Management/SAS-Access-Hadoop-String-Columns-Field-Length-Options/td-p/215164) or directly in Hive.

Re: SAS Reading Hive/HADOOP ~ Variable Length

JayS — Fri, 07 Sep 2018 12:57:13 GMT

Thanks Chris for the feedback..

To connect I use all the hadoop server=URI, Server, Schema string values....

Proc Data LIBNAME.MyDay;

Set LIBNAME.HADOOP_Data;

Run;

For now I have common sensed the Length Statement after take a peek into the data...

Proc Data LIBNAME.MyDay;

Length String5Code $5

Length LongDesc $150;

Set LIBNAME.HADOOP_Data;

Run;

One thing I have noticed is the numeric are 8. which is correct...

Example of other values when read in..

Numeric Variable = Num 8 11. 11.

Date Variables = Num 8 DATETIME25.6 DATETIME25.6

Thanks, Jay

Re: SAS Reading Hive/HADOOP ~ Variable Length

JayS — Fri, 07 Sep 2018 13:06:56 GMT

Thanks Linus,

I'll touch base with the HADOOP 'Guys'... Most likely this is the root cause...

The DBMAX_TEXT option, good idea I'll check it out.

500 is better than 32k for now, with the exception of a savings account!

Right now I have forced all values after creating a Proc Contents to look at the string values, See my other response on this thread

I'm also working on trying to figure out a way to run the MAX_Length code, take the output data load it into a SAS FMT and use the SAS Format Put() to replace the traditional Length Statement.

I'll keep trying for a few more hours while I contact the HADOOP guys 😉

Thanks, I'll post my final solution as it may help other...

Re: SAS Reading Hive/HADOOP ~ Variable Length

JayS — Fri, 07 Sep 2018 15:01:55 GMT

Update:

This code dbmax_text=175 Update worked fine for me.
For now I can live with this.

Thanks to everyone who provided input.