topic SAS EG to Hadoop Hive Data step creates table with no data in SAS Enterprise Guide

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

saumyasri — Thu, 25 Jun 2020 14:52:05 GMT

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.

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

AnandVyas — Thu, 25 Jun 2020 15:49:31 GMT

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.

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

saumyasri — Thu, 25 Jun 2020 17:29:19 GMT

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?

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

AnandVyas — Mon, 29 Jun 2020 06:07:33 GMT

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.