Amazon Redshift Скачать руководство пользователя страница 103

Страница: 103 / 131

select * from information_schema.tables;

To use SQL Workbench/J with an ODBC driver

Install the ODBC driver on your system. In this example, we're using a 64-bit Unicode driver.

Unzip the driver package downloaded in Step 3 of

To set up SQL Workbench/J (p. 96)

and then

install the driver by using the .MSI package.

In the Amazon Redshift console, click the name of your cluster, and write down or copy the
ODBC URL from the Configuration pane. You will use portions of the URL string in step e.

In the Control Panel, open the Administrative Tools icon, and then double-click the Data Sources
(ODBC) icon.

In the ODBC Data Source Administrator dialog box, click System DSN. Click Add. Select the
Unicode driver, and then click Finish.

API Version 2012-12-01

Amazon Redshift Management Guide

Setting Up the SQL Workbench Client

Содержание Redshift

Страница 1: ...Amazon Redshift Management Guide API Version 2012 12 01...

Страница 2: ...Amazon Web Services Amazon Redshift Management Guide...

Страница 3: ...azon Route 53 Amazon S3 Amazon VPC In addition Amazon com graphics logos page headers button icons scripts and service names are trademarks or trade dress of Amazon in the U S and or other countries A...

Страница 4: ...sing the Console 63 Viewing Events Using AWS SDK for Java 65 View Events Using Amazon Redshift CLI and API 66 Monitoring Cluster Performance 67 Summary of Performance Data 68 Working with Performance...

Страница 5: ...dless of the size of the data set Amazon Redshift offers high query performance Amazon Redshift manages all the work of setting up operating and scaling a data warehouse provisioning capacity monitori...

Страница 6: ...see Monitoring Amazon Redshift Cluster Performance p 67 Control access to your Amazon Redshift resources The AWS account that creates the cluster has full access to the cluster Within your AWS accoun...

Страница 7: ...tabase developer the Amazon Redshift Database Developer Guide explains how to design build query and maintain the databases that make up your data warehouse API Version 2012 12 01 3 Amazon Redshift Ma...

Страница 8: ...r way involves minimal downtime Resizing replaces old cluster at the end of the resize operation When you submit a resize request the source cluster remains in read only mode until the resize operatio...

Страница 9: ...32 nodes 8XL clusters can contain 2 to 100 nodes For pricing information and detailed information about individual node types go the Amazon Redshift product detail page The number of nodes you choose...

Страница 10: ...switch you will notice the connection loss You must restart the query on the new cluster In a modify operation you can resize your cluster as necessary For example you can add more nodes change the no...

Страница 11: ...by step instructions go to Supported Platforms in the Amazon Elastic Compute Cloud User Guide 3 If your account supports both EC2 platforms choose the platform on which you want to deploy the Amazon...

Страница 12: ...If no clusters are configured for your account click Launch Cluster b If one or more clusters are defined for your account in the navigation pane click Clusters Under Launch an Amazon Redshift Cluster...

Страница 13: ...database instance d In the Master User Name box specify an account name for the master user of the database Master user names must meet the following conditions Must contain from 1 to 128 alphanumeri...

Страница 14: ...the Cluster Type box click a cluster type c In the Number of Compute Nodes box specify the number of nodes that the cluster will contain If you select Cluster Type as Single Node the number of comput...

Страница 15: ...ity Zone box click the availability zone where you want to provision your cluster c In the Cluster Security Groups box click a security group to associate with the cluster Provision the cluster outsid...

Страница 16: ...he cluster If everything is satisfactory click Launch Cluster to start the creation process Otherwise click Back to make any necessary modification and then click Continue to return to the Review page...

Страница 17: ...retention period All of these changes take effect only after the cluster is restarted If you decrease the automated snapshot retention period existing automated snapshots that fall outside of the new...

Страница 18: ...associate multiple security groups with a cluster you can select multiple items from the Cluster Security Groups list In the following example a cluster provisioned in a VPC is modified so that two V...

Страница 19: ...mation about cluster snapshots see Amazon Redshift Snapshots p 52 To delete a cluster 1 Sign into the AWS Management Console and open the Amazon Redshift console at https console aws amazon com redshi...

