Show Create Table

The SHOW CREATE TABLE statement can be used to print the DDL statement of already existing user-created doc tables 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. |    "fourth_column" OBJECT (STRICT) AS (             |
  8. |       "key" STRING,                                 |
  9. |       "value" STRING                                |
  10. |    ),                                               |
  11. |    "second_column" STRING,                          |
  12. |    "third_column" TIMESTAMP,                        |
  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. |    column_policy = 'dynamic',                       |
  24. |    "mapping.total_fields.limit" = 1000,             |
  25. |    max_ngram_diff = 1,                              |
  26. |    max_shingle_diff = 3,                            |
  27. |    number_of_replicas = '0-1',                      |
  28. |    refresh_interval = 1000,                         |
  29. |    "routing.allocation.enable" = 'all',             |
  30. |    "routing.allocation.total_shards_per_node" = -1, |
  31. |    "translog.durability" = 'REQUEST',               |
  32. |    "translog.flush_threshold_size" = 536870912,     |
  33. |    "translog.sync_interval" = 5000,                 |
  34. |    "unassigned.node_left.delayed_timeout" = 60000,  |
  35. |    "warmer.enabled" = true,                         |
  36. |    "write.wait_for_active_shards" = 'ALL'           |
  37. | )                                                   |
  38. +-----------------------------------------------------+
  39. 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.