Amazon Redshift User's Guide

Amazon RedshiftManagement GuideAPI Version 2012-12-01

Database Audit LoggingYou can use the database audit logging feature to track information about authentication attempts,connections, disconnections, c

If you select a smaller number of days to retain snapshots in the destination region, any automatedsnapshots that were taken before the new retention

import com.amazonaws.services.redshift.model.DescribeClusterSnapshotsResult;import com.amazonaws.services.redshift.model.Snapshot;public class CreateA

DescribeClusterSnapshotsRequest request = new DescribeClusterSnapshots Request() .withEndTime(creationDate) .withCluster

Managing Snapshots Using the Amazon RedshiftCLI and APIYou can use the following Amazon Redshift CLI operations to manage snapshots.• authorize-snapsh

Amazon Redshift DatabaseEncryptionTopics• Overview (p. 98)• Hardware Security Modules (p. 99)• Rotating Encryption Keys (p. 105)OverviewAmazon Redshif

(SOX), the Health Insurance Portability and Accountability Act (HIPAA), and other such regulations provideguidelines for handling specific types of da

You can use the following procedures to configure hardware security modules by using the AWSManagement Console.Creating an HSM Connection1. Sign into

5. After the connection is created, you can create an HSM client certificate. If you want to create anHSM client certificate immediately after creatin

4. On the Create HSM Client Certificate page, type a name in the HSM Client Certificate Identifierbox to identify this client certificate.5. Click Nex

c. Paste the public key into the open file and save it with the file name displayed in step 1 from theconfirmation page. Make sure that you save the f

Amazon Redshift ClustersTopics• Overview (p. 5)• About Clusters and Nodes (p. 6)• Resizing a Cluster (p. 8)• Supported Platforms to Launch Your Cluste

Displaying the Public Key for an HSM Client Certificate1. Sign into the AWS Management Console and open the Amazon Redshift console at https://console

Configuring HSM Using the Amazon Redshift CLIand APIYou can use the following Amazon Redshift CLI operations to manage hardware security modules.• cre

2. In the navigation pane, click Clusters.3. In the list, click the cluster for which you want to rotate keys.4. Click Database, and then click Rotate

Purchasing Amazon RedshiftReserved NodesTopics• Overview (p. 107)• Purchasing a Reserved Node Offering with the Amazon Redshift Console (p. 108)• Purc

account will be billed for the up-front and recurring charges of the reserved nodes that you purchased.Amazon Redshift has a predefined list of the re

Listing Reserved Node OfferingsTo list reserved node offerings1. Sign into the AWS Management Console and open the Amazon Redshift console at https://

4. In the Purchase Reserved Nodes wizard, specify Node Type, Term, Offering Type, and Numberof Nodes.5. When the settings are as you want them, click

Purchasing a Reserved Node Offering Using theAWS SDK for JavaThe following example demonstrates how to use the AWS SDK for Java to do the following:•

public static String nodeTypeToPurchase = "dw.hs1.xlarge"; public static Double fixedPriceLimit = 10000.00; public static ArrayLis

} private static void purchaseReservedNodeOffer() throws IOException { if (matchingNodes.size() == 0) { return; } else

NoteAlthough the console displays this information in one field, it is two parameters in the AmazonRedshift API: ClusterVersion and ClusterRevisionNum

System.out.format("Offering Type: %s\n", node.getOfferingType()); System.out.format("Duration: %s\n", node.getDurat

Controlling Access to AmazonRedshift ResourcesYou can use AWS Identity and Access Management (IAM) to control which AWS users can create,configure, or

Using IAM Users, Groups, and Policies for ClusterManagementAmazon Redshift uses AWS Identity and Access Management (IAM) to control which users in you

To create the Administrators group1. Sign in to the AWS Management Console and open the IAM console at https://console.aws.amazon.com/iam/.2. In the n

"Resource": [ "arn:aws:redshift:us-east-1:123456789012:snapshot:my-cluster*" ] } ]}IAM Policy Action

• Restoring a cluster from a snapshot.• Revoking cluster snapshot access.Some Amazon Redshift actions, such as copying a cluster snapshot and restorin

Constructing an Amazon Redshift ARNYou can identify resources that are created in Amazon Web Services by a unique identifier called anAmazon Resource

