Post Syndicated from Ilesh Garish original https://aws.amazon.com/blogs/big-data/federate-access-to-amazon-redshift-using-the-jdbc-browser-plugin-for-single-sign-on-authentication-with-microsoft-azure-active-directory/

Since 2020, Amazon Redshift has supported multi-factor authentication (MFA) to any SAML 2.0 compliant identity provider (IdP) in our JDBC and ODBC drivers. You can map the IdP user identity and group memberships in order to control authorization for database objects in Amazon Redshift. This simplifies administration by enabling you to manage user access in a central location, reducing the overhead of creating and maintaining users in the database in conjunction with the IdP.

Recently, we helped a customer who was building their data warehouse on Amazon Redshift and had the requirement of using Microsoft Azure Active Directory (Azure AD) as their corporate IdP with MFA. This post illustrates how to set up federation using Azure AD and AWS Identity and Access Management (IAM). Azure AD manages the users and provides federated access to Amazon Redshift using IAM.

Prerequisites

This post assumes that you have the following:

• An Azure AD account
• An Amazon Redshift cluster (for instructions, see Step 2: Create a sample Amazon Redshift cluster)
• Permissions for administering IAM identities and AWS Management Console access
• A database you want to permit access to (for this post, we use the default database dev)

Solution overview

This post consists of the following three sections to implement the solution:

1. Set up the Azure Enterprise non-gallery application using single sign-on (SSO) with SAML.
2. Set up the IAM provider and roles, which includes the following steps:
   1. Create the SAML identity provider.
   2. Create an IAM role for access to the Amazon Redshift cluster.
   3. Create an IAM provider and an IAM role to use SAML-based federation.
   4. Test the SSO setup.
3. Configure the JDBC client to use Azure AD user credentials using a browser to log in to the Amazon Redshift cluster. This post uses a JDBC client, but you can use the same setup to support ODBC clients.

Set up an Azure Enterprise application

To set up an Azure Enterprise application to control Amazon Redshift access, complete the following steps:

1. Log in to Azure Portal and under Services, choose Enterprise applications.
   
2. Choose New application.
   
3. For Add an application, choose Non-gallery application.
4. For Name¸ enter Redshift.
5. Choose Add.
   
6. For Identifier (Entity ID), enter a string (it’s not used in the flow by default).
7. For ReplyURL, enter http://localhost/redshift/.
   
8. Choose Add new claim.
9. Configure your SAML claims as shown in the following table (for more information, see Configure SAML assertions for your IdP).

10. In the Manage claim section, for Name, enter Role.
11. For Source attribute, enter your source, which includes your AWS account ID, IAM policy, and IAM provider.
    
12. On the Permissions page, add users or groups to your application (alternatively, grant universal admin consent for the entire organization).
    
13. Download your federation metadata.

You need the metadata to configure the IAM IdP. Check your IdP for how to download this document, because every IdP handles this differently.

14. On the App registration page, choose Authentication in the navigation pane.
15. In the Mobile and desktop applications section, add http://localhost/redshift/.
    
16. For Enable the following mobile and desktop flows, choose Yes.
    
17. On the Enterprise applications page, choose your application.
18. In the Set up Single Sign-On with SAML section, choose Edit.
    
19. Confirm the reply URL.
    
    
20. On the Users and groups page, add the necessary role or group.
    

Set up IAM to allow Azure AD users to access Amazon Redshift

In this section, you configure IAM to allow Azure AD users to access Amazon Redshift resources and get temporary credentials.

1. Sign in to the AWS Management Console as the admin account.
2. On the IAM console, choose Identity providers in the navigation pane.
   
3. Choose Create Provider.
   
4. For Provider Type, choose SAML.
5. For Provider name, enter a name for your provider.
6. For Metadata Document, choose the file you downloaded or saved from your IdP.
   
7. Choose Next Step.
8. Choose Create.

Now you set up your policy. For detailed instructions, see Federate Database User Authentication Easily with IAM and Amazon Redshift.

