SHOW SERVICES

Lists the Snowpark Container Services services (including job services) for which you have access privileges.

  • The SHOW SERVICES output also includes services running as jobs (see EXECUTE JOB SERVICE).

  • SHOW JOB SERVICES provides only the list of services running as jobs.

  • SHOW SERVICES EXCLUDE JOBS output does not include services running as jobs.

See also:

CREATE SERVICE , ALTER SERVICE, DROP SERVICE , DESCRIBE SERVICE, SHOW SERVICE INSTANCES IN SERVICE, SHOW SERVICE CONTAINERS IN SERVICE

Syntax

SHOW [ JOB ] SERVICES [ EXCLUDE JOBS ] [ LIKE '<pattern>' ]
           [ IN
                {
                  ACCOUNT                  |

                  DATABASE                 |
                  DATABASE <database_name> |

                  SCHEMA                   |
                  SCHEMA <schema_name>     |
                  <schema_name>            |

                  COMPUTE POOL <compute_pool_name>
                }
           ]
           [ STARTS WITH '<name_string>' ]
           [ LIMIT <rows> [ FROM '<name_string>' ] ]
Copy

Parameters

LIKE 'pattern'

Optionally filters the command output by object name. The filter uses case-insensitive pattern matching, with support for SQL wildcard characters (% and _).

For example, the following patterns return the same results:

... LIKE '%testing%' ...
... LIKE '%TESTING%' ...

. Default: No value (no filtering is applied to the output).

[ IN ... ]

Optionally specifies the scope of the command. Specify one of the following:

ACCOUNT

Returns records for the entire account.

DATABASE, . DATABASE db_name

Returns records for the current database in use or for a specified database (db_name).

If you specify DATABASE without db_name and no database is in use, the keyword has no effect on the output.

Note

Using SHOW commands without an IN clause in a database context can result in fewer than expected results.

Objects with the same name are only displayed once if no IN clause is used. For example, if you have table t1 in schema1 and table t1 in schema2, and they are both in scope of the database context you’ve specified (that is, the database you’ve selected is the parent of schema1 and schema2), then SHOW TABLES only displays one of the t1 tables.

SCHEMA, . SCHEMA schema_name

Returns records for the current schema in use or a specified schema (schema_name).

SCHEMA is optional if a database is in use or if you specify the fully qualified schema_name (for example, db.schema).

If no database is in use, specifying SCHEMA has no effect on the output.

Default: Depends on whether the session currently has a database in use:

  • Database: DATABASE is the default (that is, the command returns the objects you have privileges to view in the database).

  • No database: ACCOUNT is the default (that is, the command returns the objects you have privileges to view in your account).

STARTS WITH 'name_string'

Optionally filters the command output based on the characters that appear at the beginning of the object name. The string must be enclosed in single quotes and is case-sensitive.

For example, the following strings return different results:

... STARTS WITH 'B' ...
... STARTS WITH 'b' ...

. Default: No value (no filtering is applied to the output)

LIMIT rows

Optionally limits the maximum number of rows returned. The actual number of rows returned might be less than the specified limit. For example, the number of existing objects is less than the specified limit.

Default: No value (no limit is applied to the output).

Output

The command output provides service properties and metadata in the following columns:

Column

Description

name

Snowpark Container Services service name.

status

One of the following values, which indicates the current status of the service:

  • PENDING

  • RUNNING

  • FAILED

  • DONE

  • SUSPENDING

  • SUSPENDED

  • DELETING

  • DELETED

  • INTERNAL_ERROR

database_name

Database in which the service is created.

schema_name

Schema in which the service is created.

owner

Role that owns the service.

compute_pool

Compute pool name where Snowflake runs the service.

dns_name

Snowflake-assigned DNS name of the service in this format: service-name.unique-id.svc.spcs.internal.

The unique-id is a 4-8 character long alphanumeric identifier that is unique to a particular instance of a database schema. To find the unique ID for a schema, call the SYSTEM$GET_SERVICE_DNS_DOMAIN function. For example:

SELECT SYSTEM$GET_SERVICE_DNS_DOMAIN('mydb.myschema');
Copy

Note the following:

  • If you rename a schema, the identifier remains unchanged.

  • If you drop and recreate a schema with the same name, the identifier will change.

The DNS name enables service-to-service communications (see Tutorial 3).

current_instances

The current number of instances for the service.

target_instances

The target number of service instances that should be running as determined by Snowflake.

When the current_instances value is not equal to the target_instances value, Snowflake is either in the process of shutting down or launching service instances.

For example, consider the following:

  • Suppose you create a service with MIN_INSTANCES = 1 and MAX_INSTANCES = 3. While the service is running, Snowflake might determine that one instance is not enough. In this case, the value of target_instances will increase, indicating Snowflake is in the process of launching additional instances.

    It’s also possible that the target_instances value is less than the current_instances value, which indicates that Snowflake is in the process of reducing the number of running instances.

  • If you create services but the compute pool does’t have capacity for the minimum number of instances that you requested, the value of target_instances will be equal to the value of min_instances. The value of current_instances will be less than the value of target_instances.

min_ready_instances

Indicates the minimum service instances that must be ready for Snowflake to consider the service is ready to process requests.

min_instances

Minimum number of service instances Snowflake should run.

max_instances

Maximum number of service instances that Snowflake can scale when needed.

auto_resume

If true, Snowflake auto-resumes the service, if suspended, when service function is called or when an incoming request (ingres) is received (see Using a service).

external_access_integrations

