AWS Payments is a part of the AWS Commerce Platform (CP) group that owns the customer expertise of paying AWS invoices. It helps AWS prospects handle their fee strategies and fee preferences, and helps clients make self-service payments to AWS.
The Machine Learning, Data and Analytics (MLDA) group at AWS Payments allows data-driven decision-making throughout funds processes and partners by delivering information, business insights, and causal and ML inferences through a scalable set of information, insights, and ML inference providers.
In this submit, we talk about tips on how to democratize information access to Amazon Redshift utilizing the Amazon Redshift Query Editor V2 .
Background
At AWS Payments, we had been utilizing Redash to permit our customers to creator and run SQL queries in opposition to our Amazon Redshift information warehouse. Redash is a web-based SQL shopper application that can be utilized to creator and run queries, visualize question results with charts, and collaborate with teams.
Over time, we began to notice incompatibilities between Redash’s operations and the wants of our workload.
We had the next necessities in thoughts when in search of an alternate tool:
* Authentication and authorization * Provide knowledge entry with out creating a database user and password
* Allow listing customers using permission teams (POSIX/LDAP) for accessing the tool
* Limit person entry to database objects
* User expertise * Run SQL queries on the selected database
* Save a question and rerun it later
* Write a dynamic SQL query and run the query based mostly on enter parameters
* Export a question end result to CSV
* Search saved queries
* Share a question with other customers as a URL
After an evaluation of alternate companies, we selected the Amazon Redshift Query Editor V2.
Amazon Redshift Query Editor V2
The Amazon Redshift Query Editor V2 has the following advantages:
* It makes knowledge throughout analytics and information scientists extra accessible with a unified web-based analyst workbench for data analysts to discover, share, and collaborate on knowledge by way of a SQL interface
* It offers a managed service that allows you to focus on exploring your knowledge with out managing your infrastructure
* Users can log in to the Query Editor using single sign-on (SSO)
* Users can connect to Amazon Redshift utilizing federated access without providing a user name and password
* It lets you collaborate with staff members by providing the ability to share saved queries securely
* You can benefit from new options as quickly as they get launched by the Amazon Redshift Query Editor staff
* You can keep monitor of adjustments made to saved queries utilizing the Query History feature
* You can write parameterized SQL queries, which permits you to reuse a question with different values
* You can turn on the Chart characteristic to display a graphic visualization of the present web page of results
* You can use notebooks to organize, annotate, and share a quantity of SQL queries in a single document
* You can run a quantity of queries in parallel by operating each question in a separate tab
However, it presented the next challenges:
* To restrict person entry to different AWS providers within our AWS account, we connected the AWS Identity and Access Management (IAM) policies (see the appendix on the end of this post) to the SAML IAM role. The insurance policies promote the next: * The person can solely access the Query Editor V2 service.
* The federated user gets assigned to a database group with restricted entry.
* The Query Editor V2 at present doesn’t support cross-account Amazon Redshift connections. However, we set up Amazon Redshift information sharing to entry the Amazon Redshift cluster from other AWS accounts. For more particulars, refer to Sharing knowledge across clusters in Amazon Redshift.
Architecture overview
The following diagram illustrates our structure.
In the following sections, we’ll walk you thru the steps to arrange the question editor and migrate Redash queries.
Prerequisites
To implement this answer, you should set up federated access to the Amazon Redshift Query Editor V2 utilizing your id supplier (IdP) services.
You can find extra information in the following posts:
Set up Amazon Redshift Query Editor V2
To set up the question editor, full the next steps:
1. Create an Amazon Redshift database group with read-only access.
2. Create an IAM position for accessing Query Editor V2 in an AWS account and attach the required IAM policies based mostly in your use case. For more info, check with Configuring your AWS account.
three. Create a belief relationship between your IdP and AWS.
4. Add the principal tag sqlworkbench-team to the IAM function to share queries. For extra data, refer to Sharing a query.
Migrate Redash queries to Amazon Redshift Query Editor V2
In this section, we stroll you thru alternative ways emigrate your Redash queries to the Amazon Redshift Query Editor V2.
Query with out parameters
Querying without parameters is pretty straightforward, simply copy your query from Redash and enter it in the question editor.
1. In Redash, navigate to the saved question and choose Edit Source.
2. Copy the source question.
3. In Amazon RedShift Query Editor V2, enter the query into the editor, choose the Save icon, and provides your query a title.
Query with parameters
In Redash, a string between {{ }} shall be treated as a parameter, but Amazon RedShift Query Editor V2 makes use of ${ } to identify a parameter. To migrate queries with parameters, observe the identical steps however substitute {{ with ${ and }} with }.
The following screenshot reveals an instance query in Redash.
The following screenshot shows the same query in Amazon RedShift Query Editor V2.
Multi-part query to a Query Editor V2 notebook
For a multi-part question, copy the query of every section of a Redash dashboard and add it to a notebook. The notebook in Amazon Redshift Query Editor V2 runs queries successively. You also can add a description on your query.
The following screenshot exhibits an instance query on the Redash dashboard.
The following screenshot reveals the query in an Amazon Redshift Query Editor V2 notebook.
Summary
In this publish, we demonstrated how we arrange Amazon Redshift Query Editor V2 with SSO and Amazon Redshift federated entry, and migrated our customers from Redash to Amazon Redshift Query Editor V2. This resolution lowered our operational price of maintaining a third-party software and its infrastructure.
If you could have related use cases and wish to offer a web-based tool to your customers to explore data in your Amazon Redshift cluster, consider using Amazon Redshift Query Editor V2.
Appendix: Customer IAM policies
In this part, we offer the code for the IAM policies we connected to the SAML IAM position to limit user entry to different AWS providers inside our AWS account:
* query-editor-credentials-policy – In the next code, present your Region, account, and cluster parameters to grant entry to Amazon Redshift to get cluster credentials, create users, and permit users to hitch teams:
{
“Version”: ” “,
“Statement”: [ { “Action”: “redshift:GetClusterCredentials”, “Resource”: [ “arn:aws:redshift:::cluster:”, “arn:aws:redshift:::dbname:/payments_beta”, “arn:aws:redshift:::dbuser:/${redshift:DbUser}” ], “Effect”: “Allow” }, { “Action”: “redshift:JoinGroup”, “Resource”: “arn:aws:redshift:::dbgroup:/payments_ro_users”, “Effect”: “Allow” }, { “Action”: “redshift:DescribeClusters”, “Resource”: “arn:aws:redshift:::cluster:”, “Effect”: “Allow” }, { “Action”: “redshift:CreateClusterUser”, “Resource”: “arn:aws:redshift:::dbuser:/${redshift:DbUser}”, “Effect”: “Allow” }
]
}
* query-editor-access-policy – See the next code:
{
“Version”: ” “,
“Statement”: [ { “Action”: “redshift:DescribeClusters”, “Resource”: “*”, “Effect”: “Allow”, “Sid”: “RedshiftPermissions” }, { “Condition”: { “StringEquals”: { “secretsmanager:ResourceTag/sqlworkbench-resource-owner”: “${aws:userid}” } }, “Action”: [ “secretsmanager:CreateSecret”, “secretsmanager:GetSecretValue”, “secretsmanager:DeleteSecret”, “secretsmanager:TagResource” ], “Resource”: “arn:aws:secretsmanager:::sqlworkbench!”, “Effect”: “Allow”, “Sid”: “SecretsManagerPermissions” }, { “Condition”: { “StringEquals”: { “aws:CalledViaLast”: “sqlworkbench.amazonaws.com” } }, “Action”: “tag:GetResources”, “Resource”: “*”, “Effect”: “Allow”, “Sid”: “ResourceGroupsTaggingPermissions” }, { “Action”: [ “sqlworkbench:CreateFolder”, “sqlworkbench:PutTab”, “sqlworkbench:BatchDeleteFolder”, “sqlworkbench:DeleteTab”, “sqlworkbench:GenerateSession”, “sqlworkbench:GetAccountInfo”, “sqlworkbench:GetAccountSettings”, “sqlworkbench:GetUserInfo”, “sqlworkbench:GetUserWorkspaceSettings”, “sqlworkbench:PutUserWorkspaceSettings”, “sqlworkbench:ListConnections”, “sqlworkbench:ListFiles”, “sqlworkbench:ListTabs”, “sqlworkbench:UpdateFolder”, “sqlworkbench:ListRedshiftClusters”, “sqlworkbench:DriverExecute”, “sqlworkbench:ListTaggedResources” ], “Resource”: “*”, “Effect”: “Allow”, “Sid”: “AmazonRedshiftQueryEditorV2NonResourceLevelPermissions” }, { “Condition”: { “StringEquals”: { “aws:RequestTag/sqlworkbench-resource-owner”: “${aws:userid}” } }, “Action”: [ “sqlworkbench:CreateConnection”, “sqlworkbench:CreateSavedQuery”, “sqlworkbench:CreateChart” ], “Resource”: “*”, “Effect”: “Allow”, “Sid”: “AmazonRedshiftQueryEditorV2CreateOwnedResourcePermissions” }, { “Condition”: { “StringEquals”: { “aws:ResourceTag/sqlworkbench-resource-owner”: “${aws:userid}” } }, “Action”: [ “sqlworkbench:DeleteChart”, “sqlworkbench:DeleteConnection”, “sqlworkbench:DeleteSavedQuery”, “sqlworkbench:GetChart”, “sqlworkbench:GetConnection”, “sqlworkbench:GetSavedQuery”, “sqlworkbench:ListSavedQueryVersions”, “sqlworkbench:UpdateChart”, “sqlworkbench:UpdateConnection”, “sqlworkbench:UpdateSavedQuery”, “sqlworkbench:AssociateConnectionWithTab”, “sqlworkbench:AssociateQueryWithTab”, “sqlworkbench:AssociateConnectionWithChart”, “sqlworkbench:UpdateFileFolder”, “sqlworkbench:ListTagsForResource” ], “Resource”: “*”, “Effect”: “Allow”, “Sid”: “AmazonRedshiftQueryEditorV2OwnerSpecificPermissions” }, { “Condition”: { “StringEquals”: { “aws:ResourceTag/sqlworkbench-resource-owner”: “${aws:userid}”, “aws:RequestTag/sqlworkbench-resource-owner”: “${aws:userid}” }, “ForAllValues:StringEquals”: { “aws:TagKeys”: “sqlworkbench-resource-owner” } }, “Action”: “sqlworkbench:TagResource”, “Resource”: “*”, “Effect”: “Allow”, “Sid”: “AmazonRedshiftQueryEditorV2TagOnlyUserIdPermissions” }, { “Condition”: { “StringEquals”: { “aws:ResourceTag/sqlworkbench-team”: “${aws:PrincipalTag/sqlworkbench-team}” } }, “Action”: [ “sqlworkbench:GetChart”, “sqlworkbench:GetConnection”, “sqlworkbench:GetSavedQuery”, “sqlworkbench:ListSavedQueryVersions”, “sqlworkbench:ListTagsForResource”, “sqlworkbench:AssociateQueryWithTab” ], “Resource”: “*”, “Effect”: “Allow”, “Sid”: “AmazonRedshiftQueryEditorV2TeamReadAccessPermissions” }, { “Condition”: { “StringEquals”: { “aws:ResourceTag/sqlworkbench-resource-owner”: “${aws:userid}”, “aws:RequestTag/sqlworkbench-team”: “${aws:PrincipalTag/sqlworkbench-team}” } }, “Action”: “sqlworkbench:TagResource”, “Resource”: “*”, “Effect”: “Allow”, “Sid”: “AmazonRedshiftQueryEditorV2TagOnlyTeamPermissions” }, { “Condition”: { “StringEquals”: { “aws:ResourceTag/sqlworkbench-resource-owner”: “${aws:userid}” }, “ForAllValues:StringEquals”: { “aws:TagKeys”: “sqlworkbench-team” } }, “Action”: “sqlworkbench:UntagResource”, “Resource”: “*”, “Effect”: “Allow”, “Sid”: “AmazonRedshiftQueryEditorV2UntagOnlyTeamPermissions” }
]
}
* query-editor-notebook-policy – See the following code:
{
“Version”: ” “,
“Statement”: [ { “Action”: [ “sqlworkbench:ListNotebooks”, “sqlworkbench:ListNotebookVersions”, “sqlworkbench:ListQueryExecutionHistory” ], “Resource”: “*”, “Effect”: “Allow” }, { “Condition”: { “StringEquals”: { “aws:RequestTag/sqlworkbench-resource-owner”: “${aws:userid}” } }, “Action”: [ “sqlworkbench:CreateNotebook”, “sqlworkbench:ImportNotebook”, “sqlworkbench:DuplicateNotebook” ], “Resource”: “*”, “Effect”: “Allow” }, { “Condition”: { “StringEquals”: { “aws:ResourceTag/sqlworkbench-resource-owner”: “${aws:userid}” } }, “Action”: [ “sqlworkbench:GetNotebook”, “sqlworkbench:UpdateNotebook”, “sqlworkbench:DeleteNotebook”, “sqlworkbench:CreateNotebookCell”, “sqlworkbench:DeleteNotebookCell”, “sqlworkbench:UpdateNotebookCellContent”, “sqlworkbench:UpdateNotebookCellLayout”, “sqlworkbench:BatchGetNotebookCell”, “sqlworkbench:AssociateNotebookWithTab”, “sqlworkbench:ExportNotebook”, “sqlworkbench:CreateNotebookVersion”, “sqlworkbench:GetNotebookVersion”, “sqlworkbench:CreateNotebookFromVersion”, “sqlworkbench:DeleteNotebookVersion”, “sqlworkbench:RestoreNotebookVersion” ], “Resource”: “*”, “Effect”: “Allow” }, { “Condition”: { “StringEquals”: { “aws:ResourceTag/sqlworkbench-team”: “${aws:PrincipalTag/sqlworkbench-team}” } }, “Action”: [ “sqlworkbench:GetNotebook”, “sqlworkbench:BatchGetNotebookCell”, “sqlworkbench:AssociateNotebookWithTab” ], “Resource”: “*”, “Effect”: “Allow” }
]
}
About the Authors
Mohammad Nejad leads the AWS Payments Data Platform group. He has experience leading teams, architecting designs, implementing options, and launching merchandise. Currently, his team focuses on constructing a contemporary information platform on AWS to offer an entire solution for processing, analyzing, and presenting data.
Erol Murtezaoglu, a Technical Product Manager at AWS, is an inquisitive and enthusiastic thinker with a drive for self-improvement and studying. He has a robust and confirmed technical background in software program development and architecture, balanced with a drive to deliver commercially profitable products. Erol highly values the method of understanding customer needs and problems, to be able to deliver options that exceed expectations.
Mohamed Shaabanis a Senior Software Engineer in Amazon Redshift and is predicated in Berlin, Germany. He has over 12 years of experience in the software program engineering. He is keen about cloud companies and building solutions that delight prospects. Outside of labor, he is an novice photographer who likes to explore and seize distinctive moments.
About The Author
