SHOW Statements

SHOW statements are used to list objects within their corresponding parent, such as catalogs, databases, tables and views, columns, functions, and modules. See the individual commands for more details and additional options.

SHOW CREATE statements are used to print a DDL statement with which a given object can be created. The currently ‘SHOW CREATE’ statement is only available in printing DDL statement of the given table and view.

Flink SQL supports the following SHOW statements for now:

  • SHOW CATALOGS
  • SHOW CURRENT CATALOG
  • SHOW CREATE CATALOG
  • SHOW DATABASES
  • SHOW CURRENT DATABASE
  • SHOW TABLES
  • SHOW CREATE TABLE
  • SHOW COLUMNS
  • SHOW PARTITIONS
  • SHOW PROCEDURES
  • SHOW VIEWS
  • SHOW CREATE VIEW
  • SHOW FUNCTIONS
  • SHOW MODULES
  • SHOW JARS
  • SHOW JOBS

Run a SHOW statement

Java

SHOW statements can be executed with the executeSql() method of the TableEnvironment. The executeSql() method returns objects for a successful SHOW operation, otherwise will throw an exception.

The following examples show how to run a SHOW statement in TableEnvironment.

Scala

SHOW statements can be executed with the executeSql() method of the TableEnvironment. The executeSql() method returns objects for a successful SHOW operation, otherwise will throw an exception.

The following examples show how to run a SHOW statement in TableEnvironment.

Python

SHOW statements can be executed with the execute_sql() method of the TableEnvironment. The execute_sql() method returns objects for a successful SHOW operation, otherwise will throw an exception.

The following examples show how to run a SHOW statement in TableEnvironment.

SQL CLI

SHOW statements can be executed in SQL CLI.

The following examples show how to run a SHOW statement in SQL CLI.

