steampipe plugin install aws

Table: aws_redshift_cluster - Query AWS Redshift Clusters using SQL

The AWS Redshift Cluster is a fully managed, petabyte-scale data warehouse service in the cloud. It enables users to analyze all their data using their existing business intelligence tools by leveraging SQL and existing business intelligence tools. It provides a cost-effective solution for analyzing large-scale data sets as it uses columnar storage technology and parallel query execution.

Table Usage Guide

The aws_redshift_cluster table in Steampipe provides you with information about Redshift clusters within Amazon Web Services. This table allows you, as a DevOps engineer, to query cluster-specific details, such as cluster status, node type, number of nodes, and associated metadata. You can utilize this table to gather insights on clusters, including their availability, performance, and security settings. The schema outlines the various attributes of the Redshift cluster for you, including the cluster identifier, creation time, database name, and associated tags.

Examples

Basic info

Analyze the settings to understand the different types of nodes and their geographical distribution in your AWS Redshift clusters. This can help optimize resource allocation and improve data management across different regions.

select
cluster_identifier,
arn,
node_type,
region
from
aws_redshift_cluster;
select
cluster_identifier,
arn,
node_type,
region
from
aws_redshift_cluster;

List clusters that are publicly accessible

Determine the areas in which your clusters are publicly accessible, enabling you to identify potential security risks and take necessary precautions.

select
cluster_identifier,
node_type,
number_of_nodes,
publicly_accessible
from
aws_redshift_cluster
where
publicly_accessible;
select
cluster_identifier,
node_type,
number_of_nodes,
publicly_accessible
from
aws_redshift_cluster
where
publicly_accessible = 1;

List clusters that are not in a VPC

Discover the segments that are not part of any Virtual Private Cloud (VPC) in your AWS Redshift clusters. This can be useful in identifying potential security risks or compliance issues, as clusters not in a VPC may be more exposed to external threats.

select
cluster_identifier,
node_type,
number_of_nodes,
vpc_id
from
aws_redshift_cluster
where
vpc_id is null;
select
cluster_identifier,
node_type,
number_of_nodes,
vpc_id
from
aws_redshift_cluster
where
vpc_id is null;

List clusters whose storage is not encrypted

Identify the instances where your clusters' storage is not encrypted. This can help enhance your data security by pinpointing areas that need encryption.

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

Get logging status for each cluster

Determine the logging status for each cluster in your AWS Redshift environment to ensure proper logging practices are being followed for data security and compliance purposes.

select
cluster_identifier,
logging_status -> 'LoggingEnabled' as LoggingEnabled
from
aws_redshift_cluster
select
cluster_identifier,
json_extract(logging_status, '$.LoggingEnabled') as LoggingEnabled
from
aws_redshift_cluster