Страница 20: ...ing a Cluster When you reboot a cluster the cluster status is set to rebooting and a cluster event is created when the reboot is completed Any pending cluster modifications are applied at this reboot...

Страница 21: ...ou should wait until the resize is complete to resume working with the cluster To resize a cluster 1 Sign into the AWS Management Console and open the Amazon Redshift console at https console aws amaz...

Страница 22: ...dshift console at https console aws amazon com redshift 2 In the navigation pane click Clusters 3 Under Performance Status click the magnifying glass icon beside the cluster identifier The cluster sum...

Страница 23: ...us 1 Sign into the AWS Management Console and open the Amazon Redshift console at https console aws amazon com redshift 2 In the navigation pane click Clusters 3 Under Performance Status click the mag...

Страница 24: ...snapshot of a cluster 1 Sign into the AWS Management Console and open the Amazon Redshift console at https console aws amazon com redshift 2 In the navigation pane click Clusters 3 Under Performance S...

Страница 25: ...After you initiate the request for the cluster to be created you must wait until the cluster is in the available state before you can modify it This example uses a loop to periodically check the statu...

Страница 26: ...eateResponse client createCluster request System out println Created cluster createResponse getClusterIdenti fier private static void describeClusters DescribeClustersRequest request new DescribeClust...

Страница 27: ...status equalsIgnoreCase available clusterReady true else System out print Thread sleep sleepTime 1000 Manage Clusters Using Amazon Redshift CLI and API You can use the following Amazon Redshift CLI o...

Страница 28: ...nformation see Creating a Cluster in a VPC p 24 Restoring a Snapshot of a Cluster in VPC A snapshot of a cluster in VPC can only be restored in a VPC not outside the VPC You can restore it in the same...

Страница 29: ...C on the public Internet to connect to the cluster you must modify the VPC security group Specifically you should allow the port that you used when you launched the cluster For examples of security gr...

Страница 30: ...bnet Group p 28 The section explains how to manage your cluster subnet groups using the Amazon Redshift console You can create a cluster subnet group manage an existing one or delete one All of these...

Страница 31: ...ne subnet group 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 Sign into the AWS Management...

Страница 32: ...from the group 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 Sign into the AWS Management Console and open the A...

Страница 33: ...rrayList import java util List import com amazonaws auth AWSCredentials import com amazonaws auth PropertiesCredentials import com amazonaws services redshift AmazonRedshiftClient import com amazonaws...

Страница 34: ...ClusterSubnetGroupsResult result1 client describeClusterSub netGroups request1 List String subnetNames new ArrayList String We can work with just the first group returned since we requested info about...

Страница 35: ...subnet group getSubnets System out format Subnet s s s n subnet getSubnetIdenti fier subnet getSubnetAvailabilityZone getName subnet getSub netStatus Manage Cluster Subnet Groups Using Amazon Redshif...

Страница 36: ...cluster or later Also you can associate a security group with multiple clusters Amazon Redshift provides a security group called default which is created automatically when you launch your first clust...

Страница 37: ...Cloud VPC p 23 Managing Security Groups Using the Console Topics Creating a Security Group p 34 Managing Ingress Rules for a Security Group p 34 Deleting a Security Group p 36 Associating a Security...

Страница 38: ...he new group will be displayed in the list of security groups Managing Ingress Rules for a Security Group To manage ingress rules for a security group 1 Sign into the AWS Management Console and open t...

Страница 39: ...ct the account to use By default the account currently logged into the console is used In this case you need click the name of the EC2 security group you want in the EC2 Security Group Name box If you...

Страница 40: ...m redshift 2 In the navigation pane click Security Groups 3 Click the row for the security group that you want to delete and then click Delete One row must be selected for the Delete button to be enab...

Страница 41: ...fying an owner ID and Amazon EC2 security group combination For step by step instructions to run the following example see Running Java Examples for Amazon Redshift Using Eclipse p 118 You need to upd...

Страница 42: ...yGroup result client authorizeClusterSecurityGroupIn gress request request new AuthorizeClusterSecurityGroupIngressRequest withClusterSecurityGroupName clusterSecurityGroupName withEC2SecurityGroupNam...

