Redshift from the command line

I recently found myself writing and referencing Saved Queries in the AWS Redshift console, and knew there must be an easier way to keep track of my common sql statements (which I mostly use for bespoke COPY jobs or checking the logs, since we use Mode for all of our BI).

Turns out there IS an easier way, and it’s called psql (Postgres’ terminal-based interactive tool)! Getting started with psql is super easy, and you’ll rejoice in the amount of AWS console clicking it cuts out.

Step 1: Install

Start by installing postgres.

$ brew install postgres

Step 2: Establish a Redshift connection

Next, connect to your Redshift cluster.

Since you will be using psql ALL the time, I recommend creating an alias in your ~/.bash_profile so you can easily establish your database connection with a single word. I use redshift as my alias. You can use the credentials you’ve defined in your .bash_profile:

alias redshift='psql "host=$REDSHIFT_HOST user=$REDSHIFT_USER dbname=$REDSHIFT_DB port=$REDSHIFT_PORT sslmode=verify-ca     sslrootcert=<path to your postgres root .crt file> password=$REDSHIFT_PASSWORD"'

Step 3: Add your dev IP address

You will need to ensure your cluster security group is set to receive inbound traffic from whatever IP address you’ll be using in development. If your cluster is in a custom VPC, you can do this from the command line using the CLI’s authorize-security-group-ingress. Otherwise, if you’re using the default VPC, you can add your IP address to the Inbound rules for the Security Group manually in the console

Step 4: Explore your warehouse

Now that you’re connected, type redshift on the command line, and try out these handy commands:

\dt — view your tables
\df — view your functions
\dg — list database roles
\dn — list schemas
\dy — list event triggers
\dp — show access privileges for tables, views, and sequences

Step 5: Take a query for a spin

$ SELECT * FROM your_schema.your_table LIMIT 10;

Step 6: Run a simple transaction

Open your favorite text editor and write a simple transaction (a series of sql — statements to be run as a single body of work, doing nothing if any one of them fails), for example:

BEGIN; 
INSERT INTO your_table VALUES (1), (2), (3), (4);
INSERT INTO your_table VALUES (5), (6), (7), (8);
INSERT INTO your_table VALUES (9), (10), (11), (12);
COMMIT;

Save this transaction and run in the psql repl with the \i command:

dev=# \i your_transaction.sql

Have fun and let me know what you discover!

Siobhán is the Data Engineering Lead at Landed. Landed is on a mission to help essential professionals build financial security near the communities they serve, and we invest alongside educators when they’re ready to buy homes in expensive markets. Sound awesome? Join us!