Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

Highlighted

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

New Contributor

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.png
5 REPLIES 5

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

Super Guru

@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.

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

New Contributor

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 .

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

Super Guru

@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

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

New Contributor

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

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

Super Guru

@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.

Don't have an account?
Coming from Hortonworks? Activate your account here