Страница 43: ...sult getClusterSecurityGroups private static void printResultSecurityGroups List ClusterSecurityGroup groups if groups null System out println nDescribe cluster security groups result is null return S...

Страница 44: ...curity group describe cluster security groups revoke cluster security group ingress You can use the following Amazon Redshift APIs to manage security groups AuthorizeClusterSecurityGroupIngress Create...

Страница 45: ...ter group is named default redshift version For example default redshift 1 0 The list of parameters available in a parameter group depends on the parameter group family to which it belongs A parameter...

Страница 46: ...parameter type changes to user to indicate that the value has been modified For parameter groups that you create you can modify a parameter value at any time or you can reset all parameter values to t...

Страница 47: ...Parameter Group p 44 Modifying a Parameter Group p 44 Deleting a Parameter Group p 46 Associating a Parameter Group with a Cluster p 47 You can create modify and delete parameter groups by using the...

Страница 48: ...Create Cluster Parameter Group dialog box specify a parameter group family a parameter group name and a parameter group description When the settings are as you want them click Yes Create Modifying a...

Страница 49: ...hen the settings are as you want them click Save Changes 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 cl...

Страница 50: ...ue in the list Click the X button of the queue to remove Delete a queue b When the settings are as you want them click Save Changes Deleting a Parameter Group If a parameter group is associated with o...

Страница 51: ...ollowing tasks related to parameter groups Creating a parameter group Modifying a parameter group Associating a parameter group with a cluster Getting information about parameter groups This example c...

Страница 52: ...n the following example see Running Java Examples for Amazon Redshift Using Eclipse p 118 You need to update the code and provide a cluster identifier import java io IOException import java util Array...

Страница 53: ...eClusterPara meterGroups printResultClusterParameterGroups result private static void modifyClusterParameterGroup List Parameter parameters new ArrayList Parameter parameters add new Parameter withPar...

Страница 54: ...Name DescribeClusterParametersRequest request new DescribeClusterParamet ersRequest withParameterGroupName parameterGroupName DescribeClusterParametersResult result client describeClusterParamet ers r...

Страница 55: ...groups describe default cluster parameters modify cluster parameter group reset cluster parameter group You can use the following Amazon Redshift APIs to manage parameter groups CreateClusterParamete...

Страница 56: ...f you set the retention period to zero automated snapshots are disabled in which case Amazon Redshift stops taking snapshots and deletes any existing automated snapshots for the cluster Only Amazon Re...

Страница 57: ...h any other security or parameter group you will need to manually associate those groups with the new cluster Copying an Automated Snapshot Amazon Redshift deletes an automated snapshot at the end of...

Страница 58: ...filter the list by using the snapshot type a time range and associated cluster When you select an existing snapshot the snapshot details are shown in line in the list as shown in the example below Dep...

Страница 59: ...of being created Deleting a Manual Snapshot To delete a manual snapshot 1 Sign into the AWS Management Console and open the Amazon Redshift console at https console aws amazon com redshift 2 In the na...

Страница 60: ...his copy as long as you want To copy an automated snapshot 1 Sign into the AWS Management Console and open the Amazon Redshift console at https console aws amazon com redshift 2 In the navigation pane...

Страница 61: ...https console aws amazon com redshift 2 In the navigation pane click Snapshots 3 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...

Страница 62: ...ge the value as appropriate c Select Allow Version Upgrade as appropriate d In the Availability Zone box accept the availability zone from the snapshot or change the value as appropriate 7 Click Resto...

Страница 63: ...ster Displaying information about all the snapshots of a cluster Deleting manual snapshots of a cluster In this example a snapshot of the cluster is initiated When the snapshot is successfully created...

Страница 64: ...pshotId describeSnapshots deleteManualSnapshotsBefore createDate describeSnapshots catch Exception e System err println Operation failed e getMessage private static Date createManualSnapshot String sn...

Страница 65: ...ts System out format Identifier s n snapshot getSnapshotIdentifi er System out format Snapshot type s n snapshot getSnapshotType System out format Snapshot create time s n snapshot getSnap shotCreateT...

