Amazon Redshift - Database Developer Guide

1 Amazon RedshiftDatabase Developer GuideAPI Version 2012-12-01 Amazon Redshift Database Developer GuideAmazon Redshift : Database Developer GuideCopyright 2018 Amazon Web Services, Inc. and/or its affiliates. All rights 's trademarks and trade dress may not be used in connection with any product or service that is not Amazon 's, in any mannerthat is likely to cause confusion among customers, or in any manner that disparages or discredits Amazon . All other trademarks notowned by Amazon are the property of their respective owners, who may or may not be affiliated with, connected to, or sponsored Redshift Database Developer GuideTable of ContentsWelcome .. 1 Are You a First-Time Amazon Redshift User? .. 1 Are You a Database Developer ? .. 2 Prerequisites .. 3 Amazon Redshift System Overview .. 4 Data Warehouse System Architecture.

2 4 Performance .. 6 Massively Parallel Processing .. 6 Columnar Data 7 Data Compression .. 7 Query Optimizer .. 7 Result Caching .. 7 Compiled 8 Columnar 8 Internal Architecture and System Operation .. 10 Workload Management .. 11 Using Amazon Redshift with Other Services .. 11 Moving Data Between Amazon Redshift and Amazon S3 .. 11 Using Amazon Redshift with Amazon DynamoDB .. 11 Importing Data from Remote Hosts over SSH .. 11 Automating Data Loads Using AWS Data Pipeline .. 12 Migrating Data Using AWS Database Migration Service (AWS DMS) .. 12 Getting Started Using Databases .. 13 Step 1: Create a Database .. 13 Step 2: Create a Database User .. 14 Delete a Database 14 Step 3: Create a Database Table .. 14 Insert Data Rows into a Table.

3 15 Select Data from a Table .. 15 Step 4: Load Sample 15 Step 5: Query the System Tables .. 16 View a List of Table Names .. 16 View Database 17 View Recent Queries .. 17 Determine the Process ID of a Running Query .. 18 Step 6: Cancel a Query .. 18 Cancel a Query from Another Session .. 19 Cancel a Query Using the Superuser Queue .. 19 Step 7: Clean Up Your Resources .. 20 Proof of Concept Playbook .. 21 Identifying the Goals of the Proof of Concept .. 21 Setting Up Your Proof of Concept .. 21 Designing and Setting Up Your Cluster .. 22 Converting Your Schema and Setting Up the Datasets .. 22 Cluster Design 22 Amazon Redshift Evaluation Checklist .. 23 Benchmarking Your Amazon Redshift Evaluation .. 24 Additional Resources .. 25 Amazon Redshift Best Practices.

4 26 Best Practices for Designing Tables .. 26 Take the Tuning Table Design Tutorial .. 27 Choose the Best Sort Key .. 27 Choose the Best Distribution Style .. 27 Use Automatic Compression .. 28 API Version 2012-12-01iiiAmazon Redshift Database Developer GuideDefine 28 Use the Smallest Possible Column Size .. 28 Using Date/Time Data Types for Date Columns .. 29 Best Practices for Loading Data .. 29 Take the Loading Data Tutorial .. 29 Take the Tuning Table Design Tutorial .. 29 Use a COPY Command to Load Data .. 30 Use a Single COPY Command .. 30 Split Your Load Data into Multiple Files .. 30 Compress Your Data Files .. 30 Use a Manifest 30 Verify Data Files Before and After a Load .. 30 Use a Multi-Row Insert .. 31 Use a Bulk Insert .. 31 Load Data in Sort Key Order.

5 31 Load Data in Sequential 31 Use Time-Series Tables .. 32 Use a Staging Table to Perform a Merge .. 32 Schedule Around Maintenance Windows .. 32 Best Practices for Designing Queries .. 32 Working with Advisor .. 34 Access Advisor .. 35 Recommendations .. 36 Tutorial: Tuning Table Design .. 45 Prerequisites .. 45 Step 1: Create a Test Data Set .. 45To Create a Test Data Set .. 46 Next Step .. 49 Step 2: Establish a 49To Test System Performance to Establish a Baseline .. 50 Next Step .. 52 Step 3: Select Sort Keys .. 52To Select Sort Keys .. 53 Next Step .. 53 Step 4: Select Distribution Styles .. 53 Distribution Styles .. 54To Select Distribution Styles .. 54 Next Step .. 57 Step 5: Review Compression Encodings .. 57To Review Compression Encodings.

