Post Syndicated from Bhanu Pittampally original https://aws.amazon.com/blogs/big-data/use-amazon-redshift-ra3-with-managed-storage-in-your-modern-data-architecture/

Amazon Redshift is a fully managed, petabyte-scale data warehouse service in the cloud. You can start with just a few hundred gigabytes of data and scale to a petabyte or more. This enables you to use your data to acquire new insights for your business and customers.

Over the years, Amazon Redshift has evolved a lot to meet our customer demands. Its journey started as a standalone data warehousing appliance that provided a low-cost, high-performance, cloud-based data warehouse. Support for Amazon Redshift Spectrum compute nodes was later added to extend your data warehouse to data lakes, and the concurrency scaling feature was added to support burst activity and scale your data warehouse to support thousands of queries concurrently. In its latest offering, Amazon Redshift runs on third-generation architecture where storage and compute layers are decoupled and scaled independent of each other. This latest generation powers the several modern data architecture patterns our customers are actively embracing to build flexible and scalable analytics platforms.

When spinning up a new instance of Amazon Redshift, you get to choose either Amazon Redshift Serverless, for when you need a data warehouse that can scale seamlessly and automatically as your demand evolves unpredictably, or you can choose an Amazon Redshift provisioned cluster for steady-state workloads and greater control over your Amazon Redshift cluster’s configuration.

An Amazon Redshift provisioned cluster is a collection of computing resources called nodes, which are organized into a group called a cluster. Each cluster runs the Amazon Redshift engine and contains one or more databases. Creating an Amazon Redshift cluster is the first step in your process of building an Amazon Redshift data warehouse. While launching a provisioned cluster, one option that you specify is the node type. The node type determines the CPU, RAM, storage capacity, and storage drive type for each node.

In this post, we cover the current generation node RA3 architecture, different RA3 node types, important capabilities that are available only on RA3 node types, and how you can upgrade your current Amazon Redshift node types to RA3.

Amazon Redshift RA3 nodes

RA3 nodes with managed storage enable you to optimize your data warehouse by scaling and paying for compute and managed storage independently. RA3 node types are the latest node type for Amazon Redshift. With RA3, you choose the number of nodes based on your performance requirements and only pay for the managed storage that you use. RA3 architecture gives you the ability to size your cluster based on the amount of data you process daily or the amount of data that you want to store in your warehouse; there is no need to account for both storage and processing needs together.

Other node types that we previously offered include the following:

• Dense compute – DC2 nodes enable you to have compute-intensive data warehouses with local SSD storage included. You choose the number of nodes you need based on data size and performance requirements.
• Dense storage (deprecated) – DS2 nodes enable you to create large data warehouses using hard disk drives (HDDs). If you’re using the DS2 node type, we strongly recommend that you upgrade to RA3 to get twice as much storage and improved performance for the same on-demand cost.

When you use the RA3 node size and choose your number of nodes, you can provision the compute independent of storage. RA3 nodes are built on the AWS Nitro System and feature high bandwidth networking and large high-performance SSDs as local caches. RA3 nodes use your workload patterns and advanced data management techniques to deliver the performance of local SSD while scaling storage automatically to Amazon Simple Storage Service (Amazon S3).

RA3 node types come in three different sizes to accommodate your analytical workloads. You can quickly start experimenting with the RA3 node type by creating a single-node ra3.xlplus cluster and explore various features that are available. If you’re running a medium-sized data warehouse, you can size your cluster with ra3.4xlarge nodes. For large data warehouses, you can start with ra3.16xlarge. The following table gives more information about RA3 node types and their specifications as of this writing.

Amazon Redshift with managed storage

Amazon Redshift with a managed storage architecture (RMS) still boasts the same resiliency and industry-leading hardware. With managed storage, Amazon Redshift uses intelligent data prefetching and data evictions based on the temperature of your data. This method helps you decide where to store your most-queried data. Most frequently used blocks (hot data) are cached locally on SSD, and infrequently used blocks (cold data) are stored on an RMS layer backed by Amazon S3. The following diagram depicts the leader node, compute node, and Amazon Redshift managed storage.

In the following sections, we discuss the capabilities that Amazon Redshift RA3 with managed storage can provide.

Independently scale compute and storage

As the scale of an organization grows, data continues to grow—reaching petabytes. The amount of data you ingest into your Amazon Redshift data warehouse also grows. You may be looking for ways to cost-effectively analyze all your data and at the same time have control over choosing the right compute or storage resource at the right time. For customers who are looking to be cost conscientious and cost-effective, the RA3 platform provides the option to scale and pay for your compute and storage resources separately.

With RA3 instances with managed storage, you can choose the number of nodes based on your performance requirements, and only pay for the managed storage that you use. This gives you the flexibility to size your RA3 cluster based on the amount of data you process daily without increasing your storage costs. It allows you to pay per hour for the compute and separately scale your data warehouse storage capacity without adding any additional compute resources and paying only for what you use.

Another benefit of RMS is that Amazon Redshift manages which data should be stored locally for fastest access, and data that is slightly colder is still kept within fast-access reach.

Advanced hardware

RA3 instances use high-bandwidth networking built on the AWS Nitro System to further reduce the time taken for data to be offloaded to and retrieved from Amazon S3. Managed storage uses high-performance SSDs for your hot data and Amazon S3 for your cold data, providing ease of use, cost-effective storage, and fast query performance.

Additional security options

Amazon Redshift managed VPC endpoints enable you to set up a private connection to securely access your Amazon Redshift cluster within your virtual private cloud (VPC) from client applications in another VPC within the same AWS account, another AWS account, or a subnet without using public IPs and without requiring the traffic to traverse across the internet.

The following scenarios describe common reasons to allow access to a cluster using an Amazon Redshift managed VPC endpoint:

• AWS account A wants to allow a VPC in AWS account B to have access to a cluster
• AWS account A wants to allow a VPC that is also in AWS account A to have access to a cluster
• AWS account A wants to allow a different subnet in the cluster’s VPC within AWS account A to have access to a cluster

For information about access options to another VPC, refer to Enable private access to Amazon Redshift from your client applications in another VPC.

Further optimize your workload

In this section, we discuss two ways to further optimize your workload.

AQUA

AQUA (Advanced Query Accelerator) is a new distributed and hardware-accelerated cache that enables Amazon Redshift to run up to 10 times faster than other enterprise cloud data warehouses by automatically boosting certain types of queries. AQUA is available with the ra3.16xlarge, ra3.4xlarge, or ra3.xlplus nodes at no additional charge and with no code changes.

AQUA is an analytics query accelerator for Amazon Redshift that uses custom-designed hardware to speed up queries that scan large datasets. AQUA automatically optimizes query performance on subsets of the data that require extensive scans, filters, and aggregation. With this approach, you can use AQUA to run queries that scan, filter, and aggregate large datasets.

For more information about using AQUA, refer to How to evaluate the benefits of AQUA for your Amazon Redshift workloads.

Concurrency scaling for write operations

With RA3 nodes, you can take advantage of concurrency scaling for write operations, such as extract, transform, and load (ETL) statements. Concurrency scaling for write operations is especially useful when you want to maintain consistent response times when your cluster receives a large number of requests. It improves throughput for write operations contending for resources on the main cluster.

Concurrency scaling supports COPY, INSERT, DELETE, and UPDATE statements. In some cases, you might follow DDL statements, such as CREATE, with write statements in the same commit block. In these cases, the write statements are not sent to the concurrency scaling cluster.

When you accrue credit for concurrency scaling, this credit accrual applies to both read and write operations.

Increased agility to scale compute resources

Elastic resize allows you to scale your Amazon Redshift cluster up and down in minutes to get the performance you need, when you need it. However, there are limits on the nodes that you can add to a cluster. With some RA3 node types, you can increase the number of nodes up to four times the existing count. All RA3 node types support a decrease in the number of nodes to a quarter of the existing count. The following table lists growth and reduction limits for each RA3 node type.

RA3 node types also have a shorter duration of snapshot restoration time because of the separation of storage and compute.

Improved resiliency

Amazon Redshift employs extensive fault detection and auto remediation techniques in order to maximize the availability of a cluster. With the RA3 architecture, you can enable cluster relocation, which provides additional resiliency by having the ability to relocate a cluster across Availability Zones without losing any data (RPO is zero) or having to change your client applications. The cluster’s endpoint remains the same after the relocation occurs so applications can continue operating without modifications. As the existing cluster fails, a new cluster is created on demand in another Availability Zone so cost of a standby replica cluster is avoided.

Accelerate data democratization

In this section, we share two techniques to accelerate data democratization.

Data sharing

Data sharing provides instant, granular, and high-performance access without copying data and data movement. You can query live data constantly across all consumers on different RA3 clusters in the same AWS account, in a different AWS account, or in a different AWS Region. Data is shared securely and provides governed collaboration. You can provide access in different granularity, including schema, database, tables, views, and user-defined functions.

This opens up various new use cases where you may have one ETL cluster that is producing data and have multiple consumers such as ad-hoc querying, dashboarding, and data science clusters to view the same data. This also enables bi-directional collaboration where groups such as marketing and finance can share data with one another. Queries accessing shared data use the compute resources of the consumer Amazon Redshift cluster and don’t impact the performance of the producer cluster.

For more information about data sharing, refer to Sharing Amazon Redshift data securely across Amazon Redshift clusters for workload isolation.

AWS Data Exchange for Amazon Redshift

AWS Data Exchange for Amazon Redshift enables you to find and subscribe to third-party data in AWS Data Exchange that you can query in an Amazon Redshift data warehouse in minutes. You can also license your data in Amazon Redshift through AWS Data Exchange. Access is automatically granted when a customer subscribes to your data and is automatically revoked when their subscription ends. Invoices are automatically generated, and payments are automatically collected and disbursed through AWS. This feature empowers you to quickly query, analyze, and build applications with third-party data.