Schema for aws_redshift_cluster

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
account_idtextThe AWS Account ID in which the resource is located.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
allow_version_upgradebooleanA boolean value that, if true, indicates that major version upgrades will be applied automatically to the cluster during the maintenance window.
arntextThe Amazon Resource Name (ARN) specifying the cluster.
automated_snapshot_retention_periodbigintThe number of days that automatic cluster snapshots are retained.
availability_zonetextThe name of the Availability Zone in which the cluster is located.
availability_zone_relocation_statustextDescribes the status of the Availability Zone relocation operation.
cluster_availability_statustextThe availability status of the cluster for queries.
cluster_create_timetimestamp with time zoneThe date and time that the cluster was created.
cluster_identifiertext=The unique identifier of the cluster.
cluster_namespace_arntextThe namespace Amazon Resource Name (ARN) of the cluster.
cluster_nodesjsonbThe nodes in the cluster.
cluster_parameter_groupsjsonbThe list of cluster parameter groups that are associated with this cluster. Each parameter group in the list is returned with its status.
cluster_public_keytextThe public key for the cluster.
cluster_revision_numbertextThe specific revision number of the database in the cluster.
cluster_security_groupsjsonbA list of cluster security group that are associated with the cluster. Each security group is represented by an element that contains ClusterSecurityGroup.Name and ClusterSecurityGroup.Status subelements. Cluster security groups are used when the cluster is not created in an Amazon Virtual Private Cloud (VPC). Clusters that are created in a VPC use VPC security groups, which are listed by the VpcSecurityGroups parameter.
cluster_snapshot_copy_statusjsonbA value that returns the destination region and retention period that are configured for cross-region snapshot copy.
cluster_statustextThe current state of the cluster.
cluster_subnet_group_nametextThe name of the subnet group that is associated with the cluster. This parameter is valid only when the cluster is in a VPC.
cluster_versiontextThe version ID of the Amazon Redshift engine that is running on the cluster.
data_transfer_progressjsonbDescribes the status of a cluster while it is in the process of resizing with an incremental resize.
db_nametextThe name of the initial database that was created when the cluster was created. This same name is returned for the life of the cluster. If an initial database was not specified, a database named devdev was created by default.
deferred_maintenance_windowsjsonbDescribes a group of DeferredMaintenanceWindow objects.
elastic_ip_statusjsonbThe status of the elastic IP (EIP) address.
elastic_resize_number_of_node_optionstextThe number of nodes that you can resize the cluster to with the elastic resize method.
encryptedbooleanA boolean value that, if true, indicates that data in the cluster is encrypted at rest.
endpointjsonbThe connection endpoint.
enhanced_vpc_routingbooleanAn 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. If this option is true, enhanced VPC routing is enabled.
expected_next_snapshot_schedule_timetimestamp with time zoneThe date and time when the next snapshot is expected to be taken for clusters with a valid snapshot schedule and backups enabled.
expected_next_snapshot_schedule_time_statustextThe status of next expected snapshot for clusters having a valid snapshot schedule and backups enabled.
hsm_statusjsonbA value that reports whether the Amazon Redshift cluster has finished applying any hardware security module (HSM) settings changes specified in a modify cluster command.
iam_rolesjsonbA list of AWS Identity and Access Management (IAM) roles that can be used by the cluster to access other AWS services.
kms_key_idtextThe AWS Key Management Service (AWS KMS) key ID of the encryption key used to encrypt data in the cluster.
logging_statusjsonbDescribes the status of logging for a cluster.
maintenance_track_nametextThe name of the maintenance track for the cluster.
manual_snapshot_retention_periodbigintThe default number of days to retain a manual snapshot. If the value is -1, the snapshot is retained indefinitely. This setting doesn't change the retention period of existing snapshots. The value must be either -1 or an integer between 1 and 3,653.
master_usernametextThe master user name for the cluster. This name is used to connect to the database that is specified in the DBName parameter.
modify_statustextThe status of a modify operation, if any, initiated for the cluster.
next_maintenance_window_start_timetimestamp with time zoneThe date and time in UTC when system maintenance can begin.
node_typetextThe node type for the nodes in the cluster.
number_of_nodesbigintThe number of compute nodes in the cluster.
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
pending_actionsjsonbCluster operations that are waiting to be started.
pending_modified_valuesjsonbA value that, if present, indicates that changes to the cluster are pending. Specific pending changes are identified by subelements.
preferred_maintenance_windowtextThe weekly time range, in Universal Coordinated Time (UTC), during which system maintenance can occur.
publicly_accessiblebooleanA boolean value that, if true, indicates that the cluster can be accessed from a public network.
regiontextThe AWS Region in which the resource is located.
resize_infojsonbDescribes a resize operation.
restore_statusjsonbA value that describes the status of a cluster restore action. This parameter returns null if the cluster was not created by restoring a snapshot.
scheduled_actionsjsonbA list of scheduled actions for specified cluster.
snapshot_schedule_identifiertextA unique identifier for the cluster snapshot schedule.
snapshot_schedule_statetextThe current state of the cluster snapshot schedule.
tagsjsonbA map of tags for the resource.
tags_srcjsonbThe list of tags for the cluster.
titletextTitle of the resource.
vpc_idtextThe identifier of the VPC the cluster is in, if the cluster is in a VPC.
vpc_security_groupsjsonbA list of Amazon Virtual Private Cloud (Amazon VPC) security groups that are associated with the cluster. This parameter is returned only if the cluster is 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_cluster