All Products
Search

This Product

    MaxCompute:SHOW

    Document Center

    MaxCompute:SHOW

    Last Updated:Mar 26, 2026

    Use SHOW commands to inspect project objects, query history, permissions, and optimizer statistics directly from the MaxCompute client (odpscmd) or SQL interface.

    The following table lists the SHOW command categories covered in this topic.

    Category What you can do
    SET operations View properties configured with the SET statement
    Tunnel operations View tunnel command history and upload/download logs
    Security operations View security configuration properties of a project
    Package operations View packages created or installed in a project
    Backup operations View table and partition backup data within the retention period
    Table operations View table definitions, list objects, and inspect partitions
    Function operations List user-defined functions (UDFs) and built-in functions
    Instance operations View instance execution history and status
    Permission query operations Query permissions for users, roles, or objects
    Optimizer statistics operations Test column statistics collection results

    SET operations

    Display the flags configured with the SET statement.

    Syntax

    show flags;

    For the full SET command reference, see SET operations.

    Tunnel operations

    View upload and download history

    Display the history of tunnel upload and download commands.

    Syntax

    tunnel show history [-n <number>];

    Parameters

    Parameter Required Description
    -n <number> No Number of history records to display. Defaults to 500.

    Output

    Each record contains three fields: instance ID, status (success, failed, or bad), and the command string.

    Examples

    Display the default history (up to 500 records):

    tunnel show history;

    Expected output:

    20230505xxxxxxxxxxxxxx0b0d5b3c  bad     'upload d:\data.txt sale_detail/sale_date=201312,region=hangzhou -dbr true -time true'
20230505xxxxxxxxxxxxxx0ad720a3  failed  'upload d:\data.txt sale_detail/sale_date=201312,region=hangzhou -time true'
20230505xxxxxxxxxxxxxx0ad5ca68  bad     'upload d:\data.txt sale_detail/sale_date=201312,region=hangzhou -dbr true'
......

    Display the five most recent records:

    tunnel show history -n 5;

    Expected output:

    20230505xxxxxxxxxxxxxx0aa48c4b  success 'download sale_detail/sale_date=201312,region=hangzhou result.txt'
20230505xxxxxxxxxxxxxx0aa6165c  success 'download sale_detail/sale_date=201312,region=hangzhou result.txt'
20230505xxxxxxxxxxxxxx0af11472  failed  'upload d:\data.txt sale_detail/sale_date=201312,region=hangzhou -s false'
20230505xxxxxxxxxxxxxx0b464374  success 'upload d:\data.txt sale_detail/sale_date=201312,region=hangzhou -s false'
20230505xxxxxxxxxxxxxx02dbb6bd  failed  'upload d:\data.txt sale_detail/sale_date="201312",region="hangzhou" -s false'

    View the last upload or download log

    Display the log from the most recent tunnel upload or download operation.

    Syntax

    tunnel show log;

    For the full tunnel command reference, see Tunnel commands.

    Security operations

    View the security configuration properties of the current project.

    For the full security command reference, see Security operations.

    Package operations

    List the packages created or installed in a MaxCompute project. Only the package creator can run this command.

    Syntax

    show packages;

    Output

    The result includes two tables. The first table lists packages you have created:

    Field Description
    PackageName Name of the package
    CreateTime Time when the package was created

    The second table lists packages installed from other projects:

    Field Description
    PackageName Name of the package
    SourceProject Project that owns the package
    InstallTime Time when the package was installed
    Status Current status of the package

    Example

    -- View packages in the current project.
show packages;

    Expected output:

    +-------------+--------------------------+
| PackageName | CreateTime               |
+-------------+--------------------------+
| datashare   | 2021-12-28T18:10:39+0800 |
+-------------+--------------------------+
+-------------+--------------------+--------------------------+--------+
| PackageName | SourceProject      | InstallTime              | Status |
+-------------+--------------------+--------------------------+--------+
| systables   | information_schema | 2020-11-24T14:11:23+0800 | OK     |
+-------------+--------------------+--------------------------+--------+

    For the full package command reference, see Cross-project resource access based on packages.

    Backup operations

    Use backup SHOW commands to inspect backup data for tables and partitions within their retention period. To look up backup data for a deleted table or partition, first run show history for table <table_name>; and get the object ID from the ObjectId field in the output.

    List tables including deleted tables

    show history for tables [like <table_name>];

    Use the optional LIKE clause to filter by table name. The asterisk (*) matches any character.

    View backup data for a table

    show history for table <table_name>;

    View backup data for a deleted table

    show history for table table_name ('id'='xxxx');

    Replace xxxx with the value from the ObjectId field returned by show history for table <table_name>;.

    View backup data for a partition

    show history for table table_name partition_spec;

    View backup data for a deleted partition

    show history for table table_name PARTITION('id'='xxxx');

    Replace xxxx with the value from the ObjectId field returned by show history for table <table_name>;.

    For the full backup and restoration reference, see Backup and restoration.

    Table operations

    show external tables;, show views;, and show materialized views; require MaxCompute client (odpscmd) V0.43.0 or later.

    To view backup data for tables that have been deleted, see Backup operations.

    View the CREATE TABLE statement

    Print the CREATE TABLE statement used to create a table.

    Syntax

    show create table <table_name>;

    Parameters

    Parameter Required Description
    table_name Yes Name of the table

    Example

    -- View the CREATE TABLE statement for the sale_detail table.
