Getting file format exception while trying to import CSV into hive

Question

I have a Hive table with the following structure:

app_id                  string                                      
platform                string                                      
app_name                string                                      
developer_name          string                                      
rating                  double                                      
category_csv            string                                      
num_ratings             bigint                                      
num_recommendations     bigint                                      
num_downloads           bigint                                      
size_mbs                double                                      
price                   double                                      
in_app_purchase_confidence  double                                      
content_maturity        string                                      
badges_csv              string                                      
bundle_id               string                                      
rfi_brand_rating        string                                      
category_id_csv         string                                      
country_code            string                                      
permissions             string

I am trying to import the following one line CSV into it (which has tab separated values):

1000011509  IOS Emily+Grows+Up+-+Journey+from+Birth+to+Adulthood    Ninjafish+Studios   Games,Role-Playing,Family   0   0   0   0   59  0   1   4+  iosUniversal    com.ninjafishstudios.emilygrowsup       6014,7014,7009  MY  abc

I am using the following command:

hive -e "LOAD DATA LOCAL INPATH '/home/usernamehere/a0/a1/new' OVERWRITE INTO TABLE mobile_repo;"

But I'm getting the following exception:

Failed with exception Wrong file format. Please check the file's format. 18/03/30 12:05:39 ERROR exec.Task: Failed with exception Wrong file format. Please check the file's format. org.apache.hadoop.hive.ql.metadata.HiveException: Wrong file format. Please check the file's format.

Can someone please tell me what's the problem ?

EDIT: The table I'm trying to import into was created using the following command:

CREATE TABLE mobile_repo (app_id string, platform string, app_name string, developer_name string, rating double, category_csv string, num_ratings bigint, num_recommendations bigint, num_downloads bigint, size_mbs double, price double, in_app_purchase_confidence double, content_maturity string, badges_csv string, bundle_id string, rfi_brand_rating string, category_id_csv string, country_code string, permissions string);

how did you specify row format and delimiters when creating the table? can you show them as well? — Vamsi Prabhala
@VamsiPrabhala I have added the command I used to create the table — Ahmad

Vamsi Prabhala Vamsi Prabhala · Accepted Answer · 2018-03-30T17:22:16

Specify the delimiter when creating the table so when you load data from a file it will be parsed accordingly.

CREATE TABLE mobile_repo 
(app_id string, platform string, app_name string, developer_name string, 
 rating double, category_csv string, num_ratings bigint, num_recommendations bigint,
 num_downloads bigint, size_mbs double, price double, in_app_purchase_confidence double, 
 content_maturity string, badges_csv string, bundle_id string, rfi_brand_rating string, 
 category_id_csv string, country_code string, permissions string)
row format delimited 
fields terminated by '\t'
location 'specify a HDFS location'
;

Getting file format exception while trying to import CSV into hive

1 Answers