Transferring hive table from one database to another

Question

I need to move a hive table from one database to another. How can I do that?

Answer 1

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.

Answer 2

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.

Answer 3

You can try - CTAS

USE NEW_DB;

CREATE TABLE table
AS
SELECT * FROM OLD_DB.table;

DROP TABLE OLD_DB.table;

Answer 4

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 <table name> 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 <table name> 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’;

Answer 5

If the tables are partitioned, then basically you can copy partitioned data from old table to new table and delete old tables.

1. use new_db;

2. Create new table in new database:

   Create Table table_name;
   

3. Insert data in new tables from old tables using the command:

   insert into new_table_name partition (partition_column='value') 
     select col1, col2, col3, col4 from old_db.old_table_name 
     where partition_column='value';
   

Answer 6

The database you want to migrate it would have provided you with a database connector. With the help of sqoop and the database connector, you could be able to migrate it. would be great if you are more specific about the type of database you want to migrate the data to

Answer 7

https://issues.apache.org/jira/browse/HIVE-2496

that improvement is still open. I know that it's possible in Impala, not in Hive currently.

Development seems stalled. You can vote up for that issue on that page to bring some attention.

Answer 8

There are many methods given in answers but no guide on when to use what,

1. Use CTAS when your target is a Managed table:

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

2. If you just want to do it with a simple table name change, but the location of the table doesn't change:

   alter table old_database.table_a rename to new_database.table_a;
   

3. Using CREATE LIKE and INSERT:

   CREATE NEW_DB.TABLE table LIKE OLD_DB.TABLE;
   INSERT INTO TABLE new_table_name partition (partition_column) select col1, col2, col3, col4 from old_db.old_table_name where partition_column='value';
   DROP TABLE old_db.old_table_name;