Versions Compared

Key

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

...

  1. Copy the VQL below into the shell

  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. Code Block

...

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

  1. Copy the VQL below into the shell
  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.Click OK in the Configure

JSON

...

In the next screen, for Tuple Root, put /JSONFile/JSONArray and click Create Base View.

...

languagesql

...

  1. Copy the VQL below into the shell:

    Code Block
    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;

...

Excel (WIP)

  1. Copy the VQL below into the shell

  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

    Image Added
  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.

    Image Added