Transferring hive table from one database to anoth

Question

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

Answer 1

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 2

You can try - CTAS

USE NEW_DB;

CREATE TABLE table
AS
SELECT * FROM OLD_DB.table;

DROP TABLE OLD_DB.table;

Answer 3

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 4

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;
   

Answer 5

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 6

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