Amazon Redshift Management Guide API Version 2012-12-01
Amazon Redshift Management Guide Amazon Redshift: Management Guide Copyright © 2015 Amazon Web Services, Inc. and/or its affiliates. All rights reserved. The following are trademarks of Amazon Web Services, Inc.
Amazon Redshift Management Guide Table of Contents What Is Amazon Redshift? .............................................................................................................. 1 Are You a First-Time Amazon Redshift User? ............................................................................. 1 Amazon Redshift Management Overview .................................................................................. 2 Cluster Management ...........................................................
Amazon Redshift Management Guide Configuring the wlm_json_configuration Parameter ............................................................ 69 Managing Parameter Groups Using the Console ....................................................................... 72 Creating a Parameter Group .......................................................................................... 73 Modifying a Parameter Group ........................................................................................
Amazon Redshift Management Guide Configuring Connections in Amazon Redshift .................................................................. Connecting to Clusters From Client Tools and Code ......................................................... Troubleshooting Connection Issues in Amazon Redshift .................................................... Monitoring Cluster Performance .................................................................................................... Overview .............
Amazon Redshift Management Guide Step 7: Delete the Source Cluster ................................................................................. Step 8: Clean Up Your Environment ............................................................................... Limits ....................................................................................................................................... Quotas and Limits .......................................................................................
Amazon Redshift Management Guide Are You a First-Time Amazon Redshift User? What Is Amazon Redshift? Welcome to the Amazon Redshift Cluster Management Guide. Amazon Redshift is a fully managed, petabyte-scale data warehouse service in the cloud. You can start with just a few hundred gigabytes of data and scale to a petabyte or more. This enables you to use your data to acquire new insights for your business and customers.
Amazon Redshift Management Guide Amazon Redshift Management Overview • Amazon Redshift API Reference • SDK References in Tools for Amazon Web Services Amazon Redshift Management Overview The Amazon Redshift service manages all of the work of setting up, operating, and scaling a data warehouse. These tasks include provisioning capacity, monitoring and backing up the cluster, and applying patches and upgrades to the Amazon Redshift engine. Topics • Cluster Management (p. 2) • Cluster Access and Security (p.
Amazon Redshift Management Guide Monitoring Clusters AWS Accounts and IAM Credentials By default, an Amazon Redshift cluster is only accessible to the AWS account that creates the cluster. The cluster is locked down so that no one else has access. Within your AWS account, you use the AWS Identity and Access Management (IAM) service to create user accounts and manage permissions for those accounts to control cluster operations. For more information, see Controlling Access to Amazon Redshift Resources (p.
Amazon Redshift Management Guide Databases Database Audit Logging You can use the database audit logging feature to track information about authentication attempts, connections, disconnections, changes to database user definitions, and queries run in the database. This information is useful for security and troubleshooting purposes in Amazon Redshift. The logs are stored in Amazon S3 buckets. For more information, see Database Audit Logging (p. 215).
Amazon Redshift Management Guide Overview Amazon Redshift Clusters Topics • Overview (p. 5) • About Clusters and Nodes (p. 6) • Resizing a Cluster (p. 8) • Supported Platforms to Launch Your Cluster (p. 9) • Regions and Availability Zone Considerations (p. 10) • Maintenance Windows (p. 10) • Default Disk Space Alarm (p. 11) • Renaming Clusters (p. 11) • Shutting Down and Deleting Clusters (p. 12) • Managing Clusters Using the Console (p. 13) • Managing Clusters Using the AWS SDK for Java (p.
Amazon Redshift Management Guide About Clusters and Nodes Note Although the console displays this information in one field, it is two parameters in the Amazon Redshift API: ClusterVersion and ClusterRevisionNumber. For more information, go to Cluster in the Amazon Redshift API Reference. Amazon Redshift provides a setting, Allow Version Upgrade, to specify whether to automatically upgrade the Amazon Redshift engine in your cluster if a new version of the engine becomes available.
Amazon Redshift Management Guide Determining the Number of Nodes being sent back to the client applications. For more information about leader nodes and compute nodes, go to Data warehouse system architecture in the Amazon Redshift Database Developer Guide. The following tables outline the available node type combinations for Amazon Redshift. For more information and pricing, go to the Amazon Redshift pricing page.
Amazon Redshift Management Guide Resizing a Cluster compute nodes, you purchase what are called reserved node offerings. You purchase one offering for each compute node that you want to reserve. When you reserve a compute node, you pay a fixed up-front charge and then an hourly recurring charge, whether your cluster is running or not. The hourly charges, however, are significantly lower than those for on-demand usage. For more information, see Purchasing Amazon Redshift Reserved Nodes (p. 107).
Amazon Redshift Management Guide Supported Platforms to Launch Your Cluster You can get the dig utility as part of the BIND software download. For more information on BIND, go to BIND in the Internet Systems Consortium documentation. Supported Platforms to Launch Your Cluster Amazon Redshift clusters run in Amazon Elastic Compute Cloud (Amazon EC2) instances that are configured for the Amazon Redshift node type and size that you select.
Amazon Redshift Management Guide Choose a Platform For more information about Amazon Virtual Private Cloud (Amazon VPC), go to the Amazon VPC product detail page. Choose a Platform Your AWS account is capable of launching instances either into both platforms, or only into EC2-VPC, on a region-by-region basis. To determine which platform your account supports, and then launch a cluster, do the following: 1. Decide on the AWS region in which you want to deploy a cluster.
Amazon Redshift Management Guide Default Disk Space Alarm • EU (Ireland) region: 22:00-06:00 UTC While Amazon Redshift is performing maintenance, it terminates any queries or other operations that are in progress. If there are no maintenance tasks to perform during the scheduled maintenance window, your cluster continues to operate normally until the next scheduled maintenance window.
Amazon Redshift Management Guide Shutting Down and Deleting Clusters You might rename a cluster if you want to change the cluster to which your applications connect without having to change the endpoint in those applications. In this case, you must first rename the original cluster and then change the second cluster to reuse the name of the original cluster prior to the rename.
Amazon Redshift Management Guide Managing Clusters Using the Console Managing Clusters Using the Console This topic explains how to work with clusters in the Amazon Redshift console. You can use the Clusters section in the Amazon Redshift console to create, modify, resize, delete, reboot, and back up clusters. When you don't have any clusters in a region, and you navigate to the Clusters page, you will see an option to launch a cluster. In the following screenshot, the region is the US East (N.
Amazon Redshift Management Guide Creating a Cluster Creating a Cluster Before you create a cluster, review the information in the Overview (p. 5) of this section. To create a cluster 1. 2. 3. Sign in to the AWS Management Console and open the Amazon Redshift console at https:// console.aws.amazon.com/redshift. Click Launch Cluster. On the Cluster Details page, specify values for the following options, and then click Continue. Cluster Identifier Type a unique name for your cluster.
Amazon Redshift Management Guide Creating a Cluster • 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 must contain at least one number. • It can be any printable ASCII character (ASCII code 33 to 126) except single quotation mark, double quotation mark, \, /, @, or space.
Amazon Redshift Management Guide Creating a Cluster 5. On the Additional Configuration page, specify values for the following options, and then click Continue. a. Under Provide the optional additional configuration details below, configure the following options: Cluster Parameter Group Select a cluster parameter group to associate with the cluster. If you don't select one, the cluster uses the default parameter group.
Amazon Redshift Management Guide Creating a Cluster your own CMK gives you more flexibility, including the ability to create, rotate, disable, define access controls for, and audit the encryption keys used to protect your data. If you want to use another CMK from your account, you need to create it first in AWS KMS. Then the key will be available in the Master Key list in Amazon Redshift.
Amazon Redshift Management Guide Creating a Cluster Cluster Subnet Group Select the Amazon Redshift subnet group in which to launch the cluster. Note This option is available only for clusters in a VPC. Publicly Accessible Click Yes to enable connections to the cluster from outside of the VPC in which you launch the cluster. Click No if you want to limit connections to the cluster from only within the VPC. Note This option is available only for clusters in a VPC.
Amazon Redshift Management Guide Creating a Cluster d. Under Optionally create a basic alarm for this cluster, configure the following options, and then click Continue: Create CloudWatch Alarm Click Yes if you want to create an alarm that monitors the disk usage of your cluster, and then specify values for the corresponding options. Click No if you don't want to create an alarm.
Amazon Redshift Management Guide Modifying a Cluster 7. After you initiate the creation process, click Close. The cluster might take several minutes to be ready to use. You can monitor the status of the operation in the performance dashboard.
Amazon Redshift Management Guide Modifying a Cluster If you decrease the automated snapshot retention period, existing automated snapshots whose settings fall outside of the new retention period are deleted. For information about working with snapshots, see Amazon Redshift Snapshots (p. 82). To modify a cluster 1. 2. Sign in to the AWS Management Console and open the Amazon Redshift console at https:// console.aws.amazon.com/redshift.
Amazon Redshift Management Guide Shutting Down or Deleting a Cluster Shutting Down or Deleting a Cluster If you no longer need your cluster, you can shut it down or delete it. If you plan to provision a new cluster with the same data and configuration as the one you are shutting down, you will need a snapshot so that you can restore the snapshot at a later time and resume using the cluster. If you shut down your cluster but you don't create a final snapshot, the cluster will be deleted.
Amazon Redshift Management Guide Rebooting a Cluster After you initiate the shutdown of the cluster, it can take several minutes for the cluster to be shut down. You can monitor the status in the cluster list as shown in the following screenshot. If you requested a final snapshot, Cluster Status will show final-snapshot before deleting. When the process has finished, you can verify that the cluster has been shut down because it will no longer appear in the list of clusters on the Clusters page.
Amazon Redshift Management Guide Resizing a Cluster 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 be available. You can monitor the status of the reboot in the cluster list as shown in the following screenshot. Resizing a Cluster When you resize a cluster, you specify a number of nodes or node type that is different from the current configuration of the cluster.
Amazon Redshift Management Guide Resizing a Cluster 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 parameters including the Node Type, Cluster Type, and Number of Nodes, and then click Resize. You can monitor the progress of the resize on the Status tab.
Amazon Redshift Management Guide Getting Information About Cluster Configuration Getting Information About Cluster Configuration To get cluster configuration details 1. 2. 3. Sign in to the AWS Management Console and open the Amazon Redshift console at https:// console.aws.amazon.com/redshift. In the navigation pane, click Clusters, and then click the cluster for which you want to view configuration information. On the Configuration tab of the cluster details page, review the configuration information.
Amazon Redshift Management Guide Taking a Snapshot of a Cluster 3. Click the Status tab. The status summary page is displayed as shown in the following screenshot. Taking a Snapshot of a Cluster You can take a snapshot of your cluster from the Configuration tab of your cluster as shown following. You can also take a snapshot of your cluster from the snapshots part of the Amazon Redshift console. For more information, go to Managing Snapshots Using the Console (p. 86). To take a snapshot of a cluster 1.
Amazon Redshift Management Guide Editing the Default Disk Space Alarm 4. 5. 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 the Snapshot Identifier box, type a name for the snapshot. Click Create. To view details about the snapshot taken and all other snapshots for your AWS account, go to the snapshots part of the Amazon Redshift console (see Managing Snapshots Using the Console (p. 86)).
Amazon Redshift Management Guide Working with Cluster Performance Data 5. In the Edit Alarm window, edit any values that you want to change, such as the percentage or minutes. 6. To change the Amazon SNS topic that the alarm is associated with, do one of the following: • If you want to select another existing topic, select a topic from the Send a notification to list. • If you want to create a new topic, click create topic and specify a new topic name and the email addresses for recipients. 7.
Amazon Redshift Management Guide Managing Clusters Using the AWS SDK for Java For step-by-step instructions to run the following example, see Running Java Examples for Amazon Redshift Using Eclipse (p. 130). You need to update the code and specify a cluster identifier. import java.io.IOException; import import import import com.amazonaws.auth.AWSCredentials; com.amazonaws.auth.PropertiesCredentials; com.amazonaws.services.redshift.AmazonRedshiftClient; com.amazonaws.services.redshift.model.
Amazon Redshift Management Guide Manage Clusters Using the Amazon Redshift CLI and API ModifyClusterRequest request = new ModifyClusterRequest() .withClusterIdentifier(clusterIdentifier) .withPreferredMaintenanceWindow("wed:07:30-wed:08:00"); client.modifyCluster(request); System.out.println("Modified cluster " + clusterIdentifier); } private static void printResult(DescribeClustersResult result) { if (result == null) { System.out.println("Describe clusters result is null."); return; } System.out.
Amazon Redshift Management Guide Managing Clusters in an Amazon Virtual Private Cloud (VPC) You can use the following Amazon Redshift APIs to manage clusters. • CreateCluster • DeleteCluster • DescribeClusters • DescribeClusterVersions • DescribeOrderableClusterOptions • ModifyCluster • RebootCluster Managing Clusters in an Amazon Virtual Private Cloud (VPC) Topics • Overview (p. 32) • Creating a Cluster in a VPC (p. 33) • Managing VPC Security Groups for a Cluster (p.
Amazon Redshift Management Guide Creating a Cluster in a VPC If you have a publicly accessible cluster in a VPC, and you want to connect to it by using the private IP address from within the VPC, you must set the following VPC parameters to true: • DNS resolution • DNS hostnames If you have a publicly accessible cluster in a VPC, but do not set those parameters to true in the VPC, connections made from within the VPC will resolve to the EIP of the cluster instead of the private IP address.
Amazon Redshift Management Guide Creating a Cluster in a VPC Started Guide. Make a note of the VPC identifier, subnet, and subnet's availability zone. You will need this information when you launch your cluster. Note You must have at least one subnet defined in your VPC so you can add it to the cluster subnet group in the next step. If you use the VPC Wizard, a subnet for your VPC is automatically created for you. For more information about adding a subnet to your VPC, go to Adding a Subnet to Your VPC.
Amazon Redshift Management Guide Managing VPC Security Groups for a Cluster Now you are ready to use the cluster. You can follow the Getting Started steps to test the cluster by uploading sample data and trying example queries. Managing VPC Security Groups for a Cluster When you provision an Amazon Redshift cluster, it is locked down by default so nobody has access to it. To grant other users inbound access to an Amazon Redshift cluster, you associate the cluster with a security group.
Amazon Redshift Management Guide Cluster Subnet Groups all service levels, you might want to create separate VPC security groups, one for each service level. You can then associate these VPC security groups with your cluster. Keep in mind that while you can create up to 100 VPC security groups for a VPC, and you can associate a VPC security group with many clusters, you can only associate up to 5 VPC security groups with a given cluster. Amazon Redshift applies changes to a VPC security group immediately.
Amazon Redshift Management Guide Cluster Subnet Groups (highlighted), you can edit or delete it. The details of the selected security group are shown under Cluster Subnet Group Details. Creating a Cluster Subnet Group You must have at least one cluster subnet group defined to provision a cluster in a VPC. To create a cluster subnet group 1. 2. 3. Sign into the AWS Management Console and open the Amazon Redshift console at https:// console.aws.amazon.com/redshift. In the navigation pane, click Security.
Amazon Redshift Management Guide Cluster Subnet Groups 5. Click Yes, Create. The new group will be displayed in the list of cluster subnet groups. Modifying a Cluster Subnet Group To modify a cluster subnet group 1. 2. 3. Sign into the AWS Management Console and open the Amazon Redshift console at https:// console.aws.amazon.com/redshift. In the navigation pane, click Security. On the Subnet Groups tab, in the cluster subnet group list, click the row of the group you want to modify, and then click Edit.
Amazon Redshift Management Guide Cluster Subnet Groups To... Do this... Remove a subnet from the group. In the lists of subnets in use for the group, click Remove next to the subnet to remove. Click Save. Deleting a Cluster Subnet Group You cannot delete a cluster subnet group that is used by a cluster. To delete a cluster subnet group 1. 2. 3. Sign into the AWS Management Console and open the Amazon Redshift console at https:// console.aws.amazon.com/redshift.
Amazon Redshift Management Guide Cluster Subnet Groups • Creating a cluster subnet group. • Listing metadata about a cluster subnet group. • Modifying a cluster subnet group. For step-by-step instructions to run the following example, see Running Java Examples for Amazon Redshift Using Eclipse (p. 130). You need to update the code and provide a cluster subnet group name and two subnet identifiers. import java.io.IOException; import java.util.ArrayList; import java.util.
Amazon Redshift Management Guide Cluster Subnet Groups DescribeClusterSubnetGroupsRequest request1 = new DescribeClusterSubnet GroupsRequest() .withClusterSubnetGroupName(clusterSubnetGroupName); DescribeClusterSubnetGroupsResult result1 = client.describeClusterSub netGroups(request1); List subnetNames = new ArrayList(); // We can work with just the first group returned since we requested info about one group. for (Subnet subnet : result1.getClusterSubnetGroups().get(0).
Amazon Redshift Management Guide Cluster Subnet Groups subnet.getSubnetAvailabilityZone().getName(), subnet.getSub netStatus()); } } } Manage Cluster Subnet Groups Using Amazon Redshift CLI and API You can use the following Amazon Redshift CLI operations to manage cluster subnet groups. • create-cluster-subnet-group • delete-cluster-subnet-group • describe-cluster-subnet-groups • modify-cluster-subnet-group You can use the following Amazon Redshift APIs to manage cluster subnet groups.
Amazon Redshift Management Guide Overview Amazon Redshift Cluster Security Groups When you provision an Amazon Redshift cluster, it is locked down by default so nobody has access to it. To grant other users inbound access to an Amazon Redshift cluster, you associate the cluster with a security group. If you are on the EC2-Classic platform, you define a cluster security group and associate it with a cluster as described following.
Amazon Redshift Management Guide Managing Cluster Security Groups Using the Console might want to create separate cluster security groups, one for each company. You can add rules in each cluster security group to identify the Amazon EC2 security groups and the IP address ranges specific to a company. You can then associate all these cluster security groups with your cluster. You can associate a cluster security group with many clusters, and you can associate many cluster security groups with a cluster.
Amazon Redshift Management Guide Creating a Cluster Security Group You cannot delete the default cluster security group, but you can modify it by authorizing or revoking ingress access. To add or modify the rules associated with a security group, click on the security group to go to the Security Group Connections page. Creating a Cluster Security Group To create a cluster security group 1. 2. Sign into the AWS Management Console and open the Amazon Redshift console at https:// console.aws.amazon.
Amazon Redshift Management Guide Tagging a Cluster Security Group Tagging a Cluster Security Group To tag a cluster security group 1. 2. Sign into the AWS Management Console and open the Amazon Redshift console at https:// console.aws.amazon.com/redshift. In the navigation pane, click Security. 3. On the Security Groups tab, select the cluster security group and click Manage Tags. 4. In the Manage Tags dialog box, do one of the following: a. Remove a tag.
Amazon Redshift Management Guide Managing Ingress Rules for a Cluster Security Group 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 resource, see How to Manage Tags in the Amazon Redshift Console (p. 243). Managing Ingress Rules for a Cluster Security Group To manage ingress rules for a cluster security group 1. 2. 3. 4.
Amazon Redshift Management Guide Managing Ingress Rules for a Cluster Security Group 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 the CIDR/IP to Authorize box, specify the range. • Click Authorize. b. Add an ingress rule based on an EC2 Security Group. • Under Connection Type, select EC2 Security Group. • Select the AWS account to use.
Amazon Redshift Management Guide Revoking Ingress Rules for a Cluster Security Group Revoking Ingress Rules for a Cluster Security Group To revoke ingress rules for a cluster security group 1. 2. 3. 4. Sign into the AWS Management Console and open the Amazon Redshift console at https:// console.aws.amazon.com/redshift. In the navigation pane, click Security. On the Security Groups tab, in the cluster security group list, click the cluster security group whose rules you want to manage.
Amazon Redshift Management Guide Tagging Ingress Rules for a Cluster Security Group 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. Note Tagging an EC2 Security Group rule only tags that rule, not the EC2 Security Group itself. If you want the EC2 Security Group tagged as well, you must do that separately. • In the Add Tags section, type a key/value pair for the tag. • Click Apply Changes.
Amazon Redshift Management Guide Deleting a Cluster Security Group For more information about tagging an Amazon Redshift resource, see How to Manage Tags in the Amazon Redshift Console (p. 243). Deleting a Cluster Security Group If a cluster security group is associated with one or more clusters, you cannot delete it. To delete a cluster security group 1. 2. 3. Sign into the AWS Management Console and open the Amazon Redshift console at https:// console.aws.amazon.com/redshift.
Amazon Redshift Management Guide Associating a Cluster Security Group with a Cluster 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 it. The following example shows that securitygroup1 is used by examplecluster2. Associating a Cluster Security Group with a Cluster Each cluster you provision on the EC2-Classic platform has one or more cluster security groups associated with it.
Amazon Redshift Management Guide Managing Cluster Security Groups Using the AWS SDK for Java By default, when a new cluster security group is created, it has no ingress rules. This example modifies a new cluster security group by adding two ingress rules. One ingress rule is added by specifying a CIDR/IP range; the other is added by specifying an owner ID and Amazon EC2 security group combination.
Amazon Redshift Management Guide Managing Cluster Security Groups Using the AWS SDK for Java .withClusterSecurityGroupName(clusterSecurityGroupName) .withCIDRIP("192.168.40.5/32"); ClusterSecurityGroup result = client.authorizeClusterSecurityGroupIn gress(request); request = new AuthorizeClusterSecurityGroupIngressRequest() .withClusterSecurityGroupName(clusterSecurityGroupName) .withEC2SecurityGroupName("default") .withEC2SecurityGroupOwnerId(ownerID); result = client.
Amazon Redshift Management Guide Manage Cluster Security Groups Using the Amazon Redshift CLI and API null."); return; } System.out.println("\nPrinting security group results:"); for (ClusterSecurityGroup group : groups) { printResultSecurityGroup(group); } } private static void printResultSecurityGroup(ClusterSecurityGroup group) { System.out.format("\nName: '%s', Description: '%s'\n", group.getClusterSecurityGroupName(), group.getDescription()); for (EC2SecurityGroup g : group.
Amazon Redshift Management Guide Overview Amazon Redshift Parameter Groups Topics • Overview (p. 56) • Configuring the Maximum Size of a Cursor Result Set (p. 57) • Configuring Workload Management (p. 59) • Managing Parameter Groups Using the Console (p. 72) • Managing Parameter Groups Using the AWS SDK for Java (p. 77) • Managing Parameter Groups Using the Amazon Redshift CLI and API (p. 80) Overview When you provision an Amazon Redshift cluster, you associate a parameter group with it.
Amazon Redshift Management Guide Configuring the Maximum Size of a Cursor Result Set Parameter Name Value extra_float_digits 0 max_cursor_result_set_size default query_group default require_ssl false search_path $user, public statement_timeout 0 wlm_json_configuration [{"query_concurrency":5}] Use the following topics for more information about these database parameters: • enable_user_activity_logging parameter: Database Audit Logging (p. 215).
Amazon Redshift Management Guide Configuring the Maximum Size of a Cursor Result Set number of cursors for your cluster, and to achieve the ideal balance between maximum result set size and concurrency. Important Before you modify this parameter, you should understand the considerations involved with using cursors and the impact that they have on query performance. For more information about the considerations, see Cursor Constraints in the Amazon Redshift Database Developer Guide.
Amazon Redshift Management Guide Configuring Workload Management of range for the cluster. If you change the parameter value and want to return to the default setting, reset the parameter to 0. After you change this value, and restart the cluster, you can query STV_CURSOR_CONFIGURATION to make sure that the values took effect.
Amazon Redshift Management Guide Configuring Workload Management Console Field JSON Property eul aV Concurrency query_concurrency e hT -mun r eb f o - reuq se i taht na c nu r - noc - ruc yl tner n i a .eueuq n e hW a eueuq sehcaer eh t - noc - ruc ycner , level y na - bus tneuqes - reuq se i t i aw n i eh t eueuq - nu lit -er secruos era - l iava e l ba o t -orp ssec .m eht e hT egnar s i neewteb 1 dna .
Amazon Redshift Management Guide Configuring Workload Management Console Field JSON Property eul aV User Groups user_group A -amoc - pes - ra de t a tsi l f o r esu puorg . sm ean n e hW -mem s r eb f o eh t r esu puorg nu r - reuq se i n i eh t -atad ,esab r ieht - reuq se i era detuor o t eh t eueuq taht s i - sa - os - ic de t a h t iw r ieht r esu .
Amazon Redshift Management Guide Configuring Workload Management Console Field JSON Property User Group Wildcard user_group_wild_card API Version 2012-12-01 62 eul aV
Amazon Redshift Management Guide Configuring Workload Management Console Field JSON Property eul aV A naelooB eu l av taht -n i -c i d se t a -hteh w r e o t - ne e l ba -dl iw sdrac ro f r esu .spuorg f I s i ht s i , 0 -dl iw sdrac era -s i d ;delba f i s i ht s i , 1 -dl iw sdrac era - ne .
Amazon Redshift Management Guide Configuring Workload Management Console Field JSON Property eul aV -nur gn i n - reuq . sei Query Groups query_group API Version 2012-12-01 64 A -amoc - pes - ra de t a tsi l f o yreuq .spuorg n e hW -mem s r eb f o eh t yreuq puorg nu r - reuq se i n i eh t -atad ,esab r ieht - reuq se i era detuor o t eh t eueuq taht s i - sa - os - ic de t a h t iw r ieht yreuq .
Amazon Redshift Management Guide Configuring Workload Management Console Field JSON Property Query Group Wildcard query_group_wild_card API Version 2012-12-01 65 eul aV
Amazon Redshift Management Guide Configuring Workload Management Console Field JSON Property eul aV A naelooB eu l av taht -n i -c i d se t a -hteh w r e o t - ne e l ba -dl iw sdrac ro f yreuq .spuorg f I s i ht s i , 0 -dl iw sdrac era -s i d ;delba f i s i ht s i , 1 -dl iw sdrac era - ne .
Amazon Redshift Management Guide Configuring Workload Management Console Field JSON Property eul aV -nur gn i n - reuq . sei Timeout max_execution_time API Version 2012-12-01 67 e hT - xam -m i m u ,m ei t n i -l m i -i l ,sdnoces - reuq se i na c nu r - eb ero f - eb gn i - nac .
Amazon Redshift Management Guide Configuring Workload Management Console Field JSON Property eul aV % Memory memory_percent_to_use e hT tnuo m a f o y romem o t - la -co l eta o t eh t .eueuq e hT latot tnuo m a f o y romem ssorca l la seueuq t sum l auqe 001 - rep , tnec o s f i uo y t es s i ht -porp y t re ro f eno ,eueuq uo y t sum t es eh t -h t o r e seueuq - ca -droc .
Amazon Redshift Management Guide Configuring the wlm_json_configuration Parameter user groups or query groups for the default queue. The default queue must always be the last queue in the WLM configuration. As with other parameters, you cannot modify the WLM configuration in the default parameter group. Clusters associated with the default parameter group always use the default WLM configuration.
Amazon Redshift Management Guide Configuring the wlm_json_configuration Parameter "q2_second_property_name":"q2_second_property_value", ... } ... ]" } ] In the preceding example, the representative properties that begin with q1 are objects in an array for the first queue. Each of these objects is a name/value pair; name and value together set the WLM properties for the first queue. The representative properties that begin with q2 are objects in an array for the second queue.
Amazon Redshift Management Guide Configuring the wlm_json_configuration Parameter Rules for Configuring WLM by Using the AWS CLI in the Command Line on the Linux and Mac OS X Operating Systems • The entire JSON structure must be enclosed in single quotation marks (') and brackets ([ ]). • All parameter names and parameter values must be enclosed in double quotation marks ("). • Within the ParameterValue value, you must enclose the entire nested structure in double-quotation marks (") and brackets ([ ]).
Amazon Redshift Management Guide Managing Parameter Groups Using the Console Note For readability purposes, the example is displayed on two lines, but in the actual AWS CLI this would be one line.
Amazon Redshift Management Guide Creating a Parameter Group You can create, modify, and delete parameter groups by using the Amazon Redshift console. All of the tasks start from the parameter group list. In the parameter group list below, there are two parameter groups, the default parameter group and a custom parameter group called parametergroup1. Because parametergroup1 is selected (highlighted), you can edit or delete it. You cannot delete or modify the default parameter group.
Amazon Redshift Management Guide Modifying a Parameter Group Modifying a Parameter Group If the parameter group you are modifying is associated with a cluster, then the changes you make to the parameter group apply only after you reboot the cluster. To modify a parameter group 1. 2. 3. Sign into the AWS Management Console and open the Amazon Redshift console at https:// console.aws.amazon.com/redshift. In the navigation pane, click Parameter Groups.
Amazon Redshift Management Guide Modifying a Parameter Group 5. To edit the workload management configuration, in the parameter group list, click the row of the parameter group you want to modify, and then click Edit WLM. Click the WLM tab to edit the workload management configuration. a. Do one of the following: To... Do this... Modify an existing queue Change the queue's Concurrency, User Groups, or Query Groups fields.
Amazon Redshift Management Guide Deleting a Parameter Group b. To... Do this... Change the order of a queue in the list Use the Up and Down buttons to change the order that queries are assigned to queues based on users and query groups. For more information, go to Defining Query Queues in the Amazon Redshift Developer Guide. Delete a queue Click the X button of the queue to remove. When the settings are as you want them, click Save Changes.
Amazon Redshift Management Guide Associating a Parameter Group with a Cluster Associating a Parameter Group with a Cluster Each cluster you provision has one parameter group associated with it. You can associate a parameter group with a cluster when you create the cluster, or you can associate a parameter group later by modifying the cluster. For more information, see To create a cluster (p. 14) and To modify a cluster (p. 21).
Amazon Redshift Management Guide Managing Parameter Groups Using the AWS SDK for Java Because this example replaces the WLM configuration, this JSON configuration also defines the default queue with no specific user group or query group. It sets the concurrency to the default value, 5. { "query_concurrency":5 } For more information about Workload Management (WML) configuration, go to Implementing workload management.
Amazon Redshift Management Guide Managing Parameter Groups Using the AWS SDK for Java private static void describeClusterParameterGroups() { DescribeClusterParameterGroupsResult result = client.describeClusterPara meterGroups(); printResultClusterParameterGroups(result); } private static void modifyClusterParameterGroup() { List parameters = new ArrayList(); parameters.add(new Parameter() .withParameterName("extra_float_digits") .withParameterValue("2")); // Replace WLM configuration.
Amazon Redshift Management Guide Managing Parameter Groups Using the Amazon Redshift CLI and API } } private static void describeClusterParameters(String parameterGroupName) { DescribeClusterParametersRequest request = new DescribeClusterParamet ersRequest() .withParameterGroupName(parameterGroupName); DescribeClusterParametersResult result = client.
Amazon Redshift Management Guide Managing Parameter Groups Using the Amazon Redshift CLI and API • CreateClusterParameterGroup • DeleteClusterParameterGroup • DescribeClusterParameters • DescribeClusterParameterGroups • DescribeDefaultClusterParameters • ModifyClusterParameterGroup • ResetClusterParameterGroup API Version 2012-12-01 81
Amazon Redshift Management Guide Overview Amazon Redshift Snapshots Topics • Overview (p. 82) • Managing Snapshots Using the Console (p. 86) • Managing Snapshots Using the AWS SDK for Java (p. 94) • Managing Snapshots Using the Amazon Redshift CLI and API (p. 97) Overview Snapshots are point-in-time backups of a cluster. There are two types of snapshots: automated and manual. Amazon Redshift stores these snapshots internally in Amazon S3 by using an encrypted Secure Sockets Layer (SSL) connection.
Amazon Redshift Management Guide Manual Snapshots To disable automated snapshots, set the retention period to zero. If you disable automated snapshots, Amazon Redshift stops taking snapshots and deletes any existing automated snapshots for the cluster. Only Amazon Redshift can delete an automated snapshot; you cannot delete them manually. Amazon Redshift deletes automated snapshots at the end of a snapshot’s retention period, when you disable automated snapshots, or when you delete the cluster.
Amazon Redshift Management Guide Restoring a Cluster from a Snapshot Restoring a Cluster from a Snapshot A snapshot contains data from any databases that are running on your cluster, and also information about your cluster, including the number of nodes, node type, and master user name. If you need to restore your cluster from a snapshot, Amazon Redshift uses the cluster information to create a new cluster and then restores all the databases from the snapshot data.
Amazon Redshift Management Guide Sharing Snapshots "Effect":"Allow", "Action":[ "redshift:AuthorizeSnapshotAccess", "redshift:RevokeSnapshotAccess" ], "Resource":[ "arn:aws:redshift:us-east-1:012345678912:snapshot:*/my-snap shot20130829" ] } ] } • IAM users in an AWS account with which a snapshot has been shared cannot perform actions on that snapshot unless they have IAM policies allowing those actions: • To list or describe a snapshot, they must have an IAM policy that allows the DescribeClusterSnapshot
Amazon Redshift Management Guide Managing Snapshots Using the Console ] } • Once access to a snapshot has been revoked from an AWS account, no users in that account can access the snapshot, even if they have IAM policies that allow actions on the previously shared snapshot resource. Managing Snapshots Using the Console Topics • Creating a Manual Snapshot (p. 87) • Deleting a Manual Snapshot (p. 88) • Copying an Automated Snapshot (p. 88) • • • • • Restoring a Cluster from a Snapshot (p.
Amazon Redshift Management Guide Creating a Manual Snapshot Creating a Manual Snapshot You can create a manual snapshot of a cluster from the snapshots list as follows. Or, you can take a snapshot of a cluster in the cluster configuration pane. For more information, see Taking a Snapshot of a Cluster (p. 27). To create a manual snapshot 1. Sign into the AWS Management Console and open the Amazon Redshift console at https:// console.aws.amazon.com/redshift. 2. 3. In the navigation pane, click Snapshots.
Amazon Redshift Management Guide Deleting a Manual Snapshot Deleting a Manual Snapshot To delete a manual snapshot 1. 2. 3. Sign into the AWS Management Console and open the Amazon Redshift console at https:// console.aws.amazon.com/redshift. In the navigation pane, click Snapshots. If you need to filter the list in order to find the snapshot that you want to delete, do any or all of the following: • In the Time Range box, click a time range that will narrow your search appropriately.
Amazon Redshift Management Guide Restoring a Cluster from a Snapshot • 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 the cluster whose snapshot you want to copy. 4. In the snapshot list, click the row of the snapshot that you want to copy. 5. Click Copy Automated Snapshot. 6. In the Snapshot Identifier box of the Copy Automated Snapshot dialog box, type a name for the snapshot copy. 7.
Amazon Redshift Management Guide Restoring a Cluster from a Snapshot 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 identifier for the restored cluster. Cluster identifiers must meet the following conditions: • • • • • b. c. d. e. f. They must contain from 1 to 255 alphanumeric characters or hyphens. Alphabetic characters must be lowercase. The first character must be a letter.
Amazon Redshift Management Guide Sharing a Cluster Snapshot 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 into a cluster that uses the EC2-Classic platform. 7. Click Restore. Sharing a Cluster Snapshot You can authorize other users to access a manual snapshot you own, and you can later revoke that access when it is no longer required. To share a cluster snapshot 1. 2. 3.
Amazon Redshift Management Guide Configuring Cross-Region Snapshot Copy • 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 box, click the cluster whose snapshot you want to copy. 4. In the snapshot list, click the row that contains the snapshot that you want to use. 5. 6. Click Manage Access.
Amazon Redshift Management Guide Modifying the Retention Period for Cross-Region Snapshot Copy 5. Click Save Configuration. After you configure cross-region snapshot copy, the Cluster detail page updates with the information in the Backup, Maintenance, and Logging section. Modifying the Retention Period for Cross-Region Snapshot Copy After you configure cross-region snapshot copy, you might want to change the settings.
Amazon Redshift Management Guide Disabling Cross-Region Snapshot Copy 5. If you select a smaller number of days to retain snapshots in the destination region, any automated snapshots that were taken before the new retention period will be deleted. If you select a larger number of days to retain snapshots in the destination region, the retention period for existing automated snapshots will be extended by the difference between the old value and the new value. Click Save Configuration.
Amazon Redshift Management Guide Managing Snapshots Using the AWS SDK for Java import com.amazonaws.services.redshift.model.DescribeClusterSnapshotsResult; import com.amazonaws.services.redshift.model.
Amazon Redshift Management Guide Managing Snapshots Using the AWS SDK for Java DescribeClusterSnapshotsRequest request = new DescribeClusterSnapshots Request() .withEndTime(creationDate) .withClusterIdentifier(clusterIdentifier) .withSnapshotType("manual"); DescribeClusterSnapshotsResult result = client.describeClusterSnap shots(request); for (Snapshot s : result.getSnapshots()) { DeleteClusterSnapshotRequest deleteRequest = new DeleteClusterSnap shotRequest() .withSnapshotIdentifier(s.
Amazon Redshift Management Guide Managing Snapshots Using the Amazon Redshift CLI and API Managing Snapshots Using the Amazon Redshift CLI and API You can use the following Amazon Redshift CLI operations to manage snapshots.
Amazon Redshift Management Guide Overview Amazon Redshift Database Encryption Topics • Overview (p. 98) • Hardware Security Modules (p. 99) • Rotating Encryption Keys (p. 105) Overview Amazon Redshift provides database encryption for its clusters to help protect data at rest. When you enable encryption for your cluster, Amazon Redshift encrypts all data by using hardware-accelerated AES-256.
Amazon Redshift Management Guide Hardware Security Modules (SOX), the Health Insurance Portability and Accountability Act (HIPAA), and other such regulations provide guidelines for handling specific types of data. Encryption is an immutable property of the cluster.The only way to go from an encrypted to a nonencrypted cluster or vice versa is to unload the data and reload it to a new cluster. Encryption also applies to backups.
Amazon Redshift Management Guide Configuring HSM Using the Amazon Redshift Console You can use the following procedures to configure hardware security modules by using the AWS Management Console. Creating an HSM Connection 1. 2. Sign into the AWS Management Console and open the Amazon Redshift console at https:// console.aws.amazon.com/redshift. In the left navigation pane, click Security, and then click the HSM Connections tab. 3. Click Create HSM Connection. 4.
Amazon Redshift Management Guide Configuring HSM Using the Amazon Redshift Console 5. After the connection is created, you can create an HSM client certificate. If you want to create an HSM client certificate immediately after creating the connection, click Yes and complete the steps in the next procedure. Otherwise, click Not now to return to the list of HSM connections and complete the remainder of the process at another time. Creating an HSM Client Certificate 1.
Amazon Redshift Management Guide Configuring HSM Using the Amazon Redshift Console 4. On the Create HSM Client Certificate page, type a name in the HSM Client Certificate Identifier box to identify this client certificate. 5. 6. Click Next. After the certificate is created, a confirmation page appears with information to register the key on your HSM. If you do not have permission to configure the HSM, coordinate the following steps with an HSM administrator. a. b.
Amazon Redshift Management Guide Configuring HSM Using the Amazon Redshift Console c. Paste the public key into the open file and save it with the file name displayed in step 1 from the confirmation page. Make sure that you save the file with the .pem file extension, for example: 123456789mykey.pem. d. Upload the .pem file to your HSM. e. On the HSM, open a command-prompt window and run the commands listed in step 4 on the confirmation page to register the key.
Amazon Redshift Management Guide Configuring HSM Using the Amazon Redshift Console Displaying the Public Key for an HSM Client Certificate 1. Sign into the AWS Management Console and open the Amazon Redshift console at https:// console.aws.amazon.com/redshift. 2. 3. In the navigation pane, click Security, and then click the HSM Certificates tab. Click the HSM client certificate to display the public key.
Amazon Redshift Management Guide Configuring HSM Using the Amazon Redshift CLI and API Configuring HSM Using the Amazon Redshift CLI and API You can use the following Amazon Redshift CLI operations to manage hardware security modules. • create-hsm-client-certificate • create-hsm-configuration • delete-hsm-client-certificate • delete-hsm-configuration • describe-hsm-client-certificates • describe-hsm-configurations You can use the following Amazon Redshift API actions to manage hardware security modules.
Amazon Redshift Management Guide Rotating Encryption Keys Using the Amazon Redshift CLI and API 2. 3. In the navigation pane, click Clusters. In the list, click the cluster for which you want to rotate keys. 4. 5. Click Database, and then click Rotate Encryption Keys. Click Yes, Rotate Keys if you want to rotate the keys or Cancel if you do not. Note Your cluster will be momentarily unavailable until the key rotation process completes.
Amazon Redshift Management Guide Overview Purchasing Amazon Redshift Reserved Nodes Topics • Overview (p. 107) • Purchasing a Reserved Node Offering with the Amazon Redshift Console (p. 108) • Purchasing a Reserved Node Offering Using the AWS SDK for Java (p. 111) • Purchasing a Reserved Node Offering Using the Amazon Redshift CLI and API (p.
Amazon Redshift Management Guide Purchasing a Reserved Node Offering with the Console 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 reserved node offerings that are available for purchase. Each offering describes the node type, the duration for which it will be reserved, offering type, and fixed and recurring charges. From the Amazon Redshift console, you select these values.
Amazon Redshift Management Guide Listing Reserved Node Offerings Listing Reserved Node Offerings To list reserved node offerings 1. 2. Sign into the AWS Management Console and open the Amazon Redshift console at https:// console.aws.amazon.com/redshift. In the navigation pane, click Reserved Nodes. 3. (Optional) To filter the display of offers, use the Filter boxes. Do one or both of the following: • Type a full or partial reservation ID.
Amazon Redshift Management Guide Purchasing Reserved Node Offerings 4. In the Purchase Reserved Nodes wizard, specify Node Type, Term, Offering Type, and Number of Nodes. 5. 6. When the settings are as you want them, click Continue. Review the offering details, and then click Purchase. 7. In the reserved nodes list, check the Status of your offering. When the status changes to active, you can use your node. At that time, the Remaining Days box will display the correct value.
Amazon Redshift Management Guide Purchasing a Reserved Node Offering Using Java Purchasing a Reserved Node Offering Using the AWS SDK for Java The following example demonstrates how to use the AWS SDK for Java to do the following: • List existing reserved nodes. • Search for a new reserved node offering based on specified node criteria. • Purchase a reserved node. In this example, all the reserved node offerings that match a specified node type and fixed price value are selected.
Amazon Redshift Management Guide Purchasing a Reserved Node Offering Using Java public static String nodeTypeToPurchase = "dw.hs1.xlarge"; public static Double fixedPriceLimit = 10000.00; public static ArrayList matchingNodes = new ArrayL ist(); public static void main(String[] args) throws IOException { AWSCredentials credentials = new PropertiesCredentials( ListAndPurchaseReservedNodeOffering.class .getResourceAsStream("AwsCredentials.
Amazon Redshift Management Guide Purchasing a Reserved Node Offering Using Java } private static void purchaseReservedNodeOffer() throws IOException { if (matchingNodes.size() == 0) { return; } else { System.out.println("\nPurchasing nodes."); for (ReservedNodeOffering offering : matchingNodes) { printOfferingDetails(offering); System.out.println("WARNING: purchasing this offering will incur costs."); System.out.println("Purchase this offering [Y or N]?"); DataInput in = new DataInputStream(System.
Amazon Redshift Management Guide Purchasing a Reserved Node Offering Using the Amazon Redshift CLI and API System.out.format("Offering Type: %s\n", node.getOfferingType()); System.out.format("Duration: %s\n", node.getDuration()); } } Purchasing a Reserved Node Offering Using the Amazon Redshift CLI and API You can use the following Amazon Redshift CLI operations to purchase reserved node offerings.
Amazon Redshift Management Guide Overview of Access to Amazon Redshift Resources Controlling Access to Amazon Redshift Resources You can use AWS Identity and Access Management (IAM) to control which AWS users can create, configure, or delete Amazon Redshift clusters and other resources. Topics • Overview of Access to Amazon Redshift Resources (p. 115) • IAM Policy Elements for Amazon Redshift (p. 117) • Constructing an Amazon Redshift ARN (p. 120) • Example Policies for Amazon Redshift (p.
Amazon Redshift Management Guide Using IAM Users, Groups, and Policies for Cluster Management Using IAM Users, Groups, and Policies for Cluster Management Amazon Redshift uses AWS Identity and Access Management (IAM) to control which users in your AWS account can create, modify, or delete clusters for your AWS account. If you do not create IAM users and groups within an account, you must provide the account root credentials to any people who need to manage Amazon Redshift clusters owned by the account.
Amazon Redshift Management Guide IAM Policy Elements for Amazon Redshift To create the Administrators group 1. Sign in to the AWS Management Console and open the IAM console at https:// console.aws.amazon.com/iam/. 2. 3. In the navigation pane, click Groups, then click Create New Group. In the Group Name box, type Administrators and then click Next Step. 4. In the list of policies, select the check box next to the AdministratorAccess policy.
Amazon Redshift Management Guide IAM Policy Actions for Amazon Redshift "Resource": [ "arn:aws:redshift:us-east-1:123456789012:snapshot:my-cluster*" ] } ] } IAM Policy Actions for Amazon Redshift The Action element in an IAM policy defines what a user can do in Amazon Redshift, such as creating clusters, deleting security groups, restoring clusters from snapshots, and so on.
Amazon Redshift Management Guide IAM Policy Resources for Amazon Redshift • Restoring a cluster from a snapshot. • Revoking cluster snapshot access. Some Amazon Redshift actions, such as copying a cluster snapshot and restoring a cluster from a snapshot, reference more than one Amazon Redshift resource. To successfully complete those actions, an IAM user must have policies with Resource elements that cover both resources.
Amazon Redshift Management Guide Constructing an Amazon Redshift ARN Constructing an Amazon Redshift ARN You can identify resources that are created in Amazon Web Services by a unique identifier called an Amazon Resource Name (ARN). When using the Resource element in an IAM policy to control which Amazon Redshift resources a user or group can access, you specify one or more ARNs that define the resources the users are allowed to access.
Amazon Redshift Management Guide Example Policies for Amazon Redshift The following table shows examples of ARNs for Amazon Redshift resources that have an AWS account of 123456789012, that were created in the US East (Northern Virginia) region, and that have a resource name that begins with "my": Resource Type Sample ARN Cluster arn:aws:redshift:us-east-1:123456789012:cluster:my-cluster Cluster security group arn:aws:redshift:us-east-1:123456789012:securitygroup:my-security-group CIDR/IP arn:aws:re
Amazon Redshift Management Guide Example Policies for Amazon Redshift Example 1: Allow an IAM user full access to all Amazon Redshift actions and resources The following policy allows access to all Amazon Redshift actions on all resources. { "Version": "2012-10-17", "Statement": [ { "Action": [ "redshift:*" ], "Effect": "Allow", "Resource": "*" } ] } The value redshift:* in the Action element indicates all the actions in Amazon Redshift.
Amazon Redshift Management Guide Example Policies for Amazon Redshift Example 3: Manage clusters The following policy allows a user to create, delete, modify, and reboot all clusters, and then denies permission to delete any clusters whose name starts with protected.
Amazon Redshift Management Guide Example Policies for Amazon Redshift Example 4: Allow a user to authorize and revoke snapshot access The following policy allows a user, for example UserA, to do the following: • Authorize access to any snapshot created from a cluster named shared. • Revoke snapshot access for any snapshot created from the shared cluster where the snapshot name starts with revokable.
Amazon Redshift Management Guide Example Policies for Amazon Redshift ], "Effect": "Allow" } ] } Example 5: Allow a user permissions to copy a cluster snapshot and restore a cluster from a snapshot The following policy allows a user to copy any snapshot created from the cluster named big-cluster-1, and restore any snapshot whose name starts with snapshot-for-restore.
Amazon Redshift Management Guide Example Policies for Amazon Redshift Example 6: Grant permission to Amazon Redshift and to common actions and resources for related AWS services The following example policy allows access to all actions and resources for Amazon Redshift, Amazon SNS, and Amazon CloudWatch, and allows specified actions on all related Amazon EC2 resources under the account. Note Resource-level permissions are not supported for the Amazon EC2 actions that are specified in this example policy.
Amazon Redshift Management Guide Example Policies for Amazon Redshift } ] } API Version 2012-12-01 127
Amazon Redshift Management Guide Using the Amazon Redshift Management Interfaces Accessing Amazon Redshift Clusters and Databases There are several management tools and interfaces you can use to create, manage, and delete Amazon Redshift clusters and the databases within the clusters. • You work with Amazon Web Services management tools and interfaces to create, manage, and delete Amazon Redshift clusters.
Amazon Redshift Management Guide Using the AWS SDK for Java • Signing an HTTP Request (p. 131) • Setting Up the Amazon Redshift CLI (p. 134) Amazon Redshift supports several management interfaces that you can use to use to create, manage, and delete Amazon Redshift clusters; the AWS SDKs, the AWS command line interface, and the Amazon Redshift management API. Amazon Redshift QUERY API — is a Amazon Redshift management API you can call by submitting a Query request.
Amazon Redshift Management Guide Using the AWS SDK for Java The AmazonRedshiftClient class defines methods that map to underlying Amazon Redshift Query API actions. (These actions are described in the Amazon Redshift API Reference). When you call a method, you must create a corresponding request object and response object. The request object includes information that you must pass with the actual request. The response object include information returned from Amazon Redshift in response to the request.
Amazon Redshift Management Guide Signing an HTTP Request 2. Put the code that you want to run in a .java file, and save the file in the src folder. To illustrate the process, we use the code from Managing Cluster Security Groups Using the AWS SDK for Java (p. 52) so that the file in the src directory is CreateAndModifyClusterSecurityGroup.java. 3. Compile the code. javac -cp sdk/lib/aws-java-sdk-1.3.18.jar -d bin src\CreateAndModifyCluster SecurityGroup.
Amazon Redshift Management Guide Signing an HTTP Request function includes the text of your request and your secret access key. The hash function returns a hash value that you include in the request as your signature. The signature is part of the Authorization header of your request. Note For API access, you need an access key ID and secret access key. For more information about creating access keys, see How Do I Get Security Credentials? in the AWS General Reference.
Amazon Redshift Management Guide Signing an HTTP Request &NodeType=dw.hs1.xlarge &Version=2012-12-01 &x-amz-algorithm=AWS4-HMAC-SHA256 &x-amz-credential=AKIAIOSFODNN7EXAMPLE/20121207/us-east-1/redshift/aws4_re quest &x-amz-date=20121207T000000Z &x-amz-signedheaders=content-type;host;x-amz-date The canonical form of the request calculated for Task 1: Create a Canonical Request (p. 132) is: POST / content-type:application/x-www-form-urlencoded; charset=utf-8 host:redshift.us-east-1.amazonaws.
Amazon Redshift Management Guide Setting Up the Amazon Redshift CLI Authorization: AWS4-HMAC-SHA256 Credential=AKIAIOSFODNN7EXAMPLE/20121207/useast-1/redshift/aws4_request, SignedHeaders=content-type;host;x-amz-date, Signature=9a6b557aa9f38dea83d9215d8f0eae54100877f3e0735d38498d7ae489117920 Setting Up the Amazon Redshift CLI This section explains how to set up and run the AWS CLI command line tools for use in managing Amazon Redshift.
Amazon Redshift Management Guide Setting Up the Amazon Redshift CLI • Step 6: Delete Your Sample Cluster (p. 138) To help you get started using the command line interface, this section shows how to perform basic administrative tasks for an Amazon Redshift cluster. These tasks are very similar to those in the Amazon Redshift Getting Started Guide, but they are focused on the command line interface rather than the Amazon Redshift console.
Amazon Redshift Management Guide Setting Up the Amazon Redshift CLI Parameter Name Parameter Value for This Exercise Master Password TopSecret1 Node Type dw1.xlarge or the node size that you want to use. For more information, see About Clusters and Nodes (p. 6) Cluster Type single-node To create your cluster, type the following command: aws redshift create-cluster --cluster-identifier examplecluster --master-username masteruser --master-user-password TopSecret1 --node-type dw1.
Amazon Redshift Management Guide Setting Up the Amazon Redshift CLI To allow network ingress to your cluster, type the following command: aws redshift authorize-cluster-security-group-ingress --cluster-security-groupname default --cidrip 192.0.2.0/24 Ingress Rules for EC2 Instances If you are accessing your cluster from an Amazon EC2 instance, you will need to authorize an Amazon EC2 security group.
Amazon Redshift Management Guide Connecting to a Cluster Step 5: Create Tables, Upload Data, and Try Example Queries For information about creating tables, uploading data, and issuing queries, go to the Amazon Redshift Getting Started Guide. Step 6: Delete Your Sample Cluster After you have launched a cluster and it is available for use, you are billed for the time the cluster is running, even if you are not actively using it. When you no longer need the cluster, you can delete it.
Amazon Redshift Management Guide Configuring Connections in Amazon Redshift • Configure an ODBC Connection (p. 144) • Configure Security Options for Connections (p. 158) JDBC and ODBC Drivers for Amazon Redshift To work with data in your cluster, you need JDBC or ODBC drivers for connectivity from your client computer or instance. Code your applications to use JDBC or ODBC data access APIs, and use SQL client tools that support either JDBC or ODBC.
Amazon Redshift Management Guide Configuring Connections in Amazon Redshift Configure a JDBC Connection You can use a JDBC connection to connect to your Amazon Redshift cluster from many third-party SQL client tools. To do this, you need to download a JDBC driver. Follow the steps in this section if you want to use a JDBC connection. Topics • Download the Amazon Redshift JDBC Driver (p. 140) • Obtain the JDBC URL (p. 140) • JDBC Driver Configuration Options (p.
Amazon Redshift Management Guide Configuring Connections in Amazon Redshift jdbc:redshift://endpoint:port/database Note A JDBC URL specified with the former format of jdbc:postgresql://endpoint:port/database will still work. Field Value endpoint The endpoint of the Amazon Redshift cluster. port The port number that you specified when you launched the cluster. If you have a firewall, ensure that this port is open for you to use. database The database that you created for your cluster.
Amazon Redshift Management Guide Configuring Connections in Amazon Redshift JDBC Driver Configuration Options To control the behavior of the Amazon Redshift JDBC driver, you can append the configuration options described in the following table to the JDBC URL . JDBC Option Matching PostgreSQL Option Exists? Default Value Description AuthMech No DISABLE The authentication mechanism to use. The following values are possible: • DISABLE—Establish a non-SSL connection.
Amazon Redshift Management Guide Configuring Connections in Amazon Redshift JDBC Option Matching PostgreSQL Option Exists? loglevel Yes Default Value Description The amount of logging information output by the driver. By default, no logging is performed. Information will be output to the location specified by the LogStream or LogValue option in the driver manager. The following values are possible: • 2 (DEBUG)—Log a lot of detailed information. • 1 (INFO)—Log fewer details.
Amazon Redshift Management Guide Configuring Connections in Amazon Redshift JDBC Option Matching PostgreSQL Option Exists? UID Yes Default Value Description The user name to use to connect to the Amazon Redshift server. Configure an ODBC Connection You can use an ODBC connection to connect to your Amazon Redshift cluster from many third-party SQL client tools and applications. To do this, you need to set up the connection on your client computer or Amazon EC2 instance.
Amazon Redshift Management Guide Configuring Connections in Amazon Redshift Field Value UID The user name of a user account that has permission to connect to the database. This value is a database permission, not an Amazon Redshift permission, although you can use the master user account that you set up when you launched the cluster. PWD The password for the user account to connect to the database. Port The port number that you specified when you launched the cluster.
Amazon Redshift Management Guide Configuring Connections in Amazon Redshift Install and Configure the Amazon Redshift ODBC Driver on Microsoft Windows Operating Systems System Requirements You install the Amazon Redshift ODBC driver on client computers accessing an Amazon Redshift data warehouse.
Amazon Redshift Management Guide Configuring Connections in Amazon Redshift 3. Select the System DSN tab if you want to configure the driver for all users on the computer, or the User DSN tab if you want to configure the driver for your user account only. 4. Click Add. The Create New Data Source window opens. 5. Select the Amazon Redshift ODBC driver, and then click Finish. The Amazon Redshift ODBC Driver DSN Setup window opens. 6.
Amazon Redshift Management Guide Configuring Connections in Amazon Redshift • Retrieve Entire Result Into Memory. Select this option if you want query results to be returned all at once to the SQL client tool or application. The default is enabled. 10. In Logging Options, specify values for the following: • Log Level. Select an option to specify whether to enable logging and the level of detail that you want captured in the logs.
Amazon Redshift Management Guide Configuring Connections in Amazon Redshift • unixODBC 2.3.0 or later. For more information about the unixODBC driver manager and links to download it, go to the unixODBC website. • An Amazon Redshift master user or user account to connect to the database Installing the Amazon Redshift Driver on Linux Operating Systems Use the steps in this section to download and install the Amazon Redshift ODBC drivers on a supported Linux distribution.
Amazon Redshift Management Guide Configuring Connections in Amazon Redshift zypper install AmazonRedshiftODBC-1.x.x.xxxx-x.x86_64.rpm Important When you have finished installing the drivers, configure them for use on your system. For more information on driver configuration, see Configure the ODBC Driver on Linux and Mac OS X Operating Systems (p. 151).
Amazon Redshift Management Guide Configuring Connections in Amazon Redshift Configure the ODBC Driver on Linux and Mac OS X Operating Systems On Linux and Mac OS X operating systems, you use an ODBC driver manager to configure the ODBC connection settings. ODBC driver managers use configuration files to define and configure ODBC data sources and drivers. The ODBC driver manager that you use depends on the operating system that you use.
Amazon Redshift Management Guide Configuring Connections in Amazon Redshift Database=database_name locale=locale The following example shows the configuration for odbc.ini on Linux operating systems: [ODBC Data Sources] Amazon_Redshift_x32=Amazon Redshift (x86) Amazon_Redshift_x64=Amazon Redshift (x64) [Amazon Redshift (x86)] Driver=/opt/amazon/redshiftodbc/lib/32/libamazonredshiftodbc32.so Host=examplecluster.abc123xyz789.us-west-2.redshift.amazonaws.
Amazon Redshift Management Guide Configuring Connections in Amazon Redshift Use the following format on Linux operating systems: [ODBC Drivers] driver_name=Installed ... [driver_name] Description=driver_description Driver=path/driver_file ... The following example shows the odbcinst.
Amazon Redshift Management Guide Configuring Connections in Amazon Redshift Configuring Environment Variables for Driver Configuration Files In order for the Amazon Redshift ODBC driver to function properly, you need to set a number of environmental variables, as described following. Set an environment variable to specify the path to the driver manager libraries: • On Linux, set LD_LIBRARY_PATH to point to the directory containing the driver manager libraries.
Amazon Redshift Management Guide Configuring Connections in Amazon Redshift ODBC Driver Configuration Options You can use the configuration options described in the following table to control the behavior of the Amazon Redshift ODBC driver. In Windows, you typically set driver options when you configure a DSN. You can also set driver options in the connection string when you connect programatically, or by adding or changing registry keys in HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\your_DSN.
Amazon Redshift Management Guide Configuring Connections in Amazon Redshift ODBC Option Matching PostgreSQL option exists? Default Value Description KeepAliveTime Yes 0 The number of seconds of inactivity before the driver sends a TCP keepalives packet. When this option is set to 0, the driver uses the TCP/IP system default for this setting. KeepAliveInterval Yes 0 The number of seconds between each TCP keepalive retransmission. Locale No en-US The locale to use for error messages.
Amazon Redshift Management Guide Configuring Connections in Amazon Redshift ODBC Option Matching PostgreSQL option exists? Default Value Description TextAsLongVarchar Yes 0 When this option is enabled (1), the driver returns TEXT columns as LONG VARCHAR data. When this option is disabled (0), the driver returns TEXT columns as TEXT data. UID Yes UseDeclareFetch Yes The user name to use to connect to the Amazon Redshift server.
Amazon Redshift Management Guide Configuring Connections in Amazon Redshift ODBC Option Matching PostgreSQL option exists? Default Value Description UseUnicode No 0 When this option is enabled (1), the driver returns data as Unicode character types: • CHAR is returned as SQL_WCHAR. • VARCHAR is returned as SQL_WVARCHAR. • TEXT is returned as SQL_WLONGVARCHAR. When this option is disabled (0), the driver returns data as regular SQL types: • CHAR is returned as SQL_CHAR.
Amazon Redshift Management Guide Configuring Connections in Amazon Redshift Amazon Redshift supports the Elliptic Curve Diffie—Hellman Ephemeral (ECDHE) key agreement protocol. With ECDHE, the client and server each have an elliptic curve public-private key pair that is used to establish a shared secret over an insecure channel.
Amazon Redshift Management Guide Configuring Connections in Amazon Redshift sslmode (cli- require_SSL ent) (server) Result verify-ca The connection is encrypted and the server certificate is verified. false Connect Using the Server Certificate with ODBC on Microsoft Windows If you want to connect to your cluster using SSL and the server certificate, you need to download the certificate to your client computer or Amazon EC2 instance, and then configure the ODBC DSN. 1.
Amazon Redshift Management Guide Configuring Connections in Amazon Redshift ${JAVA_HOME}/bin/keytool -keystore -alias -import -file Where is the keystore that your client application uses, and and correspond to the descriptions as previously described.You will be prompted to enter and re-enter a password that will be used for working with the keystore. Make note of this password for later use.
Amazon Redshift Management Guide Connecting to Clusters From Client Tools and Code The following is an example connection string for SQL Workbench/J: jdbc:redshift://examplecluster.abc123xyz789.us-west-2.redshift.amazon aws.com:5439/dev?ssl=true&sslfactory=com.amazon.redshift.ssl.NonValidatingFactory For more information about JDBC connections, see Obtain the JDBC URL (p. 140).
Amazon Redshift Management Guide Connecting to Clusters From Client Tools and Code 2. Go to the Installing and starting SQL Workbench/J page. Follow the instructions for installing SQL Workbench/J on your system. Note SQL Workbench/J requires the Java Runtime Environment (JRE) be installed on your system. Ensure you are using the correct version of the JRE required by the SQL Workbench/J client.
Amazon Redshift Management Guide Connecting to Clusters From Client Tools and Code 6. 7. If the Please select one driver dialog box displays, select com.amazon.redshift.jdbc4.Driver or com.amazon.redshift.jdbc41.Driver and click OK. SQL Workbench/J automatically completes the Classname box. Leave the Sample URL box blank, and then click OK. In the Driver box, select the driver you just added. In URL, copy the JDBC URL from the Amazon Redshift console and paste it here. 8.
Amazon Redshift Management Guide Connecting to Clusters From Client Tools and Code Test the SQL Workbench/J Connection After you configure your JDBC or ODBC connection, you can test the connection by running an example query. 1.
Amazon Redshift Management Guide Connecting to Clusters From Client Tools and Code Connect to Your Cluster by Using the psql Tool After you create an Amazon Redshift cluster, you can use psql, a terminal-based front end from PostgreSQL, to query the data in your cluster. You can type the queries interactively or read them from a file. To connect from psql, you must specify the cluster endpoint, database, and port. Note Amazon Redshift does not provide the psql tool; it is installed with PostgreSQL.
Amazon Redshift Management Guide Connecting to Clusters From Client Tools and Code 3. At a command prompt, specify the connection information by using either command line parameters or a connection information string. To use parameters: psql -h -U -d -p Where: • is the Endpoint you recorded in the previous step. • is a user ID with permissions to connect to the cluster. • is the Database Name you recorded in the previous step.
Amazon Redshift Management Guide Connecting to Clusters From Client Tools and Code For more information about connection information string parameters, see http://www.postgresql.org/ docs/8.4/static/libpq-connect.html. To connect by using a certificate 1. Save the public key from https://s3.amazonaws.com/redshift-downloads/redshift-ssl-ca-cert.pem as a .pem file to your computer. If you do a File\Save as using Internet Explorer, specify the file type as Text file (*.txt) and delete the .txt extension.
Amazon Redshift Management Guide Connecting to Clusters From Client Tools and Code 4. At the psql password prompt, enter the password for the user. You are connected to the cluster, and you can interactively enter commands. Connect to Your Cluster Programmatically Topics • Connecting to a Cluster by Using Java (p. 169) • Connecting to a Cluster by Using .NET (p. 171) This section explains how to connect to your cluster programmatically.
Amazon Redshift Management Guide Connecting to Clusters From Client Tools and Code Example : Connect to a Cluster by Using Java The following example connects to a cluster and runs a sample query that returns system tables. It is not necessary to have data in your database to use this example. If you are using a server certificate to authenticate your cluster, you can restore the line that uses the keystore, which is commented out: props.
Amazon Redshift Management Guide Connecting to Clusters From Client Tools and Code //Retrieve two columns. String catalog = rs.getString("table_catalog"); String name = rs.getString("table_name"); //Display values. System.out.print("Catalog: " + catalog); System.out.println(", Name: " + name); } rs.close(); stmt.close(); conn.close(); }catch(Exception ex){ //For convenience, handle all errors here. ex.printStackTrace(); }finally{ //Finally block to close resources. try{ if(stmt!=null) stmt.
Amazon Redshift Management Guide Connecting to Clusters From Client Tools and Code Example Connect to a Cluster by Using .NET The following example connects to a cluster and runs a sample query that returns system tables. It does not show server authentication. It is not necessary to have data in your database to use this example. This example uses the System.Data.Odbc Namespace, a .NET Framework Data Provider for ODBC. using System; using System.Data; using System.Data.Odbc; namespace redshift.amazon.com.
Amazon Redshift Management Guide Troubleshooting Connection Issues in Amazon Redshift // Make a connection using the psqlODBC provider. OdbcConnection conn = new OdbcConnection(connString); conn.Open(); // Try a simple query. string sql = query; OdbcDataAdapter da = new OdbcDataAdapter(sql, conn); da.Fill(ds); dt = ds.Tables[0]; foreach (DataRow row in dt.Rows) { Console.WriteLine(row["table_catalog"] + ", " + row["table_name"]); } conn.Close(); Console.ReadKey(); } catch (Exception ex) { Console.Error.
Amazon Redshift Management Guide Troubleshooting Connection Issues in Amazon Redshift Your client connection to the database appears to hang or timeout when running long queries, such as a COPY command. In this case, you might observe that the Amazon Redshift console displays that the query has completed, but the client tool itself still appears to be running the query. The results of the query might be missing or incomplete depending on when the connection stopped.
Amazon Redshift Management Guide Troubleshooting Connection Issues in Amazon Redshift sysctl sysctl sysctl sysctl net.inet.tcp.keepidle net.inet.tcp.keepintvl net.inet.tcp.keepinit net.inet.tcp.always_keepalive To change DSN timeout settings You can set keep-alive behavior at the DSN level if you choose. You do this by adding or modifying the following parameters in the odbc.ini file: KeepAlivesCount The number of TCP keep-alive packets that can be lost before the connection is considered broken.
Amazon Redshift Management Guide Troubleshooting Connection Issues in Amazon Redshift • If you created your Amazon Redshift cluster in a VPC, you need to add your client CIDR/IP address to the VPC security group in Amazon VPC. For more information about configuring VPC security groups for your cluster, see Managing Clusters in an Amazon Virtual Private Cloud (VPC) (p. 32).
Amazon Redshift Management Guide Troubleshooting Connection Issues in Amazon Redshift Review the MTU value for the Ethernet adapter in the output. If the value is not 1500, run the following command to set it: netsh interface ipv4 set subinterface "Ethernet" mtu=1500 store=persistent After you set this value, restart your computer for the changes to take effect.
Amazon Redshift Management Guide Troubleshooting Connection Issues in Amazon Redshift 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 'SAVEPOINT%' GROUP BY ut.userid, u.
Amazon Redshift Management Guide Overview Monitoring Amazon Redshift Cluster Performance Topics • Overview (p. 179) • Summary of Amazon Redshift Performance Data (p. 180) • Working with Performance Data in the Amazon Redshift Console (p. 183) Overview Amazon Redshift provides performance metrics and data so that you can track the health and performance of your clusters and databases.
Amazon Redshift Management Guide Summary of Performance Data (as compared to Bytes/s in Amazon CloudWatch), which is a more relevant unit for the typical storage space of a node. • Performance data for the nodes of a cluster can easily be displayed together on the same graph so that you can easily monitor the performance of all nodes of a cluster; however, you can also view performance data per node.
Amazon Redshift Management Guide Amazon Redshift CloudWatch Metrics Metric Description DatabaseConnections The number of database connections to a cluster. Units: Count Dimensions: ClusterIdentifier HealthStatus Indicates the health of the cluster. Every minute the cluster connects to its database and performs a simple query. If it is able to perform this operation successfully, the cluster is considered healthy. Otherwise, the cluster is unhealthy.
Amazon Redshift Management Guide Amazon Redshift Query/Load Performance Data Metric Description PercentageDiskSpaceUsed The percent of disk space used. Units: Percent Dimensions: NodeID, ClusterIdentifier ReadIOPS The average number of disk read operations per second. Units: Count/second Dimensions: NodeID ReadLatency The average amount of time taken for disk read I/O operations. Units: Seconds Dimensions: NodeID ReadThroughput The average number of bytes read from disk per second.
Amazon Redshift Management Guide Working with Performance Data The following table describes different aspects of query and load data you can access in the Amazon Redshift console. Query/Load Data Description Query summary A list of queries in a specified time period. The list can be sorted on values such as query ID, query run time, and status. Access this data in the Queries tab of the cluster detail page.
Amazon Redshift Management Guide Viewing Cluster Performance Data Health of the cluster (e.g. healthy), whether the cluster is undergoing maintenance, and count of recent events. From the dashboard, select a cluster to work with and go to the details of the cluster. From this page you can access the Events+Alarms, Performance, Queries, and Loads tabs which contain the performance data. These tabs are shown in the following example.
Amazon Redshift Management Guide Viewing Cluster Performance Data By default, the performance view displays cluster performance over the past hour. If you need to fine tune the view you have filters that you can use as described in the following table. To... Use this filter... Change the time range for which data is dis- Select a time range from the Time Range drop played down. By default, the last hour is shown. Change the period for which data is displayed Select a period from the Period drop down.
Amazon Redshift Management Guide Viewing Query Performance Data The following example shows the NetworkReceiveThroughput for a cluster with two nodes. It shows a line for the leader and two compute nodes. Note that the leader node metrics is flat and is not of interest since data is only loaded on the compute nodes. The example shows that one long query ran in the time period shown. On the Queries graph the cursor is positioned over the long running query and the Query ID is displayed on the right.
Amazon Redshift Management Guide Viewing Query Performance Data Use the button bar, shown following, to refresh the data in the table, to configure the columns that appear in the table, or to open the Amazon Redshift documentation. To view query performance data 1. 2. 3. 4. Sign in to the AWS Management Console, and open the Amazon Redshift console at https:// console.aws.amazon.com/redshift. In the left navigation, click Clusters.
Amazon Redshift Management Guide Viewing Query Performance Data 3. 4. In the Cluster list, click the cluster you want to open. Click the Queries tab. 5. Do one of the following: • In the list, select the query or queries that you want to terminate, and click Terminate Query. • In the list, open a query if you want to review the query information first, and then click Terminate Query. 6. In the Terminate Queries dialog box, click Confirm.
Amazon Redshift Management Guide Viewing Query Performance Data Analyzing Query Execution The Query Execution Details section of the Query view provides information about the way the query was processed. This section combines data from SVL_QUERY_REPORT, STL_EXPLAIN, and other system views and tables. The Query Execution Details section has two tabs: • Plan. This tab shows the explain plan for the query that is displayed. • Actual.
Amazon Redshift Management Guide Viewing Query Performance Data The Avg statistic shows the average execution time for the step across data slices, and the percentage of the total query runtime that represents. The Max statistic shows the longest execution time for the step on any of the data slices, and the skew. The skew is the difference between the average and maximum execution times for the step.
Amazon Redshift Management Guide Viewing Query Performance Data The information on the Plan tab is analogous to running the EXPLAIN command in the database. The EXPLAIN command examines your query text, and returns the query plan. You use this information to evaluate queries, and revise them for efficiency and performance if necessary. The EXPLAIN command doesn’t actually run the query.
Amazon Redshift Management Guide Viewing Query Performance Data For more information about the difference between the explain plan and system views and logs, go to Mapping the Query Plan to System Views in the Amazon Redshift Database Developer Guide in the Amazon Redshift Database Developer Guide. Viewing Query Execution Details Using the Console Use the following procedure to look at the details of query execution. To view query execution details 1.
Amazon Redshift Management Guide Viewing Query Performance Data 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 sorted by Run time to find the query with the maximum run time. 6. In the Query page that opens, scroll to the Cluster Performance During Query Execution section to view cluster metrics.
Amazon Redshift Management Guide Viewing Cluster Metrics During Load Operations Viewing Cluster Metrics During Load Operations When you view cluster performance during load operations, you can identify queries that are consuming resources and take action to mitigate their effect. You can terminate a load if you don't want it to run to completion. Note The ability to terminate queries and loads in the Amazon Redshift console requires specific permission.
Amazon Redshift Management Guide Creating an Alarm 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 Viewing Query Performance Data (p. 186). You can review the details of the query and see the values of cluster metrics during the load operation. To terminate a running load 1. 2. 3. 4. 5. Sign in to the AWS Management Console, and open the Amazon Redshift console at https:// console.aws.amazon.com/redshift.
Amazon Redshift Management Guide Creating an Alarm 3. In the Cluster list, select the cluster for which you want to view cluster performance during query execution. 4. Select the Events+Alarms tab. 5. Click Create Alarm. 6. In the Create Alarm dialog box, configure an alarm, and click Create. Note The notifications that are displayed the Send a notification to box are your Amazon Simple Notification Service (Amazon SNS) topics.
Amazon Redshift Management Guide Working with Performance Metrics in the Amazon CloudWatch Console 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 the alarm as shown in the following example. After a few moments the state will turn to OK. 8.
Amazon Redshift Management Guide Working with Performance Metrics in the Amazon CloudWatch Console • First, you specify the metric dimension to work with. A dimension is a name-value pair that helps you to uniquely identify a metric. The dimensions for Amazon Redshift are ClusterIdentifier and NodeID. In the Amazon CloudWatch console, the Redshift Cluster and Redshift Node views are provided to easily select cluster and node-specific dimensions.
Amazon Redshift Management Guide Overview Amazon Redshift Events Topics • Overview (p. 199) • Viewing Events Using the Console (p. 199) • Viewing Events Using the AWS SDK for Java (p. 201) • View Events Using the Amazon Redshift CLI and API (p. 202) • Amazon Redshift Event Notifications (p. 202) Overview Amazon Redshift tracks events and retains information about them for a period of several weeks in your AWS account.
Amazon Redshift Management Guide Filtering Events An event source type indicates what the event was about. The following source types are possible: Cluster, Cluster Parameter Group, Cluster Security Group, and Snapshot. Filtering Events Sometimes you want to find a specific category of events or events for a specific cluster. In these cases, you can filter the events displayed. To filter events 1. 2. 3. Sign into the AWS Management Console and open the Amazon Redshift console at https:// console.aws.
Amazon Redshift Management Guide Viewing Events Using the AWS SDK for Java Viewing Events Using the AWS SDK for Java The following example lists the events for a specified cluster and specified event source type. The example shows how to use pagination. For step-by-step instructions to run the following example, see Running Java Examples for Amazon Redshift Using Eclipse (p. 130). You need to update the code and specify a cluster identifier and event source type. import java.io.IOException; import java.
Amazon Redshift Management Guide View Events Using the Amazon Redshift CLI and API } } while (marker != null); } static void printEvent(Event event) { if (event == null) { System.out.println("\nEvent object is null."); return; } System.out.println("\nEvent metadata:\n"); System.out.format("SourceID: %s\n", event.getSourceIdentifier()); System.out.format("Type: %s\n", event.getSourceType()); System.out.format("Message: %s\n", event.getMessage()); System.out.format("Date: %s\n", event.
Amazon Redshift Management Guide Overview text message, or a call to an HTTP endpoint. For example, all regions support email notifications, but SMS notifications can only be created in the US East (N. Virginia) region. When you create an event notification subscription, you specify one or more event filters. Amazon Redshift sends notifications through the subscription any time an event occurs that matches all of the filter criteria.
Amazon Redshift Management Guide Amazon Redshift Event Categories and Event Messages • 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 send notifications for only monitoring events with a severity of ERROR from my-cluster-1. • Subscription specifies a source type of cluster, a category of Configuration, and a severity of INFO.
Amazon Redshift Management Guide Amazon Redshift Event Categories and Event Messages Amazon Redshift Category Event ID Event Severity Description Configuration REDSHIFT-EVENT1001 INFO Your Amazon Redshift cluster [cluster name] was modified to use parameter group [parameter group name] at [time]. Configuration REDSHIFT-EVENT1500 ERROR The Amazon VPC [VPC name] does not exist. Your configuration changes for cluster [cluster name] were not applied.
Amazon Redshift Management Guide Amazon Redshift Event Categories and Event Messages Amazon Redshift Category Event ID Event Severity Description Management REDSHIFT-EVENT2007 INFO The resize for your Amazon Redshift cluster [cluster name] is in progress. Your cluster is in read-only mode. Management REDSHIFT-EVENT2008 INFO Your restore operation to create a new Amazon Redshift cluster [cluster name] snapshot [snapshot name] was started at [time].
Amazon Redshift Management Guide Amazon Redshift Event Categories and Event Messages Amazon Redshift Category Event ID Event Severity Description Monitoring REDSHIFT-EVENT3508 ERROR The Amazon Redshift cluster [cluster name] cannot be created using EIP [IP address]. The EIP cannot be found. Monitoring REDSHIFT-EVENT3509 ERROR Cross-region snapshot copy is not enabled for cluster [cluster name].
Amazon Redshift Management Guide Amazon Redshift Event Categories and Event Messages Amazon Redshift Category Event ID Event Severity Description Security REDSHIFTEVENT-4002 INFO Cluster security group [group name] was created. Security REDSHIFTEVENT-4003 INFO Cluster security group [group name] was deleted. Security REDSHIFTEVENT-4004 INFO Cluster security group [group name] was changed at [time]. Changes will be automatically applied to all associated clusters.
Amazon Redshift Management Guide Managing Event Notifications Using the Amazon Redshift Console Amazon Red- Event ID shift Category Event Severity Description Monitoring ERROR The user snapshot [snapshot name] for your Amazon Redshift cluster [cluster name] failed at [time]. The team is investigating the issue. Please visit the AWS Management Console to retry the operation.
Amazon Redshift Management Guide Managing Event Notifications Using the Amazon Redshift Console ii. iii. iv. Select an event severity from the Severity dropdown menu. If you select Any, events with severities of either INFO or ERROR are published. If you select Error, only events with a severity of ERROR are published. Select a source type from the Source Type dropdown menu.
Amazon Redshift Management Guide Managing Event Notifications Using the Amazon Redshift Console e. Use one of three tabs to specify the Amazon SNS topic the subscription will use to publish events. i. ii. To select an existing Amazon SNS topic by from a list, select the Use Existing Topic tab and select the topic from the list. To specify an existing Amazon SNS topic by its Amazon Resource Name (ARN), select the Provide Topic ARN tab and specify the ARN in the ARN: box.
Amazon Redshift Management Guide Managing Event Notifications Using the Amazon Redshift Console 5. To create the subscription, click Create. To delete the definition without creating a subscription, click Cancel. To return to the subscription settings, click Previous. Listing Your Amazon Redshift Event Notification Subscriptions You can list your current Amazon Redshift event notification subscriptions. To list your current Amazon Redshift event notification subscriptions 1. 2.
Amazon Redshift Management Guide Managing Event Notifications Using the Amazon Redshift Console a. Use the Subscription Settings pane to change the event filter criteria. As you select the criteria, the Subscribed Events list displays the Amazon Redshift events that match the criteria. Do the following: i. ii. iii. iv. b. c. d. Select one or more event categories from the Categories box. To specify all categories, select the Category button.
Amazon Redshift Management Guide Managing Event Notifications Using the Amazon Redshift CLI and API Removing a Source Identifier from an Amazon Redshift Event Notification Subscription You can remove a source identifier (the Amazon Redshift source generating the event) from a subscription if you no longer want to be notified of events for that source.
Amazon Redshift Management Guide Overview Database Audit Logging Topics • Overview (p. 215) • Amazon Redshift Logs (p. 215) • Enabling Logging (p. 217) • Managing Log Files (p. 218) • Troubleshooting Amazon Redshift Audit Logging (p. 220) • Using AWS CloudTrail for Amazon Redshift (p. 221) • Configuring Auditing Using the Console (p. 221) • Configuring Logging by Using the Amazon Redshift CLI and API (p. 223) Overview Amazon Redshift logs information about connections and user activities in your database.
Amazon Redshift Management Guide Connection Log The user activity log is useful primarily for troubleshooting purposes. It tracks information about the types of queries that both the users and the system perform in the database. The connection log and user log both correspond to information that is stored in the system tables in your database. You can use the system tables to obtain the same information, but the log files provide an easier mechanism for retrieval and review.
Amazon Redshift Management Guide User Activity Log Column name Description action Action that occurred. Valid values: • Alter • Create • Drop • Rename usecreatedb If true (1), indicates that the user has create database privileges. usesuper If true (1), indicates that the user is a superuser. usecatupd If true (1), indicates that the user can update system catalogs. valuntil Password expiration date. pid Process ID. xid Transaction ID. recordtime Time in UTC that the query started.
Amazon Redshift Management Guide Managing Log Files The connection log, user log, and user activity log are enabled together by using the AWS Management Console, the Amazon Redshift API Reference, or the AWS Command Line Interface (AWS CLI). For the user activity log, you must also enable the enable_user_activity_logging database parameter.
Amazon Redshift Management Guide Bucket Permissions for Amazon Redshift Audit Logging Region Account ID Asia Pacific (Singapore) region 361669875840 Asia Pacific (Sydney) region 762762565011 The bucket policy uses the following format, where BucketName and AccountId are placeholders for your own values: { "Version": "2012-10-17", "Statement": [ { "Sid": "Put bucket policy needed for audit logging", "Effect": "Allow", "Principal": { "AWS": "arn:aws:iam::AccountId:user/logs" }, "Action": "s3:PutObject
Amazon Redshift Management Guide Bucket Structure for Amazon Redshift Audit Logging "Action": "s3:GetBucketAcl", "Resource": "arn:aws:s3:::AuditLogs" } ] } For more information about creating Amazon S3 buckets and adding bucket policies, go to Creating a Bucket and Editing Bucket Permissions in the Amazon Simple Storage Service Console User Guide.
Amazon Redshift Management Guide Using AWS CloudTrail for Amazon Redshift Using AWS CloudTrail for Amazon Redshift This service supports AWS CloudTrail, which is a service that records AWS calls for your AWS account and delivers log files to an Amazon S3 bucket. By using information collected by CloudTrail, you can determine what requests were successfully made to AWS services, who made the request, when it was made, and so on.
Amazon Redshift Management Guide Modifying the Bucket for Audit Logging 7. 8. Optionally, in the S3 Key Prefix box, type a prefix to add to the S3 bucket. Click Save. After you configure audit logging, the Cluster details page updates to display information about the logging configuration. On the Cluster details page, under Backup, Maintenance, and Logging, click Go to the S3 console to navigate to the bucket. Modifying the Bucket for Audit Logging 1. 2.
Amazon Redshift Management Guide Disabling Audit Logging Using the Console Disabling Audit Logging Using the Console 1. 2. Sign into the AWS Management Console and open the Amazon Redshift console at https:// console.aws.amazon.com/redshift. In the navigation pane, click Clusters. 3. 4. In the list, click the cluster for which you want to disable logging. In the cluster details page, click Database, and then click Configure Audit Logging. 5.
Amazon Redshift Management Guide Overview Tutorial: Resizing Clusters in Amazon Redshift Topics • Overview (p. 224) • Resize Operation Overview (p. 224) • Snapshot, Restore, and Resize Operation Overview (p. 225) • Tutorial: Using the Resize Operation to Resize a Cluster (p. 226) • Tutorial: Using the Snapshot, Restore, and Resize Operations to Resize a Cluster (p.
Amazon Redshift Management Guide Snapshot, Restore, and Resize Operation Overview the amount of data and the number of nodes in the smaller cluster. It can take anywhere from a couple of hours to a couple of days. When you start the resize operation, Amazon Redshift puts the existing cluster into read-only mode until the resize finishes. During this time, you can only run queries that read from the database; you cannot run any queries that write to the database, including read-write queries.
Amazon Redshift Management Guide Tutorial: Using the Resize Operation to Resize a Cluster 4. Resize the target cluster. Select the new node type, number of nodes, and other settings for the target cluster. 5. Review the loads from your ETL processes that occurred after you took a snapshot of the source cluster.You’ll need to reload the same data in the same order into the target cluster.
Amazon Redshift Management Guide Prerequisites Prerequisites Before you start this tutorial, make sure that you have the following prerequisites: • A sample cluster. In this example, you’ll start with the sample cluster that you created in the Amazon Redshift Getting Started exercise. If you don't have a sample cluster to use for this tutorial, complete the Getting Started exercise to create one and then return to this tutorial. Step 1: Resize the Cluster 1. Open the Amazon Redshift console. 2.
Amazon Redshift Management Guide Step 2: Delete the Sample Cluster Step 2: Delete the Sample Cluster After you are sure that you no longer need the sample cluster, you can delete it. In a production environment, whether you decide to keep a final snapshot depends on your data policies. In this tutorial, you’ll delete the cluster without a final snapshot because you are using sample data. Important You are charged for any clusters until they are deleted. 1. Open the Amazon Redshift console. 2.
Amazon Redshift Management Guide Prerequisites • 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 (p. 232) • Step 5: Copy Post-Snapshot Data from the Source to the Target Cluster (p. 233) • Step 6: Rename the Source and Target Clusters (p. 234) • Step 7: Delete the Source Cluster (p. 235) • Step 8: Clean Up Your Environment (p.
Amazon Redshift Management Guide Step 2: Restore the Snapshot into the Target Cluster 5. In the navigation pane, click Snapshots and verify that a new manual snapshot is being created. The snapshot status will be creating. Step 2: Restore the Snapshot into the Target Cluster 1. In the navigation pane, click Snapshots, and then select the examplecluster-source snapshot. 2. Click Restore From Snapshot. 3. In the Choose Platform window, select the platform you want to restore the cluster into.
Amazon Redshift Management Guide Step 3: Verify Data in the Target Cluster • Snapshot Identifier: check the snapshot name, examplecluster-source. • Cluster Identifier: type examplecluster-target. • Port: leave the port number as is. • Allow Version Upgrade: leave this option as Yes. • Availability Zone: select an Availability Zone. • Cluster Parameter Group: select a parameter group to use. • Cluster Security Group: select a security group or groups to use. 5. In the navigation pane, click Clusters.
Amazon Redshift Management Guide Step 4: Resize the Target Cluster FROM pg_table_def WHERE tablename = 'sales'; -- Find total sales on a given calendar date. SELECT sum(qtysold) FROM sales, date WHERE sales.dateid = date.dateid AND caldate = '2008-01-05'; -- Find top 10 buyers by quantity. SELECT firstname, lastname, total_quantity FROM (SELECT buyerid, sum(qtysold) total_quantity FROM sales GROUP BY buyerid ORDER BY total_quantity desc limit 10) Q, users WHERE Q.buyerid = userid ORDER BY Q.
Amazon Redshift Management Guide Step 5: Copy Post-Snapshot Data from the Source to the Target Cluster 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 Source to the Target Cluster For the purposes of this tutorial, this step provides a simple set of COPY statements to load data from Amazon S3 into Amazon Redshift.
Amazon Redshift Management Guide Step 6: Rename the Source and Target Clusters First, you’ll use the following COPY statements to load new data from Amazon S3 to the tables in your Amazon Redshift TICKIT database in the target cluster. copy venue from 's3:///resize/etl_venue_pipe.txt' CREDENTIALS 'aws_access_key_id=;aws_secret_access_key=' delimiter '|'; copy category from 's3:///resize/etl_cat egory_pipe.
Amazon Redshift Management Guide Step 7: Delete the Source Cluster 2. In the navigation pane, click Clusters, and then click the cluster to open. If you are using the same cluster from this tutorial, click examplecluster. 3. On the Configuration tab of the Cluster details page, click Modify in the Cluster list. 4. In the Modify Cluster window, type examplecluster-source in the New Cluster Identifier box, and then click Modify. 5.
Amazon Redshift Management Guide Step 8: Clean Up Your Environment 2. In the navigation pane, click Clusters, and then click the cluster to open. If you are using the same cluster names from this tutorial, click examplecluster-source. 3. On the Configuration tab of the Cluster details page, click Delete in the Cluster list. 4. In the Delete Cluster window, click No for Create final snapshot, and then click Delete.
Amazon Redshift Management Guide Quotas and Limits Limits in Amazon Redshift Topics • Quotas and Limits (p. 237) • Naming Constraints (p. 238) Quotas and Limits Amazon Redshift has quotas that limit the number of clusters you can create, the total number of nodes that you can provision, and the number of snapshots that you can create; these quotas are per AWS account per region. Amazon Redshift has a default quota for each of these, which are listed at AWS Service Limits.
Amazon Redshift Management Guide Naming Constraints Naming Constraints The following table describes naming constraints within Amazon Redshift. Cluster identifier • A cluster identifier must contain only lowercase characters. • It must contain from 1 to 63 alphanumeric characters or hyphens. • Its first character must be a letter. • It cannot end with a hyphen or contain two consecutive hyphens. • It must be unique for all clusters within an AWS account.
Amazon Redshift Management Guide Naming Constraints Cluster security group name • A cluster security group name must contain no more than 255 alphanumeric characters or hyphens. • It must contain only lowercase characters. • It must not be Default. • It must be unique for all security groups that are created by your AWS account. Subnet group name • A subnet group name must contain no more than 255 alphanumeric characters or hyphens. • It must contain only lowercase characters. • It must not be Default.
Amazon Redshift Management Guide Tagging Overview Tagging Resources in Amazon Redshift Topics • Tagging Overview (p. 240) • Managing Resource Tags Using the Console (p. 241) • Managing Tags Using the Amazon Redshift API (p. 243) Tagging Overview In AWS, tags are user-defined labels that consist of key-value pairs. Amazon Redshift supports tagging to provide metadata about resources at a glance, and to categorize your billing reports based on cost allocation.
Amazon Redshift Management Guide Tagging Requirements • EC2 security group • HSM connection • HSM client certificate • Parameter group • Snapshot • Subnet group Tagging Requirements Tags have the following requirements: • Keys can't be prefixed with aws:. • Keys must be unique per tag set. • A key must be between 1 and 128 allowed characters. • A value must be between 0 and 256 allowed characters. • Values do not need to be unique per tag set.
Amazon Redshift Management Guide How To Open the Manage Tags Window After you add key pairs to a resource, they display in the Applied Tags section; this is the tag set for the resource. You can modify a tag value, but you can't modify the key name. You can, however, delete a key if you no longer need it for the resource. You can view the tags for a resource by reviewing the Applied Tags section of the Manage Tags window.
Amazon Redshift Management Guide How to Manage Tags in the Amazon Redshift Console Resource Description HSM Certificate In the left navigation pane, click Security. On the HSM Certificates tab, select a certificate from the list. Then click Manage Tags. Parameter Group In the left navigation pane, click Parameter Groups and select a parameter group from the list. Then click Manage Tags.
Amazon Redshift Management Guide Managing Tags Using the Amazon Redshift API • CreateCluster • CreateClusterParameterGroup • CreateClusterSecurityGroup • CreateClusterSnapshot • CreateClusterSubnetGroup • CreateHsmClientCertificate • CreateHsmConfiguration • DescribeClusters • DescribeClusterParameterGroups • DescribeClusterSecurityGroups • DescribeClusterSnapshots • DescribeClusterSubnetGroups • DescribeHsmClientCertificates • DescribeHsmConfigurations API Version 2012-12-01 244
Amazon Redshift Management Guide Document History The following table describes the important changes to the Amazon Redshift Management Guide. Latest documentation update: April 30, 2015 For a list of the changes to the Amazon Redshift database documentation, go to the Amazon Redshift Developer Guide Document History. Change Description Documentation Fixes Published various documentation fixes.
Amazon Redshift Management Guide Change Description Documentation Update Added a troubleshooting topic about an issue where January 26, 2015 queries fail to complete in the SQL client tool and the cluster when a transaction encounters an error. For more information, see Queries Appear to Hang in the Client and the Cluster (p. 177).
Amazon Redshift Management Guide Change Description Documentation Update Added links to the Microsoft Visual C++ 2010 Redistribut- October 30, 2014 able Packages that are required for Amazon Redshift to use PostgreSQL ODBC drivers. For more information, see Install and Configure the Amazon Redshift ODBC Driver on Microsoft Windows Operating Systems (p. 146). New Feature Added the ability to terminate queries and loads from the October 28, 2014 Amazon Redshift console.
Amazon Redshift Management Guide Change Description Release Date New Feature Added statement about Amazon Redshift support for the Elliptic curve Diffie—Hellman (ECDH) key agreement protocol. For more information, see Connect Using SSL (p. 158). April 18, 2014 Documentation Update Revised and reorganized the topics in the Connecting to April 15, 2014 a Cluster (p. 138) section. Added more information about JDBC and ODBC connections, and a new troubleshooting section for connection issues.
Amazon Redshift Management Guide Change Description Release Date New Feature Added information about the new user activity log and the December 6, 2013 enable_user_activity_logging database parameter for the database audit logging feature in Amazon Redshift. For more information about database audit logging, see Database Audit Logging (p. 215). For more information about database parameters, see Amazon Redshift Parameter Groups (p. 56).
