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
Note: Data Explorer cannot modify data, generate reports, or transfer data between data sources. Instead, use a client tool to perform operations on data from your connected data sources.
When you open Data Explorer, the interface displays a navigation pane on the left and the query editor on the right.
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:
Click Select a Connection to open a drop-down menu of your connections.
- 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.
- Choose the data that you want to query.
- 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.
Set the Limit field to the number of results that you want to return. By default, the maximum number for this field is 100.
- Click Execute to run the query.
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.
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:
Click Save to open the Save Query modal.
Enter a name for the query and click Confirm.
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.
Creating a Derived View
To create a new derived view, follow these steps:
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 box appears.
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.
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.
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 modal 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 modal that appears, click Delete again.
Note: Deleting a derived view does not affect data in the source.