SHOW SCHEMAS
Queries all user schemas or databases.
Syntax
SHOW {SCHEMAS | DATABASES}[LIKE 'pattern'][EXTRA]
Example
mysql> show schemas;+------------------+| TABLE_SCHEMA |+------------------+| tpch_string_100x || tpch_string_10x || tpch_string_1x |+------------------+3 rows in set (0.01 sec)
mysql> show schemas like 'tpch%';+------------------+| TABLE_SCHEMA |+------------------+| tpch_string_100x || tpch_string_10x || tpch_string_1x |+------------------+3 rows in set (0.01 sec)
mysql> show schemas like 'tpch%' extra;+--------------+------------------+------------------------------+| CATALOG_NAME | TABLE_SCHEMA | CREATOR_ID |+--------------+------------------+------------------------------+| hive | tpch_string_100x | OA$oa_101302231286633637597c || hive | tpch_string_10x | OA$oa_101302231286633637597c || hive | tpch_string_1x | OA$oa_101302231286633637597c |+--------------+------------------+------------------------------+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
USE database_name
to switch to the target schema.
Example
mysql> use tpch_string_1x;Database changed
mysql> show tables;+-------------------------+| TABLE_NAME |+-------------------------+| customer_avro_string || customer_json_string || customer_orc_string || customer_parquet_string || customer_rcfile_string || customer_text_string |+-------------------------+6 rows in set (0.01 sec)
Run SELECT DATABASE() to view the schemas of the current session.
mysql> select database();+----------------+| database() |+----------------+| tpch_string_1x |+----------------+1 row in set (0.00 sec)
mysql> show tables like 'cus%';+-------------------------+| TABLE_NAME |+-------------------------+| customer_avro_string || customer_json_string || customer_orc_string || customer_parquet_string || customer_rcfile_string || customer_text_string |+-------------------------+6 rows in set (0.01 sec)
SHOW CREATE TABLE
Queries the table creation statement.
Syntax
SHOW CREATE TABLE tbl_name
Example
mysql> show create table customer_text_string\G*************************** 1. row ***************************Result: CREATE EXTERNAL TABLE `customer_text_string`(`c_custkey` int,`c_name` string,`c_address` string,`c_nationkey` int,`c_phone` string,`c_acctbal` double,`c_mktsegment` string,`c_comment` string)ROW FORMAT DELIMITEDFIELDS TERMINATED BY '|'STORED AS INPUTFORMAT'org.apache.hadoop.mapred.TextInputFormat'OUTPUTFORMAT'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'LOCATION'oss://acs:ram::111111111:role&aliyunopenanalyticsossfullaccessrole@test_bucket/datasets/tpch/1x/text_string/customer_text'TBLPROPERTIES ('COLUMN_STATS_ACCURATE'='false','numFiles'='1','numRows'='-1','rawDataSize'='-1','totalSize'='24346144','transient_lastDdlTime'='1523448357')1 row in set (0.38 sec)
SHOW PARTITIONS
Lists all partitions (if any) of the table.
Syntax
SHOW PARTITIONS[database_name.]table_name
Example
mysql> show partitions primitives_avro_p;+--------------------------------------------------------------------------+| Result |+--------------------------------------------------------------------------+| year=2009/month=1year=2009/month=2year=2009/month=3year=2009/month=4|+--------------------------------------------------------------------------+1 row in set (0.46 sec)mysql> show partitions basic_test.primitives_avro_p;+--------------------------------------------------------------------------+| Result |+--------------------------------------------------------------------------+| year=2009/month=1year=2009/month=2year=2009/month=3year=2009/month=4|+--------------------------------------------------------------------------+1 row in set (0.67 sec)
SHOW PARTITIONS
SHOW PARTITIONS [database_name.]table_name [PARTITION(partition_spec)];
Example
mysql> show partitions primitives_avro_p partition(year='2009');+--------------------------------------------------------------------------+| Result |+--------------------------------------------------------------------------+| year=2009/month=1year=2009/month=2year=2009/month=3year=2009/month=4|+--------------------------------------------------------------------------+1 row in set (0.51 sec)mysql> show partitions primitives_avro_p partition(month='3');+--------------------+| Result |+--------------------+| year=2009/month=3|+--------------------+1 row in set (0.45 sec)mysql> show partitions primitives_avro_p partition(year='2009',month='3');+--------------------+| Result |+--------------------+| year=2009/month=3|+--------------------+1 row in set (0.46 sec)
SHOW QUERY_TASK
Queries the query tasks of the user.
Syntax
SHOW QUERY_TASK[WHERE expr][ORDER BY {col_name | expr}[ASC | DESC], ...][LIMIT {[offset,] row_count | row_count OFFSET offset}]
Example
mysql> show query_task where create_time > '2018-04-12' order by create_time desc limit 4, 1\G*************************** 1. row ***************************TABLE_SCHEMA: tpch_string_1xID: e573ff53_1523547045897STATUS: SUCCESSMESSAGE: NULLCOMMAND: select * from customer_text_string_2CREATOR_ID: OA$oa_101302231286633637597cCREATE_TIME: 2018-04-12 23:30:46.0UPDATE_TIME: 2018-04-12 23:30:46.0ROW_COUNT: 10000ELAPSE_TIME: 3017SCANNED_DATA_BYTES: 1630878RESULT_FILE_OSS_FILE: oss://aliyun-oa-query-results-*****-oss-cn-hangzhou/*****/2018/4/12/20180412233042e573ff53000017/result.csvCANCELLABLE_TASK: 01 row in set (0.04 sec)
mysql> show query_task where id = 'e573ff53_1523547045897'\G*************************** 1. row ***************************TABLE_SCHEMA: tpch_string_1xID: e573ff53_1523547045897STATUS: SUCCESSMESSAGE: NULLCOMMAND: select * from customer_text_string_2CREATOR_ID: OA$oa_101302231286633637597cCREATE_TIME: 2018-04-12 23:30:46.0UPDATE_TIME: 2018-04-12 23:30:46.0ROW_COUNT: 10000ELAPSE_TIME: 3017SCANNED_DATA_BYTES: 1630878RESULT_FILE_OSS_FILE: oss://aliyun-oa-query-results-*****-oss-cn-hangzhou/*****/2018/4/12/20180412233042e573ff53000017/result.csvCANCELLABLE_TASK: 01 row in set (0.01 sec)