show create table sale_detail;

    Expected output:

    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;

    List tables, external tables, views, and materialized views

    List all objects in the current project, or filter by name pattern. Objects include tables, external tables, views, and materialized views.

    Syntax

    -- List all objects.
show tables;
-- List objects whose names match the pattern.
show tables like '<chart>';

    The asterisk (*) in the pattern matches any character.

    Output

    Each line follows the format PREFIX$account_name:object_name, where ALIYUN indicates the object was created by an Alibaba Cloud account and RAM indicates it was created by a RAM user.

    Example

    -- List objects whose names start with "sale".
show tables like 'sale*';

    Expected output:

    ALIYUN$account_name:sale_detail
......

    List external tables

    List all external tables in the current project, or filter by name pattern. Requires odpscmd V0.43.0 or later.

    Syntax

    -- List all external tables.
show external tables;
-- List external tables whose names match the pattern.
show external tables like '<external_chart>';

    Example

    -- List external tables whose names start with "a".
show external tables like 'a*';

    Expected output:

    ALIYUN$account_name:a_et
......

    List views

    List all views in the current project, or filter by name pattern. Requires odpscmd V0.43.0 or later.

    Syntax

    -- List all views.
show views;
-- List views whose names match the pattern.
show views like '<view>';

    Example

    -- List views whose names start with "mf".
show views like 'mf*';

    Expected output:

    ALIYUN$account_name:mf_v
......

    List materialized views

    List all materialized views in the current project, or filter by name pattern. Requires odpscmd V0.43.0 or later.

    Syntax

    -- List all materialized views.
show materialized views;
-- List materialized views whose names match the pattern.
show materialized views like '<materialized_view>';

    Example

    -- List materialized views whose names start with "test".
show materialized views like 'test*';

    Expected output:

    ALIYUN$account_name:test_two_mv
ALIYUN$account_name:test_create_one_mv
......

    List partitions

    List all partitions of a partitioned table. Returns an error if the table does not exist or is not partitioned.

    Syntax

    show partitions <table_name>;

    Parameters

    Parameter Required Description
    table_name Yes Name of the partitioned table

    Example

    -- List all partitions of the sale_detail table.
show partitions sale_detail;

    Expected output:

    sale_date=201310/region=beijing
sale_date=201312/region=shenzhen
sale_date=201312/region=xian
sale_date=2014/region=shenzhen

OK

    For the full table command reference, see Table operations.

    Function operations

    show functions; and show builtin functions; require MaxCompute client (odpscmd) V0.43.0 or later.

    List user-defined functions (UDFs)

    List all UDFs in a MaxCompute project. The following two syntaxes are equivalent:

    list functions [-p <project_name>];
    show functions;

    Parameters

    Parameter Required Description
    project_name No Name of the project to query. Defaults to the current project.

    Output

    Field Description
    Name Name of the UDF
    Owner Account that created the UDF
    Create Time Time when the UDF was created
    Class Java class that implements the UDF
    Resources Resource files the UDF depends on

    Example

    list functions;

    Expected output (truncated):

    Name              Owner                                        Create Time           Class                             Resources
ipv4_ipv6_aton    ALIYUN$****@aliyun.com 2021-11-15 13:42:14   com.aliyun.odps.udf.udfFunction.IpLocation ipv4.txt,ipv6.txt,udf-1.0-SNAPSHOT.jar
Lower_test        ALIYUN$****@aliyun.com 2021-08-25 15:51:22   com.aliyun.odps.udf.example.Lower udf-1.0-SNAPSHOT.jar
my_add            ALIYUN$****@aliyun.com 2021-05-08 11:26:02
my_index          ALIYUN$****@aliyun.com 2021-08-25 12:01:05   com.aliyun.odps.examples.udf.UdfArray udf-1.0-SNAPSHOT.jar
my_sum            ALIYUN$****@aliyun.com 2021-05-08 10:24:58
my_udtf           ALIYUN$****@aliyun.com 2021-02-23 11:37:30   com.aliyun.odps.examples.udf.UDTFResource udf-1.0-SNAPSHOT.jar
......

    List built-in functions

    List built-in functions in a MaxCompute project. Filter by exact name or pattern. Requires odpscmd V0.43.0 or later.

    Syntax

    -- List all built-in functions.
show builtin functions;
-- Query a specific built-in function by name.
show builtin functions [<function_name>];
-- List built-in functions whose names match the pattern.
show builtin functions like '<function_name>';

    The asterisk (*) in the pattern matches any character.

    Examples

    List all built-in functions:

    show builtin functions;

    Expected output (truncated):

    ID = 20230307081023424gef2hwowr1