Java

  1. StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
  2. StreamTableEnvironment tEnv = StreamTableEnvironment.create(env);
  4. // show catalogs
  5. tEnv.executeSql("SHOW CATALOGS").print();
  6. // +-----------------+
  7. // |    catalog name |
  8. // +-----------------+
  9. // | default_catalog |
  10. // +-----------------+
  12. // show current catalog
  13. tEnv.executeSql("SHOW CURRENT CATALOG").print();
  14. // +----------------------+
  15. // | current catalog name |
  16. // +----------------------+
  17. // |      default_catalog |
  18. // +----------------------+
  20. // create a catalog
  21. tEnv.executeSql("CREATE CATALOG cat2 WITH (...)");
  23. // show create catalog
  24. tEnv.executeSql("SHOW CREATE CATALOG cat2").print();
  25. // +---------------------------------------------------------------------------------------------+
  26. // |                                                                                      result |
  27. // +---------------------------------------------------------------------------------------------+
  28. // | CREATE CATALOG `cat2` WITH (
  29. //   'default-database' = 'db',
  30. //   'type' = 'generic_in_memory'
  31. // )
  32. // |
  33. // +---------------------------------------------------------------------------------------------+
  34. // 1 row in set
  36. // show databases
  37. tEnv.executeSql("SHOW DATABASES").print();
  38. // +------------------+
  39. // |    database name |
  40. // +------------------+
  41. // | default_database |
  42. // +------------------+
  44. // show current database
  45. tEnv.executeSql("SHOW CURRENT DATABASE").print();
  46. // +-----------------------+
  47. // | current database name |
  48. // +-----------------------+
  49. // |      default_database |
  50. // +-----------------------+
  52. // create a table
  53. tEnv.executeSql("CREATE TABLE my_table (...) WITH (...)");
  54. // show tables
  55. tEnv.executeSql("SHOW TABLES").print();
  56. // +------------+
  57. // | table name |
  58. // +------------+
  59. // |   my_table |
  60. // +------------+
  62. // show create table
  63. tEnv.executeSql("SHOW CREATE TABLE my_table").print();
  64. // CREATE TABLE `default_catalog`.`default_db`.`my_table` (
  65. //   ...
  66. // ) WITH (
  67. //   ...
  68. // )
  70. // show columns
  71. tEnv.executeSql("SHOW COLUMNS FROM my_table LIKE '%f%'").print();
  72. // +--------+-------+------+-----+--------+-----------+
  73. // |   name |  type | null | key | extras | watermark |
  74. // +--------+-------+------+-----+--------+-----------+
  75. // | field2 | BYTES | true |     |        |           |
  76. // +--------+-------+------+-----+--------+-----------+
  79. // create a view
  80. tEnv.executeSql("CREATE VIEW my_view AS SELECT * FROM my_table");
  81. // show views
  82. tEnv.executeSql("SHOW VIEWS").print();
  83. // +-----------+
  84. // | view name |
  85. // +-----------+
  86. // |   my_view |
  87. // +-----------+
  89. // show create view
  90. tEnv.executeSql("SHOW CREATE VIEW my_view").print();
  91. // CREATE VIEW `default_catalog`.`default_db`.`my_view`(`field1`, `field2`, ...) as
  92. // SELECT *
  93. // FROM `default_catalog`.`default_database`.`my_table`
  95. // show functions
  96. tEnv.executeSql("SHOW FUNCTIONS").print();
  97. // +---------------+
  98. // | function name |
  99. // +---------------+
  100. // |           mod |
  101. // |        sha256 |
  102. // |           ... |
  103. // +---------------+
  105. // create a user defined function
  106. tEnv.executeSql("CREATE FUNCTION f1 AS ...");
  107. // show user defined functions
  108. tEnv.executeSql("SHOW USER FUNCTIONS").print();
  109. // +---------------+
  110. // | function name |
  111. // +---------------+
  112. // |            f1 |
  113. // |           ... |
  114. // +---------------+
  116. // show modules
  117. tEnv.executeSql("SHOW MODULES").print();
  118. // +-------------+
  119. // | module name |
  120. // +-------------+
  121. // |        core |
  122. // +-------------+
  124. // show full modules
  125. tEnv.executeSql("SHOW FULL MODULES").print();
  126. // +-------------+-------+
  127. // | module name |  used |
  128. // +-------------+-------+
  129. // |        core |  true |
  130. // |        hive | false |
  131. // +-------------+-------+

