Data connection

Data connection

ThoughtSpot supports connecting to external data warehouses and using these as data sources for analytics and visualizations. ThoughtSpot users can run a live query of the data directly from the following external data sources to generate the information they need:

This article describes the procedures for setting and managing data connections through the REST APIs. For information about how to set up a data connection, see Data Connections in ThoughtSpot.

Data Connection APIs🔗

The connection API endpoints allow you to create, edit, delete, export, and query data connections on a ThoughtSpot cluster. For more information about the API endpoints, see the following pages:

Required permissions🔗

You must have data management permissions to create, edit, delete or query data connection objects. Make sure your user account has the DATAMANAGEMENT privilege.

If Role-Based Access Control (RBAC) is enabled on your ThoughtSpot cluster, the CAN_CREATE_OR_EDIT_CONNECTIONS (Can create/edit Connections) privilege is required.

Configuration attributes🔗

The configuration attributes include authentication and database information required to set up or update a data connection. For most data connections, you can use service account or OAuth credentials to connect to and fetch data from a Cloud Data Warehouse. Some data connections like Teradata, Oracle, and Presto do not support OAuth. The Snowflake connections also support external OAuth credentials with Microsoft Azure AD or Okta.

Note

To set up connections with OAuth, make sure your connection metadata includes client ID, client secret key, auth URL, and access token URL.

Amazon Redshift connection

  • host

    String. The hostname of the database connection.

  • port

    Integer. Enter the port number associated with the database. The default port is 5439.

  • user

    String. Username of your Redshift account.

  • password

    String. Password of your Redshift account.

  • database

    String. Specify the name of the database that you want to use for live query..

To set up a Redshift connection with OAuth, see Configure AWS IDC OAuth for a Redshift connection and Configure Azure AD external OAuth for a Redshift connection

Azure Synapse connection

  • host

    String. The hostname of the database connection.

  • port

    Integer. Enter the port number associated with the database.

  • user

    String. Username of your Azure Synapse account.

  • password

    String. Password of your Azure Synapse account.

  • database

    String. Specify the database associated with the account.

To set up a Synapse connection with OAuth, see Configure OAuth for a Synapse connection

Databricks connection

  • host

    String. The hostname of the database connection. For example, dbs.example.cloud.databricks.com.

  • http_path

    String. The HTTP path of your Databricks connection host.

  • user

    String. Username of your Databricks account.

  • password

    String. Password of your Databricks account.

To set up a Databricks connection with OAuth, see Configure OAuth for a Databricks connection.

Dremio connection

  • host

    String. The hostname of Dremio server.

  • port

    String. Dremio server port.

  • user

    String. Username of the Dremio server account.

  • password

    String. Password of the Dremio server account.

  • database

    String. The database associated with the account.

To set up a Dremio connection with OAuth, see Configure OAuth for a Dremio connection.

Denodo connection

  • host

    String. The hostname of Denodo server.

  • port

    String. Denodo server port.

  • user

    String. Username of the Denodo server account.

  • password

    String. Password of the Denodo server account.

  • database

    String. The database associated with the account.

To set up a Denodo connection with OAuth, see Configure OAuth for a Denodo connection.

The following example shows the connection metadata JSON for a Denodo connection:

{
  "configuration": {
    "host": "10.79.153.40",
    "port": "9999",
    "user": "admin",
    "password": "admin",
    "database": "thoughtspot_sales"
  },
  "externalDatabases": [
  ]
}
Google BigQuery connection

  • project_id

    String. ID of the project associated with your BigQuery account.

  • oauth_pvt_key

    String. The secret key obtained for your BigQuery service account. To obtain the secret key, you must create a custom role in BigQuery and set up your service account. For more information, see BigQuery prerequisites.

To set up a BigQuery connection with OAuth, see Configure OAuth for a Google BigQuery connection.

Looker connection

  • host

    String. The hostname of Looker server.

  • password

    String. Password of the Looker server account.

  • user

    String. Username of the Looker server account.

To set up a Looker connection with OAuth, see Connecting to Looker.

Mode connection

  • host

    String. The hostname of Mode server. Enter production-datawarehouse.mode.com

  • port

    String. Mode server port. Enter 8444

  • user

    String. Username of the Mode server account.

  • password

    String. Password of the Mode server account.

Oracle connection

  • user

    String. Username of your Oracle account.

  • password

    String. Password of your Oracle account.

  • net_service_name

    String. The Net Service Name of your Oracle account.

  • wallet_file

    String. The file containing your Oracle client credentials.

  • schema

    String. The schema of the database.

