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:

1. 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.

2. Create a connection and base view for the file

Creating a Connection and Base View

Follow the following steps:

1. Open the VQL Shell

   Open

   

2. Make sure you have the correct virtual database (VDB) selected in the top left corner of the VQL shell.
   
   

   Open

   

3. Follow the sections below based on the type of file you want to connect to.

Getting Box File ID

To get the ID of a Box file, navigate to the file in the Box web UI and copy the ID from the end of the URL.

CSV

1. Copy the VQL below into the shell

   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;

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

3. Replace {BOX_FILE_ID} with the ID of the file in Box.

4. Navigate to, and double click on the new connection, then click Create Base View.

JSON

1. Copy the VQL below into the shell:

   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 ACCESSTOKENEXPIRESIN = 4155 ) PROXY OFF;

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

3. Replace {BOX_FILE_ID} with the ID of the file in Box.

4. Navigate to, and double click on the new connection, then click Create Base View.

5. Click OK in the Configure JSON View Dialog
   
   

6. In the next screen, for Tuple Root, enter the value that matches your file structure, then click Create Base View. A common Tuple Root value is /JSONFile/JSONArray.