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 CLI filtering

The output from an AWS CLI command can be displayed in JSON, text or a table. More details here:
http://docs.aws.amazon.com/cli/latest/userguide/controlling-output.html

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);

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.

Creating PIOPS volume for Oracle Database

When running your Oracle Database on AWS EC2, you may find that you need additional IOPS for your database and which to add an EBS volume to your Oracle instances for some of your highly accessed tables that require high IO.  Here are the steps to create a new PIOPS volume and attached it your EC2 instance.

1. aws ec2 stop-instances --instance-id i-NewDBServer
2. aws ec2 create-volume --availability-zone az-asdf-1a --size 200 --volume-type io1 --iops 2000

3. aws ec2 attach-volume --volume-id vol-FastDrive --instance-id i-NewDBServer - -device /dev/xvdb

Get the OS in order for your EC2 instance to recognize your volume by running the following commands:

1. sudo mkdir /mnt/piops1

2. sudo mkfs.ext3 /dev/xvdb

3. sudo mount /dev/xvdb /mnt/piops1

4. df -h (to see the newly mounted piops volume)

You would then create an Oracle tablespace on this new volume and move your tables to this new tablespace.