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

Database Developer Guide

API Version 2012-12-01

Amazon Redshift Database Developer Guide

Amazon Redshift: Database Developer Guide

Copyright © 2018 Amazon Web Services, Inc. and/or its affiliates. All rights reserved.

Amazon's trademarks and trade dress may not be used in connection with any product or service that is not Amazon's, in any manner

that is likely to cause confusion among customers, or in any manner that disparages or discredits Amazon. All other trademarks not

owned by Amazon are the property of their respective owners, who may or may not be affiliated with, connected to, or sponsored by

Amazon.

Amazon Redshift Database Developer Guide

Table of Contents

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

Data Compression ............................................................................................................. 7

Query Optimizer ................................................................................................................ 7

Result Caching .................................................................................................................. 7

Compiled Code .................................................................................................................. 8

Columnar Storage ...................................................................................................................... 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 User ..................................................................................................... 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 Data ......................................................................................................... 15

Step 5: Query the System Tables ............................................................................................... 16

View a List of Table Names ............................................................................................... 16

View Database Users ........................................................................................................ 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 Considerations .................................................................................................... 22

Amazon Redshift Evaluation Checklist ......................................................................................... 23

Benchmarking Your Amazon Redshift Evaluation .......................................................................... 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-01

iii

Amazon Redshift Database Developer Guide

Define Constraints ............................................................................................................ 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 File ........................................................................................................... 30

Verify Data Files Before and After a Load ............................................................................ 31

Use a Multi-Row Insert ..................................................................................................... 31

Use a Bulk Insert .............................................................................................................. 31

Load Data in Sort Key Order .............................................................................................. 31

Load Data in Sequential Blocks .......................................................................................... 32

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

Advisor Recommendations ................................................................................................. 35

Tutorial: Tuning Table Design ............................................................................................................. 45

Prerequisites ............................................................................................................................ 45

Steps ...................................................................................................................................... 45

Step 1: Create a Test Data Set ................................................................................................... 45

To Create a Test Data Set .................................................................................................. 46

Next Step ........................................................................................................................ 49

Step 2: Establish a Baseline ....................................................................................................... 49

To Test System Performance to Establish a Baseline ............................................................. 50

Next Step ........................................................................................................................ 52

Step 3: Select Sort Keys ............................................................................................................ 52

To Select Sort Keys .......................................................................................................... 53

Next Step ........................................................................................................................ 53

Step 4: Select Distribution Styles ............................................................................................... 53

Distribution Styles ............................................................................................................ 54

To Select Distribution Styles .............................................................................................. 54

Next Step ........................................................................................................................ 57

Step 5: Review Compression Encodings ....................................................................................... 57

To Review Compression Encodings ..................................................................................... 57

Next Step ........................................................................................................................ 59

Step 6: Recreate the Test Data Set ............................................................................................. 59

To Recreate the Test Data Set ............................................................................................ 60

Next Step ........................................................................................................................ 62

Step 7: Retest System Performance After Tuning ......................................................................... 62

To 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-01

iv

Amazon Redshift Database Developer Guide

Steps ...................................................................................................................................... 71

Step 1: Launch a Cluster ........................................................................................................... 71

Next Step ........................................................................................................................ 72

Step 2: Download the Data Files ................................................................................................ 72

Next Step ........................................................................................................................ 72

Step 3: Upload the Files to an Amazon S3 Bucket ........................................................................ 72

...................................................................................................................................... 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 .................................................................................................................... 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 WLM ..................................................................................................... 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 ...................................................... 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 Spectrum .......................................................... 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 ............................................................ 106

Extension 3: Accessing an Array of Scalars Directly Using an Alias ......................................... 108

Extension 4: Accessing Elements of Maps .......................................................................... 108

Nested Data Use Cases ............................................................................................................ 109

Ingesting Nested Data ..................................................................................................... 109

Aggregating Nested Data with Subqueries ........................................................................ 109

Joining Amazon Redshift and Nested Data ........................................................................ 110

Nested Data Limitations .......................................................................................................... 111

Managing Database Security ............................................................................................................ 112

Amazon Redshift Security Overview .......................................................................................... 112

Default Database User Privileges .............................................................................................. 113

API Version 2012-12-01

v

Amazon Redshift Database Developer Guide

Superusers ............................................................................................................................. 113

Users ..................................................................................................................................... 114

