Sas to hive tables ingestion

[bhaskersuresh]

I am ingested one sas dataset into hive .
Table created in hive
Now I am trying to append the data to the existing table in hive ,
Pls let me know how can I proceed.
Thanks in advance

[reprui]

libname sasdata 'SAS-library';

libname hdp hadoop user=myusr1 pwd=mypwd1 server='hdpcluster'   schema=statsdiv;

proc append base=[hdp.Hadoop_Hive_table_name] data=[sasdata.sasdataset_name];

run;

[bhaskersuresh]

Thanks.
I tried this but partition variable, value is not reflecting.

Here I am using year_month=201803, hardcoded in code.

Pls suggest,thanks.

[Reeza]

Too vague.

[Reeza]

Assuming you're using libname you can use the standard SAS approaches, which include PROC APPEND, a data step or PROC SQL insert.

[bhaskersuresh]

Thanks.
When I use proc append, partition variable value not displaying.

When I use proc SQL insert tablename

Below is sample error. Pls help.

Error: value 496 on the select clause does not match the data type of corresponding column listed after insert table name

[Reeza]

Well, do the types match? What type is the value column on the table you're trying to append and the hive table?

[r_behata]

Try these commands directly on Hive :

use <DATABASENAME>;

show create table  <TABLENAME> ;

Post the Output , which would be similar as below.

/**********SAMPLE OUTPUT********************/


1 CREATE TABLE `class`( 
2   `name` string COMMENT 'from deserializer',  
3   `sex` string COMMENT 'from deserializer',  
4   `age` string COMMENT 'from deserializer',  
5   `height` string COMMENT 'from deserializer',  
6   `weight` string COMMENT 'from deserializer') 
7 ROW FORMAT SERDE  
8   'org.apache.hadoop.hive.serde2.OpenCSVSerde'  
9 WITH SERDEPROPERTIES (  
10   'separatorChar'=',')  
11 STORED AS INPUTFORMAT  
12   'org.apache.hadoop.mapred.TextInputFormat'  
13 OUTPUTFORMAT  
14   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
15 LOCATION 
16   'hdfs://<path>' 
17 TBLPROPERTIES ( 
18   'COLUMN_STATS_ACCURATE'='true',  
19   'SAS OS Name'='Linux',  
20   'SAS Version'='9.04.01M4P11092016',  
21   'numFiles'='1',  
22   'numRows'='19',  
23   'rawDataSize'='0',  
24   'totalSize'='644',  
25   'transient_lastDdlTime'='1560450111') 


/*********************************************************/

based on how it is configured the output might differ from above

[bhaskersuresh]

Thanks
In the table I have 600+ fields, and I have to append to monthly. Data for
each month from jan2018 to till this month.

Seeking help from the experts who worked in sas to hive tables ingestion
Pls help
Thanks

[bhaskersuresh]

hi,

sas data type numeric will be changed to double in hive.

sas data type char will be changed to varchar in hive.

i created the table in hive with partition year_month=201801 , using the data set in sas.

now i am trying to append data to existing hive table with pratition year_month=201802.

using proc sql insert syntax. getting errors as below like this 20  same errors i am getting , when i check fields in sas and hive shown  below , pls help.

129 R50_DUNGR    NUM 3   --- in sas  
`r50_dungr` double,      --- in hive

182 R50_SIGN_ID    Char 1  --- in sas 
`r50_sign_id` varchar(1),     --- hive

183 R50_CTOPEN    Num 5   --- sas
`r50_ctopen`        double,    --- in hive

ERROR: Value 182 on the SELECT clause does not match the data type of the corresponding column listed after the INSERT table name.
ERROR: Value 183 on the SELECT clause does not match the data type of the corresponding column listed after the INSERT table name.

[Reeza]

Show a proc contents on each of the tables please. Or post the exact error the error you're receiving from the log - it usually helps debugging the issue. Alternatively, if you're paying for SAS, you can contact SAS tech support directly and they'll work with the developers and such to get you sorted out. Paid support is a great feature, use it if you have it 🙂