Versions Compared

Version Old Version 95 New Version 96
Changes made by

Matthew Girard

Matthew Girard

Saved on

Key

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

...

Code Block
Hello,

Please update the firewall to allow the following bi-directional traffic:

Source
Group_Denodo_TSS (Firewall Group)

Destination
${DATASOURCE_HOSTNAME} (${DATASOURCE_IP_ADDRESS})

Port
${PORT_NUMBER} (tcp/udp)

For vendor-hosted data sources, an additional step may be needed to allow Denodo traffic from Tufts on their side.

...

  1. Add the file(s) 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. This approach is strongly encouraged since it prevents dependency on files owned by individual users.

    If adding files to the drop folder isn’t an option, you can share files or folders with the Box service account for Denodo by giving view access to AutomationUser_1958391_MbvCoOnEz5@boxdevedition.com.

  2. Get the ID of the Box file. 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.

  3. Open the VQL Shell

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

  5. Follow the sections below based on the type of file you want to connect to. You’ll need to request the CLIENTIDENTIFIER and CLIENTSECRET values by submitting a ticket to data-virtualization@tufts.edu.

CSV

  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. Execute the VQL

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

...

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 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/SZzDyQXUYDobbUKnCWmYbm0blah' 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;

...

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=blah' 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;

...

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' ;

CREATE OR REPLACE DATASOURCE CUSTOM ${CONNECTION_NAME}
    FOLDER = '/01-base_layer/01-connections/box'
    CLASSNAME='com.denodo.vdb.contrib.wrapper.xls.ExcelWrapper';

CREATE OR REPLACE WRAPPER CUSTOM ${CONNECTION_NAME}
    FOLDER = '/01-base_layer/01-connections/box'
    DATASOURCENAME=${CONNECTION_NAME}
    PARAMETERS (
      'Type of file' = 'Excel 2007 or later (*.xlsx)',
      'File location' = ROUTE HTTP 'http.CommonsHttpClientConnection,120000' GET 'https://api.box.com/2.0/files/{BOX_FILE_ID}/content'
        AUTHENTICATION OAUTH20 ( 
          ACCESSTOKEN = 'mf49ZT1AJWtoSYrIQmyBynccLH8ALOGPrZgmLK34jHOxdNTenJ2vppdG676VZYpFCnqnaRfVXhqmWfVZm540+lEy3ci1JYgkhHgxNTNkFwybmbLq/X2qpEyPEOcruh4OkqrcqlesRcuCwjZzo5jSbNGtRjwrSgKGulwdeCAow3Y=blah' 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 = 'mwZrFKkcSmcHbDBwqN3B+KCJERaYFLZyO2U47jvzp6F0O9xFarhNAXK2wqGuuL2f+PrNaXZek1e2azoQ9nncngaWC4K4jX7qoReEbNYJtFxszv67rP8h1+/WqWFPIxnpTwGuDxOGrmb/3ogEUS77iuMVsIREw1QyUbjvY6c/AeA=' ENCRYPTED
        )
        PROXY OFF,
      'Extract data from all sheets' = true,
      'Extract sheet name as a new column' = false,
      'Has headers' = true,
      'Stream tuples' = true
    );

...