Amazon RedShift - install, data load, and query

bogotobogo.com site search:

Launch Cluster

Connecting to Redshift

We'll use a web-based PostgreSQL client (pgweb, ip-address = 34.216.21.87) to connect to Redshift.

Loading data

In this section, we'll create a Redshift table to store flight information.

Paste the following above the "Run Query":

CREATE TABLE flights (
  year           smallint,
  month          smallint,
  day            smallint,
  carrier        varchar(80) DISTKEY,
  origin         char(3),
  dest           char(3),
  aircraft_code  char(3),
  miles          int,
  departures     int,
  minutes        int,
  seats          int,
  passengers     int,
  freight_pounds int
);

Click "Run Query":

We can now load our data. The data has already been uploaded to S3 and all we have to do is COPY.

Paste the following above the "Run Query":

COPY flights
FROM 's3://us-west-2-aws-training/awsu-spl/spl17-redshift/static/data/flights-usa'
IAM_ROLE 'arn:aws:iam::113125727155:role/Redshift-Role'
GZIP
DELIMITER ','
REMOVEQUOTES
REGION 'us-west-2';

Click "Run Query". It may take about 3 minutes to load the data.

Running queries

Appendix - Redshift VPC cloudformation template

The Redshift cloudformation creates the following resources and output:

The "publicIp" is the address of the PostgreSQL server (linux) that's running by the CF stack:

AWSTemplateFormatVersion: 2010-09-09
Description: SPL-17 Working with Amazon Redshift

Parameters:
  AmazonLinuxAMIID:
    Type: AWS::SSM::Parameter::Value<AWS::EC2::Image::Id>
    Default: /aws/service/ami-amazon-linux-latest/amzn-ami-hvm-x86_64-gp2

Resources:
  VPC:
    Type: AWS::EC2::VPC
    Properties:
      CidrBlock: 10.0.0.0/16
      EnableDnsSupport: true
      EnableDnsHostnames: true
      Tags:
        - Key: Name
          Value: Lab VPC

  IGW:
    Type: AWS::EC2::InternetGateway
    Properties:
      Tags:
        - Key: Name
          Value: Lab IGW

  VPCtoIGWConnection:
    Type: AWS::EC2::VPCGatewayAttachment
    DependsOn:
      - IGW
      - VPC
    Properties:
      InternetGatewayId: !Ref IGW
      VpcId: !Ref VPC

  InternetRouteTable:
    Type: AWS::EC2::RouteTable
    DependsOn: VPC
    Properties:
      VpcId: !Ref VPC
      Tags:
        - Key: Name
          Value: Public Route Table

  InternetRoute:
    Type: AWS::EC2::Route
    DependsOn:
      - InternetRouteTable
      - IGW
    Properties:
      DestinationCidrBlock: 0.0.0.0/0
      GatewayId: !Ref IGW
      RouteTableId: !Ref InternetRouteTable

  PublicSubnet:
    Type: AWS::EC2::Subnet
    DependsOn: VPC
    Properties:
      VpcId: !Ref VPC
      MapPublicIpOnLaunch: true
      CidrBlock: 10.0.0.0/24
      AvailabilityZone: !Select
        - 0
        - !GetAZs
          Ref: AWS::Region
      Tags:
        - Key: Name
          Value: Public Subnet

  PublicRouteTable:
    Type: AWS::EC2::SubnetRouteTableAssociation
    DependsOn:
      - InternetRouteTable
      - PublicSubnet
    Properties:
      RouteTableId: !Ref InternetRouteTable
      SubnetId: !Ref PublicSubnet

  pgwebSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    DependsOn: VPC
    Properties:
      GroupName: pgweb Security Group
      GroupDescription: Enable access to pgweb
      VpcId: !Ref VPC
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: 80
          ToPort: 80
          CidrIp: 0.0.0.0/0
      Tags:
        - Key: Name
          Value: pgweb Security Group

  pgwebServer:
    Type: AWS::EC2::Instance
    DependsOn:
      - InternetRoute
      - PublicSubnet
      - pgwebSecurityGroup
    Properties:
      InstanceType: t2.micro
      SubnetId: !Ref PublicSubnet
      ImageId: !Ref AmazonLinuxAMIID
      SecurityGroupIds:
        - !Ref pgwebSecurityGroup
      UserData:
        Fn::Base64: !Sub |
          #!/bin/bash
          yum update -y
          yum install -y docker
          service docker start
          docker run -d -p 80:8081 awsu/pgweb
      Tags:
        - Key: Name
          Value: pgweb

  RedshiftRole:
    Type: AWS::IAM::Role
    Properties:
      RoleName: Redshift-Role
      AssumeRolePolicyDocument:
        Version: 2012-10-17
        Statement:
          - Effect: Allow
            Principal:
              Service:
                - redshift.amazonaws.com
            Action:
              - sts:AssumeRole
      Path: /
      Policies:
        - PolicyName: root
          PolicyDocument:
            Version: 2012-10-17
            Statement:
              - Effect: Allow
                Action: s3:*
                Resource: '*'

  RedshiftClusterSubnetGroup:
    Type: AWS::Redshift::ClusterSubnetGroup
    Properties:
      Description: LabClusterSubnet
      SubnetIds:
        - !Ref PublicSubnet

  RedshiftSG:
    Type: AWS::EC2::SecurityGroup
    DependsOn:
      - VPC
      - RedshiftRole
    Properties:
      GroupName: Redshift Security Group
      GroupDescription: Enable access to redshift
      VpcId: !Ref VPC
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: 5439
          ToPort: 5439
          CidrIp: 0.0.0.0/0
      Tags:
        - Key: Name
          Value: Redshift Security Group

Outputs:

  Region:
    Value: !Ref AWS::Region
    Description: Lab Region

  RedshiftRole:
    Value: !GetAtt
      - RedshiftRole
      - Arn
    Description: Redshift Role ARN

  pgweb:
    Value: !GetAtt
      - pgwebServer
      - PublicIp
    Description: pgwebIPAddress