CData Connect allows users to define Derived Views. These are virtual, read-only tables which are defined by a single SQL query. They do no hold any data, but when queried, they return the results of the defining SQL query. This allows users to easily and quickly run complex SQL queries against the available data model.

Create a Derived View

To create a new Derived View, open the Data Model page and click the Data Explorer tab. Here, you can access the data model of the connections you have configured. On the right side, you can design SQL Queries against the data model and preview their response. See SQL Query Syntax for more information on the supported SQL Syntax.

When you are satisfied with the results of an SQL query, you can create a new view by clicking Save As View. In the next window, provide a name for your new view, then click Save.

View Permissions

The contents of a Defined View listed in the UserViews virtual schema are only accessible to users that already have permissions to access the underlying data-sources. These views do not provide access to information that a user could not already read. However, the Derived Views metadata (i.e., view column names) is accessible to all users on the instance. If displaying the column names of a view can be a security concern in your organization, make sure to obfuscate the column names (e.g. Using SQL AS keyword) when designing the view.

Managing Derived Views

You can list existing Derived Views from the Derived Views sub-tab in the Data Explorer. From here, click on a Derived View to examine the defining SQL query, and click Run to preview the results. You can also make changes to the View SQL and clone or delete your views.

Querying Derived Views

The Derived Views can be queried through the Virtual SQL Server and REST endpoints of CData Connect, and are listed in the UserViews database. For example, after creating the view my_custom_view, you can query it with the following SQL Query:

SELECT * FROM UserViews.my_custom_view