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.

2 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 .. 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.

3 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 .. 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.

4 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.

5 24 Additional Resources .. 25 Amazon Redshift Best Practices .. 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.

6 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 .. 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.

7 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.

8 57To Review Compression Encodings .. 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.

9 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 .. 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.

10 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 .. 98 Step 1: View Query Queue Configuration in the Database .