Creating, Altering, and Deleting Users ............................................................................... 114

Groups .................................................................................................................................. 114

Creating, Altering, and Deleting Groups ............................................................................. 115

Schemas ................................................................................................................................ 115

Creating, Altering, and Deleting Schemas .......................................................................... 115

Search Path ................................................................................................................... 116

Schema-Based Privileges ................................................................................................. 116

Example for Controlling User and Group Access ......................................................................... 116

Designing Tables ............................................................................................................................ 118

Choosing a Column Compression Type ...................................................................................... 118

Compression Encodings ................................................................................................... 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 ............................................................................................... 132

Designating Distribution Styles ......................................................................................... 132

Evaluating the Query Plan ............................................................................................... 133

Query Plan Example ....................................................................................................... 134

Distribution Examples ..................................................................................................... 138

Choosing Sort Keys ................................................................................................................. 140

Compound Sort Key ........................................................................................................ 141

Interleaved Sort Key ....................................................................................................... 141

Comparing Sort Styles .................................................................................................... 142

Defining Constraints ............................................................................................................... 145

Analyzing Table Design ........................................................................................................... 146

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

Prerequisites .................................................................................................................. 150

Steps ............................................................................................................................ 150

Step 1. Create an IAM Role .............................................................................................. 150

Step 2: Associate the IAM Role with Your Cluster ................................................................ 151

Step 3: Create an External Schema and an External Table .................................................... 152

Step 4: Query Your Data in Amazon S3 ............................................................................. 152

IAM Policies for Amazon Redshift Spectrum ............................................................................... 154

Amazon S3 Permissions ................................................................................................... 155

Cross-Account Amazon S3 Permissions .............................................................................. 156

Grant or Restrict Access Using Redshift Spectrum ............................................................... 156

Minimum Permissions ..................................................................................................... 157

Chaining IAM Roles ......................................................................................................... 158

Access AWS Glue Data .................................................................................................... 158

Creating Data Files for Queries in Amazon Redshift Spectrum ...................................................... 164

Creating External Schemas ...................................................................................................... 165

Working with External Catalogs ........................................................................................ 167

Creating External Tables .......................................................................................................... 171

Pseudocolumns .............................................................................................................. 172

Partitioning Redshift Spectrum External Tables .................................................................. 173

Mapping to ORC Columns ............................................................................................... 177

Improving Amazon Redshift Spectrum Query Performance .......................................................... 179

Monitoring Metrics .................................................................................................................. 181

API Version 2012-12-01

vi

Amazon Redshift Database Developer Guide

Troubleshooting Queries .......................................................................................................... 181

Retries Exceeded ............................................................................................................ 182

No Rows Returned for a Partitioned Table ......................................................................... 182

Not Authorized Error ....................................................................................................... 182

Incompatible Data Formats .............................................................................................. 182

Syntax Error When Using Hive DDL in Amazon Redshift ....................................................... 183

Permission to Create Temporary Tables ............................................................................. 183

Loading Data ................................................................................................................................. 184

Using COPY to Load Data ........................................................................................................ 184

Credentials and Access Permissions ................................................................................... 185

Preparing Your Input Data ............................................................................................... 186

Loading Data from Amazon S3 ........................................................................................ 187

Loading Data from Amazon EMR ...................................................................................... 196

Loading Data from Remote Hosts ..................................................................................... 200

Loading from Amazon DynamoDB .................................................................................... 206

Verifying That the Data Was Loaded Correctly ................................................................... 208

Validating Input Data ...................................................................................................... 208

Automatic Compression ................................................................................................... 209

Optimizing for Narrow Tables .......................................................................................... 211

Default Values ................................................................................................................ 211

Troubleshooting ............................................................................................................. 211

Updating with DML ................................................................................................................ 216

Updating and Inserting ........................................................................................................... 216

Merge Method 1: Replacing Existing Rows ......................................................................... 216

Merge Method 2: Specifying a Column List ........................................................................ 217

Creating a Temporary Staging Table ................................................................................. 217

Performing a Merge Operation by Replacing Existing Rows .................................................. 217

Performing a Merge Operation by Specifying a Column List ................................................. 218

Merge Examples ............................................................................................................. 219

Performing a Deep Copy ......................................................................................................... 221

Analyzing Tables .................................................................................................................... 223

Analyzing Tables ............................................................................................................ 223

Analysis of New Table Data ............................................................................................. 224

