"metadata": [
{
"identifier": "my_worksheet",
"type": "LOGICAL_TABLE"
}
]
Data and Report APIs
Data APIsπ
ThoughtSpot provides the following REST API v2 endpoints to fetch data:
-
POST /api/rest/2.0/searchdata
to search data from a given data source. -
POST /api/rest/2.0/metadata/liveboard/data
to get data from a Liveboard. -
POST /api/rest/2.0/metadata/answer/data
to get data from a saved Answer.
Search data APIπ
The /api/rest/2.0/searchdata
endpoint requires you to specify the data source object ID and a query string for a successful API call. You can also define additional parameters such as runtime_filter
, runtime_sort
, and runtime_param_override
to apply runtime overrides on the data set.
Data sourceπ
To search data via API call, you require at least view access to the data source object. The data source object can be a Worksheet, View, or Table.
You can specify the data source object GUID in the logical_table_identifier
. The search data endpoint doesnβt support searching data from multiple Worksheets, Views, or Tables in a single API request.
To find the GUID of the Worksheet, View, or Table, use one of the following methods:
- Get data object GUID via API
-
Send an API request to the
/api/rest/2.0/metadata/search
endpoint with the following parameters in the metadata array:ExampleIf you donβt know the exact name of the data source object, specify the metadata
type
asLOGICAL_TABLE
in your API request, and then copy the GUID of the data object from the API response. - Find the GUID of the data object via UI
-
-
Log in to your ThoughtSpot application instance:
-
Navigate to Data.
If you are using the new experience, click the Application switcher > Data workspace
https://<your-thoughtspot-instance>/#/data/tables/
-
On the Data > Home page, select the data object. For example, if the data source object is a Worksheet, click Worksheets and then open the Worksheet.
-
In the address bar of the web browser, note the GUID of the data source object. For example, in the following address string, the GUID is
9d93a6b8-ca3a-4146-a1a1-e908b71b963f
:
https://<your-thoughtspot-instance>/#/data/tables/9d93a6b8-ca3a-4146-a1a1-e908b71b963f
-
Copy the GUID.
-
Search queryπ
When issuing a query through the ThoughtSpot UI, users make selections to disambiguate a query. It is often difficult to programmatically use the result set of a query that runs in the ThoughtSpot UI search bar. Because the selection is not possible with an API approach, the API query language is modified to include query disambiguation.
Components of a search queryπ
In ThoughtSpot Query Language, the components of a query are classified into various types of tokens:
- Column
-
Columns must be enclosed in square brackets, [ ].
For 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.
For example, specify revenue over 1000, and limit ship mode to 'air':
[revenue] > 1000 [ship mode] = 'air'
- Value
-
String (text) and date values must be enclosed within quotes, ''. Do not use quotes for numeric values, except for dates.
When using multiple values, separate them by a comma.
For example, when a ThoughtSpot UI query is revenue top 2 ship mode, the equivalent API query is:
[revenue] top 2 [ship mode]
For example, when a ThoughtSpot UI query is revenue ship mode = air, the equivalent API query is:
[revenue] [ship mode] = 'air'
- 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. Use a period to separate the column with which the date bucket is bound, and the date bucket token.
Single word date buckets can be expressed as is. Multi-word date buckets must be enclosed within quotes.
For example, when a ThoughtSpot UI query is
revenue commit date monthly
, and if monthly is bound to commit date, the equivalent API query is:[revenue] [commit date].monthly
When a ThoughtSpot UI query is
revenue day of week = 5
, and if day of week is bound to commit date, the equivalent API query is as follows:[revenue] [commit date].'day of week' = 5
- Keyword
-
Use keywords in the API query in the same manner as in the UI.
For example, when a ThoughtSpot UI query uses keywords growth of and sort by, the equivalent API query is as follows:
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, enclose these words in single quotes.
For example, when a ThoughtSpot UI query is revenue by commit date fiscal, where the name of the calendar is fiscal, the equivalent API query is as follows:
[revenue] by [commit date] calendar.fiscal
For 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 query is:
[revenue] by [commit date] calendar.'my calendar'
Functional limitationsπ
-
To avoid join path ambiguities, a query can use only a single data source.
-
Search execution of query strings is not case-sensitive.
-
All column names in the data source must have unique names and must 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, 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.
-
Your browser locale must be
en-US
. Swagger does not accept other variations of English, such as British English, or other languages. Your search keywords must also be in American English. Your column names and other data values do not need to be in American English. You can change your preferred locale toen-US
in your browser settings.
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][store]",
"logical_table_identifier": "cd252e5c-b552-49a8-821d-3eadaa049cca",
}'
Fetch Liveboard Data APIπ
To get data from a Liveboard object and its visualizations via POST /api/rest/2.0/metadata/liveboard/data
endpoint, you require at least view access to the Liveboard.
The API request body must include the name or GUID of the Liveboard to fetch data. To get specific visualizations from a given Liveboard, add the names or GUIDs of the visualizations in the visualization_identifiers
array.
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": "d084c256-e284-4fc4-b80c-111cb606449a",
"data_format": "COMPACT",
"visualization_identifiers": [
"a9655c18-9855-4a73-9e7b-ff4fb6da334b",
"bf4c9814-82c1-4ec4-b879-57eae2134cb4",
"8c46d2b6-94c7-4ba7-a628-6e74e297f973",
"f6ef5d1f-cddb-4547-8b66-af4d5f4da5ad"
]
}'
Liveboard data with unsaved changesπ
To download or retrieve a Liveboard with unsaved changes, pass the transient_pinboard_content
script with getExportRequestForCurrentPinboard
method in the browser fetch request .
function getExportRequestForCurrentPinboard(frame: HTMLIframeElement): Promise<string>;
The promise returned resolves to a string that contains the transient Liveboard content, which is encoded as JSON and is sent to the Liveboard API endpoint with the transient content key. This content resembles the current Liveboard as is, including the unsaved changes if any, including unsaved changes to the following:
-
Liveboard filters
-
Runtime filters applied on visualizations on a Liveboard
-
Liveboard layout
If the new Liveboard experience is enabled, the transient content includes ad hoc changes to visualizations such as sorting, toggling of legends, and data drill down.
< iframe src = "http://ts_host:port/" id = "ts-embed" > < /iframe>
< script src = "/path/to/liveboard.js" > < /script>
< script >
const embed = new LiveboardEmbed("#embed", {
frameParams: {},
});
async function liveboardData() {
const transientPinboardContent = await embed.trigger(HostEvent.getExportRequestForCurrentPinboard);
const liveboardDataResponse = await fetch("https://ts_host:port/api/rest/2.0/metadata/liveboard/data", {
method: "POST",
body: createFormDataObjectWith({
"data_format": "COMPACT",
"transient_content": transientPinboardContent,
}),
});
}
< /script>
Fetch Answer Data APIπ
To get data from a saved Answer object via /api/rest/2.0/metadata/answer/data
, you need at least view access to the saved Answer.
The API request body must include the name or GUID of the 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": "f605dbc7-db19-450b-8613-307118f74c3c",
}'
Report APIsπ
ThoughtSpot provides the following REST API v2 endpoints to fetch data:
-
POST /api/rest/2.0/report/liveboard
Download data from a Liveboard in the PDF, PNG, CSV, or XLSX file format. -
POST /api/rest/2.0/report/answer
Download data from a saved Answer in the PDF, PNG, CSV, or XLSX file format.
Liveboard Report APIπ
To download a Liveboard report via /api/rest/2.0/report/liveboard
API, you need DATADOWNLOADING
privilege and at least view access to the Liveboard.
In the POST
request body, specify the GUID or name of the Liveboard as metadata_identifier
. To download reports with specific visualizations, add GUIDs or names of the visualizations in the visualization_identifiers
.
The default file_format
is CSV. For PDF and PNG file format, you can specify additional parameters to customize the page orientation and include or exclude the cover page, logo, footer text, and page numbers.
For PNG file format, you can include cover page and filters.
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": "9bd202f5-d431-44bf-9a07-b4f7be372125",
"file_format": "PNG",
"visualization_identifiers": [
"9bd202f5-d431-44bf-9a07-b4f7be372125",
"9bd202f5-d431-44bf-9a07-b4f7be372125",
"9bd202f5-d431-44bf-9a07-b4f7be372125"
],
"png_options": {
"include_cover_page": true,
"include_filter_page": true
}
}'
Liveboard data with unsaved changesπ
To download or retrieve a Liveboard with unsaved changes, pass the transient_pinboard_content
script with getExportRequestForCurrentPinboard
method in the browser fetch request .
function getExportRequestForCurrentPinboard(frame: HTMLIframeElement): Promise<string>;
The promise returned resolves to a string that contains the transient Liveboard content, which is encoded as JSON and is sent to the Liveboard API endpoint with the transient content key. This content resembles the current Liveboard as is, including the unsaved changes if any, including unsaved changes to the following:
-
Liveboard filters
-
Runtime filters applied on visualizations on a Liveboard
-
Liveboard layout
If the new Liveboard experience is enabled, the transient content includes ad hoc changes to visualizations such as sorting, toggling of legends, and data drill down.
< iframe src = "http://ts_host:port/" id = "ts-embed" > < /iframe>
< script src = "/path/to/liveboard.js" > < /script>
< script >
const embed = new LiveboardEmbed("#embed", {
frameParams: {},
});
async function liveboardData() {
const transientPinboardContent = await embed.trigger(HostEvent.getExportRequestForCurrentPinboard);
const liveboardDataResponse = await fetch("https://ts_host:port/api/rest/2.0/report/liveboard", {
method: "POST",
body: createFormDataObjectWith({
"transient_content": transientPinboardContent,
}),
});
}
< /script>
Answer Report APIπ
To download Answer data via /api/rest/2.0/report/answer
API, you need DATADOWNLOADING
privilege and at least view access to the saved Answer.
In the request body, specify the GUID or name of the Answer object as metadata_identifier
.
You can download thw Answer data in the PNG
, PDF
, CSV
, or XLSX
format. The default file_format
is CSV.
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": "9bd202f5-d431-44bf-9a07-b4f7be372125",
"file_format": "PNG"
}'
Pagination settings for Data and Report APIsπ
When you make REST API calls to some v2 Data endpoints to query data, the API may return many rows of data in response. By default, the following parameters are set in API requests to the v2 Data API endpoints:
{
"data_format": "COMPACT",
"record_offset": 0,
"record_size": 10
}
You can set record_size
to -1
and record_offset
to 0
for precise and complete results. The APIs return a maximum of 100000 rows of data at any given time. If you must retrieve a higher number of rows in an API call, contact ThoughtSpot Customer Support to increase the row size limit. However, if the record size and number of rows are high, the API may take a while to fetch the data, and the request may time out.
Runtime overridesπ
The Data API endpoints support the following runtime overrides:
-
Runtime filters
-
Runtime sorting of columns
-
Runtime Parameters
Runtime filtersπ
To add runtime filters, in the runtime_filter
property, add the col1
, op1
, and val1
parameters JSON key-value format:
"runtime_filter": {
"col1": "type",
"op1": "EQ",
"val1": "roasted",
}
To add additional filters, increment the number at the end of each parameter for each filter: for example, col2, op2, val2, and so on.
"runtime_filter": {
"col1": "type",
"op1": "EQ",
"val1": "roasted",
"col2": "tea",
"op2": "EQ",
"val2": "barley"
}
Some operators such as allow more than one value in the val
parameter:
"runtime_filter": {
"col1": "tea",
"op1": "CONTAINS",
"val1": [
"barley",
"mint"
],
"col2": "type",
"op2": "CONTAINS",
"val2": [
"roasted",
"loose leaves"
]
}
For more information, see Supported runtime filter operators and Apply runtime filters via REST APIs.
Runtime parametersπ
To add runtime Parameters, in the runtime_param_override
property, add the param1, and `paramVal1
parameters JSON key-value format. The Parameter value must be defined as per the data type. For example, Date Param
and Date List Param
Parameters, specify Epoch time as value.
To apply Parameter overrides on Liveboards and Answers, ensure that the Parameters are configured in the Worksheet used for generating Liveboard visualizations and Answer.
"runtime_param_override": {
"param1": "Double List Param",
"paramVal1": 0.5
}
To add additional Parameter overrides, increment the number at the end of each parameter: for example, paramVal2, and so on.
"runtime_param_override": {
"param1": "Double List Param",
"paramVal1": 0.5,
"param2": "Date Param",
"paramVal2": 1696932000
}
For more information, see Runtime Parameter overrides.
Runtime sortπ
To sort columns on a Liveboard or Answer, define runtime sort properties in runtime_sort
as a key-value pair in JSON format. The runtime_sort
object allows sortCol1
and asc1
properties. To sort more columns, increment the number at the end of the parameter for each key: for example, sortCol2
, asc2
, sortCol3
, asc3
, and so on.
"runtime_sort": {
"sortCol1": "sales",
"asc1": true,
"sortCol2": "region",
"asc2": false
}
For more information, see Runtime sorting of columns.