All Products
Search
Document Center

SHOW

Last Updated: Apr 24, 2019

SHOW SCHEMAS

Queries all user schemas or databases.

Syntax

  1. SHOW {SCHEMAS | DATABASES}
  2. [LIKE 'pattern']
  3. [EXTRA]

Example

  1. mysql> show schemas;
  2. +------------------+
  3. | TABLE_SCHEMA |
  4. +------------------+
  5. | tpch_string_100x |
  6. | tpch_string_10x |
  7. | tpch_string_1x |
  8. +------------------+
  9. 3 rows in set (0.01 sec)
  1. mysql> show schemas like 'tpch%';
  2. +------------------+
  3. | TABLE_SCHEMA |
  4. +------------------+
  5. | tpch_string_100x |
  6. | tpch_string_10x |
  7. | tpch_string_1x |
  8. +------------------+
  9. 3 rows in set (0.01 sec)
  1. mysql> show schemas like 'tpch%' extra;
  2. +--------------+------------------+------------------------------+
  3. | CATALOG_NAME | TABLE_SCHEMA | CREATOR_ID |
  4. +--------------+------------------+------------------------------+
  5. | hive | tpch_string_100x | OA$oa_101302231286633637597c |
  6. | hive | tpch_string_10x | OA$oa_101302231286633637597c |
  7. | hive | tpch_string_1x | OA$oa_101302231286633637597c |
  8. +--------------+------------------+------------------------------+
  9. 3 rows in set (0.01 sec)

SHOW TABLES

Before running SHOW TABLES to query all tables in a schema, you must run the following statement

  1. USE database_name

to switch to the target schema.

Example

  1. mysql> use tpch_string_1x;
  2. Database changed
  1. mysql> show tables;
  2. +-------------------------+
  3. | TABLE_NAME |
  4. +-------------------------+
  5. | customer_avro_string |
  6. | customer_json_string |
  7. | customer_orc_string |
  8. | customer_parquet_string |
  9. | customer_rcfile_string |
  10. | customer_text_string |
  11. +-------------------------+
  12. 6 rows in set (0.01 sec)

Run SELECT DATABASE() to view the schemas of the current session.

  1. mysql> select database();
  2. +----------------+
  3. | database() |
  4. +----------------+
  5. | tpch_string_1x |
  6. +----------------+
  7. 1 row in set (0.00 sec)
  1. mysql> show tables like 'cus%';
  2. +-------------------------+
  3. | TABLE_NAME |
  4. +-------------------------+
  5. | customer_avro_string |
  6. | customer_json_string |
  7. | customer_orc_string |
  8. | customer_parquet_string |
  9. | customer_rcfile_string |
  10. | customer_text_string |
  11. +-------------------------+
  12. 6 rows in set (0.01 sec)

SHOW CREATE TABLE

Queries the table creation statement.

Syntax

  1. SHOW CREATE TABLE tbl_name

Example

  1. mysql> show create table customer_text_string\G
  2. *************************** 1. row ***************************
  3. Result: CREATE EXTERNAL TABLE `customer_text_string`(
  4. `c_custkey` int,
  5. `c_name` string,
  6. `c_address` string,
  7. `c_nationkey` int,
  8. `c_phone` string,
  9. `c_acctbal` double,
  10. `c_mktsegment` string,
  11. `c_comment` string)
  12. ROW FORMAT DELIMITED
  13. FIELDS TERMINATED BY '|'
  14. STORED AS INPUTFORMAT
  15. 'org.apache.hadoop.mapred.TextInputFormat'
  16. OUTPUTFORMAT
  17. 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
  18. LOCATION
  19. 'oss://acs:ram::111111111:role&aliyunopenanalyticsossfullaccessrole@test_bucket/datasets/tpch/1x/text_string/customer_text'
  20. TBLPROPERTIES (
  21. 'COLUMN_STATS_ACCURATE'='false',
  22. 'numFiles'='1',
  23. 'numRows'='-1',
  24. 'rawDataSize'='-1',
  25. 'totalSize'='24346144',
  26. 'transient_lastDdlTime'='1523448357')
  27. 1 row in set (0.38 sec)

SHOW PARTITIONS

Lists all partitions (if any) of the table.

Syntax

  1. SHOW PARTITIONS
  2. [database_name.]table_name