ANALYZE Command History ............................................................................................. 227

Vacuuming Tables ................................................................................................................... 228

VACUUM Frequency ........................................................................................................ 228

Sort Stage and Merge Stage ............................................................................................ 229

Vacuum Threshold .......................................................................................................... 229

Vacuum Types ................................................................................................................ 229

Managing Vacuum Times ................................................................................................. 230

Vacuum Column Limit Exceeded Error ............................................................................... 236

Managing Concurrent Write Operations ..................................................................................... 238

Serializable Isolation ....................................................................................................... 238

Write and Read-Write Operations ..................................................................................... 239

Concurrent Write Examples .............................................................................................. 240

Unloading Data .............................................................................................................................. 242

Unloading Data to Amazon S3 ................................................................................................. 242

Unloading Encrypted Data Files ................................................................................................ 245

Unloading Data in Delimited or Fixed-Width Format ................................................................... 246

Reloading Unloaded Data ........................................................................................................ 247

Creating User-Defined Functions ...................................................................................................... 248

UDF Security and Privileges ..................................................................................................... 248

Creating a Scalar SQL UDF ...................................................................................................... 248

Scalar SQL Function Example ........................................................................................... 249

Creating a Scalar Python UDF .................................................................................................. 249

Scalar Python UDF Example ............................................................................................. 250

Python UDF Data Types .................................................................................................. 250

API Version 2012-12-01

vii

Amazon Redshift Database Developer Guide

ANYELEMENT Data Type ................................................................................................. 251

Python Language Support ............................................................................................... 251

UDF Constraints ............................................................................................................. 254

Naming UDFs ......................................................................................................................... 254

Overloading Function Names ........................................................................................... 255

Preventing UDF Naming Conflicts ..................................................................................... 255

Logging Errors and Warnings ................................................................................................... 255

Tuning Query Performance .............................................................................................................. 257

Query Processing .................................................................................................................... 257

Query Planning And Execution Workflow ........................................................................... 257

Reviewing Query Plan Steps ............................................................................................ 259

Query Plan .................................................................................................................... 260

Factors Affecting Query Performance ................................................................................ 266

Analyzing and Improving Queries ............................................................................................. 267

Query Analysis Workflow ................................................................................................. 267

Reviewing Query Alerts ................................................................................................... 268

Analyzing the Query Plan ................................................................................................ 269

Analyzing the Query Summary ......................................................................................... 270

Improving Query Performance ......................................................................................... 275

Diagnostic Queries for Query Tuning ................................................................................. 277

Troubleshooting Queries .......................................................................................................... 280

Connection Fails ............................................................................................................. 281

Query Hangs .................................................................................................................. 281

Query Takes Too Long .................................................................................................... 282

Load Fails ...................................................................................................................... 283

Load Takes Too Long ...................................................................................................... 283

Load Data Is Incorrect ..................................................................................................... 283

Setting the JDBC Fetch Size Parameter ............................................................................. 284

Implementing Workload Management ............................................................................................... 285

Defining Query Queues ........................................................................................................... 285

Concurrency Level .......................................................................................................... 286

User Groups ................................................................................................................... 287

Query Groups ................................................................................................................ 287

Wildcards ....................................................................................................................... 287

WLM Memory Percent to Use ........................................................................................... 288

WLM Timeout ................................................................................................................ 288

Query Monitoring Rules .................................................................................................. 288

WLM Query Queue Hopping .................................................................................................... 288

WLM Timeout Queue Hopping ......................................................................................... 289

WLM Timeout Reassigned and Restarted Queries ................................................................ 289

QMR Hop Action Queue Hopping ..................................................................................... 289

QMR Hop Action Reassigned and Restarted Queries ............................................................ 290

WLM Query Queue Hopping Summary .............................................................................. 290

Short Query Acceleration ........................................................................................................ 291

Maximum SQA Run Time ................................................................................................. 292

Monitoring SQA .............................................................................................................. 292

Modifying the WLM Configuration ............................................................................................ 293

WLM Queue Assignment Rules ................................................................................................. 293

Queue Assignments Example ........................................................................................... 295

Assigning Queries to Queues ................................................................................................... 296

Assigning Queries to Queues Based on User Groups ............................................................ 296

Assigning a Query to a Query Group ................................................................................. 296

Assigning Queries to the Superuser Queue ........................................................................ 297

