ODBC Sources

Virtual DataPort provides access to ODBC sources.

To retrieve data from a Microsoft Excel file, we recommend creating an Excel data source instead of an ODBC one. The performance of the Excel data sources is significantly better.

To query Microsoft Access, Virtual DataPort requires the Access ODBC drivers. If Access is installed on the same host as Virtual DataPort, you do not need to do anything else (when you install Access, its drivers are also installed). If Access is not installed, install the Microsoft Access Database Engine 2010 Redistributable.

Note

If a source can be accessed with a JDBC driver and with an ODBC driver, create a JDBC data source instead of an ODBC one. Queries involving a JDBC data source run faster than if they involve an ODBC data source, even if the underlying source is the same.

Important

Consider the following:

To create a new ODBC data source, right-click on a database of the Server Explorer and click New > Data source > ODBC.

The Tool will display the dialog to create the data source.

“Connection” tab

ODBC data source: “Connection” tab

ODBC data source: “Connection” tab

  • Name. Name of the new data source.

  • Database adapter. Adapter used to connect to the database. Virtual DataPort provides adapters to connect to Database Management Systems (DBMS). Each adapter knows the capabilities supported by the database: what operators and functions the database has, what clauses it supports, etc.

  • Connection type. There are two types of ODBC connections:

    • DSN (Data Source Name). Use one of the existing DSNs of the system where Virtual DataPort server is installed.

    • Direct. Access to a file stored in the local file system of the Server, using one of the ODBC drivers registered on the Server. The path can use interpolation variables (see section Paths and Other Values with Interpolation Variables).

  • Login. User login to access the external database.

  • Password. User password to access the external database.

  • Pass-through session credentials. If selected, when a client queries a view that uses this data source, Virtual DataPort will use the credentials of the user to connect to the database, instead of the values of the fields “Login” and “Password”.

    The section Importing JDBC Sources explains this option in more detail.

    Warning

    Users should be careful when enabling the cache for views that involve data sources with pass-through credentials enabled. The appendix Considerations When Configuring Data Sources with Pass-Through Credentials explains the issues that may arise.

  • Transaction isolation. Isolation level of the transactions and queries executed in the database of this data source. The section Importing JDBC Sources explains the different isolation levels in more detail.

    If you select Database default, the Server uses the default isolation level of the database to execute queries.

  • Click DSN Properties Configuration to add properties to the connection URI.

  • Click Test Connection to check that the Server can connect to this database.

  • Open the Connections Pool Configuration dialog to configure several parameters of the connections pool that Virtual DataPort will use to optimize access to the external database:

  • Initial Size: number of connections with which the pool is to be initialized. A number of connections are established and created in “idle” state, ready to be used.

  • Maximum number of active connections: maximum number of active connections to this database the pool will open. Once this limit is reached, the next request that involves sending a query to this database will have to wait until another query finishes.

    • Enter -1 to remove the limit of maximum number of active connections. There will not be a limit on the number of connections that the pool creates.

    • Enter 0 to disable the pool of connections. Therefore, for every query Virtual DataPort sends to the database, it will open a new connection to this database. Once the query finishes, it will close this connection.

  • Ping Query: SQL query used by the pool to verify the status of the connections. The query should consume as little resources in the database as possible, and the table in question should exist

  • Test Connection: if this property is checked and there is a specified ping query, each connection retrieved from the connection pool will be validated by executing the ping query.

Note

In production environments, we strongly recommend selecting the Test connection check box and defining a Ping query.

“Read & Write” tab

ODBC data source: “Read & Write” tab

ODBC data source: “Read & Write” tab

  • Fetch size: it gives the ODBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed.

  • Ignore trailing spaces: if selected, the Server removes the space characters at the end of text type values of the results returned by this data source’s views.

“Source Configuration” tab

This tab is only visible when editing a data source, not when creating a new one.

The section Data Source Configuration Properties explains the properties that can be changed in this dialog.

Note

In most cases, the default value for these options is correct. Therefore, this dialog will only be useful in very specific environments.

“Metadata” tab

In the Metadata tab, you can set the folder where the data source will be stored and provide a description.

When editing the data source, you can also change its owner by clicking image2.

Once the ODBC source has been imported, the process of creating base views is the same as for JDBC data sources (see section Creating Base Views from a JDBC Data Source).