The following table shows examples of ARNs for Amazon Redshift resources that have an AWS accountof 123456789012, that were created in the US East (No

Example 1: Allow an IAM user full access to all Amazon Redshift actions and resourcesThe following policy allows access to all Amazon Redshift actions

Example 3: Manage clustersThe following policy allows a user to create, delete, modify, and reboot all clusters, and then deniespermission to delete a

being sent back to the client applications. For more information about leader nodes and compute nodes,go to Data warehouse system architecture in the

Example 4: Allow a user to authorize and revoke snapshot accessThe following policy allows a user, for example UserA, to do the following:• Authorize

], "Effect": "Allow" } ]}Example 5: Allow a user permissions to copy a cluster snapshot and restore a clusterfrom a

Example 6: Grant permission to Amazon Redshift and to common actions and resourcesfor related AWS servicesThe following example policy allows access t

} ]}API Version 2012-12-01127Amazon Redshift Management GuideExample Policies for Amazon Redshift

Accessing Amazon RedshiftClusters and DatabasesThere are several management tools and interfaces you can use to create, manage, and delete AmazonRedsh

• Signing an HTTP Request (p. 131)• Setting Up the Amazon Redshift CLI (p. 134)Amazon Redshift supports several management interfaces that you can use

The AmazonRedshiftClient class defines methods that map to underlying Amazon Redshift QueryAPI actions. (These actions are described in the Amazon Red

2. Put the code that you want to run in a .java file, and save the file in the src folder. To illustrate theprocess, we use the code from Managing Clu

function includes the text of your request and your secret access key. The hash function returns a hashvalue that you include in the request as your s

&NodeType=dw.hs1.xlarge &Version=2012-12-01 &x-amz-algorithm=AWS4-HMAC-SHA256 &x-amz-credential=AKIAIOSFODNN7EXAMPLE/20121207

compute nodes, you purchase what are called reserved node offerings.You purchase one offering foreach compute node that you want to reserve.When you r

Authorization: AWS4-HMAC-SHA256 Credential=AKIAIOSFODNN7EXAMPLE/20121207/us-east-1/redshift/aws4_request, SignedHeaders=content-type;host;x-amz-date,

• Step 6: Delete Your Sample Cluster (p. 138)To help you get started using the command line interface, this section shows how to perform basicadminist

Parameter Value for This ExerciseParameter NameTopSecret1Master Passworddw1.xlarge or the node size that you want to use.For more information, see Abo

To allow network ingress to your cluster, type the following command:aws redshift authorize-cluster-security-group-ingress --cluster-security-group-na

Step 5: Create Tables, Upload Data, and Try Example QueriesFor information about creating tables, uploading data, and issuing queries, go to the Amazo

• Configure an ODBC Connection (p. 144)• Configure Security Options for Connections (p. 158)JDBC and ODBC Drivers for Amazon RedshiftTo work with data

Configure a JDBC ConnectionYou can use a JDBC connection to connect to your Amazon Redshift cluster from many third-party SQLclient tools.To do this,

jdbc:redshift://endpoint:port/databaseNoteA JDBC URL specified with the former format of jdbc:postgresql://endpoint:port/databasewill still work.Value

JDBC Driver Configuration OptionsTo control the behavior of the Amazon Redshift JDBC driver, you can append the configuration optionsdescribed in the

DescriptionDefaultValueMatchingPostgr-eSQL Op-tion Ex-ists?JDBC OptionThe amount of logging information output by the driver.By default, no logging is

You can get the dig utility as part of the BIND software download. For more information on BIND, go toBIND in the Internet Systems Consortium document

DescriptionDefaultValueMatchingPostgr-eSQL Op-tion Ex-ists?JDBC OptionThe user name to use to connect to the Amazon Red-shift server.YesUIDConfigure a

ValueFieldThe user name of a user account that has permission to connect to the database.This value is a database permission, not an Amazon Redshift p

Install and Configure the Amazon Redshift ODBC Driver on MicrosoftWindows Operating SystemsSystem RequirementsYou install the Amazon Redshift ODBC dri

3. Select the System DSN tab if you want to configure the driver for all users on the computer, or theUser DSN tab if you want to configure the driver

• Retrieve Entire Result Into Memory. Select this option if you want query results to be returned allat once to the SQL client tool or application. Th

• unixODBC 2.3.0 or later. For more information about the unixODBC driver manager and links todownload it, go to the unixODBC website.• An Amazon Reds

zypper install AmazonRedshiftODBC-1.x.x.xxxx-x.x86_64.rpmImportantWhen you have finished installing the drivers, configure them for use on your system

Configure the ODBC Driver on Linux and Mac OS X Operating SystemsOn Linux and Mac OS X operating systems, you use an ODBC driver manager to configure

Database=database_namelocale=localeThe following example shows the configuration for odbc.ini on Linux operating systems:[ODBC Data Sources]Amazon_Red

Use the following format on Linux operating systems:[ODBC Drivers]driver_name=Installed...[driver_name]Description=driver_descriptionDriver=path/drive

For more information about Amazon Virtual Private Cloud (Amazon VPC), go to the Amazon VPC productdetail page.Choose a PlatformYour AWS account is cap

Configuring Environment Variables for Driver Configuration FilesIn order for the Amazon Redshift ODBC driver to function properly, you need to set a n

ODBC Driver Configuration OptionsYou can use the configuration options described in the following table to control the behavior of theAmazon Redshift

DescriptionDefaultValueMatchingPostgr-eSQL op-tion ex-ists?ODBC OptionThe number of seconds of inactivity before the driversends a TCP keepalives pack

DescriptionDefaultValueMatchingPostgr-eSQL op-tion ex-ists?ODBC OptionWhen this option is enabled (1), the driver returns TEXTcolumns as LONG VARCHAR

DescriptionDefaultValueMatchingPostgr-eSQL op-tion ex-ists?ODBC OptionWhen this option is enabled (1), the driver returns dataas Unicode character typ

Amazon Redshift supports the Elliptic Curve Diffie—Hellman Ephemeral (ECDHE) key agreement protocol.With ECDHE, the client and server each have an ell

Resultrequire_SSL(server)sslmode (cli-ent)The connection is encrypted and the server certificate is verified.falseverify-caConnect Using the Server Ce

${JAVA_HOME}/bin/keytool -keystore <keystore_name> -alias <alias> -import -file<certificate_filename>Where <keystore_name> is

The following is an example connection string for SQL Workbench/J:jdbc:redshift://examplecluster.abc123xyz789.us-west-2.redshift.amazon aws.com:5439/d

2. Go to the Installing and starting SQL Workbench/J page. Follow the instructions for installing SQLWorkbench/J on your system.NoteSQL Workbench/J re

• EU (Ireland) region: 22:00-06:00 UTCWhile Amazon Redshift is performing maintenance, it terminates any queries or other operations that arein progre

If the Please select one driver dialog box displays, select com.amazon.redshift.jdbc4.Driver orcom.amazon.redshift.jdbc41.Driver and click OK. SQL Wor

Test the SQL Workbench/J ConnectionAfter you configure your JDBC or ODBC connection, you can test the connection by running an examplequery.1. If you

Connect to Your Cluster by Using the psql ToolAfter you create an Amazon Redshift cluster, you can use psql, a terminal-based front end fromPostgreSQL

3. At a command prompt, specify the connection information by using either command line parametersor a connection information string. To use parameter

For more information about connection information string parameters, see http://www.postgresql.org/docs/8.4/static/libpq-connect.html.To connect by us

4. At the psql password prompt, enter the password for the <userid> user.You are connected to the cluster, and you can interactively enter comma

Example : Connect to a Cluster by Using JavaThe following example connects to a cluster and runs a sample query that returns system tables. It is notn

//Retrieve two columns. String catalog = rs.getString("table_catalog"); String name = rs.getString(&

Example Connect to a Cluster by Using .NETThe following example connects to a cluster and runs a sample query that returns system tables. It doesnot s

// Make a connection using the psqlODBC provider. OdbcConnection conn = new OdbcConnection(connString);

You might rename a cluster if you want to change the cluster to which your applications connect withouthaving to change the endpoint in those applicat

Your client connection to the database appears to hang or timeout when running long queries, such asa COPY command. In this case, you might observe th

sysctl net.inet.tcp.keepidlesysctl net.inet.tcp.keepintvlsysctl net.inet.tcp.keepinitsysctl net.inet.tcp.always_keepaliveTo change DSN timeout setting

• If you created your Amazon Redshift cluster in a VPC, you need to add your client CIDR/IP addressto the VPC security group in Amazon VPC. For more i

Review the MTU value for the Ethernet adapter in the output. If the value is not 1500, run the followingcommand to set it:netsh interface ipv4 set sub

SELECT ut.userid, trim(u.usename), COUNT(text) as count FROM stl_utilitytext ut JOIN pg_user u ON ut.userid = u.usesysid WHERE ut.text LIKE 'SAVE

Monitoring Amazon RedshiftCluster PerformanceTopics• Overview (p. 179)• Summary of Amazon Redshift Performance Data (p. 180)• Working with Performance

(as compared to Bytes/s in Amazon CloudWatch), which is a more relevant unit for the typical storagespace of a node.• Performance data for the nodes o

DescriptionMetricThe number of database connections to a cluster.Units: CountDimensions: ClusterIdentifierDatabaseConnec-tionsIndicates the health of

DescriptionMetricThe percent of disk space used.Units: PercentDimensions: NodeID, ClusterIdentifierPercenta-geDiskSpaceUsedThe average number of disk

The following table describes different aspects of query and load data you can access in the AmazonRedshift console.DescriptionQuery/Load DataA list o

Managing Clusters Using the ConsoleThis topic explains how to work with clusters in the Amazon Redshift console.You can use the Clusterssection in the

Health of the cluster (e.g. healthy), whether the cluster is undergoing maintenance, and count of recentevents. From the dashboard, select a cluster t

By default, the performance view displays cluster performance over the past hour. If you need to finetune the view you have filters that you can use a

The following example shows the NetworkReceiveThroughput for a cluster with two nodes. It showsa line for the leader and two compute nodes. Note that

Use the button bar, shown following, to refresh the data in the table, to configure the columns that appearin the table, or to open the Amazon Redshif

3. In the Cluster list, click the cluster you want to open.4. Click the Queries tab.5. Do one of the following:• In the list, select the query or quer

Analyzing Query ExecutionThe Query Execution Details section of the Query view provides information about the way the querywas processed.This section

The Avg statistic shows the average execution time for the step across data slices, and the percentageof the total query runtime that represents. The

The information on the Plan tab is analogous to running the EXPLAIN command in the database. TheEXPLAIN command examines your query text, and returns

For more information about the difference between the explain plan and system views and logs, go toMapping the Query Plan to System Views in the Amazo

5. In the query list, find the query you want to work with, and click the query ID in the Query column.In the following example, the queries are sorte

Amazon Redshift: Management GuideCopyright © 2015 Amazon Web Services, Inc. and/or its affiliates. All rights reserved.The following are trademarks of

Creating a ClusterBefore you create a cluster, review the information in the Overview (p. 5) of this section.To create a cluster1. Sign in to the AWS

Viewing Cluster Metrics During Load OperationsWhen you view cluster performance during load operations, you can identify queries that are consumingres

6. In the new Query tab that is opened, you can view the details of the load operation.At this point, you can work with the Query tab as shown in View

3. In the Cluster list, select the cluster for which you want to view cluster performance during queryexecution.4. Select the Events+Alarms tab.5. Cli

7. In the list of alarms, find your new alarm.You may need to wait a few moments as sufficient data is collected to determine the state of thealarm as

• First, you specify the metric dimension to work with. A dimension is a name-value pair that helps youto uniquely identify a metric. The dimensions f

Amazon Redshift EventsTopics• Overview (p. 199)• Viewing Events Using the Console (p. 199)• Viewing Events Using the AWS SDK for Java (p. 201)• View

An event source type indicates what the event was about. The following source types are possible:Cluster, Cluster Parameter Group, Cluster Security Gr

Viewing Events Using the AWS SDK for JavaThe following example lists the events for a specified cluster and specified event source type.The examplesho

} } while (marker != null); } static void printEvent(Event event) { if (event == null) {

text message, or a call to an HTTP endpoint. For example, all regions support email notifications, butSMS notifications can only be created in the US

• It must be from 8 to 64 characters in length.• It must contain at least one uppercase letter.• It must contain at least one lowercase letter.• It mu

• Subscription specifies a source type of cluster, a source ID of my-cluster-1, a category of Monitoring,and a severity of ERROR.The subscription will

DescriptionEventSeverityEvent IDAmazonRedshiftCategoryYour Amazon Redshift cluster [cluster name] wasmodified to use parameter group [parameter groupn

DescriptionEventSeverityEvent IDAmazonRedshiftCategoryThe resize for your Amazon Redshift cluster[cluster name] is in progress.Your cluster is inread-

DescriptionEventSeverityEvent IDAmazonRedshiftCategoryThe Amazon Redshift cluster [cluster name] can-not be created using EIP [IP address]. The EIPcan

DescriptionEventSeverityEvent IDAmazonRedshiftCategoryCluster security group [group name]was created.INFOREDSHIFT-EVENT-4002SecurityCluster security g

DescriptionEvent Sever-ityEvent IDAmazon Red-shift CategoryThe user snapshot [snapshot name] for yourAmazon Redshift cluster [cluster name] failedat [

ii. Select an event severity from the Severity dropdown menu. If you select Any, events withseverities of either INFO or ERROR are published. If you s

e. Use one of three tabs to specify the Amazon SNS topic the subscription will use to publishevents.i. To select an existing Amazon SNS topic by from

5. To create the subscription, click Create.To delete the definition without creating a subscription, clickCancel. To return to the subscription setti

a. Use the Subscription Settings pane to change the event filter criteria. As you select the criteria,the Subscribed Events list displays the Amazon R

5. On the Additional Configuration page, specify values for the following options, and then clickContinue.a. Under Provide the optional additional con

Removing a Source Identifier from an Amazon Redshift EventNotification SubscriptionYou can remove a source identifier (the Amazon Redshift source gene

Database Audit LoggingTopics• Overview (p. 215)• Amazon Redshift Logs (p. 215)• Enabling Logging (p. 217)• Managing Log Files (p. 218)• Troubleshootin

The user activity log is useful primarily for troubleshooting purposes. It tracks information about the typesof queries that both the users and the sy

DescriptionColumn nameAction that occurred. Valid values:• Alter• Create• Drop• RenameactionIf true (1), indicates that the user has create database p

The connection log, user log, and user activity log are enabled together by using the AWS ManagementConsole, the Amazon Redshift API Reference, or the

Account IDRegion361669875840Asia Pacific (Singapore) region762762565011Asia Pacific (Sydney) regionThe bucket policy uses the following format, where

"Action": "s3:GetBucketAcl", "Resource": "arn:aws:s3:::AuditLogs" } ]}For more information about creatin

Using AWS CloudTrail for Amazon RedshiftThis service supports AWS CloudTrail, which is a service that records AWS calls for your AWS accountand delive

7. Optionally, in the S3 Key Prefix box, type a prefix to add to the S3 bucket.8. Click Save.After you configure audit logging, the Cluster details pa

Disabling Audit Logging Using the Console1. Sign into the AWS Management Console and open the Amazon Redshift console at https://console.aws.amazon.co

your own CMK gives you more flexibility, including the ability to create, rotate, disable,define access controls for, and audit the encryption keys us

Tutorial: Resizing Clusters inAmazon RedshiftTopics• Overview (p. 224)• Resize Operation Overview (p. 224)• Snapshot, Restore, and Resize Operation Ov

the amount of data and the number of nodes in the smaller cluster. It can take anywhere from a coupleof hours to a couple of days.When you start the r

4. Resize the target cluster. Select the new node type, number of nodes, and other settings for the targetcluster.5. Review the loads from your ETL pr

PrerequisitesBefore you start this tutorial, make sure that you have the following prerequisites:• A sample cluster. In this example, you’ll start wit

Step 2: Delete the Sample ClusterAfter you are sure that you no longer need the sample cluster, you can delete it. In a productionenvironment, whether

• Step 2: Restore the Snapshot into the Target Cluster (p. 230)• Step 3: Verify Data in the Target Cluster (p. 231)• Step 4: Resize the Target Cluster

5. In the navigation pane, click Snapshots and verify that a new manual snapshot is being created. Thesnapshot status will be creating.Step 2: Restore

Snapshot Identifier: check the snapshot name, examplecluster-source.•• Cluster Identifier: type examplecluster-target.• Port: leave the port number as

FROM pg_table_def WHERE tablename = 'sales'; -- Find total sales on a given calendar date.SELECT sum(qtysold) FROM sales, date WHERE

5. Click Resize.6. Click Status, and review the resize status information to see the resize progress.Step 5: Copy Post-Snapshot Data from the Sourceto

Cluster Subnet GroupSelect the Amazon Redshift subnet group in which to launch the cluster.NoteThis option is available only for clusters in a VPC.Pub

First, you’ll use the following COPY statements to load new data from Amazon S3 to the tables in yourAmazon Redshift TICKIT database in the target clu

2. In the navigation pane, click Clusters, and then click the cluster to open. If you are using the samecluster from this tutorial, click exampleclust

2. In the navigation pane, click Clusters, and then click the cluster to open. If you are using the samecluster names from this tutorial, click exampl

Limits in Amazon RedshiftTopics• Quotas and Limits (p. 237)• Naming Constraints (p. 238)Quotas and LimitsAmazon Redshift has quotas that limit the num

Naming ConstraintsThe following table describes naming constraints within Amazon Redshift.• A cluster identifier must contain only lowercase character

• A cluster security group name must contain no more than255 alphanumeric characters or hyphens.• It must contain only lowercase characters.• It must

Tagging Resources in AmazonRedshiftTopics• Tagging Overview (p. 240)• Managing Resource Tags Using the Console (p. 241)• Managing Tags Using the Amazo

• EC2 security group• HSM connection• HSM client certificate• Parameter group• Snapshot• Subnet groupTagging RequirementsTags have the following requi

After you add key pairs to a resource, they display in the Applied Tags section; this is the tag set for theresource.You can modify a tag value, but y

DescriptionResourceIn the left navigation pane, click Security. On the HSM Certificates tab, select acertificate from the list. Then click Manage Tags

d. Under Optionally create a basic alarm for this cluster, configure the following options, andthen click Continue:Create CloudWatch AlarmClick Yes if

• CreateCluster• CreateClusterParameterGroup• CreateClusterSecurityGroup• CreateClusterSnapshot• CreateClusterSubnetGroup• CreateHsmClientCertificate•

Document HistoryThe following table describes the important changes to the Amazon Redshift Management Guide.Latest documentation update: April 30, 201

Release DateDescriptionChangeJanuary 26, 2015Added a troubleshooting topic about an issue wherequeries fail to complete in the SQL client tool and the

Release DateDescriptionChangeOctober 30, 2014Added links to the Microsoft Visual C++ 2010 Redistribut-able Packages that are required for Amazon Redsh

Release DateDescriptionChangeApril 18, 2014Added statement about Amazon Redshift support for theElliptic curve Diffie—Hellman (ECDH) key agreementprot

Release DateDescriptionChangeDecember 6, 2013Added information about the new user activity log and theenable_user_activity_logging database parameterf

7. After you initiate the creation process, click Close.The cluster might take several minutes to beready to use.You can monitor the status of the ope

If you decrease the automated snapshot retention period, existing automated snapshots whose settingsfall outside of the new retention period are delet

Shutting Down or Deleting a ClusterIf you no longer need your cluster, you can shut it down or delete it. If you plan to provision a new clusterwith t

After you initiate the shutdown of the cluster, it can take several minutes for the cluster to be shutdown.You can monitor the status in the cluster l

Table of ContentsWhat Is Amazon Redshift? ...

4. In the Reboot Clusters window, confirm that you want to reboot this cluster, and then click Reboot.It can take several minutes for the cluster to b

3. On the Configuration tab of the cluster details page, click Cluster and then click Resize.4. In the Resize Clusters window, configure the resize pa

Getting Information About Cluster ConfigurationTo get cluster configuration details1. Sign in to the AWS Management Console and open the Amazon Redshi

3. Click the Status tab.The status summary page is displayed as shown in the following screenshot.Taking a Snapshot of a ClusterYou can take a snapsho

4. In the Create Snapshot dialog box, do the following:a. In the Cluster Identifier box, click the cluster that you want to take a snapshot of.b. In t

5. In the Edit Alarm window, edit any values that you want to change, such as the percentage orminutes.6. To change the Amazon SNS topic that the alar

For step-by-step instructions to run the following example, see Running Java Examples for AmazonRedshift Using Eclipse (p. 130).You need to update the

ModifyClusterRequest request = new ModifyClusterRequest() .withClusterIdentifier(clusterIdentifier) .withPreferredMaintenance

You can use the following Amazon Redshift APIs to manage clusters.• CreateCluster• DeleteCluster• DescribeClusters• DescribeClusterVersions• DescribeO

If you have a publicly accessible cluster in a VPC, and you want to connect to it by using the privateIP address from within the VPC, you must set the

Configuring the wlm_json_configuration Parameter ... 69Managing Parameter Groups Using the Co

Started Guide. Make a note of the VPC identifier, subnet, and subnet's availability zone.You willneed this information when you launch your clust

Now you are ready to use the cluster.You can follow the Getting Started steps to test the cluster byuploading sample data and trying example queries.M

all service levels, you might want to create separate VPC security groups, one for each service level.Youcan then associate these VPC security groups

(highlighted), you can edit or delete it.The details of the selected security group are shown under ClusterSubnet Group Details.Creating a Cluster Sub

5. Click Yes, Create.The new group will be displayed in the list of cluster subnet groups.Modifying a Cluster Subnet GroupTo modify a cluster subnet g

Do this...To...In the lists of subnets in use for the group, click Remove next tothe subnet to remove.Click Save.Remove a subnet from thegroup.Deletin

• Creating a cluster subnet group.• Listing metadata about a cluster subnet group.• Modifying a cluster subnet group.For step-by-step instructions to

DescribeClusterSubnetGroupsRequest request1 = new DescribeClusterSubnet GroupsRequest() .withClusterSubnetGroupName(clusterSubnetGr

subnet.getSubnetAvailabilityZone().getName(), subnet.getSub netStatus()); } }}Manage Cluster Subnet Groups Using Amazon

