Amazon data warehouse migration to Amazon Redshift

Informative video on moving an Oracle RAC and Hadoop based web log data mining solution to Amazon Redshift:

https://www.youtube.com/watch?v=P4KPPvEb_QI

Amazon Redshift - What is new

Here are some new things for Redshift:

• Increased concurrency to 50 slots —Run more simultaneous queries and balance large and small queries
• Copy from EMR — Easily load data in parallel from EMR clusters to Redshift
• Copy from JSON – Load in parallel directly from JSON files on S3.
• REGEX_SUBSTR() - Use regex expressions directly in SQL queries
• Resize Progress Indicator — Resize progress monitor in the console or using API to get the same data.
• Max Result Set Size — Choose cursor counts and result set sizes
• FedRAMP approval — FedRAMP 
• Support for ECDHE-RSA & ECDHE-ESDCSA SSL cipher suites — Perfect forward security for SSL connections.

AWS NYC Summit

Join me at the AWS New York Summit on July 10
https://aws.amazon.com/aws-summit-2014/new-york/

I will be assisting with this Big Data technical boot camp the day before:
https://secure1.regsvc.com/registration/index.aspx?TYPE=E&ID=125&LC=&LC=WS_NYSummit2014_BootcampDetailPage&PIN=&REF=&dbGUID=B00572E7-DEE7-45C0-9422-D21532101A75

Redshift SQL query tools

      Here are two tools that can be used with Amazon Redshift to issue query commands:      

1    1. SQL Workbench :

http://www.sql-workbench.net/downloads.html

The information required regarding your Redshift cluster are:

A. JDBC or ODBC connection string: example JBDC connection string looks like this: jdbc:postgresql://redshiftexamplecluster.cvhv40lk8tel.us-east-1.redshift.amazonaws.com:5439/redexampletl?tcpKeepAlive=true

B. User ID : master

C. password : password

More details here: http://docs.aws.amazon.com/redshift/latest/mgmt/connecting-using-workbench.html

2. Aginity : http://www.aginity.com                       A. Server (endpoint): example looks like this: lab.cfmvmxhkrtel.us-west-        2.redshift.amazonaws.com

B. User ID : master

C. password : password

D. Database: databasename

E. Port: 5439

The nice thing about Aginity is that there is no JDBC driver to install. With SQL Workbench, you will need to download and install 

More on where to find the  JDBC driver and configure with SQL Workbench

1. Location of driver: http://jdbc.postgresql.org/download/postgresql-8.4-703.jdbc4.jar

2.     Add the downloaded driver JAR to the driver to the PostGres Driver in SQL Workbench

AWS common command line interface

The AWS Common Command Line interface can be found here:

http://docs.aws.amazon.com/cli/latest/userguide/cli-chap-getting-started.html

Redshift cluster sizes

100 nodes maximum for each configuration.

Dense Storage (DW1) nodes are available in two sizes.   These are HDD backed instances.

A. The Extra Large has three HDDs with a total of 2TB of magnetic storage. Maximum of 200 TB of storage.

B. The Eight Extra Large has 24 HDDs with a total of 16TB of magnetic storage. Maximum of 1.6 Pedabyte of storage. 

Dense Compute (DW2) nodes are also available in two sizes.  These are SSD back instances.

A. The Large has 160GB of SSD storage per EC2 instance with a maximum is 1.6 TB

B The Eight Extra Large is sixteen times bigger (then the dense compute large) with 2.56TB of SSD storage on the EC2 instance for a maximum of 256TB of SSD storage.

Redshift sample schema from AWS web site

Below are the sample schemas that can be used when getting started with AWS Redshift. More information can be found here: http://docs.aws.amazon.com/redshift/latest/gsg/getting-started.html