::ABS   SCALAR  DECIMAL(?,?) ABS(DECIMAL(?,?) arg0),DOUBLE ABS(DOUBLE arg0),BIGINT ABS(BIGINT arg0),INT ABS(INT arg0)
::ACOS  SCALAR  DOUBLE ACOS(DOUBLE arg0),DOUBLE ACOS(DECIMAL(?,?) arg0)
::ADD_MONTHS    SCALAR  STRING ADD_MONTHS(DATE arg0, BIGINT arg1),STRING ADD_MONTHS(TIMESTAMP arg0, BIGINT arg1),STRING ADD_MONTHS(STRING arg0, BIGINT arg1)
......

    Query a specific function by name:

    -- Query the built-in function named abs.
show builtin functions abs;

    Expected output:

    ID = 20230307085650740gm4qqqk6dox5
::ABS   SCALAR  DOUBLE ABS(DOUBLE arg0),DECIMAL(?,?) ABS(DECIMAL(?,?) arg0),BIGINT ABS(BIGINT arg0),INT ABS(INT arg0)

    List built-in functions matching a pattern:

    -- List built-in functions whose names start with "a".
show builtin functions like 'a*';

    Expected output (truncated):

    ID = 20230307085929584g784o0q9z8d1
::ABS   SCALAR  INT ABS(INT arg0),DOUBLE ABS(DOUBLE arg0),BIGINT ABS(BIGINT arg0),DECIMAL(?,?) ABS(DECIMAL(?,?) arg0)
::ACOS  SCALAR  DOUBLE ACOS(DOUBLE arg0),DOUBLE ACOS(DECIMAL(?,?) arg0)
::ADD_MONTHS    SCALAR  STRING ADD_MONTHS(STRING arg0, BIGINT arg1),STRING ADD_MONTHS(TIMESTAMP arg0, BIGINT arg1),STRING ADD_MONTHS(DATE arg0, BIGINT arg1)
......

    For the full function command reference, see Function operations.

    Instance operations

    View information about query instances, including start time, run time, status, and the query statement.

    All of the following statements are equivalent: show p, show proc, show processlist, show instances, ls instances, list instances.

    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];

    Parameters

    Parameter Required Description
    startdate / enddate No Date range to query, in yyyy-mm-dd format. The range is inclusive of startdate and exclusive of enddate. Defaults to the current day.
    project_name No Project to query. Defaults to the current project. Requires permission to view instances in that project.
    -limit <number> or <number> No Maximum number of instances to return, ordered by submission time (most recent first). The two forms are equivalent.
    -all No Return instances from all project members. Without this flag, only the current user's instances are returned.
    Without -limit, up to 50 instances are returned by default. Combining -all with -p <project_name> requires permission to view instances in the target project. The enddate is exclusive — instances submitted on enddate are not included in the results.

    Output

    Field Description
    StartTime Instance submission time, accurate to the second
    RunTime Execution duration in seconds
    Status Instance status
    InstanceID Unique instance identifier
    Owner Account that submitted the instance
    Query SQL statement of the instance

    Examples

    View today's instances for the current user in the current project:

    show p;

    Expected output:

    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

    View up to 10 of the current user's instances within a specified date range:

    show instances from 2021-09-14 to 2021-09-15 -limit 10;

    Expected output:

    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

    View up to 10 instances from all users in another project within a specified date range:

    ls instances from 2021-09-14 to 2021-09-15 -p doc_test_dev -all -limit 10;

    Expected output:

    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

    View up to 5 instances from all users in another project on the current day:

    show p -p doc_test_dev -all 5;

    Expected output:

    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

    For the full instance command reference, see Instance operations.

    Permission query operations

    Query permissions granted to a user or role, or permissions on a specific object. For details, see Query permissions by using MaxCompute SQL and Permission-related command set.

    Optimizer statistics operations

    Run show statistic to test column statistics collection results for the MaxCompute optimizer.

    Syntax

    -- Test collection results for a single column.
show statistic <table_name> columns (<column_name>);

-- Test collection results for multiple columns.
show statistic <table_name> columns (<col1>, <col2>, ...);

-- Test collection results for all columns.
show statistic <table_name> columns;

    Output

    Each column in the result includes the following metrics:

    Metric Description
    MaxValue Maximum column value
    DistinctNum Number of distinct values (NDV)
    MinValue Minimum column value
    NullNum Number of null values
    TopK Up to 20 values with the highest occurrence frequency, with their counts

    String and varchar columns include two additional metrics:

    Metric Description
    MaxLength Maximum column value length
    AvgLength Average column value length

    Examples

    Test collection results for the tinyint1 column:

    show statistic analyze2_test columns (tinyint1);

    Expected output:

    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. Up to 20 values with the highest occurrence frequency can be returned.

    Test collection results for multiple columns:

    show statistic analyze2_test columns (smallint1, string1, boolean1, timestamp1);

    Expected output:

    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}

    Test collection results for all columns:

    show statistic analyze2_test columns;

    Expected output:

    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}

    For the full optimizer statistics reference, see Collect information for the optimizer of MaxCompute.