Loading data into Hive table

Question

CREATE TABLE IF NOT EXISTS TestingTable2 
( 
USER_ID BIGINT, 
PURCHASED_ITEM ARRAY<STRUCT<PRODUCT_ID: BIGINT,TIMESTAMPS:STRING>> 
) ROW FORMAT
DELIMITED FIELDS TERMINATED BY '-'
collection items terminated by ','
map keys terminated by ':'
LINES TERMINATED BY '\n' 
STORED AS TEXTFILE
LOCATION '/user/rkost/output2';

Below is my data which is in only one row data that I need to upload it in above table.

1015826235-[{"product_id":220003038067,"timestamps":"1340321132000"},{"product_id":300003861266,"timestamps":"1340271857000"},{"product_id":140002997245,"timestamps":"1339694926000"},{"product_id":200002448035,"timestamps":"1339172659000"},{"product_id":260003553381,"timestamps":"1339072514000"}]-

After uploading the data when I do select query, I am not seeing data correctly. I should be getting only one row as below but I am not getting the below result in the table

**USER_ID**     **PURCHASED_ITEM**
1015826235     [{"product_id":220003038067,"timestamps":"1340321132000"},    {"product_id":300003861266,"timestamps":"1340271857000"},    {"product_id":140002997245,"timestamps":"1339694926000"},    {"product_id":200002448035,"timestamps":"1339172659000"},    {"product_id":260003553381,"timestamps":"1339072514000"}]

Instead of above data, I am getting something like this in my table data after I do select query. Anything wrong with the delimeter?

1015826235      [{"product_id":null,"timestamps":" 220003038067"},{"product_id":null,"timestamps":" \"1340321132000\"}"},{"product_id":null,"timestamps":"  
                                 300003861266"},{"product_id":null,"timestamps":" \"1340271857000\"}"},{"product_id":null,"timestamps":" 140002997245"},
                                      {"product_id":null,"timestamps":" \"1339694926000\"}"},{"product_id":null,"timestamps":" 200002448035"},
                                            {"product_id":null,"timestamps":" \"1339172659000\"}"},{"product_id":null,"timestamps":" 260003553381"},
                                                       {"product_id":null,"timestamps":" \"1339072514000\"}]"}]

Can anyone point me what wrong I am doing?

You may check this link http://stackoverflow.com/a/31130602 for auto hive scripts generation tool. — Vijay Kumar, Jul 02 '15 at 10:44

score 0 · Answer 1 · answered Jul 06 '12 at 19:05

0

Add double quotes to product id

1015826235-[{"product_id":"220003038067","timestamps":"1340321132000"},{"product_id":"300003861266","timestamps":"1340271857000"},{"product_id":"140002997245","timestamps":"1339694926000"},{"product_id":"200002448035","timestamps":"1339172659000"},{"product_id":"260003553381","timestamps":"1339072514000"}]-

answered Jul 06 '12 at 19:05

Sathishkumar

3,394
4
20
23

No, it should not be like that. I figured that out on my own. Actual data that needs to be uploaded should be somehow like this- `1015826235-220003038067:1340321132000,300003861266:1340271857000,140002997245:1339694926000,200002448035:1339172659000,260003553381:1339072514000` – arsenal Jul 06 '12 at 19:20

score 0 · Accepted Answer · answered Jul 06 '12 at 19:20

0

I have figured it out on my own. The whole data that needs to be loaded should be somehow like this-

1015826235-220003038067:1340321132000,300003861266:1340271857000,140002997245:1339694926000,200002448035:1339172659000,260003553381:1339072514000

answered Jul 06 '12 at 19:20

arsenal

23,366
85
225
331

Loading data into Hive table

2 Answers2