Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Why all data is loaded to first column of Hive table?

Solved Go to solution

Why all data is loaded to first column of Hive table?

I have Quickstart VM and trying to load data, but all data is stored to first column. What is wrong?

 

/*CREATE TABLE   -- Table will be succesfully created*/
CREATE TABLE IF NOT EXISTS Auto_Insurance_Claims_US  
(Customer String,Country String,StateCode String,State String,ClaimAmount Float,Response String,Coverage String,Education String,EffectiveToDate String,EmploymentStatus String,Gender String,Income String,LocationCode String,MaritalStatus String,MonthlyPremiumAuto String,MonthsSinceLastClaim String,MonthsSincePolicyInception String,NumberOfOpenComplaints Int,NumberOfPolicies Int,PolicyType String,Policy String,ClaimReason String,SalesChannel String,TotalClaimAmount Float,VehicleClass String,VehicleSize String)  
ROW FORMAT DELIMITED
STORED AS TEXTFILE

/*LOAD -- All lines of data loaded, but they are all stored to first column Customer*/
LOAD DATA LOCAL INPATH '/home/cloudera/workspace/MyData/Auto_Insurance_Claims_Sample.csv'
OVERWRITE INTO TABLE Auto_Insurance_Claims_US;

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Why all data is loaded to first column of Hive table?

Guru
It is CSV, so I assume that it is "," delimited?

You will need to tell that to Hive:

CREATE TABLE IF NOT EXISTS Auto_Insurance_Claims_US
(Customer String,Country String,StateCode String,State String,ClaimAmount Float,Response String,Coverage String,Education String,EffectiveToDate String,EmploymentStatus String,Gender String,Income String,LocationCode String,MaritalStatus String,MonthlyPremiumAuto String,MonthsSinceLastClaim String,MonthsSincePolicyInception String,NumberOfOpenComplaints Int,NumberOfPolicies Int,PolicyType String,Policy String,ClaimReason String,SalesChannel String,TotalClaimAmount Float,VehicleClass String,VehicleSize String)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ","
STORED AS TEXTFILE;

Try and see if it works for you.
2 REPLIES 2
Highlighted

Re: Why all data is loaded to first column of Hive table?

Guru
It is CSV, so I assume that it is "," delimited?

You will need to tell that to Hive:

CREATE TABLE IF NOT EXISTS Auto_Insurance_Claims_US
(Customer String,Country String,StateCode String,State String,ClaimAmount Float,Response String,Coverage String,Education String,EffectiveToDate String,EmploymentStatus String,Gender String,Income String,LocationCode String,MaritalStatus String,MonthlyPremiumAuto String,MonthsSinceLastClaim String,MonthsSincePolicyInception String,NumberOfOpenComplaints Int,NumberOfPolicies Int,PolicyType String,Policy String,ClaimReason String,SalesChannel String,TotalClaimAmount Float,VehicleClass String,VehicleSize String)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ","
STORED AS TEXTFILE;

Try and see if it works for you.

Re: Why all data is loaded to first column of Hive table?

You should specify file delimiter and new line character while creating table.



ROW FORMAT DELIMITED FIELDS TERMINATED BY '\u0001' LINES TERMINATED BY '\n'


Don't have an account?
Coming from Hortonworks? Activate your account here