9. On the IAM console, choose Policies in the navigation pane.
10. Choose Create policy.

You’re directed to the Create policy page, where you can choose the Visual editor tab for step-by-step policy creation or the JSON tab to edit the policy in one step.

11. For this post, choose the JSON tab.
    
12. In the text box, enter the following code:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "redshift:GetClusterCredentials",
            "Resource": [
                "arn:aws:redshift:us-west-1:your-account-number:dbname:cluster-identifier/dev",
                "arn:aws:redshift:us-west-1:your-account-number :dbuser:cluster-identifier/${redshift:DbUser}",
                "arn:aws:redshift:us-west-1:your-account-number :cluster:cluster-identifier"
            ],
            "Condition": {
                "StringEquals": {
                    "aws:userid": "unique role ID:${redshift:DbUser}@companyemail.com"
                }
            }
        },
        {
            "Effect": "Allow",
            "Action": "redshift:CreateClusterUser",
            "Resource": "arn:aws:redshift:us-west-1:your-account-number:dbuser:cluster-identifier/${redshift:DbUser}"
        },
        {
            "Effect": "Allow",
            "Action": "redshift:JoinGroup",
            "Resource": "arn:aws:redshift:us-west-1:your-account-number:dbgroup:cluster-identifier/db_group"
        },
        {
            "Effect": "Allow",
            "Action": [
                "redshift:DescribeClusters",
                "iam:ListRoles"
            ],
            "Resource": "*"
        }
    ]
}

In the preceding code, provide the following information:

• The Region of your cluster (for this post, we use us-west-1).
• The account number your cluster is on.
• The Amazon Redshift cluster you want to grant users permission to (you can also enter * for all clusters under that account).
• Your database name (for this post, we use dev; you can also enter * to allow access to all databases).
• The unique ID of the IAM role you created (you can get this by running aws iam get-role --role-name Your_Role_Name in the terminal).
• Your tenant or company email.
• The database group you want to assign users to.
• ${redshift:DbUser} is replaced with whatever your IdP (Azure) specified for the SAML DbUser field for the user.

The first statement allows users to grab temporary credentials from the cluster if:

• It’s on the specified cluster, in the correct account, in the Region specified.
• The dbname the user is trying to connect to is dev.
• The user trying to connect matches the DbUser specified in Azure.
• The user is under the role specified by the unique role ID with the IAM account under your company’s email.

This all depends on your setup with IdP (Azure) configuration. If your employee’s email is [email protected], you need to set ${redshift:DbUser} to the super field that matches to the employee’s username johndoe and set the AWS SAML RoleSessionName field to be the super field that matches the employee’s email [email protected] to make this condition work.

If you set ${redshift:DbUser} to be the employee’s email, remove the @companyemail.com in the example code to match the RoleSessionName.

If you set the RoleSessionId to be just the employee’s username, remove the @companyemail.com in the example code to match the RoleSessionName.

In the Azure setup instructions, ${redshift:DbUser} and RoleSessionName are both set to the employee’s email, so you should remove @companyemail.com in the preceding code if you’re following these instructions closely. This post creates the user’s database username under their email and signs them in to AWS under this email.

The second statement allows users to create a database username under the specified conditions. In the preceding code, it restricts creation to ${redshift:DbUser}.

The third statement specifies what groups the user can join.

The final statement specifies what actions the user can perform on the resources. In the preceding code, users can call DescribeClusters to get cluster information, and IAM ListRoles to check which roles the user can assume. “Resource: “*” applies the preceding actions to any Amazon Redshift cluster the user has access to.

13. Choose Review policy.
14. For Name¸ enter a name for your policy.
15. For Description, enter an optional description.
16. For Summary, review your policy components and make sure to resolve any warnings or errors.
    
17. Choose Create policy.

Lastly, we create the IAM role.

18. In the navigation pane, choose Roles.
    
19. Choose Create role.
    
20. For Select type of trusted entity, choose SAML 2.0 federation.
21. For SAML provider, choose the provider you created (for this post, AzureTest).
22. For Attribute, leave at the default (SAML:aud).
23. For Value¸ enter http://localhost/redshift/.
    