For details on how to publish a data product and subscribe to a data product using AWS Data Exchange for Amazon Redshift, refer to New – AWS Data Exchange for Amazon Redshift.

Cross-database queries for Amazon Redshift

Amazon Redshift supports the ability to query across databases in a Redshift cluster. With cross-database queries, you can seamlessly query data from any database in the cluster, regardless of which database you are connected to. Cross-database queries can eliminate data copies and simplify your data organization to support multiple business groups on the same cluster.

One of many use cases where Cross-database query helps you is when data is organized across multiple databases in a Redshift cluster to support multi-tenant configurations. For example, different business groups and teams that own and manage data sets in their specific database in the same data warehouse need to collaborate with other groups. You might want to perform common ETL staging and processing while your raw data is spread across multiple databases. Organizing data in multiple Redshift databases is also a common scenario when migrating from traditional data warehouse systems. With cross-database queries, you can now access data from any of the databases on the Redshift cluster without having to connect to that specific database. You can also join data sets from multiple databases in a single query

You can read more about cross-database queries here.

Upgrade to RA3

You can upgrade to RA3 instances within minutes no matter the size of your current Amazon Redshift clusters. Simply take a snapshot of your cluster and restore it to a new RA3 cluster. For more information, refer to Upgrading to RA3 node types.

You can also simplify your migration efforts with Amazon Redshift Simple Replay. For more information, refer to Simplify Amazon Redshift RA3 migration evaluation with Simple Replay utility.

Summary

In this post, we talked about the RA3 node types, the benefits of Amazon Redshift managed storage, and the additional capabilities that you get by using Amazon Redshift RA3 with managed storage. Migrating to RA3 node types isn’t a complicated effort, you can get started today.

About the Authors

Bhanu Pittampally is an Analytics Specialist Solutions Architect based out of Dallas. He specializes in building analytic solutions. His background is in data warehouses—architecture, development, and administration. He has been in the data and analytics field for over 13 years.

Jason Pedreza is an Analytics Specialist Solutions Architect at AWS with data warehousing experience handling petabytes of data. Prior to AWS, he built data warehouse solutions at Amazon.com. He specializes in Amazon Redshift and helps customers build scalable analytic solutions.

Post Syndicated from Jessica Ho original https://aws.amazon.com/blogs/big-data/ingest-stripe-data-in-a-fast-and-reliable-way-using-stripe-data-pipeline-for-amazon-redshift/

Enterprises typically host a myriad of business applications for varying data needs. As companies grow, so does the demand for insights from a complete set of business data. Having data from various applications that store data in disparate silos can delay the decision-making process. However, building and maintaining an API integration or a third-party extract, transform, and load (ETL) pipeline to move data into a destination data store can be time-consuming and expensive.

Today we’re delighted to introduce Stripe Data Pipeline for Amazon Redshift to help you access your Stripe data and extract insight securely and easily from Amazon Redshift. This data, including billing, issuing, and payment records, can be shared in a consistent and automated fashion. You can integrate your Stripe data with data from other sources in your Amazon Redshift clusters to create a single source of truth.

In this post, we discuss the benefits of Stripe Data Pipeline and some of its use cases.

Solution overview

Amazon Redshift is a fast, fully managed, petabyte-scale cloud data warehousing service that makes it simple and cost-effective to efficiently analyze all your data using your existing business intelligence (BI) tools. It’s optimized for datasets ranging from a few hundred gigabytes to petabytes or more. This columnar data warehouse provides provisioned as well as serverless deployment options and uses an industry-standard SQL interface to analyze structured and semi-structured data with fast query performance.

Stripe Data Pipeline is powered by Amazon Redshift’s latest RA3 instances, which provide cross-account data sharing capability. RA3 takes a performant, cost-effective approach to address rapidly growing data volume by decoupling data processing from managed storage. You can then scale compute and storage independently and only pay for what you use. Data sharing provides read access directly to data stored across Amazon Redshift clusters without data movement. This capability removes the complexity and delays that are often associated with managing large distributed datasets across multiple accounts.

The solution provides the following core features and benefits:

• Scalable and managed data pipeline – You don’t need to build, maintain, and scale custom ETL jobs. You can set up Stripe Data Pipeline in minutes, and it and scales automatically to handle increased business activities and data volume.
• Up-to-date financial data – You automatically receive and refresh a complete set of your Stripe data and reports in Amazon Redshift on a low-latency schedule. Stripe Data Pipeline is built into Stripe and always provides accurate data.
• Security and compliance – Data is shared directly from Stripe with your Amazon Redshift cluster, and confidentiality of the data is protected in transit and at rest. Amazon Redshift offers comprehensive security controls and monitoring via native integration with AWS CloudTrail and Amazon CloudWatch (for more information, see Logging Amazon Redshift API calls with AWS CloudTrail and Monitoring Amazon Redshift using CloudWatch metrics, respectively). You can define and audit who has access to what and ensure the compliance requirements are met.
• Extensibility – Once the data is accessible in AWS, you benefit from the breadth of native integrations Amazon Redshift supports. You can join datasets from other data stores in operational databases, build reports and dashboards with BI tools, or identify patterns and generate prediction using Amazon Redshift ML.

The following architecture diagram provides a quick overview of how data sharing works and how other AWS services can be used together. We dive deeper into different use cases in the following sections.

Accept datashares from Stripe

You can configure the solution in a few steps with no code necessary.

Once Stripe creates a datashare from the producer cluster and authorizes your AWS account, you can view this datashare on your Amazon Redshift console. You need to associate it with specific or all clusters in your AWS account as the consumer. Clusters can be specified by namespaces as globally unique identifiers. Next, you create a database from the datashare in order to start querying data.

Query data from the consumer Amazon Redshift cluster

You can now access your Stripe data and schema directly from Amazon Redshift’s web-based query editor. This direct connection enables teams to pull accurate analysis of various functions of the business. For example:

• Finance – “How does my cash flow change based on seasonality?”
• Sales – “How many customers do we have in the US?”
• Product – “How many active users do we have on each subscription plan?”
• Sales operations – “Which customers haven’t paid their invoices?”

The following screenshot shows an example in which the query editor displays the number of charges blocked per Stripe’s connected account.

Use federated queries

The modern data architecture of Amazon Redshift enables you to store data in purpose-built data stores based on specific use cases, and allows querying external databases on Amazon Relational Database Service (Amazon RDS) or datasets in an Amazon Simple Storage Service (Amazon S3) data lake without moving these datasets to Amazon Redshift clusters. You can drive deeper by incorporating data from Amazon RDS, or from an S3 data lake through Amazon Redshift Spectrum. This capability provides a native integration without requiring additional ETL jobs.

The following syntax allows you to create an external schema from an Amazon Aurora MySQL-Compatible Edition database to an Amazon Redshift cluster. Amazon Redshift assumes an AWS Identity and Access Management (IAM) role and uses AWS Secrets Manager to access external data stores. For more information and examples with other supported data stores, refer to Querying data with federated queries in Amazon Redshift.

CREATE EXTERNAL SCHEMA auroram
FROM MYSQL
DATABASE ‘example_database’ SCHEMA 'example_schema' -- schema is optional
URI ‘hostname’
IAM_ROLE ‘iam_role_arn’
SECRET_ARN ‘aws_secrets_manager_arn’; 

Coming back to Stripe Data Pipeline, now you can combine the data from an Aurora table and create further analysis. For example, you can correlate the trends of customer acquisition against sales campaign by region, so you can gain an understanding of the campaign effectiveness and make adjustment to marketing strategies.

Create visualizations and dashboards

Now that your complete set of business data is accessible from Amazon Redshift, you can start to explore the data and create visualizations. Amazon QuickSight is a serverless BI service that allows you to easily connect to a data source, create analyses, publish dashboards, and share between teams. QuickSight seamlessly integrates with AWS services such as Amazon Redshift, Amazon S3, and many more.

The following screenshot illustrates how straightforward it is to connect an Amazon Redshift instance to QuickSight as a new data source.

The following screenshot is of a sample QuickSight dashboard pulling data from Amazon Redshift.

Key considerations

When using Stripe Data Pipeline, consider the following:

• Instance type – This solution is available for all RA3 node types. If you run an existing DS2 or DC2 cluster, there are multiple options to migrate to RA3, including elastic resize, snapshot and restore, and classic resize. For more information, including an upgrade sizing reference between different node types, refer to Upgrading to RA3 node types.
• RI migration – If you have Amazon Redshift Reserved Instances (RIs), you can use the RI migration feature to migrate the DS2 RI clusters to equivalent RA3 RI clusters as part of a cross-instance resize or cross-instance snapshot restore operation. The RA3 RI covering the new cluster will be the same cost and on the same calendar terms as the original DS2 RI for supported configurations.
• Encryption – The consumer cluster must be encrypted as part of the enhanced security control for cross-account sharing. You can enable encryption at cluster creation time, or modify an unencrypted cluster with either AWS Key Management Service (AWS KMS) or AWS CloudHSM.
• Federated queries – This capability works with external DB instances, including Amazon RDS for PostgreSQL, Amazon Aurora PostgreSQL-Compatible Edition, Amazon RDS for MySQL, and Aurora MySQL-Compatible Edition. You should also ensure that you have an Amazon Redshift cluster with a cluster maintenance version that supports federated queries.

Conclusion

In this post, we introduced Stripe Data Pipeline for Amazon Redshift and discussed options to further integrate with AWS services. Stripe Data Pipeline removes the need to build custom API integration or adopt a third-party ETL pipeline, making data accessible with a few clicks and with no code required. Businesses can automatically receive up-to-date data from Stripe in their data warehouse on AWS, reduce data silos, and extract deep insights to address business needs.

