In this post we will see how Hive Create new table using existing table metadata. There are various ways in which this can be achieved but first lets understand why we need to do this.
Table of Contents
Why?
Quick Table Creation: If we need to quickly create a table similar to an existing table having the same data then it will be helpful. Else we have to first get the table creation script using show create table. Then create the new table. After which we have to write insert into script which inserts data from old table to new table. This is a lengthy process.
Table Creation and Maintenance Programmatically: Let’s say we have an Employee table and Employee Back Up table. Now Employee BackUp create table script is present inside our spark sql code. Now every time the Employee table structure changes we have to manually make the same change in the script for Employee Back Up table. This process could be very cumbersome.
Hive Create new table with existing table metadata and data
Below is the syntax to create a new Hive table with the same metadata as the existing table and also to load all the data . This is achieved by CTAS [Create Table As]. Lets see an exmaple.
Syntax:
Create Table db_name.table_nm_new
as
Select * from db_name.table_nm
This creates table with same number of columns and rows as the source table.
Note: There is one issue in this approach. If the source table has partitioned columns , then the columns will be create in the new table but they will not be partition column. In the next example we will see how this issue is resolved.
Hive Create new empty table with existing table metadata using LIKE
Sometimes we may need only the table metadata and not the actual data. This can be achieved by using the LIKE keyword in hive.
Syntax:
Create Table db_name.table_nm_new
like db_name.table_nm
This creates the destination table with the same structure as source. The new table is empty and also it has the same partition structure as the source database.
2 advantage of LIKE over CTAS:
1.Like creates an empty table with the same structure as the source table.
2.If the source table is partitioned then the same partitioned structure is maintained in the new table.
Hive Create new empty table with existing table metadata using CTAS
We have seen earlier how to use CTAS. We also know that using CTAS the new table gets the same rows as the destination. But here we will see how to create empty table using CTAS
Syntax:
Create Table db_name.table_nm_new
as
Select * from db_name.table_nm where 1=2
Here we use the same syntax for CTAS but we use a filter where 1=2 which fails for each record. So it does not select and record and a hence creates an empty table.
2 advantage of CTAS over LIKE:
1.Using CTAS we can create new table which is either empty or has records.
2.As we are using select statement in CTAS, we can use filters to fetch relevant records . For example we can fetch where employee_name like “%ABY%”. This would create a new table containing only the records which satisfy the condition.
Conclusion
So today we learnt how to create a new hive table from an existing hive table. We saw how to create empty and table with rows using CTAS. We also saw how to use LIKE to create a new table with same partitioned columns as the source table.
You can check the below post to see how to create Hive Internal and External Tables. You can also refer official apache spark sql page.
🙂 kudos for learning something new 🙂
