[Proposal]Support new syntax CREATE TABLE LIKE to create a new table from existed table

[WingsGo]

Is your feature request related to a problem? Please describe. In some scenario, user may create a temp table whose schema is from a existed table for some reason such as load data to temp table and replace the new table, In this case, user must execute "SHOW CREATE TABLE old_table" and copy the text, change the table name, re-execute the sql in cli, it is not convient and waste some. So, I decide to support a new synax in create table.

Describe the solution you'd like Synax: CREATE TABLE [IF NOT EXISTS] [db_name].table_name AS [db_name2].table_name2; Behavior: The new create table synax will not only create a new table whose schema is the same as old_table, but also copy the old_table's partition, buckets, properties etc...

For example:

We have an existed table table_hash

CREATE TABLE example_db.table_hash
(
k1 TINYINT,
k2 DECIMAL(10, 2) DEFAULT "10.5",
v1 CHAR(10) REPLACE,
v2 INT SUM
)
ENGINE=olap
AGGREGATE KEY(k1, k2)
COMMENT "my first doris table"
DISTRIBUTED BY HASH(k1) BUCKETS 32
PROPERTIES ("storage_type"="column");

if we want create a new table named table_hash_tmp, we only need to execute sql CREATE TABLE example_db.table_hash_tmp AS example_db.table_hash, then we execute sql SHOW CREATE TABLE example_db.table_hash_tmp we will get:

CREATE TABLE example_db.table_hash_tmp
(
k1 TINYINT,
k2 DECIMAL(10, 2) DEFAULT "10.5",
v1 CHAR(10) REPLACE,
v2 INT SUM
)
ENGINE=olap
AGGREGATE KEY(k1, k2)
COMMENT "my first doris table"
DISTRIBUTED BY HASH(k1) BUCKETS 32
PROPERTIES ("storage_type"="column");

Describe alternatives you've considered A clear and concise description of any alternative solutions or features you've considered.

Additional context Add any other context or screenshots about the feature request here.

[morningman]

Make sure that this syntax will not conflict with create table xx as select ...;

[imay]

I aggree with @morningman maybe create table xxx like is something what you needs. We can refer other's syntax. redshift Snowflake MySQL PostgreSQL

So, before your code work, we should aggre on the syntax.

[WingsGo]

@morningman @imay the syntax create table xxx like look good to me, I will change it to this, For some detail design, some schema including column define, aggragete type, table index, table properties such as dynamic partition, colocate group..., partitions etc.... will keep the same as existed table. Some information will add later or just ignore, for example, Rollup information.

[imay]

@morningman @imay the syntax create table xxx like look good to me, I will change it to this, For some detail design, some schema including column define, aggragete type, table index, table properties such as dynamic partition, colocate group..., partitions etc.... will keep the same as existed table. Some information will add later or just ignore, for example, Rollup information.

Looking forward to your proposal

[caiconghui]

related issue #4402