Check out Stripe Data Pipeline for more information about the solution and how to get started.

About the Authors

Jessica Ho is a Sr. Partner Solutions Architect at AWS supporting ISV partners who build business applications. She is passionate about creating differentiated solutions that promote cloud adoption. Outside of work, she enjoys spoiling her garden into a mini jungle.

Alexander Mahabir is a Sr. Partner Solutions Architect at AWS based in the D.C metropolitan area. Alex has over 16 year of experience building cloud, and on-premise solutions for small, medium, and large enterprises. Alex currently works with ISV partners in the Digital Customer Experience segment.

Post Syndicated from Sumeet Joshi original https://aws.amazon.com/blogs/big-data/federate-single-sign-on-access-to-amazon-redshift-query-editor-v2-with-okta/

Amazon Redshift query editor v2 is a web-based SQL client application that you can use to author and run queries on your Amazon Redshift data warehouse. You can visualize query results with charts and collaborate by sharing queries with members of your team. You can use query editor v2 to create databases, schemas, tables, and load data from Amazon Simple Storage Service (Amazon S3) using the COPY command or by using a wizard. You can browse multiple databases and run queries on your Amazon Redshift data warehouse or data lake, or run federated queries to operational databases such as Amazon Aurora. Because it’s a managed SQL editor in your browser and it’s integrated with your single sign-on (SSO) provider, Amazon Redshift query editor v2 reduces the number of steps to the first query so you gain insights faster.

Amazon Redshift query editor v2 integration with your identity provider (IdP) automatically redirects the user’s browser to the query editor v2 console instead of Amazon Redshift console. This enables your users to easily access Amazon Redshift clusters through query editor v2 using federated credentials without managing database users and passwords.

In this post, we focus on Okta as the IdP and illustrate how to set up your Okta application and AWS Identity and Access Management (IAM) permissions. We also demonstrate how you can limit the access for your users to use only query editor v2 without giving them access to perform any admin functions on the AWS Management Console.

Solution overview

The high-level steps in this post are as follows:

1. Set up Okta, which contains your users organized into logical groups and AWS account federation application.
2. Set up two IAM roles: one that establishes a trust relationship between your IdP and AWS, and a second role that Okta uses to access Amazon Redshift.
3. Complete Okta advanced configuration:
   1. Finalize the Okta configuration by inputting the roles that you just created.
   2. Define a default relay state to direct users to Amazon Redshift query editor v2 after successful SAML authentication.
   3. Configure the SAML PrincipalTagAttribute. This element allows you to pass attributes as session tags in the SAML assertion. For more information about session tags, see Passing session tags in AWS STS.
4. Set up Amazon Redshift database groups:
   1. Create groups within the Amazon Redshift database to match the Okta groups.
   2. Authorize these groups to access certain schemas and tables.
   3. Access Amazon Redshift query editor v2 using your enterprise credentials and query your Amazon Redshift database.
5. Sign in to your Okta account and access the application assigned to you. The application directs you to Amazon Redshift query editor v2 using federated login.
6. Access and query your Amazon Redshift database.

Prerequisites

This post assumes that you have the following prerequisites:

• An Okta account – If you don’t have access to an Okta account, you can start a 30-day free trial.
• An Amazon Redshift cluster – For instructions, see Create a sample Amazon Redshift cluster.
• Query editor v2 configured – If this is the first time logging in to query editor v2 from your account, you first need to configure the encryption key. For more information, refer to Configuring your AWS account.

Set up Okta

First, we set up the Okta application and create users and groups. Complete the following steps:

1. Log in to your Okta admin console using the URL https://<prefix>-admin.okta.com/admin/dashboard, where <prefix> is specific to your account and was created at account setup.
2. On the admin console, choose Admin.
   
3. Under Directory in the navigation pane, choose People.
4. To add users, choose Add person.
   The following screenshot shows the users that we created.
   
5. To add groups into Okta, choose Groups in the navigation pane, then choose Add group.
   The following screenshot shows two groups that we created. We added Jane to analyst_users and Mike to bi_users.
   
6. Under Applications in the navigation pane, choose Applications and choose Browse App Catalog.
7. Search for AWS Account Federation and choose Add.
   
8. After you add the application, choose AWS Account Federation.
   
9. Leave the values in General Settings at their default and choose Next.
10. Under Sign-On Options, select SAML 2.0.
11. Choose the Identity Provider metadata link to download the metadata file in .xml format.
    

Configure IAM roles

Next, you set up an IAM role that establishes a trust relationship between the IdP and AWS. You also create an IAM role that Okta uses to access Amazon Redshift query editor v2.

1. On the IAM console, under Access management in the navigation pane, choose Identity providers.
2. Choose Add provider.
   
3. For Provider type¸ select SAML.
4. For Provider name¸ enter a name.
5. Choose Choose file and upload the metadata file you downloaded.
6. Choose Add provider.
   
   Now you create the IAM SAML 2.0 federation role.
7. On the IAM console, choose Roles in the navigation pane.
8. Choose Create role.
9. For Trusted entity type, select SAML 2.0 federation.
10. For SAML 2.0-based provider, choose the IdP you created in the previous step.
11. Select Allow programmatic and AWS Management Console access.
    
12. Choose Next and then choose Create Policy.
    

Amazon Redshift query editor v2 provides multiple managed policies to access the query editor. For a list of managed policies, refer to Configuring your AWS account. The managed policy enables you to limit the access for your users to use only query editor v2 without giving them access to perform any admin functions on the console. For this post, we use the AmazonRedshiftQueryEditorV2ReadSharing managed policy and create a custom policy.

In the following code, provide your Region, account, and cluster parameters to grant access to Amazon Redshift to get cluster credentials, create users, and allow users to join groups:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "RedshiftClusterPermissions",
            "Effect": "Allow",
            "Action": [
                "redshift:GetClusterCredentials",
                "redshift:CreateClusterUser",
                "redshift:JoinGroup"
            ],
            "Resource": [
                "arn:aws:redshift:<region>:<account>:cluster:<cluster>,                 
                "arn:aws:redshift:<region>:<account>:dbuser:<cluster>/${aws:PrincipalTag/RedshiftDbUser}",
                "arn:aws:redshift:<region>:<account>:dbgroup:<cluster>/analyst_users",
                "arn:aws:redshift:<region>:<account>:dbgroup:<cluster>/bi_users",
                "arn:aws:redshift:<region>:<account>:dbname:<cluster>/${redshift:DBName}"
            ]
        }
    ]
}

The group membership lasts only for the duration of the user session. Additionally, there is no CreateGroup permission because groups need to be manually created and granted DB privileges.

1. Attach the policy you created to the role.
   The following screenshot shows the summary page for the role.
   
2. Modify the trust relationships for your role and add sts:TagSession permission.
   When using session tags, trust policies for all roles connected to the IdP passing tags must have the sts:TagSession permission. For roles without this permission in the trust policy, the AssumeRole operation fails.
3. Choose Update policy.
   

Set up Okta advanced configuration

In this section, you finalize the Okta configuration by adding the IAM roles that you just created. You set up SAML PrincipalTag attributes such as RedshiftDbUser and RedshiftDbGroups, which are passed in the SAML assertion to federate the access to Amazon Redshift query editor v2. You also define a default relay state, which is the URL that users are directed to after a successful authentication through SAML.

1. In your Okta account, open the AWS Account Federation app.
2. On the Sign On tab, set Default Relay State to the query editor URL, using the format https://<region>.console.aws.amazon.com/sqlworkbench/home. For this post, we use https://eu-west-1.console.aws.amazon.com/sqlworkbench/home.
3. Choose Attributes and set up the following attribute mappings:
   1. Set the DB user using PrincipalTag:RedshiftDbUser. This uses the user name in the directory. This is a required tag and defines the database user that is used by query editor v2.
   2. Set the DB groups using PrincipalTag:RedshiftDbGroups. This uses the Okta groups to fill the principal tags. Its value must be a colon-separated list.
   3. Set the transitive keys using TransitiveTagKeys. This prevents users from changing the session tags in case of role chaining.

These tags are forwarded to the redshift:GetClusterCredentials API to get credentials for your cluster. The following table summarizes their attribute configuration.

4. Under Advanced Sign-on Settings¸ select Use Group Mapping.
5. Enter the IdP and IAM role ARNs, which are globally unique, and make sure that Okta is directed to your AWS account.
   
6. Authorize users to use the AWS Account Federation application by selecting their respective groups or individual user accounts. In this example, we authorized users by group.
   

Set up Amazon Redshift database groups

Next, you set up groups in the Amazon Redshift database to match the Okta groups. You also authorize these groups to access certain schemas and tables.

1. Log in to your Amazon Redshift cluster with an admin account.
2. Create groups that match the IdP group names, and grant the appropriate permissions to tables and schemas:

CREATE GROUP analyst_users;
CREATE GROUP bi_users;

ALTER DEFAULT PRIVILEGES IN SCHEMA sales_analysis
GRANT SELECT on TABLES to GROUP analyst_users;
GRANT USAGE on SCHEMA sales_analysis to GROUP analyst_users;
GRANT SELECT on ALL TABLES in SCHEMA sales_analysis to GROUP analyst_users;

ALTER DEFAULT PRIVILEGES IN SCHEMA sales_bi
GRANT SELECT on TABLES to GROUP bi_users;
GRANT USAGE on SCHEMA sales_bi to GROUP bi_users;
GRANT SELECT on ALL TABLES in SCHEMA sales_bi to GROUP bi_users;

In Okta, you created the user Jane and assigned Jane to the analyst_users group.

In the Amazon Redshift database, you created two database groups: analyst_users and bi_users.

