book

Amazon Redshift: The Definitive Guide

by Rajesh Francis, Rajiv Gupta, Milind Oke

October 2023

Intermediate to advanced

464 pages

12h 49m

English

O'Reilly Media, Inc.

Book available

Read now

Unlock full access

Conventions Used in This BookUsing Code ExamplesO’Reilly Online LearningHow to Contact UsAcknowledgments
Data-Driven OrganizationsBusiness Use CasesNew Business Use Cases with Generative AIModern Data StrategyComprehensive Set of CapabilitiesIntegrated Set of ToolsEnd-to-End Data GovernanceModern Data ArchitectureRole of Amazon Redshift in a Modern Data ArchitectureReal-World Benefits of Adopting a Modern Data ArchitectureReference Architecture for Modern Data ArchitectureData SourcingExtract, Transform, and LoadStorageAnalysisData Mesh and Data FabricData MeshData FabricSummary
Amazon Redshift Architecture OverviewGet Started with Amazon Redshift ServerlessCreating an Amazon Redshift Serverless Data WarehouseSample DataActivate Sample Data Models and Query Using the Query EditorWhen to Use a Provisioned Cluster?Creating an Amazon Redshift Provisioned ClusterEstimate Your Amazon Redshift CostAmazon Redshift Managed StorageAmazon Redshift Serverless Compute CostAmazon Redshift Provisioned Compute CostAWS Account ManagementConnecting to Your Amazon Redshift Data WarehousePrivate/Public VPC and Secure AccessStored PasswordTemporary CredentialsFederated UserSAML-Based Authentication from an Identity ProviderNative IdP IntegrationAmazon Redshift Data APIQuerying a Database Using the Query Editor V2Business Intelligence Using Amazon QuickSightConnecting to Amazon Redshift Using JDBC/ODBCSummary
Data Lake First Versus Data Warehouse First StrategyData Lake First StrategyData Warehouse First StrategyDeciding On a StrategyDefining Your Data ModelDatabase Schemas, Users, and GroupsStar Schema, Denormalized, NormalizedStudent Information Learning Transactional DatasetCreate Data Models for Student Information Learning Transactional DatasetIngest Data for the Student Learning Transaction DatasetStudent Information Learning Analytics DatasetCreate Data Models for Student Information Learning Analytics DatasetLoad Batch Data into Amazon RedshiftUsing the COPY CommandIngest Data for the Student Learning Analytics DatasetBuilding a Star SchemaContinuous File Ingestion from Amazon S3Using AWS Glue for TransformationsManual Loading Using SQL CommandsUsing the Query Editor V2Load Real-Time and Near Real-Time DataNear Real-Time Replication Using AWS Database Migration ServiceAmazon Aurora Zero-ETL Integration with Amazon RedshiftUsing Amazon AppFlowStreaming IngestionOptimize Your Data StructuresAutomatic Table Optimization and AutonomicsDistribution StyleSort KeyCompression EncodingSummary
Comparing ELT and ETL StrategiesIn-Database TransformationSemistructured DataUser-Defined FunctionsStored ProceduresScheduling and OrchestrationAccess All Your DataExternal Amazon S3 DataExternal Operational DataExternal Amazon Redshift DataExternal TransformationAWS GlueSummary
Scale StorageAutoscale Your Serverless Data WarehouseScale Your Provisioned Data WarehouseEvolving Compute DemandUnpredictable Workload ChangesWLM, Queues, and QMRQueue AssignmentShort Query AccelerationQuery Monitoring RulesAutomatic WLMManual WLMParameter GroupWLM Dynamic Memory AllocationMaterialized ViewsAutonomicsAuto Table Optimizer and Smart DefaultsAuto VacuumAuto Vacuum SortAuto AnalyzeAuto Materialized Views (AutoMV)Amazon Redshift AdvisorWorkload IsolationAdditional Optimizations for Achieving the Best Price and PerformanceDatabase Versus Data WarehouseAmazon Redshift ServerlessMulti-Warehouse EnvironmentAWS Data ExchangeTable DesignIndexes Versus Zone MapsDriversSimplify ETLQuery Editor V2Query TuningQuery ProcessingAnalyzing QueriesIdentifying Queries for Performance TuningSummary
Machine Learning CycleAmazon Redshift MLAmazon Redshift ML FlexibilityGetting Started with Amazon Redshift MLMachine Learning TechniquesSupervised Learning TechniquesUnsupervised Learning TechniquesMachine Learning AlgorithmsIntegration with Amazon SageMaker AutopilotCreate ModelLabel ProbabilityExplain ModelUsing Amazon Redshift ML to Predict Student OutcomesAmazon SageMaker Integration with Amazon RedshiftIntegration with Amazon SageMaker—Bring Your Own Model (BYOM)BYOM LocalBYOM RemoteAmazon Redshift ML CostsSummary
Amazon Redshift Data Sharing OverviewData Sharing Use CasesKey Concepts of Data SharingHow to Use Data SharingSharing Data Within the Same AccountSharing Data Across Accounts Using Cross-Account Data SharingAnalytics as a Service Use Case with Multi-Tenant Storage PatternsScaling Your Multi-tenant Architecture Using Data SharingMulti-tenant Storage Patterns Using Data SharingExternal Data Sharing with AWS ADX IntegrationPublishing a Data ProductSubscribing to a Published Data ProductConsiderations When Using AWS Data Exchange for Amazon RedshiftQuery from the Data Lake and Unload to the Data LakeAmazon DataZone to Discover and Share DataUse Cases for a Data Mesh Architecture with Amazon DataZoneKey Capabilities and Use Cases for Amazon DataZoneAmazon DataZone Integrations with Amazon Redshift and Other AWS ServicesComponents and Capabilities of Amazon DataZoneGetting Started with Amazon DataZoneSecurity in Amazon DataZoneSummary
Object-Level Access ControlsObject OwnershipDefault PrivilegesPublic Schema and Search PathAccess Controls in ActionDatabase RolesDatabase Roles in ActionRow-Level SecurityRow-Level Security in ActionRow-Level Security ConsiderationsDynamic Data MaskingDynamic Data Masking in ActionDynamic Data Masking ConsiderationsExternal Data Access ControlAssociate IAM RolesAuthorize Assume Role PrivilegesEstablish External SchemasLake Formation for Fine-Grained Access ControlSummary

