SQL-based views

With SQL views, you can quickly start using ThoughtSpot to get insights from your data. Simply create a connection to your external data warehouse, and you can run SQL queries against it. When you save these queries as SQL views, they become data sources that you can run traditional ThoughtSpot searches on, allowing you to create charts, tables, and Liveboards from the data.

SQL views are very similar to other ThoughtSpot views. Refer to Understanding views for an introduction to views and their use in ThoughtSpot. This article describes a specific type of view, the SQL view, which follows the same rules as other ThoughtSpot views.

Prerequisites

To create a SQL view, you must satisfy the following prerequisites:

  • You must have either the Can administer ThoughtSpot or the Can manage data privilege.

  • If you have row-level security (RLS) requirements, apply row-level security to the view’s underlying table in order to restrict the data shown when searching on the view. You cannot apply RLS directly to a view.

  • You can only create SQL views based on data from Connections.

Creating SQL views

To create a SQL view, follow these steps:

  1. Connect to your data in your external cloud data warehouse. Note that you do not have to select any tables or columns to create the connection and start creating SQL views.

  2. Select Data from the top navigation bar.

  3. From the side navigation bar, select + Create new > Custom SQL view.

  4. Choose a connection to the data you would like to query. Select Choose connection in the side panel, and click on or search for a connection in the dropdown.

    If you did not already create a connection, select Add new connection. Refer to the connections documentation.

  5. After you select the connection, a list of tables appears in the side panel, under the schema and database names. To see a list of the columns in a table, click on the table name. A list of the columns and their data types appears.

  6. In the SQL editor, write your SQL query.

    The SQL you use must be compliant with your data warehouse’s SQL standard.

    SQL views only support SELECT and WITH clauses. Your SQL view can only contain one SQL statement.

    The SQL editor has autocomplete functionality, based on the tables and columns in your connection.

    In the following example, after we type select * from, the SQL editor suggests several tables. When we click on the DIM_RETAPP_PRODUCTS table, the query autocompletes as select * from RETAILAPPAREL.PUBLIC.DIM_RETAPP_PRODUCTS AS d, completing this part of the query and assigning the table an alias.

    SQL view autocomplete gif

  7. Select Run. The results of your SQL query appear in the Result section. You can collapse or expand this section, by selecting the up and down arrows in the upper right of the Results section.

  8. Select Save.

  9. The Save modal appears, and prompts you to name your SQL view. To improve the SQL view’s visibility, optionally add a description.

  10. Select Save again.

  11. To create a copy of the SQL view, select Save as.

  12. To return the SQL view to its last saved state, select Revert. This button only appears after you save the view.

  13. To open the SQL view you just created as a data source, select Open view details. Otherwise, when you finish editing the SQL view, save it and select Exit.

Editing SQL views

If you have can edit privileges on a SQL view, you see the Edit View button on the details page for the view. To edit the SQL query that the view is based on, select Edit View.

Edit SQL view

For information about how to edit the metadata of your view, see Data modeling settings.

Managing SQL views

You can use a SQL view just like any other data source. You can also join a SQL view to other tables or views by defining a relationship. When you save a SQL query as a view, and then use it as a source in a new search, it is similar to doing a nested query in SQL, only much easier to create.

You can perform the same actions on a SQL view that you can perform on a regular view:

Creating a search from a SQL view

After you create a SQL view, you can search on it. You can do this 1 of 2 ways:

  • Navigate to the SQL view you would like to search on, from the Data tab. Select Search this view. This opens the Search Data interface, where you can query your data.

  • Select Search data from the top navigation bar. Select the Choose sources icon, and select your SQL view. Select Done. This opens the Search Data interface, where you can query your data.

Was this page helpful?
×