Redshift is a fast, managed, data warehouse solution that's part of AWS. Although it is traditional SQL and meant for BI (Business Intelligence), it is designed for scalability and can support many workloads typically reserved for Big Data tools. It is protocol-compatible with PostgreSQL and is available through JDBC/ODBC, opening it up to a huge range of existing SQL tools.
Redshift uses columnar storage, which means it physically stores the data for each column in contiguous blocks, as opposed to regular transactional databases that store data in rows. This allows it to perform common analysis queries very fast. All of this is transparent to the user, but it does mean that Redshift has particular performance characteristics that should be considered when loading data and querying.
Here are some Redshift tips learned from experience to help you along.
Redshift is optimized for aggregations on large data sets. It is not designed for small incremental changes. It is much faster to load data into Redshift in batches.
The ideal situation is that your data (from logs, or other sources) is already in S3. This allows you to use the COPY command that can read from S3 and load your data from multiple files in parallel.
If your source data is coming from a transactional system, it is tempting to insert data as it comes using INSERT statements. But this really going against the grain and you'll run into performance issues. You are better off going through the work of appending to S3 buckets and loading from there. I recommend using Kinesis Firehose, which allows you to push your data one at a time using an API, and it will manage the creation of S3 buckets and load the data for you using COPY commands. A good example from AWS for this here.
Unlike databases like SQL Server and Oracle, Redshift provides no built-in facilities for scheduling query jobs. It is an unfortunate omission as a lot of aggregation work can be done directly on Redshift using queries.
So, here are some options:
Redshift doesn't support traditional keys or indexes. It depends on the physical layout and distribution of the data to perform well. So you should select the SORT and PARTITION keys for each table very carefully.
The SORT key will determine the order in which the data is stored for each table. Follow these best practices from AWS to choose the SORT key.
The PARTITION key will determine how data is distributed to each compute node. You can read more about it here.
The Redshift query planner does not optimize a view query based on the data used in the where clause of the select. That means your query will scan the entire resultset from the view even if your query is only interested in a subset. Views are also not materialized and all data will be re-evaluated when you run a query on them.
It is better to create tables that are refreshed regularly (effectively like materialized views).
Redshift can perform a lot of the heavy-lifting when it comes to data cleanup and processing. Here are some recommendations:
It is a common practice to clean up the data before loading it into a warehouse. But with Redshift, I have found that it’s easier to load the raw data directly into a table as a first step. All cleanup operations can be performed within Redshift from that point on using queries.
Most DDL commands in Redshift work within a transaction and, in fact, might perform better when multiple operations are grouped together in a transaction.
This reminds me a bit of making transformations on immutable data structures on functional programming languages.
Here are a few example patterns you can use as a starting point.
Avoid DELETE statements when deleting large segments of your data. It is usually faster to recreate the table with the unwanted data filtered out:
BEGIN;
ALTER TABLE events RENAME TO events_copy;
CREATE TABLE events AS (
SELECT * events_copy WHERE event_type NOT IN (7,8)
);
DROP TABLE events_copy;
COMMIT;
-- assuming events_stage contains all the new events
BEGIN;
ALTER TABLE events RENAME TO events_copy;
CREATE TABLE events AS (
SELECT * FROM events_copy UNION
SELECT * FROM events_stage
);
DROP TABLE events_copy;
TRUNCATE TABLE events_stage;
COMMIT;
For performance, Redshift depends on the physical organization and sorting of the underlying data and on statistics metadata. As data is deleted/modified the physical organization of the data can change in ways that can lower performance. And the statistics can go out-of-date preventing the query planner from producing the most optimal execution plan. It is recommended that you regularly perform these commands:
Keep in mind that if you load your data in sort key order into a brand new table, you won't need to VACUUM. And the COPY command automatically updates the metadata statistics.
Being standard SQL, Redshift will work with pretty much any visualization tool out there. Here's a couple of recommendations if you want to start out simple and cheap:
Part of AWS and pretty inexpensive for internal use. It is very easy to use, and it has some very cool features. With some work, you can also embed visualizations from it into your own website. You'll just have to figure out its somewhat contrive session-based pricing.
Free and open source solution for basic data exploration. A bit more work to setup since it is not part of AWS.
Redshift is a powerful tool for data transformation and analysis. It is relatively inexpensive, very easy to use and can serve as an excellent starting point for BI. It is designed to be fast and scalable, but you have to consider its architecture and take some specific steps in order to get the best performance out of it.
For more information, checkout the getting started guide from AWS.