create table users(

     userid integer not null distkey sortkey,

     username char(8),

     firstname varchar(30),

     lastname varchar(30),

     city varchar(30),

     state char(2),

     email varchar(100),

     phone char(14),

     likesports boolean,

     liketheatre boolean,

     likeconcerts boolean,

     likejazz boolean,

     likeclassical boolean,

     likeopera boolean,

     likerock boolean,

     likevegas boolean,

     likebroadway boolean,

     likemusicals boolean);

create table venue(

     venueid smallint not null distkey sortkey,

     venuename varchar(100),

     venuecity varchar(30),

     venuestate char(2),

     venueseats integer);

create table category(

     catid smallint not null distkey sortkey,

     catgroup varchar(10),

     catname varchar(10),

     catdesc varchar(50));

create table date(

     dateid smallint not null distkey sortkey,

     caldate date not null,

     day character(3) not null,

     week smallint not null,

     month character(5) not null,

     qtr character(5) not null,

     year smallint not null,

     holiday boolean default('N'));

create table event(

     eventid integer not null distkey,

     venueid smallint not null,

     catid smallint not null,

     dateid smallint not null sortkey,

     eventname varchar(200),

     starttime timestamp);

create table listing(

     listid integer not null distkey,

     sellerid integer not null,

     eventid integer not null,

     dateid smallint not null  sortkey,

     numtickets smallint not null,

     priceperticket decimal(8,2),

     totalprice decimal(8,2),

     listtime timestamp);

create table sales(

     salesid integer not null,

     listid integer not null distkey,

     sellerid integer not null,

     buyerid integer not null,

     eventid integer not null,

     dateid smallint not null sortkey,

     qtysold smallint not null,

     pricepaid decimal(8,2),

     commission decimal(8,2),

     saletime timestamp);

AWS Redshift visualization

Jaspersoft can be used with AWS Redshift.  Here are a couple of demos:

Here is a recording of the flow: http://youtu.be/-_JipmnU4ww

Here is a slightly longer recording (showing dashboard and interactive report): http://youtu.be/SEaj8bqGPCE

Have to have a datasource and domain created.  These demos already have them created.

Redshift : optimizing query performance with compression, distribution key and sort key

Encoding/compression

In order to determine the correct compression, first issue these commands to clean up dead space and analyze the data in the table:

vacuum orders;

analyze orders;

Then issue this command:

analyze compression orders;

Then create a table that matches the results from the analyze compression statement:

CREATE TABLE orders (

  o_orderkey int8 NOT NULL ENCODE MOSTLY32 PRIMARY KEY       ,

  o_custkey int8 NOT NULL ENCODE MOSTLY32 DISTKEY REFERENCES customer_v3(c_custkey),

  o_orderstatus char(1) NOT NULL ENCODE RUNLENGTH            ,

  o_totalprice numeric(12,2) NOT NULL ENCODE MOSTLY32        ,

  o_orderdate date NOT NULL ENCODE BYTEDICT SORTKEY          ,

  o_orderpriority char(15) NOT NULL ENCODE BYTEDICT          ,

  o_clerk char(15) NOT NULL ENCODE RAW                       ,

  o_shippriority int4 NOT NULL ENCODE RUNLENGTH              ,

  o_comment varchar(79) NOT NULL ENCODE TEXT255

);

Distributing data

Partition data using a distribution key. This allows data to be spread out on a cluster to maximize the parallelization potential of the queries. To help queries run fast, the distribution key should be a value that will be used in regularly joined tables. This allows Redshift to co-locate the data of these different entities, reducing IO and network exchanges.

Redshift also uses a specific sort column to know in advance what values of a column are in a given block, and to skip reading that entire block if the values it contains don’t fall into the range of a query. Using columns that are used in filters (i.e. where clauses) helps execution.

Compression depends directly on the data as it is stored on disk, and storage is modified by distribution and sort options.  Therefore, if you change sort or distribution key or create a new table that has the same data but different distribution and sort keys you will need to rerun the vacuum, analyze ad analyze compression statements.