Scala

  1. val env = StreamExecutionEnvironment.getExecutionEnvironment()
  2. val tEnv = StreamTableEnvironment.create(env)
  4. // show catalogs
  5. tEnv.executeSql("SHOW CATALOGS").print()
  6. // +-----------------+
  7. // |    catalog name |
  8. // +-----------------+
  9. // | default_catalog |
  10. // +-----------------+
  12. // create a catalog
  13. tEnv.executeSql("CREATE CATALOG cat2 WITH (...)")
  15. // show create catalog
  16. tEnv.executeSql("SHOW CREATE CATALOG cat2").print()
  17. // +---------------------------------------------------------------------------------------------+
  18. // |                                                                                      result |
  19. // +---------------------------------------------------------------------------------------------+
  20. // | CREATE CATALOG `cat2` WITH (
  21. //   'default-database' = 'db',
  22. //   'type' = 'generic_in_memory'
  23. // )
  24. // |
  25. // +---------------------------------------------------------------------------------------------+
  26. // 1 row in set
  28. // show databases
  29. tEnv.executeSql("SHOW DATABASES").print()
  30. // +------------------+
  31. // |    database name |
  32. // +------------------+
  33. // | default_database |
  34. // +------------------+
  36. // create a table
  37. tEnv.executeSql("CREATE TABLE my_table (...) WITH (...)")
  38. // show tables
  39. tEnv.executeSql("SHOW TABLES").print()
  40. // +------------+
  41. // | table name |
  42. // +------------+
  43. // |   my_table |
  44. // +------------+
  46. // show create table
  47. tEnv.executeSql("SHOW CREATE TABLE my_table").print()
  48. // CREATE TABLE `default_catalog`.`default_db`.`my_table` (
  49. //  ...
  50. // ) WITH (
  51. //  ...
  52. // )
  54. // show columns
  55. tEnv.executeSql("SHOW COLUMNS FROM my_table LIKE '%f%'").print()
  56. // +--------+-------+------+-----+--------+-----------+
  57. // |   name |  type | null | key | extras | watermark |
  58. // +--------+-------+------+-----+--------+-----------+
  59. // | field2 | BYTES | true |     |        |           |
  60. // +--------+-------+------+-----+--------+-----------+
  62. // create a view
  63. tEnv.executeSql("CREATE VIEW my_view AS SELECT * FROM my_table")
  64. // show views
  65. tEnv.executeSql("SHOW VIEWS").print()
  66. // +-----------+
  67. // | view name |
  68. // +-----------+
  69. // |   my_view |
  70. // +-----------+
  72. // show create view
  73. tEnv.executeSql("SHOW CREATE VIEW my_view").print();
  74. // CREATE VIEW `default_catalog`.`default_db`.`my_view`(`field1`, `field2`, ...) as
  75. // SELECT *
  76. // FROM `default_catalog`.`default_database`.`my_table`
  78. // show functions
  79. tEnv.executeSql("SHOW FUNCTIONS").print()
  80. // +---------------+
  81. // | function name |
  82. // +---------------+
  83. // |           mod |
  84. // |        sha256 |
  85. // |           ... |
  86. // +---------------+
  88. // create a user defined function
  89. tEnv.executeSql("CREATE FUNCTION f1 AS ...")
  90. // show user defined functions
  91. tEnv.executeSql("SHOW USER FUNCTIONS").print()
  92. // +---------------+
  93. // | function name |
  94. // +---------------+
  95. // |            f1 |
  96. // |           ... |
  97. // +---------------+
  99. // show modules
  100. tEnv.executeSql("SHOW MODULES").print()
  101. // +-------------+
  102. // | module name |
  103. // +-------------+
  104. // |        core |
  105. // +-------------+
  107. // show full modules
  108. tEnv.executeSql("SHOW FULL MODULES").print()
  109. // +-------------+-------+
  110. // | module name |  used |
  111. // +-------------+-------+
  112. // |        core |  true |
  113. // |        hive | false |
  114. // +-------------+-------+

