This topic describes how to use SHOW commands in different operations.

The following table describes the usage of SHOW commands in different operations.
Operation type Description
SET operations View the properties that you configured by using the SET command.
Tunnel operations View the commands that are used to upload or download data or the logs of the upload or download operations.
Security operations View the security configuration properties of a project.
Package operations View the packages that are created or installed in a project.
Backup operations
  • View the information about tables in a project and tables in the backup state. You can view information such as table names, table IDs, creation time, and deletion time.
  • View the backup data of a specified table and obtain information about data versions within the retention period.
  • View the backup data of a deleted table and obtain information about data versions within the retention period.
  • View the backup data of a specified partition and obtain information about data versions within the retention period.
  • View the backup data of a deleted partition and obtain information about data versions within the retention period.
Table operations
  • View all the partitions of a table.
  • View all the tables and views in a project or the tables and views that meet specific rules in a project.
Function operations View the information about all built-in functions in a project.
Instance operations View the information about an instance. The information includes StartTime (accurate to the second), RunTime (in seconds), Status, InstanceID, Owner, and Query (query statement).
Permission query operations Query the permissions of users or roles and permissions on objects.
Operations that can be performed to collect information about the optimizer of MaxCompute Test collection results of column stats metrics.

SET operations

The SHOW FLAGS statement displays the properties that you configured by using the SET statement. Syntax:
show flags;
Note For more information about SET operations, see SET operations.

Tunnel operations

  • Displays historical records.
    • Syntax
      tunnel show history [-n <number>];

      -n <number>: specifies the number of times that the command is executed.

    • Examples
      Example 1: Display history records. By default, 500 data records are displayed. 
      tunnel show history;
      Example 2: Display the commands used in the last five data uploads or downloads. 
      tunnel show history -n 5;
  • Displays the logs of the last data upload or download. 
    tunnel show log;
Note For more information about Tunnel commands, see Tunnel commands.

Security operations

Views the security configuration properties of the current project.

Note For more information about security operations, see Security operations.

Package operations

A package creator views the packages that are created or installed in a MaxCompute project.

  • Syntax
    show packages;
  • Examples

    View the information about the packages that are created or installed in the test_project_a project. Sample command: 

    -- View packages. 
show packages;
    The returned result contains the following information:
    • PackageName: the name of the package that is created or installed.
    • CreateTime: the time when the package is created.
    • SourceProject: the name of the MaxCompute project to which the package belongs.
    • InstallTime: the time when the package is installed.
    • Status: the status of the package.
    +-------------+--------------------------+
| PackageName | CreateTime               |
+-------------+--------------------------+
| datashare   | 2021-12-28T18:10:39+0800 |
+-------------+--------------------------+
+-------------+--------------------+--------------------------+--------+
| PackageName | SourceProject      | InstallTime              | Status |
+-------------+--------------------+--------------------------+--------+
| systables   | information_schema | 2020-11-24T14:11:23+0800 | OK     |
+-------------+--------------------+--------------------------+--------+
Note For more information about package operations, see Cross-project resource access based on packages.

Backup operations

  • View the information about tables in a project and tables in the backup state. You can view information such as table names, table IDs, creation time, and deletion time. 
    show history for tables;
  • View the backup data of a specified table and obtain information about data versions within the retention period. 
    show history for table <table_name>;
  • View the backup data of a deleted table and obtain information about data versions within the retention period. 
    show history for table table_name ('id'='xxxx');
  • View the backup data of a specified partition and obtain information about data versions within the retention period. 
    show history for table table_name partition_spec;
  • View the backup data of a deleted partition and obtain information about data versions within the retention period. You can obtain the value of id from the ObjectId field in the output of the show history for table; command. 
    show history for table table_name PARTITION('id'='xxxx');
Note For more information about backup and restoration commands, see Backup and restoration.

Table operations

  • View the CREATE TABLE statement.
    • Syntax
      show create table <table_name>;
    • Parameters

      table_name: required. The name of the table for which you want to view the CREATE TABLE statement.

    • Example
      -- View the CREATE TABLE statement that is used to create the sale_detail table. 
