@Aarya wrote:
Hi Alexal,
I just started working on SAS from past 2 days and i went through the links you provided me and i did not understand anything.
...
libname aaa hadoop server=&svr hdfs_tempdir='/tmp/sastmp' user = <username>
password = <pxxx> schema=<schema name> port 10000
DBCREATE_TABLE_OPTS=&stng
subprotocol=hive2;
libname xxx '/workspace/abc/xyz';
data aaa.test;
set xxx.abc.test;
run;
LOCATION: libname xxx '/workspace/abc/xyz';
This is the place where they drop ".sas7bdat" files and this location keeps changing. Based on that information, we make modidfication.
I ran this script on a 0.3GB sas7bdat file and it created a test table successfully in HIVE. But when i query Hive table,
select count(1) from test;
I do not see any records. How a 0.3GB file does not contain any data? am i missing something here? how can i check the contents of sas7bdat file?
Any help will be greatly appreciated.
First understand how SAS uses librefs to find datasets. You use a LIBNAME statement to define a LIBREF that points to a specific location where datasets are stored. You then use this libref (essentially it is an alias for the location) by prefixing it to the the name of a specific dataset in that libraray. Some examples from your codes: SASHELP.CLASS or AAA.TEST. (Notice that your code above is invalid since you tried to use two periods in the reference in the SET statement.)
In this block of code you are defining two librefs. One to point to physical directory on your SAS machine where the SAS datasets are stored and one to point to a Hadoop environment. One advantage of this structure is you can create a program to copy the data that is independent of the physical locations, just change the LIBNAME statement and the same code with read and/or write to the locations they point to.
So make sure that the location you are pointing your libref in the SAS code is where you are trying to read when using other tools to read the hadoop files.
Also make sure that the code you ran worked and actually created the files in the hadoop cluster. Check the SAS log from the step that creates the file.
So let's look at your libname statement to define the connection to hadoop. First let's give it a more meaningful libref. Remember that librefs are limited to 8 characters.
libname myhive hadoop
user = <username> password = <pxxx>
server=&svr port 10000
hdfs_tempdir='/tmp/sastmp'
schema=<schema name>
DBCREATE_TABLE_OPTS=&stng
subprotocol=hive2
;
I would assume that the SCHEMA= setting is the one that is telling the hadoop server where you want to find/write tables.
Then your LIBNAME statement to point to the SAS dataset. Again let's use a little more meaningful libref.
libname mysas '/workspace/abc/xyz';
So this is saying that on the machine where SAS is running use the path specified to find/create SAS datasets. With a path like that it looks like SAS is running on Unix. So the physical files in that directory should be in all lower case with an extension of .sas7bdat. There might be some other files there also if you are using other SAS features. For example if any of your datasets had indexes defined then there will be a separate file to store the index. You could also create catalogs, usually for things like format catalogs.
If the folder where the SAS files is frequently changing then this is the statement that needs to change. I noticed that your program was using some macro variables to help you create your libref to Hadoop. You could do the something similar to make it easier to change to location for the SAS datasets. You could set the path into macro variable and then use that macro variable to define the libref.
%let saspath = /workspace/abc/xyz ;
libname mysas "&saspath.";
Notice the use of double quotes instead of single quotes. Macro variable references are not resolved inside of single quotes.
Now you can use these two librefs to reference datasets (a.k.a. "tables"). For example here is a data step to create a new table in hadoop from an existing SAS dataset using the two librefs we defined above.
data myhive.myhivetable ;
set mysas.mysasdataset ;
run;
In a future SAS session you then reference that same table you would just need to make sure to define a libref using the same settings so that SAS can find it. The actual libref name (aka alias) you use could be different. It is the details about the server and schema etc that is important for SAS to know where to find it. For example tomorrow you might want to pull out from Hadoop just the records where MYVAR='T'.
libname oldhive hadoop ....... ;
data work.test ;
set oldhive.myhivetable ;
where myvar = 'T' ;
run;
When you run a simple data step like this you are telling SAS to make a NEW dataset. That is why there is no "table definition". It is all done as part of making a new dataset, so there is no need to separate the table creation and the table loading into two steps. If you did want to append data to an existing table in hive then you might want to use PROC APPEND instead.
proc append base=myhive.myhivetable data=mysas.mysasdataset ;
run;