Example

  1. mysql> show partitions primitives_avro_p;
  2. +--------------------------------------------------------------------------+
  3. | Result |
  4. +--------------------------------------------------------------------------+
  5. | year=2009/month=1
  6. year=2009/month=2
  7. year=2009/month=3
  8. year=2009/month=4
  9. |
  10. +--------------------------------------------------------------------------+
  11. 1 row in set (0.46 sec)
  12. mysql> show partitions basic_test.primitives_avro_p;
  13. +--------------------------------------------------------------------------+
  14. | Result |
  15. +--------------------------------------------------------------------------+
  16. | year=2009/month=1
  17. year=2009/month=2
  18. year=2009/month=3
  19. year=2009/month=4
  20. |
  21. +--------------------------------------------------------------------------+
  22. 1 row in set (0.67 sec)

SHOW PARTITIONS

  1. SHOW PARTITIONS [database_name.]table_name [PARTITION(partition_spec)];

Example

  1. mysql> show partitions primitives_avro_p partition(year='2009');
  2. +--------------------------------------------------------------------------+
  3. | Result |
  4. +--------------------------------------------------------------------------+
  5. | year=2009/month=1
  6. year=2009/month=2
  7. year=2009/month=3
  8. year=2009/month=4
  9. |
  10. +--------------------------------------------------------------------------+
  11. 1 row in set (0.51 sec)
  12. mysql> show partitions primitives_avro_p partition(month='3');
  13. +--------------------+
  14. | Result |
  15. +--------------------+
  16. | year=2009/month=3
  17. |
  18. +--------------------+
  19. 1 row in set (0.45 sec)
  20. mysql> show partitions primitives_avro_p partition(year='2009',month='3');
  21. +--------------------+
  22. | Result |
  23. +--------------------+
  24. | year=2009/month=3
  25. |
  26. +--------------------+
  27. 1 row in set (0.46 sec)

SHOW QUERY_TASK

Queries the query tasks of the user.

Syntax

  1. SHOW QUERY_TASK
  2. [WHERE expr]
  3. [ORDER BY {col_name | expr}
  4. [ASC | DESC], ...]
  5. [LIMIT {[offset,] row_count | row_count OFFSET offset}]

Example

  1. mysql> show query_task where create_time > '2018-04-12' order by create_time desc limit 4, 1\G
  2. *************************** 1. row ***************************
  3. TABLE_SCHEMA: tpch_string_1x
  4. ID: e573ff53_1523547045897
  5. STATUS: SUCCESS
  6. MESSAGE: NULL
  7. COMMAND: select * from customer_text_string_2
  8. CREATOR_ID: OA$oa_101302231286633637597c
  9. CREATE_TIME: 2018-04-12 23:30:46.0
  10. UPDATE_TIME: 2018-04-12 23:30:46.0
  11. ROW_COUNT: 10000
  12. ELAPSE_TIME: 3017
  13. SCANNED_DATA_BYTES: 1630878
  14. RESULT_FILE_OSS_FILE: oss://aliyun-oa-query-results-*****-oss-cn-hangzhou/*****/2018/4/12/20180412233042e573ff53000017/result.csv
  15. CANCELLABLE_TASK: 0
  16. 1 row in set (0.04 sec)
  1. mysql> show query_task where id = 'e573ff53_1523547045897'\G
  2. *************************** 1. row ***************************
  3. TABLE_SCHEMA: tpch_string_1x
  4. ID: e573ff53_1523547045897
  5. STATUS: SUCCESS
  6. MESSAGE: NULL
  7. COMMAND: select * from customer_text_string_2
  8. CREATOR_ID: OA$oa_101302231286633637597c
  9. CREATE_TIME: 2018-04-12 23:30:46.0
  10. UPDATE_TIME: 2018-04-12 23:30:46.0
  11. ROW_COUNT: 10000
  12. ELAPSE_TIME: 3017
  13. SCANNED_DATA_BYTES: 1630878
  14. RESULT_FILE_OSS_FILE: oss://aliyun-oa-query-results-*****-oss-cn-hangzhou/*****/2018/4/12/20180412233042e573ff53000017/result.csv
  15. CANCELLABLE_TASK: 0
  16. 1 row in set (0.01 sec)