Amazon Redshift Cluster SecurityGroupsWhen you provision an Amazon Redshift cluster, it is locked down by default so nobody has access toit. To grant

Configuring Connections in Amazon Redshift ... 138Connecting to Clusters From Client To

might want to create separate cluster security groups, one for each company.You can add rules in eachcluster security group to identify the Amazon EC2

You cannot delete the default cluster security group, but you can modify it by authorizing or revokingingress access.To add or modify the rules associ

Tagging a Cluster Security GroupTo tag a cluster security group1. Sign into the AWS Management Console and open the Amazon Redshift console at https:/

b. Add a tag.• In the Add Tags section, type a key/value pair for the tag.• Click Apply Changes.For more information about tagging an Amazon Redshift

5. In the Add Connection Type dialog, do one of the following:a. Add an ingress rule based on CIDR/IP.• In the Connection Type box, click CIDR/IP.• In

Revoking Ingress Rules for a Cluster SecurityGroupTo revoke ingress rules for a cluster security group1. Sign into the AWS Management Console and open

a. Remove a tag.• In the Applied Tags section, select Delete next to the tag you want to remove.• Click Apply Changes.b. Add a tag.NoteTagging an EC2

For more information about tagging an Amazon Redshift resource, see How to Manage Tags in theAmazon Redshift Console (p. 243).Deleting a Cluster Secur

