Transferring hive table from one database to another

Since 0.14, you can use following statement to move table from one database to another in the same metastore:

use old_database; alter table table_a rename to new_database.table_a

The above statements will also move the table data on hdfs if table_a is a managed table.

answered Jun 2, 2015 at 2:45 1,077 9 9 silver badges 9 9 bronze badges

Fastest and simplest solution. Wondering why it didn't click to me and so many others. :| Thanks. Should be the accepted answer.

Commented Feb 23, 2017 at 14:02 This is the right answer, provided you are using Hive metastore 0.14+. Commented Sep 1, 2017 at 18:28 @user2942227 - Why is this not accepted as the answer yet? Commented Jan 12, 2018 at 14:25

I'm using hive 2.1.1 the table is managed and the files/ table data are not moving with the rename. Is there anything else I need to do so the files move with it?

Commented Aug 23, 2021 at 13:20

create external table new_db.table like old_db.table location '(path of file in hdfs file)';

if you have partition in table then you have to add partition in new_db.table.

answered Feb 10, 2015 at 14:34 234 1 1 gold badge 2 2 silver badges 12 12 bronze badges

USE NEW_DB; CREATE TABLE table AS SELECT * FROM OLD_DB.table; DROP TABLE OLD_DB.table;

answered Apr 17, 2015 at 5:25 user 923227 user 923227 2,677 4 4 gold badges 30 30 silver badges 48 48 bronze badges

Concise and simple, I like it! Note that CTAS has these restrictions: 1) The target table cannot be an external table. 2) The target table cannot be a list bucketing table.

Commented Jun 4, 2020 at 13:29

This might be helpful to you.

EXPORT TABLE table_or_partition TO hdfs_path; IMPORT [[EXTERNAL] TABLE table_or_partition] FROM hdfs_path [LOCATION[table_location]];

Some sample statements would look like:

EXPORT TABLE TO 'location in hdfs'; Use test_db; IMPORT FROM 'location in hdfs'; Export Import can be appled on a partition basis as well: EXPORT TABLE PARTITION (loc="USA") to 'location in hdfs';

The below import commands imports to an external table instead of a managed one

IMPORT EXTERNAL TABLE FROM 'location in hdfs' LOCATION ‘/location/of/external/table’;