Failed to Load Data to Hive Tables

Symptom

After creating a table, a user runs the LOAD command to import data to the table. However, the following problem occurs during the import:

.......
> LOAD DATA INPATH '/user/tester1/hive-data/data.txt' INTO TABLE employees_info;
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)
..........

Cause Analysis

1. The storage format is not specified during table creation, and the default format RCFile is used.
2. However, the data to be imported is in TEXTFILE format.

Solution

This problem is caused by an application defect. You can use a proper method based on site requirements only by ensuring that the storage format specified by the table is the same as the format of the data to be imported.

• Method 1:

  Specify the storage format when creating a table as a user who has the Hive table operation permission. For example:

  CREATE TABLE IF NOT EXISTS employees_info(name STRING,age INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;

  Specify the format of the data to be imported as TEXTFILE.

• Method 2:

  Import RCFile data, but not TEXTFILE data.