Local Path¶

Use this type of path to obtain the data from a file or a set of files located in the same directory. This path can be:

• On the local file system of the Virtual DataPort Server (not to the local file system where the Administration Tool is running)

• Or on a Windows shared drive accessible from the host where Virtual DataPort is running. Note that the user account that launches the Server has to have privileges to read this file.

To enter the path to a file, we recommend you to click the Browse button, enter the path in the File name box and click Ok. The reason for this is that in Virtual DataPort some characters have a special meaning and have to be escaped. By doing it this way, these characters are automatically escaped.

Instead of specifying the path to the file, you can use interpolation variables (see section Paths and Other Values with Interpolation Variables) if you do not know the path to the file at the time of creating the base view and you want to provide it at runtime.

For example, you can enter the path /tmp/datafiles/@REPORT_ID.txt. The base views created over this data source will have an extra field called report_id and at runtime you will have to provide the value of this field in the WHERE clause of the query.

From Variable¶

Use this path if the data will not be obtained from any source but it is provided by clients in the WHERE clause of the queries that involve the base views of the data source.

To use this type of path, you have to provide a name for the variable. All the base views created over a data source with this type of path, will have an extra field, which represents this variable.

Let us say that:

• You create a DF data source with Data route From Variable and the name of the variable is “variable_with_data”. The Column delimiter is “,”, and select the Header check box.

• Click Create base view. Now you will have to provide the value of the variable “variable_with_data” so the Server infers the schema of the data. For example:

  REGION,SALES,ACTIVITY
  NORTH,20000,75%
  SOUTH,51000,79%
  WEST,12000,72%
  

• Then, every time a client queries this view, she will have to provide the value of the variable in the WHERE clause of the query. E.g.:

  SELECT region, sales, activity
  FROM bv_df_view_from_data
  WHERE input_variable_with_data = 'REGION,SALES,ACTIVITY
  NORTH,20000,75%
  SOUTH,51000,79%
  WEST,12000,72%';
  

HTTP Path¶

Use an HTTP path when the data has to be obtained by sending an HTTP request to a server.

With HTTP paths you can either send a GET or a POST request.

There are two ways of adding parameters to the body of a POST request:

1. URL parameters: if the URL contains query parameters, they will be removed from the URL and sent in the body of the request as the values of an HTML form. The “Content-type” header of the HTTP request will be “application/x-www-form-urlencoded”.

   For example, if the URL is http://acme/customer?first_name=John&last_name=Smith at runtime, the Server will send the HTTP request to http://acme/customer (without the query parameters) and the body of the request will be first_name=John&last_name=Smith.

2. Post body: the contents of the text area below this option will be sent in the body of the HTTP request. Enter the content type of the body in the Content type box.

To add a header to the HTTP requests sent to retrieve the data, click HTTP headers. In the “HTTP Headers” dialog, click New and enter the name and the value of the header.

The following elements of an HTTP path can contain interpolation variables (the section Paths and Other Values with Interpolation Variables explains what interpolation variables are):

1. The URL. It can contain one or more interpolation variables. For example, https://acme.com/department?id=@{department_id}

2. The HTTP headers. Both the names and their values can be interpolation variables.

3. The “Post body” can contain one or more interpolation variables. This is useful if you want to send a POST request and one or more values of the body need to be set at runtime, when the data source is queried. For example, let us say that the body of the POST request is the following:

   <employee_info>
      <last_name>@last_name</last_name>
      <first_name>@first_name</first_name>
   </employee_info>
   

HTTP POST request with XML Body with an interpolation variable¶

When you create a base view over this data source, the view will have three extra fields: department_id, last_name and first_name. The queries to this view will always have to provide a value for these fields in the WHERE clause.

For example, if the query is

SELECT *
FROM view
WHERE department_id = 2 AND first_name = 'John' AND last_name = 'Smith'

the Server will send a request to https://acme.com/department?id=@{department_id} with this body:

<employee_info>
    <last_name>Smith</last_name>
    <first_name>John<first_name>
</employee_info>

Any query involving a base view created over this data source that does not provide a value for these three fields will fail.

Note

You have to escape the characters “@”, “{” and “}” when they are not part of the name of an interpolation variable. They are escaped with the character “” and the character “” with itself (e.g. “\”). For example:

