BookmarkSubscribeRSS Feed
bhaskersuresh
Calcite | Level 5
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
10 REPLIES 10
reprui
SAS Employee

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;


Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF

View now: on-demand content for SAS users

bhaskersuresh
Calcite | Level 5
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
Super User
Too vague.
Reeza
Super User
Assuming you're using libname you can use the standard SAS approaches, which include PROC APPEND, a data step or PROC SQL insert.
bhaskersuresh
Calcite | Level 5
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
Super User
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
Barite | Level 11

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
Calcite | Level 5
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
Calcite | Level 5

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
Super User
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 🙂

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 3724 views
  • 2 likes
  • 4 in conversation