When user Jane logs in via federated authentication to Amazon Redshift using query editor v2, the user is created if it doesn’t already exist and the analyst_users database group is assumed. The user Jane can query tables only in sales_analysis schema.

Because user Jane isn’t part of the bi_users group, when they try to access the sales_bi schema, they get a permission denied error.

The following diagram illustrates this configuration.

Access Amazon Redshift query editor v2

Now you’re ready to connect to your Amazon Redshift cluster using Amazon Redshift query editor v2 using federated login. Log in to your Okta account with your user credentials and under My Apps¸ choose Amazon Redshift Query Editor V2.

You’re redirected to the Amazon Redshift query editor v2 URL that you specified as the default relay state.

Connect to an Amazon Redshift database and run queries

Now let’s set up the connection to your Amazon Redshift cluster.

1. In the query editor, choose your cluster (right-click) and choose Create connection.
2. For Database, enter a name.
3. For Authentication, select Federated user.
   The user name is prepopulated with your federated login information.
4. Choose Create connection.
   

When you’re connected to your Amazon Redshift database, you can verify the connection details, as shown in the following screenshot. Notice the session-level group association as per the group assignment in your Okta application configuration. In this case, user Jane is assigned to the analyst_users group.

This user has access to SELECT all tables in the sales_analysis schema and no access to the sales_bi schema. You can run the following statements to test your access.

The following screenshot shows the results from a query to the sales_analysis.store_sales_us table.

When user Jane tries to access the tables in the sales_bi schema, they get a permission denied error.

Summary

In this post, we demonstrated how to federate SSO access to Amazon Redshift query editor v2 using Okta as your IdP. We showed how to set up Okta, set different PrinicpalTag attributes for query editor v2, and pass group memberships defined in your Okta IdP to your Amazon Redshift cluster. We showed how to log in to Amazon Redshift query editor v2 using federated login and validate the configuration by running a few queries. This solution allows you to control access to Amazon Redshift database objects, and your users can easily access Amazon Redshift clusters through query editor v2 using federated credentials without managing database users and passwords.

If you have any feedback or questions, please leave them in the comments.

About the Authors

Sumeet Joshi is an Analytics Specialist Solutions Architect based out of New York. He specializes in building large-scale data warehousing solutions. He has over 16 years of experience in the data warehousing and analytical space.

Bhanu Pittampally is an Analytics Specialist Solutions Architect based out of Dallas. He specializes in building analytical solutions. His background is in data and analytics for over 14 years. His LinkedIn profile can be found here.

Erol Murtezaoglu, a Technical Product Manager at AWS, is an inquisitive and enthusiastic thinker with a drive for self-improvement and learning. He has a strong and proven technical background in software development and architecture, balanced with a drive to deliver commercially successful products. Erol highly values the process of understanding customer needs and problems, in order to deliver solutions that exceed expectations.

Yanis Telaoumaten is a Software Development Engineer at AWS. His passions are building reliable software and creating tools to allow other engineers to work more efficiently. In the past years, he worked on identity, security and reliability of Redshift services

Post Syndicated from Sumeet Joshi original https://aws.amazon.com/blogs/big-data/federate-access-to-amazon-redshift-query-editor-v2-with-active-directory-federation-services-ad-fs-part-3/

In the first post of this series, Federate access to your Amazon Redshift cluster with Active Directory Federation Services (AD FS): Part 1, you set up Microsoft Active Directory Federation Services (AD FS) and Security Assertion Markup Language (SAML) based authentication and tested the SAML federation using a web browser.

In Part 2, you learned to set up an Amazon Redshift cluster and use federated authentication with AD FS to connect from a JDBC SQL client tool.

In this post, we walk through the steps to configure Amazon Redshift query editor v2 to work with AD FS federation SSO.

Organizations want to enable their end-users such as data analysts, data scientists, and database developers to use the query editor v2 to accelerate self-service analytics. Amazon Redshift query editor v2 lets users explore, analyze, and collaborate on data. You can use the query editor to create databases, schemas, tables, and load data from Amazon Simple Storage Service (Amazon S3) using the COPY command or by using a wizard. You can browse multiple databases and run queries on your Amazon Redshift data warehouse or data lake, or run federated queries to operational databases such as Amazon Aurora.

In this post, we show how you can use your corporate Active Directory (AD) and the SAML 2.0 AD FS identity provider (IdP) to enable your users to easily access Amazon Redshift clusters through query editor v2 using corporate user names without managing database users and passwords. We also demonstrate how you can limit the access for your users to use only the query editor without giving them access to perform any admin functions on the AWS Management Console.

Solution overview

After you follow the steps explained in Part 1, you set up a deep link for federated users via the SAML 2.0 RelayState parameter in AD FS. You use the user you set up in your AD in Part 1 (Bob) to authenticate using AD FS and control access to database objects based on the group the user is assigned to. You also test if user Bob is integrated with Amazon Redshift database groups as controlled in AD groups.

By the end of this post, you will have created a unique deep link that authenticates the user Bob using AD FS and redirects them directly to the query editor v2 console, where they’re authenticated using the federation SSO option.

The sign-in process is as follows:

1. The user chooses a deep link that redirects to the IdP for authentication with the information about the destination (query editor v2, in our case) URL embedded in the RelayState parameter. The user enters their credentials on the login page.
2. Your IdP (AD FS in the case) verifies the user’s identity in your organization.
3. Your IdP generates a SAML authentication response that includes assertions that identify the user and attributes about the user. The IdP sends this response to the user’s browser.
4. The user’s browser is redirected to the AWS Single Sign-On endpoint and posts the SAML assertion and the RelayState parameter.
5. The endpoint calls the AssumeRoleWithSAML API action to request temporary credentials from the AWS Identity and Access Management (IAM) role specified in the SAML assertion and creates a query editor v2 console sign-in URL that uses those credentials. The IAM role trusts the SAML federation entity and also has a policy that has access to query editor V2. If the SAML authentication response includes attributes that map to multiple IAM roles, the user is first prompted to choose the role to use for access to the query editor v2 console. The sign-in URL is the one specified by the RelayState parameter.
6. AWS sends the sign-in URL back to the user’s browser as a redirect.
7. The user’s browser is redirected to the Amazon Redshift query editor v2 console defined by the RelayState parameter.

The following diagram illustrates this flow.

In this post, we walk you through the following steps:

1. Set up the Sales group in AD and set up the PrincipalTag claim rules in AD FS.
2. Update the IAM roles.
3. Construct the SSO URL to authenticate and redirect users to the Amazon Redshift query editor v2 console.
4. Set up Amazon Redshift database groups and permissions on the Amazon Redshift cluster.
5. Set up Amazon Redshift query editor v2 to use federated authentication with AD FS to connect directly from the query editor interface.
6. Query Amazon Redshift objects to validate your authorization.

Prerequisites

For this walkthrough, complete the following prerequisite steps:

1. Create an Amazon Redshift cluster. For instructions, refer to Create a sample Amazon Redshift cluster or complete the steps in Part 2 of this series.
2. Complete the steps in Part 1 to set up SAML federation with AD FS:
   1. Set up an AD domain controller using an AWS CloudFormation template on a Windows 2016 Amazon Elastic Compute Cloud (Amazon EC2) instance.
   2. Configure federation in AD FS.
   3. Configure AWS as the relying party with AD FS using an IAM SAML provider and SAML roles with an attached policy to allow access to the Amazon Redshift cluster.
   4. Configure claim rules.
   5. Test the SAML authentication using a web browser.
3. Verify that your IdP supports RelayState and is enabled. If you’re using AD FS 2.0, you need to download and install either Update Rollup 3 or Update Rollup 2 from Microsoft to enable the RelayState parameter.

Configure AD and AD FS

After you configure your AD FS and AD services by following the instructions in Part 1, you can set up the following AD group and claim rules.

In this post, you use the user Bob to log in to Amazon Redshift and check if Bob can access the Sales and Marketing schemas on the Amazon Redshift cluster. To create the sales group and assign the user [email protected] to it, log in to your AD FS server (Amazon EC2 machine) that you created in Part 1 and use the Windows command tool to run the following command:

dsadd group "cn=RSDB-sales, cn=Users, dc=adfsredshift, dc=com" -members "cn=Bob, cn=Users, dc=adfsredshift, dc=com"

Now you’re ready to create your custom claim rules: PrincipalTag:RedshiftDbUser and PrincipalTag:RedshiftDbGroup.

PrincipalTag:RedshiftDbUser

The custom claim rule PrincipalTag:RedshiftDbUser is mapped to the universal principal name in AD FS. When a user authenticates through federated SSO, this claim rule is mapped to the user name. If user doesn’t exist in the Amazon Redshift database, then the user is automatically created. The auto create option is granted through an IAM policy that is attached to the IAM role. The CreateClusterUser permission allows for auto creation of the user (you set this up as part of Part 1 as a prerequisite).

Complete the following steps to create your custom claim rule:

1. 1. On the AD FS management console, choose Relying Party Trusts.
   2. Choose Edit Claim Issuance Policy.
   3. Choose Choose Rule Type.
   4. For Claim rule template, choose Send Claims Using a Custom Rule.
   5. Choose Next.
   6. For Claims rule name, enter RedshiftDbUser.
   7. Add the following custom rule:

      c:[Type == "http://schemas.microsoft.com/ws/2008/06/identity/claims/windowsaccountname", Issuer == "AD AUTHORITY"]
       => issue(store = "Active Directory", types = ("https://aws.amazon.com/SAML/Attributes/PrincipalTag:RedshiftDbUser"), query = ";userPrincipalName;{0}", param = c.Value);

   8. Choose Finish.
   9. Capture the claim rules sent in a SAML assertion response through your browser. For instructions, refer to How to view a SAML response in your browser for troubleshooting.