Dynamic and Static Properties ................................................................................................. 297

WLM Dynamic Memory Allocation .................................................................................... 298

Dynamic WLM Example ................................................................................................... 298

Query Monitoring Rules .......................................................................................................... 299

API Version 2012-12-01

viii

Amazon Redshift Database Developer Guide

Defining a Query Monitor Rule ......................................................................................... 300

Query Monitoring Metrics ................................................................................................ 301

Query Monitoring Rules Templates ................................................................................... 302

System Tables and Views for Query Monitoring Rules ......................................................... 303

WLM System Tables and Views ................................................................................................ 304

SQL Reference ............................................................................................................................... 306

Amazon Redshift SQL ............................................................................................................. 306

SQL Functions Supported on the Leader Node ................................................................... 306

Amazon Redshift and PostgreSQL .................................................................................... 307

Using SQL ............................................................................................................................. 312

SQL Reference Conventions ............................................................................................. 312

Basic Elements ............................................................................................................... 313

Expressions .................................................................................................................... 337

Conditions ..................................................................................................................... 340

SQL Commands ...................................................................................................................... 357

ABORT .......................................................................................................................... 359

ALTER DATABASE ........................................................................................................... 360

ALTER DEFAULT PRIVILEGES ............................................................................................ 361

ALTER GROUP ................................................................................................................ 363

ALTER SCHEMA .............................................................................................................. 364

ALTER TABLE ................................................................................................................. 365

ALTER TABLE APPEND ..................................................................................................... 374

ALTER USER ................................................................................................................... 377

ANALYZE ....................................................................................................................... 380

ANALYZE COMPRESSION ................................................................................................. 382

BEGIN ........................................................................................................................... 384

CANCEL ......................................................................................................................... 385

CLOSE ........................................................................................................................... 387

COMMENT ..................................................................................................................... 388

COMMIT ........................................................................................................................ 389

COPY ............................................................................................................................ 390

CREATE DATABASE .......................................................................................................... 448

CREATE EXTERNAL SCHEMA ............................................................................................ 449

CREATE EXTERNAL TABLE ................................................................................................ 452

CREATE FUNCTION ......................................................................................................... 463

CREATE GROUP .............................................................................................................. 467

CREATE LIBRARY ............................................................................................................ 468

CREATE SCHEMA ............................................................................................................ 470

CREATE TABLE ............................................................................................................... 471

CREATE TABLE AS ........................................................................................................... 483

CREATE USER ................................................................................................................. 490

CREATE VIEW ................................................................................................................. 493

DEALLOCATE .................................................................................................................. 496

DECLARE ....................................................................................................................... 496

DELETE ......................................................................................................................... 499

DROP DATABASE ............................................................................................................ 500

DROP FUNCTION ............................................................................................................ 501

DROP GROUP ................................................................................................................ 502

DROP LIBRARY ............................................................................................................... 502

DROP SCHEMA ............................................................................................................... 503

DROP TABLE .................................................................................................................. 504

DROP USER ................................................................................................................... 507

DROP VIEW ................................................................................................................... 508

END .............................................................................................................................. 509

EXECUTE ....................................................................................................................... 510

EXPLAIN ........................................................................................................................ 511

FETCH ........................................................................................................................... 515

API Version 2012-12-01

ix

Amazon Redshift Database Developer Guide

GRANT .......................................................................................................................... 516

INSERT .......................................................................................................................... 520

LOCK ............................................................................................................................ 524

PREPARE ....................................................................................................................... 525

RESET ........................................................................................................................... 527

REVOKE ......................................................................................................................... 527

ROLLBACK ..................................................................................................................... 531

SELECT .......................................................................................................................... 532

SELECT INTO .................................................................................................................. 560

SET ............................................................................................................................... 560

SET SESSION AUTHORIZATION ........................................................................................ 563

SET SESSION CHARACTERISTICS ....................................................................................... 564

SHOW ........................................................................................................................... 564

START TRANSACTION ...................................................................................................... 565

TRUNCATE ..................................................................................................................... 565

UNLOAD ........................................................................................................................ 566

UPDATE ......................................................................................................................... 580

VACUUM ........................................................................................................................ 584

SQL Functions Reference ......................................................................................................... 588

Leader Node–Only Functions ........................................................................................... 588

Compute Node–Only Functions ........................................................................................ 589