show create table sale_detail;
      The following result is returned: 
      CREATE TABLE IF NOT EXISTS doc_test_dev.sale_detail(shop_name STRING,customer_id STRING,total_price DOUBLE) PARTITIONED BY (sale_date STRING,region STRING) STORED AS ALIORC;
  • View all the tables and views or the tables and views that meet specific rules in a project.
    • Syntax
      -- Display all the tables and views in a project. 
show tables;
-- Display the tables or views whose names contain the chart keyword in a project. 
show tables like '<chart>';
    • Example
      -- Display the tables whose names contain the sale* keyword in a project. The asterisk (*) indicates any character. 
show tables like 'sale*';
      The following result is returned: 
      ALIYUN$account_name:sale_detail
......
-- ALIYUN is a system prompt, which indicates that the table is created by using an Alibaba Cloud account. If the table was created by a RAM user, the system prompt is RAM.
  • View all the partitions of a table. If the table does not exist or the table is a non-partitioned table, an error is returned.
    • Syntax
      show partitions <table_name>;
    • Parameters

      table_name: required. The name of the partitioned table whose partition information you want to view.

    • Example
      -- Display all the partitions of the sale_detail table. 
show partitions sale_detail;
      The following result is returned: 
      sale_date=201310/region=beijing
sale_date=201312/region=shenzhen
sale_date=201312/region=xian
sale_date=2014/region=shenzhen

OK
Note For more information about table operations, see Table operations.

Function operations

View the information about all built-in functions in a MaxCompute project.
  • Syntax
    show functions;
  • Example
    show functions;
    The following result is returned: 
    ID = 20211214091641326gg0g****
