Database API

Database API

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.

The /tspublic/v1/database API endpoints are applicable to ThoughtSpot Software deployments only.

Supported operations🔗

API endpointAvailable from

GET /tspublic/v1/database/tables
Gets a list of all tables in a database and schema in Falcon.

ThoughtSpot Cloud Not applicable
ThoughtSpot Software 6.3.1

GET /tspublic/v1/database/schemas
Gets a list of all schemas in a particular database in Falcon.

ThoughtSpot Cloud Not applicable
ThoughtSpot Software 6.3.1

GET /tspublic/v1/database/databases
Gets a list of all databases in Falcon.

ThoughtSpot Cloud Not applicable
ThoughtSpot Software 6.3.1

GET /tspublic/v1/database/getTableDetail
Gets details of a particular table.

ThoughtSpot Cloud Not applicable
ThoughtSpot Software 6.3.1

POST /tspublic/v1/database/createtable
Creates tables in a database.

ThoughtSpot Cloud Not applicable
ThoughtSpot Software 6.3.1

POST /tspublic/v1/database/run
Runs TQL commands with SQL statements.

ThoughtSpot Cloud Not applicable
ThoughtSpot Software 6.3.1

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.

Resource URL🔗

GET /tspublic/v1/database/tables

Request parameters🔗

Query parameterDescription

database

String. Name or GUID of the database from which you want to fetch the tables.

schema

String. Name of the database schema.

Example request🔗

cURL
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'
Request URL
https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/database/tables?database=usage_stats&schema=falcon_default_schema

Example response🔗

If the request is successful, the API returns the tables for the specified database and schema:

[
  "table_usage",
  "use_case"
]

Response codes🔗

HTTP status codeDescription

200

Successful operation.

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.

Resource URL🔗

GET /tspublic/v1/database/schemas

Request parameters🔗

Query parameterDescription

database

String. Name or GUID of the database for which you want to fetch the schemas.

Example request🔗

cURL
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'
Request URL
https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/database/schemas?database=b87a99b8-4a1b-4d94-bd57-e1c9971722c8

Example response🔗

If the request is successful, the API returns the schema for the specified database.

[
  "NPATH_PUBLIC_MuHAOPj"
]

Response codes🔗

HTTP status codeDescription

200

Successful operation

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.

Resource URL🔗

GET /tspublic/v1/database/databases

Request parameters🔗

None

Example request🔗

cURL
curl -X GET \
--header 'Accept: application/json' \
--header 'X-Requested-By: ThoughtSpot' \
'https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/database/databases'
Request URL
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"
 ]

Response codes🔗

HTTP status codeDescription

200

Successful operation

Get details of a table🔗

To get details of a particular table, send a GET request to the /tspublic/v1/database/databases endpoint.

Resource URL🔗

GET /tspublic/v1/database/getTableDetail

Request parameters🔗

Query parameterDescription

database

String. Name or GUID of the database.

schema

String. Name of the schema.

table

String. Name of the table.

Example request🔗

cURL
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'
Request URL
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"
      }
    }
  ]
}

Response codes🔗

HTTP status codeDescription

200

Successful operation

Create tables🔗

To create tables in a database, send a POST request to the /tspublic/v1/database/createtable/ API endpoint.

Resource URL🔗

POST /tspublic/v1/database/createtable/

Request parameters🔗

Form parameterDescription

createdb

Boolean. Set this flag to true to create tables with a database and schema. If you are adding a table to an existing database and schema, you can set createdb to false.

ddl_schema

String. The schema of the request that needs to be generated.

Example request🔗

cURL
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'
Request URL
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"
}

Response codes🔗

HTTP status codeDescription

200

Successful operation

400

Invalid parameter values

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.

Resource URL🔗

POST /tspublic/v1/database/run

Request parameters🔗

Form parameterDescription

sqlstatements

String. A JSON array of SQL statements separated by a semicolon (;).

Example request🔗

cURL
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'
Request URL
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"
  }
]

Response codes🔗

HTTP status codeDescription

200

Successful operation

500

Invalid input or server error