Python

  1. table_env = StreamTableEnvironment.create(...)
  3. # show catalogs
  4. table_env.execute_sql("SHOW CATALOGS").print()
  5. # +-----------------+
  6. # |    catalog name |
  7. # +-----------------+
  8. # | default_catalog |
  9. # +-----------------+
  11. # create a catalog
  12. table_env.execute_sql("CREATE CATALOG cat2 WITH (...)")
  14. # show create catalog
  15. table_env.execute_sql("SHOW CREATE CATALOG cat2").print()
  16. # +---------------------------------------------------------------------------------------------+
  17. # |                                                                                      result |
  18. # +---------------------------------------------------------------------------------------------+
  19. # | CREATE CATALOG `cat2` WITH (
  20. #   'default-database' = 'db',
  21. #   'type' = 'generic_in_memory'
  22. # )
  23. #  |
  24. # +---------------------------------------------------------------------------------------------+
  25. # 1 row in set
  27. # show databases
  28. table_env.execute_sql("SHOW DATABASES").print()
  29. # +------------------+
  30. # |    database name |
  31. # +------------------+
  32. # | default_database |
  33. # +------------------+
  35. # create a table
  36. table_env.execute_sql("CREATE TABLE my_table (...) WITH (...)")
  37. # show tables
  38. table_env.execute_sql("SHOW TABLES").print()
  39. # +------------+
  40. # | table name |
  41. # +------------+
  42. # |   my_table |
  43. # +------------+
  44. # show create table
  45. table_env.executeSql("SHOW CREATE TABLE my_table").print()
  46. # CREATE TABLE `default_catalog`.`default_db`.`my_table` (
  47. #   ...
  48. # ) WITH (
  49. #   ...
  50. # )
  52. # show columns
  53. table_env.execute_sql("SHOW COLUMNS FROM my_table LIKE '%f%'").print()
  54. # +--------+-------+------+-----+--------+-----------+
  55. # |   name |  type | null | key | extras | watermark |
  56. # +--------+-------+------+-----+--------+-----------+
  57. # | field2 | BYTES | true |     |        |           |
  58. # +--------+-------+------+-----+--------+-----------+
  60. # create a view
  61. table_env.execute_sql("CREATE VIEW my_view AS SELECT * FROM my_table")
  62. # show views
  63. table_env.execute_sql("SHOW VIEWS").print()
  64. # +-----------+
  65. # | view name |
  66. # +-----------+
  67. # |   my_view |
  68. # +-----------+
  70. # show create view
  71. table_env.execute_sql("SHOW CREATE VIEW my_view").print()
  72. # CREATE VIEW `default_catalog`.`default_db`.`my_view`(`field1`, `field2`, ...) as
  73. # SELECT *
  74. # FROM `default_catalog`.`default_database`.`my_table`
  76. # show functions
  77. table_env.execute_sql("SHOW FUNCTIONS").print()
  78. # +---------------+
  79. # | function name |
  80. # +---------------+
  81. # |           mod |
  82. # |        sha256 |
  83. # |           ... |
  84. # +---------------+
  86. # create a user defined function
  87. table_env.execute_sql("CREATE FUNCTION f1 AS ...")
  88. # show user defined functions
  89. table_env.execute_sql("SHOW USER FUNCTIONS").print()
  90. # +---------------+
  91. # | function name |
  92. # +---------------+
  93. # |            f1 |
  94. # |           ... |
  95. # +---------------+
  97. # show modules
  98. table_env.execute_sql("SHOW MODULES").print()
  99. # +-------------+
  100. # | module name |
  101. # +-------------+
  102. # |        core |
  103. # +-------------+
  105. # show full modules
  106. table_env.execute_sql("SHOW FULL MODULES").print()
  107. # +-------------+-------+
  108. # | module name |  used |
  109. # +-------------+-------+
  110. # |        core |  true |
  111. # |        hive | false |
  112. # +-------------+-------+

SQL CLI

  2. Flink SQL> SHOW CATALOGS;
  3. default_catalog
  5. Flink SQL> CREATE CATALOG cat2 WITH (...);
  6. [INFO] Execute statement succeeded.
  8. Flink SQL> SHOW CREATE CATALOG cat2;
  9. CREATE CATALOG `cat2` WITH (
  10.   ...
  11. )
  13. Flink SQL> SHOW DATABASES;
  14. default_database
  16. Flink SQL> CREATE TABLE my_table (...) WITH (...);
  17. [INFO] Table has been created.
  19. Flink SQL> SHOW TABLES;
  20. my_table
  22. Flink SQL> SHOW CREATE TABLE my_table;
  23. CREATE TABLE `default_catalog`.`default_db`.`my_table` (
  24.   ...
  25. ) WITH (
  26.   ...
  27. )
  30. Flink SQL> SHOW COLUMNS from MyUserTable LIKE '%f%';
  31. +--------+-------+------+-----+--------+-----------+
  32. |   name |  type | null | key | extras | watermark |
  33. +--------+-------+------+-----+--------+-----------+
  34. | field2 | BYTES | true |     |        |           |
  35. +--------+-------+------+-----+--------+-----------+
  36. 1 row in set
  39. Flink SQL> CREATE VIEW my_view AS SELECT * from my_table;
  40. [INFO] View has been created.
  42. Flink SQL> SHOW VIEWS;
  43. my_view
  45. Flink SQL> SHOW CREATE VIEW my_view;
  46. CREATE VIEW `default_catalog`.`default_db`.`my_view`(`field1`, `field2`, ...) as
  47. SELECT *
  48. FROM `default_catalog`.`default_database`.`my_table`
  50. Flink SQL> SHOW FUNCTIONS;
  51. mod
  52. sha256
  53. ...
  55. Flink SQL> CREATE FUNCTION f1 AS ...;
  56. [INFO] Function has been created.
  58. Flink SQL> SHOW USER FUNCTIONS;
  59. f1
  60. ...
  62. Flink SQL> SHOW MODULES;
  63. +-------------+
  64. | module name |
  65. +-------------+
  66. |        core |
  67. +-------------+
  68. 1 row in set
  71. Flink SQL> SHOW FULL MODULES;
  72. +-------------+------+
  73. | module name | used |
  74. +-------------+------+
  75. |        core | true |
  76. +-------------+------+
  77. 1 row in set
  80. Flink SQL> SHOW JARS;
  81. /path/to/addedJar.jar

