Overview

Connecting Denodo to a Box file is a two step process. The first step involves creating an app that will give Denodo access to Box via OAuth and that people can share files with. This only needs to be done once and can be used for all files. This is outlined in the section below. The second step involves creating a connection to a Box file in Denodo using the credentials of the app.

Creating a Box App

This outlines how to create a Box App for Denodo. Since an app has already been created for Denodo, this guide is just for reference. The link to the app and it’s assigned email address are below.

1. Go to the Box developer console: https://tufts.app.box.com/developers

2. Click Create a New App

   

3. Select Custom App

   

4. Select Server Authentication and give the app the name Denodo

   

5. Request authorization of the app in Box and create a TechConnect ticket requesting authorization of the Box app.

   

6. Once authorized, note down the email address/Service Account ID of the app, accessible from the General Settings tab. Use this email address to grant the app access to files.

Connecting Denodo to Box

In Denodo, you can query files in Box by creating connections to individual files. You will need to create one connection and one base view for each file you want to query. To create a connection, you can either execute the VQL in the VQL section in the VQL shell (faster), or follow the steps outlined in Step-by-step.

In order to connect a file from Box to Denodo, files must be in the data strategy drop folder. You can accomplish this by either uploading the file to a folder owned by the Denodo Box app (recommended, see Data Strategy Drop Folder) To share a file that is owned by a different service account with the Denodo Box App, in the Share dialog, use the email address provided under Creating a Box App and select Invite as Viewer (example below).

VQL

Copy and paste this VQL into the VQL shell and replace the following:

1. Make sure you have the correct database selected in the top left corner of the VQL shell.

2. {CONNECTION_NAME} with the desired name for the Box file connection

3. {BOX_FILE_ID} with the ID of the file in Box (see step 3 in Step-by-Step).

After running the below VQL, navigate to, and double click on the new connection, then click Create Base View.

CSV

CREATE OR REPLACE FOLDER '/01-base_layer' ;

CREATE OR REPLACE FOLDER '/01-base_layer/01-connections' ;

CREATE OR REPLACE FOLDER '/01-base_layer/01-connections/Box' ;

DROP DATASOURCE DF IF EXISTS {CONNECTION_NAME} CASCADE;

CREATE DATASOURCE DF {CONNECTION_NAME}
    FOLDER = '/01-base_layer/01-connections/box'
    ROUTE HTTP 'http.CommonsHttpClientConnection,120000' GET 'https://api.box.com/2.0/files/{BOX_FILE_ID}/content'
    CHECKCERTIFICATES
    AUTHENTICATION OAUTH20 ( 
      ACCESSTOKEN = 'QdwTWOBaEREKeA5X30NqMRRwjEGY094b5uJp8q/eD38cycmYXTvY43eg8BvtHHm/X8tbbR9GDexgux5dm9STG8VFbDxhgKJMNoEl4jabdPodBkn5vcwrA9kcaDUxgwZh' ENCRYPTED
      REQUESTSIGNINGMETHOD = HEADER
      AUTHENTICATION_GRANT = CLIENT_CREDENTIALS_GRANT
      TOKENENDPOINTURL = 'https://api.box.com/oauth2/token'
      EXTRA_PARAMETERS_OF_REFRESH_TOKEN_REQUEST (
          'box_subject_type' = 'enterprise', 
          'box_subject_id' = '192935'
      )
      CLIENTIDENTIFIER = '0jngdws659fyhpkb80f8fbt26q7olovd'
      CLIENTSECRET = '4fMN87hoo/bjczYeTKGDn+d4kxRCxmjLiy5MQcuYpFlNiT1WMX1BP/m7Ggj5LeUROHpwKOC3872PkcfQLuOK40zjLG5MQ07G7Jro0gK+PG4QpUidG6TmsK86WHylZbf/ZvRGBc0pGr9SzpAccK2MiEsXenWuY5Uju6HPPYRF7IA=' ENCRYPTED
      ACCESSTOKENEXPIRESIN = 4155
    )
    PROXY OFF
    COLUMNDELIMITER = ','
    ENDOFLINEDELIMITER = '\n'
    HEADER = TRUE;

JSON

# Generated with Denodo Platform 8.0 20230914.

CREATE OR REPLACE FOLDER '/01-base_layer' ;

CREATE OR REPLACE FOLDER '/01-base_layer/01-connections' ;

CREATE OR REPLACE FOLDER '/01-base_layer/01-connections/Box' ;

DROP DATASOURCE JSON IF EXISTS {CONNECTION_NAME} CASCADE;

