Database Specialty - Redshift Architecture

Question 1

Redshift Architecture

Answer 1

• Massively parallel columnar database, runs within a VPC
• Single leader node and multiple compute nodes
• You can connect to Redshift using any application supporting JDBC or ODBC driver
  for PostgreSQL
• Clients query the leader node using SQL endpoint
• A job is distributed across compute nodes.
• Compute nodes partition the job into slices.
• Leader node then aggregates the results and returns them to the client

Question 2

Redshift node types

Answer 2

• Dense compute nodes (DC2)
• For compute-intensive DW workloads with local SSD storage
• Dense storage nodes (DS2)
• For large DWs, uses hard disk drives (HDDs)
• RA3 nodes with managed storage
• For large DWs, uses large local SSDs
• Recommended over DS2
• Automatically offloads data to S3 if node grows beyond its size
• Compute and managed storage is billed independently

Question 3

Loading data into Redshift

Answer 3

• Typically, data from OLTP systems is loaded into Redshift for analytics and BI purposes
• Data from OLTP systems can be loaded into S3 and data from S3 can then be loaded into Redshift
• Data from Kinesis Firehose can also be loaded in the same way
• COPY command
• Loads data from files stored in S3 into Redshift
• Data is stored locally in the Redshift cluster (persistent storage = cost)
• DynamoDB table data and EMR data can also be loaded using COPY command

Question 4

Loading data from S3 with COPY command
copy users from ‘s3://my_bucket/tickit/allusers_pipe.txt’
credentials ‘aws_iam_role=arn:aws:iam::0123456789:role/MyRedshiftRole’
delimiter ‘|’ region ‘us-west-2’;

Answer 4

• Create an IAM Role
• Create your Redshift cluster
• Attach the IAM role to the cluster
• The cluster can then temporarily assume the IAM role on your behalf
• Load data from S3 using COPY command

Question 5

More ways to load data into Redshift

Answer 5

• Use AWS Glue – fully managed ETL service
• ETL = Extract, Transform, and Load
• Use ETL tools from APN partners
• Use Data Pipeline
• For migration from on-premise, use:
• AWS Import/Export service (AWS Snowball)
• AWS Direct Connect (private connection between your datacenter and AWS)

Question 6

Querying external data with Redshift

Answer 6

• Two ways
• Redshift Spectrum
• Redshift Federated Query

Question 7

Redshift Spectrum

Answer 7

• Query exabytes of data from S3 without loading it into Redshift
• Must have a Redshift cluster available to start the query
• The query is then submitted to thousands of Redshift Spectrum nodes
• External table structure/schemas can be created in external data catalog like Athena / Glue / Apache Hive metastore (EMR)
• These external tables are read-only (insert / update / delete operations not possible)
• Redshift cluster and S3 bucket must be in the same region

Question 8

Redshift Federated Query

Answer 8

• Query and analyze data across different DBs, DWs, and data lakes
• Currently works with Redshift, PostgreSQL on RDS, Aurora PostgreSQL
  and S3

Question 9

Star Schema

Answer 9

• Typical way of organizing data in a data warehouse
  
  • Two types of tables – fact tables and dimension tables
  • A star pattern consists of a fact table and multiple dimension tables
  • Fact table has foreign key relationships with multiple dimension tables
  • Dimension tables are generally small (fewer records, but often with many fields)
• Recommended to store:
  
  • smaller dimension tables as local Redshift tables
  • larger fact tables as external Spectrum tables

Question 10

Redshift Key Constraints

Answer 10

• Redshift does not support indexes (clustered or non-clustered)
• Has a concept of sort keys
• data is stored on disk in a sorted order by sort key
• Primary Key, Unique keys, and Foreign key constraints are not enforce (are informational only)
• Query optimizer uses these constraints to generate more efficient query plans
• You can enforce PK/FK relationships through your application

Question 11

Redshift Table Design

Answer 11

• Key factors for efficient table design
  
  • Data distribution – how data is distributed across nodes
  • Sort strategies – how data is sorted in the tables
  • Compression – to reduce storage and I/O needs

Question 12

Redshift Sort Styles

Answer 12

• Single-column sort key (e.g. Dept)
• Compound sort key
  
  • more that one column as sort key
  • e.g. Dept + Location
  • Hierarchical (order of the column in the sort key is important)
• Interleaved sort key
  
  • gives equal weight to each column (or subset of columns) in the sort key
  • In effect, you can have multiple sort key combinations
  • e.g. Dept + Location, Location + Dep
• Must be defined at the table creation time

Question 13

VACUUM operation in the tables

Answer 13

• As you delete row from or add more rows to a sorted table containing data, performance might deteriorate over time
• VACUUM operation re-sorts rows in one or all DB tables
• And reclaims space from table rows marked for deletion
• Redshift automatically sorts data and runs VACUUM DELETE in the background
• Need not run manually, but you can if required

Question 14

Redshift Compression

Answer 14

• Column-level operation to reduce size of stored data
• Reduces disk I/O and improves query performance
• Compression type = encoding
• Each column can be separately compressed with different encodings (manually / automatically)
• COPY command applies compression by default
• RAW = No compression (default for sort keys)
• LZO = Very high compression with good performance (default encoding)
• Encoding cannot be changed after table creation

Question 15

Redshift Workload Management (WLM)

Answer 15

• Helps you prioritize workloads
• Can prevent long-running queries from impacting short-running ones
• Two modes – Automatic WLM and Manual WLM
• Automatic WLM supports queue priorities
• SQA (Short query acceleration)
  
  • prioritize selected short running queries w/o creating dedicated queue

Question 16

Modifying the WLM configuration

Answer 16

• Switching between Manual and Auto WLM modes requires cluster reboot
• WLM uses parameter groups for its config
• WLM configuration properties are either dynamic or static
• Dynamic property changes do not require cluster reboot
• Static property changes require cluster reboot