24. Choose Next: Permissions.
25. Under Attach permissions policies, search for and select the policy you created.
26. Under Set permissions boundary¸ you can set advanced controls for user permissions (for this post, we don’t make any changes).
    
27. Choose Next: Tags.
28. Under Add tabs (optional), you can add key-value tags to better organize, track, or control access for this role. For this post, we don’t add any tags.
29. Choose Next: Review.
30. For Role name, enter a name for your role.
31. For Role description, enter an optional description.
32. For Trusted entities, verify the ARN of the provider you specified earlier is correct.
33. For Permissions boundary, verify that the settings you specified earlier (if any) are correct.
    
34. Choose Create role.

If you haven’t already, you’re now ready to create the Amazon Redshift cluster for the Azure AD users to connect to.

Connect through JDBC and run queries

You can use any application that can take in a JDBC driver to connect using Azure SSO, or even use a language like Java to connect using a script. For this post, we use SQL Workbench/J, which is a common application to connect to JDBC and run queries.

1. Install SQL Workbench/J if not already installed.
2. Start SQL Workbench/J.
3. On the Select Connection Profile page, choose the Add profile group icon.

Adding a folder is optional but helps keep things organized.

4. Name your folder (for this post, AzureAuth).
   
5. Choose the New connection profile icon.

Creating a new profile in your profile group is optional but helps keep things organized.

6. Name your profile (for this post, Azure).
   
7. Choose Manage Drivers.
   
8. Choose Amazon Redshift.
   
9. Choose the Open folder icon.
10. Choose the JDBC JAR file.
11. Choose OK.
    
