CODEWITHSUNDEEP
sqlhivehiveql
sharp
sharp

Reputation: 2158

Copy tables in HIVE, from one database to another database

In a database, I have 50+ tables, I was wondering is there any way to copy these tables into second database at one shot?

I have used this, but running this 50+ times isn't efficient. 

create table database2.table1 as select * from database1.table1;

Thanks!

Upvotes: 2

Views: 13003

Answers (2)

Farooque
Farooque

Reputation: 3766

Copying data from one database table to another database table in Hive is like copying data file from existing location in HDFS to new location in HDFS.

The best way of copying data from one Database table to another Database table would be to create external Hive table in new Database and put the location value as for e.g. LOCATION '/user/hive/external/ and copy the file of older table data using distcp to from the old HDFS location to new one.

Example: Existing table in older Database:
CREATE TABLE stations( number STRING, latitude INT, longitude INT, elevation INT, name STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE; LOAD DATA LOCAL INPATH "/home/cloudera/Desktop/Stations.csv"

Now you create external table in new Database:
CREATE EXTERNAL TABLE external_stations( number STRING, latitude INT, longitude INT, elevation INT, name STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/user/hive/external/';

Now you just copy the data file from /user/hive/warehouse/training.db/stations/ to /user/hive/external/ using distcp command. These two paths are specific to my hive locations. You can have similarly in yours.

In this way you can copy table data of any number.

Upvotes: 3

sunil
sunil

Reputation: 1279

One approach would be to create your table structures in the new database and use distcp to copy data from the old HDFS location to new one.

Upvotes: 0

Related Questions