In my example, I use the following SAML attribute for the RedshiftDbUser PrincipalTag:

<Attribute Name="https://aws.amazon.com/SAML/Attributes/PrincipalTag:RedshiftDbUser"> <AttributeValue>[email protected]</AttributeValue> 
</Attribute>

PrincipalTag:RedshiftDbGroup

The custom claim rule PrincipalTag:RedshiftDbGroup is built out of AD groups that the user is a member of. This rule is mapped to the Amazon Redshift database groups. The AD groups and Amazon Redshift database group names should match. JoinGroup permission set in the IAM policy allows the user to assume a database group and is session based. If the user is mapped to multiple groups in the AD group, the SAML assertion response should send those groups in : separated values and not as multiple value claims. The following steps demonstrate how to send AD groups as : separated values.

In this example, the user Bob is assigned to the marketing and sales groups. The following code shows how to send multiple groups through the SAML response when the user is in multiple groups, and also how to handle the situation when a user doesn’t exist in any particular group.

1. Follow the same steps as in the previous section to create the rule Marketing, using the following code for the custom rule:

   c:[Type == " http://temp/groups", Value =~ "RSDB-Marketing"] => add(Type = " http://temp/marketing", Value = c.Value);

2. Create the rule MarketingNotExists using the following code:

   NOT EXISTS([Type == "http://temp/variable", Value =~ "RSDB-marketing"]) => add(Type = "http://temp/marketing", Value = ""); 

3. Create the rule sales using the following code:

   c:[Type == " http://temp/groups", Value =~ "RSDB-sales"] => add(Type = " http://temp/marketing", Value = c.Value);

4. Create the rule SalesNotExists using the following code:

   NOT EXISTS([Type == "http://temp/variable", Value =~ "RSDB-sales"])
    => add(Type = "http://temp/sales", Value = ""); 

5. Create the rule RedshiftDbGroups using the following code:

   c:[Type == "http://temp/marketing"]
    && c2:[Type == "http://temp/sales"]
    => issue(Type = "https://aws.amazon.com/SAML/Attributes/PrincipalTag:RedshiftDbGroups", Value = c.Value + ":" + c2.Value);

The following screenshot shows the list of rules that I created in my AD FS. Note the number of rules and the order in which they’re positioned. We created rules 6–11 as part of this post.

If you see a similar SAML response for RedshiftDbGroups, your setup is good:

<Attribute Name="https://redshift.amazon.com/SAML/Attributes/PrincipalTag:RedshiftDbGroups"> <AttributeValue> marketing:sales</AttributeValue>

If a user doesn’t exist in one of the groups, an empty value is passed to the claim rule. For example, if user bob is removed from the marketing group, the SAML response for PrincipalTag:RedshiftDbGroup would be :sales.

Update IAM roles

In Part 1 of this series, you created two IAM roles: ADFZ-Dev and ADFZ-Production. These two roles aren’t yet set up with grants on the query editor. In this section, you update these roles with query editor permissions.

Amazon Redshift query editor v2 provides multiple managed policies to access the query editor. For a list of all the managed policies, refer to Configuring your AWS account. For this post, we attach the AmazonRedshiftQueryEditorV2ReadSharing managed policy to the roles.

1. On the IAM console, choose Roles in the navigation pane.
2. Choose the role ADFZ-Dev.
3. Choose Add permissions and then Attach policies.
   
4. Under Other permission policies, search for the AmazonRedshiftQueryEditorV2ReadSharing managed policy.
5. Select the policy and choose Attach policies.
   
6. Modify the trust relationships for your role and add sts:TagSession. While in role select Trust relationships and click on Edit trust policy.When using session tags, trust policies for all roles connected to the IdP passing tags must have the sts:TagSession permission. For roles without this permission in the trust policy, the AssumeRole operation fails.
7. Choose Update policy.
   
8. Repeat these steps to attach the AmazonRedshiftQueryEditorV2ReadSharing managed policy to the ADFZ-Production role.

Limiting User access only to Query Editor

If you would like to limit users only access Query Editor then  update the policy redshift-marketing that you have created in Part 1 blog post as below.

Note: once updated, users will lose admin privileges such as create cluster.

Replace the region, account, and cluster parameters. This custom policy grants access to Amazon Redshift to get cluster credentials, create users, and allow users to join groups.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "RedshiftClusterPermissions",
            "Effect": "Allow",
            "Action": [
                "redshift:GetClusterCredentials",
                "redshift:CreateClusterUser",
                "redshift:JoinGroup"
            ],
            "Resource": [
                "arn:aws:redshift:<region>:<account>:cluster:<cluster>,
                "arn:aws:redshift:<region>:<account>:dbuser:<cluster>/${aws:PrincipalTag/RedshiftDbUser}",
                "arn:aws:redshift:<region>:<account>:dbgroup:<cluster>/marketing",
                "arn:aws:redshift:<region>:<account>:dbgroup:<cluster>/sales",
                "arn:aws:redshift:<region>:<account>:dbname:<cluster>/${redshift:DBName}"
            ]
        }
    ]
}

There are a few important things to note:

1. The group membership lasts only for the duration of the user session.
2. There is no CreateGroup permission because groups need to be manually created and granted DB privileges.

Generate the SSO destination URL as the Amazon Redshift query editor v2 console

In this step, you construct the sign-in URL for the AD FS IdP to redirect users to the Amazon Redshift query editor v2 console. For instructions, refer to How to Use SAML to Automatically Direct Federated Users to a Specific AWS Management Console Page.

To provide a full SSO experience for the end-users, the SAML response can include an optional parameter called RelayState. This parameter contains the destination URL.

Microsoft provides a tool to help generate these SSO URLs for AD FS called the AD FS 2.0 RelayState Generator.

To build this URL, you need three pieces of information:

• IdP URL string – The string is in the format https://ADFSSERVER/adfs/ls/idpinitiatedsignon.aspx. For this post, we use https://EC2AMAZ-F9TJOIC.adfsredshift.com/adfs/ls/IdpInitiatedSignOn.aspx.
• Relying party identifier – For AWS, this is urn:amazon:webservices.
• Relay state or target app – This is the AWS Management Console URL you want your authenticated users redirect to. In this case, it’s https://eu-west-1.console.aws.amazon.com/sqlworkbench/home?. For this post, we use the eu-west-1 Region, but you can adjust this as needed.

I followed the instructions in How to Use SAML to Automatically Direct Federated Users to a Specific AWS Management Console Page and used the AD FS 2.0 RelayState Generator to generate the URL shown in the following screenshot.

The following is an example of the final URL that you use to get authenticated and also get redirected to Amazon Redshift query editor v2 (this URL won’t work in your setup because it has been created specifically for an AD FS server in my account): https://EC2AMAZ-F9TJOIC.adfsredshift.com/adfs/ls/IdpInitiatedSignOn.aspx?RelayState=RPID%3Durn%253Aamazon%253Awebservices%26RelayState%3Dhttps%253A%252F%252Feu-west-1.console.aws.amazon.com%252Fsqlworkbench%252Fhome%253Fregion%253Deu-west-1%2523%252Fclient

You can now save this URL and use it from anywhere you can reach your AD FS server. After you enter the URL in a browser, you first authenticate to AD FS, then you’re redirected to the Amazon Redshift query editor v2 console.

Set up DB groups on Amazon Redshift cluster

In this step, you set up your Amazon Redshift database group. This step is necessary because when the user is authenticated, they have to be part of an Amazon Redshift DB group with proper permissions set on a schema or table (or view).

In Active Directory, the user Bob is part of two groups: Sales and Marketing. In your Amazon Redshift database, you have three database groups: Sales, Marketing, and Finance.

When user Bob logs in via federated authentication, the user assumes the Sales and Marketing database groups, so this user can query tables in both the Sales and Marketing schemas. Because the user Bob isn’t part of the Finance group, when they try to access the Finance schema, they receive a permission denied error.

The following diagram illustrates this configuration.

Complete the following steps to set up your DB groups:

1. Connect as awsuser (a superuser).
2. Create three database groups:

   CREATE GROUP sales;
   CREATE GROUP marketing;
   CREATE GROUP finance;

3. Create three schemas:

   CREATE SCHEMA sales;
   CREATE SCHEMA marketing;
   CREATE SCHEMA finance;

4. Create a table in each schema:

   CREATE TABLE IF NOT EXISTS marketing.employee
   (
   	n_empkey INTEGER   
   	,n_name CHAR(25)   
   	,n_regionkey INTEGER   
   	,n_comment VARCHAR(152)   
   )
   DISTSTYLE AUTO
    SORTKEY (n_empkey);
   
   CREATE TABLE IF NOT EXISTS sales.employee_sales
   (
   	n_empkey INTEGER   
   	,n_name CHAR(25)   
   	,n_regionkey INTEGER   
   	,n_comment VARCHAR(152)   
   )
   DISTSTYLE AUTO
    SORTKEY (n_empkey);
   
   
   CREATE TABLE IF NOT EXISTS finance.accounts
   (
   	account_id INTEGER   
   	,account_name CHAR(25)   
   	 
   )
   DISTSTYLE AUTO
    SORTKEY (account_id);

5. Insert sample data into the three tables:

   INSERT INTO marketing.employee
   VALUES(1, 'Bob', 0, 'Marketing');
   
   INSERT INTO sales.employee_sales
   VALUES(1, 'John', 0, 'Sales');
   
   INSERT INTO finance.accounts
   VALUES(1, 'online company');

6. Validate the data is available in the tables:

   Select * from marketing.employee;
   Select * from sales.employee_sales;
   Select * from finance.accounts;

