• Creating and Managing Queries
  • Creating a Query
  • Modifying a Query
  • Saving a Query
    • Using Saved Queries
• Configuring Derived Views
  • Creating a Derived View
  • Accessing a Derived View in Client Tools
  • Accessing a Derived View in Data Explorer
  • Modifying a Derived View
  • Deleting a Derived View

The Data Explorer page enables you to execute basic queries on your connected data sources, browse your data models, and create derived views.

Data Explorer can be useful to:

• Verify that your data sources are connected correctly
• Explore the columns and data types that are available in your data sources
• Search for specific data in your data sources manually or with the AI query generator

When you open Data Explorer, the interface displays a navigation pane on the left and the query editor on the right. Click the arrow to the right to view the list of schema for the connection. If there is only one schema in a connection, the schema is already open to a list of tables in the schema. If there are multiple schemas, open the desired schema to view the list of tables.

Creating and Managing Queries

The query editor allows you to create, modify, and save queries.

Creating a Query

Follow these steps to execute a query using Data Explorer:

1. Click Select a Connection to open a drop-down menu of your connections.

2. Select a connection. All available items appear in the left column.
   • The solid table icon indicates a table.
   • The dashed square icon indicates a view.
   • The blue icon to the right of the item indicates that the item is being cached. See Caching for more information.
3. Choose the data that you want to query. If you want to use the AI query generator, refer to the AI Generator documentation.
   • To create a query that selects an entire table or view, drag the item from the left column to the query editor. Alternatively, click the three dots next to the table or view and select Query for results or View Details for metadata information.
   • To select a specific column, click the expansion arrow next to the item that you want to query. Then, drag the column to the query editor.

   Alternatively, you can enter a query manually by using SQL syntax in the query editor. CData Connect Cloud has an autocomplete feature to assist you with SQL syntax.

   

4. Set the Limit field to the number of results that you want to return. By default, the limit is 100, but you can set the limit up to 1,000.

5. Click Execute to run the query.

When you execute the query, a loading icon appears in the Results section, along with a time elapsed notification. The loading icon also appears in the query’s tab.

If your time elapsed exceeds five minutes, please contact CData support for assistance.

When the query is finished executing, your results appear under the query editor. The Details section displays metadata returned by the query, and the Results section displays the data returned by the query. You can scroll through pages and adjust the number of results per page by using the fields at the bottom of the page. You can also adjust the column widths of the table details and results.

If your query returns an error, an error message appears above the query. An error icon also appears in the query’s tab. Click Learn More for more details about the error.

Modifying a Query

After a query executes, you can perform the following actions:

• Modify the query manually by entering query parameters into the query editor.
• Clear the query editor by selecting Clear and then clicking OK in the prompt that is displayed.
• Generate a new query by dragging a new table or column into the query editor and clicking OK in the prompt that is displayed.

Saving a Query

Data Explorer provides the ability to save queries and return to them later without the need to re-enter the query parameters each time.

After you create and successfully execute a query, the Save button at the top of the Data Explorer pane becomes available. Follow these steps to save a query and access it later:

1. Click Save to open the Save Query dialog.

2. Enter a name for the query and click Confirm.

3. The Data Explorer Saved Queries menu opens, and your newly-saved query appears in the list.

Using Saved Queries

Saved queries are available in the Saved Queries folder in the Data Explorer navigation pane. To execute a saved query, click the three dots next to the saved query name and select Query. The contents of the saved query populate in the Query Editor, and you can click Execute to retrieve the results.

Note: The Details tab, which contains metadata, is not available when executing saved queries.

You can also rename and delete saved queries when you click the three dots.

Configuring Derived Views

The derived views feature of Data Explorer allows you to create and save queries that return dynamically populated data results from your connections. You can query your saved derived views from Client Tools, and the results automatically update when the data in your connections change.

Note: Only administrators of CData Connect Cloud can create or modify derived views. Query users can query saved derived views created by an administrator.

Creating a Derived View

To create a new derived view, follow these steps:

1. Click Create Derived View at the top of the Data Explorer table or at the top of the Derived Views tab. The Create Derived View dialog appears.

   

2. Enter a name for the derived view in the View Name field. This is the name that appears in the list of saved derived views.

3. Enter the query for your derived view in the Query field. If there is an existing query in the query field, it automatically populates here. You can modify or delete it as needed.

4. Click Confirm to save the derived view.

Accessing a Derived View in Client Tools

When issuing queries to a derived view from within a client tool, set the Connection or Database value to CData. The following syntax shows the correct fully-qualified syntax for accessing a derived view:

SELECT * FROM [CData].[DerivedViews].[SampleDerivedView]

Substitute the name of your derived view for the [SampleDerivedView] value in the example above.

Accessing a Derived View in Data Explorer

After you create a derived view, it appears in the list of derived views in Data Explorer. You can click the three dots next to the entry in the list to open a list of options.

• To execute the query, click Query. The contents of the derived view appear in the Data Explorer query view, and you can then click Execute to view the query results.

• To return metadata, click View Details. This action automatically executes, and the results populate under the Details tab.

• You can click Rename to change the name of the derived view, or you can click Delete to remove it from the list.

Modifying a Derived View

To modify the query of a derived view, use one of the methods outlined above to access it. With the derived view open, modify the text in the query field as needed and click Save Changes at the top of the Data Explorer.

To rename a derived view, click the three dots next to the entry in the list and select Rename. Enter the new name in the dialog that appears and click Confirm.

Deleting a Derived View

To delete a derived view, click the three dots next to the entry in the list and select Delete. In the confirmation dialog that appears, click Delete again.

Note: Deleting a derived view does not affect data in the source.