Skip to content
AWS for Query.Farm

aws.redshift.describe_cluster_snapshots

Example SQL Queries

SELECT * FROM
aws.redshift.describe_cluster_snapshots;

Description

Returns one or more snapshot objects, which contain metadata about your cluster snapshots. By default, this operation returns information about all snapshots of all clusters that are owned by your Amazon Web Services account. No information is returned for snapshots owned by inactive Amazon Web Services accounts.

If you specify both tag keys and tag values in the same request, Amazon Redshift returns all snapshots that match any combination of the specified keys and values. For example, if you have owner and environment for tag keys, and admin and test for tag values, all snapshots that have any combination of those values are returned. Only snapshots that you own are returned in the response; shared snapshots are not returned with the tag key and tag value request parameters.

If both tag keys and values are omitted from the request, snapshots are returned regardless of whether they have tag keys or values associated with them.

Table Definition

Column NameColumn Data Type
cluster_exists Input Column

A value that indicates whether to return snapshots only for an existing cluster. You can perform table-level restore only by using a snapshot of an existing cluster, that is, a cluster that has not been deleted. Values for this parameter work as follows:

  • If ClusterExists is set to true, ClusterIdentifier is required.

  • If ClusterExists is set to false and ClusterIdentifier isn't specified, all snapshots associated with deleted clusters (orphaned snapshots) are returned.

  • If ClusterExists is set to false and ClusterIdentifier is specified for a deleted cluster, snapshots associated with that cluster are returned.

  • If ClusterExists is set to false and ClusterIdentifier is specified for an existing cluster, no snapshots are returned. 

BOOLEAN
cluster_identifier Input Column

The identifier of the cluster for which the snapshot was taken.

VARCHAR
end_time Input Column

A time value that requests only snapshots created at or before the specified time. The time value is specified in ISO 8601 format. For more information about ISO 8601, go to the ISO8601 Wikipedia page.

Example: 2012-07-16T18:00:00Z 

TIMESTAMP_S
owner_account Input Column

For manual snapshots, the Amazon Web Services account used to create or copy the snapshot. For automatic snapshots, the owner of the cluster. The owner can perform all snapshot actions, such as sharing a manual snapshot.

VARCHAR
snapshot_arn Input Column

The Amazon Resource Name (ARN) of the snapshot.

VARCHAR
snapshot_identifier Input Column

The snapshot identifier that is provided in the request.

VARCHAR
snapshot_type Input Column

The snapshot type. Snapshots created using CreateClusterSnapshot and CopyClusterSnapshot are of type "manual". 

VARCHAR
sorting_entities Input Column

STRUCT(
    "attribute" VARCHAR,
    "sort_order" VARCHAR
)[]
Show child fields
sorting_entities[]
Show child fields
sorting_entities[].attribute

The category for sorting the snapshots.

sorting_entities[].sort_order

The order for listing the attributes.

start_time Input Column

A value that requests only snapshots created at or after the specified time. The time value is specified in ISO 8601 format. For more information about ISO 8601, go to the ISO8601 Wikipedia page.

Example: 2012-07-16T18:00:00Z 

TIMESTAMP_S
tag_keys Input Column

A tag key or keys for which you want to return all matching cluster snapshots that are associated with the specified key or keys. For example, suppose that you have snapshots that are tagged with keys called owner and environment. If you specify both of these tag keys in the request, Amazon Redshift returns a response with the snapshots that have either or both of these tag keys associated with them.

VARCHAR[]
Show child fields
tag_keys[]
tag_values Input Column

A tag value or values for which you want to return all matching cluster snapshots that are associated with the specified tag value or values. For example, suppose that you have snapshots that are tagged with values called admin and test. If you specify both of these tag values in the request, Amazon Redshift returns a response with the snapshots that have either or both of these tag values associated with them.

VARCHAR[]
Show child fields
tag_values[]
_aws_profile Input Column

The AWS profile defines the AWS identity used. It can be defined via credentials or by assuming a IAM role.

STRUCT(
    "type" VARCHAR,
    "name" VARCHAR,
    "account_id" VARCHAR,
    "via_profile_name" VARCHAR,
    "assumed_role_arn" VARCHAR,
    "organization" STRUCT(
        "account_name" VARCHAR,
        "id" VARCHAR,
        "tags" STRUCT(
            "key" VARCHAR,
            "value" VARCHAR
        )[],
        "master_account" STRUCT(
            "id" VARCHAR,
            "email" VARCHAR
        ),
        "parents" STRUCT(
            "type" VARCHAR,
            "id" VARCHAR,
            "name" VARCHAR,
            "tags" STRUCT(
                "key" VARCHAR,
                "value" VARCHAR
            )[]
        )[]
    )
)
Show child fields
_aws_profile.account_id

The AWS account id

_aws_profile.assumed_role_arn

The ARN of the assumed role

_aws_profile.name

The unique name of the profile.

_aws_profile.organization

Information about this profile's membership in the AWS organization.

Show child fields
_aws_profile.organization.account_name

The name of account speciifed by the organization

_aws_profile.organization.id

The organization id

_aws_profile.organization.master_account
Show child fields
_aws_profile.organization.master_account.email

The organization master account email address

_aws_profile.organization.master_account.id

The organization master account id

