new Date('2020-05-22').getTime() / 1000
Runtime filters
Runtime filters allow you to apply filters on a Liveboard, Answer, visualization, or conversation session with Spotter. These filters are not persistent filters saved in the object, but are applied dynamically at runtime. You can use runtime filters to pre-filter content for your application users based on context and embedding app’s requirements.
Overview🔗
Runtime filters can be applied using one of the following options:
-
Via Visual Embed SDK
Use theruntimeFilters
property in the Visual Embed SDK for embedded objects.
The SDK also provides host events to update filters. -
Via REST APIs Use REST API requests to apply runtime overrides on Liveboard, Answer, or visualization data.
-
Via URL query parameters
Pass filter properties as query parameters in the URL.
Important
|
|
Runtime filter properties🔗
A runtime filter consists of the following attributes:
- column name
-
String. Name of the column to filter on. For example,
item type
orproduct
. This attribute is defined ascol1
,col2
,col3
in the object URLs and REST API requests, and ascolumnName
in theruntimeFilters
array in the Visual Embed SDK. - operator
-
String. The runtime filter operator. For example,
EQ
orIN
. In the Visual Embed SDK, operators are specified asoperator
in theruntimeFilters
array, whereas in the object URLs and REST API requests, you can define the operator asop1
,op2
, andop3
. - values
-
String, Integer, or Boolean. The values to filter by. For example, if the column name is defined as
State
, the value can be the name of the state likeMichigan
.This attribute is defined as
val1
,val2
,val3
in the object URLs and REST API requests, and asvalues
in theruntimeFilters
array in the Visual Embed SDK.Some operators like
EQ
andLE
accept a single operand, whereasBW_INC_MAX
,BW_INC_MIN
,BW_INC
,BW
, andIN
accept multiple operands.
Supported data types🔗
You can apply filters on these data types:
-
VARCHAR
-
BIGINT
-
INT
-
FLOAT
-
DOUBLE
-
BOOLEAN
-
DATE
-
DATE_TIME
-
TIME
Important
|
For the For example, if you want to filter data for 2020-05-22, you can specify the date value in the Epoch time format as |
Supported filter operators🔗
Operator | Description | Number of Values |
---|---|---|
| equals | 1 |
| Not equal to | 1 |
| less than | 1 |
| less than or equal to | 1 |
| greater than | 1 |
| greater than or equal to | 1 |
| contains | 2 |
| begins with | 1 |
| ends with | 1 |
| between inclusive of the higher value | 2 |
| between inclusive of the lower value | 2 |
| between inclusive | 2 |
| between non-inclusive | 2 |
| is included in this list of values | multiple |
| is not included in this list of values | multiple |
Runtime filters in Visual Embed SDK🔗
If you are embedding Spotter, Liveboard, visualization or the full application using Visual Embed SDK, you can apply filters using the runtimeFilters
property. In the full app embed mode, ThoughtSpot applies runtime filters on all Liveboard, visualization, and Answer objects in the embedded app.
The following example shows how to apply runtime filters on an embedded visualization in the SDK. Here, the runtime filter is operating on the Revenue
column to filter the data matching 100000
.
const liveboardEmbed = new LiveboardEmbed('#tsEmbed', {
... // other embed view config
liveboardId: '133e6c5f-e522-41a0-b0ad-b9c3b066e276',
vizId: '28b73b4a-1341-4535-ab71-f76b6fe7bf92',
runtimeFilters: [{
columnName: 'Revenue',
operator: RuntimeFilterOp.EQ,
values: ['100000']
}]
});
In the following example, runtime filters are applied on a Spotter conversation session with a pre-defined search query string:
const spotterEmbed = new SpotterEmbed('#tsEmbed', {
... // other embed view config
worksheetId: "cd252e5c-b552-49a8-821d-3eadaa049cca",
searchOptions: {
searchQuery: 'sales data for west coast',
},
runtimeFilters: [{
columnName: 'Item type',
operator: RuntimeFilterOp.EQ,
values: ['jackets']
}]
});
Spotter applies the specified filters to the data and returns an answer for the initial query passed in the embed code. Unless the filters are explicitly overridden, runtime filters will continue to be applied to all answers generated for subsequent queries within the same conversation session.
Applying multiple runtime filters🔗
The following examples show how to apply multiple runtime filters on Liveboard visualizations using the SDK:
const liveboardEmbed = new LiveboardEmbed('#tsEmbed', {
... // other embed view config
liveboardId: '543619d6-0015-4667-b257-eff547d13a12',
runtimeFilters: [{
columnName: 'item type', // eg: color
operator: RuntimeFilterOp.EQ,
values: ['Jackets'] // eg: red
},
{
columnName: 'Region',
operator: RuntimeFilterOp.IN,
values: ['Midwest', 'East', 'West']
},
{
columnName: 'Date',
operator: RuntimeFilterOp.EQ,
values: ['1656680400']
}
]
});
AND and OR conditions in filters🔗
If the Liveboard or Answer already has one or more filters applied, runtime filters will act as an AND
condition. This means that all filter conditions, including those supplied in the runtime filters and Liveboard filter, must match to get the desired data.
In the following example, the OR condition is applied; That is, if at least one condition matches, the Liveboard returns data.
runtimeFilters: [{
columnName: 'product name',
operator: RuntimeFilterOp.CONTAINS,
values: ['bag', 'jackets']
}]
However, when multiple runtime filters are applied, or when the Liveboard already has a filter applied, the data must match all filter conditions.
runtimeFilters: [{
columnName: 'product name',
operator: RuntimeFilterOp.CONTAINS,
values: ['vest']
},
{
columnName: 'product name',
operator: RuntimeFilterOp.CONTAINS,
values: ['hoody']
}
]
Runtime filter operator examples🔗
Operator | Example (Visual Embed SDK) |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
See also, AND/OR conditions in filters, for information about the AND and OR condition for filters. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Example🔗
The following video shows how to apply multiple runtime filters on a Liveboard.
Adjust runtime filters using SDK events🔗
Runtime filters can be set prior to the load within the configuration object of the loaded embed component:
const liveboardEmbed = new LiveboardEmbed('#embed-container', {
... // other options
runtimeFilters: [{
columnName: 'state',
operator: RuntimeFilterOp.EQ,
values: ['california'],
}, ],
})
After loading the embedded object, runtime filters can be adjusted using the HostEvent.UpdateRuntimeFilters
event:
liveboardEmbed.trigger(HostEvent.UpdateRuntimeFilters, [{
columnName: "state",
operator: RuntimeFilterOp.EQ,
values: ["michigan"]
},
{
columnName: "item type",
operator: RuntimeFilterOp.EQ,
values: ["Jackets"]
}
])
Note
|
Spotter embed does not support updating filters via host and embed events. |
Apply runtime filters on embedded objects without the SDK🔗
If you are embedding a Liveboard or visualization without using the Visual Embed SDK, you can append the filters in the embedded object URL as shown in these examples:
https://{ThoughtSpot-Host}/?embedApp=true&col1=State&op1=EQ&val1=michigan#/embed/viz/{Liveboard_id}/{visualization_id}
https://{ThoughtSpot-Host}/?embedApp=true&col1=State&op1=EQ&val1=michigan&col2=product&op2=BEGINS_WITH&val2=Travel#/embed/viz/{Liveboard_id}/{visualization_id}
URL format when embedding without SDK🔗
If embedding a ThoughtSpot Liveboard without the SDK, ensure that add the runtime filters before #/path
in the URL as shown in the following example:
https://{ThoughtSpot-Host}/?embedApp=true&col1=State&op1=EQ&val1=michigan#/embed/viz/{Liveboard_id}/{visualization_id}
For more information, see Embed without SDK.
Runtime filters via REST APIs🔗
REST API v2.0 endpoints🔗
The v2.0 Data and Report endpoints support runtime filters in REST API requests. Note that you can add additional filters by incrementing the number at the end of each parameter: for example, col2, op2, val2. Some operators, such as CONTAINS
and IN
, allow passing multiple values in the val
attribute.
Report APIs🔗
-
POST /api/rest/2.0/report/liveboard
Allows downloading Liveboard data in PDF, XLSX, CSV, and PNG format.
curl -X POST \ --url 'https://{ThoughtSpot-Host}/api/rest/2.0/report/liveboard' \ -H 'Authorization: Bearer {access-token} '\ -H 'Content-Type: application/json' \ --data-raw '{ "metadata_identifier": "0c68a0a1-930b-4ba0-b7a0-59ea49b09848", "file_format": "PDF", "runtime_filter": { "col1": "item type", "op1": "CONTAINS", "val1": "Shirts", "col2": "region", "op2": "EQ", "val2": "West", "col3": "state", "op3": "IN", "val3": [ "California", "Nevada" ] } }'
-
POST /api/rest/2.0/report/answer
Allows downloading Answer data in PDF, XLSX, CSV, and PNG format.
curl -X POST \ --url 'https://{ThoughtSpot-Host}/api/rest/2.0/report/answer' \ -H 'Authorization: Bearer {access-token} '\ -H 'Content-Type: application/json' \ --data-raw '{ "metadata_identifier": "fa68ae91-7588-4136-bacd-d71fb12dda69", "file_format": "XLSX", "runtime_filter": { "col1": "item type", "op1": "CONTAINS", "val1": [ "Bags", "Shirts" ], "col2": "state", "op2": "EQ", "val2": "California" } }'
Data APIs🔗
-
POST /api/rest/2.0/searchdata
Allows searching data from a specific data source object.
curl -X POST \ --url 'https://{ThoughtSpot-Host}/api/rest/2.0/searchdata' \ -H 'Authorization: Bearer {access-token}' \ -H 'Accept: application/json'\ -H 'Content-Type: application/json' \ --data-raw '{ "query_string": "[sales] [item type]", "logical_table_identifier": "cd252e5c-b552-49a8-821d-3eadaa049cca", "data_format": "COMPACT", "record_offset": 0, "record_size": 10, "runtime_filter": { "col1": "item type", "op1": "EQ", "val1": "Bags" } }'
-
POST /api/rest/2.0/metadata/liveboard/data
Gets data from a Liveboard.curl -X POST \ --url 'https://{ThoughtSpot-Host}/api/rest/2.0/metadata/liveboard/data' \ -H 'Authorization: Bearer {access-token}'\ -H 'Accept: application/json'\ -H 'Content-Type: application/json' \ --data-raw '{ "metadata_identifier": "3123341c-8ad4-43ad-8c94-52d75b96d8fb", "data_format": "COMPACT", "record_offset": 0, "record_size": 10, "runtime_filter": { "col1": "type", "op1": "EQ", "val1": "roasted", "col2": "tea", "op2": "EQ", "val2": "barley" } }'
-
POST /api/rest/2.0/metadata/answer/data
Gets data from a saved Answer.curl -X POST \ --url 'https://{ThoughtSpot-Host}/api/rest/2.0/metadata/answer/data' \ -H 'Authorization: Bearer {access-token}'\\ -H 'Accept: application/json'\ -H 'Content-Type: application/json' \ --data-raw '{ "metadata_identifier": "36ec5bde-e0c9-4a2b-9dc4-64758fac21d6", "data_format": "COMPACT", "record_offset": 0, "record_size": 10, "runtime_filter": { "col1": "type", "op1": "EQ", "val1": "roasted", "col2": "tea", "op2": "EQ", "val2": "mint" } }'
REST API v1 endpoints🔗
To apply runtime filters on a Liveboard object in a REST API request, add the runtime filters to the API request URL as shown here:
https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/pinboarddata?id={Liveboard_id}&col1={column-name}&op1={operator}&val1={value}
https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/pinboarddata?id=e36ee65e-64be-436b-a29a-22d8998c4fae&col1=State&op1=EQ&val1=California
The following example shows how to apply a runtime filter on a visualization object of a Liveboard:
https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/pinboarddata?id={Liveboard_id}&vizid={visualization_id}&col1={column-name}&op1={operator}&val1={value}
https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/pinboarddata?id=543619d6-0015-4667-b257-eff547d13a12&vizid=%5B%224ff5b939-453d-40ff-8fc2-a1d972047c86%22%5D&col1=State&op1=EQ&val1=California
The following is another example of a REST API request URL with a filter. Here the runtime filter is operating on the column Category
and returning values that are equal to mfgr%2324
.
https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/pinboarddata? id=e36ee65e-64be-436b-a29a-22d8998c4fae&col1=Category &op1=EQ&val1=mfgr%2324
Apply additional filters🔗
You can add additional filters by incrementing the number at the end of each parameter in the runtime filter for each filter you add, for example, col2, op2, val2, and so on. To add additional filters on a particular column, you can specify multiple values by separating them with an ampersand (&) as shown in the example here:
val1=foo&val1=bar
You can also use the IN
operator for multiple values, as shown in this example:
col1=<column_name>&op1=IN&val1=<value>&val1=<value>
The following example passes multiple variables to a single column as well as multiple columns. It shows that the data values are returned as epochs.
col1=region&op1=IN&val1=midwest&val1=south&val1=northeast&col2=date&op2=BET&val2=<epoch_start>&val2=<epoch_end>
Apply runtime filters on a Liveboard or visualization URL🔗
The following examples show the runtime filter query string in a Liveboard URL:
https://{ThoughtSpot-Host}/?col1=State&op1=EQ&val1=California#/pinboard/d084c256-e284-4fc4-b80c-111cb606449a
You can apply multiple filters in the same URL, as shown in this example:
https://{ThoughtSpot-Host}/?col1=State&op1=EQ&val1=California&col2=product&op2=BEGINS_WITH&val2=Travel#/pinboard/d084c256-e284-4fc4-b80c-111cb606449a
You can also apply multiple filters with multiple values, as shown in this example:
https://<thoughtspot-host>/?col1=State&op1=IN&val1=California&val1=Michigan&col2=item%20type&op2=EQ&val2=Jackets#/pinboard/d084c256-e284-4fc4-b80c-111cb606449a
Limitations of runtime filters🔗
-
The
DATE
andDATE_TIME
data types must be specified as EPOCH time (Unix or POSIX time) in runtime filters. -
Runtime filters work only on Answers and Liveboard visualizations built from Models. Runtime filters on visualizations and Answers built directly from Tables, Views, and SQL Views do not work because the possibility of multiple join paths and join path choice is not supported as input in runtime filters.
-
Runtime filters do not allow you to apply
HAVING
filters in the URL parameters.