::::ABS SCALAR  INT ABS(INT arg0),DOUBLE ABS(DOUBLE arg0),DECIMAL(?,?) ABS(DECIMAL(?,?) arg0),BIGINT ABS(BIGINT arg0)
::::ACOS        SCALAR  DOUBLE ACOS(DOUBLE arg0),DOUBLE ACOS(DECIMAL(?,?) arg0)
::::ADD_MONTHS  SCALAR  STRING ADD_MONTHS(TIMESTAMP arg0, BIGINT arg1),STRING ADD_MONTHS(STRING arg0, BIGINT arg1),STRING ADD_MONTHS(DATE arg0, BIGINT arg1)
::::ALL_MATCH   SCALAR  BOOLEAN ALL_MATCH(ARRAY<T> arg0, java.util.function.Function<T, java.lang.Boolean> arg1)
::::ANY_MATCH   SCALAR  BOOLEAN ANY_MATCH(ARRAY<T> arg0, java.util.function.Function<T, java.lang.Boolean> arg1)
::::ANY_VALUE   AGGREGATOR      T ANY_VALUE([DISTINCT] T arg1)
::::APPROX_DISTINCT     AGGREGATOR      BIGINT APPROX_DISTINCT([DISTINCT] P arg1, DOUBLE arg2),BIGINT APPROX_DISTINCT([DISTINCT] P arg1)
::::ARG_MAX     AGGREGATOR      R ARG_MAX([DISTINCT] T arg1, R arg2)
::::ARG_MIN     AGGREGATOR      R ARG_MIN([DISTINCT] T arg1, R arg2)
::::ARRAY       SCALAR  ARRAY<STRING> ARRAY(),ARRAY<T> ARRAY(T arg0...)
::::ARRAYS_OVERLAP      SCALAR  BOOLEAN ARRAYS_OVERLAP(ARRAY<T> arg0, ARRAY<T> arg1)
::::ARRAYS_ZIP  SCALAR  null
::::ARRAY_CONTAINS      SCALAR  BOOLEAN ARRAY_CONTAINS(ARRAY<T> arg0, T arg1)
::::ARRAY_DISTINCT      SCALAR  ARRAY<T> ARRAY_DISTINCT(ARRAY<T> arg0)
::::ARRAY_EXCEPT        SCALAR  ARRAY<T> ARRAY_EXCEPT(ARRAY<T> arg0, ARRAY<T> arg1)
::::ARRAY_INTERSECT     SCALAR  ARRAY<T> ARRAY_INTERSECT(ARRAY<T> arg0, ARRAY<T> arg1)
::::ARRAY_JOIN  SCALAR  STRING ARRAY_JOIN(ARRAY<STRING> arg0, STRING arg1),STRING ARRAY_JOIN(ARRAY<STRING> arg0, STRING arg1, STRING arg2)
::::ARRAY_MAX   SCALAR  T ARRAY_MAX(ARRAY<T> arg0)
::::ARRAY_MIN   SCALAR  T ARRAY_MIN(ARRAY<T> arg0)
::::ARRAY_POSITION      SCALAR  BIGINT ARRAY_POSITION(ARRAY<T> arg0, T arg1)
::::ARRAY_REDUCE        SCALAR  OUT ARRAY_REDUCE(ARRAY<IN> arg0, BUF arg1, java.util.function.BiFunction<BUF, IN, BUF> arg2, java.util.function.Function<BUF, OUT> arg3)
::::ARRAY_REMOVE        SCALAR  ARRAY<T> ARRAY_REMOVE(ARRAY<T> arg0, T arg1)
::::ARRAY_REPEAT        SCALAR  ARRAY<T> ARRAY_REPEAT(T arg0, BIGINT arg1)
::::ARRAY_SORT  SCALAR  ARRAY<T> ARRAY_SORT(ARRAY<T> arg0, java.util.function.BiFunction<T, T, java.lang.Long> arg1)
::::ARRAY_UNION SCALAR  ARRAY<T> ARRAY_UNION(ARRAY<T> arg0, ARRAY<T> arg1)
::::ASCII       SCALAR  BIGINT ASCII(STRING arg0)
::::ASIN        SCALAR  DOUBLE ASIN(DOUBLE arg0),DOUBLE ASIN(DECIMAL(?,?) arg0)
::::ATAN        SCALAR  DOUBLE ATAN(DOUBLE arg0),DOUBLE ATAN(DECIMAL(?,?) arg0)
::::AT_MOST_ONE_ROW     AGGREGATOR      T AT_MOST_ONE_ROW([DISTINCT] T arg1)
::::AVG AGGREGATOR      DECIMAL(?,?) AVG([DISTINCT] DECIMAL(?,?) arg1),DOUBLE AVG([DISTINCT] DOUBLE arg1)
::::AVG WINDOW  DOUBLE AVG([DISTINCT] DOUBLE arg0),DECIMAL(?,?) AVG([DISTINCT] DECIMAL(?,?) arg0)
::::BASE64      SCALAR  STRING BASE64(BINARY arg0)
::::BIN SCALAR  STRING BIN(BIGINT arg0)
::::BITAND      SCALAR  BIGINT BITAND(BIGINT arg0, BIGINT arg1)
::::BITNOT      SCALAR  BIGINT BITNOT(BIGINT arg0)
::::BITOR       SCALAR  BIGINT BITOR(BIGINT arg0, BIGINT arg1)
::::BITXOR      SCALAR  BIGINT BITXOR(BIGINT arg0, BIGINT arg1)
::::BROUND      SCALAR  DOUBLE BROUND(DOUBLE arg0, BIGINT arg1),DOUBLE BROUND(DOUBLE arg0)
::::CASE        SCALAR  null
......
Note For more information about function operations, see Function operations.

Instance operations

Views instance information. The information includes StartTime (in seconds), RunTime (in seconds), Status, InstanceID, Owner, and Query statements.

  • Syntax
    show p|proc|processlist|instances [from <startdate>] [to <enddate>] [-p <project_name>] [-limit <number> | <number>] [-all];
