Connecting To Your Warehouse Via Psql Or SQL Workbench/J

[intapiuser]

Connecting To Redshift

You can connect to Amazon Redshift using a JDBC or ODBC connection, any tool or program which connects via these methods, or programmatically (AWS provides examples for Java and .NET). AWS supports SSL connections for security.

A popular, terminal/command-line based interaction method uses psql - a postgres frontend which can be used to connect to Redshift. If you’d rather use a graphical frontend (such as SQL Server Management Studio or MySQL Workbench) then you may feel more at home using SQL Workbench/J. This article will go over connecting with both. If you prefer to connect through Python (psycopg2) or your language of choice, refer to the examples posted by AWS.

Connecting Via Psql

If you have a Mac or are on Linux, you may have psql installed already; type the command:

psql

followed by:

\c

into the terminal. You should see something like the following:

If you see the above, you're successfully connected to your local database! Above, you can see I've logged into the db 'aloksubbarao' under the username 'aloksubbarao'. 

If you received an error along the lines of: 

-bash: /usr/local/bin/psql: No such file or directory 

then you don't have psql/postgresql installed. On Mac, you can use Homebrew and run the command:

brew install postgresql

If you're on linux, try:

apt-get install postgresql

For windows, download and run the installer. 

If you get the error:

FATAL: database <name> does not exist

Then no database has been initialized. Run:

createdb <dbname>

and then run psql again. The <dbname> above is an optional parameter. Don't include the <> when actually passing the name. 

Next, navigate to your Cluster Info page and set your master password (if you haven’t already) and note the cluster details information. 

Enter that information into the terminal using the following psql command (the example here assumes a site name of your-site-name, master username periscoperoot, database name site_1234, port 5439)

psql -h your-site-name.periscopewarehouse.com -U periscoperoot -d site_1234 -p 5439

You will then be prompted for your master password, and upon entering it, should see something like the following: 

Congratulations! You’re in your Redshift cluster, and you can use psql commands to interact with your cluster. psql is very powerful, and there are many commands - here are some basic ones to get you started

\l to list all databases in the warehouse

\c <dbname> to connect to a database

\dt to list all tables in the current connected database

\x for expanded print (“pretty print”) mode

\q to quit the psql interface and disconnect

And of course you can run any SQL queries you’d like - don’t forget to throw a `;` at the end of the query or statement to have it execute when within psql! The power of Redshift is now at your fingertips, have fun!

Connecting Via SQL Workbench/J

Navigate over to SQL Workbench/J’s download page and download the version according to your operating system. Ensure that your workstation has the latest Java software.

Next, download the official Redshift JDBC Driver to a convenient location. Navigate to where you downloaded SQL Workbench and double click and run the program. 

Depending on your workstation’s security settings, you may encounter a prompt like the one shown below when trying to launch SQL Workbench for the first time. If you see this prompt, you may need to navigate to the “Security and Privacy” settings on your workstation and click “Open Anyway”. If this does not resolve the issue, you may need to see assistance from your workstation administrator.

Upon first launch, you should be presented with the connection window seen below. If not, click File-> Connect Window. Create a new profile upon your first connection, name your profile, then go to 'Manage Drivers'

Under Manage Drivers, click the folder icon and select the official Redshift driver from AWS. 

Then hit "OK". 

Next, navigate to your Cluster Info page and set your master password (if you haven’t already) and note the cluster details information. Now open up the Connection Window.

Select Amazon Redshift JDBC driver after having completed the prior steps. 

For the next fields we assume an the example site name of your-site-name, master username periscoperoot, database name site_1234, port 5439

URL: jdbc:redshift://your-site-name.periscopewarehouse.com:5439/site_1234

Username: periscoperoot

Password: <your password>

Ensure that 'Autocommit' beneath the password is selected! Don't forget this step, unless you want to manually COMMIT transactions within the UI. 

Click "OK" and now you're in the cluster! I recommend going into Tools -> Database Explorer and taking a look around your Redshift.

From here you can query your Warehouse data, create new database users with different permissions, create and insert new data, and many other possibilities.