steampipe plugin install aws

Table: aws_redshift_snapshot - Query AWS Redshift Snapshots using SQL

The AWS Redshift Snapshot is a point-in-time copy of your data in AWS Redshift, a fully managed, petabyte-scale data warehouse service in the cloud. Snapshots are used to back up data and enable fast restore. They are automatically created by Redshift and can also be manually created by users.

Table Usage Guide

The aws_redshift_snapshot table in Steampipe provides you with information about snapshots within AWS Redshift. This table allows you, as a DevOps engineer, to query snapshot-specific details, including the snapshot status, creation time, source cluster, and associated metadata. You can utilize this table to gather insights on snapshots, such as snapshot availability, size, and retention period. The schema outlines the various attributes of the Redshift snapshot for you, including the snapshot identifier, snapshot type, and associated tags.

Examples

Basic info

Explore which snapshots in your AWS Redshift database are encrypted. This can help you identify potential security risks and ensure that sensitive data is adequately protected.

select
snapshot_identifier,
cluster_identifier,
node_type,
encrypted
from
aws_redshift_snapshot;
select
snapshot_identifier,
cluster_identifier,
node_type,
encrypted
from
aws_redshift_snapshot;

List manual snapshots

Explore which snapshots have been manually created in your AWS Redshift environment. This can assist in understanding your data backup and recovery practices.

select
snapshot_identifier,
snapshot_type
from
aws_redshift_snapshot
where
snapshot_type = 'manual';
select
snapshot_identifier,
snapshot_type
from
aws_redshift_snapshot
where
snapshot_type = 'manual';

List unencrypted snapshots

Discover the segments that contain unencrypted snapshots in your AWS Redshift database. This is useful for identifying potential security risks and ensuring your data is properly protected.

select
snapshot_identifier,
cluster_identifier,
node_type,
number_of_nodes,
encrypted
from
aws_redshift_snapshot
where
not encrypted;
select
snapshot_identifier,
cluster_identifier,
node_type,
number_of_nodes,
encrypted
from
aws_redshift_snapshot
where
encrypted = 0;

Get cluster info for each snapshot

Explore the specifics of each snapshot, such as the associated cluster, its size, version, and potential restore options. This is useful for understanding the characteristics of each snapshot and for planning potential restore scenarios.

select
snapshot_identifier,
cluster_identifier,
number_of_nodes,
cluster_version,
engine_full_version,
restorable_node_types
from
aws_redshift_snapshot;
select
snapshot_identifier,
cluster_identifier,
number_of_nodes,
cluster_version,
engine_full_version,
restorable_node_types
from
aws_redshift_snapshot;

List snapshots that are shared with other accounts

Identify instances where snapshots are accessible to other accounts, a crucial step in assessing data sharing and privacy practices within your AWS Redshift environment.

select
snapshot_identifier,
accounts_with_restore_access
from
aws_redshift_snapshot
where
accounts_with_restore_access is not null;
select
snapshot_identifier,
accounts_with_restore_access
from
aws_redshift_snapshot
where
accounts_with_restore_access is not null;

List accounts that are authorized to restore each snapshot

Determine which accounts have permission to restore each snapshot in your AWS Redshift database. This is useful for auditing and managing data recovery permissions across your organization.

select
snapshot_identifier,
p ->> 'AccountId' as account_id,
p ->> 'AccountAlias' as account_alias
from
aws_redshift_snapshot,
jsonb_array_elements(accounts_with_restore_access) as p;
select
snapshot_identifier,
json_extract(p.value, '$.AccountId') as account_id,
json_extract(p.value, '$.AccountAlias') as account_alias
from
aws_redshift_snapshot,
json_each(accounts_with_restore_access) as p;

Schema for aws_redshift_snapshot

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
account_idtextThe AWS Account ID in which the resource is located.
accounts_with_restore_accessjsonbA list of the AWS customer accounts authorized to restore the snapshot.
actual_incremental_backup_size_in_mega_bytesdouble precisionThe size of the incremental backup.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
availability_zonetextThe Availability Zone in which the cluster was created.
backup_progress_in_mega-bytesdouble precisionThe number of megabytes that have been transferred to the snapshot backup.
cluster_create_timetimestamp with time zoneThe time (UTC) when the cluster was originally created.
cluster_identifiertext=The identifier of the cluster for which the snapshot was taken.
cluster_versiontextThe version ID of the Amazon Redshift engine that is running on the cluster.
current_backup_rate_in_mega_bytes_per_seconddouble precisionThe number of megabytes per second being transferred to the snapshot backup.
db_nametextThe name of the database that was created when the cluster was created.
elapsed_time_in_secondstextThe amount of time an in-progress snapshot backup has been running, or the amount of time it took a completed backup to finish.
encryptedbooleanIf true, the data in the snapshot is encrypted at rest.
encrypted_with_hsmbooleanA boolean that indicates whether the snapshot data is encrypted using the HSM keys of the source cluster.
engine_full_versiontextThe cluster version of the cluster used to create the snapshot.
enhanced_vpc_routingbooleanAn option that specifies whether to create the cluster with enhanced VPC routing enabled.
estimated_seconds_to_completiontextThe estimate of the time remaining before the snapshot backup will complete.
kms_key_idtextThe AWS KMS key ID of the encryption key that was used to encrypt data in the cluster from which the snapshot was taken.
maintenance_track_nametextThe name of the maintenance track for the snapshot.
manual_snapshot_remaining_daysbigintThe number of days until a manual snapshot will pass its retention period.
manual_snapshot_retention_periodbigintThe number of days that a manual snapshot is retained.
master_usernametextThe master user name for the cluster.
node_typetextThe node type of the nodes in the cluster.
number_of_nodesbigintThe number of nodes in the cluster.
owner_accounttext=The AWS customer account used to create or copy the snapshot.
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
portbigintThe port that the cluster is listening on.
regiontextThe AWS Region in which the resource is located.
restorable_node_typesjsonbThe list of node types that this cluster snapshot is able to restore into.
snapshot_create_timetimestamp with time zone=The time (in UTC format) when Amazon Redshift began the snapshot.
snapshot_identifiertext=The unique identifier of the cluster.
snapshot_retention_start_timetimestamp with time zoneA timestamp representing the start of the retention period for the snapshot.
snapshot_typetext=The snapshot type.
source_regiontextThe source region from which the snapshot was copied.
statustextThe snapshot status.
tagsjsonbA map of tags for the resource.
tags_srcjsonbThe list of tags for the cluster.
titletextTitle of the resource.
total_backup_size_in_mega_bytesdouble precisionThe size of the complete set of backup data that would be used to restore the cluster.
vpc_idtextThe VPC identifier of the cluster if the snapshot is from a cluster in a VPC.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- aws

You can pass the configuration to the command with the --config argument:

steampipe_export_aws --config '<your_config>' aws_redshift_snapshot