SAS EG to Hadoop Hive Data step creates table with no data

[saumyasri]

Hi All,

I am creating a Hive table from SAS. When I write to hadoop from SAS EG I get results that 10 rows have been created. However, I see an empty table with just column names in hadoop.

I am using following code:

option set=SAS_HADOOP_CONFIG_PATH="/sas/hadoopfiles/conf";
option set=SAS_HADOOP_JAR_PATH="/sas/hadoopfiles/lib";

 

LIBNAME dmart HADOOP  PORT=xxx SERVER="xxxx"  SCHEMA=p_mart;

proc contents dmart =dmart.tb_temp; run;

 

data work.local_sample;
set dmart.tb_temp(obs=10 keep=col dbsastype=(col='CHAR(200)') );
run;

 

data dmart .tb_load;
set work.local_sample;
run;

 

Any help on this is highly appreciated.

[AnandVyas]

Are you able to create hive table directly using beeline using the same ID that you are using from SAS to make connection to hive? It's always good to test sample scenario out of SAS just to make sure it works.

Would also suggest to add trace options to the code to get more detailed logs on whats causing this.

[saumyasri]

Hi @AnandVyas,

My create table command works fine outside SAS and I can see data in my Hive table.

However, I am not getting data from SAS. I turned on trace option. I am getting an output given below:

37         data mart.cars_test;
38            set sashelp.cars;
39         run;

 
HADOOP_1: Prepared: on connection 1
SHOW TABLES IN `mart` 'CARS_TEST'
 
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
2                                                          The SAS System                            Thursday, 25 June 2020 18:57:00

 
HADOOP_2: Executed: on connection 2
CREATE TABLE `mart`.`CARS_TEST` (`Make` VARCHAR(13),`Model` VARCHAR(40),`Type` VARCHAR(8),`Origin` VARCHAR(6),`DriveTrain` 
VARCHAR(5),`MSRP` DOUBLE,`Invoice` DOUBLE,`EngineSize` DOUBLE,`Cylinders` DOUBLE,`Horsepower` DOUBLE,`MPG_City` 
DOUBLE,`MPG_Highway` DOUBLE,`Weight` DOUBLE,`Wheelbase` DOUBLE,`Length` DOUBLE) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' 
LINES TERMINATED BY '\012' STORED AS TEXTFILE TBLPROPERTIES ('SAS OS Name'='Linux','SAS Version'='9.04.01M6P11072018')
 
NOTE: There were 428 observations read from the data set SASHELP.CARS.
NOTE: The data set MART.CARS_TEST has 428 observations and 15 variables.
 
HADOOP_3: Executed: on connection 2
LOAD DATA INPATH '/tmp/sasdata-xxxxx.dlv' OVERWRITE INTO TABLE `mart`.`CARS_TEST`
 
NOTE: DATA statement used (Total process time):
      real time           4:04.02
      cpu time            0.18 seconds
      

40         
 
HADOOP_4: Prepared: on connection 0
DESCRIBE FORMATTED `mart`.`CARS_TEST`
 
 
HADOOP_5: Prepared: on connection 0
SELECT * FROM `mart`.`CARS_TEST` LIMIT 0 -- /* initial prepare */
 

It looks fine from the output but still no luck. Do you have any idea?

[AnandVyas]

I don't see any error/warn on the output. Try to view the table created from SAS using beeline. If that also doesn't work you may have to check logs on hadoop side to see if there are any errors being reported.

Also, I would advise you to raise a SAS TS track by sharing these details at support@sas.com for assistance.