Skip to main content

SHOW CREATE TABLE

Returns the CREATE TABLE statement that was used to create a given table.

NOTE

In versions earlier than v3.0, the SHOW CREATE TABLE statement requires you to have the SELECT_PRIV privilege on the table. Since v3.0, the SHOW CREATE TABLE statement requires you to have the SELECT privilege on the table.

Since v3.0, you can use the SHOW CREATE TABLE statement to view the CREATE TABLE statements of the tables that are managed by an external catalog and are stored in Apache Hive™, Apache Iceberg, Apache Hudi, or Delta Lake.

Since v2.5.7, StarRocks can automatically set the number of buckets (BUCKETS) when you create a table or add a partition. You no longer need to manually set the number of buckets. For detailed information, see set the number of buckets.

  • If you specified the number of buckets when creating a table, the output of SHOW CREATE TABLE will display the number of buckets.
  • If you did not specify the number of buckets when creating a table, the output of SHOW CREATE TABLE will not display the number of buckets. You can run SHOW PARTITIONS to view the number of buckets for each partition.

In versions earlier than v2.5.7, you are required to set the number of buckets when creating a table. Therefore, SHOW CREATE TABLE displays the number of buckets by default.

Syntax​

SHOW CREATE TABLE [db_name.]table_name

Parameters​

ParameterRequiredDescription
db_nameNoThe database name. If this parameter is not specified, the CREATE TABLE statement of a given table in your current database is returned by default.
table_nameYesThe table name.

Output​

+-----------+----------------+
| Table     | Create Table   |                                               
+-----------+----------------+

The following table describes the parameters returned by this statement.

ParameterDescription
TableThe table name.
Create TableThe CREATE TABLE statement of the table.

Examples​

Bucket number is not specified​

Create a table named example_table with no bucket number specified in DISTRIBUTED BY.

CREATE TABLE example_table
(
    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 starrocks table"
DISTRIBUTED BY HASH(k1);

Run SHOW CREATE TABLE to display the CREATE TABLE statement of example_table. No bucket number is displayed in DISTRIBUTED BY. Note that if you did not specify PROPERTIES when you create the table, the default properties are displayed in the output of SHOW CREATE TABLE.

SHOW CREATE TABLE example_table\G
*************************** 1. row ***************************
       Table: example_table
Create Table: CREATE TABLE `example_table` (
  `k1` tinyint(4) NULL COMMENT "",
  `k2` decimal64(10, 2) NULL DEFAULT "10.5" COMMENT "",
  `v1` char(10) REPLACE NULL COMMENT "",
  `v2` int(11) SUM NULL COMMENT ""
) ENGINE=OLAP 
AGGREGATE KEY(`k1`, `k2`)
COMMENT "my first starrocks table"
DISTRIBUTED BY HASH(`k1`)
PROPERTIES (
"replication_num" = "3",
"in_memory" = "false",
"enable_persistent_index" = "false",
"replicated_storage" = "true",
"compression" = "LZ4"
);

Bucket number is specified​

Create a table named example_table1 with bucket number set to 10 in DISTRIBUTED BY.

CREATE TABLE example_table1
(
    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 starrocks table"
DISTRIBUTED BY HASH(k1) BUCKETS 10;

Run SHOW CREATE TABLE to display the CREATE TABLE statement of example_table. The bucket number (BUCKETS 10) is displayed in DISTRIBUTED BY. Note that if you did not specify PROPERTIES when you create the table, the default properties are displayed in the output of SHOW CREATE TABLE.

SHOW CREATE TABLE example_table1\G
*************************** 1. row ***************************
       Table: example_table1
Create Table: CREATE TABLE `example_table1` (
  `k1` tinyint(4) NULL COMMENT "",
  `k2` decimal64(10, 2) NULL DEFAULT "10.5" COMMENT "",
  `v1` char(10) REPLACE NULL COMMENT "",
  `v2` int(11) SUM NULL COMMENT ""
) ENGINE=OLAP 
AGGREGATE KEY(`k1`, `k2`)
COMMENT "my first starrocks table"
DISTRIBUTED BY HASH(`k1`) BUCKETS 10 
PROPERTIES (
"replication_num" = "3",
"in_memory" = "false",
"enable_persistent_index" = "false",
"replicated_storage" = "true",
"compression" = "LZ4"
);

References​