6 57 Next Step .. 59 Step 6: Recreate the Test Data Set .. 59To Recreate the Test Data Set .. 60 Next Step .. 62 Step 7: Retest System Performance After Tuning .. 62To Retest System Performance After Tuning .. 62 Next Step .. 66 Step 8: Evaluate the Results .. 66 Next Step .. 68 Step 9: Clean Up Your Resources .. 68 Next Step .. 68 Summary .. 68 Next Step .. 69 Tutorial: Loading Data from Amazon S3 .. 70 Prerequisites .. 70 Overview .. 70 API Version 2012-12-01ivAmazon Redshift Database Developer 71 Step 1: Launch a 71 Next Step .. 72 Step 2: Download the Data Files .. 72 Next Step .. 72 Step 3: Upload the Files to an Amazon S3 Bucket .. 73 Next Step .. 73 Step 4: Create the Sample Tables .. 74 Next Step .. 76 Step 5: Run the COPY Commands.

7 76 COPY Command Syntax .. 76 Loading the SSB Tables .. 77 Step 6: Vacuum and Analyze the Database .. 87 Next Step .. 88 Step 7: Clean Up Your Resources .. 88 Next .. 88 Summary .. 88 Next Step .. 89 Tutorial: Configuring WLM Queues to Improve Query Processing .. 90 Overview .. 90 Prerequisites .. 90 Sections .. 90 Section 1: Understanding the Default Queue Processing Behavior .. 90 Step 1: Create the WLM_QUEUE_STATE_VW View .. 91 Step 2: Create the WLM_QUERY_STATE_VW View .. 92 Step 3: Run Test Queries .. 93 Section 2: Modifying the WLM Query Queue Configuration .. 94 Step 1: Create a Parameter Group .. 94 Step 2: Configure 95 Step 3: Associate the Parameter Group with Your Cluster .. 96 Section 3: Routing Queries to Queues Based on User Groups and Query Groups.

8 98 Step 1: View Query Queue Configuration in the Database .. 98 Step 2: Run a Query Using the Query Group Queue .. 99 Step 3: Create a Database User and Group .. 100 Step 4: Run a Query Using the User Group Queue .. 100 Section 4: Using wlm_query_slot_count to Temporarily Override Concurrency Level in a Queue .. 101 Step 1: Override the Concurrency Level Using wlm_query_slot_count .. 102 Step 2: Run Queries from Different Sessions .. 103 Section 5: Cleaning Up Your Resources .. 103 Tutorial: Querying Nested Data with Amazon Redshift 104 Overview .. 104 Prerequisites .. 104 Step 1: Create an External Table That Contains Nested Data .. 105 Step 2: Query Your Nested Data in Amazon S3 with SQL Extensions .. 105 Extension 1: Access to Columns of Structs .. 105 Extension 2: Ranging Over Arrays in a FROM Clause.

9 106 Extension 3: Accessing an Array of Scalars Directly Using an Alias .. 108 Extension 4: Accessing Elements of Maps .. 108 Nested Data Use 109 Ingesting Nested 109 Aggregating Nested Data with Subqueries .. 109 Joining Amazon Redshift and Nested Data .. 110 Nested Data 111 Managing Database Security .. 112 Amazon Redshift Security Overview .. 112 Default Database User 113 API Version 2012-12-01vAmazon Redshift Database Developer 114 Creating, Altering, and Deleting Users .. 114 Groups .. 114 Creating, Altering, and Deleting Groups .. 115 Creating, Altering, and Deleting Schemas .. 115 Search Path .. 116 Schema-Based 116 Example for Controlling User and Group Access .. 116 Designing Tables .. 118 Choosing a Column Compression Type .. 118 Compression Encodings.

10 119 Testing Compression Encodings .. 125 Example: Choosing Compression Encodings for the CUSTOMER Table .. 127 Choosing a Data Distribution Style .. 129 Data Distribution Concepts .. 129 Distribution Styles .. 130 Viewing Distribution Styles .. 131 Evaluating Query Patterns .. 131 Designating Distribution Styles .. 132 Evaluating the Query Plan .. 132 Query Plan Example .. 134 Distribution 138 Choosing Sort Keys .. 140 Compound Sort Key .. 140 Interleaved Sort Key .. 141 Comparing Sort Styles .. 142 Defining 145 Analyzing Table Design .. 145 Using Amazon Redshift Spectrum to Query External Data .. 148 Amazon Redshift Spectrum Overview .. 148 Amazon Redshift Spectrum Regions .. 149 Amazon Redshift Spectrum Considerations .. 149 Getting Started With Amazon Redshift 150 Prerequisites.