To use the data retrieved from a search query programmatically, you can first query this data using the ThoughtSpot Search Data API.

It is often difficult to programmatically use the result set of a query that runs in the ThoughtSpot UI search bar. To use the data that we retrieve from a query programmatically, you can use ThoughtSpot Search Data API.

When issuing a query through the ThoughtSpot UI, users make selections to disambiguate a query. Because selection is not possible with an API approach, we modified the API query language to include query disambiguation. See Components of a search query.

Resource URL

post/tspublic/v1/searchdata

Request Parameters

Query Parameter Data Type Description
query_string string The data search query.

See Components of a search query.

data_source_guid string The GUID of the data source, either a worksheet, a view, or a table.

Example: ["4fdf9d2c-6f34-4e3b-9fa6-bd0ca69676e1"]

batchsize integer The batch size for loading search objects.

The system default is -1.

pagenumber integer Alternate way to specify 1-based offset:

indexingOffset = (pageNumber - 1) * batchSize

The system default is -1.

offset integer Specify a 1-based offset.

The system default is -1.

formattype string The format of the data.

Valid values are COMPACT (default) or FULL JSON.

Components of a search query

In ThoughtSpot Query Language, we classify components of a query into various types of tokens: Column, Operator, Value, Date Bucket, Keyword, and Calendar:

Column

Columns must be enclosed in square brackets, [ ].

Example In the query revenue by ship mode, both revenue and ship mode are columns. A valid query for the API is:

[revenue] by [ship mode]
Operator

ThoughtSpot supports various operators such as =, !=, >, >=, <=, <, contains, not contains, and so on. Use these operators in the API query in the same manner as in the UI.

Example Specify revenue over 1000, and limit ship mode to 'air':

[revenue] > 1000 [ship mode] = ‘air’
Value

All user-defined column values and constants must be enclosed within quotes, ’ ’.

When using multiple values, separate them by a comma, ,.

Example When a ThoughtSpot UI query is revenue top 2 ship mode, the equivalent API is:

[revenue] top 2 [ship mode]

Example When a ThoughtSpot UI query is revenue ship mode = air, the equivalent API is:

[revenue] [ship mode] = ‘air’,’ship mode’
Date Bucket

In the ThoughtSpot UI, when there are several date columns, users can bind date bucket tokens to a specific column. When using the API, this binding between the date column and the date bucket must be made explicit. The column with which the date bucket is bound, and the date bucket token, must be separated by a period, ..

Single word date buckets can be expressed as is. Multi-word date buckets must be enclosed within quotes.

Example When a ThoughtSpot UI query is revenue commit date monthly, and if monthly is bound to commit date, the equivalent API is:

[revenue] [commit date].monthly

Example When a ThoughtSpot UI query is revenue day of week = 5, and if day of week is bound to commit date, the equivalent API is:

[revenue] [commit date].'day of week' = 5
Keyword

Use keywords in the API query in the same manner as in the UI.

Example When a ThoughtSpot UI query uses keywords growth of and sort by, the equivalent API form is:

growth of [revenue] by [commit date]
Calendar

You can specify a custom calendar in the query. Use the calendar.calendar_name format explicitly.

When the calendar name contains multiple words, these words must be enclosed in single quotes.

Example When a ThoughtSpot UI query is revenue by commit date fiscal, where the name of the calendar is fiscal, the equivalent API is:

[revenue] by [commit date] calendar.fiscal

Example When a ThoughtSpot UI query is revenue by commit date my calendar, where the name of the calendar is my calendar, the equivalent API is:

[revenue] by [commit date] calendar.'my calendar'

Request example

cURL - COMPACT
curl -X POST --header 'Content-Type: application/json' --header 'Accept: application/json' --header 'X-Requested-By: ThoughtSpot' 'https://<instance>/callosum/v1/tspublic/v1/searchdata?query_string=%5Bsales%5D%20%5Bstore%20region%5D&data_source_guid=06517bd1-84c0-4bc6-bd09-f57af52e8316&batchsize=-1&pagenumber=-1&offset=-1&formattype=COMPACT'
Request URL - COMPACT
https://<instance>/callosum/v1/tspublic/v1/searchdata?query_string=%5Bsales%5D%20%5Bstore%20region%5D&data_source_guid=06517bd1-84c0-4bc6-bd09-f57af52e8316&batchsize=-1&pagenumber=-1&offset=-1&formattype=COMPACT
cURL - FULL
curl -X POST --header 'Content-Type: application/json' --header 'Accept: application/json' --header 'X-Requested-By: ThoughtSpot' 'https://<instance>/callosum/v1/tspublic/v1/searchdata?query_string=%5Bsales%5D%20%5Bstore%20region%5D&data_source_guid=06517bd1-84c0-4bc6-bd09-f57af52e8316&batchsize=-1&pagenumber=-1&offset=-1&formattype=FULL’
Request URL - FULL
https://<instance>/callosum/v1/tspublic/v1/searchdata?query_string=%5Bsales%5D%20%5Bstore%20region%5D&data_source_guid=06517bd1-84c0-4bc6-bd09-f57af52e8316&batchsize=-1&pagenumber=-1&offset=-1&formattype=FULL

Response example

{
  "columnNames": [
    "Store Region",
    "Total Sales"
  ],
  "data": [
    [
      "east",
      18934491.05134509
    ],
    [
      "midwest",
      29157090.327609923
    ],
    [
      "south",
      25484693.074720126
    ],
    [
      "southwest",
      34241076.52103955
    ],
    [
      "west",
      30848491.458509445
    ]
  ],
  "samplingRatio": 1,
  "totalRowCount": 5,
  "rowCount": 5,
  "pageSize": 100000,
  "offset": 0
}

Using the Search Data API

To test the search query API, follow these steps:

Determine the GUID of the data source
  1. In a Web Browser tab, navigate to the following address, and log in as admin user:

    https://<instance>/
    
  2. In the top navigation, click Data.

    Alternatively, navigate to the following address:

    https://<instance>/#/data/tables/
    
  3. Select a data source that you plan to query: a worksheet, a table, or a view.

  4. In the address bar of the Web Browse, note the GUID of the selected data source; it is the last string of the address. For example, in the following address string, the GUID is 9d93a6b8-ca3a-4146-a1a1-e908b71b963f:

     https://<instance>/#/data/tables/9d93a6b8-ca3a-4146-a1a1-e908b71b963f
    
  5. Copy and save the GUID.

Run the Search Data API
  1. In another browser, navigate to the following address:
  https://<instance>/external/swagger/#!/tspublic%2Fv1/searchData
  1. Click on POST /tspublic/v1/searchdataTS.

    The parameter interface appears.

  2. In the Parameters interface, enter the values for the following:

    You can leave other parameters at their default value.

  3. Click Try it out!, and note the results.

    You may wish to check that the same query, when you run it in the ThoughtSpot UI search bar (with slightly different syntax), returns the same data.

Limitations of Search Query API

  • To avoid join path ambiguities, a query can use only a single data source.

  • Search execution of query strings is case insensitive.

  • All Column names in the data source must have unique names that also pass the “case insensitivity” test.

    For example, Columns [Revenue] and [revenue] are not unique.

  • Column names cannot contain square brackets, [ or ].

  • Values must be enclosed in quotes, ‘’, but they cannot contain quotes.

  • The API does not support in-query formula definitions. To use a formula, first create it on the worksheet or a table using the ThoughtSpot UI, and then use the named formula inside the API query.

  • Users must be authenticated and have read access to the data source.