DEV Community

Cover image for How to share SQL queries in Amazon Redshift with your team
Wendy Wong for AWS Community Builders

Posted on • Updated on

How to share SQL queries in Amazon Redshift with your team

Introduction

As you and your team create advanced queries and especially this time of the year you may go on vacation, you might just need to share your SQL queries within Amazon Redshift using Query Editor v2 with your team who are still working over the new year period.

In this tutorial,if you have been following along from the last three Amazon Redshift tutorials we will continue using this 2 month free trial and the dev database.

Re-cap

To re-cap from the last tutorial, previously the following areas were explored including:

our cluster

  1. Amazon Redshift cluster that was previously created with one node
  2. Connected to the dev database
  3. Created a table
  4. Using the COPY command data was loaded into the Amazon Redshift cluster

How to share your SQL queries using Amazon Redshift?

Just say you are about to go on vacation, but you need to continue reporting for the end of year, you have to train your team mate and show them how to automate the data extraction process. You have thought about writing a SQL script and sharing it with them so they can run the query while you are away. Here I will show you how you can achieve this using Amazon Redshift Query Editor 2.

In this example, I have data stored in a Redshift cluster and after clicking 'Query in Query Editor v2' I connect to the dev database.

Step 1: Query the data

sales table

Step 2: Save your SQL query

save the SQL query

Step 3: Go to Queries and view your saved SQL query. Right-click on the square and click 'Share with my team'.

You may notice an error message when you try to share a SQL query.

The error message reads:

To share a query with your team you will need to have principal tag on'SQLworkbench team' added to your user or role.

error message

Step 4: To troubleshoot, refer to the Amazon Redshift Developer Guide to browse the correct Redshift IAM policy to enable you to have the required permissions to share SQL queries with your team

The necessary role is Query Editor V2 with Full Access

Documentation

Step 5: Explore a summary of the Redshift role with Query Editor v2 with full access:

Summary

An overview in JSON describes the level of access and permissions provided by the IAM policy for Amazon Redshift Query Editor v2 with full access to operations and resources including sharing a sql query with the line entry of sqlworkbench:

allow

more JSON

Specific permissions for sqlworkbench include read, write, tagging, list databases:

full access

After the IAM role is updated to include Amazon Redshift Query Editor v2 Full Access with the principal tag 'sqlworkbench', SQL queries may be shared with your team members.

Clean Up Resources - Delete Amazon Redshift cluster

As best practice, to avoid any unexpected billing costs at the end of the month it is good practice to delete AWS resources that you no longer use. To delete the Amazon Redshift Cluster and clean up the resources follow these steps:

On the Amazon Redshift dashboard, click on Clusters on the left hand pane, select the cluster you created and click Actions.

On the Actions drop-down menu, click delete.

Redshift dashboard

delete cluster

Type the word delete to finalize the deletion of the Redshift cluster.

Final Word

You may also explore a use case between Amazon Redshift and Amazon SageMaker to create, train and deploy a machine learning model using SQL with Amazon Redshift ML using SageMaker endpoint for your business case. It is explained in the Amazon Machine Learning Blog here.

References

Happy New Year and happy learning!

Discussion (0)