CREATE DATASOURCE JSON {CONNECTION_NAME}
    FOLDER = '/01-base_layer/01-connections/box'
    ROUTE HTTP 'http.CommonsHttpClientConnection,120000' GET 'https://api.box.com/2.0/files/{BOX_FILE_ID}/content'
    AUTHENTICATION OAUTH20 ( 
      ACCESSTOKEN = 'lJ9DtUEFbvcyXTYkOmnLGRd5LHac+ala0FuWq0d10L8650cm2zzHNWqrBnYmWBo0HEdQL0k8FQ2otLs8Ju6hDl8IdtLn2wvB8YFrgnV2XdyDlhmWYPYIIsQ/BzjSJprpeAYMEw6KP/KaJRJsWvMj6SPH/UmmLioKyxm9vs3oxps=' ENCRYPTED
      REQUESTSIGNINGMETHOD = HEADER
      AUTHENTICATION_GRANT = CLIENT_CREDENTIALS_GRANT
      TOKENENDPOINTURL = 'https://api.box.com/oauth2/token'
      EXTRA_PARAMETERS_OF_REFRESH_TOKEN_REQUEST (
          'box_subject_type' = 'enterprise', 
          'box_subject_id' = '192935'
      )
      CLIENTIDENTIFIER = '0jngdws659fyhpkb80f8fbt26q7olovd'
      CLIENTSECRET = 'Lda6Qv/p2BtkG0TXv51kqATg12ZLui50GRk05dwNgasWv1VT75zK6MGdadb59hQQv9ABsq18WEBo+K1Fkl6bk2RMlQAy0G64HF0K/k6OIF5eT0azGD7P9ZGnbWUk25c614W1+5vbaeaVdwcKmIsGZtRetyg6dFnfg+SeysC3Jig=' ENCRYPTED
    )
    PROXY OFF;

Step-by-step

1. Create a new Data Source file connection (Delimited file, JSON, XML, Excel)
   

   

   Alternatively, you can use the following VQL:
   

2. Select ‘HTTP Client’ as the Data Route
   

   

3. Fill in the Base URL using this format, https://api.box.com/2.0/files/{FILE_ID}/content, replacing {FILE_ID} with the ID of the file in box.
   https://developer.box.com/reference/get-files-id-content/
   

   

   

4. Under the Authentication section, fill in the fields as follows. The Token endpoint URL is https://api.box.com/oauth2/token. You’ll need to click on Extra parameters of the refresh token requests and fill in the additional parameters that Box requires (screenshot , as outlined in the linked documentation below
   https://developer.box.com/guides/authentication/client-credentials/

   

   
   

   

   
   

   

   
   

   

5. Fill out the bottom section to match your file configuration. The screenshot below shows a common configuration for a CSV
   

   

6. Test the connection, confirm it works, then click Save.

7. Click Create Base View to create a base view for the file.

Interacting with the Denodo Service Account

File can be uploaded to the Denodo Box service account to prevent dependency on the Box accounts of individuals. While you can’t log in to Box and interact with the service account using the traditional UI, you can perform all essential tasks via the API. The sections below outline how to perform essential Box tasks.

The API can be accessed through a number of different methods including cURL, Postman, and Python, with Postman being the easiest method for most.

Files uploaded to the Denodo service account are, by design, accessible by the Denodo service account and do not need to go through the share process outlined above.

Data Strategy Drop Folder

A general Data Strategy Drop Folder has been created to that members of the Data Strategy team can request access to. Sub folders can be created in here and shared with other teams within the university for uploading of shared data files.

Importing API Collection into Postman

An API collection for Postman has been created and can be downloaded here.

To import into Postman, click Import, then find and select the Box.postman_collection.json file.

Once imported, you’ll need to obtain the client ID and client secret for the service account and add them to the collection variables. You can obtain these from the app configuration page here (if you’ve been added as a collaborator): https://tufts.app.box.com/developers/console/app/1958391/configuration, or by sending a request to denodo-devs-request@elist.tufts.edu.

Once you have the client ID and secret, set them in the collection variables page and you’re ready to start making API requests.

Obtaining an Access Token

https://developer.box.com/reference/post-oauth2-token/

Access tokens are temporary and typically only valid for an hour.

Creating a Folder

https://developer.box.com/reference/post-folders/

Once a folder is created, you can add people as collaborators so that they interact with the folder using the Box UI, as outlined in Adding Collaborators.

To make uploading files to a folder easy, it’s recommended that you enable the folder upload email and make the access open.

When people upload files to the folder, they should receive a response from Box like the one below indicating that the upload was successful.

If a file being uploaded via email already exists, rather than add it as a new version of that file, it will create a new file with a randomly generated string as part of of the filename.

Listing Folder Items

https://developer.box.com/reference/get-folders-id-items/

Get Folder Information

https://developer.box.com/reference/get-folders-id/

Shared Links

https://developer.box.com/reference/put-files-id--add-shared-link/

https://developer.box.com/reference/put-folders-id--add-shared-link/

Adding Collaborators

https://developer.box.com/reference/post-collaborations/

Deleting a File

https://developer.box.com/reference/delete-files-id/