SHOW CATALOGS

  1. SHOW CATALOGS

Show all catalogs.

SHOW CURRENT CATALOG

  1. SHOW CURRENT CATALOG

Show current catalog.

SHOW CREATE CATALOG

  1. SHOW CREATE CATALOG catalog_name

Show creation statement for an existing catalog.

The output includes the catalog’s name and relevant properties, which allows you to gain an intuitive understanding of the underlying catalog’s metadata.

Assumes that the catalog cat2 is created as follows:

  1. create catalog cat2 WITH (
  2.     'type'='generic_in_memory',
  3.     'default-database'='db'
  4. );

Shows the creation statement.

  1. show create catalog cat2;
  2. +---------------------------------------------------------------------------------------------+
  3. |                                                                                      result |
  4. +---------------------------------------------------------------------------------------------+
  5. | CREATE CATALOG `cat2` WITH (
  6.   'default-database' = 'db',
  7.   'type' = 'generic_in_memory'
  8. )
  9.  |
  10. +---------------------------------------------------------------------------------------------+
  11. 1 row in set

SHOW DATABASES

  1. SHOW DATABASES [ ( FROM | IN ) catalog_name] [ [NOT] (LIKE | ILIKE) <sql_like_pattern> ]

Show all databases within optionally specified catalog. If no catalog is specified, then the default catalog is used. Additionally, a <sql_like_pattern> can be used to filter the databases.

LIKE Show all databases with a LIKE clause, whose name is similar to the <sql_like_pattern>.

The syntax of the SQL pattern in the LIKE clause is the same as that of the MySQL dialect.

  • % matches any number of characters, even zero characters, and \% matches one % character.
  • _ matches exactly one character, \_ matches one _ character.

ILIKE The same behavior as LIKE but the SQL pattern is case-insensitive.

SHOW CURRENT DATABASE

  1. SHOW CURRENT DATABASE

Show current database.

SHOW TABLES

  1. SHOW TABLES [ ( FROM | IN ) [catalog_name.]database_name ] [ [NOT] LIKE <sql_like_pattern> ]

Show all tables for an optionally specified database. If no database is specified then the tables are returned from the current database. Additionally, the output of this statement may be filtered by an optional matching pattern.

LIKE Show all tables with given table name and optional LIKE clause, whose name is whether similar to the <sql_like_pattern>.

The syntax of sql pattern in LIKE clause is the same as that of MySQL dialect.

  • % matches any number of characters, even zero characters, \% matches one % character.
  • _ matches exactly one character, \_ matches one _ character.

SHOW TABLES EXAMPLES

Assumes that the db1 database located in catalog1 catalog has the following tables:

  • person
  • dim

