Destination table is stored as ORC but the file being loaded is not a valid ORC file

[#1]

org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: SemanticException [Error 30019]: The file that you are trying to load does not match the file format of the destination table. Destination table is stored as ORC but the file being loaded is not a valid ORC file

1.png

[#2]

@satya s I am not sure i completely follow your question but here is a try. I think you are trying to load a orc table with a non orc file. you should either convert the text file to orc, or create external hive table using format of the text file and then load into orc table using select into statement.

[#3]

yes, i am trying to load a orc table with a non orc file, may i know how to convert csv to orc or how to create external hive table .

[#4]

@satya s can extremely useful guide/tutorial is here

https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.3.0/bk_dataintegration/content/moving_data_from...

exactly what you are trying to do

[#5]

Thank you , i will follow, but during installation time of ambari i changed hive.default.fileformat to ORC (default is TextFile)

[#6]

@satya s that is totally fine. that is just default. with hive you are not locked into any format. default just means that, it is default. create a hive table using this format:

CREATE EXTERNAL TABLE IF NOT EXISTS Cars(
        Name STRING, 
        Miles_per_Gallon INT,
        Cylinders INT,
        Displacement INT,
        Horsepower INT, 
        Weight_in_lbs INT,
        Acceleration DECIMAL,
        Year DATE,
        Origin CHAR(1))
    COMMENT 'Data about cars from a public database'
    ROW FORMAT DELIMITED
    FIELDS TERMINATED BY ','
    STORED AS TEXTFILE
    location '/user/<username>/visdata';

and then create another hive table with similar schema but this time have it as orc table

CREATE TABLE IF NOT EXISTS mycars(
        Name STRING, 
        Miles_per_Gallon INT,
        Cylinders INT,
        Displacement INT,
        Horsepower INT, 
        Weight_in_lbs INT,
        Acceleration DECIMAL,
        Year DATE,
        Origin CHAR(1))
    COMMENT 'Data about cars from a public database'
    ROW FORMAT DELIMITED
    FIELDS TERMINATED BY ','
    STORED AS ORC;

then simply load from your text base hive table into the orc table

INSERT OVERWRITE TABLE mycars SELECT * FROM cars;

and now your data has been converted to orc.