List of external access integrations associated with the service. For more information, see Configuring network egress.

created_on

Date and time when the service was created.

updated_on

Date and time when service is last updated.

resumed_on

Timestamp when the service was last resumed.

suspended_on

Timestamp when the service was last suspended. suspended_on is set when Snowflake suspends a service and remains unchanged even after the service is resumed. If suspended_on is NULL, the service was never suspended.

auto_suspend_secs

Number of seconds of inactivity after which Snowflake automatically suspends the service. If auto_suspend_secs is set to 0 or never set, Snowflake does not automatically suspend the service.

comment

Service related comment.

owner_role_type

The type of role that owns the object, either ROLE or DATABASE_ROLE.

query_warehouse

When a service container connects to Snowflake to execute a query and does not explicitly specify a warehouse to use, Snowflake uses this warehouse as default.

is_job

true if the service is a job service; false otherwise. SHOW JOB SERVICES and SHOW SERVICES EXCLUDE JOBS do not include this column in the output.

is_async_job

If TRUE, the job service is running asynchronously. By default, Snowflake executes the job services synchronously. This column is included in the output of the SHOW SERVICES, and SHOW JOB SERVICES commands but not in the output of the SHOW SERVICES EXCLUDING JOBS command.

spec_digest

The unique and immutable identifier representing the service spec content.

To observe the changes to the value of the spec_digest column over time, a service user might execute the SHOW SERVICES command periodically. If the service user notices a change in value, they can infer that the service was upgraded.

is_upgrading

TRUE, if Snowflake is in the process of upgrading the service.

managing_object_domain

The domain of the managing object (for example, the domain of the notebook that manages the service). NULL if the service is not managed by a Snowflake entity.

managing_object_name

The name of the managing object (for example, the name of the notebook that manages the service). NULL if the service is not managed by a Snowflake entity.

Access control requirements

A role used to execute this SQL command must have at least one of the following privileges at a minimum:

Privilege

Object

Notes

Any one of these privileges: OWNERSHIP, USAGE, MONITOR or OPERATE

Service

The USAGE privilege on the parent database and schema are required to perform operations on any object in a schema.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

Usage notes

  • The command doesn’t require a running warehouse to execute.

  • The command only returns objects for which the current user’s current role has been granted at least one access privilege.

  • The MANAGE GRANTS access privilege implicitly allows its holder to see every object in the account. By default, only the account administrator (users with the ACCOUNTADMIN role) and security administrator (users with the SECURITYADMIN role) have the MANAGE GRANTS privilege.

  • To post-process the output of this command, you can use the RESULT_SCAN function, which treats the output as a table that can be queried.

  • The command returns a maximum of ten thousand records for the specified object type, as dictated by the access privileges for the role used to execute the command. Any records above the ten thousand records limit aren’t returned, even with a filter applied.

    To view results for which more than ten thousand records exist, query the corresponding view (if one exists) in the Snowflake Information Schema.

Examples

The following example lists services in the current database and schema for the session:

SHOW SERVICES;
Copy

Sample output:

+--------------+---------+---------------+-------------+-----------+-----------------------+-------------------------------------+-------------------+------------------+---------------------+---------------+---------------+-------------+------------------------------+-------------------------------+-------------------------------+------------+--------------+-------------------+---------+-----------------+-----------------+--------+--------------+------------------------------------------------------------------+--------------+------------------------+----------------------+
| name         | status  | database_name | schema_name | owner     | compute_pool          | dns_name                            | current_instances | target_instances | min_ready_instances | min_instances | max_instances | auto_resume | external_access_integrations | created_on                    | updated_on                    | resumed_on | suspended_on | auto_suspend_secs | comment | owner_role_type | query_warehouse | is_job | is_async_job | spec_digest                                                      | is_upgrading | managing_object_domain | managing_object_name |
|--------------+---------+---------------+-------------+-----------+-----------------------+-------------------------------------+-------------------+------------------+---------------------+---------------+---------------+-------------+------------------------------+-------------------------------+-------------------------------+------------+--------------+-------------------+---------+-----------------+-----------------+--------+--------------+------------------------------------------------------------------+--------------+------------------------+----------------------|
| ECHO_SERVICE | RUNNING | TUTORIAL_DB   | DATA_SCHEMA | TEST_ROLE | TUTORIAL_COMPUTE_POOL | echo-service.k3m6.svc.spcs.internal |                 1 |                1 |                   1 |             1 |             1 | true        | NULL                         | 2024-11-29 12:12:47.310 -0800 | 2024-11-29 12:12:48.843 -0800 | NULL       | NULL         |                 0 | NULL    | ROLE            | NULL            | false  | false        | edaf548eb0c2744a87426529b53aac75756d0ea1c0ba5edb3cbb4295a381f2b4 | false        | NULL                   | NULL                 |
+--------------+---------+---------------+-------------+-----------+-----------------------+-------------------------------------+-------------------+------------------+---------------------+---------------+---------------+-------------+------------------------------+-------------------------------+-------------------------------+------------+--------------+-------------------+---------+-----------------+-----------------+--------+--------------+------------------------------------------------------------------+--------------+------------------------+----------------------+

The following example lists one service:

SHOW SERVICES LIMIT 1;
Copy

The following example lists services with names containing “echo”:

SHOW SERVICES LIKE '%echo%';
Copy

The following example lists one service with a name containing “echo”:

SHOW SERVICES LIKE '%echo%' LIMIT 1;
Copy

The following example lists only services running as a job:

SHOW JOB SERVICES;
Copy