全部產品
Search
文件中心

Data Lake Analytics - Deprecated:SHOW

更新時間:Jul 06, 2024

本文主要介紹在DDL下的SHOW文法以及相關文法樣本。

SHOW SCHEMAS

查詢使用者所有的schema(database)。

  • 文法:
    SHOW {SCHEMAS | DATABASES}
        [LIKE 'pattern']
        [EXTRA]
  • 樣本:
    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

查詢當前schema下的表,在使用前,您必須先切換到對應的schema。文法如下:
USE database_name
樣本:
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)
通過SELECT DATABASE()查看當前session的schema。
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

查看建表語句。

  • 文法:
    SHOW CREATE TABLE tbl_name
  • 樣本:
    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 DELIMITED 
      FIELDS 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

列出表的所有分區資訊。

  • 文法:
    SHOW PARTITIONS
        [database_name.]table_name
  • 樣本:
    mysql> show partitions primitives_avro_p;
    +--------------------------------------------------------------------------+
    | Result                                                                   |
    +--------------------------------------------------------------------------+
    | year=2009/month=1
    year=2009/month=2
    year=2009/month=3
    year=2009/month=4
     |
    +--------------------------------------------------------------------------+
    1 row in set (0.46 sec)
    
    mysql> show partitions basic_test.primitives_avro_p;
    +--------------------------------------------------------------------------+
    | Result                                                                   |
    +--------------------------------------------------------------------------+
    | year=2009/month=1
    year=2009/month=2
    year=2009/month=3
    year=2009/month=4
     |
    +--------------------------------------------------------------------------+
    1 row in set (0.67 sec)
    支援指定分區進行過濾:
    SHOW PARTITIONS [database_name.]table_name [PARTITION(partition_spec)];
    樣本:
    mysql> show partitions primitives_avro_p partition(year='2009');
    +--------------------------------------------------------------------------+
    | Result                                                                   |
    +--------------------------------------------------------------------------+
    | year=2009/month=1
    year=2009/month=2
    year=2009/month=3
    year=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

查詢使用者的查詢任務資訊。

  • 文法:
    SHOW QUERY_TASK [FOR UserName]
        [WHERE expr]
        [ORDER BY {col_name | expr}
          [ASC | DESC], ...]
        [LIMIT {[offset,] row_count | row_count OFFSET offset}]
  • 樣本:
    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_1x
                      ID: e573ff53_1523547045897
                  STATUS: SUCCESS
                 MESSAGE: NULL
                 COMMAND: select * from customer_text_string_2
              CREATOR_ID: OA$oa_101302231286633637597c
             CREATE_TIME: 2018-04-12 23:30:46.0
             UPDATE_TIME: 2018-04-12 23:30:46.0
               ROW_COUNT: 10000
             ELAPSE_TIME: 3017
      SCANNED_DATA_BYTES: 1630878
    RESULT_FILE_OSS_FILE: oss://aliyun-oa-query-results-*****-oss-cn-hangzhou/*****/2018/4/12/20180412233042e573ff53000017/result.csv
        CANCELLABLE_TASK: 0
    1 row in set (0.04 sec)
    mysql> show query_task where id = 'e573ff53_1523547045897'\G
    *************************** 1. row ***************************
            TABLE_SCHEMA: tpch_string_1x
                      ID: e573ff53_1523547045897
                  STATUS: SUCCESS
                 MESSAGE: NULL
                 COMMAND: select * from customer_text_string_2
              CREATOR_ID: OA$oa_101302231286633637597c
             CREATE_TIME: 2018-04-12 23:30:46.0
             UPDATE_TIME: 2018-04-12 23:30:46.0
               ROW_COUNT: 10000
             ELAPSE_TIME: 3017
      SCANNED_DATA_BYTES: 1630878
    RESULT_FILE_OSS_FILE:  oss://aliyun-oa-query-results-*****-oss-cn-hangzhou/*****/2018/4/12/20180412233042e573ff53000017/result.csv
        CANCELLABLE_TASK: 0
    1 row in set (0.01 sec)
    如果是主帳號登入,可以查詢其他客戶SQL執行記錄
    mysql> show query_task  for your sun_account where  create_time > '2020-06-01'

SHOW PROCESSLIST

查看Server端中各個線程(串連)的運行情況。

  • 文法:
    show processlist
  • 樣本:
    mysql> show processlist\G
    
    *************************** 1. row ***************************
           Id: 16777224            -- 線程(串連)Id
         User: oa_1xxxxxxxxxx7597c -- dla帳號 
         Host: 212.20.XX.XX:7320  -- 用戶端Ip和Port
           db: tpch_50x_text       -- 串連上綁定的Schema
      Command: SELECT              -- 當前SQL類型
         Time: 1                   -- SQL執行時間
        State: RUNNING             -- SQL狀態
         Info: select count(*) from lineitem   -- SQL語句
    ProcessId: q201908070004sh0aa2bf4a0001009   -- ProcessId用於排查問題
    1 row in set (0.08 sec)

SHOW GRANTS

查看帳號的許可權資訊。

  • 文法:
    show grants for <account_id>
  • 樣本:
    GRANT ALL ON *.* TO dla_p123456789 WITH GRANT OPTION
說明 其中 ALL表示所有許可權,更多許可權資訊請參見GRANT