Amazon Redshift
Getting Started Guide

The AWS Documentation website is getting a new look!
Try it now and let us know what you think. Switch to the new look >>

You can return to the original look by selecting English in the language selector above.

Step 5: Connect to the Sample Cluster and Run Queries

To query databases hosted by your Amazon Redshift cluster, you have two options:

  • Connect to your cluster and run queries on the AWS Management Console with the Query Editor.

    If you use the Query Editor, you don't have to download and set up a SQL client application.

  • Connect to your cluster through a SQL client tool, such as SQL Workbench/J.

Querying a Database Using the Query Editor

Using the Query Editor is the easiest way to run queries on databases hosted by your Amazon Redshift cluster. After creating your cluster, you can immediately run queries by using the Query Editor on the Amazon Redshift console.

The following cluster node types support the Query Editor:

  • DC1.8xlarge

  • DC2.large

  • DC2.8xlarge

  • DS2.8xlarge

Using the Query Editor, you can do the following:

  • Run single SQL statement queries.

  • Download result sets as large as 100 MB to a comma-separated value (CSV) file.

  • Save queries for reuse. You can't save queries in the EU (Paris) Region or the Asia Pacific (Osaka-Local) Region.

  • View query execution details for user-defined tables.

Query Editor Considerations

For details about considerations when using the Query Editor, see Querying a Database Using the Query Editor in the Amazon Redshift Cluster Management Guide.

Enabling Access to the Query Editor

To access the Query Editor, you need permission. To enable access, attach the AmazonRedshiftQueryEditor and AmazonRedshiftReadOnlyAccess policies for AWS Identity and Access Management (IAM) to the AWS IAM user that you use to access your cluster.

If you have already created an IAM user to access Amazon Redshift, you can attach the AmazonRedshiftQueryEditor and AmazonRedshiftReadOnlyAccess policies to that user. If you haven't created an IAM user yet, create one and attach the policies to the IAM user.

To attach the required IAM policies for the Query Editor

  1. Sign in to the AWS Management Console and open the IAM console at https://console.aws.amazon.com/iam/.

  2. Choose Users.

  3. Choose the user that needs access to the Query Editor.

  4. Choose Add permissions.

  5. Choose Attach existing policies directly.

  6. For Policy names, choose AmazonRedshiftQueryEditor and AmazonRedshiftReadOnlyAccess.

  7. Choose Next: Review.

  8. Choose Add permissions.

Using the Query Editor

In the following example, you use the Query Editor to perform the following tasks:

  • Run SQL commands.

  • View query execution details.

  • Save a query.

  • Download a query result set.

To use the Query Editor

  1. Sign in to the AWS Management Console and open the Amazon Redshift console at https://console.aws.amazon.com/redshift/.

  2. In the navigation pane, choose Query Editor.

  3. In the Credentials dialog box, enter the following values and then choose Connect:

    • Cluster: Choose examplecluster.

    • Database: dev.

    • Database user: awsuser

    • Password: Enter the password that you specified when you launched the cluster.

  4. For Schema, choose public to create a new table based on that schema.

  5. Enter the following in the Query Editor window and choose Run query to create a new table.

    create table shoes( shoetype varchar (10), color varchar(10));
  6. Choose Clear.

  7. Enter the following command in the Query Editor window and choose Run query to add rows to the table.

    insert into shoes values ('loafers', 'brown'), ('sandals', 'black');
  8. Choose Clear.

  9. Enter the following command in the Query Editor window and choose Run query to query the new table.

    select * from shoes;

    You should see the following results.

Querying a Database Using a SQL Client

Next, you connect to your cluster by using a SQL client tool and run a simple query to test the connection. You can use most SQL client tools that are compatible with PostgreSQL. For this tutorial, you use the SQL Workbench/J client. Complete this section by performing the following steps:

After you complete this step, you can determine whether you want to load sample data from Amazon S3 in Step 6: Load Sample Data from Amazon S3 or find more information about Amazon Redshift and reset your environment at Where Do I Go From Here?.

Install SQL Client Drivers and Tools

You can use most SQL client tools with Amazon Redshift JDBC or ODBC drivers to connect to an Amazon Redshift cluster. In this tutorial, you connect using SQL Workbench/J, a free, DBMS-independent, cross-platform SQL query tool. If you plan to use SQL Workbench/J to complete this tutorial, use the steps following to set up the Amazon Redshift JDBC driver and SQL Workbench/J. For more complete instructions for installing SQL Workbench/J, go to Setting Up the SQL Workbench/J Client in the Amazon Redshift Cluster Management Guide. If you use an Amazon EC2 instance as your client computer, install SQL Workbench/J and the required drivers on the instance.

Note

Install any third-party database tools that you want to use with your clusters yourself. Amazon Redshift doesn't provide or install any third-party tools or libraries.

To Install SQL Workbench/J on Your Client Computer

  1. Review the SQL Workbench/J software license.

  2. Go to the SQL Workbench/J website and download the appropriate package for your operating system.

  3. Go to the Installing and starting SQL Workbench/J page and install SQL Workbench/J.

    Important

    Note the Java runtime version prerequisites for SQL Workbench/J and ensure you are using that version. Otherwise, the client application doesn't run.

  4. Go to Configure a JDBC Connection and download an Amazon Redshift JDBC driver to enable SQL Workbench/J to connect to your cluster.

For more information about using the Amazon Redshift JDBC or ODBC drivers, see Configuring Connections in Amazon Redshift.

To Get Your Connection String

  1. In the Amazon Redshift console, in the navigation pane, choose Clusters.

  2. Choose examplecluster to open it, and make sure that you are on the Configuration tab.

  3. On the Configuration tab, under Cluster Database Properties, copy the JDBC URL of the cluster.

    Note

    The endpoint for your cluster is not available until the cluster is created and in the available state.

To Connect from SQL Workbench/J to Your Cluster

This step assumes you installed SQL Workbench/J.

  1. Open SQL Workbench/J.

  2. Choose File, and then choose Connect window.

  3. Choose Create a new connection profile.

  4. For New profile, enter a name for the profile.

  5. Choose Manage Drivers. The Manage Drivers dialog box opens.

  6. Choose Create a new entry. For Name, enter a name for the driver.

    Choose the folder icon next to the Library box, navigate to the location of the driver, choose it, and then choose Open.

    If the Please select one driver dialog box displays, choose com.amazon.redshift.jdbc4.Driver or com.amazon.redshift.jdbc41.Driver and then choose OK. SQL Workbench/J automatically completes the Classname box. Keep Sample URL blank, and choose OK.

  7. For Driver, choose the driver that you just added.

  8. For URL, copy the JDBC URL from the Amazon Redshift console and paste it here.

  9. For Username, enter awsuser for the master user.

  10. For Password, enter the password associated with the master user account.

  11. Choose Autocommit.

  12. Choose the Save profile list icon, as shown following.

  13. Choose OK.

  14. Enter the following command in the query window and choose SQL, Execute Current to add rows to the table.

    create table shoes( shoetype varchar (10), color varchar(10));
  15. Run the following command to add rows to the table.

    insert into shoes values ('loafers', 'brown'), ('sandals', 'black');
  16. Run the following command to query the new table.

    select * from shoes;