CODEWITHSUNDEEP
amazon-web-serviceshadoophivehdfshiveql
Ruth Ineh
Ruth Ineh

Reputation: 43

Unable to load data in Hive table in the correct form

Hi Please i have this create statement in for external table in Hive, but my data is not consistent - so when i run it, i get Null?

create external table sampleartistdata(
    artistid int,
    artistname string
) 
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe' 
WITH SERDEPROPERTIES ("field.delim"="# ") 
STORED AS TEXTFILE
    location '/user/users/sampledata/';
select * from sampleartistdata limit 3;

this is what the data looks like:

1134999 06Crazy Life
10113088    Terfel, Bartoli- Mozart: Don
6826647 Bodenstandig 3000
10186265    Jota Quest e Ivete Sangalo
6828986 Toto_XX (1977
10236364    U.S Bombs -
1135000 artist formaly know as Mat
10299728    Kassierer - Musik für beide Ohren
10299744    Rahzel, RZA

result:

sampleartistdata.artistid       sampleartistdata.artistname
NULL    NULL
NULL    NULL
NULL    NULL

Upvotes: 0

Views: 38

Answers (1)

Ruth Ineh
Ruth Ineh

Reputation: 43

I was able to resolve it by changing the values of the row delimiter, rather than using ROW FORMAT SERDE, i used

drop table sampleartistdata;
create external table sampleartistdata(
    artistid int,
    artistname string
) ROW format DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE
    location '/user/jovyan/sampledata/';

Upvotes: 1

Related Questions