HIVE LOAD DATA INPATH

3
votes

I tried to create a table in hive using the following command: 

CREATE TABLE apple (dates STRING, open INT, high INT, low INT, close INT, adj_close DECIMAL, vol INT) row format delimited fields terminated by ',' lines terminated by '\n' tblproperties ("skip.header.line.count"="1");**

Then I tried to load data using the command: 

LOAD DATA INPATH '/user/root/stockdata/APPL.csv' OVERWRITE INTO TABLE apple;

The file APPL.csv is stored in HDFS

Executing the above resulted in the following error:

Error: Error while compiling statement: FAILED: SemanticException Unable to load data to destination table. Error: The file that you are trying to load does not match the file format of the destination table. (state=42000,code=40000)

Can someone help resolve this?

3
hadoophive

3 Answers

1
votes

Put the file directly into table location.

hdfs dfs -copyFromLocal /user/root/stockdata/APPL.csv' /user/<username>/apple

Substitute destination location with your table location. You can check it using DESCRIBE FORMATTED tablename.

1
votes

You can load the data into the table in multiple ways!! But make sure that table format is valid.

  • First put the specific table into the table location.

    Syntax: hadoop fs -put source_path destination_hive_table_location

    Ex: hadoop fs -put APPL.csv /user/test.db/apple/APPL.csv

  • Once file in Hadoop file system, we can load the file into the table via load command in hive console

load data inpath '/user/test.db/apple/APPL.csv' into table apple ;

PS: You are loading the CSV data.so make sure that the table has proper text input & output file format.

STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
1
votes

Maybe this has to do with the creation of your table I had the same error I resolved it by adding "STORED AS TEXTFILE" at the end of the hive query of the table creation

earlier my table was

    CREATE TABLE %(output_db)s.%(selected_query_tbl)s
    (
       query                     VARCHAR(255)
      ,ATC_beyond_10_percentage  DECIMAL(25,25)
      ,ATC_rate                  DECIMAL(25,25)
      ,block_5_tau               DECIMAL(25,25)
      ,Kendall_tau               DECIMAL(25,25)
    );

now it is

     CREATE TABLE %(output_db)s.%(selected_query_tbl)s
    (
       query                     VARCHAR(255)
      ,ATC_beyond_10_percentage  DECIMAL(25,25)
      ,ATC_rate                  DECIMAL(25,25)
      ,block_5_tau               DECIMAL(25,25)
      ,Kendall_tau               DECIMAL(25,25)
    )STORED AS TEXTFILE;

Hope it helps :)