ls|list instances [from <startdate>] [to <enddate>] [-p <project_name>] [-limit <number> | <number>] [-all];

    The following statements are equivalent: show p, show proc, show processlist, show instances, ls instances, and list instances.

  • Parameters
    • startdate and enddate: optional. The instance information that is submitted by a user within the period from startdate to enddate is returned. The date specified by the startdate parameter must be earlier than the date specified by the enddate parameter. The instance information that is submitted on the day that is specified by enddate is not included. The values of the two parameters must be in the yyyy-mm-dd format and are accurate to the day. If you do not configure the parameters, the instance information that is submitted on the current day is returned.
    • project_name: optional. The name of the MaxCompute project to which the instance that you want to query belongs. You must have the permissions to view the instance of the MaxCompute project. If you do not configure this parameter, the instance of the current MaxCompute project is queried.
    • number: optional. The number of instances that you want to return.
    • If you configure this parameter, information about N instances that is submitted at the time nearest to the current time is returned in chronological order. N is specified by the number parameter. If you do not configure this parameter, information about the instances that meet specific requirements is returned. -limit <number> and number are equivalent.
    • -all: optional. Information about all instances that are run by the members of the MaxCompute project is returned. If you do not configure this parameter, information about the instances that are run by the current user in the MaxCompute project is returned.

      If the number parameter is not specified, information about up to 50 instances is returned by default. If the number parameter is configured, information about N instances is returned. N is specified by the number parameter.

  • Examples
    • Example 1: View the information about all instances that are run by the current user in the current MaxCompute project on the current day. Sample statement: 
      show p;
      The following result is returned: 
      StartTime             RunTime  Status   InstanceID                    Owner                            Query
2021-09-14 11:43:04   0s       Success  20210914**************3rw2    ALIYUN$****@test.aliyunid.com
2021-09-14 11:43:05   1s       Success  20210914**************5t32    ALIYUN$****@test.aliyunid.com select date_sub(datetime '2005-03-01 00:00:00', 1);
2021-09-14 11:58:13   0s       Success  20210914**************5pr2    ALIYUN$****@test.aliyunid.com
2021-09-14 11:58:15   1s       Success  20210914**************5qgr    ALIYUN$****@test.aliyunid.com select date_sub(date '2005-02-28', -1);
2021-09-14 12:02:15   1s       Success  20210914**************h8o7    ALIYUN$****@test.aliyunid.com select date_sub('2008-03-01 00:00:00', 2);
2021-09-14 12:02:15   0s       Success  20210914**************5t32    ALIYUN$****@test.aliyunid.com
2021-09-14 12:02:31   0s       Success  20210914**************5pr2    ALIYUN$****@test.aliyunid.com
2021-09-14 12:02:32   0s       Success  20210914**************euq2    ALIYUN$****@test.aliyunid.com select date_sub('2005-03-01 00:00:00', 2);
2021-09-14 13:35:42   0s       Success  20210914**************1ms2    ALIYUN$****@test.aliyunid.com
2021-09-14 13:35:43   0s       Success  20210914**************j8o7    ALIYUN$****@test.aliyunid.com select date_sub(getdate(),1);
2021-09-14 13:40:40   1s       Success  20210914**************h3wz    ALIYUN$****@test.aliyunid.com select date_sub(getdate(),0);
2021-09-14 13:40:40   0s       Success  20210914**************9nm7    ALIYUN$****@test.aliyunid.com
12 instances
    • Example 2: View the information about the instances that are run by the current user in the current MaxCompute project within a specified period of time, and specify the number of instances whose information you want to query. Sample statement: 
      show instances from 2021-09-14 to 2021-09-15 -limit 10;
      The following result is returned: 
      StartTime             RunTime  Status   InstanceID                    Owner                            Query
2021-09-14 11:58:13   0s       Success  20210914**************5pr2    ALIYUN$****@test.aliyunid.com
2021-09-14 11:58:15   1s       Success  20210914**************5qgr    ALIYUN$****@test.aliyunid.com    select date_sub(date '2005-02-28', -1);
2021-09-14 12:02:15   1s       Success  20210914**************h8o7    ALIYUN$****@test.aliyunid.com    select date_sub('2008-03-01 00:00:00', 2);
2021-09-14 12:02:15   0s       Success  20210914**************5t32    ALIYUN$****@test.aliyunid.com
2021-09-14 12:02:31   0s       Success  20210914**************5pr2    ALIYUN$****@test.aliyunid.com
2021-09-14 12:02:32   0s       Success  20210914**************euq2    ALIYUN$****@test.aliyunid.com    select date_sub('2005-03-01 00:00:00', 2);
2021-09-14 13:35:42   0s       Success  20210914**************1ms2    ALIYUN$****@test.aliyunid.com
2021-09-14 13:35:43   0s       Success  20210914**************j8o7    ALIYUN$****@test.aliyunid.com    select date_sub(getdate(),1);
2021-09-14 13:40:40   1s       Success  20210914**************h3wz    ALIYUN$****@test.aliyunid.com    select date_sub(getdate(),0);
2021-09-14 13:40:40   0s       Success  20210914**************9nm7    ALIYUN$****@test.aliyunid.com
10 instances
    • Example 3: View the information about the instances that are run by all users in another MaxCompute project within a specified period of time and specify the number of instances whose information you want to query. Sample statement: 
      ls instances from 2021-09-14 to 2021-09-15 -p doc_test_dev -all -limit 10;
      The following result is returned: 
      StartTime             RunTime  Status   InstanceID                    Owner                            Query