Страница 66: ...er snapshot revoke snapshot access You can use the following Amazon Redshift API actions to manage snapshots AuthorizeSnapshotAccess CopyClusterSnapshot CreateClusterSnapshot DeleteClusterSnapshot Des...

Страница 67: ...or the AWS SDKs to obtain event information You can obtain a list of all events or you can apply filters such as event duration or start and end date to obtain events information for a specific period...

Страница 68: ...ts displayed To filter events 1 Sign into the AWS Management Console and open the Amazon Redshift console at https console aws amazon com redshift 2 In the navigation pane click Events 3 To filter eve...

Страница 69: ...fier public static String eventSourceType provide source type e g cluster snapshot public static void main String args throws IOException AWSCredentials credentials new PropertiesCredentials ListEvent...

Страница 70: ...Type s n event getSourceType System out format Message s n event getMessage System out format Date s n event getDate View Events Using Amazon Redshift CLI and API You an use the following Amazon Reds...

Страница 71: ...h Command Line Interface CLI or one of the AWS Software Development Kits SDKs Query Load Performance Data Performance data helps you monitor database activity and performance This data is aggregated i...

Страница 72: ...it that tell you what the metric is applicable to that is the scope of the metric Amazon Redshift has the following two dimensions Metrics that have a NodeID dimension are metrics that provide perfor...

Страница 73: ...ignored and only HEALTHY or UNHEALTHY are displayed In Amazon CloudWatch values different than 1 and 0 may occur because of sampling issue Any value below 1 for HealthStatus is reported as 0 UNHEALTH...

Страница 74: ...formance data can be used to help you understand the relation between database performance and cluster metrics For example if you notice that a cluster s CPU spiked you can find the spike on the clust...

Страница 75: ...h Metrics p 68 Load Detail Working with Performance Data in the Amazon Redshift Console This section explains how to view performance data in the Amazon Redshift console which includes information abo...

Страница 76: ...diagnosing why the query was slow and what can be done to improve its performance The default cluster view shows all nodes graphed together an Average statistic and data for the last hour You can cha...

Страница 77: ...ed for metrics Select a metrics from the Metrics drop down By default all metrics are shown Change what metrics are shown all or a specific metric Click the Nodes button By default node data for a giv...

Страница 78: ...ReceiveThroughput value is displayed during the query execution Viewing Cluster Metrics During Query Execution You can view cluster metrics during query execution to help identify poorly performing qu...

Страница 79: ...rics scroll to the bottom of the query details For more examples of the Query tab see Cluster Metrics During Query Execution Examples p 76 In the following example the CPUUtilization and NetworkReceiv...

Страница 80: ...owing example the Query Properties Details SQL and Loaded Files information for the query are displayed In the following example an Explain Plan is shown API Version 2012 12 01 76 Amazon Redshift Mana...

Страница 81: ...s during load operations 1 Sign into the AWS Management Console and open the Amazon Redshift console at https console aws amazon com redshift 2 In the left navigation click Clusters 3 In the cluster l...

Страница 82: ...ill help indicate when the node is over utilized Likewise setting an alarm for low CPUUtilization on a cluster node will help indicate when the node is underutilized This section explains how to creat...

Страница 83: ...opic in the Create Alarm dialog by clicking the create topic link The details of your alarm will vary with your circumstance In the following example the average CPU utilization of a node Compute 0 ha...

Страница 84: ...age space of a node When working with Amazon Redshift metrics in the Amazon CloudWatch console command line tools or an Amazon SDK there are two concepts to keep in mind First you specify the metric d...

Страница 85: ...tions In general metrics in for this dimension e g ReadIOPS that are also metrics of nodes represent an aggregate of the node metric data You should take care in interpreting these metrics because the...

Страница 86: ...or 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 char...

Страница 87: ...of those nodes at the reserved node rate Also any similar nodes that you run in excess of the number of nodes you reserved will be billed at the normal rate Let s look at the following examples Suppo...

Страница 88: ...llowing Type a full or partial reservation ID The displayed items will be only offers that include the string you type Click All Purchases Active or Inactive Purchasing Reserved Node Offerings To purc...

