SHOW TBLPROPERTIES

Description

This statement returns the value of a table property given an optional value for a property key. If no key is specified then all the properties are returned.

Syntax

  1. SHOW TBLPROPERTIES table_identifier 
  2.    [ ( unquoted_property_key | property_key_as_string_literal ) ]

Parameters

  • table_identifier

    Specifies the table name of an existing table. The table may be optionally qualified with a database name.

    Syntax: [ database_name. ] table_name

  • unquoted_property_key

    Specifies the property key in unquoted form. The key may consists of multiple parts separated by dot.

    Syntax: [ key_part1 ] [ .key_part2 ] [ ... ]

  • property_key_as_string_literal

    Specifies a property key value as a string literal.

Note

  • Property value returned by this statement excludes some properties that are internal to spark and hive. The excluded properties are :
    • All the properties that start with prefix spark.sql
    • Property keys such as: EXTERNAL, comment
    • All the properties generated internally by hive to store statistics. Some of these properties are: numFiles, numPartitions, numRows.

Examples

  1. -- create a table `customer` in database `salesdb`
  2. USE salesdb;
  3. CREATE TABLE customer(cust_code INT, name VARCHAR(100), cust_addr STRING)
  4.     TBLPROPERTIES ('created.by.user' = 'John', 'created.date' = '01-01-2001');
  6. -- show all the user specified properties for table `customer`
  7. SHOW TBLPROPERTIES customer;
  8. +---------------------+----------+
  9. |                  key|     value|
  10. +---------------------+----------+
  11. |      created.by.user|      John|
  12. |         created.date|01-01-2001|
  13. |transient_lastDdlTime|1567554931|
  14. +---------------------+----------+
  16. -- show all the user specified properties for a qualified table `customer`
  17. -- in database `salesdb`
  18. SHOW TBLPROPERTIES salesdb.customer;
  19. +---------------------+----------+
  20. |                  key|     value|
  21. +---------------------+----------+
  22. |      created.by.user|      John|
  23. |         created.date|01-01-2001|
  24. |transient_lastDdlTime|1567554931|
  25. +---------------------+----------+
  27. -- show value for unquoted property key `created.by.user`
  28. SHOW TBLPROPERTIES customer (created.by.user);
  29. +-----+
  30. |value|
  31. +-----+
  32. | John|
  33. +-----+
  35. -- show value for property `created.date`` specified as string literal
  36. SHOW TBLPROPERTIES customer ('created.date');
  37. +----------+
  38. |     value|
  39. +----------+
  40. |01-01-2001|
  41. +----------+