<employee_info_request>
  <department_name>department \@1 \{ACCT\}</department_name>
</employee_info_request>

If the body of the request is loaded with Load file, the Tool escapes all these characters automatically.

The benefit of using interpolation variables is that the HTTP request to the Web server is not static and can be different for every query.

If the definition of the HTTP path has interpolation variables and you click “Test connection”, you will have to provide the value of the interpolation variables. You also have to do this when creating a base view over this data source. The Administration Tool will display a dialog like the following to provide the value of the variable.

Providing the value of an interpolation variable for an HTTP path¶

In this dialog, select the URI parameter check box if the value of the variable is the value of a query parameter of the URL. If selected, the value of the variable will be escaped accordingly. Otherwise, if the variable is part of the URL, the value will be escaped as any other part of the URL. For example, if the URL is http://acme/recipe?name=@dish, when you provide the value of the variable “dish”, select the check box “URI parameter”. That way, if the value of dish is “Mac&Cheese”, the Server will send a request to http://acme/recipe?name=Mac%26cheese. Note that “&” has been properly encoded by replacing “&” with “%26”. If dish is not marked as a “URI parameter”, the URL will be http://acme/recipe?name=Mac&cheese, which in this case is not correct because as the URI has “&” after “Mac”, the Web server will treat “cheese” as another query parameter.

When the interpolation variable does not belong to the URL of the path, leave the “URI parameter” check box cleared.

Select the check box Check certificates if you are in one of these scenarios:

• The service uses SSL (i.e. the URL starts with https) and you want Virtual DataPort to validate that the certificate presented by this service was issued by a Certificate Authority (CA) trusted by the Java Virtual Machine (JVM) included with the Denodo Platform. This validation will be performed for every connection established with the service.

  If the certificate presented by this service was not issued by a trusted CA or it was self-signed, but you still want Virtual DataPort to validate it, import the certificate into the list of trusted certificates of the JVM. The section Importing the Certificates of Data Sources (SSL Connections) of the Installation Guide explains how to do this.

• Also, select this check box if the service requires SSL client authentication.

Clearing the check box has two implications:

1. Virtual DataPort will accept any certificate presented by the service without checking who issued it.

2. And, all the requests will fail if the service requires SSL client authentication.

In the Proxy tab, you can set a proxy configuration for this data source or use the Default configuration of the Server (see section Default Configuration of HTTP Proxy).

The supported authentication methods for HTTP connections are:

• Basic. The credentials are sent in plain text (RFC 2617 - HTTP Authentication: Basic and Digest Access Authentication).

• Digest. The credentials are sent encrypted.

• NTLM. Uses the Microsoft NTLM Authenticaion (NT LAN Manager Authentication Protocol Specification) to connect to the service. Virtual DataPort supports NTLM v1 and NTLM v2.

• OAuth 1.0a and OAuth 2.0. See section OAuth Authentication.

• SPNEGO (Kerberos). See section SPNEGO (Kerberos).

If you select the check box Pass-through session credentials (available for the authentication methods “Basic”, “Digest”, “NTLM” and “SPNEGO (Kerberos)”) , when a client executes a query that involves this data source, the credentials used to send a request to the service are the credentials of the user that executes the query; not the credentials of the fields “Login” and “Password”. When this option is selected, the credentials of the fields “User” and “Password” are used only when creating base views over this data source, to send a request to the service and analyze the output of the URL.

The section SPNEGO (Kerberos) explains in detail the behavior of Virtual DataPort when the authentication method is “SPNEGO (Kerberos)” and “Pass-through session credentials” is selected.

OAuth Authentication¶

OAuth is an authorization framework that allows third-party applications (in this case, Virtual DataPort), to access resources on a server on behalf of a resource owner.

The main benefit is that you do not need to share your username and password with third-party applications in order to authorizing them to access your data.

The following subsections explain how to use the wizards that help you obtain the credentials needed to connect to a service with OAuth 1.0a or OAuth 2.0 authentication.

Note

Before creating the data sources in Virtual DataPort, you have to register Virtual DataPort as an application in the service that you want to access.

Note

