SHOW CREATE TABLE

Description

SHOW CREATE TABLE returns the CREATE TABLE statement or CREATE VIEW statement that was used to create a given table or view. SHOW CREATE TABLE on a non-existent table or a temporary view throws an exception.

Syntax

  1. SHOW CREATE TABLE table_identifier [ AS SERDE ]

Parameters

  • table_identifier

    Specifies a table or view name, which may be optionally qualified with a database name.

    Syntax: [ database_name. ] table_name

  • AS SERDE

    Generates Hive DDL for a Hive SerDe table.

Examples

  1. CREATE TABLE test (c INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
  2.     STORED AS TEXTFILE
  3.     TBLPROPERTIES ('prop1' = 'value1', 'prop2' = 'value2');
  5. SHOW CREATE TABLE test;
  6. +----------------------------------------------------+
  7. |                                      createtab_stmt|
  8. +----------------------------------------------------+
  9. |CREATE TABLE `default`.`test` (`c` INT)
  10.  USING text
  11.  TBLPROPERTIES (
  12.    'transient_lastDdlTime' = '1586269021',
  13.    'prop1' = 'value1',
  14.    'prop2' = 'value2')
  15. +----------------------------------------------------+
  17. SHOW CREATE TABLE test AS SERDE;
  18. +------------------------------------------------------------------------------+
  19. |                                                                createtab_stmt|
  20. +------------------------------------------------------------------------------+
  21. |CREATE TABLE `default`.`test`(
  22.   `c` INT)
  23.  ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
  24.  WITH SERDEPROPERTIES (
  25.    'serialization.format' = ',',
  26.    'field.delim' = ',')
  27.  STORED AS
  28.    INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
  29.    OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
  30.  TBLPROPERTIES (
  31.    'prop1' = 'value1',
  32.    'prop2' = 'value2',
  33.    'transient_lastDdlTime' = '1641800515')
  34. +------------------------------------------------------------------------------+