Connect to Cirium Sky Warehouse Using IP Allow List

Pre-requisites

Before you start, you will need the following information:

• The Redshift URL: alto.sky.cirium.com

• Port: You may connect via either port 5439 or 443

• Username: (Provided as part of your account set up)

• Temporary password: (Provided as part of your account set up)

You can find more information on how to protect your user credentials here.

If you need additional user accounts for other members of your organisation, please contact our support team via the Cirium Help Desk or by sending an email to support-ciriumsky@cirium.com.

Setting up the connection

The following steps walk through the process of making a connection to the Cirium Sky database using Beekeeper Studio and running an example query. These steps assume this is the first time you have made a connection. While we use Beekeeper Studio as an example, the steps are similar for other SQL clients.

1. Download the latest Amazon Redshift CA Certificate Bundle from https://s3.amazonaws.com/redshift-downloads/redshift-ca-bundle.crt. This file is used to let your SQL client verify that it is communicating with a genuine Redshift instance.

2. Open Beekeeper Studio. You will be asked which database type you are connecting to - choose Amazon Redshift from the list:

3. Enter the connection information. You’ll need to fill in the following fields:

• Host - use the Redshift server address you have been provided which should be alto.sky.cirium.com

• Port - either 5439 or 443.

• Enable SSL - ensure this is turned on. You won’t able to connect to Cirium Sky without SSL.

• Uncheck Reject Unauthorized - ensure that the "Reject Unauthorized" checkbox is unchecked.

• CA Cert - choose the CA certificate bundle file you downloaded in Step 1.

• User - use the username you have been provided.

• Password - use the temporary password you have been provided.

• Default Database - unless specified otherwise in your account set up information, set this to ciriumsky.

4. Click Test, and you should see a notification appear that the connection was successful. If an error is shown instead, check that all the details specified above are completed correctly, and that your IP address has been granted access to Cirium Sky.

5. Click Connect. You should see the left hand panel is populated with the schema of the Cirium Sky data warehouse, and the right panel allows you to run SQL queries.

6. The first task you should do is to change your password from the temporary password to a new one. In the query window on the right, enter

ALTER USER "<your_username>" PASSWORD '<your_new_password>';

substituting your username and new password as necessary, and click Run.

Note: Your chosen password must be between 8 to 64 characters in length and contain at least one uppercase letter, one lowercase letter, and one number. We recommend randomly generating the password with a password manager. See Protecting your Cirium Sky credentials for further advice.

7. Now you are ready to begin writing queries. You can expand the public schema on the left hand panel in order to see the tables available within the Cirium Sky data warehouse and the columns that they contain.

Full documentation on the meaning and relationships of each column is available within the Data Dictionary, and information on the SQL constructs that Redshift supports is available within the Redshift documentation.

The example query below shows the usage of two Cirium Sky datasets to find the number of flights departing Washington Dulles Airport for the previous day, broken down by the name of the carrier.

Example Query

SELECT 
  carriers.name, 
  count(*) 
FROM 
  flights 
JOIN 
  carriers ON carriers.carrier_id = operating_carrier_id
WHERE
  departure_airport_id = 'IAD'
  AND published_departure >= CURRENT_DATE - interval '1 day'
  AND published_departure < CURRENT_DATE
GROUP BY
  carriers.name
ORDER BY
  count(*) DESC;

If you have any feedback or ideas about Cirium Sky Data Warehouse then we would love to hear from you via the Cirium Help Desk.