The CData Drivers and Connectors allow you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver or connector, e.g. when using the driver from a tool. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.
There are two ways to create user defined views:
- Create a JSON-formatted configuration file defining the views you want.
- ODBC (Windows) & Power BI Connector: Use the DSN Configuration wizard.
- DDL statements.
Defining Views Using a Configuration File
User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The driver automatically detects the views specified in this file.
You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the driver.
This User Defined View configuration file is formatted as follows:
- Each root element defines the name of a view.
- Each root element contains a child element, called query, which contains the custom SQL query for the view.
For example:
{
"MyView": {
"query": "SELECT * FROM �CData].�Human_Resources].Workers WHERE MyColumn = 'value'"
},
"MyView2": {
"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
}
}
Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:
"UserDefinedViews", "C:\\Users\\yourusername\\Desktop\\tmp\\UserDefinedViews.json"
Defining Views Using the DSN Configuration Wizard
If you are using an ODBC Driver on Windows or a Power BI Connector, you can create User Defined Views in the DSN Configuration wizard.
- Open the DSN Configuration wizard (on Windows, search for "ODBC" from the start menu and select "ODBC Data Sources." Double-click on the connection you wish to configure.
- Navigate to the "Metadata" tab and click the underlying "User Defined Views" tab.
- Click "Define New View," name the view, and enter a SQL query to define the view. For example:
SELECT * FROM Account WHERE MyColumn = 'value'
Defining Views Using DDL Statements
The driver is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.
Create a View
To create a new view using DDL statements, provide the view name and query as follows:
CREATE LOCAL VIEW iMyViewName] AS SELECT * FROM Customers LIMIT 20;
If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.
Alter a View
To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:
ALTER LOCAL VIEW qMyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';
The view is then updated in the JSON configuration file.
Drop a View
To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.
DROP LOCAL VIEW iMyViewName]
This removes the view from the JSON configuration file. It can no longer be queried.
Schema for User Defined Views
User Defined Views are exposed in the UserViews schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName property.
Working with User Defined Views
For example, a SQL statement with a User Defined View called UserViews.RCustomers only lists customers in Raleigh:
SELECT * FROM Customers WHERE City = 'Raleigh';
An example of a query to the driver:
SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';
Resulting in the effective query to the source:
SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';
That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.