the current database in session has the following tables:

  • items

  • orders

  • Shows all tables of the given database.

  1. show tables from db1;
  2. -- show tables from catalog1.db1;
  3. -- show tables in db1;
  4. -- show tables in catalog1.db1;
  5. +------------+
  6. | table name |
  7. +------------+
  8. |        dim |
  9. |     person |
  10. +------------+
  11. 2 rows in set
  • Shows all tables of the given database, which are similar to the given sql pattern.
  1. show tables from db1 like '%n';
  2. -- show tables from catalog1.db1 like '%n';
  3. -- show tables in db1 like '%n';
  4. -- show tables in catalog1.db1 like '%n';
  5. +------------+
  6. | table name |
  7. +------------+
  8. |     person |
  9. +------------+
  10. 1 row in set
  • Shows all tables of the given database, which are not similar to the given sql pattern.
  1. show tables from db1 not like '%n';
  2. -- show tables from catalog1.db1 not like '%n';
  3. -- show tables in db1 not like '%n';
  4. -- show tables in catalog1.db1 not like '%n';
  5. +------------+
  6. | table name |
  7. +------------+
  8. |        dim |
  9. +------------+
  10. 1 row in set
  • Shows all tables of the current database.
  1. show tables;
  2. +------------+
  3. | table name |
  4. +------------+
  5. |      items |
  6. |     orders |
  7. +------------+
  8. 2 rows in set

SHOW CREATE TABLE

  1. SHOW CREATE TABLE [[catalog_name.]db_name.]table_name

Show create table statement for specified table.

The output includes the table name, column names, data types, constraints, comments, and configurations.

It is a very useful statement when you need to understand the structure, configuration and constraints of an existing table or to recreate the table in another database.

Assumes that the table orders is created as follows:

  1. CREATE TABLE orders (
  2.   order_id BIGINT NOT NULL comment 'this is the primary key, named ''order_id''.',
  3.   product VARCHAR(32),
  4.   amount INT,
  5.   ts TIMESTAMP(3) comment 'notice: watermark, named ''ts''.',
  6.   ptime AS PROCTIME() comment 'notice: computed column, named ''ptime''.',
  7.   WATERMARK FOR ts AS ts - INTERVAL '1' SECOND,
  8.   CONSTRAINT `PK_order_id` PRIMARY KEY (order_id) NOT ENFORCED
  9. ) WITH (
  10.   'connector' = 'datagen'
  11. );

Shows the creation statement.

  1. show create table orders;
  2. +---------------------------------------------------------------------------------------------+
  3. |                                                                                      result |
  4. +---------------------------------------------------------------------------------------------+
  5. | CREATE TABLE `default_catalog`.`default_database`.`orders` (
  6.   `order_id` BIGINT NOT NULL COMMENT 'this is the primary key, named ''order_id''.',
  7.   `product` VARCHAR(32),
  8.   `amount` INT,
  9.   `ts` TIMESTAMP(3) COMMENT 'notice: watermark, named ''ts''.',
  10.   `ptime` AS PROCTIME() COMMENT 'notice: computed column, named ''ptime''.',
  11.   WATERMARK FOR `ts` AS `ts` - INTERVAL '1' SECOND,
  12.   CONSTRAINT `PK_order_id` PRIMARY KEY (`order_id`) NOT ENFORCED
  13. ) WITH (
  14.   'connector' = 'datagen'
  15. )
  16.  |
  17. +---------------------------------------------------------------------------------------------+
  18. 1 row in set

Attention Currently SHOW CREATE TABLE only supports table that is created by Flink SQL DDL.

SHOW COLUMNS

  1. SHOW COLUMNS ( FROM | IN ) [[catalog_name.]database.]<table_name> [ [NOT] LIKE <sql_like_pattern>]

Show all columns of the table with given table name and optional like clause.

LIKE Show all columns of the table with given table name and optional LIKE clause, whose name is whether similar to the <sql_like_pattern>.

The syntax of sql pattern in LIKE clause is the same as that of MySQL dialect.

SHOW COLUMNS EXAMPLES