You can now set up appropriate privileges for the sales, finance, and marketing groups. Groups are collections of users who are all granted privileges associated with the group. You can use groups to assign privileges by job function. For example, you can create different groups for sales, administration, and support, and give the users in each group the appropriate access to the data they require for their work. You can grant or revoke privileges at the group level, and those changes apply to all members of the group, except for superusers.

7. Enter the following SQL queries to grant access to all tables in the sales schema to the sales group, access to all tables in the marketing schema to the marketing group, and access to all tables in the finance schema to the finance group:

ALTER DEFAULT PRIVILEGES IN SCHEMA sales
GRANT SELECT on TABLES to GROUP sales;
GRANT USAGE on SCHEMA sales to GROUP sales;
GRANT SELECT on ALL TABLES in SCHEMA sales to GROUP sales;

ALTER DEFAULT PRIVILEGES IN SCHEMA marketing
GRANT SELECT on TABLES to GROUP marketing;
GRANT USAGE on SCHEMA marketing to GROUP marketing;
GRANT SELECT on ALL TABLES in SCHEMA marketing to GROUP marketing;

ALTER DEFAULT PRIVILEGES IN SCHEMA finance
GRANT SELECT on TABLES to GROUP finance;
GRANT USAGE on SCHEMA finance to GROUP finance;
GRANT SELECT on ALL TABLES in SCHEMA finance to GROUP finance;

Access Amazon Redshift query editor v2 through federated authentication

Now that you have completed your SAML integration, deep link setup, and DB groups and access rights configuration, you can set up Amazon Redshift query editor v2 to use federated authentication with AD FS to connect from directly from the query editor interface.

1. Navigate to the deep link URL you created earlier.
   You’re redirected to the AD FS login page.
2. Sign in as [email protected].
   For this post, I accessed this URL from an Amazon EC2 machine, but you can access it from any location where you can reach the AD FS IdP.
   After AD FS successfully authenticates you, it redirects to the AWS SSO endpoint and posts the SAML assertion and RelayState parameter. Because you configured two IAM roles on the AWS side, you’re prompted to select a role.
3. Select a role (for this example, ADFZ-Dev) and choose Sign In.
   
   AWS sends the sign-in URL that is based on the RelayState value back to your browser as a redirect. Your browser is redirected to the query editor v2 console automatically.
   
4. Right-click your Amazon Redshift cluster (for this post, redshift-cluster-1) and choose Edit connection.
   
   The value for User name is automatically populated, and Federated Access is automatically selected.
5. Choose Edit connection to save the connection and log in to the database.
   
   After you’re successfully logged in, you can browse the database objects in the left pane.
6. Test the connection by running the following query:

   select * from stv_sessions;

The following screenshot shows the output.

The output shows that the user [email protected] was authenticated using AD FS. The user also joined the marketing and Sales groups as enforced by the AD FS PrincipalTag:RedshiftDbGroups claim rule and the policy associated with the ADFZ-Dev role, which the user assumes during this session.

Run queries to validate authorization through federated groups

In this final step, you validate how the groups and membership configured in AD are seamlessly integrated with Amazon Redshift database groups.

Run the following query against the marketing and sales schema:

select * from marketing.employee;
select * from sales.employee_sales;

The following screenshots shows the output:

The preceding images show that AD user Bob is part of the AD group RSDB-marketing and RSDB-sales, which are mapped to the DB groups marketing and sales. These DB groups have select access to the schemas marketing and sales and all tables in those schemas. Therefore, the user can successfully query the tables.

To run a query against the finance schema, enter the following code:

select * from finance.accounts;

The following screenshot shows the output.

The output shows that Bob is only part of the AD groups RSDB-marketing and RSDB-sales. Due to the way the claim rule is set up, Bob doesn’t have access to the database group finance, and therefore the query returns with a permission denied error.

Clean up

To avoid incurring future charges, delete the resources by deleting the CloudFormation stack. This cleans up all the resources from your AWS account that you set up in Part 1.

Conclusion

In this post, we demonstrated how to set up an AD FS server, configure different PrincipalTag attributes used for Amazon Redshift query editor v2, and generate an SSO URL with the query editor as the destination location. You then connected to the Amazon Redshift DB cluster using a database user with administrator privileges to set up DB groups and permissions, and used a federated user authentication with AD FS to run several queries. This solution enables you to control access to your Amazon Redshift database objects by using AD groups and memberships seamlessly.

If you have any feedback or questions, please leave them in the comments.

About the Authors

Sumeet Joshi is an Analytics Specialist Solutions Architect based out of New York. He specializes in building large-scale data warehousing solutions. He has over 16 years of experience in the data warehousing and analytical space.

Bhanu Pittampally is an Analytics Specialist Solutions Architect based out of Dallas. He specializes in building analytical solutions. His background is in data and analytics for over 14 years. His LinkedIn profile can be found here.

Erol Murtezaoglu, a Technical Product Manager at AWS, is an inquisitive and enthusiastic thinker with a drive for self-improvement and learning. He has a strong and proven technical background in software development and architecture, balanced with a drive to deliver commercially successful products. Erol highly values the process of understanding customer needs and problems, in order to deliver solutions that exceed expectations.

Yanis Telaoumaten is a Software Development Engineer at AWS. His passions are building reliable software and creating tools to allow other engineers to work more efficiently. In the past years, he worked on identity, security and reliability of Redshift services

Post Syndicated from Sam Selvan original https://aws.amazon.com/blogs/big-data/real-time-analytics-with-amazon-redshift-streaming-ingestion/

Amazon Redshift is a fast, scalable, secure, and fully managed cloud data warehouse that makes it simple and cost-effective to analyze all your data using standard SQL. Amazon Redshift offers up to three times better price performance than any other cloud data warehouse. Tens of thousands of customers use Amazon Redshift to process exabytes of data per day and power analytics workloads such as high-performance business intelligence (BI) reporting, dashboarding applications, data exploration, and real-time analytics.

We’re excited to launch Amazon Redshift streaming ingestion for Amazon Kinesis Data Streams, which enables you to ingest data directly from the Kinesis data stream without having to stage the data in Amazon Simple Storage Service (Amazon S3). Streaming ingestion allows you to achieve low latency in the order of seconds while ingesting hundreds of megabytes of data into your Amazon Redshift cluster.

In this post, we walk through the steps to create a Kinesis data stream, generate and load streaming data, create a materialized view, and query the stream to visualize the results. We also discuss the benefits of streaming ingestion and common use cases.

The need for streaming ingestion

We hear from our customers that you want to evolve your analytics from batch to real time, and access your streaming data in your data warehouses with low latency and high throughput. You also want to enrich your real-time analytics by combining them with other data sources in your data warehouse.

Use cases for Amazon Redshift streaming ingestion center around working with data that is generated continually (streamed) and needs to be processed within a short period (latency) of its generation. Sources of data can vary, from IoT devices to system telemetry, utility service usage, geolocation of devices, and more.

Before the launch of streaming ingestion, if you wanted to ingest real-time data from Kinesis Data Streams, you needed to stage your data in Amazon S3 and use the COPY command to load your data. This usually involved latency in the order of minutes and needed data pipelines on top of the data loaded from the stream. Now, you can ingest data directly from the data stream.

Solution overview

Amazon Redshift streaming ingestion allows you to connect to Kinesis Data Streams directly, without the latency and complexity associated with staging the data in Amazon S3 and loading it into the cluster. You can now connect to and access the data from the stream using SQL and simplify your data pipelines by creating materialized views directly on top of the stream. The materialized views can also include SQL transforms as part of your ELT (extract, load and transform) pipeline.

After you define the materialized views, you can refresh them to query the most recent stream data. This means that you can perform downstream processing and transformations of streaming data using SQL at no additional cost and use your existing BI and analytics tools for real-time analytics.

Amazon Redshift streaming ingestion works by acting as a stream consumer. A materialized view is the landing area for data that is consumed from the stream. When the materialized view is refreshed, Amazon Redshift compute nodes allocate each data shard to a compute slice. Each slice consumes data from the allocated shards until the materialized view attains parity with the stream. The very first refresh of the materialized view fetches data from the TRIM_HORIZON of the stream. Subsequent refreshes read data from the last SEQUENCE_NUMBER of the previous refresh until it reaches parity with the stream data. The following diagram illustrates this workflow.

Setting up streaming ingestion in Amazon Redshift is a two-step process. You first need to create an external schema to map to Kinesis Data Streams and then create a materialized view to pull data from the stream. The materialized view must be incrementally maintainable.

Create a Kinesis data stream

First, you need to create a Kinesis data stream to receive the streaming data.

1. On the Amazon Kinesis console, choose Data streams.
2. Choose Create data stream.
3. For Data stream name, enter ev_stream_data.
4. For Capacity mode, select On-demand.
   
5. Provide the remaining configurations as needed to create your data stream.

Generate streaming data with the Kinesis Data Generator

You can synthetically generate data in JSON format using the Amazon Kinesis Data Generator (KDG) utility and the following template:

{
    
   "_id" : "{{random.uuid}}",
   "clusterID": "{{random.number(
        {   "min":1,
            "max":50
        }
    )}}", 
    "connectionTime": "{{date.now("YYYY-MM-DD HH:mm:ss")}}",
    "kWhDelivered": "{{commerce.price}}",
    "stationID": "{{random.number(
        {   "min":1,
            "max":467
        }
    )}}",
      "spaceID": "{{random.word}}-{{random.number(
        {   "min":1,
            "max":20
        }
    )}}",
 
   "timezone": "America/Los_Angeles",
   "userID": "{{random.number(
        {   "min":1000,
            "max":500000
        }
    )}}"
}

The following screenshot shows the template on the KDG console.

Load reference data

