Load desk export data into Redshift

Load Robin desk export data into Redshift with the Robin Analytics API. The API brings Robin data into the tool you use, so you see your whole workplace and can plan your hybrid strategy with your own criteria. This article shows one example with Redshift.

Find your account ID and generate an API token in Robin

The export code needs your account ID and a token with the Read scope.

1. Sign in to Robin.
2. Go to Manage > Integrations.
3. At the top of the page, select Manage API Keys. The API tokens page opens.
4. Find your account ID in the example request at the top of the page. It is the number after /organizations/.
5. Select Generate new token.
6. Enter a name for your token.
7. Select the Read scope.
8. Select Generate Token.
9. Copy your token and save it. Robin shows the token only once.

Create an export request

The code below requests an export of 1 day of desk reservations. It then polls the API to see when the export is complete. When the export is complete, the code saves the data to disk. The file name is the from_date you give.

The code checks up to 6 times if the export is ready, with 10 seconds between each check. This keeps your requests under the rate limit.

If you do not have the requests package, install it: pip install requests

Robin recommends that you install packages in a virtual environment, but you do not have to.

import os
import requests
from datetime import datetime
from datetime import timedelta
import time

account_id =  os.getenv('ACCOUNT_ID')  # <-- add your account ID here, e.g. 00
read_token = os.getenv('READ_TOKEN')  # <-- add your token here

def make_export_creation_request(export_type, from_date):
    to_date = from_date + timedelta(1)
    print(f'Requesting an export from {from_date} to {to_date}.')

    url = f'https://api.robinpowered.com/v1.0/insights/exports/organizations/{account_id}/{export_type}'
    response_object = requests.post(url,
                             headers={
                                 'Authorization': f'Access-token {read_token}',
                                 'Content-type': 'application/json'
                             },
                             json={'from': from_date.isoformat(), 'to': to_date.isoformat()})

    response_object.raise_for_status()
    response = response_object.json()
    export_id = response['data']['export_id']
    print(f'The export request has been received and assigned the ID {export_id}.')

    return export_id


def retrieve_export(export_id, from_date):
    url = f'https://api.robinpowered.com/v1.0/insights/exports/{export_id}'

    iteration = 0
    while iteration < 6: # 720:
        print(f'Attempting to retrieve export {export_id}')
        response_object = requests.get(url,
                                       headers={
                                           'Authorization': f'Access-token {read_token}'
                                       })

        if response_object.status_code != 404:
            filename = f'./{from_date}.csv'
            response_object.raise_for_status()

            file = open(filename, 'wb')
            file.write(response_object.content)
            print(f'The CSV content was received and saved as {filename}.')
            break

        iteration += 1
        print(f'{iteration}. Export not ready. Will try again in 10 seconds.')
        time.sleep(10)


# Request that an event export be prepared.
from_date = datetime.strptime('2022-12-01T00:00:00+0400', '%Y-%m-%dT%H:%M:%S%z')
export_id = make_export_creation_request('desks', from_date)

# Get the export contents and save it to disk.
retrieve_export(export_id, from_date)

Load a desk export CSV into Redshift

To load the export API data into Redshift, first create a schema and a table for the data. Then upload the data to S3 and copy it into the table.

Create the schema and table

Run these commands in Redshift.

1. Create a schema:

   create schema test_schema;

2. Create a table:

   create table test_schema.desk_export (
       organization varchar,
       building varchar,
       floor varchar,
       desk_group varchar,
       desk_id integer,
       desk_name varchar,
       reservation_id varchar,
       series_id bigint,
       "type" varchar,
       "start" timestamp,
       "end" timestamp,
       hour_duration numeric,
       checked_in_at timestamp,
       canceled_at timestamp,
       cancellation_type varchar,
       creator_name varchar,
       creator_department varchar,
       creator_groups varchar,
       creator_email varchar,
       assignee_name varchar,
       assignee_department varchar,
       assignee_groups varchar,
       assignee_email varchar);

Upload the data to S3

You now have a schema and a table. To load data into them, you use the Redshift COPY command, which needs your data in S3. Upload your data to S3 through the AWS Console or the AWS CLI.

In the AWS console

1. In the S3 console, select the bucket for your data.
2. Select the UPLOAD button.
3. Select Add Files and select the file you generated from the Export API.
4. Select Upload to confirm the upload.

With the AWS CLI

1. Configure the AWS CLI on your system.

2. Run this command, and fill in your values:

   aws s3 cp /<path>/<to>/<local>/<file> s3://<bucket-name>/

The command copies a file from your local system to the S3 bucket you name. To put the file below the top level of the bucket, add subfolders to the S3 location:

aws s3 cp /<path>/<to>/<local>/<file> s3://<bucket-name>/<subdirectory>/<another-subdirectory>/

Copy the data into your table

You now have a schema and a table in Redshift for the data, and the data is in S3. Next, COPY the data into your table.

1. In Redshift, run the command for your method and fill in your values.

   • With a role:

     copy test_schema.desk_export
     from 's3://<bucket-name>/<file-name>'
     iam_role 'arn:aws:iam::<account-number>:role/<role-name>'
     format as csv
     ignoreheader 1;

   • With AWS keys:

     copy test_schema.desk_export
     from 's3://<bucket-name>/<file-name>'
     access_key_id '<access-key-id>'
     secret_access_key '<secret-access-key>'
     session_token '<token>'
     format as csv
     ignoreheader 1;

2. Check that the test_schema.desk_export table has data:

   select * from test_schema.desk_export;