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

Posted on by

Contents

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

CREATE TABLE <New_Table_Name> AS
SELECT * FROM <Old_Table_Name>

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
hive> Create Table Transaction_Bkup AS Select * From Transaction;
Query ID = revisit_batch_20200225100936_2e7443c1-f2a0-4682-b32c-15880d9c3a73
Total jobs = 1
Launching Job 1 out of 1
Tez session was closed. Reopening...
Session re-established.
Status: Running (Executing on YARN cluster with App id application_1580425344507_558299)

--------------------------------------------------------------------------------
        VERTICES      STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED
--------------------------------------------------------------------------------
Map 1 ..........   SUCCEEDED      2          2        0        0       0       0
--------------------------------------------------------------------------------
VERTICES: 01/01  [==========================>>] 100%  ELAPSED TIME: 7.68 s
--------------------------------------------------------------------------------
Moving data to directory hdfs://test_batch/apps/hive/warehouse/testdb.db/transaction_bkup
Table testdb.transaction_bkup stats: [numFiles=2, numRows=3, totalSize=110, rawDataSize=113]
OK
account_id      balance_amt     account_type    last_txn_date
Time taken: 16.685 seconds

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.

CREATE TABLE <New_Table_Name>
LIKE <Old_Table_Name>;

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