12. On the Select Connection Profile page, for Driver, choose Amazon Redshift (com.amazon.redshift.jdbc.Driver).
13. For URL, enter the IAM JDBC URL with your cluster identifier, Region, and database name (for example, jdbc:redshift:iam://cluster-identifier:us-west-1/dev).
    

Alternatively, you can use the format jdbc:redshift:iam://<cluster-dns-here>:<cluster-port>/<your-DB-name-here>.

14. Choose Extended Properties.
    
15. Choose the Create new entry icon.
16. Enter the following information:
    1. Property plugin_name with value com.amazon.redshift.plugin.BrowseAzureCredentialsProvider. This tells the driver what authentication method to choose. This should always be set to com.amazon.redshift.plugin.BrowserAzureCredentialsProvider.
    2. Property idp_tenant with the value of your IdP tenant. This is the tenant name of your company configured on your IdP (Azure). This value can either be the tenant name or the tenant unique ID with hyphens (preferred). If you use a tenant name, it could cause uncertainty when setting up the application.
    3. Property client_id with the value of your application client ID. This is the client ID with hyphens of the Amazon Redshift application you created when setting up your Azure SSO configurations.
17. Choose OK.
    
18. On the Select Connection Profile page, leave everything else at the default values and choose OK.
    

The driver opens the default browser with the SSO sign-in page.

After you sign in, you’re redirected to localhost with a success message.

Troubleshooting

If something goes wrong, logging is the first call to start an investigation.

You can add an extended property with the following code:

DSILogLevel=6
LogPath=<any existing directory>

Alternatively, use a connection string:

jdbc:redshift://<cluster_url>:<port>/<db>?DSILogLevel=6&LogPath=<any existing directory>

For an Unauthorized exception, check your authentication in Azure Portal, under Mobile and desktop applications.

For a PKX exception, first try to use the ssl=false extended property. The vanish exception means that the problem is in the SSL certificate between the cluster and client. If so, first try to use the latest driver and check if your cluster version is old. Then run your application with the “-Djavax.net.debug=all” key for JVM. This shows all the TLS traffic. Make sure the certification is there.

For the exception SAML error: Not authorized to perform sts:AssumeRoleWithSAML, you need to edit the IAM role trust relationship.

Change "StringEquals" to "StringLike" : { "saml:aud": "*" }, then save it and try again. Also check that saml:aud and replyURL in Azure are exactly the same. If they’re different, authentication fails and causes the same error.

Summary

Amazon Redshift makes it easy to integrate with third-party identity providers to provide centralized user management. In this post, we showed how to configure the Amazon Redshift browser-based plugin to use multi-factored authentication with Microsoft Azure Active Directory. You can follow these same steps to work with your SAML 2.0 compliant identity provider of choice.

About the Authors

Ilesh Garish is a Software Development Engineer at AWS. His role is to develop connectors for Amazon Redshift. Prior to AWS, he built database drivers for the Oracle RDBMS, TigerLogic XDMS, and OpenAccess SDK. He worked in the database internal technologies at San Francisco Bay Area startups.

Brandon Schur is a Senior Database Engineer at AWS.  He focuses on performance tuning for MPP databases, drivers & connectivity, and integrations with AWS services and partners.

Post Syndicated from Harshida Patel original https://aws.amazon.com/blogs/big-data/optimize-your-analytical-workloads-using-the-automatic-query-rewrite-feature-of-amazon-redshift-materialized-views/

Amazon Redshift materialized views enable you to significantly improve performance of complex queries that are frequently run as part of your extract, load, and transform (ELT), business intelligence (BI), or dashboarding applications. Materialized views precompute and store the result sets of the SQL query in the view definition. Materialized views speed up data access, because the query doesn’t need to rerun the computation each time the query runs, which also reduces the resource consumption.

Amazon Redshift has the ability to automatically rewrite your SQL queries that don’t explicitly reference existing materialized views to use an existing materialized view if it will improve performance. This feature is valuable and, in some cases, the only option for performance optimization. Consider packaged ISV apps or even just reports— users often don’t have access to the SQL to optimize. In some cases, even if they do have access, the code or script is so old that nobody is familiar with it and you don’t know what regressions even a small change might introduce.

In this post, we describe how the automatic query rewrite feature works and some scenarios where you could take advantage of this feature. For information about the materialized view feature itself, refer to Speed up your ELT and BI queries with Amazon Redshift materialized views and Creating materialized views in Amazon Redshift.

All examples in this post are run on an 8 node ra3.4xlarge cluster with the 3 TB TPC-DS cloud benchmark dataset.

Let’s look at three different scenarios where the automatic query rewrite feature could help: optimizing joins between two large tables, optimizing joins for tables that have multiple join paths, and optimizing table scans.

Optimize joins between two large tables

There are many situations where you have two large tables that are joined frequently. In this case, creating a materialized view that joins these two tables could help improve the performance of those queries. Materialized views precompute the join and store the results so subsequent runs only need to retrieve the saved results; no need to run the expensive JOINs each time. With automatic query rewrite, none of the end-user queries have to be modified to refer to the materialized view. When creating the explain plan for the query, Amazon Redshift replaces the join between the two tables with the materialized view.

By default, the automatic query rewrite uses a materialized view only if it’s up to date and reflects all changes from its base tables. This means that the query isn’t rewritten to use the materialized view if the base tables have more recent updates that aren’t yet reflected in the materialized view.

For example, consider the following SQL query. The query joins two tables: store_sales (8,639,936,081 rows) and customer (30,000,000 rows):

SELECT 
cust.c_customer_id 
FROM store_sales sales
INNER JOIN customer cust
ON sales.ss_customer_sk = cust.c_customer_sk
GROUP BY cust.c_customer_id;

The query runs in 545,520 milliseconds; the following is the explain plan for the query:

XN HashAggregate  (cost=9602679386653.98..9602679386653.98 rows=29705556 width=20)
  ->  XN Hash Join DS_BCAST_INNER  (cost=375000.00..9602659714194.54 rows=7868983773 width=20)
        Hash Cond: (""outer"".ss_customer_sk = ""inner"".c_customer_sk)
        ->  XN Seq Scan on store_sales sales  (cost=0.00..86399365.12 rows=8245454518 width=4)
              Filter: (ss_customer_sk IS NOT NULL)
        ->  XN Hash  (cost=300000.00..300000.00 rows=30000000 width=24)
              ->  XN Seq Scan on customer cust  (cost=0.00..300000.00 rows=30000000 width=24)

Let’s create a materialized view that pre-computes the join between the store_sales and customer tables using the following SQL statement:

CREATE MATERIALIZED VIEW cust_store_sales
AS 
SELECT         
  cust.c_customer_id
, cust.c_first_name
, cust.c_last_name
, sales.ss_item_sk
, sales.ss_quantity
, cust.c_current_addr_sk
FROM  store_sales sales
INNER JOIN customer cust
ON sales.ss_customer_sk = cust.c_customer_sk;

Let’s now rerun the original query:

SELECT 
cust.c_customer_id 
FROM store_sales sales
INNER JOIN customer cust
ON sales.ss_customer_sk = cust.c_customer_sk
GROUP BY cust.c_customer_sk;

The query runs much faster (46,493 milliseconds). This is because of the automatic query rewrite feature, which has rewritten the preceding query to use the newly created materialized view instead of joining both tables. The explain plan for this query shows this change:

XN HashAggregate  (cost=103138905.60..103138905.60 rows=29705556 width=20)
  ->  XN Seq Scan on mv_tbl__cust_store_sales__0 derived_table1  (cost=0.00..82511124.48 rows=8251112448 width=20)

The original query run also consumed 1,263 CPU seconds and read 45,013 blocks of data, whereas the query that ran after the creation of the materialized view only consumed 898 CPU seconds and read 29,256 blocks. That is a reduction of 29% in CPU consumption and 35% in blocks read.

The optimizer can also rewrite the following query to use the previously created materialized view, which includes the additional join to the customer_address table:

SELECT
cust.c_customer_id
,addr.ca_state
FROM store_sales sales
INNER JOIN customer cust
ON sales.ss_customer_sk = cust.c_customer_sk
INNER JOIN customer_address addr
ON cust.c_current_addr_sk = addr.ca_address_sk
GROUP BY cust.c_customer_id, addr.ca_state;
     
      XN HashAggregate  (cost=30242919089.37..30242919089.37 rows=1544688912 width=26)
         ->  XN Hash Join DS_BCAST_INNER  (cost=542661.20..30201663527.13 rows=8251112448 width=26)
        Hash Cond: ("outer".c_current_addr_sk = "inner".ca_address_sk)
        ->  XN Seq Scan on mv_tbl__cust_store_sales_1__0 derived_table1  (cost=0.00..82511124.48 rows=8251112448 width=24)
        ->  XN Hash  (cost=150000.00..150000.00 rows=15000000 width=10)
              ->  XN Seq Scan on customer_address addr  (cost=0.00..150000.00 rows=15000000 width=10)

Optimize joins for tables that have multiple join paths

For large tables on Amazon Redshift, the ideal distribution style would be ‘KEY’, with the distribution key being the column that is used most frequently in the JOIN clause. There are situations where some large tables have multiple join paths. 50% of the queries may use a particular column to join to the table, and the other 50% of the queries may use a different column to join to the table. Both types of queries are important and have stringent performance requirements. In this case, you could pick one column as the distribution key for the table and then create a materialized view with the second column as the distribution key. This is possible because materialized views can have their own distribution and sort keys.

Here’s an example to illustrate how this works.

The web_sales table (2,159,968,881 rows) has the distribution key ws_order_number. This helps optimize a majority of the queries (70% of the joins to this table use ws_order_number as the join column). The remaining 30% use the column ws_bill_customer_sk to join to the table, as shown in the following SQL statement. This query took 12,790 milliseconds to run.

SELECT 
  c_customer_id
, c_email_address 
FROM web_sales ws
INNER JOIN customer cs
ON ws.ws_bill_customer_sk=cs.c_customer_sk;

We can create the materialized view to help improve the performance of the remaining 30% of the queries. Note the DISTKEY keyword in the following code. We have defined a new distribution key for the materialized view (ws_bill_customer_sk):

CREATE MATERIALIZED VIEW web_sales_cust_dist
DISTKEY (ws_bill_customer_sk)
AS
SELECT * FROM web_sales;

Rerunning the following query returns rows much faster than before (7,715 milliseconds vs. 12,790 milliseconds):

SELECT 
  c_customer_id
, c_email_address 
FROM web_sales ws
INNER JOIN customer cs
ON ws.ws_bill_customer_sk=cs.c_customer_sk;

Again, the explain plan of the query has changed; it now references the materialized view even though the SQL statement doesn’t explicitly reference the materialized view:

XN Hash Join DS_DIST_NONE  (cost=375000.00..696964927.69 rows=2159968768 width=74)
  Hash Cond: (""outer"".ws_bill_customer_sk = ""inner"".c_customer_sk)
  ->  XN Seq Scan on mv_tbl__web_sales_cust_dist__0 derived_table1  (cost=0.00..21599687.68 rows=2159968768 width=4)
  ->  XN Hash  (cost=300000.00..300000.00 rows=30000000 width=78)
        ->  XN Seq Scan on customer cs  (cost=0.00..300000.00 rows=30000000 width=78)

Optimize table scans

Table scans on Amazon Redshift are made efficient through the use of sort keys. Sort keys determine the order in which the columns are stored in the data blocks. Picking a column that appears frequently in your filtering conditions as a sort key can improve query performance significantly.

Compound sort keys with multiple columns can be defined on your table in case multiple columns are good candidates for sort keys. But in some situations where two or more high cardinality columns are sort key candidates, the compound sort key may not provide adequate performance. In these cases, a materialized view could be created with a different sort key to maintain that data in an alternate sorted order to help cater to a subset of the queries.

In the following example query, the web_sales table uses the column ws_sold_date_sk for the sort key, because this is the column that is used commonly for filtering rows. A smaller set of queries use ws_sales_price for filtering rows. Given that both ws_sold_date_sk and ws_sales_price are high cardinality columns with lots of unique values, a compound sort key with both columns may not be performant for all query patterns.

SELECT *
FROM web_sales 
WHERE ws_sales_price BETWEEN 50 AND 100;

Let’s create the following materialized view and see how it can help improve the performance of the preceding query:

CREATE MATERIALIZED VIEW web_sales_sort_on_price
SORTKEY (ws_sales_price)
AS
SELECT * FROM web_sales;

Running the following query returns rows much faster (5 milliseconds vs. 3,548 milliseconds) because the automatic query rewrite is using the materialized view:

SELECT *
FROM web_sales 
WHERE ws_sales_price BETWEEN 50 AND 100;

The following is the new explain plan:

XN Seq Scan on mv_tbl__web_sales_cust_dist__0 derived_table1  (cost=0.00..32399531.52 rows=10799844 width=260)
  Filter: ((ws_sales_price <= 100.00) AND (ws_sales_price >= 50.00))

Conclusion

Materialized views on Amazon Redshift can be a powerful optimization tool if used appropriately. With automatic query rewrite, you can optimize queries without any impact to end-users or their queries. This allows you to create materialized views after the application has gone live. Some customers plan this as part of their performance-optimization strategy when building new apps. The real value is that you can optimize queries and workloads without needing to modify the source code or scripts, and you can benefit even with a partial match.

About the Authors

Harshida Patel is a Specialist Sr. Solutions Architect, Analytics with AWS.

Jeetesh Srivastva is a Sr. Manager, Specialist Solutions Architect at AWS. He specializes in Amazon Redshift and works with customers to implement scalable solutions using Amazon Redshift and other AWS Analytic services. He has worked to deliver on-premises and cloud-based analytic solutions for customers in banking and finance and hospitality industry verticals.

Sain Das is an Analytics Specialist Solutions Architect at AWS and helps customers build scalable cloud solutions that help turn data into actionable insights.

Somdeb Bhattacharjee is an Enterprise Solutions Architect at AWS.