Migration ConsiderationsRetire Versus RetainMigration Data SizePlatform-Specific Transformations RequiredData Volatility and Availability RequirementsSelection of Migration and ETL ToolsData Movement ConsiderationsDomain Name System (DNS)Migration StrategiesOne-Step MigrationTwo-Step MigrationIterative MigrationMigration Tools and ServicesAWS Schema Conversion ToolData Warehouse Migration ServiceAWS Snow FamilyAWS Snowball Edge ClientDatabase Migration ProcessStep 1: Convert Schema and Subject AreaStep 2: Initial Data Extraction and LoadStep 3: Incremental Load Through Data CaptureAmazon Redshift Migration Tools ConsiderationsAccelerate Your Migration to Amazon RedshiftMacro ConversionCase-Insensitive String ComparisonRecursive Common Table ExpressionsProprietary Data TypesSummary
Amazon Redshift Monitoring OverviewMonitoringTroubleshootingOptimizationMonitoring Using ConsoleMonitoring and Administering ServerlessMonitoring Provisioned Data Warehouse Using ConsoleMonitoring Queries and Loads Across ClustersIdentifying Systemic Query Performance ProblemsMonitoring Using Amazon CloudWatchAmazon Redshift CloudWatch MetricsMonitoring Using System Tables and ViewsMonitoring Serverless Using System ViewsHigh Availability and Disaster RecoveryRecovery Time Objective and Recovery Point Objective ConsiderationsMulti-AZ Compared to Single-AZ DeploymentCreating or Converting a Provisioned Data Warehouse with Multi-AZ ConfigurationAuto Recovery of Multi-AZ DeploymentSnapshots, Backup, and RestoreSnapshots for BackupAutomated SnapshotsManual SnapshotsDisaster Recovery Using Cross-Region SnapshotsUsing Snapshots for Simple-ReplayMonitoring Amazon Redshift Using CloudTrailBring Your Own Visualization Tool to Monitor Amazon RedshiftMonitor Operational Metrics Using System Tables and Amazon QuickSightMonitor Operational Metrics Using Grafana Plug-in for Amazon RedshiftSummary

Content preview from Amazon Redshift: The Definitive Guide

About the Authors

Rajesh Francis is a principal analytics specialist at AWS and works with strategic customers to help drive data strategy and deliver business outcomes. He has worked in various capacities over the last 25 years as a consultant, solutions architect, business development executive, and a leader in the data and analytics space with global organizations. He specializes in Amazon Redshift and is passionate about building scalable data architectures and working closely with customers and product teams to guide the direction of AWS Analytics Services. In prior roles, Rajesh led implementation of SAP analytics solutions for Fortune 500 corporations.

Rajiv Gupta is a manager of Amazon Redshift Specialist Solutions Architects who help customers design and build their data warehouse platforms. He is an AWS-certified SA Associate and an Analytics Specialty certification holder, based out of Lake Forest, California. Rajiv has been working in the data and analytics space for 20-plus years as a consultant, not only helping customers successfully implement their data warehouse implementations but also writing technical articles that educate his customers and colleagues and has managed teams of architects with the goal of producing positive customer outcomes.

Milind Oke is a senior Redshift specialist solutions architect who has worked at Amazon Web Services for three years. He is an AWS-certified SA Associate, Security Specialty and Analytics Specialty certification holder, ...