4. In the Delete Cluster Security Groups dialog box, click Continue.If the cluster security group is used by a cluster, you will not be able to delete

By default, when a new cluster security group is created, it has no ingress rules.This example modifiesa new cluster security group by adding two ingr

Step 7: Delete the Source Cluster ... 235Step 8: Clean Up Your Environme

.withClusterSecurityGroupName(clusterSecurityGroupName) .withCIDRIP("192.168.40.5/32"); ClusterSecurityGroup r

null."); return; } System.out.println("\nPrinting security group results:"); for (ClusterSecurityGroup

Amazon Redshift ParameterGroupsTopics• Overview (p. 56)• Configuring the Maximum Size of a Cursor Result Set (p. 57)• Configuring Workload Management

ValueParameter Name0extra_float_digitsdefaultmax_cursor_result_set_sizedefaultquery_groupfalserequire_ssl$user, publicsearch_path0statement_timeout[{&

number of cursors for your cluster, and to achieve the ideal balance between maximum result set sizeand concurrency.ImportantBefore you modify this pa

of range for the cluster. If you change the parameter value and want to return to the default setting, resetthe parameter to 0.After you change this v

ValueJSON PropertyConsole FieldThenum-berofquer-iesthatcanruncon-cur-rentlyinaqueue.Whenaqueuereachesthecon-cur-rencylevel,anysub-sequentquer-ieswaiti

ValueJSON PropertyConsole FieldAcomma-sep-ar-atedlistofusergroupnames.Whenmem-bersoftheusergrouprunquer-iesinthedata-base,theirquer-iesareroutedtotheq

ValueJSON PropertyConsole Fielduser_group_wild_cardUser Group WildcardAPI Version 2012-12-0162Amazon Redshift Management GuideConfiguring Workload Man

ValueJSON PropertyConsole FieldABooleanvaluethatin-dic-ateswheth-ertoen-ablewild-cardsforusergroups.Ifthisis0,wild-cardsaredis-abled;ifthisis1,wild-ca

What Is Amazon Redshift?Welcome to the Amazon Redshift Cluster Management Guide. Amazon Redshift is a fully managed,petabyte-scale data warehouse serv

ValueJSON PropertyConsole Fieldrun-ningquer-ies.Acomma-sep-ar-atedlistofquerygroups.Whenmem-bersofthequerygrouprunquer-iesinthedata-base,theirquer-ies

ValueJSON PropertyConsole Fieldquery_group_wild_cardQuery Group WildcardAPI Version 2012-12-0165Amazon Redshift Management GuideConfiguring Workload M

ValueJSON PropertyConsole FieldABooleanvaluethatin-dic-ateswheth-ertoen-ablewild-cardsforquerygroups.Ifthisis0,wild-cardsaredis-abled;ifthisis1,wild-c

ValueJSON PropertyConsole Fieldrun-ningquer-ies.Themax-im-umtime,inmil-li-seconds,quer-iescanrunbe-forebe-ingcan-celled.max_execution_timeTimeoutAPI V

ValueJSON PropertyConsole FieldTheamountofmemorytoal-loc-atetothequeue.Thetotalamountofmemoryacrossallqueuesmustequal100per-cent,soifyousetthisprop-er

user groups or query groups for the default queue.The default queue must always be the last queue inthe WLM configuration.As with other parameters, yo

"q2_second_property_name":"q2_second_property_value", ... } ... ]&

