Overview

In Denodo, you can query files in Box by creating connections to individual files.

Creating connections to files in Box involves the following steps:

Add the file to the Data Strategy drop folder. Ask a Data Strategy team member to create a sub folder for your team if one does not already exist.
Create a connection and base view for the file

Creating a Connection and Base View

Follow the following steps:

Open the VQL Shell
Make sure you have the correct virtual database (VDB) selected in the top left corner of the VQL shell.
Follow the sections below based on the type of file you want to connect to.

CSV

Copy the VQL below into the shell
Replace {CONNECTION_NAME} with the desired name for the Box file connection
Replace {BOX_FILE_ID} with the ID of the file in Box (see step 3 in Step-by-Step).
Navigate to, and double click on the new connection, then click Create Base View.

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 = 'o35eKDYzCuM2buo8pazGwKwpVIw3MLDwlnqOyXrLr3SsiPG6g4TV5IpxOt4WnRGpTwIClXzb34tBhaqpGeV/RNR5RBSiYbVugR3f5XFL/SZzDyQXUYDobbUKnCWmYbm0' 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 = 'h4At1ylcdKSNQwrPno80ds7qHUefLv1OXOJUzjBRbWN8lOgBuMpjFzsYVfKwzuPqj2V1iI1VmAopMo18r3ZNyL7/Q8q3lEdj1Nj9qz9grSZN2ad2+OyW73xCXEOneEOc9WlZYfwS4WMsxqV+Rko5k5i4RTnoY9L4ohjMmlvXSUs=' ENCRYPTED
      ACCESSTOKENEXPIRESIN = 4155
    )
    PROXY OFF
    COLUMNDELIMITER = ','
    ENDOFLINEDELIMITER = '\n'
    HEADER = TRUE;

JSON

Copy the VQL below into the shell
Replace {CONNECTION_NAME} with the desired name for the Box file connection
Replace {BOX_FILE_ID} with the ID of the file in Box (see step 3 in Step-by-Step).
Navigate to, and double click on the new connection, then click Create Base View.
Click OK in the Configure JSON View Dialog
In the next screen, for Tuple Root, put /JSONFile/JSONArray and click Create Base View.

# 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 = 'Casmq3IRPhmhtynCi+xaldm11WSqZxwPoL1iBmii3qKtHIcofjdOrTNmsHEopSFL5Sv3wJA36xquOjkb0eGT6zIMz9s9R2/5KCdBDO4Gpw4M0QC27DrP+2y/gHkbWQq0g5h5fPZ7IfwzhfPWgnW9LSUDw7la29W7GiaygNE4Q7w=' ENCRYPTED
    )
    PROXY OFF;

Excel

Copy the VQL below into the shell
Replace {CONNECTION_NAME} with the desired name for the Box file connection
Replace {BOX_FILE_ID} with the ID of the file in Box (see step 3 in Step-by-Step).
Navigate to, and double click on the new connection, then click Create Base View.

Denodo-Box Quick Start

Overview

Creating a Connection and Base View

CSV

JSON

Excel