Load desk export data into Snowflake

Load Robin desk export data into Snowflake 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 Snowflake.

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 Snowflake

To load the Robin desk export (Analytics > Exports > Desks) into Snowflake, first create a database, a schema and a table for the CSV data. Use the commands below.

1. Sign in to Snowflake and create a database:

   create database if not exists robin_test;

2. Create a schema in the new database:

   create schema if not exists robin_test.test_schema;

3. Create the table for the desk export CSV:

   create or replace table robin_test.test_schema.desk_export (
       organization string,
       building string,
       floor string,
       desk_group string,
       desk_id number,
       desk_name string,
       reservation_id string,
       series_id number,
       type string,
       "START" timestamp_ntz,
       end timestamp_ntz,
       hour_duration number,
       checked_in_at timestamp_ntz,
       canceled_at timestamp_ntz,
       cancellation_type string,
       creator_name string,
       creator_department string,
       creator_groups string,
       creator_email string,
       assignee_name string,
       assignee_department string,
       assignee_groups string,
       assignee_email string);

4. Set the file format, so Snowflake knows how to read the uploaded desk export file. The example below defines a CSV format named "Robincsv", with the settings that load the API data correctly.

   create or replace file format robincsv
     type = 'CSV'
     field_delimiter = ','
     record_delimiter = '\n'
     FIELD_OPTIONALLY_ENCLOSED_BY = '0x22'
     skip_header = 1;

5. Define a stage in Snowflake. A stage is an area that holds the data in Snowflake before Snowflake loads it into a table. The example below defines a stage named "robin_csv_stage". It tells Snowflake that the files you upload to this stage match the CSV file format above.

   create or replace stage robin_csv_stage
     file_format = robincsv;

6. You now have a local data file, a file format, and a stage that uses that file format. To load the data into Snowflake, install the SnowSQL CLI.

7. Set up the SnowSQL CLI to sign in to your Snowflake account. Then run the command below in the CLI to load your desk export data to your stage:

   put file:///<path>/<to>/<file>/robin_desk_export.csv @robin_csv_stage auto_compress=true;

   The CLI loads your data to your stage and compresses it to save storage.

8. Load the data from your stage to your table in the Snowflake Web console:

   copy into desk_export
     from @robin_csv_stage/robin_desk_export.csv.gz
     file_format = (format_name = robincsv)
     on_error = 'skip_file';

Query your table to see the data you exported from the Robin Analytics API:

select * from robin_test.test_schema.desk_export;