We recommend creating a single data source for all the views that retrieve data from the same OAuth-authenticated service. The reason is that, if at any point, the OAuth credentials change, you will only have to change them in one data source. To do this, you can create the data source with an interpolation variable in the URL (http://service.com/@OBJECT_TYPE/ <http://service.com/@OBJECT_TYPE/>)

OAuth 1.0a¶

This section explains how to configure an “HTTP Client” route to retrieve data from a service with OAuth 1.0a authentication. The Tool provides the OAuth 1.0a credentials wizard to help you obtain these credentials.

Follow these steps:

1. In the “Edit HTTP connection” dialog, click the Authentication tab.

2. Select OAuth 1.0a in the Authentication list.

3. Enter the Client identifier and the Client shared secret provided by the service.

4. Select the Signature method. The HMAC-SHA1 signature is the most used, so usually is the right option.

5. If you already have the Access token and the Access token secret, enter them in the boxes below and click Ok.

   If you do not have these tokens, click launch the OAuth 1.0a credentials… to open the wizard that will help you obtain them.

OAuth 1.0a credentials wizard¶

1. Enter the Temporary credential request URL, the Resource owner authorization URL and the Token request URL

   The documentation of the service you are accessing must provide these details.

2. Select the Callback URL. When you get to the step 2 of the wizard, you will have to open an URL in your browser. In this URL, the service displays a page where you have to authorize Virtual DataPort to access your data. If you proceed, you will obtain the Verification code, which Virtual DataPort will use to send an HTTP request to the service. The response will contain the Access token and the Access token secret.

   The Callback URL determines how the service will return the Verification code.

   Note

   Depending on the service, you cannot select any option. Some of them force you to use a specific redirect URL, others only allow oob, etc.

   1. oob: with this option, the wizard will request the service to display the Verification code in your browser after the authentication process.

   2. If you select the second or the third option, the service will redirect your browser to this URL and it will add the parameter code to it. The value of this parameter is the Verification code.

      The default URL (http://localhost:9090/oauth/1.0a/callbackURL.jsp) points to a JSP located in the Apache Tomcat embedded with the Denodo Platform, which will display the value of the code parameter in a box that makes it easier to copy it.

      If you have to indicate another callback URL, you will have to extract manually the value of the code parameter from the URL.

3. Click Generate the authorization URL. Virtual DataPort will request a Temporary token and with it, it will generate the Authorization URL.

4. Click Open URL. If the browser is not launched, copy the URL and open it manually.

   In this URL, you have to authorize the Virtual DataPort server to retrieve data from the service.

5. After authorizing Virtual DataPort to access your data, the service returns the Verification code. Enter this code in the Paste the verification code text field.

   If the Callback URL is oob, you have to type the value. If you have selected the default URL, you can copy it and paste it into this box.

6. Click Obtain the OAuth 1.0a credentials. The Server will request the OAuth tokens using all the details you have provided and the Verification code.

7. Click Ok to close the wizard.

   The wizard will fill the text areas “OAuth access token” and “OAuth access token secret”.

6. Click Ok to close the “Edit HTTP Connection” dialog and then, Save to create the data source.

To use this wizard independently, you can do so by clicking on OAuth 1.0a wizard on the menu Tools > OAuth credentials wizards of the Administration Tool.

You may need to use this wizard when using a custom wrapper whose input parameters are OAuth credentials.

OAuth 2.0¶

This section explains how to configure an “HTTP Client” route to retrieve data from a service with OAuth 2.0 authentication. The Tool provides the OAuth 2.0 credentials wizard to help you obtain these credentials.

The Tool provides the OAuth 2.0 credentials wizard to help you obtain these credentials.

Configuring OAuth 2.0 authentication for a data source¶

Follow these steps:

1. In the “Edit HTTP connection” dialog, click the Authentication tab.

2. Select OAuth 2.0 in the Authentication list.

3. Enter the Client identifier and the Client secret provided by the service.

4. Select one of the options of Authentication method used by the authorization server. This controls how Virtual DataPort will send the credentials to the service when requesting a new OAuth access token. The options are:

   1. Include the client credentials in the body of the request: Virtual DataPort will add the credentials to the body of the request, in the parameters client_id and client_secret.

   2. Send credentials using the HTTP Basic authentication scheme: Virtual DataPort will send the credentials of the user in the Authentication header of the HTTP request.

   These two options are the ones described in the section “2.3.1. Client Password” of the OAuth 2.0 specification (RFC 6749 - The OAuth 2.0 Authorization Framework).

   Several OAuth 2.0 services such as SalesForce require the clients to send the credentials in the body of the request (first option).

5. If you already have the OAuth access token, enter it in the Access token box and select the appropriate Request signing method. If you also have the Refresh token, enter it in the Refresh token box enter the value of the Token endpoint URL and, if you know it, the number of seconds until the access token expires.

   If you do not have the access token and it will be provided at runtime instead of being stored in the data source, select Access token value is an interpolation variable and in the box below, enter a name for the variable. At runtime, the queries to the base views of this data source will have to provide a value for this variable. This value will be the access token used to connect to the source. This option is useful if the source requires OAuth 2.0 authentication but does not fully implement the standard. In this case, you can develop a stored procedure that obtains this token and pass it to the base view.

   If you do not have the access token and want to obtain it from the source, click launch the OAuth 2.0 credentials… to open the wizard that will help you obtain it.

OAuth 2.0 credentials wizard¶

1. Enter the Authorization server URL.

2. Select the Redirect URI. When you get to the step 2 of the wizard, you will have to open an URL in your browser. In this URL, the service displays a page where you have to authorize Virtual DataPort to access your data.

   If you proceed, the service will redirect your browser to the Redirect URI and it will add several parameters to it. Virtual DataPort will use the values of these parameters to send an HTTP request to the service. The response will contain the Access token and maybe, the Refresh token.

3. Click the button for each scope you want to add and enter its name.

   Scopes are “privileges” defined by the service, which control the data that the application can request.

   For example, Twitter defines several scopes and depending on the scopes requested in this wizard, Virtual DataPort will be able to retrieve your tweets, but may not post new ones on your behalf.

4. Usually, you can leave the Set the “state” request parameter selected. However, if the process of obtaining the OAuth credentials fails, check that the service allows setting this parameter.

5. Enter the Token endpoint URL.

6. Click on Generate the authorization URL.

   Virtual DataPort will generate an URL with all the parameters you have provided.

7. Click on Open URL.

   If the browser is not launched, copy the URL and open it manually.

   In this URL, you have to authorize the Virtual DataPort server to retrieve data from the service.

8. After authorizing the application, the service will redirect you to an URL. Paste this URL in the text field of step 3.

9. Click on Obtain the OAuth 2.0 credentials.

   The Server will request the OAuth credentials using all the details you have provided and the parameters of the URL you have pasted in the previous step.

10. Click Ok to close the wizard.

    The wizard will fill the text areas and text fields with the information returned by the service.

    Not all the services provide a Refresh token, so this text area may be empty.

6. Select the Request signing method. Virtual DataPort has to sign each request with the Access token. Usually, all OAuth services allow the “Authorization” request header method, which consists on adding a special HTTP header to the request. If the service does not support this method, you can select the other methods defined by the standard:

   1. Form-encoded body parameter: send the token in the body of the request (only available with HTTP POST requests)

   2. URL query parameter (“access_token”): the token is sent in the parameter access_token of the URL.

   3. Or, add the token as a query parameter with a name different from “access_token” (URL custom query parameter).

7. If you do not have the refresh token and it will be provided at runtime instead of being stored in the data source, select Refresh token value is an interpolation variable and in the box below, enter a name for the variable. At runtime, the queries to the base views of this data source will have to provide a value for this variable. This value will be used to refresh the access token if necessary. This option is useful if the source requires OAuth 2.0 authentication but does not fully implement the standard. In this case, you can develop a stored procedure that obtains this token and pass it to the base view.

8. Some REST APIs with OAuth 2.0 authentication require clients to send additional parameters. When you click extra parameters of the refresh token requests and add a parameter, Denodo will send this parameter in:

   1. The request to obtain a new access token for the first time. This is the URL generated in the OAuth Credentials Wizard, when you click Generate the authorization URL.

   2. The requests sent when the current access token expired and Denodo has to obtain a new one, using the refresh token.

   For instance, when connecting to Microsoft services on the cloud with OAuth 2.0 authentication, add the parameter resource. Its value has to be the identifier of the application you want to connect to. This is a requirement of this API.

9. Click Ok to close the “Edit HTTP Connection” dialog and then, Save to create the data source.

Usually, you only need to launch the OAuth 2.0 wizard from the dialogs “Create JSON data source” or “Create XML data source”. However, if you need to use this wizard independently, you can do so by clicking on OAuth 2.0 wizard on the menu Tools > OAuth credentials wizards of the Administration Tool.

You may need to use this wizard when using a custom wrapper whose input parameters are OAuth credentials.

Denodo Browser¶

The Denodo Browser is a GUI-less browser that embeds an HTTP client and a JavaScript engine. It can perform web navigations over complex web sites as any other browser. The benefits of the Denodo browser over other browsers is that, as it does not have a graphical interface, is much lighter. This improve the response time of the navigation sequences and decreases the CPU load.

To retrieve data from a web site using the Denodo Browser you have to provide a navigation sequence written in ITPilot’s NSEQL (Navigation SEQuence Language).

You can easily generate a navigation sequence using the ITPilot toolbar for Microsoft Internet Explorer. To do this, do the following:

1. Open Microsoft Internet Explorer. We assume that the ITPilot toolbar for Internet Explorer is installed.

2. Click Rec.

3. In the URL box, enter the URL where you want to start the navigation.

4. Record the navigation.

5. Once you finish, click Stop and then, Toolbar State Info.

6. Copy the content of the text box NSEQL Sequence.

7. Go back to the administration tool and paste this sequence in the Sequence box of the dialog “Edit Denodo Browser Connection”. Take the following into account:

   • Paste the sequence after the text sequence://. For example:

sequence://Navigate(www.denodo.com,0);
ExtendedWaitPages(-1);

• You cannot put comments in the sequence. I.e. there can be no lines that start with # or /*.

In the Browser pool list, the available options are:

• Internal: to execute the navigation sequence, Virtual DataPort will create an instance of the Denodo Browser to execute the navigation sequence.

• External: to execute the navigation sequence, Virtual DataPort will send a request to the ITPilot browser pool of the current installation (you have to make sure it is started) to execute the navigation sequence.

The benefit of using the internal browser is that you do not have to start the ITPilot Browser Pool. On the other hand, it puts more load on the Virtual DataPort server than if the external browser pool executes the navigation sequence.

The ITPilot User Guide and the ITPilot NSEQL Guide provide more information about the Denodo Browser, NSEQL sequences and how to record sequences using the Internet Explorer toolbar.

FTP / SFTP / FTPS¶

Use this path to retrieve the file from an FTP server (FTP, SFTP or FTPS). The parameters required to configure this path are the URL and credentials of the server.

Example of a URL to an FTP server: ftp://ftp.example.com/pub/employee.csv.

Example of a URL to an SFTP server: sftp://example.com//home/joe/employee.csv (note the two slashes after the host name)

Compressed or Encrypted Data Sources¶

Virtual DataPort can access data files that are:

• Compressed with ZIP

• Compressed with GZIP.

• Encrypted with the algorithm “Password-Based Encryption with MD5 and DES” (PBE with MD5 and DES). This encryption method is described in the Java Cryptography Architecture (JCA) Reference Guide.

In the dialog to configure the path to the data file (delimited file, XML or JSON data sources), the available options are:

• Decompress (zip format). Select if the data file is a ZIP compressed file.

• Decompress (gzip format). Select if the data file is a GZIP compressed file.

• Decrypt. Select this option if the data file is encrypted with PBE with MD5 and DES. In this case, you can also fill in the “Password” box.

• Custom. You can develop your own custom filters to preprocess the data before is processed by Virtual DataPort. See more about custom filters in the section Custom Input Filters.

If an external application generates a DF, JSON or XML file to be read by Virtual DataPort, you can encrypt it for security reasons. Then, when creating the data source to access these data, select the “Decrypt” filter.

Virtual DataPort provides a tool to encrypt the data with the “PBE with MD5 and DES” algorithm. To do this, uncompress the file denodo-crypt-tools.zip located in the <DENODO_HOME>/tools/db/denodo-crypt-tools directory. Then, you have two options:

1. Write the file to disk in plain text and encrypt it with the script encrypt of the <DENODO_HOME>/tools/db/denodo-crypt-tools/bin directory.

2. Or, use the library denodo-crypt-tools.jar (located in <DENODO_HOME>/tools/db/denodo-crypt-tools/lib) to encrypt the data from your Java application. That way, you can avoid writing the file to disk in plain text.

The README file in the directory <DENODO_HOME>/tools/db/denodo-crypt-tools provides more details about both options.

Custom Input Filters¶

Besides the input filters listed in the section Compressed or Encrypted Data Sources, Virtual DataPort provides a Java API that allows you to develop filters that preprocess the data from the source in any way you need. This allows you to transform the input data in any way you like before it is processed by Virtual DataPort.

To develop a custom filter, see the section Developing Custom Input Filters of the Developer Guide. Once you have developed and imported the filter, you can assign it to a DF, JSON or XML data source.

To assign a filter to a data source, open the dialog to configure the path to the data file (delimited file, XML or JSON data sources). In this dialog, click the Custom option button (for “HTTP client” routes, this button is located in the Filters tab) and then, Edit. The Tool will display a dialog like the Assigning a custom filter to a DF data source.

Assigning a custom filter to a DF data source¶

Select the Select jars check box and then, select the class name of the custom filter from the Class name box.

If the custom filter was imported into Virtual DataPort by copying its jar file to <DENODO_HOME>/extensions/thirdparty/lib directory, clear the Select jars check box and enter the name of the class in the Class name box.

If the custom filter has input parameters, click Add parameter and then, enter a name and a value for each parameter. If you select the Encrypted check box for a parameter, the value of the parameter will be hidden and sent encrypted to the Server. To remove a parameter, click .

Once you have finished, click Ok to close the dialog and continue configuring the data source as usual.

Paths Pointing to a Directory¶

The File path can point to a file or to a directory and it can be in the local file system or in a Windows shared drive.

When you create a base view over a data source that points to a directory, Virtual DataPort infers the schema of the new view from the first file in the directory and it assumes that all the other files have the same schema.

Only for delimited-file data sources: if the path points to a directory and you enter a value in File name pattern, the data source will only process the files whose name matches the regular expression entered in this box. For example, if you only want to process the files with the extension log, enter (.*)\\.log.

Paths and Other Values with Interpolation Variables¶

Paths and other parameters of data sources can be parameterized by adding interpolation variables to their value. By using these variables, you do not have to provide the value of a field at design time (when creating the data source). Instead, you will provide the values of these variables at runtime, when you query a view that uses the data source.

For instance, if you create a XML data source that retrieves the XML file from a Web server and you do not know the full path of the file when creating the view, you can enter an URL like http://acme.com/reports/@{DEPT_NAME}.xml

In this URL, DEPT_NAME is an interpolation variable, which means that the string @{DEPT_NAME} will be replaced at runtime by the value of the variable. The clients that query the views over this data source will have to provide this value in the WHERE clause of the query.

When you create a data source with interpolation variables, the base views created over this source will have a field for each interpolation variable. The name of the field will be the same as the name of the variable. If the data obtained from the source has a field that conflicts with the name of the variable, the field from the source will be renamed.

In our example, the base views created over this XML data source will have an extra field called DEPT_NAME. When a client executes a query that access this source:

SELECT *
FROM ...
WHERE DEPT_NAME = 'it'

the string @{DEPT_NAME} will be replaced by it. So the Server will retrieve the XML file from the URL http://acme.com/reports/it.xml

When you begin creating a base view over a data source that has interpolation variables, the Tool will display a dialog to enter the value of these variables (see Providing input values to interpolation variables). The Server will use these values to query the source and calculate the schema of the new base view.

Providing input values to interpolation variables¶

For example, if the path to a file is like this C:/dir}/file.xml, you have to escape the brace like this: C:/dir\}/file.xml

In Windows systems, the character \ is used to specify file paths, causing some ambiguous situations. For example, the Server could interpret that the path C:\tmp\@{var} references a file named tmp@{var} in the C:\ directory because the second \, due to its appearance just before @, is considered an escape character. Therefore, you have to escape the \ character.

For example, the C:\\tmp\\@{var} path references a file which name is the value of the var variable and it is located at C:\tmp.

The section Execution Context of a Query and Interpolation Strings of the VQL Guide explain this in more detail.