Copy the data or table structure from one table to another in Hive
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
|
1 2 |
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.
|
1 |
hive> Create Table Transaction_Bkup AS Select * From Transaction; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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 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.
|
1 2 |
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
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
- Hive describe command to check the meta data of the Hive table
- Get the create table statement for an existing Hive table using Show create table
- Find list of databases/tables with specific pattern in Hive
