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

AutoScaling commands : new universal CLI

Here is an example of creating an auto scaling group using the new CLI:

aws autoscaling create-launch-configuration --launch-configuration-name NiravTestASconfig --image-id ami-35792c5c --security-groups NiravTestSecGrp --instance-type t1.micro --key-name niravkothariaws

aws autoscaling create-auto-scaling-group --auto-scaling-group-name NiravTestASGrp --launch-configuration-name NiravTestASconfig --min-size 2 --max-size 4 --default-cooldown 300 --load-balancer-names NiravTestELB --tags Key=Name,Value=CreatedBYAS --availability-zones us-east-1c --termination-policies NewestInstance

ADDARN=`aws autoscaling put-scaling-policy --auto-scaling-group-name NiravTestASGrp --policy-name NiravTestADDPolicy --scaling-adjustment +1 --adjustment-type ChangeInCapacity --cooldown 300 | grep -i "PolicyARN" | awk '{print $2}' | sed 's/"//g’`

DELARN=`aws autoscaling put-scaling-policy --auto-scaling-group-name NiravTestASGrp --policy-name NiravTestDELPolicy --scaling-adjustment -1 --adjustment-type ChangeInCapacity --cooldown 300 | grep -i "PolicyARN" | awk '{print $2}' | sed 's/"//g'`

echo ARN for scale-up policy $ADDARN

echo ARN for scale-down policy $DELARN

aws autoscaling put-notification-configuration --auto-scaling-group-name NiravTestASGrp --topic-arn arn:aws:sns:us-east-1:228152208343:niravtestsns --notification-types autoscaling:EC2_INSTANCE_LAUNCH autoscaling:EC2_INSTANCE_TERMINATE

sleep 10s

Auto Scaling Script

Auto Scaling with VPC 

This example uses the CLI version 1.0.61.2 (API 2011-01-01).  There is now a newer version of the CLI.

Overall notes:

1. Create an image (aka: gold image) that has a health check on it that consists of a simple static HTML page such as a ping.html or an index.html. 

2. The ELB (need an ELB before creating Auto Scaling group) hat has the instances running from your gold image AMI created in step 1. Could potentially (nothing is preventing you from doing) create a auto scale launch configuration (step 6 in process below) with an AMI that is different, with a different instance type as instance type is another parameter of your launch configuration, than the instance that will be part of your auto scaling group.  Both are possible but most will have the same AMI (gold image, so you know you have a health check) and the same instance type.

3. Before you create the ELB you should have instances running of the  gold image to make sure the ELB is working properly.  To provide true HA, make sure to put the instances in two AZs. (for example: us-west-2a, us-west-2b)

4. When creating elb that is using subnets, limitation on ELB is that only one subnet per AZ is allowed.  

5. When creating autoscaling group (as-create-auto-scaling-group)

 need to make sure to specific the VPC subnets and AZs.  The VPC subnets need to be in the AZs specified in the AZ parameter for command.

Steps:

Prework:

1. Identify the VPC : For example, VPC: vpc-9b120cf2

2. Add subnets : In this case, using two public subnets. I had to create the second one (subnet) which was private by default and I had to add an igw to make it public.

3. Subnets -

            A. Subnet: subnet-9f120cf6

            CIDR: 10.0.0.0/24   VPC: vpc-9b120cf2   Availability Zone: us-west-2a

            B. Subnet: subnet-8c130de5

            CIDR: 10.0.5.0/24   VPC: vpc-9b120cf2   Availability Zone: us-west-2b

4. Luanch two instances from AMI that has Apache installed with ping.html (or some other file) as a health check. One instance in subnet A and the other in subnet B. 

5. Create an ELB with the two instances across subnets.  ELB is healthy with two instances running in two AZs in two different public subnets. Limitation on ELB is that one subnet per AZ. 

6. Auto Scaling:

A. as-create-launch-config vpcautoscaling-as-lc --image-id ami-dcd344ec --instance-type t1.micro --key AutoScalingKey

B. as-create-auto-scaling-group vpcautoscaling-as-grp --launch-configuration vpcautoscaling-as-lc  --min-size 4 --max-size 12 --load-balancers VpcautoscalingAutoScalingELB --vpc-zone-identifier subnet-9f120cf6,subnet-8c130de5 --availability-zones us-west-2a,us-west-2b

C. as-describe-auto-scaling-groups --headers

output of command:

INSTANCE  INSTANCE-ID  AVAILABILITY-ZONE  STATE      STATUS   LAUNCH-CONFIG

INSTANCE  i-3af6e708   us-west-2b         InService  Healthy  vpcautoscaling-as-lc

INSTANCE  i-3cf6e70e   us-west-2a         InService  Healthy  vpcautoscaling-as-lc

INSTANCE  i-3ef6e70c   us-west-2a         InService  Healthy  vpcautoscaling-as-lc

INSTANCE  i-38f6e70a   us-west-2b         InService  Healthy  vpcautoscaling-as-lc

D. as-put-scaling-policy vpcautoscaling-scale-out-policy --auto-scaling-group vpcautoscaling-as-grp --adjustment=30 --type PercentChangeInCapacity

ARN: arn:aws:autoscaling:us-west-2:649163059618:scalingPolicy:69270ce4-3350-48f4-9d6f-71bc64225554:autoScalingGroupName/vpcautoscaling-as-grp:policyName/vpcautoscaling-scale-out-policy

E.  as-put-scaling-policy vpcautoscaling-scale-in-policy --auto-scaling-group vpcautoscaling-as-grp --adjustment=1 --type PercentChangeInCapacity

ARM - arn:aws:autoscaling:us-west-2:649163059618:scalingPolicy:d9a6780b-3319-4b00-98f1-e98dcfc68d82:autoScalingGroupName/vpcautoscaling-as-grp:policyName/vpcautoscaling-scale-in-policy

F. mon-put-metric-alarm --alarm-name AddCapacity --metric-name CPUUtilization --namespace "AWS/EC2" --statistic "Average" --evaluation-periods 6 --period 120 --threshold 80 --comparison-operator GreaterThanOrEqualToThreshold --dimensions "AutoScalingGroupName=vpcautoscaling-as-grp"  --alarm-actions arn:aws:autoscaling:us-west-2:649163059618:scalingPolicy:69270ce4-3350-48f4-9d6f-71bc64225554:autoScalingGroupName/vpcautoscaling-as-grp:policyName/vpcautoscaling-scale-out-policy

G. mon-put-metric-alarm --alarm-name RemoveCapacity --metric-name CPUUtilization --namespace "AWS/EC2" --statistic "Average" --evaluation-periods 2 --period 120 --threshold 40 --comparison-operator LessThanOrEqualToThreshold --dimensions "AutoScalingGroupName=vpcautoscaling-as-grp"  --alarm-actions arn:aws:autoscaling:us-west-2:649163059618:scalingPolicy:d9a6780b-3319-4b00-98f1-e98dcfc68d82:autoScalingGroupName/vpcautoscaling-as-grp:policyName/vpcautoscaling-scale-in-policy

H. as-describe-policies --auto-scaling-group vpcautoscaling-as-grp —headers