Presto connection

  • host

    String. Hostname of the Presto database.

  • port

    String. Presto server port.

  • user

    String. Username of your Presto database user.

  • password

    String. Password of your Presto database user.

  • database

    String. The database to fetch tables from.

SAP HANA connection

  • host and port

    String. The hostname and port of the SAP HANA database connection. Enter the host followed by a colon and the port number of the SAP HANA database. For example, if your host is 192.168.1.1, and your port is 8080, specify 192.168.1.1:8080.

  • user

    String. Username of your SAP HANA database account.

  • password

    String. Password of your SAP HANA database account.

  • database

    String. The database associated with the SAP HANA database.

Starburst connection

  • host

    String. Hostname of the database connection.

  • port

    String. Starburst server port.

  • user

    String. Username of your Starburst account.

  • password

    String. Password of your Starburst account.

  • database

    String. The database from which you want to fetch tables.

To set up a Starburst connection with OAuth, see Configure OAuth for a Starburst connection

Snowflake connection

  • accountName

    String. The service account name associated with the Snowflake connection.

  • user

    String. The Snowflake account username.

  • password

    String. The password of your Snowflake account.

  • role

    String. The role assigned to your user account. For example, sysadmin.

    Make sure the role you specify for this attribute has privileges to access the database, schema, and tables that you want to use for the live query service.

  • warehouse

    String. Data warehouse from which you want to fetch data.

  • database

    String. The database associated with the Snowflake database.

To set up a Snowflake connection with OAuth, see Configure internal OAuth for a Snowflake connection.

Teradata connection

  • host

    String. The hostname of the database connection.

  • user

    String. Username of the Teradata database user.

  • password

    String. Password of the Teradata database user.

  • database

    String. Database from which you want to fetch tables.

Trino connection

  • host

    String. Hostname of the Trino database.

  • port

    String. Starburst server port.

  • user

    String. Username of the Trino database user.

  • password

    String. Password of the Trino database user.

  • database

    String. The Database from which you want to fetch tables.

Connection attributes🔗

To create or modify data connections through REST APIs, you need to provide a map of JSON attributes. This JSON includes configuration attributes and a map of the database schema with tables.

Example JSON with connection attributes - with tables🔗

{
   "configuration":{
      "accountName":"thoughtspot_partner",
      "user":"tsadmin",
      "password":"TestConn123",
      "role":"sysadmin",
      "warehouse":"MEDIUM_WH"
   },
   "externalDatabases":[
      {
         "name":"AllDatatypes",
         "isAutoCreated":false,
         "schemas":[
            {
               "name":"alldatatypes",
               "tables":[
                  {
                     "name":"allDatatypes",
                     "type":"TABLE",
                     "description":"",
                     "selected":true,
                     "linked":true,
                     "columns":[
                        {
                           "name":"CNUMBER",
                           "type":"INT64",
                           "canImport":true,
                           "selected":true,
                           "isLinkedActive":true,
                           "isImported":false,
                           "tableName":"allDatatypes",
                           "schemaName":"alldatatypes",
                           "dbName":"AllDatatypes"
                        },
                        {
                           "name":"CDECIMAL",
                           "type":"INT64",
                           "canImport":true,
                           "selected":true,
                           "isLinkedActive":true,
                           "isImported":false,
                           "tableName":"allDatatypes",
                           "schemaName":"alldatatypes",
                           "dbName":"AllDatatypes"
                        }
                     ]
                  }
               ]
            }
         ]
      }
   ]
}
  "validate": true
}

Example JSON with connection attributes - without tables🔗

{
   "configuration":{
      "accountName":"thoughtspot_partner",
      "user":"tsadmin",
      "password":"TestConn123",
      "role":"sysadmin",
      "warehouse":"MEDIUM_WH"
   },
   "externalDatabases":[

   ]
}
  "validate": false
}

Integrate with dbt🔗

ThoughtSpot’s dbt sync allows you to easily provide your existing dbt models and automatically create ThoughtSpot Worksheets, which you can use to search your data. Integration with dbt is certified only for Amazon Redshift, Databricks, Google BigQuery, and Snowflake connections.

For information on integrating with dbt using the Data Workspace option in the UI, see Integrate with dbt.

To understand the API endpoints for establishing a dbt connection, see REST APIs v2.

Data catalog integration🔗

ThoughtSpot supports integration with only one data catalog tool at a time. You must have CAN_CREATE_CATALOG permissions to establish a data catalog connection. To understand about the data catalog tools supported by ThoughtSpot, see the following pages:

To understand the metadata API endpoints, see the following pages: