Database APIs
The Database API endpoints allow you to get details of your databases and their schemas, create tables within a database, and run TQL commands in Falcon-based ThoughtSpot deployments.
To modify the schema of a database or add a new table to a database, you must have admin access to the database and its objects.
Supported operations
|
|
|
|
|
|
Get a list of tables in a database
To get a list of all tables in a database and schema, send a GET request to the /tspublic/v1/database/tables endpoint.
Request parameters
| Query parameter | Description |
|---|---|
|
String. Name or GUID of the database from which you want to fetch the tables. |
|
String. Name of the database schema. |
Example request
curl -X GET \
--header 'Accept: application/json' \
--header 'X-Requested-By: ThoughtSpot' \
'https://ThoughtSpot-host>/callosum/v1/tspublic/v1/database/tables?database=usage_stats&schema=falcon_default_schema'https://<ThoughtSpot-host>/callosum/v1/tspublic/v1/database/tables?database=usage_stats&schema=falcon_default_schema
Get a list of schemas in a database
To get a list of schemas in a database, send a GET request to the /tspublic/v1/database/schemas endpoint.
Request parameters
| Query parameter | Description |
|---|---|
|
String. Name or GUID of the database for which you want to fetch the schemas. |
Example request
curl -X GET \
--header 'Accept: application/json' \
--header 'X-Requested-By: ThoughtSpot' \
'https://<ThoughtSpot-host>/callosum/v1/tspublic/v1/database/schemas?database=b87a99b8-4a1b-4d94-bd57-e1c9971722c8'https://<ThoughtSpot-host>/callosum/v1/tspublic/v1/database/schemas?database=b87a99b8-4a1b-4d94-bd57-e1c9971722c8
Get a list of databases
To get a list of all databases associated with your ThoughtSpot application instance, send a GET request to the /tspublic/v1/database/databases endpoint.
Example request
curl -X GET \ --header 'Accept: application/json' \ --header 'X-Requested-By: ThoughtSpot' \ 'https://<ThoughtSpot-host>/callosum/v1/tspublic/v1/database/databases'
https://<ThoughtSpot-host>/callosum/v1/tspublic/v1/database/databases
Example response
If the request is successful, the API returns a list of databases associated with your ThoughtSpot instances.
[
"b7d11389-a8e1-4f8c-851b-816148fd6508",
"usage_stats",
"log_replayer",
"analyze_scenarios",
"a8bb3bcc-a53f-43bf-a192-a877e88c8196",
"623617f2-ba5a-45a8-aa18-27a951939ece",
"test123456y",
"ac55d819-bd12-4a00-bec8-a6e0bcdf2843",
"049f092f-5893-469f-a899-7f7ac732d417",
"43fc17b4-e97c-4a7d-a19f-fd03cbc18d00",
"DataConnect",
"2b7c4240-159c-4cdd-95df-ce80ccc553f0",
"599c4f73-940f-4cd8-9276-5f64089453aa",
"MarketSpot",
"53cd18f9-b0af-4223-9e93-d50657e77fda"
]Get details of a table
To get details of a particular table, send a GET request to the /tspublic/v1/database/databases endpoint.
Request parameters
| Query parameter | Description |
|---|---|
|
String. Name or GUID of the database. |
|
String. Name of the schema. |
|
String. Name of the table. |
Example request
curl -X GET \ --header 'Accept: application/json' \ --header 'X-Requested-By: ThoughtSpot' \ 'https://<ThoughtSpot-host>/callosum/v1/tspublic/v1/database/getTableDetail?database=usage_stats&schema=falcon_default_schema&table=table_usage'
https://<ThoughtSpot-host>/callosum/v1/tspublic/v1/database/getTableDetail?database=usage_stats&schema=falcon_default_schema&table=table_usage
Example response
If the request is successful, the API returns the table details such as columns and joins.
{
"column": [
{
"data_type": "TYPE_VAR_CHAR",
"size": 0,
"id": {
"guid": "0197db8b-b2d8-4df8-b18d-3832624e8bdd",
"name": "incident_id"
},
"datetime": "TYPE_NOT_DATE",
"compression_scheme": "AUTO"
},
{
"data_type": "TYPE_VAR_CHAR",
"size": 0,
"id": {
"guid": "25b86811-3afb-4822-9d77-4b20fe6fdbd1",
"name": "table_name"
},
"datetime": "TYPE_NOT_DATE",
"compression_scheme": "AUTO"
},
{
"data_type": "TYPE_VAR_CHAR",
"size": 0,
"id": {
"guid": "a8b96637-a027-40ca-b55e-6a9e14233606",
"name": "column_name"
},
"datetime": "TYPE_NOT_DATE",
"compression_scheme": "AUTO"
},
{
"data_type": "TYPE_VAR_CHAR",
"size": 0,
"id": {
"guid": "f90407d8-c566-4c73-895a-646eb436db11",
"name": "column_guid"
},
"datetime": "TYPE_NOT_DATE",
"compression_scheme": "AUTO"
},
{
"data_type": "TYPE_INT64",
"size": 0,
"parsing_hint": {
"date_format": "%Y%m%d %H:%M:%S"
},
"id": {
"guid": "7b1bdfd6-597e-41cb-8410-4dce9c1ad3c1",
"name": "upload_ts"
},
"datetime": "TYPE_DATE_TIME",
"compression_scheme": "AUTO"
}
],
"id": {
"guid": "f5431116-a86c-40c1-9f4b-3e52374b0a82",
"name": "table_usage"
},
"version": 0,
"primary_key": {
"column": [
{
"guid": "0197db8b-b2d8-4df8-b18d-3832624e8bdd",
"name": "incident_id"
},
{
"guid": "f90407d8-c566-4c73-895a-646eb436db11",
"name": "column_guid"
}
]
},
"relation": [
{
"type": "FOREIGNKEY",
"column": [
{
"source": {
"guid": "25b86811-3afb-4822-9d77-4b20fe6fdbd1",
"name": "table_name"
},
"target": {
"guid": "ad402f95-d112-4d5a-a0eb-d7615adf9463",
"name": "table_name"
},
"target_table": {
"name": "use_case"
},
"target_schema": {
"name": "falcon_default_schema"
},
"target_database": {
"name": "usage_stats"
},
"source_table": {
"name": "table_usage"
},
"source_schema": {
"name": "falcon_default_schema"
},
"source_database": {
"name": "usage_stats"
}
}
],
"id": {
"guid": "d61220ae-b150-49a1-98e0-d6d4780db8df"
}
}
]
}Create tables
To create tables in a database, send a POST request to the /tspublic/v1/database/createtable/ API endpoint.
Request parameters
| Form parameter | Description |
|---|---|
|
Boolean. Set this flag to |
|
String. The schema of the request that needs to be generated. |
Example request
curl -X POST \
--header 'Content-Type: application/x-www-form-urlencoded' \
--header 'Accept: application/json' --header 'X-Requested-By: ThoughtSpot' -d 'create_db=true&ddl_schema={"database":{"name":"DataFlow_DB"},"schema":{"name":"falcon_default_schema"},"table":{"id":{"name":"test_table1"},"primary_key":[{"name":"test_pk"}],"column":[{"id":{"name":"test_pk"},"size":0,"data_type":"TYPE_INT32"},{"id":{"name":"test_col1"},"size":0,"data_type":"TYPE_FLOAT"},{"id":{"name":"test_col2"},"data_type":"TYPE_INT64","datetime":"TYPE_DATE"}{"id":{"name":"test_col3"},"size":10,"data_type":"TYPE_VAR_CHAR"}]}}'
'https://<ThoughtSpot-host>/callosum/v1/tspublic/v1/database/createtable'https://<ThoughtSpot-host>/callosum/v1/tspublic/v1/database/createtable
Example DDL schema for table creation
{
"database":{
"name":"DataFlow_DB"
},
"schema":{
"name":"falcon_default_schema"
},
"table":{
"id":{
"name":"test_table1"
},
"primary_key":[
{
"name":"test_pk"
}
],
"column":[
{
"id":{
"name":"test_pk"
},
"size":0,
"data_type":"TYPE_INT32"
},
{
"id":{
"name":"test_col1"
},
"size":0,
"data_type":"TYPE_FLOAT"
},
{
"id":{
"name":"test_col2"
},
"data_type":"TYPE_INT64",
"datetime":"TYPE_DATE"
}{
"id":{
"name":"test_col3"
},
"size":10,
"data_type":"TYPE_VAR_CHAR"
}
]
}
}Example response
If the request is successful, the API returns the following response:
{
"logical_table_header": {
"id_guid": "322d9ff5-3d2a-4f40-b2cc-3e2c23a1ac55",
"name": "test_table1",
"author_guid": "59481331-ee53-42be-a548-bd87be6ddd4a",
"author_name": "tsadmin",
"author_display_name": "Administrator",
"created": 1638538990887,
"modified": 1638538991877,
"modified_by": "59481331-ee53-42be-a548-bd87be6ddd4a",
"generation_num": 500,
"owner_guid": "322d9ff5-3d2a-4f40-b2cc-3e2c23a1ac55",
"deleted": false,
"hidden": false,
"database_stripe": "DataFlow_DB",
"schema_stripe": "falcon_default_schema",
"type": "ONE_TO_ONE_LOGICAL",
"metadata_type": "LOGICAL_TABLE"
},
"physical_table_id": "a97af543-b122-4e1a-b425-bffc4ad3dbe4"
}Run a given TQL command
To perform administrative tasks directly in the database, you can use the ThoughtSpot SQL Command Line (TQL). TQL supports many, but not all, common SQL commands.
The /tspublic/v1/database/run allows you to run TQL commands to create or update a database schema.
Request parameters
| Form parameter | Description |
|---|---|
|
String. A JSON array of SQL statements separated by a semicolon (;). |
Example request
curl -X POST \
--header 'Content-Type: application/x-www-form-urlencoded' \
--header 'Accept: application/json' \
--header 'X-Requested-By: ThoughtSpot' \
-d 'sqlstatements=["alter table geo.falcon_default_schema.test_table434 add column test_col17 varchar(10) DEFAULT '';";"alter table geo.falcon_default_schema.test_table434 add column test_col18 varchar(10) DEFAULT '';"]' \
'http://<ThoughtSpot-host>/callosum/v1/tspublic/v1/database/run'https://<ThoughtSpot-host>/callosum/v1/tspublic/v1/database/run
Example response
If the request is successful, the API returns the SQL statements in the response body.
[
{
"sqlStatement": "alter table geo.falcon_default_schema.test_table434 add column test_col17 varchar(10) DEFAULT '';",
"cmdStatus": "FAILED",
"errorMsg": "Error Code: INVALID_ARGUMENT Incident Id: 8e9c61bc-0261-4690-a28e-daf621803355\nError Message: Error while getting server schema",
"errorCode": {
"name": "INVALID_ARGUMENT",
"ordinal": 9
}
},
{
"sqlStatement": "alter table geo.falcon_default_schema.test_table434 add column test_col18 varchar(10) DEFAULT '';",
"cmdStatus": "NOT_EXECUTED"
}
]
