Denodo - Creating a Dropbox File Data Source

This document describes the process to create a Denodo data source against a Dropbox file via the Dropbox API.  Note that Dropbox shareable links do not work with Denodo due to a failure to follow Dropbox redirects.  Authenticated calls via the API are required to connect Denodo to any Dropbox files.  Dropbox API utilities are used to generate an OAuth token that can be stored in Denodo.

Note: Dropbox Apps are owned by individual user accounts. If a user account becomes disabled for any reason, including the account owner leaving the University, credentials related to the Dropbox App are invalidated and any Denodo data sources using them become non-functional. For production usage, a more durable solution such as an SFTP host and service account is suggested.

Configure Dropbox

  1. Create an App using the Dropbox App Console:
    • API: Scoped Access
    • Type of Access: Select based on your use case. The data source example below assumes Full Dropbox.
  2. Enter the following on the Settings tab:
    • Redirect URI: https://localhost (be sure to click Add)
    • Access token expiration: Short-lived (recommended)
    • Copy the App Key and App Secret
  3. Grant the scope files.content.read on the Permissions tab. 

Create OAuth Access and Refresh Tokens

Complete the following steps in the Denodo VDP client:

  1. Open the VDP client.
  2. Connect to the Denodo environment. 
  3. Navigate to Tools > OAuth credentials wizards > OAuth 2.0 wizard and set the following:
    • Authentication grant: Authorization code grant
    • Client identifier: App Key from #2 in previous section
    • Client secret: App Secret from #2 in previous section
    • Authentication method: Include the client credentials in the body of the request
    • Token endpoint URL: https://api.dropboxapi.com/oauth2/token
    • Authorization server URL: https://www.dropbox.com/oauth2/authorize
    • Redirect URI: https://localhost
    • Click Generate the authorization URL
      • Copy and paste the URL into a text editor
      • Append the value &token_access_type=offline to the URL
      • Copy the new URL and paste it into a browser address bar and press Enter
  4. Give consent when prompted.
  5. The browser returns a message that ‘localhost’ refused to connect, but the browser address bar contains the required authorization response URL. Copy the URL from the browser address bar and paste it into the Paste the authorization response URL field in the Denodo OAuth credentials wizard.
  6. Click Obtain the OAuth 2.0 credentials.
  7. Copy the credentials to the clipboard and store in a secure location. If the credentials do not contain a refresh token, you need to regenerate the credentials, being sure to append the offline access token property to the authorization URL as shown in #3 above.

Create a data source

Create a Denodo data source appropriate for the file to which you are connecting. The example below walks through connecting a CSV (a delimited file data source) in the Dropbox directory /home/example_directory/sample_file.csv.

  1. Navigate to New > Data source > Delimited file
  2. Set the Data Route to HTTP Client and configure the following: 
    • Configuration tab
      • HTTP method: GET
      • URL: https://content.dropboxapi.com/2/files/download
      • Choose either the HTTP Header or URL Parameter method to specify the file being connected. If the file is specified using both the HTTP Header and the URL Parameter, the Dropbox API returns an error. In either case, the value must be specified as JSON and the curly braces escaped with the backslash character:
        • Click on HTTP headers and enter:
          • Name: Dropbox-API-Arg
          • Value: \{"path": "/example_directory/sample_file.csv"\}
        • Query string parameter:
          • Append the following value to the URL above: ?arg=\{"path": "/example_directory/sample_file.csv"\}
    • Authentication tab
      • Authentication: OAuth 2.0
      • Client identifier: Paste from credentials obtained above
      • Client secret: Paste from credentials obtained above
      • Access token: Paste from credentials obtained above
      • Refresh token: Paste from credentials obtained above
        • If the credentials do not contain a refresh token, you need to regenerate them, being sure to append the offline access token property to the authorization URL as explained in #3 in the Create OAuth Access and Refresh Tokens section.
      • Token endpoint URL: https://api.dropboxapi.com/oauth2/token
      • Access token expires in: 14400 (default expiration time for Dropbox tokens)
  3. Click Test Connection to verify the data source connection. 
    • The following situations may cause error messages: 
      • Invalid credentials were supplied
      • An invalid token endpoint was supplied
      • The path argument is improperly formatted or contains an invalid path
      • The Dropbox App is not granted the appropriate permission scope(s) 
        • If you have to add permissions, note that you will need to generate a new token. The old token will retain the old permissions.
Tags: 
Last Updated: 
Monday, January 4, 2021