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

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

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 ]

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

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

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

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

Recommended Articles