Amazon Redshift Database Developer Guide
User Manual:
Open the PDF directly: View PDF
Page Count: 1006 [warning: Documents this large are best viewed by clicking the View PDF Link!]
- Amazon Redshift
- Table of Contents
- Welcome
- Amazon Redshift System Overview
- Getting Started Using Databases
- Building a Proof of Concept for Amazon Redshift
- Amazon Redshift Best Practices
- Amazon Redshift Best Practices for Designing Tables
- Amazon Redshift Best Practices for Loading Data
- Take the Loading Data Tutorial
- Take the Tuning Table Design Tutorial
- Use a COPY Command to Load Data
- Use a Single COPY Command to Load from Multiple Files
- Split Your Load Data into Multiple Files
- Compress Your Data Files
- Use a Manifest File
- Verify Data Files Before and After a Load
- Use a Multi-Row Insert
- Use a Bulk Insert
- Load Data in Sort Key Order
- Load Data in Sequential Blocks
- Use Time-Series Tables
- Use a Staging Table to Perform a Merge (Upsert)
- Schedule Around Maintenance Windows
- Amazon Redshift Best Practices for Designing Queries
- Working with Recommendations from Amazon Redshift Advisor
- Viewing Amazon Redshift Advisor Recommendations in the Console
- Amazon Redshift Advisor Recommendations
- Compress Table Data
- Compress Amazon S3 File Objects Loaded by COPY
- Isolate Multiple Active Databases
- Reallocate Workload Management (WLM) Memory
- Skip Compression Analysis During COPY
- Split Amazon S3 Objects Loaded by COPY
- Update Table Statistics
- Enable Short Query Acceleration
- Replace Single-Column Interleaved Sort Keys
- Tutorial: Tuning Table Design
- Prerequisites
- Steps
- Step 1: Create a Test Data Set
- Step 2: Test System Performance to Establish a Baseline
- Step 3: Select Sort Keys
- Step 4: Select Distribution Styles
- Step 5: Review Compression Encodings
- Step 6: Recreate the Test Data Set
- Step 7: Retest System Performance After Tuning
- Step 8: Evaluate the Results
- Step 9: Clean Up Your Resources
- Summary
- Tutorial: Loading Data from Amazon S3
- Tutorial: Configuring Workload Management (WLM) Queues to Improve Query Processing
- Overview
- Section 1: Understanding the Default Queue Processing Behavior
- Section 2: Modifying the WLM Query Queue Configuration
- Section 3: Routing Queries to Queues Based on User Groups and Query Groups
- Section 4: Using wlm_query_slot_count to Temporarily Override Concurrency Level in a Queue
- Section 5: Cleaning Up Your Resources
- Tutorial: Querying Nested Data with Amazon Redshift Spectrum
- Managing Database Security
- Designing Tables
- Using Amazon Redshift Spectrum to Query External Data
- Amazon Redshift Spectrum Overview
- Getting Started with Amazon Redshift Spectrum
- IAM Policies for Amazon Redshift Spectrum
- Creating Data Files for Queries in Amazon Redshift Spectrum
- Creating External Schemas for Amazon Redshift Spectrum
- Creating External Tables for Amazon Redshift Spectrum
- Improving Amazon Redshift Spectrum Query Performance
- Monitoring Metrics in Amazon Redshift Spectrum
- Troubleshooting Queries in Amazon Redshift Spectrum
- Loading Data
- Using a COPY Command to Load Data
- Credentials and Access Permissions
- Preparing Your Input Data
- Loading Data from Amazon S3
- Loading Data from Amazon EMR
- Loading Data From Amazon EMR Process
- Step 1: Configure IAM Permissions
- Step 2: Create an Amazon EMR Cluster
- Step 3: Retrieve the Amazon Redshift Cluster Public Key and Cluster Node IP Addresses
- Step 4: Add the Amazon Redshift Cluster Public Key to Each Amazon EC2 Host's Authorized Keys File
- Step 5: Configure the Hosts to Accept All of the Amazon Redshift Cluster's IP Addresses
- Step 6: Run the COPY Command to Load the Data
- Loading Data from Remote Hosts
- Before You Begin
- Loading Data Process
- Step 1: Retrieve the Cluster Public Key and Cluster Node IP Addresses
- Step 2: Add the Amazon Redshift Cluster Public Key to the Host's Authorized Keys File
- Step 3: Configure the Host to Accept All of the Amazon Redshift Cluster's IP Addresses
- Step 4: Get the Public Key for the Host
- Step 5: Create a Manifest File
- Step 6: Upload the Manifest File to an Amazon S3 Bucket
- Step 7: Run the COPY Command to Load the Data
- Loading Data from an Amazon DynamoDB Table
- Verifying That the Data Was Loaded Correctly
- Validating Input Data
- Loading Tables with Automatic Compression
- Optimizing Storage for Narrow Tables
- Loading Default Column Values
- Troubleshooting Data Loads
- Updating Tables with DML Commands
- Updating and Inserting New Data
- Performing a Deep Copy
- Analyzing Tables
- Vacuuming Tables
- Managing Concurrent Write Operations
- Using a COPY Command to Load Data
- Unloading Data
- Creating User-Defined Functions
- Tuning Query Performance
- Query Processing
- Analyzing and Improving Queries
- Troubleshooting Queries
- Implementing Workload Management
- SQL Reference
- Amazon Redshift SQL
- Using SQL
- SQL Reference Conventions
- Basic Elements
- Names and Identifiers
- Literals
- Nulls
- Data Types
- Multibyte Characters
- Numeric Types
- Character Types
- Datetime Types
- Boolean Type
- Type Compatibility and Conversion
- Collation Sequences
- Expressions
- Conditions
- SQL Commands
- ABORT
- ALTER DATABASE
- ALTER DEFAULT PRIVILEGES
- ALTER GROUP
- ALTER SCHEMA
- ALTER TABLE
- ALTER TABLE APPEND
- ALTER USER
- ANALYZE
- ANALYZE COMPRESSION
- BEGIN
- CANCEL
- CLOSE
- COMMENT
- COMMIT
- COPY
- COPY Syntax
- COPY Syntax Overview
- COPY Parameter Reference
- Usage Notes
- COPY Examples
- Load FAVORITEMOVIES from an DynamoDB Table
- Load LISTING from an Amazon S3 Bucket
- Load LISTING from an Amazon EMR Cluster
- Using a Manifest to Specify Data Files
- Load LISTING from a Pipe-Delimited File (Default Delimiter)
- Load LISTING Using Columnar Data in Parquet Format
- Load LISTING Using Temporary Credentials
- Load EVENT with Options
- Load VENUE from a Fixed-Width Data File
- Load CATEGORY from a CSV File
- Load VENUE with Explicit Values for an IDENTITY Column
- Load TIME from a Pipe-Delimited GZIP File
- Load a Timestamp or Datestamp
- Load Data from a File with Default Values
- COPY Data with the ESCAPE Option
- Copy from JSON Examples
- Copy from Avro Examples
- Preparing Files for COPY with the ESCAPE Option
- CREATE DATABASE
- CREATE EXTERNAL SCHEMA
- CREATE EXTERNAL TABLE
- CREATE FUNCTION
- CREATE GROUP
- CREATE LIBRARY
- CREATE SCHEMA
- CREATE TABLE
- Syntax
- Parameters
- Usage Notes
- Examples
- Create a Table with a Distribution Key, a Compound Sort Key, and Compression
- Create a Table Using an Interleaved Sort Key
- Create a Table Using IF NOT EXISTS
- Create a Table with ALL Distribution
- Create a Table with Default EVEN Distribution
- Create a Temporary Table That Is LIKE Another Table
- Create a Table with an IDENTITY Column
- Create a Table with DEFAULT Column Values
- DISTSTYLE, DISTKEY, and SORTKEY Options
- CREATE TABLE AS
- CREATE USER
- CREATE VIEW
- DEALLOCATE
- DECLARE
- DELETE
- DROP DATABASE
- DROP FUNCTION
- DROP GROUP
- DROP LIBRARY
- DROP SCHEMA
- DROP TABLE
- DROP USER
- DROP VIEW
- END
- EXECUTE
- EXPLAIN
- FETCH
- GRANT
- INSERT
- LOCK
- PREPARE
- RESET
- REVOKE
- ROLLBACK
- SELECT
- SELECT INTO
- SET
- SET SESSION AUTHORIZATION
- SET SESSION CHARACTERISTICS
- SHOW
- START TRANSACTION
- TRUNCATE
- UNLOAD
- Syntax
- Parameters
- Usage Notes
- UNLOAD Examples
- Unload VENUE to a Pipe-Delimited File (Default Delimiter)
- Unload VENUE with a Manifest File
- Unload VENUE with MANIFEST VERBOSE
- Unload VENUE with a Header
- Unload VENUE to Smaller Files
- Unload VENUE Serially
- Load VENUE from Unload Files
- Unload VENUE to Encrypted Files
- Load VENUE from Encrypted Files
- Unload VENUE Data to a Tab-Delimited File
- Unload VENUE Using Temporary Credentials
- Unload VENUE to a Fixed-Width Data File
- Unload VENUE to a Set of Tab-Delimited GZIP-Compressed Files
- Unload Data That Contains a Delimiter
- Unload the Results of a Join Query
- Unload Using NULL AS
- ALLOWOVERWRITE Example
- UPDATE
- VACUUM
- SQL Functions Reference
- Leader Node–Only Functions
- Compute Node–Only Functions
- Aggregate Functions
- Bit-Wise Aggregate Functions
- Window Functions
- Window Function Syntax Summary
- AVG Window Function
- COUNT Window Function
- CUME_DIST Window Function
- DENSE_RANK Window Function
- FIRST_VALUE and LAST_VALUE Window Functions
- LAG Window Function
- LEAD Window Function
- LISTAGG Window Function
- MAX Window Function
- MEDIAN Window Function
- MIN Window Function
- NTH_VALUE Window Function
- NTILE Window Function
- PERCENT_RANK Window Function
- PERCENTILE_CONT Window Function
- PERCENTILE_DISC Window Function
- RANK Window Function
- RATIO_TO_REPORT Window Function
- ROW_NUMBER Window Function
- STDDEV_SAMP and STDDEV_POP Window Functions
- SUM Window Function
- VAR_SAMP and VAR_POP Window Functions
- Window Function Examples
- AVG Window Function Examples
- COUNT Window Function Examples
- CUME_DIST Window Function Examples
- DENSE_RANK Window Function Examples
- FIRST_VALUE and LAST_VALUE Window Function Examples
- LAG Window Function Examples
- LEAD Window Function Examples
- LISTAGG Window Function Examples
- MAX Window Function Examples
- MEDIAN Window Function Examples
- MIN Window Function Examples
- NTH_VALUE Window Function Examples
- NTILE Window Function Examples
- PERCENT_RANK Window Function Examples
- PERCENTILE_CONT Window Function Examples
- PERCENTILE_DISC Window Function Examples
- RANK Window Function Examples
- RATIO_TO_REPORT Window Function Examples
- ROW_NUMBER Window Function Example
- STDDEV_POP and VAR_POP Window Function Examples
- SUM Window Function Examples
- Unique Ordering of Data for Window Functions
- Conditional Expressions
- Date and Time Functions
- Summary of Date and Time Functions
- Summary of Date and Time Functions
- Date and Time Functions in Transactions
- Deprecated Leader Node-Only Functions
- ADD_MONTHS Function
- AT TIME ZONE Function
- CONVERT_TIMEZONE Function
- CURRENT_DATE Function
- DATE_CMP Function
- DATE_CMP_TIMESTAMP Function
- DATE_CMP_TIMESTAMPTZ Function
- DATE_PART_YEAR Function
- DATEADD Function
- DATEDIFF Function
- DATE_PART Function
- DATE_TRUNC Function
- EXTRACT Function
- GETDATE Function
- INTERVAL_CMP Function
- LAST_DAY Function
- MONTHS_BETWEEN Function
- NEXT_DAY Function
- SYSDATE Function
- TIMEOFDAY Function
- TIMESTAMP_CMP Function
- TIMESTAMP_CMP_DATE Function
- TIMESTAMP_CMP_TIMESTAMPTZ Function
- TIMESTAMPTZ_CMP Function
- TIMESTAMPTZ_CMP_DATE Function
- TIMESTAMPTZ_CMP_TIMESTAMP Function
- TIMEZONE Function
- TO_TIMESTAMP Function
- TRUNC Date Function
- Dateparts for Date or Time Stamp Functions
- Math Functions
- Mathematical Operator Symbols
- ABS Function
- ACOS Function
- ASIN Function
- ATAN Function
- ATAN2 Function
- CBRT Function
- CEILING (or CEIL) Function
- CHECKSUM Function
- COS Function
- COT Function
- DEGREES Function
- DEXP Function
- DLOG1 Function
- DLOG10 Function
- EXP Function
- FLOOR Function
- LN Function
- LOG Function
- MOD Function
- PI Function
- POWER Function
- RADIANS Function
- RANDOM Function
- ROUND Function
- SIN Function
- SIGN Function
- SQRT Function
- TAN Function
- TO_HEX Function
- TRUNC Function
- String Functions
- || (Concatenation) Operator
- BPCHARCMP Function
- BTRIM Function
- BTTEXT_PATTERN_CMP Function
- CHAR_LENGTH Function
- CHARACTER_LENGTH Function
- CHARINDEX Function
- CHR Function
- CONCAT (Oracle Compatibility Function)
- CRC32 Function
- FUNC_SHA1 Function
- INITCAP Function
- LEFT and RIGHT Functions
- LEN Function
- LENGTH Function
- LOWER Function
- LPAD and RPAD Functions
- LTRIM Function
- MD5 Function
- OCTET_LENGTH Function
- POSITION Function
- QUOTE_IDENT Function
- QUOTE_LITERAL Function
- REGEXP_COUNT Function
- REGEXP_INSTR Function
- REGEXP_REPLACE Function
- REGEXP_SUBSTR Function
- REPEAT Function
- REPLACE Function
- REPLICATE Function
- REVERSE Function
- RTRIM Function
- SPLIT_PART Function
- STRPOS Function
- STRTOL Function
- SUBSTRING Function
- TEXTLEN Function
- TRANSLATE Function
- TRIM Function
- UPPER Function
- JSON Functions
- Data Type Formatting Functions
- System Administration Functions
- System Information Functions
- CURRENT_DATABASE
- CURRENT_SCHEMA
- CURRENT_SCHEMAS
- CURRENT_USER
- CURRENT_USER_ID
- HAS_DATABASE_PRIVILEGE
- HAS_SCHEMA_PRIVILEGE
- HAS_TABLE_PRIVILEGE
- PG_BACKEND_PID
- PG_GET_COLS
- PG_GET_LATE_BINDING_VIEW_COLS
- PG_LAST_COPY_COUNT
- PG_LAST_COPY_ID
- PG_LAST_UNLOAD_ID
- PG_LAST_QUERY_ID
- PG_LAST_UNLOAD_COUNT
- SESSION_USER
- SLICE_NUM Function
- USER
- VERSION
- Reserved Words
- System Tables Reference
- System Tables and Views
- Types of System Tables and Views
- Visibility of Data in System Tables and Views
- STL Tables for Logging
- STL_AGGR
- STL_ALERT_EVENT_LOG
- STL_ANALYZE
- STL_BCAST
- STL_COMMIT_STATS
- STL_CONNECTION_LOG
- STL_DDLTEXT
- STL_DELETE
- STL_DISK_FULL_DIAG
- STL_DIST
- STL_ERROR
- STL_EXPLAIN
- STL_FILE_SCAN
- STL_HASH
- STL_HASHJOIN
- STL_INSERT
- STL_LIMIT
- STL_LOAD_COMMITS
- STL_LOAD_ERRORS
- STL_LOADERROR_DETAIL
- STL_MERGE
- STL_MERGEJOIN
- STL_NESTLOOP
- STL_PARSE
- STL_PLAN_INFO
- STL_PROJECT
- STL_QUERY
- STL_QUERY_METRICS
- STL_QUERYTEXT
- STL_REPLACEMENTS
- STL_RESTARTED_SESSIONS
- STL_RETURN
- STL_S3CLIENT
- STL_S3CLIENT_ERROR
- STL_SAVE
- STL_SCAN
- STL_SESSIONS
- STL_SORT
- STL_SSHCLIENT_ERROR
- STL_STREAM_SEGS
- STL_TR_CONFLICT
- STL_UNDONE
- STL_UNIQUE
- STL_UNLOAD_LOG
- STL_USERLOG
- STL_UTILITYTEXT
- STL_VACUUM
- STL_WINDOW
- STL_WLM_ERROR
- STL_WLM_RULE_ACTION
- STL_WLM_QUERY
- STV Tables for Snapshot Data
- STV_ACTIVE_CURSORS
- STV_BLOCKLIST
- STV_CURSOR_CONFIGURATION
- STV_EXEC_STATE
- STV_INFLIGHT
- STV_LOAD_STATE
- STV_LOCKS
- STV_PARTITIONS
- STV_QUERY_METRICS
- STV_RECENTS
- STV_SESSIONS
- STV_SLICES
- STV_STARTUP_RECOVERY_STATE
- STV_TBL_PERM
- STV_TBL_TRANS
- STV_WLM_QMR_CONFIG
- STV_WLM_CLASSIFICATION_CONFIG
- STV_WLM_QUERY_QUEUE_STATE
- STV_WLM_QUERY_STATE
- STV_WLM_QUERY_TASK_STATE
- STV_WLM_SERVICE_CLASS_CONFIG
- STV_WLM_SERVICE_CLASS_STATE
- System Views
- SVV_COLUMNS
- SVL_COMPILE
- SVV_DISKUSAGE
- SVV_EXTERNAL_COLUMNS
- SVV_EXTERNAL_DATABASES
- SVV_EXTERNAL_PARTITIONS
- SVV_EXTERNAL_SCHEMAS
- SVV_EXTERNAL_TABLES
- SVV_INTERLEAVED_COLUMNS
- SVL_QERROR
- SVL_QLOG
- SVV_QUERY_INFLIGHT
- SVL_QUERY_QUEUE_INFO
- SVL_QUERY_METRICS
- SVL_QUERY_METRICS_SUMMARY
- SVL_QUERY_REPORT
- SVV_QUERY_STATE
- SVL_QUERY_SUMMARY
- SVL_S3LOG
- SVL_S3PARTITION
- SVL_S3QUERY
- SVL_S3QUERY_SUMMARY
- SVL_S3RETRIES
- SVL_STATEMENTTEXT
- SVV_TABLES
- SVV_TABLE_INFO
- SVV_TRANSACTIONS
- SVL_USER_INFO
- SVL_UDF_LOG
- SVV_VACUUM_PROGRESS
- SVV_VACUUM_SUMMARY
- SVL_VACUUM_PERCENTAGE
- System Catalog Tables
- Configuration Reference
- Sample Database
- Appendix: Time Zone Names and Abbreviations
- Document History