Show Create Table

The SHOW CREATE TABLE statement prints the CREATE TABLE statement of an existing user-created doc table in the cluster:

  1. cr> show create table my_table;
  2. +-----------------------------------------------------+
  3. | SHOW CREATE TABLE doc.my_table                      |
  4. +-----------------------------------------------------+
  5. | CREATE TABLE IF NOT EXISTS "doc"."my_table" (       |
  6. |    "first_column" INTEGER,                          |
  7. |    "second_column" TEXT,                            |
  8. |    "third_column" TIMESTAMP WITH TIME ZONE,         |
  9. |    "fourth_column" OBJECT(STRICT) AS (              |
  10. |       "key" TEXT,                                   |
  11. |       "value" TEXT                                  |
  12. |    ),                                               |
  13. |    PRIMARY KEY ("first_column")                     |
  14. | )                                                   |
  15. | CLUSTERED BY ("first_column") INTO 5 SHARDS         |
  16. | WITH (                                              |
  17. |    "allocation.max_retries" = 5,                    |
  18. |    "blocks.metadata" = false,                       |
  19. |    "blocks.read" = false,                           |
  20. |    "blocks.read_only" = false,                      |
  21. |    "blocks.read_only_allow_delete" = false,         |
  22. |    "blocks.write" = false,                          |
  23. |    codec = 'default',                               |
  24. |    column_policy = 'strict',                        |
  25. |    "mapping.total_fields.limit" = 1000,             |
  26. |    max_ngram_diff = 1,                              |
  27. |    max_shingle_diff = 3,                            |
  28. |    "merge.scheduler.max_thread_count" = 1,          |
  29. |    number_of_replicas = '0-1',                      |
  30. |    "routing.allocation.enable" = 'all',             |
  31. |    "routing.allocation.total_shards_per_node" = -1, |
  32. |    "store.type" = 'fs',                             |
  33. |    "translog.durability" = 'REQUEST',               |
  34. |    "translog.flush_threshold_size" = 536870912,     |
  35. |    "translog.sync_interval" = 5000,                 |
  36. |    "unassigned.node_left.delayed_timeout" = 60000,  |
  37. |    "write.wait_for_active_shards" = '1'             |
  38. | )                                                   |
  39. +-----------------------------------------------------+
  40. SHOW 1 row in set (... sec)

The table settings returned within the WITH clause of the result are all available table settings showing their respective values at the time of the execution of the SHOW statement.

Different versions of CrateDB may have different default table settings. This means that if you re-create the table using the resulting CREATE TABLE statement the settings of the ‘old’ table may differ from the settings of the ‘new’ table. This is because the table settings are set explicitly on creation time.