Hive: Data not getting copied into Hive table from .csv file (stored on hdfs)

Question

Learning hive, created a table and trying to insert data from a csv file, no error is raised but data inserted is all nulls(not actual data from .csv file).There are 100s of records in the .csv input file(file uploaded into hdfs). Please help me out, thanks in advance.

Following is the sequence of commands executed

hive> CREATE TABLE IF NOT EXISTS CampaignDB (isano int,MemberName string,cityordist string,state string,mobile int,email string,memtype string) comment 'Doc Campaign data' row format delimited stored as textfile;
OK
Time taken: 0.323 seconds
hive> desc CampaignDB;
OK
isano                   int                     None                
membername              string                  None                
cityordist              string                  None                
state                   string                  None                
mobile                  int                     None                
email                   string                  None                
memtype                 string                  None                
Time taken: 0.212 seconds, Fetched: 7 row(s)

hive> LOAD DATA INPATH '/user/hadoop/input/campaignDB-sample.csv' OVERWRITE INTO TABLE CampaignDB;
Loading data to table default.campaigndb
Deleted hdfs://localhost:9000/user/hive/warehouse/campaigndb
Table default.campaigndb stats: [num_partitions: 0, num_files: 1, num_rows: 0, total_size: 239, raw_data_size: 0]
OK
Time taken: 0.536 seconds
hive> CREATE TABLE IF NOT EXISTS CampaignDB (isano int,MemberName string,cityordist string,state string,mobile int,email string,memty                                                                                                                                     select * from CampaignDB;
OK
NULL    NULL    NULL    NULL    NULL    NULL    NULL
NULL    NULL    NULL    NULL    NULL    NULL    NULL
NULL    NULL    NULL    NULL    NULL    NULL    NULL
Time taken: 0.161 seconds, Fetched: 3 row(s)

user3843015 user3843015 · Accepted Answer · 2014-07-23T22:00:49

CREATE TABLE IF NOT EXISTS CampaignDB 
(isano int,
MemberName string,
cityordist string,
state string,
mobile int,
email string,
memtype string) 
comment 'Doc Campaign data' 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' --if it is comma separated file
STORED AS TEXTFILE;
location '/user/hadoop/input/campaignDB-sample.csv';

The above will create the metadata. To load data,

LOAD DATA LOCAL INPATH '/user/hadoop/input/campaignDB-sample.csv' OVERWRITE INTO TABLE CampaignDB;

--This could happen if you don't specify a delimiter where the data in the file is using one.

Hive: Data not getting copied into Hive table from .csv file (stored on hdfs)

2 Answers