Document history
Note
For a description of new features in Amazon Redshift, see What's new
The following table describes the important documentation changes to the Amazon Redshift Database Developer Guide after May 2018. For notification about updates to this documentation, you can subscribe to an RSS feed.
API version: 2012-12-01
For a list of the changes to the Amazon Redshift Management Guide, see Amazon Redshift Management Guide Document History.
For more information about new features, including a list of fixes and the associated cluster version numbers for each release, see Cluster Version History.
| Change | Description | Date |
|---|---|---|
Support for spatial 3D and 4D geometries and new spatial functions | You can now use additional spatial functions and 3D and 4D geometry support is added to some functions. | August 19, 2021 |
Support for column compression encoding for automatic table optimization | You can specify the ENCODE AUTO option for a table to automatically manage compression encoding for all columns in the table. | August 3, 2021 |
You can now run multiple SQL statements or a statement with parameters with the Amazon Redshift Data API. | July 28, 2021 | |
Support for case-insensitive collation with column level overrides | You can now use the COLLATE clause within a CREATE DATABASE statement to specify the default collation. | June 24, 2021 |
You can now share data across AWS accounts. | April 30, 2021 | |
You can now use a recursive common table expression (CTE) in your SQL. | April 29, 2021 | |
You can now query data across databases in a cluster. | March 10, 2021 | |
Support for fine-grained access control on COPY and UNLOAD commands | You can now grant the privilege to run COPY and UNLOAD commands to specific users and groups in your Amazon Redshift cluster to create more fine-grained access control policy. | January 12, 2021 |
You can now define the SUPER data type. | December 9, 2020 | |
You can now write a federated query to a supported MySQL engine. | December 9, 2020 | |
You can now share data across Amazon Redshift clusters. | December 9, 2020 | |
You can now define automatic distribution and sort keys. | December 9, 2020 | |
You can now create, train, and deploy machine learning (ML) models. | December 8, 2020 | |
Support for automatic refresh and query rewrite of materialized views | You can now keep materialized views up-to-date with automatic refresh and query performance can be improved with automatic rewrite. | November 11, 2020 |
You can now create tables with TIME and TIMETZ data types. TIME data type stores the time of day without timezone information, and TIMETZ stores the time of day including timezone information | November 11, 2020 | |
You can now can write Lambda UDFs to enable external tokenization of data. | October 26, 2020 | |
You can now alter a table column encoding. | October 20, 2020 | |
Amazon Redshift can now query across databases in a cluster. | October 15, 2020 | |
Amazon Redshift can now store and process HyperLogLogSketches. | October 2, 2020 | |
Enhancements to creating external tables for Redshift Spectrum. | September 24, 2020 | |
Enhancements include loading a shapefile and several new spatial SQL functions. | September 15, 2020 | |
You can create materialized views in Amazon Redshift that reference external data sources. | June 19, 2020 | |
You can write to external tables by running CREATE EXTERNAL TABLE AS SELECT to write to a new external table or INSERT INTO to insert data into an existing external table. | June 8, 2020 | |
Updates to commands and views that manage storage controls for schemas. | June 2, 2020 | |
Updated information about querying data with federated queries. | April 16, 2020 | |
Added descriptions of additional spatial functions. | April 2, 2020 | |
Materialized views are generally available starting with cluster version 1.0.13059. | February 19, 2020 | |
Column-level privileges are available starting with cluster version 1.0.13059. | February 19, 2020 | |
You can use an ALTER TABLE command with the ALTER DISTSTYLE ALL clause to change the distribution style of a table. | February 11, 2020 | |
Updated the guide to describe federated query with an updated CREATE EXTERNAL SCHEMA. | December 3, 2019 | |
Updated the guide to describe new parameters of the UNLOAD command. | December 3, 2019 | |
Updated the guide to describe support for spatial data. | November 21, 2019 | |
Updated the guide to describe the new Amazon Redshift console. | November 11, 2019 | |
Amazon Redshift can automatically sort table data. | November 7, 2019 | |
You can use the BOOST option when vacuuming tables. | November 7, 2019 | |
You can create tables with default IDENTITY columns. | September 19, 2019 | |
You can encode some columns with AZ64 compression encoding. | September 19, 2019 | |
You can set the query priority of an automatic WLM queue. | August 22, 2019 | |
You can use a Lake Formation Data Catalog with Amazon Redshift Spectrum. | August 8, 2019 | |
You can use a COPY command with COMPUPDATE PRESET to enable Amazon Redshift to choose the compression encoding. | June 13, 2019 | |
You can use an ALTER TABLE command with ALTER COLUMN to increase the size of a VARCHAR column. | May 22, 2019 | |
You can define PL/pgSQL stored procedures in Amazon Redshift. | April 24, 2019 | |
Support for an automatic workload management (WLM) configuration | You can enable Amazon Redshift to run with automatic WLM. | April 24, 2019 |
You can use the UNLOAD command to apply Zstandard compression to text and comma-separated value (CSV) files unloaded to Amazon S3. | April 3, 2019 | |
When concurrency scaling is enabled, Amazon Redshift automatically adds additional cluster capacity when you need it to process an increase in concurrent read queries. | March 21, 2019 | |
You can use the UNLOAD command to unload to a file formatted as CSV text. | March 13, 2019 | |
To enable automatic distribution, you can specify the AUTO distribution style with a CREATE TABLE statement. When you enable automatic distribution, Amazon Redshift assigns an optimal distribution style based on the table data. The change in distribution occurs in the background, in a few seconds. | January 23, 2019 | |
COPY from Parquet supports SMALLINT | COPY now supports loading from Parquet formatted files into columns that use the SMALLINT data type. For more information, see COPY from Columnar Data Formats | January 2, 2019 |
You can drop an external database by including the DROP EXTERNAL DATABASE clause with a DROP SCHEMA command. | December 3, 2018 | |
You can UNLOAD to an Amazon S3 bucket in another AWS Region by specifying the REGION parameter. | October 31, 2018 | |
Amazon Redshift automatically runs a VACUUM DELETE operation in the background, so you rarely, if ever, need to run a DELETE ONLY vacuum. Amazon Redshift schedules the VACUUM DELETE to run during periods of reduced load and pauses the operation during periods of high load. | October 31, 2018 | |
When you don't specify a distribution style with a CREATE TABLE statement, Amazon Redshift assigns an optimal distribution style based on the table data. The change in distribution occurs in the background, in a few seconds. | October 31, 2018 | |
You can now specify levels of access to data stored in the AWS Glue Data Catalog. | October 15, 2018 | |
You can specify the MANIFEST VERBOSE option with an UNLOAD command to add metadata to the manifest file, including the names and data types of columns, file sizes, and row counts. | October 10, 2018 | |
Add multiple partitions using a single ALTER TABLE statement | For Redshift Spectrum external tables, you can combine multiple PARTITION clauses in a single ALTER TABLE ADD statement. For more information, see Alter External Table Examples. | October 10, 2018 |
You can specify the HEADER option with an UNLOAD command to add a header line containing column names at the top of each output file. | September 19, 2018 | |
SVL_S3Retries, SVL_USER_INFO, and STL_DISK_FULL_DIAG documentation added. | August 31, 2018 | |
You can now query nested data stored in Amazon Redshift Spectrum tables. For more information, see Tutorial: Querying Nested Data with Amazon Redshift Spectrum. | August 8, 2018 | |
Short query acceleration (SQA) is now enabled by default for all new clusters. SQA uses machine learning to provide higher performance, faster results, and better predictability of query execution times. For more information, see Short Query Acceleration. | August 8, 2018 | |
You can now get tailored recommendations on how to improve cluster performance and reduce operating costs from the Amazon Redshift Advisor. For more information, see Amazon Redshift Advisor. | July 26, 2018 | |
You can now refer to an aliased expression immediately after you define it. For more information, see SELECT List. | July 18, 2018 | |
You can now specify compression type when creating an external table with Amazon Redshift Spectrum. For more information, see Create External Tables. | June 27, 2018 | |
Documentation added for a new System Information function: PG_LAST_UNLOAD_ID. For more information, see PG_LAST_UNLOAD_ID. | June 27, 2018 | |
ALTER TABLE now supports renaming columns for external tables. For more information, see Alter External Table Examples. | June 7, 2018 |
Earlier updates
The following table describes the important changes in each release of the Amazon Redshift Database Developer Guide before June 2018.
| Change | Description | Date changed |
|---|---|---|
| COPY from Parquet includes SMALLINT | COPY now supports loading from Parquet formatted files into columns that use the SMALLINT data type. For more information, see COPY from columnar data formats |
January 2, 2019 |
| COPY from columnar formats | COPY now supports loading from files on Amazon S3 that use Parquet and ORC columnar data formats. For more information, see COPY from columnar data formats |
May 17, 2018 |
| Dynamic maximum run time for SQA | By default, workload management (WLM) now dynamically assigns a value for the short query acceleration (SQA) maximum run time based on analysis of your cluster's workload. For more information, see Maximum runtime for short queries. |
May 17, 2018 |
| New column in STL_LOAD_COMMITS | The STL_LOAD_COMMITS
system table has a new column,
|
May 10, 2018 |
| New columns in STL_HASHJOIN and other system log tables | The STL_HASHJOIN system
table has three new columns, |
May 17, 2018 |
| New columns in STL_AGGR | The STL_AGGR system table has
two new columns, |
April 19, 2018 |
| New options for REGEX functions | For the REGEXP_INSTR and REGEXP_SUBSTR functions, you can now specify which occurence of a match to use and whether to perform a case-sensitive match. REGEXP_INSTR also allows you specify whether to return the position of the first character of the match or the position of the first character following the end of the match. |
March 22, 2018 |
| New columns in system tables | The tombstonedblocks, tossedblocks, and batched_by columns were added to the STL_COMMIT_STATS system table. The localslice column was added to the STV_SLICES system view. | March 22, 2018 |
| Add and drop columns in external tables | ALTER TABLE now supports ADD COLUMN and DROP COLUMN for Amazon Redshift Spectrum external tables. | March 22, 2018 |
| Redshift Spectrum new AWS Regions | Redshift Spectrum is now available in the Mumbai and São Paulo Regions. For a list of supported Regions, see Amazon Redshift Spectrum Regions. | March 22, 2018 |
| Table limit increased to 20,000 | The maximum number of tables is now 20,000 for 8xlarge cluster node types. The limit for large and xlarge node types is 9,900. For more information, see Limits and quotas. |
March 13, 2018 |
| Redshift Spectrum support for JSON and Ion | Using Redshift Spectrum, you can reference files with scalar data in JSON or Ion data formats. For more information, see CREATE EXTERNAL TABLE. |
February 26, 2018 |
| IAM role chaining for Redshift Spectrum | You can chain AWS Identity and Access Management (IAM) roles so that your cluster can assume other roles not attached to the cluster, including roles belonging to another AWS account. For more information, see Chaining IAM roles in Amazon Redshift Spectrum. |
February 1, 2018 |
| ADD PARTITION supports IF NOT EXISTS | The ADD PARTITION clause for ALTER TABLE now supports an IF NOT EXISTS option. For more information, see ALTER TABLE. | January 11, 2018 |
| DATE data for external tables | Redshift Spectrum external tables now support the DATE data type. For more information, see CREATE EXTERNAL TABLE. | January 11, 2018 |
| Redshift Spectrum new AWS Regions | Redshift Spectrum is now available in the Singapore, Sydney, Seoul, and Frankfurt Regions. For a list of supported AWS Regions, see Amazon Redshift Spectrum Regions. | November 16, 2017 |
| Short query acceleration in Amazon Redshift workload management (WLM) | Short query acceleration (SQA) prioritizes selected short-running queries ahead of longer-running queries. SQA executes short-running queries in a dedicated space, so that SQA queries aren't forced to wait in queues behind longer queries. With SQA, short-running queries begin executing more quickly and users see results sooner. For more information, see Short query acceleration. | November 16, 2017 |
| WLM reassigns hopped queries | Instead of canceling and restarting a hopped query, Amazon Redshift workload management (WLM) now reassigns eligible queries to a new queue. When WLM reassigns a query, it moves the query to the new queue and continues execution, which saves time and system resources. Hopped queries that are not eligible to be reassigned are restarted or canceled. For more information, see WLM query queue hopping. | November 16, 2017 |
| System log access for users | In most system log tables that are visible to users, rows generated by another user are invisible to a regular user by default. To permit a regular user to see all rows in user-visible tables, including rows generated by another user, run ALTER USER or CREATE USER and set the SYSLOG ACCESS parameter to UNRESTRICTED. | November 16, 2017 |
| Result caching | With Result caching , when you
run a query Amazon Redshift caches the result. When you run the query again, Amazon Redshift
checks for a valid, cached copy of the query result. If a match is found
in the result cache, Amazon Redshift uses the cached result and doesn't run the
query. Result caching is turned on by default. To turn off result caching,
set the enable_result_cache_for_session configuration
parameter to off. |
November 16, 2017 |
| Column metadata functions | PG_GET_COLS and PG_GET_LATE_BINDING_VIEW_COLS return column metadata for Amazon Redshift tables, views, and late-binding views. | November 16, 2017 |
| WLM queue hopping for CTAS | Amazon Redshift workload management (WLM) now supports query queue hopping for CREATE TABLE AS (CTAS) statements as well as read-only queries, such as SELECT statements. For more information, see WLM query queue hopping. | October 19, 2017 |
| Amazon Redshift Spectrum manifest files | When you create a Redshift Spectrum external table, you can specify a manifest file that lists the locations of data files on Amazon S3. For more information, see CREATE EXTERNAL TABLE. | October 19, 2017 |
| Amazon Redshift Spectrum new AWS Regions | Redshift Spectrum is now available in the EU (Ireland) and Asia Pacific (Tokyo) Regions. For a list of supported AWS Regions, see Amazon Redshift Spectrum limitations. | October 19, 2017 |
| Amazon Redshift Spectrum added file formats | You can now create Redshift Spectrum external tables based on Regex, OpenCSV, and Avro data file formats. For more information, see CREATE EXTERNAL TABLE. | October 5, 2017 |
| Pseudocolumns for Amazon Redshift Spectrum external tables | You can select the $path and $size
pseudocolumns in a Redshift Spectrum external table to view the location
and size of the referenced data files in Amazon S3. For more information, see
Pseudocolumns. |
October 5, 2017 |
| Functions to validate JSON | You can use the IS_VALID_JSON and IS_VALID_JSON_ARRAY functions to check for
valid JSON formatting. The other JSON functions now have an optional
null_if_invalid argument. |
October 5, 2017 |
| LISTAGG DISTINCT | You can use the DISTINCT clause with the LISTAGG aggregate function and the LISTAGG window function to eliminate duplicate values from the specified expression before concatenating. | October 5, 2017 |
| View column names in uppercase | To view column names in SELECT results in uppercase, you can set the
describe_field_name_in_uppercase configuration
parameter to true. |
October 5, 2017 |
| Skip header lines in external tables | You can set the skip.header.line.count property in the
CREATE EXTERNAL TABLE command to skip header
lines at the beginning of Redshift Spectrum data files. |
October 5, 2017 |
| Scan row count | WLM query monitor rules uses the scan_row_count metric to return the number of rows in a scan step. The row count is the total number of rows emitted before filtering rows marked for deletion (ghost rows) and before applying user-defined query filters. For more information, see Query monitoring metrics for Amazon Redshift provisioned. | September 21, 2017 |
| SQL user-defined functions | A scalar SQL user-defined function (UDF) incorporates a SQL SELECT clause that executes when the function is called and returns a single value. For more information, see Scalar SQL UDFs. | August 31, 2017 |
| Late-binding views | A late-binding view is not bound to the underlying database objects, such as tables and user-defined functions. As a result, there is no dependency between the view and the objects it references. You can create a view even if the referenced objects don't exist. Because there is no dependency, you can drop or alter a referenced object without affecting the view. Amazon Redshift doesn't check for dependencies until the view is queried. To create a late-binding view, specify the WITH NO SCHEMA BINDING clause with your CREATE VIEW statement. For more information, see CREATE VIEW. | August 31, 2017 |
| OCTET_LENGTH function | OCTET_LENGTH returns the length of the specified string as the number of bytes. | August 18, 2017 |
| ORC and Grok files types supported | Amazon Redshift Spectrum now supports the ORC and Grok data formats for Redshift Spectrum data files. For more information, see Data files for queries in Amazon Redshift Spectrum. | August 18, 2017 |
| RegexSerDe now supported | Amazon Redshift Spectrum now supports the RegexSerDe data format. For more information, see Data files for queries in Amazon Redshift Spectrum. | July 19, 2017 |
| New columns added to SVV_TABLES and SVV_COLUMNS | The columns domain_name and remarks were
added to SVV_COLUMNS.
A remarks column was added to SVV_TABLES. |
July 19, 2017 |
| SVV_TABLES and SVV_COLUMNS system views | The SVV_TABLES and SVV_COLUMNS system views provide information about columns and other details for local and external tables and views. | July 7, 2017 |
| VPC no longer required for Amazon Redshift Spectrum with Amazon EMR Hive metastore | Redshift Spectrum removed the requirement that the Amazon Redshift cluster and the Amazon EMR cluster must be in the same VPC and the same subnet when using an Amazon EMR Hive metastore. For more information, see Working with external catalogs in Amazon Redshift Spectrum. | July 7, 2017 |
| UNLOAD to smaller file sizes | By default, UNLOAD creates multiple files on Amazon S3 with a maximum size of 6.2 GB. To create smaller files, specify the MAXFILESIZE with the UNLOAD command. You can specify a maximum file size between 5 MB and 6.2 GB. For more information, see UNLOAD. | July 7, 2017 |
| TABLE PROPERTIES | You can now set the TABLE PROPERTIES numRows parameter for CREATE EXTERNAL TABLE or ALTER TABLE to update table statistics to reflect the number of rows in the table. | June 6, 2017 |
| ANALYZE PREDICATE COLUMNS | To save time and cluster resources, you can choose to analyze only the columns that are likely to be used as predicates. When you run ANALYZE with the PREDICATE COLUMNS clause, the analyze operation includes only columns that have been used in a join, filter condition, or group by clause, or are used as a sort key or distribution key. For more information, see Analyzing tables. | May 25, 2017 |
| IAM policies for Amazon Redshift Spectrum | To grant access to an Amazon S3 bucket only using Redshift Spectrum, you
can include a condition that allows access for the user agent AWS
Redshift/Spectrum. For more information, see IAM policies for Amazon Redshift Spectrum. |
May 25, 2017 |
| Amazon Redshift Spectrum Recursive Scan | Redshift Spectrum now scans files in subfolders as well as the specified folder in Amazon S3. For more information, see External tables for Redshift Spectrum. | May 25, 2017 |
| Query monitoring rules | Using WLM query monitoring rules, you can define metrics-based performance boundaries for WLM queues and specify what action to take when a query goes beyond those boundaries—log, hop, or abort. You define query monitoring rules as part of your workload management (WLM) configuration. For more information, see WLM query monitoring rules. | April 21, 2017 |
| Amazon Redshift Spectrum | Using Redshift Spectrum, you can efficiently query and retrieve data from files in Amazon S3 without having to load the data into tables. Redshift Spectrum queries execute very fast against large datasets because Redshift Spectrum scans the data files directly in Amazon S3. Much of the processing occurs in the Amazon Redshift Spectrum layer, and most of the data remains in Amazon S3. Multiple clusters can concurrently query the same dataset on Amazon S3 without the need to make copies of the data for each cluster. For more information, see Amazon Redshift Spectrum | April 19, 2017 |
| New system tables to support Redshift Spectrum | The following new system views have been added to support Redshift Spectrum: | April 19, 2017 |
| APPROXIMATE PERCENTILE_DISC aggregate function | The APPROXIMATE PERCENTILE_DISC aggregate function is now available. | April 4, 2017 |
| Server-side encryption with KMS | You can now unload data to Amazon S3 using server-side encryption with an AWS Key Management Service key (SSE-KMS). In addition, COPY now transparently loads KMS-encrypted data files from Amazon S3. For more information, see UNLOAD. | February 9, 2017 |
| New authorization syntax | You can now use the IAM_ROLE, MASTER_SYMMETRIC_KEY, ACCESS_KEY_ID, SECRET_ACCESS_KEY, and SESSION_TOKEN parameters to provide authorization and access information for COPY, UNLOAD, and CREATE LIBRARY commands. The new authorization syntax provides a more flexible alternative to providing a single string argument to the CREDENTIALS parameter. For more information, see Authorization parameters. | February 9, 2017 |
| Schema limit increase | You can now create up to 9,900 schemas per cluster. For more information, see CREATE SCHEMA. | February 9, 2017 |
| Default table encoding | CREATE TABLE and ALTER TABLE now assign LZO compression encoding to most new columns. Columns defined as sort keys, columns that are defined as BOOLEAN, REAL, or DOUBLE PRECISION data types, and temporary tables are assigned RAW encoding by default. For more information, see ENCODE. | February 6, 2017 |
| ZSTD compression encoding | Amazon Redshift now supports ZSTD column compression encoding. | January 19, 2017 |
| PERCENTILE_CONT and MEDIAN aggregate functions | PERCENTILE_CONT and MEDIAN are now available as aggregate functions as well as window functions. | January 19, 2017 |
| User-defined function (UDF) User Logging | You can use the Python logging module to create user-defined error and warning messages in your UDFs. Following query execution, you can query the SVL_UDF_LOG system view to retrieve logged messages. For more information about user-defined messages, see Logging errors and warnings in Python UDFs | December 8, 2016 |
| ANALYZE COMPRESSION estimated reduction | The ANALYZE COMPRESSION command now reports an estimate for percentage reduction in disk space for each column. For more information, see ANALYZE COMPRESSION. | November 10, 2016 |
| Connection limits | You can now set a limit on the number of database connections a user is permitted to have open concurrently. You can also limit the number of concurrent connections for a database. For more information, see CREATE USER and CREATE DATABASE. | November 10, 2016 |
| COPY sort order enhancement | COPY now automatically adds new rows to the table's sorted region when you load your data in sort key order. For specific requirements to enable this enhancement, see Load your data in sort key order | November 10, 2016 |
| CTAS with compression | CREATE TABLE AS (CTAS) now automatically assigns compression encodings to new tables based on the column's data type. For more information, see Inheritance of column and table attributes. | October 28, 2016 |
| Time stamp with time zone data type | Amazon Redshift now supports a timestamp with time zone (TIMESTAMPTZ) data type. Also, several new functions have been added to support the new data type. For more information, see Date and time functions. | September 29, 2016 |
| Analyze threshold | To reduce processing time and improve overall system performance for
ANALYZE operations,
Amazon Redshift skips analyzing a table if the percentage of rows that
have changed since the last ANALYZE command run is lower than the
analyze threshold specified by the analyze_threshold_percent parameter. By default,
analyze_threshold_percent is 10. |
August 9, 2016 |
| New STL_RESTARTED_SESSIONS system table | When Amazon Redshift restarts a session, STL_RESTARTED_SESSIONS records the new process ID (PID) and the old PID. | August 9, 2016 |
| Updated the Date and Time Functions documentation | Added a summary of functions with links to the Date and time functions, and updated the function references for consistency. | June 24, 2016 |
| New columns in STL_CONNECTION_LOG | The STL_CONNECTION_LOG system table has two new columns to track SSL connections. If you routinely load audit logs to an Amazon Redshift table, you will need to add the following new columns to the target table: sslcompression and sslexpansion. | May 5, 2016 |
| MD5-hash password | You can specify the password for a CREATE USER or ALTER USER command by supplying the MD5-hash string of the password and user name. | April 21, 2016 |
| New column in STV_TBL_PERM | The backup column in the STV_TBL_PERM system view indicates whether the
table is included in cluster snapshots. For more information, see BACKUP. |
April 21, 2016 |
| No-backup tables | For tables, such as staging tables, that won't contain critical data, you can specify BACKUP NO in your CREATE TABLE or CREATE TABLE AS statement to prevent Amazon Redshift from including the table in automated or manual snapshots. Using no-backup tables saves processing time when creating snapshots and restoring from snapshots and reduces storage space on Amazon S3. | April 7, 2016 |
| VACUUM delete threshold | By default, the VACUUM command now reclaims space such that at least 95 percent of the remaining rows are not marked for deletion. As a result, VACUUM usually needs much less time for the delete phase compared to reclaiming 100 percent of deleted rows. You can change the default threshold for a single table by including the TO threshold PERCENT parameter when you run the VACUUM command. | April 7, 2016 |
| SVV_TRANSACTIONS system table | The SVV_TRANSACTIONS system view records information about transactions that currently hold locks on tables in the database. | April 7, 2016 |
| Using IAM roles to access other AWS resources | To move data between your cluster and another AWS resource, such as Amazon S3, DynamoDB, Amazon EMR, or Amazon EC2, your cluster must have permission to access the resource and perform the necessary actions. As a more secure alternative to providing an access key pair with COPY, UNLOAD, or CREATE LIBRARY commands, you can now you specify an IAM role that your cluster uses for authentication and authorization. For more information, see Role-based access control. | March 29, 2016 |
| VACUUM sort threshold | The VACUUM command now skips the sort phase for any table where more than 95 percent of the table's rows are already sorted. You can change the default sort threshold for a single table by including the TO threshold PERCENT parameter when you run the VACUUM command. | March 17, 2016 |
| New columns in STL_CONNECTION_LOG | The STL_CONNECTION_LOG system table has three new columns. If you routinely load audit logs to an Amazon Redshift table, you will need to add the following new columns to the target table: sslversion, sslcipher, and mtu. | March 17, 2016 |
| UNLOAD with bzip2 compression | You now have the option to UNLOAD using bzip2 compression. | February 8, 2016 |
| ALTER TABLE APPEND | ALTER TABLE APPEND appends rows to a target table by moving data from an existing source table. ALTER TABLE APPEND is usually much faster than a similar CREATE TABLE AS or INSERT INTO operation because data is moved, not duplicated. | February 8, 2016 |
| WLM query queue hopping | If workload management (WLM) cancels a read-only query, such as a SELECT statement, due to a WLM timeout, WLM attempts to route the query to the next matching queue. For more information, see WLM query queue hopping | January 7, 2016 |
| ALTER DEFAULT PRIVILEGES | You can use the ALTER DEFAULT PRIVILEGES command to define the default set of access privileges to be applied to objects that are created in the future by the specified user. | December 10, 2015 |
| bzip2 file compression | The COPY command supports loading data from files that were compressed using bzip2. | December 10, 2015 |
| NULLS FIRST and NULLS LAST | You can specify whether an ORDER BY clause should rank NULLS first or last in the result set. For more information, see ORDER BY clause and Window function syntax summary. | November 19, 2015 |
| REGION keyword for CREATE LIBRARY | If the Amazon S3 bucket that contains the UDF library files does not reside in the same AWS Region as your Amazon Redshift cluster, you can use the REGION option to specify the region in which the data is located. For more information, see CREATE LIBRARY. | November 19, 2015 |
| User-defined scalar functions (UDFs) | You can now create custom user-defined scalar functions to implement non-SQL processing functionality provided either by Amazon Redshift-supported modules in the Python 2.7 Standard Library or your own custom UDFs based on the Python programming language. For more information, see User-defined functions in Amazon Redshift. | September 11, 2015 |
| Dynamic properties in WLM configuration | The WLM configuration parameter now supports applying some properties dynamically. Other properties remain static changes and require that associated clusters be rebooted so that the configuration changes can be applied. For more information, see WLM dynamic and static configuration properties and Workload management. | August 3, 2015 |
| LISTAGG function | The LISTAGG function and LISTAGG window function return a string created by concatenating a set of column values. | July 30, 2015 |
| Deprecated parameter | The max_cursor_result_set_size configuration parameter is deprecated. The size of cursor result sets are constrained based on the cluster's node type. For more information, see Cursor constraints. |
July 24, 2015 |
| Revised COPY command documentation | The COPY command reference has been extensively revised to make the material friendlier and more accessible. | July 15, 2015 |
| COPY from Avro format | The COPY command supports loading data in Avro format from data files on Amazon S3, Amazon EMR, and from remote hosts using SSH. For more information, see AVRO and Copy from Avro examples. | July 8, 2015 |
| STV_STARTUP_RECOVERY_STATE | The STV_STARTUP_RECOVERY_STATE system table records the state of tables that are temporarily locked during cluster restart operations. Amazon Redshift places a temporary lock on tables while they are being processed to resolve stale transactions following a cluster restart. | May 25, 2015 |
| ORDER BY optional for ranking functions | The ORDER BY clause is now optional for certain window ranking functions. For more information, see CUME_DIST window function, DENSE_RANK window function, RANK window function, NTILE window function, PERCENT_RANK window function, and ROW_NUMBER window function. | May 25, 2015 |
| Interleaved sort keys | Interleaved sort keys give equal weight to each column in the sort key. Using interleaved sort keys instead of the default compound keys significantly improves performance for queries that use restrictive predicates on secondary sort columns, especially for large tables. Interleaved sorting also improves overall performance when multiple queries filter on different columns in the same table. For more information, see Sort keys and CREATE TABLE. | May 11, 2015 |
| Revised tuning query performance topic | Query performance tuning has been expanded to include new queries for analyzing query performance and more examples. Also, the topic has been revised to be clearer and more complete. Amazon Redshift best practices for designing queries has more information about how to write queries to improve performance. | March 23, 2015 |
| SVL_QUERY_QUEUE_INFO | The SVL_QUERY_QUEUE_INFO view summarizes details for queries that spent time in a WLM query queue or commit queue. | February 19, 2015 |
| SVV_TABLE_INFO | You can use the SVV_TABLE_INFO view to diagnose and address table design issues that can influence query performance, including issues with compression encoding, distribution keys, sort style, data distribution skew, table size, and statistics. | February 19, 2015 |
| UNLOAD uses server-side file encryption | The UNLOAD command now automatically uses Amazon S3 server-side encryption (SSE) to encrypt all unload data files. Server-side encryption adds another layer of data security with little or no change in performance. | October 31, 2014 |
| CUME_DIST window function | The CUME_DIST window function calculates the cumulative distribution of a value within a window or partition. | October 31, 2014 |
| MONTHS_BETWEEN function | The MONTHS_BETWEEN function determines the number of months between two dates. | October 31, 2014 |
| NEXT_DAY function | The NEXT_DAY function returns the date of the first instance of a specified day that is later than the given date. | October 31, 2014 |
| PERCENT_RANK window function | The PERCENT_RANK window function calculates the percent rank of a given row. | October 31, 2014 |
| RATIO_TO_REPORT window function | The RATIO_TO_REPORT window function calculates the ratio of a value to the sum of the values in a window or partition. | October 31, 2014 |
| TRANSLATE function | The TRANSLATE function replaces all occurrences of specified characters within a given expression with specified substitutes. | October 31, 2014 |
| NVL2 function | The NVL2 function returns one of two values based on whether a specified expression evaluates to NULL or NOT NULL. | October 16, 2014 |
| MEDIAN window function | The MEDIAN window function calculates the median value for the range of values in a window or partition. | October 16, 2014 |
| ON ALL TABLES IN SCHEMA schema_name clause for GRANT and REVOKE commands | The GRANT and REVOKE commands have been updated with an ON ALL TABLES IN SCHEMA schema_name clause. This clause allows you to use a single command to change privileges for all tables in a schema. | October 16, 2014 |
| IF EXISTS clause for DROP SCHEMA, DROP TABLE, DROP USER, and DROP VIEW commands | The DROP SCHEMA, DROP TABLE, DROP USER, and DROP VIEW commands have been updated with an IF EXISTS clause. This clause causes the command to make no changes and return a message rather than terminating with an error if the specified object doesn’t exist. | October 16, 2014 |
| IF NOT EXISTS clause for CREATE SCHEMA and CREATE TABLE commands | The CREATE SCHEMA and CREATE TABLE commands have been updated with an IF NOT EXISTS clause. This clause causes the command to make no changes and return a message rather than terminating with an error if the specified object already exists. | October 16, 2014 |
| COPY support for UTF-16 encoding | The COPY command now supports loading from data files that use UTF-16 encoding as well as UTF-8 encoding. For more information, see ENCODING. | September 29, 2014 |
| New workload management tutorial | Tutorial: Configuring manual workload management (WLM) queues walks you through the process of configuring Workload Management (WLM) queues to improve query processing and resource allocation. | September 25, 2014 |
| AES 128-bit encryption | The COPY command now supports AES 128-bit encryption as well as AES 256-bit encryption when loading from data files encrypted using Amazon S3 client-side encryption. For more information, see Loading encrypted data files from Amazon S3. | September 29, 2014 |
| PG_LAST_UNLOAD_COUNT function | The PG_LAST_UNLOAD_COUNT function returns the number of rows that were processed in the most recent UNLOAD operation. For more information, see PG_LAST_UNLOAD_COUNT. | September 15, 2014 |
| New troubleshooting queries section | Query troubleshooting provides a quick reference for identifying and addressing some of the most common and most serious issues you are likely to encounter with Amazon Redshift queries. | July 7, 2014 |
| New loading data tutorial | Tutorial: Loading data from Amazon S3 walks you through the process of loading data into your Amazon Redshift database tables from data files in an Amazon S3 bucket, from beginning to end. | July 1, 2014 |
| PERCENTILE_CONT window function | PERCENTILE_CONT window function is an inverse distribution function that assumes a continuous distribution model. It takes a percentile value and a sort specification, and returns an interpolated value that would fall into the given percentile value with respect to the sort specification. | June 30, 2014 |
| PERCENTILE_DISC window function | PERCENTILE_DISC window function is an inverse distribution function that assumes a discrete distribution model. It takes a percentile value and a sort specification and returns an element from the set. | June 30, 2014 |
| GREATEST and LEAST functions | The GREATEST and LEAST functions functions return the largest or smallest value from a list of expressions. | June 30, 2014 |
| Cross-region COPY | The COPY command supports loading data from an Amazon S3 bucket or Amazon DynamoDB table that is located in a different region than the Amazon Redshift cluster. For more information, see REGION in the COPY command reference. | June 30, 2014 |
| Best Practices expanded | Amazon Redshift best practices has been expanded, reorganized, and moved to the top of the navigation hierarchy to make it more discoverable. | May 28, 2014 |
| UNLOAD to a single file | The UNLOAD command can optionally unload table data serially to a single file on Amazon S3 by adding the PARALLEL OFF option. If the size of the data is greater than the maximum file size of 6.2 GB, UNLOAD creates additional files. | May 6, 2014 |
| REGEXP functions | The REGEXP_COUNT, REGEXP_INSTR, and REGEXP_REPLACE functions manipulate strings based on regular expression pattern matching. | May 6, 2014 |
| COPY from Amazon EMR | The COPY command supports loading data directly from Amazon EMR clusters. For more information, see Loading data from Amazon EMR. |
April 18, 2014 |
| WLM concurrency limit increase | You can now configure workload management (WLM) to run up to 50 queries concurrently in user-defined query queues. This increase gives users more flexibility for managing system performance by modifying WLM configurations. For more information, see Implementing manual WLM |
April 18, 2014 |
| New configuration parameter to manage cursor size | The For more information, see DECLARE in this guide and Configure Maximum Size of a Cursor Result Set in the Amazon Redshift Management Guide. |
March 28, 2014 |
| COPY from JSON format | The COPY command supports loading data in JSON format from data files on Amazon S3 and from remote hosts using SSH. For more information, see COPY from JSON format usage notes. | March 25, 2014 |
| New system table STL_PLAN_INFO | The STL_PLAN_INFO table supplements the EXPLAIN command as another way to look at query plans. | March 25, 2014 |
| New function REGEXP_SUBSTR | The REGEXP_SUBSTR function returns the characters extracted from a string by searching for a regular expression pattern. | March 25, 2014 |
| New columns for STL_COMMIT_STATS | The STL_COMMIT_STATS table has two new columns:
numxids and oldestxid. |
March 6, 2014 |
| COPY from SSH support for gzip and lzop | The COPY command supports gzip and lzop compression when loading data through an SSH connection. | February 13, 2014 |
| New functions | The ROW_NUMBER window function returns the number of the current row. The STRTOL function converts a string expression of a number of the specified base to the equivalent integer value. PG_CANCEL_BACKEND and PG_TERMINATE_BACKEND enable users to cancel queries and session connections. The LAST_DAY function has been added for Oracle compatibility. | February 13, 2014 |
| New system table | The STL_COMMIT_STATS system table provides metrics related to commit performance, including the timing of the various stages of commit and the number of blocks committed. | February 13, 2014 |
| FETCH with single-node clusters | When using a cursor on a single-node cluster, the maximum number of rows that can be fetched using the FETCH command is 1000. FETCH FORWARD ALL is not supported for single-node clusters. | February 13, 2014 |
| DS_DIST_ALL_INNER redistribution strategy | DS_DIST_ALL_INNER in the Explain plan output indicates that the entire inner table was redistributed to a single slice because the outer table uses DISTSTYLE ALL. For more information, see Join type examples and Evaluating the query plan. | January 13, 2014 |
| New system tables for queries | Amazon Redshift has added new system tables that customers can use to evaluate query execution for tuning and troubleshooting. For more information, see SVL_COMPILE, STL_SCAN, STL_RETURN, STL_SAVE STL_ALERT_EVENT_LOG. | January 13, 2014 |
| Single-node cursors | Cursors are now supported for single-node clusters. A single-node cluster can have two cursors open at a time, with a maximum result set of 32 GB. On a single-node cluster, we recommend setting the ODBC Cache Size parameter to 1,000. For more information, see DECLARE. | December 13, 2013 |
| ALL distribution style | ALL distribution can dramatically shorter execution times for certain types of queries. When a table uses ALL distribution style, a copy of the table is distributed to every node. Because the table is effectively collocated with every other table, no redistribution is needed during query execution. ALL distribution is not appropriate for all tables because it increases storage requirements and load time. For more information, see Data distribution for query optimization. | November 11, 2013 |
| COPY from remote hosts | In addition to loading tables from data files on Amazon S3 and from Amazon DynamoDB tables, the COPY command can load text data from Amazon EMR clusters, Amazon EC2 instances, and other remote hosts by using SSH connections. Amazon Redshift uses multiple simultaneous SSH connections to read and load data in parallel. For more information, see Loading data from remote hosts. | November 11, 2013 |
| WLM memory percent used | You can balance workload by designating a specific percentage of memory for each queue in your workload management (WLM) configuration. For more information, see Implementing manual WLM. | November 11, 2013 |
| APPROXIMATE COUNT(DISTINCT) | Queries that use APPROXIMATE COUNT(DISTINCT) execute much faster, with a relative error of about 2%. The APPROXIMATE COUNT(DISTINCT) function uses a HyperLogLog algorithm. For more information, see the COUNT function. | November 11, 2013 |
| New SQL functions to retrieve recent query details | Four new SQL functions retrieve details about recent queries and COPY commands. The new functions make it easier to query the system log tables, and in many cases provide necessary details without needing to access the system tables. For more information, see PG_BACKEND_PID, PG_LAST_COPY_ID, PG_LAST_COPY_COUNT, PG_LAST_QUERY_ID. | November 1, 2013 |
| MANIFEST option for UNLOAD | The MANIFEST option for the UNLOAD command complements the MANIFEST option for the COPY command. Using the MANIFEST option with UNLOAD automatically creates a manifest file that explicitly lists the data files that were created on Amazon S3 by the unload operation. You can then use the same manifest file with a COPY command to load the data. For more information, see Unloading data to Amazon S3 and UNLOAD examples. | November 1, 2013 |
| MANIFEST option for COPY | You can use the MANIFEST option with the COPY command to explicitly list the data files that will be loaded from Amazon S3. | October 18, 2013 |
| System tables for troubleshooting queries | Added documentation for system tables that are used to troubleshoot queries. The STL views for logging section now contains documentation for the following system tables: STL_AGGR, STL_BCAST, STL_DIST, STL_DELETE, STL_HASH, STL_HASHJOIN, STL_INSERT, STL_LIMIT, STL_MERGE, STL_MERGEJOIN, STL_NESTLOOP, STL_PARSE, STL_PROJECT, STL_SCAN, STL_SORT, STL_UNIQUE, STL_WINDOW. | October 3, 2013 |
| CONVERT_TIMEZONE function | The CONVERT_TIMEZONE function converts a timestamp from one time zone to another, with the option to automatically adjust for daylight savings time. | October 3, 2013 |
| SPLIT_PART function | The SPLIT_PART function splits a string on the specified delimiter and returns the part at the specified position. | October 3, 2013 |
| STL_USERLOG system table | STL_USERLOG records details for changes that occur when a database user is created, altered, or deleted. | October 3, 2013 |
| LZO column encoding and LZOP file compression. | LZO column compression encoding combines a very high compression ratio with good performance. COPY from Amazon S3 supports loading from files compressed using LZOP compression. | September 19, 2013 |
| JSON, regular expressions, and cursors | Added support for parsing JSON strings, pattern matching using regular expressions, and using cursors to retrieve large data sets over an ODBC connection. For more information, see JSON functions, Pattern-matching conditions, and DECLARE. | September 10, 2013 |
| ACCEPTINVCHAR option for COPY | You can successfully load data that contains invalid UTF-8 characters by specifying the ACCEPTINVCHAR option with the COPY command. | August 29, 2013 |
| CSV option for COPY | The COPY command now supports loading from CSV formatted input files. | August 9, 2013 |
| CRC32 | The CRC32 function performs cyclic redundancy checks. | August 9, 2013 |
| WLM wildcards | Workload management (WLM) supports wildcards for adding user groups and query groups to queues. For more information, see Wildcards. | August 1, 2013 |
| WLM timeout | To limit the amount of time that queries in a given WLM queue are permitted to use, you can set the WLM timeout value for each queue. For more information, see WLM timeout. | August 1, 2013 |
| New COPY options 'auto' and 'epochsecs' | The COPY command performs automatic recognition of date and time formats. New time formats, 'epochsecs' and 'epochmillisecs' enable COPY to load data in epoch format. | July 25, 2013 |
| CONVERT_TIMEZONE function | The CONVERT_TIMEZONE function converts a timestamp from one timezone to another. | July 25, 2013 |
| FUNC_SHA1 function | The FUNC_SHA1 function converts a string using the SHA1 algorithm. | July 15, 2013 |
| max_execution_time | To limit the amount of time queries are permitted to use, you can set the max_execution_time parameter as part of the WLM configuration. For more information, see Modifying the WLM configuration. | July 22, 2013 |
| Four-byte UTF-8 characters | The VARCHAR data type now supports four-byte UTF-8 characters. Five-byte or longer UTF-8 characters are not supported. For more information, see Storage and ranges. | July 18, 2013 |
| SVL_QERROR | The SVL_QERROR system view has been deprecated. | July 12, 2013 |
| Revised Document History | The Document History page now shows the date the documentation was updated. | July 12, 2013 |
| STL_UNLOAD_LOG | STL_UNLOAD_LOG records the details for an unload operation. | July 5, 2013 |
| JDBC fetch size parameter | To avoid client-side out of memory errors when retrieving large data sets using JDBC, you can enable your client to fetch data in batches by setting the JDBC fetch size parameter. For more information, see Setting the JDBC fetch size parameter. | June 27, 2013 |
| UNLOAD encrypted files | UNLOAD now supports unloading table data to encrypted files on Amazon S3. | May 22, 2013 |
| Temporary credentials | COPY and UNLOAD now support the use of temporary credentials. | April 11, 2013 |
| Added clarifications | Clarified and expanded discussions of Designing Tables and Loading Data. | February 14, 2013 |
| Added best practices | Added Amazon Redshift best practices for designing tables and Amazon Redshift best practices for loading data. | February 14, 2013 |
| Clarified password constraints | Clarified password constraints for CREATE USER and ALTER USER, various minor revisions. | February 14, 2013 |
|
New guide |
This is the first release of the Amazon Redshift Developer Guide. | February 14, 2013 |