Страница 89: ...Continue 6 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...

Страница 90: ...va io IOException import java util ArrayList import com amazonaws auth AWSCredentials import com amazonaws auth PropertiesCredentials import com amazonaws services redshift AmazonRedshiftClient import...

Страница 91: ...ervedNodes System out println Listing nodes already purchased for ReservedNode node result getReservedNodes printReservedNodeDetails node private static void findReservedNodeOffer DescribeReservedNode...

Страница 92: ...ringNotFoundException ex2 catch ReservedNodeQuotaExceededException ex3 catch Exception ex4 System out println Finished private static void printOfferingDetails ReservedNodeOffering offering System out...

Страница 93: ...purchase reserved node offerings purchase reserved node offering describe reserved node offerings describe orderable cluster options You can use the following Amazon Redshift APIs to purchase reserved...

Страница 94: ...wn credentials and only the permissions that you explicitly grant so that you do not have to share your account credentials with other users By default IAM users have no permissions you must grant per...

Страница 95: ...rSecurityGroup CreateClusterSnapshot redshift CreateClusterSnapshot CreateClusterSubnetGroup redshift CreateClusterSubnetGroup DeleteCluster redshift DeleteCluster DeleteClusterParameterGroup redshift...

Страница 96: ...redshift ResetClusterParameterGroup RestoreFromClusterSnapshot redshift RestoreFromClusterSnapshot RevokeClusterSecurityGroupIngress redshift RevokeClusterSecurityGroupIngress The following section pr...

Страница 97: ...Redshift using the console you might need to grant additional access permissions as shown in the following example 2 in which you grant access to Cloudwatch metrics the console provides By default all...

Страница 98: ...rmissions of their own access to Amazon Redshift actions does not cover them The AWS IAM console provides the following predefined Amazon Redshift policy templates that you can use to allow the necess...

Страница 99: ...manage your client s connectivity to your clusters including working with Secure Sockets Layers SSL and server certificates dealing with firewall timeout issues and using the SQL Workbench J to connec...

Страница 100: ...g SQL Workbench J 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...

Страница 101: ...avigation pane 3 In the Driver box click PostgreSQL org postgresql Driver 4 When a message appears saying that the library for the driver is not available click Yes 5 Click the browse icon next to the...

Страница 102: ...from the Amazon Redshift Getting Started Guide you can test your connection by typing the following query into the Statement window select from users order by userid limit 100 If your connection is s...

Страница 103: ...e b In the Amazon Redshift console click the name of your cluster and write down or copy the ODBC URL from the Configuration pane You will use portions of the URL string in step e c In the Control Pan...

Страница 104: ...box type a description for your reference In the SSL Mode box click the mode that you want For best results we recommend the prefer setting In the Server box type the server string from the ODBC URL y...

Страница 105: ...ype a name for your Connection Profile that will appear in the navigation pane 3 In the Driver box click ODBC Bridge sun jdbc odbc JdbcOdbcDriver 4 In the URL box replace datasource_name_from_control_...

Страница 106: ...shift Getting Started Guide you can test your connection by typing the following query into the Statement window select from users order by userid limit 100 If your connection is successful a listing...

Страница 107: ...stalling the PostgreSQL client tools select your operating system from the PostrgreSQL binary downloads page at http www postgresql org download Connect by Using the psql Defaults By default psql does...

Страница 108: ...operates with sslmode prefer To specify that psql opens an encrypted connection and uses an Amazon Redshift certificate to verify the service download an Amazon Redshift certificate to your computer...

Страница 109: ...and your cluster it should not be relied on for authenticating the server To authenticate the server install the public key pem file for the SSL certificate on your client and use the key to connect...

Страница 110: ...keystore_name alias alias file certificate_filename Where keystore_name is the keystore that your client application uses 2 Specify the following properties in your connection string property name ss...

Страница 111: ...the cluster e g 5439 DBNAME is the name of the database you specified when you created the cluster e g mydb In Java code you can specify the connection string as follows Connection conn null Propertie...