Assumes that the table named orders in the database1 database which is located in the catalog1 catalog has the following structure:

  1. +---------+-----------------------------+-------+-----------+---------------+----------------------------+
  2. |    name |                        type |  null |       key |        extras |                  watermark |
  3. +---------+-----------------------------+-------+-----------+---------------+----------------------------+
  4. |    user |                      BIGINT | false | PRI(user) |               |                            |
  5. | product |                 VARCHAR(32) |  true |           |               |                            |
  6. |  amount |                         INT |  true |           |               |                            |
  7. |      ts |      TIMESTAMP(3) *ROWTIME* |  true |           |               | `ts` - INTERVAL '1' SECOND |
  8. |   ptime | TIMESTAMP_LTZ(3) *PROCTIME* | false |           | AS PROCTIME() |                            |
  9. +---------+-----------------------------+-------+-----------+---------------+----------------------------+
  • Shows all columns of the given table.
  1. show columns from orders;
  2. -- show columns from database1.orders;
  3. -- show columns from catalog1.database1.orders;
  4. -- show columns in orders;
  5. -- show columns in database1.orders;
  6. -- show columns in catalog1.database1.orders;
  7. +---------+-----------------------------+-------+-----------+---------------+----------------------------+
  8. |    name |                        type |  null |       key |        extras |                  watermark |
  9. +---------+-----------------------------+-------+-----------+---------------+----------------------------+
  10. |    user |                      BIGINT | false | PRI(user) |               |                            |
  11. | product |                 VARCHAR(32) |  true |           |               |                            |
  12. |  amount |                         INT |  true |           |               |                            |
  13. |      ts |      TIMESTAMP(3) *ROWTIME* |  true |           |               | `ts` - INTERVAL '1' SECOND |
  14. |   ptime | TIMESTAMP_LTZ(3) *PROCTIME* | false |           | AS PROCTIME() |                            |
  15. +---------+-----------------------------+-------+-----------+---------------+----------------------------+
  16. 5 rows in set
  • Shows all columns of the given table, which are similar to the given sql pattern.
  1. show columns from orders like '%r';
  2. -- show columns from database1.orders like '%r';
  3. -- show columns from catalog1.database1.orders like '%r';
  4. -- show columns in orders like '%r';
  5. -- show columns in database1.orders like '%r';
  6. -- show columns in catalog1.database1.orders like '%r';
  7. +------+--------+-------+-----------+--------+-----------+
  8. | name |   type |  null |       key | extras | watermark |
  9. +------+--------+-------+-----------+--------+-----------+
  10. | user | BIGINT | false | PRI(user) |        |           |
  11. +------+--------+-------+-----------+--------+-----------+
  12. 1 row in set
  • Shows all columns of the given table, which are not similar to the given sql pattern.
  1. show columns from orders not like '%_r';
  2. -- show columns from database1.orders not like '%_r';
  3. -- show columns from catalog1.database1.orders not like '%_r';
  4. -- show columns in orders not like '%_r';
  5. -- show columns in database1.orders not like '%_r';
  6. -- show columns in catalog1.database1.orders not like '%_r';
  7. +---------+-----------------------------+-------+-----+---------------+----------------------------+
  8. |    name |                        type |  null | key |        extras |                  watermark |
  9. +---------+-----------------------------+-------+-----+---------------+----------------------------+
  10. | product |                 VARCHAR(32) |  true |     |               |                            |
  11. |  amount |                         INT |  true |     |               |                            |
  12. |      ts |      TIMESTAMP(3) *ROWTIME* |  true |     |               | `ts` - INTERVAL '1' SECOND |
  13. |   ptime | TIMESTAMP_LTZ(3) *PROCTIME* | false |     | AS PROCTIME() |                            |
  14. +---------+-----------------------------+-------+-----+---------------+----------------------------+
  15. 4 rows in set

SHOW PARTITIONS

  1. SHOW PARTITIONS [[catalog_name.]database.]<table_name> [ PARTITION <partition_spec>]
  3. <partition_spec>:
  4.   (key1=val1, key2=val2, ...)

Show all partitions of the partitioned table with the given table name and optional partition clause.

PARTITION Show all the partitions which are under the provided <partition_spec> in the given table.