2021-09-14 11:59:16   0s       Success  20210914**************6pr3    ALIYUN$****@test.aliyunid.com
2021-09-14 11:59:20   1s       Success  20210914**************6qgr    ALIYUN$****@test.aliyunid.com    select date_sub(date '2007-02-26', -1);
2021-09-14 12:02:19   1s       Success  20210914**************h8o7    ALIYUN$****@test.aliyunid.com    select date_sub('2009-03-01 00:00:00', 2);
2021-09-14 12:02:25   0s       Success  20210914**************7t42    ALIYUN$****@test.aliyunid.com
2021-09-14 12:02:37   0s       Success  20210914**************7pr2    ALIYUN$****@test.aliyunid.com
2021-09-14 12:02:40   0s       Success  20210914**************emq2    ALIYUN$****@test.aliyunid.com    select date_sub('2015-03-01 00:00:00', 2);
2021-09-14 13:35:42   0s       Success  20210914**************1ms2    ALIYUN$****@test.aliyunid.com
2021-09-14 13:35:43   0s       Success  20210914**************68o7    ALIYUN$kiki                      select date_sub(getdate(),1);
2021-09-14 13:45:40   1s       Success  20210914**************73wz    ALIYUN$kiki                      select date_sub(getdate(),0);
2021-09-14 13:45:45   0s       Success  20210914**************9nm7    ALIYUN$dreak
10 instances
    • Example 4: View the information about the instances that are run by all users in another MaxCompute project on the current day, and specify the number of instances whose information you want to query. Sample statement: 
      show p -p doc_test_dev -all 5;
      The following result is returned: 
      StartTime             RunTime  Status   InstanceID                    Owner                            Query
2021-09-14 12:02:40   0s       Success  20210914**************emq2    ALIYUN$****@test.aliyunid.com    select date_sub('2015-03-01 00:00:00', 2);
2021-09-14 13:35:42   0s       Success  20210914**************1ms2    ALIYUN$****@test.aliyunid.com
2021-09-14 13:35:43   0s       Success  20210914**************68o7    ALIYUN$kiki                      select date_sub(getdate(),1);
2021-09-14 13:45:40   1s       Success  20210914**************73wz    ALIYUN$kiki                      select date_sub(getdate(),0);
2021-09-14 13:45:45   0s       Success  20210914**************9nm7    ALIYUN$dreak
5 instances
Note For more information about instance operations, see Instance operations.

Permission query operations

MaxCompute allows you to query the permissions of a user or a role, or the permissions on an object by using SHOW commands. For more information, see Query permissions by using MaxCompute SQL or Permission-related command set.

Operations that can be performed to collect information about the optimizer of MaxCompute

You can run the show statistic command to test collection results of column stats metrics.
  • Examples
    -- Test the collection result of the tinyint1 column.
show statistic analyze2_test columns (tinyint1);

-- Test the collection results of the smallint1, string1, boolean1, and timestamp1 columns.
show statistic analyze2_test columns (smallint1, string1, boolean1, timestamp1);

-- Test the collection results of all columns.
show statistic analyze2_test columns;
  • Output
    -- Collection result of the tinyint1 column:
ID = 20201126085225150gnqo****
tinyint1:MaxValue:      20                   -- The value of max.
tinyint1:DistinctNum:   4.0                  -- The value of ndv.
tinyint1:MinValue:      1                    -- The value of min.
tinyint1:NullNum:       1.0                  -- The value of nNulls.
tinyint1:TopK:  {1=1.0, 10=1.0, 20=1.0}      -- The value of topK. 10=1.0 indicates that the occurrence frequency of column value 10 is 1. A maximum of 20 values with the highest frequency of occurrence can be returned.

-- Collection results of the smallint1, string1, boolean1, and timestamp1 columns:
ID = 20201126091636149gxgf****
smallint1:MaxValue:     20
smallint1:DistinctNum:  4.0
smallint1:MinValue:     2
smallint1:NullNum:      1.0
smallint1:TopK:         {2=1.0, 7=1.0, 20=1.0}

string1:MaxLength       6.0                  -- The value of maxColLen.
string1:AvgLength:      3.0                  -- The value of avgColLen.
string1:DistinctNum:    4.0
string1:NullNum:        1.0
string1:TopK:   {str1=1.0, str12=1.0, str123=1.0}

boolean1:DistinctNum:   3.0
boolean1:NullNum:       1.0
boolean1:TopK:  {false=2.0, true=1.0}

timestamp1:DistinctNum:         3.0
timestamp1:NullNum:     1.0
timestamp1:TopK:        {2018-09-17 00:00:00.0=2.0, 2018-09-18 00:00:00.0=1.0}

-- Collection results of all columns:
ID = 20201126092022636gzm1****
tinyint1:MaxValue:      20
tinyint1:DistinctNum:   4.0
tinyint1:MinValue:      1
tinyint1:NullNum:       1.0
tinyint1:TopK:  {1=1.0, 10=1.0, 20=1.0}

smallint1:MaxValue:     20
smallint1:DistinctNum:  4.0
smallint1:MinValue:     2
smallint1:NullNum:      1.0
smallint1:TopK:         {2=1.0, 7=1.0, 20=1.0}

int1:MaxValue:  7
int1:DistinctNum:       3.0
int1:MinValue:  4
int1:NullNum:   1.0
int1:TopK:      {4=2.0, 7=1.0}

bigint1:MaxValue:       11111118
bigint1:DistinctNum:    4.0
bigint1:MinValue:       8
bigint1:NullNum:        1.0
bigint1:TopK:   {8=1.0, 2222228=1.0, 11111118=1.0}

double1:MaxValue:       123452.3
double1:DistinctNum:    4.0
double1:MinValue:       12.3
double1:NullNum:        1.0
double1:TopK:   {12.3=1.0, 67892.3=1.0, 123452.3=1.0}

decimal1:MaxValue:      22.4
decimal1:DistinctNum:   4.0
decimal1:MinValue:      2.4
decimal1:NullNum:       1.0
decimal1:TopK:  {2.4=1.0, 12.4=1.0, 22.4=1.0}

decimal2:MaxValue:      52.5
decimal2:DistinctNum:   4.0
decimal2:MinValue:      2.57
decimal2:NullNum:       1.0
decimal2:TopK:  {2.57=1.0, 42.5=1.0, 52.5=1.0}

string1:MaxLength       6.0
string1:AvgLength:      3.0
string1:DistinctNum:    4.0
string1:NullNum:        1.0
string1:TopK:   {str1=1.0, str12=1.0, str123=1.0}

varchar1:MaxLength      6.0
varchar1:AvgLength:     3.0
varchar1:DistinctNum:   4.0
varchar1:NullNum:       1.0
varchar1:TopK:  {str2=1.0, str200=1.0, str21=1.0}

boolean1:DistinctNum:   3.0
boolean1:NullNum:       1.0
boolean1:TopK:  {false=2.0, true=1.0}

timestamp1:DistinctNum:         3.0
timestamp1:NullNum:     1.0
timestamp1:TopK:        {2018-09-17 00:00:00.0=2.0, 2018-09-18 00:00:00.0=1.0}

datetime1:DistinctNum:  3.0
datetime1:NullNum:      1.0
datetime1:TopK:         {1537117199000=2.0, 1537030799000=1.0}
Note For more information about how to collect metadata for the optimizer of MaxCompute, see Collect information for the optimizer of MaxCompute.