In the previous step, we showed you how to load synthetic data into the stream using the Kinesis Data Generator. In this section, you load reference data related to electric vehicle charging stations to the cluster.

Download the Plug-In EVerywhere Charging Station Network data from the City of Austin’s open data portal. Split the latitude and longitude values in the dataset and load it in to a table with the following schema.

CREATE TABLE ev_station
  (
     siteid                INTEGER,
     station_name          VARCHAR(100),
     address_1             VARCHAR(100),
     address_2             VARCHAR(100),
     city                  VARCHAR(100),
     state                 VARCHAR(100),
     postal_code           VARCHAR(100),
     no_of_ports           SMALLINT,
     pricing_policy        VARCHAR(100),
     usage_access          VARCHAR(100),
     category              VARCHAR(100),
     subcategory           VARCHAR(100),
     port_1_connector_type VARCHAR(100),
     voltage               VARCHAR(100),
     port_2_connector_type VARCHAR(100),
     latitude              DECIMAL(10, 6),
     longitude             DECIMAL(10, 6),
     pricing               VARCHAR(100),
     power_select          VARCHAR(100)
  ) DISTTYLE ALL

Create a materialized view

You can access your data from the data stream using SQL and simplify your data pipelines by creating materialized views directly on top of the stream. Complete the following steps:

1. Create an external schema to map the data from Kinesis Data Streams to an Amazon Redshift object:

   CREATE EXTERNAL SCHEMA evdata FROM KINESIS
   IAM_ROLE 'arn:aws:iam::0123456789:role/redshift-streaming-role';

2. Create an AWS Identity and Access Management (IAM) role (for the policy, see Getting started with streaming ingestion).

Now you can create a materialized view to consume the stream data. You can choose to use the SUPER datatype to store the payload as is in JSON format or use Amazon Redshift JSON functions to parse the JSON data into individual columns. For this post, we use the second method because the schema is well defined.

3. Create the materialized view so it’s distributed on the UUID value from the stream and is sorted by the approximatearrivaltimestamp value:

   CREATE MATERIALIZED VIEW ev_station_data_extract DISTKEY(5) sortkey(1) AS
       SELECT approximatearrivaltimestamp,
       partitionkey,
       shardid,
       sequencenumber,
       json_extract_path_text(from_varbyte(data, 'utf-8'),'_id')::character(36) as ID,
       json_extract_path_text(from_varbyte(data, 'utf-8'),'clusterID')::varchar(30) as clusterID,
       json_extract_path_text(from_varbyte(data, 'utf-8'),'connectionTime')::varchar(20) as connectionTime,
       json_extract_path_text(from_varbyte(data, 'utf-8'),'kWhDelivered')::DECIMAL(10,2) as kWhDelivered,
       json_extract_path_text(from_varbyte(data, 'utf-8'),'stationID')::DECIMAL(10,2) as stationID,
       json_extract_path_text(from_varbyte(data, 'utf-8'),'spaceID')::varchar(100) as spaceID,
       json_extract_path_text(from_varbyte(data, 'utf-8'),'timezone')::varchar(30) as timezone,
       json_extract_path_text(from_varbyte(data, 'utf-8'),'userID')::varchar(30) as userID
       FROM evdata."ev_station_data";

4. Refresh the materialized view:

   REFRESH MATERIALIZED VIEW ev_station_data_extract;

The materialized view doesn’t auto-refresh while in preview, so you should schedule a query in Amazon Redshift to refresh the materialized view once every minute. For instructions, refer to Scheduling SQL queries on your Amazon Redshift data warehouse.

Query the stream

You can now query the refreshed materialized view to get usage statistics:

SELECT to_timestamp(connectionTime, 'YYYY-MM-DD HH24:MI:SS') as connectiontime
,SUM(kWhDelivered) AS Energy_Consumed
,count(distinct userID) AS #Users
from ev_station_data_extract
group by to_timestamp(connectionTime, 'YYYY-MM-DD HH24:MI:SS')
order by 1 desc;

The following table contains the results.

Next, you can join the materialized view with the reference data to analyze the charging station consumption data for the last 5 minutes and break it down by station category:

SELECT to_timestamp(connectionTime, 'YYYY-MM-DD HH24:MI:SS') as connectiontime
,SUM(kWhDelivered) AS Energy_Consumed
,count(distinct userID) AS #Users
,st.category
from ev_station_data_extract ext
join ev_station st on
ext.stationID = st.siteid
where approximatearrivaltimestamp > current_timestamp -interval '5 minutes'
group by to_timestamp(connectionTime, 'YYYY-MM-DD HH24:MI:SS'),st.category
order by 1 desc, 2 desc

The following table contains the results.

Visualize the results

You can set up a simple visualization using Amazon QuickSight. For instructions, refer to Quick start: Create an Amazon QuickSight analysis with a single visual using sample data.

We create a dataset in QuickSight to join the materialized view with the charging station reference data.

Then, create a dashboard showing energy consumption and number of connected users over time. The dashboard also shows the list of locations on the map by category.

Streaming ingestion benefits

In this section, we discuss some of the benefits of streaming ingestion.

High throughput with low latency

Amazon Redshift can handle and process several gigabytes of data per second from Kinesis Data Streams. (Throughput is dependent on the number of shards in the data stream and the Amazon Redshift cluster configuration.) This allows you to experience low latency and high bandwidth when consuming streaming data, so you can derive insights from your data in seconds instead of minutes.

As we mentioned earlier, the key differentiator with the direct ingestion pull approach in Amazon Redshift is lower latency, which is in seconds. Contrast this to the approach of creating a process to consume the streaming data, staging the data in Amazon S3, and then running a COPY command to load the data into Amazon Redshift. This approach introduces latency in minutes due to the multiple steps involved in processing the data.

Straightforward setup

Getting started is easy. All the setup and configuration in Amazon Redshift uses SQL, which most cloud data warehouse users are already familiar with. You can get real-time insights in seconds without managing complex pipelines. Amazon Redshift with Kinesis Data Streams is fully managed, and you can run your streaming applications without requiring infrastructure management.

Increased productivity

You can perform rich analytics on streaming data within Amazon Redshift and using existing familiar SQL without needing to learn new skills or languages. You can create other materialized views, or views on materialized views, to do most of your ELT data pipeline transforms within Amazon Redshift using SQL.

Streaming ingestion use cases

With near-real time analytics on streaming data, many use cases and industry verticals applications become possible. The following are just some of the many application use cases:

• Improve the gaming experience – You can focus on in-game conversions, player retention, and optimizing the gaming experience by analyzing real-time data from gamers.
• Analyze clickstream user data for online advertising – The average customer visits dozens of websites in a single session, yet marketers typically analyze only their own websites. You can analyze authorized clickstream data ingested into the warehouse to assess your customer’s footprint and behavior, and target ads to your customers just-in-time.
• Real-time retail analytics on streaming POS data – You can access and visualize all your global point of sale (POS) retail sales transaction data for real-time analytics, reporting, and visualization.
• Deliver real-time application insights – With the ability to access and analyze streaming data from your application log files and network logs, developers and engineers can conduct real-time troubleshooting of issues, deliver better products, and alert systems for preventative measures.
• Analyze IoT data in real time – You can use Amazon Redshift streaming ingestion with Amazon Kinesis services for real-time applications such as device status and attributes such as location and sensor data, application monitoring, fraud detection, and live leaderboards. You can ingest streaming data using Kinesis Data Streams, process it using Amazon Kinesis Data Analytics, and emit the results to any data store or application using Kinesis Data Streams with low end-to-end latency.

Conclusion

This post showed how to create Amazon Redshift materialized views to ingest data from Kinesis data streams using Amazon Redshift streaming ingestion. With this new feature, you can easily build and maintain data pipelines to ingest and analyze streaming data with low latency and high throughput.

The streaming ingestion preview is now available in all AWS Regions where Amazon Redshift is available. To get started with Amazon Redshift streaming ingestion, provision an Amazon Redshift cluster on the current track and verify your cluster is running version 1.0.35480 or later.

For more information, refer to Streaming ingestion (preview), and check out the demo Real-time Analytics with Amazon Redshift Streaming Ingestion on YouTube.

About the Author

Sam Selvan is a Senior Analytics Solution Architect with Amazon Web Services.

Post Syndicated from Sarathi Balakrishnan original https://aws.amazon.com/blogs/big-data/modernize-your-healthcare-clinical-quality-data-repositories-with-amazon-redshift-data-vault/

With the shift to value-based care, tapping data to its fullest to improve patient satisfaction tops the priority of healthcare executives everywhere. To achieve this, reliance on key technology relevant to their sphere is a must. This is where data lakes can help. A data lake is an architecture that can assist providers in storing, sharing, and utilizing electronic health records and other patient data. Healthcare organizations are already utilizing data lakes to unite heterogeneous data from across hospital systems. The use of data lakes has aided in the effective transformation of the organizational culture, resulting in a data-driven approach to resolving issues. Healthcare clinical quality metric systems stream massive amounts of structured and semi-structured data into a data lake in real time from various sources. However, storing structured data in a data lake and applying schema-on-read leads to quality issues and adds complexity to the simple structured data process.

In this post, we demonstrate how to modernize your clinical quality data repositories with Amazon Redshift Data Vault.

How healthcare systems use data lakes

Data lakes are popular in healthcare systems because they allow the integration and exploratory analysis of diverse data. Data lakes comprise multiple data assets stored within a Hadoop ecosystem with minimal alterations to the original data format (or file). Because of this, the data lake lacks schema-on-write functionality. In data lakes, information is accessed using a methodology called schema-on-read. On the other hand, a data warehouse is a subject-oriented, time-variant, and centrally controlled database system designed for the management of mixed workloads and large queries. The schema of the data warehouse is predefined, the data written to it must conform to its schema (schema-on-write). It’s perfect for structured data storage.

