Error loading csv data to Hive table

Question

I have a csv file in hadoop and I have a Hive table, now I want to load this csv file into this Hive table

I used load LOAD DATA local 'path / to / csv / file' overwrite INTO TABLE tablename;

ended up with this error:

Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ", : Unable to retrieve JDBC result set for LOAD DATA local 'path/to/csv/file' overwrite INTO TABLE tablename (Error while processing statement: FAILED: ParseException line 1:16 missing INPATH at ''path/tp csv/file'' near '<EOF>' ) 

Note: I am trying to use an RJDBC connection in r

Answer 1

I developed a tool for creating hive scripts from a csv file. The following are some examples of how files are created. Tool - https://sourceforge.net/projects/csvtohive/?source=directory

• Select the CSV file using Browse and set the root directory of hasoop ex: / user / bigdataproject /

• The tool generates a Hadoop script with all the csv files, and the following example is a Hadoop script created to insert csv into Hadoop

   #!/bin/bash -v 
   hadoop fs -put ./AllstarFull.csv /user/bigdataproject/AllstarFull.csv hive -f ./AllstarFull.hive 
   

  hadoop fs -put ./Appearances.csv /user/bigdataproject/Appearances.csv hive -f ./Appearances.hive


  hadoop fs -put ./AwardsManagers.csv /user/bigdataproject/AwardsManagers.csv hive -f ./AwardsManagers.hive

• Sample generated hive scripts

   CREATE DATABASE IF NOT EXISTS lahman; 
   USE lahman; 
   CREATE TABLE AllstarFull (playerID string,yearID string,gameNum string,gameID string,teamID string,lgID string,GP string,startingPos string) row format delimited fields terminated by ',' stored as textfile; 
   LOAD DATA INPATH '/user/bigdataproject/AllstarFull.csv' OVERWRITE INTO TABLE AllstarFull; 
   SELECT * FROM AllstarFull; 

Thanks Vijay

Answer 2

I think the command is to load the CSV into the Hive table (when the CSV is in HDFS).

 LOAD DATA INPATH '/user/test/my.csv' INTO TABLE my_test; 

Answer 3

Since your file is already present in HDFS, delete the Local keyword

LOAD DATA inpath 'path / to / csv / file' overwrite INTO TABLE tablename;