Cloudera Docs

SHOW CREATE TABLE WITH STATS statement

Learn about using the WITH STATS clause to generate the SQL statements required to recreate a table along with its column statistics and partition metadata.

Syntax:

SHOW CREATE TABLE [database_name.]table_name [WITH STATS]

Usage notes

Usage notes:

When you run the SHOW CREATE TABLE statement by using the WITH STATS clause, Impala generates a series of DDL statement that are used to recreate the table with additional metadata like statistics, table properties, and partition values, etc. The output for WITH STATS contains the following elements:

  • The base CREATE TABLE statement.
  • The ALTER TABLE ... SET TBLPROPERTIES statement for table-level statistics.
  • The ALTER TABLE ... SET COLUMN STATS statements for all non-partition columns to restore column-level statistics.
  • The ALTER TABLE ... ADD PARTITION statements to recreate partitions (for partitioned tables).
  • The per-partition ALTER TABLE ... PARTITION (...) SET TBLPROPERTIES statements to restore partition-level statistics.

The WITH STATS output for partitions is limited by the SHOW_CREATE_TABLE_PARTITION_LIMIT query option, which defaults to 1000. If a table contains more partitions than the limit, you can set it to a larger value. Setting SHOW_CREATE_TABLE_PARTITION_LIMIT=0 will include all partitions.

If you do not use the WITH STATS clause, the statement only returns the base CREATE TABLE command.

Examples

SHOW CREATE TABLE sales_data WITH STATS;