question Re: how we can get the data from dropped external table of Hive? in Archives of Support Questions (Read Only)

how we can get the data from dropped external table of Hive?

satyap — Mon, 27 Feb 2017 21:43:32 GMT

suppose I have dropped an external table(EMP) the table was stored at /user/hive/satya/.

As we know the metadata will be deleted if we will drop the external table and actual data will be there.

So my Question is that how we can restore the external table(EMP) how we will get the data.

would anyone give me the steps need to perform to get the data?

Re: how we can get the data from dropped external table of Hive?

aervits — Mon, 27 Feb 2017 22:08:04 GMT

Use a create external table syntax on top of your data. Main thing to remember is create external and location in the syntax below, the rest depends on your file type and delimiter. https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.3.0/bk_dataintegration/content/moving_data_from_hdfs_to_hive_external_table_method.html

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';

Re: how we can get the data from dropped external table of Hive?

satyap — Tue, 28 Feb 2017 14:35:14 GMT

@Artem Ervits

Hi Artem,

Thanks for your reply. I did the same thing and I am able to get back the data. The most surprising thing I got, I have created 2-3 tables even with different schema it's showing the same data whatever the data was in an old table for extra column it's showing the null.

So every time when we have to create an external table we should give the different directory path?

Re: how we can get the data from dropped external table of Hive?

rajsyrus — Tue, 28 Feb 2017 19:06:15 GMT

@satya gaurav

Directory path is where, your hive data resides, if you give empty folder then table will be empty, so you cannot change if you want the same data to be loaded to the table. The columns with will null is due to the number columns you defined in schema may more than the delimited data.