Because the healthcare industry wants to preserve the data in raw format for compliance and regulatory requirements, data lakes are widely used in clinical quality tracking systems even though the incoming data are structured and semi-structured. For business and analytical use cases, the data in a data lake is further converted into a dimensional schema and loaded into a data warehouse. Data warehouses require several months of modeling, mapping, ETL (extract, transform, and load) planning and creation, and testing. The result is consistent, validated data for reporting and analytics.

The following diagram shows a typical clinical quality repository in a current healthcare system.

Data lakes offer information in its raw form, as well as a specialized means to obtaining data that applies the schema-on-read. In general, data lake users are experienced analysts familiar with data wrangling techniques that apply schema-on-read or understand data content from unstructured formats. Business users and data visualization builders struggle in the absence of powerful purpose-built search capabilities and data extraction algorithms. That isn’t to say data lakes are devoid of metadata or rules controlling their use, security, or administration. It’s the exact opposite. A successful data lake structures its data in such a way that it promotes better and more efficient access, and reuses data management methods or provides new tools that increase search and general knowledge of the data content.

Challenges in the data lake

However, data modeling shouldn’t be disregarded while employing the schema-on-read approach to handle data. A lack of meaningful data structure might lead to data quality problems, integration issues, performance issues, and deviations from organizational goals. Storing structured data in a data lake and then applying schema-on-read generates problems with data quality and complicates a basic structured data operation. You can also keep these structured datasets in data warehouses by skipping the data lake, but this increases the complexity of scaling, data transformation, and loading (ETL). But data warehouse ETL jobs apply a large number of business rules and heavily transform the data from its raw form to make the data fit one or more business purposes.

Dimensional representation data warehouses are located close to business applications but away from the source. This brings new challenges, like reverse tracking the data to its source to identify potential data errors and keep the original source for regulatory validation. In clinical quality reporting and regulatory reporting, accuracy is key. To provide high accuracy and data quality, developers often reverse track the data to its original source. This is very complex and sometimes not possible in a data warehouse model because the data loses its raw form. Compared to data warehouses, data lakes perform poorly in terms of dataset identification, processing, and catalog management. It’s complex to securely share data in a data lake between accounts. Cloud data lakes show improvement in this area, but some human intervention is still needed to make sure the data catalogs and security match healthcare requirements and standards.

Design and build a Data Vault model in Amazon Redshift

Amazon Redshift is a relational database system based on the PostgreSQL standard. It’s designed to efficiently run online analytical processing (OLAP) queries on petabyte-scale data. It also offers other query-handling efficiencies such as parallel processing, columnar database design, column data compression, a query optimizer, and compiled query code. A cluster is at the heart of every Amazon Redshift deployment. Each cluster consists of a leader node and one or more compute nodes, all of which are linked by high-speed networks.

Amazon Redshift RA3 instances with managed storage allow you to choose the number of nodes based on your performance requirements. The RA3 instance type can scale your data warehouse storage capacity automatically without manual intervention, and without needing to add additional compute resources. The number of nodes you choose is determined by the quantity of your data and the query processing performance you want.

The following diagram illustrates the Amazon Redshift RA3 instance architecture.

The interface to your business intelligence application is provided by a leader node. It provides conventional PostgreSQL JDBC/ODBC query and response interfaces. It acts as a traffic cop, routing requests from client applications to the proper compute nodes and managing the results that are delivered. It also distributes ingested data to computing nodes so that your databases may be built.

Amazon Redshift features like streaming, RA3’s near-limitless storage, Amazon Redshift ML, the SUPER data type, automatic table optimization, materialized views, and Amazon Redshift Spectrum open up the possibility of modernizing healthcare clinical quality data repositories and standardizing the data model for fast access and high accuracy. These features make RA3 instances the perfect candidate for your clinical quality repository. Additionally, the data sharing capability in Amazon Redshift keeps a single source for truth across the organization and removes the need for data silos. It’s tightly coupled with other AWS services, which allows you to take advantage of the AWS Cloud ecosystem and get the most out of your data with less effort.

Standardizing data modeling has two benefits: it allows you to reuse technical and organizational procedures (for example, ETL and project management), and you can readily merge data from diverse contexts without much complication. Because storage is separated from the computing, the Amazon Redshift RA3 instance retains the classic data lake feature of storing huge amounts of data. On the data side, to keep the data close to its raw form, a Data Vault model is the ideal solution to this problem.

Data Vault is a methodology to speed up the development of data warehouse initiatives and keep the data close to its sources. The Data Vault 2.0 data modeling standards are popular because they stress the business keys by hashing the keys across entities. This eliminates the need for strong relationships between entities and their linkages within the delivery of business processes. Data Vault makes it easy to integrate with versatile data sources for completely different use cases than it’s originally designed for, due to the following features:

• Entities can stand alone and be built on patterns, each with a well-defined purpose.
• Because the data represents the source system and delivers business value, data silos are eliminated.
• You can load data in parallel with the least number of dependencies.
• Historized data is kept at the coarsest level of granularity possible.
• You can implement flexible business rules independently of data loading. This reduces the time taken to load the data by 25%.
• You can add new data sources without affecting the current model.

The following diagram shows a simple clinical quality data repository with the Data Vault model with Amazon Redshift.

The following shows a simple clinical quality business Data Vault model using Amazon Redshift materialized views.

The Data Vault architecture is divided into four stages:

• Staging – A copy of the most recent modifications to data from the source systems is created. This layer doesn’t save history, and you can perform numerous alterations to the staged data while populating, such as data type changes or scaling, character set conversion, and the inclusion of metadata columns to enable subsequent processing.
• Raw vault – This stores a history of all data from numerous source systems. Except for putting the data into source system independent targets, no filters or business transformations happen here.
• Business vault – This is an optional offering that is frequently developed. It includes business computations and denormalizations with the goal of enhancing the speed and ease of access inside the consumption layer, known as the information mart layer.
• Data mart layer – This is where data is most typically accessible by users, such as reporting dashboards or extracts. You can build multiple marts from a single Data Vault integration layer, and the most frequent data modeling choice for these marts is Star/Kimball schemas.

The Amazon Redshift RA3 instance capabilities we discussed enable the development of highly performant and cost-effective Data Vault solutions. The staging and raw layers, for example, are populated in micro-batches by one Amazon Redshift cluster 24 hours a day. You can build the business Data Vault layer once a day and pause it to save costs when completed, and any number of consumer Amazon Redshift clusters can access the results.

The following diagram shows the complete architecture of the clinical quality Data Vault.

The architecture has the following components:

• Batched raw data is loaded into the staging database raw schema.
• The data quality validated structured data is delivered immediately to the staging database curated schema.
• Data from devices, EHR systems, and vendor medical systems are also sent directly into the Amazon Redshift staging database.
• The Data Vault 2.0 approach is used in the Amazon Redshift producer cluster to standardize data structure and assure data integrity.
• Materialized views make a business Data Vault.
• The Amazon Redshift SUPER data type allows you to store and query semi-structured data.
• Amazon Redshift data share capabilities securely exchange the Data Vault tables and views between departments.
• Support for streaming ingestion in Amazon Redshift eliminates the need to stage data in Amazon S3 before ingesting it into Amazon Redshift.
• Amazon Redshift allows you to offload the historical data to Amazon S3. Healthcare clinical quality tracking systems usually offload data older than 6–7 years old for archival.
• Amazon S3 and Amazon Redshift are HIPAA eligible.

Advantages of the Amazon Redshift clinical data repository

A clinical data repository in Amazon Redshift has the following benefits:

• Data Vault enables the preservation of data as raw in its native form.
• Amazon Redshift RA3 provides limitless storage, a real-time data feed, structured and semi-structured data, and more.
• The new streaming functionality and analytics capacity in Amazon Redshift provide near-real-time analysis without the need for extra services.
• Data quality is assured while maintaining data close to the business and source. You can reduce storage costs while maintaining data fidelity.
• It’s compatible with business applications and allows for easy adoption when merging or expanding a variety of businesses without complexity.
• The decoupled storage and compute option in Amazon Redshift allows for cost savings and offers ML capabilities within Amazon Redshift for quicker prediction, forecasting, and anomaly detection.

Conclusion

Data Vault is a one-of-a-kind database platform that promotes openness. It’s quick to adopt, infinitely configurable, and ready to overcome the most demanding data difficulties in current clinical quality metric tracking systems. Combined with the advantages of Amazon Redshift RA3 instances, Data Vault can outperform and deliver more value for cost and time.

To get started,

• Create Amazon Redshift RA3 instance for the primary “Clinical Data Repository” and the data marts.
• Build a Data vault schema for the raw vault an create materialized views for the business vault.
• Enable Amazon Redshift data share to share data between producer cluster and consumer cluster.
• Load the structed and unstructured data in to the producer cluster data vault for the business use.

About the Authors

Sarathi Balakrishnan is the Global Partner Solutions Architect, specializing in Data, Analytics and AI/ML at AWS. He works closely with AWS partner globally to build solutions and platforms on AWS to accelerate customers’ business outcomes with state-of-the-art cloud technologies and achieve more in their cloud explorations. He helps with solution architecture, technical guidance, and best practices to build cloud-native solutions. He joined AWS with over 20 years of large enterprise experience in agriculture, insurance, health care and life science, marketing and advertisement industries to develop and implement data and AI strategies.

Kasi Muthu is a Senior Partner Solutions Architect based in Houston, TX. He is a trusted advisor in Data, Analytics, AI/ML space. He also helps customers migrate their workloads to AWS with focus on scalability, resiliency, performance and sustainability. Outside of work, he enjoys spending time with his family and spending quite a bit of his free time on YouTube.