> For the complete documentation index, see [llms.txt](https://ysfang82.gitbook.io/development-notes/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://ysfang82.gitbook.io/development-notes/aws-notes/service/storage/redshift.md).

# Redshift

**Introduction**

* A fully managed OLAP, Business Intelligence (BI) tool based on PostgreSQL, scales to PBs of data.
* Pay for instance you provisioned
  * It's worth when you have a sustained usage, otherwise use Athena for sporadic queries.
* Currently only available in 1 AZ in VPC
* Integrates with BI tools:
  * AWS Quicksight
  * Tableau

**Feature**

* Can create 1\~128 nodes, up to 160 GB per node.
* Data can be loaded from:
  * S3
  * DynamoDB
  * Kinesis Firehose
  * DMS
* Data Processing
  * Columnar Data Storage 
    * With block size 1 MB
  * Advanced Compression 
  * Massive parallel processing (MPP): automatically distribute data and check nodes' loading
* Node Type
  * Single Node
  * Multi-Node
    * Leader Node (not charged)
    * Compute Node
* Can enable "Redshift enhanced VPC routing" for path optimizing when executing a `COPY` / `UNLOAD` command.
* Encryption
  * In-transit with SSL
  * At rest, Redshift takes care of key management
    * Can manage your own keys through hardware security module (HSM)
    * Can manage keys with KMS
* Snapshot
  * point-in-time backups of a cluster, stored in S3
  * Snapshots are incremental
  * Can restore snapshots to a new AZ or a new cluster
  * **Can configure to copy snapshot to another region**
    * If KMS is used, set up a `snapshot copy grant` for a master key in the destination region
    * Enable cross-region snapshots in your Redshift cluster to copy snapshots of the cluster to another region
  * Can be created:
    * Automatically: every 8 hours, every 5 GB, or on a schedule. With retention.
    * Manually: retained until you delete it
* Workload management
  * Can create different workloads so that short, fast-running queries won't get stuck in queues.
* Cost efficiency
  * Storing the summary data in Redshift
  * Keeping detailed transaction data out of Redshift (Ex. store on S3)
  * Making use of Redshift Spectrum for drill-down queries that join tables from Redshift / S3.
* Redshift Spectrum
  * Query data that is already in S3 without loading it
  * Must have a Redshift cluster available to start the query
  * The query is the submitted to thousands of Redshift Spectrum nodes
  * If no need to "Join" different sources, use Athena.
* [Troubleshooting queries](https://docs.aws.amazon.com/redshift/latest/dg/queries-troubleshooting.html#queries-troubleshooting-query-hangs)