Aggregate Functions ....................................................................................................... 590

Bit-Wise Aggregate Functions .......................................................................................... 605

Window Functions .......................................................................................................... 610

Conditional Expressions ................................................................................................... 654

Date and Time Functions ................................................................................................. 663

Math Functions .............................................................................................................. 700

String Functions ............................................................................................................. 724

JSON Functions .............................................................................................................. 761

Data Type Formatting Functions ....................................................................................... 767

System Administration Functions ...................................................................................... 777

System Information Functions .......................................................................................... 780

Reserved Words ...................................................................................................................... 794

System Tables Reference ................................................................................................................. 797

System Tables and Views ......................................................................................................... 797

Types of System Tables and Views ............................................................................................ 797

Visibility of Data in System Tables and Views ............................................................................. 798

Filtering System-Generated Queries .................................................................................. 798

STL Tables for Logging ........................................................................................................... 798

STL_AGGR ..................................................................................................................... 800

STL_ALERT_EVENT_LOG .................................................................................................. 801

STL_ANALYZE ................................................................................................................. 803

STL_BCAST .................................................................................................................... 805

STL_COMMIT_STATS ....................................................................................................... 806

STL_CONNECTION_LOG ................................................................................................... 807

STL_DDLTEXT ................................................................................................................. 808

STL_DELETE ................................................................................................................... 810

STL_DISK_FULL_DIAG ...................................................................................................... 812

STL_DIST ....................................................................................................................... 812

STL_ERROR .................................................................................................................... 813

STL_EXPLAIN ................................................................................................................. 814

STL_FILE_SCAN .............................................................................................................. 816

STL_HASH ..................................................................................................................... 817

STL_HASHJOIN ............................................................................................................... 819

STL_INSERT ................................................................................................................... 820

STL_LIMIT ...................................................................................................................... 821

STL_LOAD_COMMITS ...................................................................................................... 823

API Version 2012-12-01

x

Amazon Redshift Database Developer Guide

STL_LOAD_ERRORS ......................................................................................................... 825

STL_LOADERROR_DETAIL ................................................................................................ 827

STL_MERGE ................................................................................................................... 829

STL_MERGEJOIN ............................................................................................................. 830

STL_NESTLOOP .............................................................................................................. 831

STL_PARSE .................................................................................................................... 832

STL_PLAN_INFO ............................................................................................................. 833

STL_PROJECT ................................................................................................................. 835

STL_QUERY .................................................................................................................... 837

STL_QUERY_METRICS ...................................................................................................... 838

STL_QUERYTEXT ............................................................................................................ 841

STL_REPLACEMENTS ....................................................................................................... 842

STL_RESTARTED_SESSIONS ............................................................................................. 843

STL_RETURN .................................................................................................................. 844

STL_S3CLIENT ................................................................................................................ 845

STL_S3CLIENT_ERROR ..................................................................................................... 847

STL_SAVE ...................................................................................................................... 848

STL_SCAN ...................................................................................................................... 849

STL_SESSIONS ............................................................................................................... 851

STL_SORT ...................................................................................................................... 852

STL_SSHCLIENT_ERROR ................................................................................................... 853

STL_STREAM_SEGS ......................................................................................................... 854

STL_TR_CONFLICT .......................................................................................................... 855

STL_UNDONE ................................................................................................................. 856

STL_UNIQUE .................................................................................................................. 856

STL_UNLOAD_LOG .......................................................................................................... 858

STL_USERLOG ................................................................................................................ 859

STL_UTILITYTEXT ........................................................................................................... 860

STL_VACUUM ................................................................................................................. 862

STL_WINDOW ................................................................................................................ 864

STL_WLM_ERROR ........................................................................................................... 865

STL_WLM_RULE_ACTION ................................................................................................. 866

STL_WLM_QUERY ........................................................................................................... 866

STV Tables for Snapshot Data .................................................................................................. 868

STV_ACTIVE_CURSORS .................................................................................................... 869

STV_BLOCKLIST ............................................................................................................. 869

STV_CURSOR_CONFIGURATION ........................................................................................ 872

STV_EXEC_STATE ............................................................................................................ 873

STV_INFLIGHT ................................................................................................................ 874

STV_LOAD_STATE ........................................................................................................... 875

STV_LOCKS .................................................................................................................... 876

STV_PARTITIONS ............................................................................................................ 877

