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.
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.
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.
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 secondsThe backup table is created successfully. lets select the data from the Transaction_Backup table 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.
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.
Finally the table structure alone copied from Transaction table to Transaction_New.
Recommended Articles