_aws_profile.organization.parents[]
Show child fields
_aws_profile.organization.parents[].id

The id of the parent

_aws_profile.organization.parents[].name

The name of the parent

_aws_profile.organization.parents[].tags[]
Show child fields
_aws_profile.organization.parents[].tags[].key
_aws_profile.organization.parents[].tags[].value
_aws_profile.organization.parents[].type

The type of parent can be an organization unit or a root

_aws_profile.organization.tags[]
Show child fields
_aws_profile.organization.tags[].key
_aws_profile.organization.tags[].value
_aws_profile.type

The type of profile, either 'credentials' or 'assumed_role'

_aws_profile.via_profile_name

This IAM role for this profile is assumed by first utilizing another profile with this name to obtain credentials.

_aws_region Input Column

The AWS region to use.

VARCHAR
accounts_with_restore_access

A list of the Amazon Web Services accounts authorized to restore the snapshot. Returns null if no accounts are authorized. Visible only to the snapshot owner. 

STRUCT(
    "account_id" VARCHAR,
    "account_alias" VARCHAR
)[]
Show child fields
accounts_with_restore_access[]
Show child fields
accounts_with_restore_access[].account_alias

The identifier of an Amazon Web Services support account authorized to restore a snapshot. For Amazon Web Services Support, the identifier is amazon-redshift-support.

accounts_with_restore_access[].account_id

The identifier of an Amazon Web Services account authorized to restore a snapshot.

actual_incremental_backup_size_in_mega_bytes

The size of the incremental backup.

DOUBLE
availability_zone

The Availability Zone in which the cluster was created.

VARCHAR
backup_progress_in_mega_bytes

The number of megabytes that have been transferred to the snapshot backup.

DOUBLE
cluster_create_time

The time (UTC) when the cluster was originally created.

TIMESTAMP_S
cluster_version

The version ID of the Amazon Redshift engine that is running on the cluster.

VARCHAR
current_backup_rate_in_mega_bytes_per_second

The number of megabytes per second being transferred to the snapshot backup. Returns 0 for a completed backup. 

DOUBLE
db_name

The name of the database that was created when the cluster was created.

VARCHAR
elapsed_time_in_seconds

The amount of time an in-progress snapshot backup has been running, or the amount of time it took a completed backup to finish.

BIGINT
encrypted

If true, the data in the snapshot is encrypted at rest.

BOOLEAN
encrypted_with_hsm

A boolean that indicates whether the snapshot data is encrypted using the HSM keys of the source cluster. true indicates that the data is encrypted using HSM keys.

BOOLEAN
engine_full_version

The cluster version of the cluster used to create the snapshot. For example, 1.0.15503. 

VARCHAR
enhanced_vpc_routing

An option that specifies whether to create the cluster with enhanced VPC routing enabled. To create a cluster that uses enhanced VPC routing, the cluster must be in a VPC. For more information, see Enhanced VPC Routing in the Amazon Redshift Cluster Management Guide.

If this option is true, enhanced VPC routing is enabled.

Default: false

BOOLEAN
estimated_seconds_to_completion

The estimate of the time remaining before the snapshot backup will complete. Returns 0 for a completed backup. 

BIGINT
kms_key_id

The Key Management Service (KMS) key ID of the encryption key that was used to encrypt data in the cluster from which the snapshot was taken.

VARCHAR
maintenance_track_name

The name of the maintenance track for the snapshot.

VARCHAR
manual_snapshot_remaining_days

The number of days until a manual snapshot will pass its retention period.

BIGINT
manual_snapshot_retention_period

The number of days that a manual snapshot is retained. If the value is -1, the manual snapshot is retained indefinitely.

The value must be either -1 or an integer between 1 and 3,653.

BIGINT
master_password_secret_arn

The Amazon Resource Name (ARN) for the cluster's admin user credentials secret.

VARCHAR
master_password_secret_kms_key_id

The ID of the Key Management Service (KMS) key used to encrypt and store the cluster's admin credentials secret.

VARCHAR
master_username

The admin user name for the cluster.

VARCHAR
node_type

The node type of the nodes in the cluster.

VARCHAR
number_of_nodes

The number of nodes in the cluster.

BIGINT
port

The port that the cluster is listening on.

BIGINT
restorable_node_types

The list of node types that this cluster snapshot is able to restore into.

VARCHAR[]
Show child fields
restorable_node_types[]
snapshot_create_time

The time (in UTC format) when Amazon Redshift began the snapshot. A snapshot contains a copy of the cluster data as of this exact time.

TIMESTAMP_S
snapshot_retention_start_time

A timestamp representing the start of the retention period for the snapshot.

TIMESTAMP_S
source_region

The source region from which the snapshot was copied.

VARCHAR
status

The snapshot status. The value of the status depends on the API operation used: 

VARCHAR
tags

The list of tags for the cluster snapshot.

STRUCT(
    "key" VARCHAR,
    "value" VARCHAR
)[]
Show child fields
tags[]
Show child fields
tags[].key

The key, or name, for the resource tag.

tags[].value

The value for the resource tag.

total_backup_size_in_mega_bytes

The size of the complete set of backup data that would be used to restore the cluster.

DOUBLE
vpc_id

The VPC identifier of the cluster if the snapshot is from a cluster in a VPC. Otherwise, this field is not in the output.

VARCHAR