Create Table Like

Creates a new table with all of the same metadata of another table.

Description

The LIKE form of CREATE TABLE allows you to copy an existing table definition exactly.

Grammar Syntax

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  LIKE existing_table_or_view_name
  [LOCATION hdfs_path];

For more information check Hive Documentation.

Simple case

Hive

CREATE TABLE `mytable`
LIKE mydataset.sourcetable

Snowflake

CREATE TABLE `mytable`
LIKE mydataset.sourcetable

Known Issues

1. If the EXTERNAL header is added, the statement is transformed to a regular table and the EWI MSCINF0006 is added.

2. If the LOCATION clause is added, it is commented and EWI MSC-HVXXXX is added

Related EWIs

1. MSCINF0006: External table translated to a regular table.

2. MSC-HVXXXX: THE CREATE TABLE LOCATION CLAUSE IS NOT SUPPORTED IN SNOWFLAKE.