Versions Compared

Version Old Version 11 New Version 12
Changes made by

Matthew Girard

Matthew Girard

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

In Denodo, you can query files in Box by creating connections to individual files. In general, you will need to create one connection and one base view for each file you want to query. You can either execute the following VQL in the VQL shell (faster), or follow the steps below.

VQL

You’ll want to update the VQL portions that reference the URL to the Box file to reference the correct file.

Code Block
languagesql
# Generated with Denodo Platform 8.0 update 20220815 hotfix 20230120.

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 box_test_csv CASCADE;

CREATE DATASOURCE DF box_herd_2021
    FOLDER = '/01-base_layer/01-connections/box'
    ROUTE HTTP 'http.CommonsHttpClientConnection,120000' GET 'https://api.box.com/2.0/files/1149084331943/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
    DESCRIPTION = 'Sets up a connection to Box so a base view can be created to view the following file:

https://tufts.app.box.com/file/1149084331943?s=a9e0ijra1tsjfbv8eu7ezq8dn45oiri6';

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.