STV_QUERY_METRICS ..................................................................................................... 879

STV_RECENTS ................................................................................................................ 882

STV_SESSIONS ............................................................................................................... 883

STV_SLICES ................................................................................................................... 884

STV_STARTUP_RECOVERY_STATE ..................................................................................... 885

STV_TBL_PERM .............................................................................................................. 886

STV_TBL_TRANS ............................................................................................................. 888

STV_WLM_QMR_CONFIG ................................................................................................. 889

STV_WLM_CLASSIFICATION_CONFIG ................................................................................. 890

STV_WLM_QUERY_QUEUE_STATE ..................................................................................... 891

STV_WLM_QUERY_STATE ................................................................................................ 892

STV_WLM_QUERY_TASK_STATE ........................................................................................ 893

STV_WLM_SERVICE_CLASS_CONFIG .................................................................................. 894

STV_WLM_SERVICE_CLASS_STATE .................................................................................... 896

System Views ......................................................................................................................... 896

API Version 2012-12-01

xi

Amazon Redshift Database Developer Guide

SVV_COLUMNS .............................................................................................................. 897

SVL_COMPILE ................................................................................................................. 899

SVV_DISKUSAGE ............................................................................................................. 900

SVV_EXTERNAL_COLUMNS .............................................................................................. 902

SVV_EXTERNAL_DATABASES ............................................................................................ 902

SVV_EXTERNAL_PARTITIONS ............................................................................................ 903

SVV_EXTERNAL_SCHEMAS ............................................................................................... 903

SVV_EXTERNAL_TABLES .................................................................................................. 904

SVV_INTERLEAVED_COLUMNS .......................................................................................... 905

SVL_QERROR ................................................................................................................. 906

SVL_QLOG ..................................................................................................................... 906

SVV_QUERY_INFLIGHT .................................................................................................... 907

SVL_QUERY_QUEUE_INFO ............................................................................................... 908

SVL_QUERY_METRICS ..................................................................................................... 909

SVL_QUERY_METRICS_SUMMARY ..................................................................................... 911

SVL_QUERY_REPORT ...................................................................................................... 912

SVV_QUERY_STATE ......................................................................................................... 914

SVL_QUERY_SUMMARY ................................................................................................... 916

SVL_S3LOG .................................................................................................................... 918

SVL_S3PARTITION .......................................................................................................... 919

SVL_S3QUERY ................................................................................................................ 920

SVL_S3QUERY_SUMMARY ................................................................................................ 921

SVL_S3RETRIES .............................................................................................................. 924

SVL_STATEMENTTEXT ..................................................................................................... 925

SVV_TABLES .................................................................................................................. 926

SVV_TABLE_INFO ............................................................................................................ 926

SVV_TRANSACTIONS ....................................................................................................... 928

SVL_USER_INFO ............................................................................................................. 929

SVL_UDF_LOG ................................................................................................................ 930

SVV_VACUUM_PROGRESS ................................................................................................ 932

SVV_VACUUM_SUMMARY ................................................................................................ 933

SVL_VACUUM_PERCENTAGE ............................................................................................. 934

System Catalog Tables ............................................................................................................ 935

PG_CLASS_INFO ............................................................................................................. 935

PG_DEFAULT_ACL ........................................................................................................... 936

PG_EXTERNAL_SCHEMA .................................................................................................. 938

PG_LIBRARY ................................................................................................................... 939

PG_STATISTIC_INDICATOR ............................................................................................... 939

PG_TABLE_DEF ............................................................................................................... 940

Querying the Catalog Tables ............................................................................................ 942

Configuration Reference .................................................................................................................. 947

Modifying the Server Configuration .......................................................................................... 947

analyze_threshold_percent ....................................................................................................... 948

Values (Default in Bold) ................................................................................................... 948

Description .................................................................................................................... 948

Examples ....................................................................................................................... 948

datestyle ............................................................................................................................... 948

Values (Default in Bold) ................................................................................................... 948

Description .................................................................................................................... 948

Example ........................................................................................................................ 948

describe_field_name_in_uppercase ............................................................................................ 949

Values (Default in Bold) ................................................................................................... 949

Description .................................................................................................................... 948

Example ........................................................................................................................ 948

enable_result_cache_for_session ............................................................................................... 949

Values (Default in Bold) ................................................................................................... 949

Description .................................................................................................................... 948

API Version 2012-12-01

xii