SHOW PARTITIONS EXAMPLES

Assumes that the partitioned table table1 in the database1 database which is located in the catalog1 catalog has the following partitions:

  1. +---------+-----------------------------+
  2. |      id |                        date |
  3. +---------+-----------------------------+
  4. |    1001 |                  2020-01-01 |
  5. |    1002 |                  2020-01-01 |
  6. |    1002 |                  2020-01-02 |
  7. +---------+-----------------------------+
  • Shows all partitions of the given table.
  1. show partitions table1;
  2. -- show partitions database1.table1;
  3. -- show partitions catalog1.database1.table1;
  4. +---------+-----------------------------+
  5. |      id |                        date |
  6. +---------+-----------------------------+
  7. |    1001 |                  2020-01-01 |
  8. |    1002 |                  2020-01-01 |
  9. |    1002 |                  2020-01-02 |
  10. +---------+-----------------------------+
  11. 3 rows in set
  • Shows all partitions of the given table with the given partition spec.
  1. show partitions table1 partition (id=1002);
  2. -- show partitions database1.table1 partition (id=1002);
  3. -- show partitions catalog1.database1.table1 partition (id=1002);
  4. +---------+-----------------------------+
  5. |      id |                        date |
  6. +---------+-----------------------------+
  7. |    1002 |                  2020-01-01 |
  8. |    1002 |                  2020-01-02 |
  9. +---------+-----------------------------+
  10. 2 rows in set

SHOW PROCEDURES

  1. SHOW PROCEDURES [ ( FROM | IN ) [catalog_name.]database_name ] [ [NOT] (LIKE | ILIKE) <sql_like_pattern> ]

Show all procedures for an optionally specified database. If no database is specified then the procedures are returned from the current database. Additionally, a <sql_like_pattern> can be used to filter the procedures.

LIKE Show all procedures with a LIKE clause, whose name is similar to the <sql_like_pattern>.

The syntax of the SQL pattern in the LIKE clause is the same as that of the MySQL dialect.

  • % matches any number of characters, even zero characters, and \% matches one % character.
  • _ matches exactly one character, \_ matches one _ character.

ILIKE The same behavior as LIKE but the SQL pattern is case-insensitive.

SHOW VIEWS

  1. SHOW VIEWS

Show all views in the current catalog and the current database.

SHOW CREATE VIEW

  1. SHOW CREATE VIEW [catalog_name.][db_name.]view_name

Show create view statement for specified view.

SHOW FUNCTIONS

  1. SHOW [USER] FUNCTIONS [ ( FROM | IN ) [catalog_name.]database_name ] [ [NOT] (LIKE | ILIKE) <sql_like_pattern> ]

Show all functions including system functions and user-defined functions for an optionally specified database. If no database is specified then the functions are returned from the current database. Additionally, a <sql_like_pattern> can be used to filter the functions.

USER Show only user-defined functions for an optionally specified database. If no database is specified then the functions are returned from the current database. Additionally, a <sql_like_pattern> can be used to filter the functions.

LIKE Show all functions with a LIKE clause, whose name is similar to the <sql_like_pattern>.

The syntax of the SQL pattern in the LIKE clause is the same as that of the MySQL dialect.

  • % matches any number of characters, even zero characters, and \% matches one % character.
  • _ matches exactly one character, \_ matches one _ character.

ILIKE The same behavior as LIKE but the SQL pattern is case-insensitive.

SHOW MODULES

  1. SHOW [FULL] MODULES

Show all enabled module names with resolution order.

FULL Show all loaded modules and enabled status with resolution order.

SHOW JARS

  1. SHOW JARS

Show all added jars in the session classloader which are added by ADD JAR statements.

Attention Currently SHOW JARS statements only work in the SQL CLI or SQL Gateway.

SHOW JOBS

  1. SHOW JOBS

Show the jobs in the Flink cluster.

Attention Currently SHOW JOBS statements only work in the SQL CLI or SQL Gateway.