topic Re: Sas to hive tables ingestion in Developers

Sas to hive tables ingestion

bhaskersuresh — Tue, 22 Oct 2019 15:33:52 GMT

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

Re: Sas to hive tables ingestion

reprui — Tue, 22 Oct 2019 15:50:15 GMT

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;

Re: Sas to hive tables ingestion

Reeza — Tue, 22 Oct 2019 15:51:11 GMT

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

Re: Sas to hive tables ingestion

bhaskersuresh — Tue, 22 Oct 2019 15:54:11 GMT

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

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

Pls suggest,thanks.

Re: Sas to hive tables ingestion

Reeza — Tue, 22 Oct 2019 15:55:00 GMT

Too vague.

Re: Sas to hive tables ingestion

bhaskersuresh — Tue, 22 Oct 2019 16:08:41 GMT

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

Re: Sas to hive tables ingestion

Reeza — Tue, 22 Oct 2019 16:38:13 GMT

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

Re: Sas to hive tables ingestion

r_behata — Tue, 22 Oct 2019 17:23:07 GMT

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

Re: Sas to hive tables ingestion

bhaskersuresh — Tue, 22 Oct 2019 17:29:11 GMT

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

Re: Sas to hive tables ingestion

bhaskersuresh — Wed, 23 Oct 2019 09:35:10 GMT

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.

Re: Sas to hive tables ingestion

Reeza — Wed, 23 Oct 2019 15:16:35 GMT

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 🙂