Amazon Redshift via CLI

My preferred interaction with most tech tools is via command line and, whilst realising there are limitations to what’s possible (will we ever find a way to control music-making software from the terminal?), I strive constantly to minimise mouse dependence across the board. The good news for architects & builders is that AWS (and the other cloud providers) have made available comprehensive SDKs serving most languages & paradigms.

Here I’ll run through some of the commands available to run Amazon Redshift without logging into AWS Console… if you’d like to see even more, check Siobhán’s tutorial “Redshift from the command line”.

Let’s create a cluster!

aws redshift create-cluster \
--cluster-identifier oliTest2012 \
--node-type dc2.large \
--master-username oli123456 \
--master-user-password **** \
--number-of-nodes 2 \
> log.txt # in Unix bash this line saves Response (see below) to fs

Response (on Unix this is provided in a handy temp file running on less, making it searchable & pageable; consider saving by appending> log.txt):

{
  "Cluster": {
    "ClusterIdentifier": "olitest2012",
    "NodeType": "dc2.large",
    "ClusterStatus": "creating",
    "ClusterAvailabilityStatus": "Modifying",
    "MasterUsername": "oli123456",
    "DBName": "dev",
    "AutomatedSnapshotRetentionPeriod": 1,
    "ManualSnapshotRetentionPeriod": -1,
    "ClusterSecurityGroups": [],
    "VpcSecurityGroups": [
      {
        "VpcSecurityGroupId": "sg-b8c870de",
        "Status": "active"
      }
    ],
    "ClusterParameterGroups": [
      {
        "ParameterGroupName": "default.redshift-1.0",
        "ParameterApplyStatus": "in-sync"
      }
    ],
    "ClusterSubnetGroupName": "default",
    "VpcId": "vpc-14007b70",
    "PreferredMaintenanceWindow": "wed:04:30-wed:05:00",
    "PendingModifiedValues": {
    "MasterUserPassword": "****"
    },
    "ClusterVersion": "1.0",
    "AllowVersionUpgrade": true,
    "NumberOfNodes": 2,
    "PubliclyAccessible": true,
    "Encrypted": false,
    "Tags": [],
    "EnhancedVpcRouting": false,
    "IamRoles": [],
    "MaintenanceTrackName": "current",
    "DeferredMaintenanceWindows": [],
    "NextMaintenanceWindowStartTime": "2020-12-23T04:30:00+00:00"
  }
}

The same info provided in Response above, can be recalled at any time by running aws redshift describe-clusters or less log.txt if you saved the Response/log as recommended above.

Let’s query the data!

For MacOS users not yet set up with Brew and psql, those pre-requisites can be got by following the advice here.

Amazon Redshift does not provide an endpoint for querying data (this can be confirmed by running aws redshift help | grep uery). Instead the best practice advised on SO, and by AWS themselves, is to leverage the psql command (comes pre-configured on some operating systems; facile to install if not… if you know Docker and want to keep things containerised then this hackernoon tutorial works well, and includes some handy refreshers as to the available Docker option/flags). And here’s how to troubleshoot a scenario where it appears nothing is happening (if you wait long enough the terminal complains of not being able to make a successful connection): https://docs.aws.amazon.com/redshift/latest/mgmt/connecting-refusal-failure-issues.html