Страница 112: ...can refer to the keystore by specifying a property when you run your code as follows Djavax net ssl trustStore path to keystore Djavax net ssl trustStorePassword keystore password API Version 2012 12...

Страница 113: ...me master user name static final String MasterUserPassword master user password public static void main String args Connection conn null Statement stmt null try Dynamically load postgresql driver at r...

Страница 114: ...em out println Finished connectivity test Connecting to a Cluster by Using NET When you use NET C to programmatically connect to your cluster you can do so with or without server authentication If you...

Страница 115: ...on string Port e g 5439 String Port provide port MasterUserName e g masteruser Note use of single quotes String MasterUsername provide master user name MasterUserPassword e g mypassword Note use of si...

Страница 116: ...uter you are using to connect to your cluster The timeout period should be adjusted for your client and network See the To change TCP IP timeout settings p 112 Set the TCP IP keep alive behavior for y...

Страница 117: ...IP keep alive behavior in your connection string as explained in the following procedure To specify the TCP IP keep alive setting in a connection string In the JDBC connection string that you specify...

Страница 118: ...limits on the number of nodes that you can allocate provision for a cluster even if you request a quota increase These limits are different for different node types For a list of the current limits s...

Страница 119: ...me Must be between 8 and 64 characters in length Must contain at least one uppercase letter Must contain at least one lowercase letter Must contain one number Can be any printable ASCII character ASCI...

Страница 120: ...hanumeric characters or hyphens Must not be Default Must be unique for all security groups that are created by your AWS account Cluster snapshot identifier API Version 2012 12 01 116 Amazon Redshift M...

Страница 121: ...the libraries compute the necessary signature and include it in the request to Amazon Redshift Note Amazon Redshift is supported by the AWS SDK for Java NET PHP Ruby and Node js In this guide example...

Страница 122: ...sterUsername masteruser withMasterUserPassword 12345678Aa withNodeType dw hs1 xlarge withNumberOfNodes 2 Cluster createResponse client createCluster request System out println Created cluster createRe...

Страница 123: ...k lib sdk third party commons logging 1 1 1 sdk third party httpcomponents client 4 1 1 sdk third party jackson core 1 8 CreateAndModifyClusterSecurityGroup Change the class path separator as needed f...

Страница 124: ...mazon S3 by using client side encryption a database COPY operation will decrypt the data as it is copied to Amazon Redshift The COPY command uses the master symmetric key you specified For more inform...

Страница 125: ...zonaws services s3 AmazonS3 import com amazonaws services s3 AmazonS3EncryptionClient import com amazonaws services s3 model EncryptionMaterials import com amazonaws services s3 model PutObjectRequest...

Страница 126: ...ory Skip sub directories int retryCount 0 boolean done false while done try uploadToS3 s3 bucketName s3Prefix fileEntry done true catch Exception e retryCount if retryCount MAX_RETRY_COUNT System out...

Страница 127: ...Request ID ase getRequestId throw ase catch AmazonClientException ace System out println Caught an AmazonClientException which means the client encountered a serious internal problem while trying to...

Страница 128: ...same hash function and input that you used to sign the request If the resulting signature matches the signature in the request Amazon Redshift processes the request otherwise the request is rejected...

Страница 129: ...he general request syntax is https redshift us east 1 amazonaws com Action CreateCluster ClusterIdentifier examplecluster MasterUsername masteruser MasterUserPassword 12345678Aa NumberOfNode 2 NodeTyp...

Страница 130: ...is as the key to hash the data us east 1 The result of this hash becomes the key for the next hash function After you calculate the derived key you use it in a hash function that accepts two input str...

Страница 131: ...scribe cluster snapshot sharing and create snapshot progress metrics New Feature 08 July 2013 Published various documentation fixes Documentation fixes 22 April 2013 Updated the Amazon Redshift Manage...

Результаты поиска

Содержание Redshift

Отзывы:

Похожие инструкции для Redshift

Бренды по названию

Популярные бренды

Amazon Redshift, Руководство по управлению

Результаты поиска

Содержание Redshift

Отзывы:

Похожие инструкции для Redshift

Бренды по названию

Популярные бренды