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

1. Sign in to Robin and go to Manage > Integrations. Scroll down to the "API Tokens" section.

   

2. Select +Generate new token, shown in the screenshot above.

3. Name your token and select the "Basic_read permissions" box.

   

4. Select Generate Token.

5. Copy your token and save it.

   

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. Use the commands below.

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);

3. 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. The steps for both methods are below.

   A. Upload data to S3 in the AWS console

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

   B. Upload data to S3 with the AWS CLI

   Configure the AWS CLI on your system. Then run this command:

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

   Fill in your values. 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>/

   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.

   Before you run COPY

   To run a COPY command in Redshift, you need an IAM role with the correct permissions or access to your AWS keys. AWS strongly recommends role-based authentication for COPY commands when possible.

4. 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;
   
   

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

   select * from test_schema.desk_export;