Copy the data or table structure from one table to another in Hive

In some cases, you may want to copy or clone or duplicate the data ,structure of Hive table to a new table. To achieve this, Hive provides the options to create the table with or without data from the another table.

Copy the data from one table to another table in Hive

Using Create Table As Select (CTAS) option, we can copy the data from one table to another in Hive

Here we need to mention the New table name after the Create Table statement and the Older table name should be after the Select * From statement.

Example : Create Table as Select in Hive

We have a transaction table as below in Hive. Now we want to copy the data to another new table like Transaction_Backup in the same database.

Example for Create table as Select in Hive
Example for Create table as Select in Hive

The backup table is created successfully. lets select the data from the Transaction_Backup table in Hive

Copy the data from one table to another in Hive
Copy the data from one table to another in Hive

Copy the table structure in Hive

You want to create the new table from another table. But you don’t want to copy the data from the old table to new table. In that case, We can use Create table Like option in Hive.

Mention the New table name after the Create table statement and Old table name should be after Like statement.

Example : Create the new table from another table without data

Example for Create table like in Hive
Example for Create table like in Hive

The Transaction_new table is created from the existing table Transaction. As expected, it should copy the table structure alone. To confirm that, lets run the select query on this table.

Table Structure copy in Hive
Table Structure copy in Hive

Finally the table structure alone copied from Transaction table to Transaction_New.

Recommended Articles