Rules for Configuring WLM by Using the AWS CLI in the Command Lineon the Linux and Mac OS X Operating Systems• The entire JSON structure must be enclo

NoteFor readability purposes, the example is displayed on two lines, but in the actual AWS CLI thiswould be one line.aws redshift modify-cluster-param

You can create, modify, and delete parameter groups by using the Amazon Redshift console. All of thetasks start from the parameter group list.In the p

• Amazon Redshift API Reference• SDK References in Tools for Amazon Web ServicesAmazon Redshift Management OverviewThe Amazon Redshift service manages

Modifying a Parameter GroupIf the parameter group you are modifying is associated with a cluster, then the changes you make to theparameter group appl

5. To edit the workload management configuration, in the parameter group list, click the row of theparameter group you want to modify, and then click

Do this...To...Use the Up and Down buttons to change the or-der that queries are assigned to queues basedon users and query groups. For more informati

Associating a Parameter Group with a ClusterEach cluster you provision has one parameter group associated with it.You can associate a parametergroup w

Because this example replaces the WLM configuration, this JSON configuration also defines the defaultqueue with no specific user group or query group.

private static void describeClusterParameterGroups() { DescribeClusterParameterGroupsResult result = client.describeClusterPara me

} } private static void describeClusterParameters(String parameterGroupName) { DescribeClusterParametersRequest request = new Des

• CreateClusterParameterGroup• DeleteClusterParameterGroup• DescribeClusterParameters• DescribeClusterParameterGroups• DescribeDefaultClusterParameter

Amazon Redshift SnapshotsTopics• Overview (p. 82)• Managing Snapshots Using the Console (p. 86)• Managing Snapshots Using the AWS SDK for Java (p. 94

To disable automated snapshots, set the retention period to zero. If you disable automated snapshots,Amazon Redshift stops taking snapshots and delete

AWS Accounts and IAM CredentialsBy default, an Amazon Redshift cluster is only accessible to the AWS account that creates the cluster.The cluster is l

Restoring a Cluster from a SnapshotA snapshot contains data from any databases that are running on your cluster, and also information aboutyour cluste

"Effect":"Allow", "Action":[ "redshift:AuthorizeSnapshotAccess", "redshift:R

]}• Once access to a snapshot has been revoked from an AWS account, no users in that account canaccess the snapshot, even if they have IAM policies

Creating a Manual SnapshotYou can create a manual snapshot of a cluster from the snapshots list as follows. Or, you can take asnapshot of a cluster in

Deleting a Manual SnapshotTo delete a manual snapshot1. Sign into the AWS Management Console and open the Amazon Redshift console at https://console.a

• In the Time Range box, click a time range that will narrow your search appropriately.• In the Type box, click automated.• In the Cluster box, click

5. Click Restore From Snapshot.6. In the Restore Cluster from Snapshot dialog box, do the following:a. In the Cluster Identifier box, type a cluster i

The following is an example of restoring a snapshot into a cluster that uses the EC2-VPC platform.The following is an example of restoring a snapshot

• In the Time Range box, click a time range that will narrow your search appropriately.• In the Type box, click manual or automated.• In the Cluster b

5. Click Save Configuration.After you configure cross-region snapshot copy, the Cluster detail page updates with the information inthe Backup, Mainten