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:
- Amazon Redshift cluster that was previously created with one node
- Connected to the dev database
- Created a table
- 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
Step 2: Save your 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.
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
Step 5: Explore a summary of the Redshift role with Query Editor v2 with full access:
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:
Specific permissions for sqlworkbench include read, write, tagging, list databases:
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.
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!
Top comments (0)