The Excel ODBC driver provided by Microsoft Windows has some limitations:

  • It cannot delete data from Excel spreadsheets. It can only query them and append more data.

  • The Excel driver of Microsoft Excel 2003 or earlier, can execute only one query simultaneously. This can be a problem if we are combining data from the same Excel file.

    The newer versions of the Excel driver do not have this limitation.

Creating Base Views from an ODBC Data Source

After creating an ODBC data source, you have to create base views over it. You can later query these base views or combine its data with data from other views.

Schemas, tables and views of an ODBC source

Schemas, tables and views of an ODBC source

To create an ODBC base view, follow these steps:

  1. On the Server Explorer, double-click the ODBC data source to open it and then, click Create base view.

  2. Select the check boxes beside the tables that you want to create base views from.

  3. Click Create selected.

After this, each new base view will be listed in the Server Explorer in two places:

  1. In the folder that the view has been created in.

  2. As a child node of the data source that the base view belongs to. This node cannot be moved to other folders. It is added to the tree to provide an easy way to see the base views created from a data source.

As the process of creating ODBC base views is very similar to creating JDBC base views, see the section Creating Base Views from a JDBC Data Source for more information about this process.

Enabling the Support for ODBC Sources When the Virtual DataPort Server Runs on Linux

Virtual DataPort can connect to Microsoft Access and other ODBC sources. However, this feature is disabled by default when the Virtual DataPort server runs on Linux.

If you are not going to connect to Microsoft Access files nor other ODBC sources, go to the next post-installation section. You can also do the process described in this section later.

On Linux, this feature is disabled by default because, depending on the configuration of the host, Virtual DataPort could load the wrong library to connect to ODBC sources. If the wrong library is loaded, Virtual DataPort crashes. To avoid that a user creates an ODBC source that leads the Virtual DataPort server to crash, ODBC sources are disabled by default.

Follow these steps to enable this feature:

  1. Connect to the host where Denodo is installed. Log in with the user account you will use to launch Virtual DataPort.

  2. In this host, install the ODBC driver manager unixODBC. To verify if unixODBC is already installed, execute the following commands. These check if the command line utilities odbcinst and isql are already installed:

    which isql
    
    which odbcinst
    

    If both commands return the path to a file, go to step #3. If unixODBC is not installed, do this:

    1. For Linux distributions that use the RPM packaging system (e.g. Red Hat, CentOS, etc.), execute this:

      sudo yum install unixODBC
      

      For the ones based on Debian like Ubuntu execute this:

      sudo apt-get install unixODBC
      
  3. Execute this:

    odbcinst -j
    

    This is to verify that unixODBC is installed correctly:

  4. Check that these files exist:

    /usr/local/lib64/libodbc.so
    /usr/local/lib64/libodbcinst.so
    

    Or,

    /usr/local/lib/libodbc.so
    /usr/local/lib/libodbcinst.so
    

    The path to these files may change depending on the Linux/Unix distribution.

  5. Edit the file ~/.bash_profile and add the following at the end.

export LD_PRELOAD=/usr/local/lib/libodbc.so:/usr/local/lib/libodbcinst.so:$LD_PRELOAD

With this change in the value of the variable LD_PRELOAD, you make sure that Virtual DataPort loads the files libodbc.so and libodbcinst.so provided by unixODBC and not the ones provided by other libraries.

Note

If the two files listed above are in lib64 and not in lib, change the line above accordingly.

  1. Logout and login again from this user account. Do this to apply the changes done in .bash_profile.

  2. If the Virtual DataPort server was started, stop it.

  3. Start the Virtual DataPort server and login with an administrator account. Then, execute this command on the VQL Shell:

SET 'com.denodo.vdb.ODBCDataSource.enable' = 'true';
  1. To check that the configuration has been updated correctly, do the following from the administration tool:

    1. Create an ODBC data source.

    2. Create an ODBC base view.

    3. Query this base view.

Enabling the Support for ODBC Sources When Using an External JRE

Virtual DataPort provides access to ODBC data sources. If you configure the Virtual DataPort server to use an external Java Runtime Environment (JRE) instead of the one included in the Denodo Platform, do the following to be able to connect to ODBC sources:

  1. If the Denodo server runs on Windows, copy the file <DENODO_HOME>\dll\vdp\jdbc-odbc\x64\JdbcOdbc.dll to the folder <EXTERNAL_JRE_HOME>\bin.

  2. If the Denodo server runs on Linux, copy the file <DENODO_HOME>/dll/vdp/jdbc-odbc/x64/libJdbcOdbc.so to the folder <EXTERNAL_JRE_HOME>/lib/amd64.