Supported databases

Connectivity policy

Analyst Studio currently supports connectivity to customer data using Java Database Connectivity (JDBC) drivers. Analyst Studio does not write JDBC drivers. We contract the use of proprietary JDBC drivers or use established and well-supported open-source JDBC drivers.

For Preferred and Supported databases, Analyst Studio ensures functionality for all versions of the database server currently actively maintained by the database vendor. Analyst Studio does not ensure continued functionality for database server versions that have reached end of life, or for database types that are not explicitly supported.

Analyst Studio will usually support or recommend only one driver per database type. However, to give customers flexibility during their update process, we will enable customers to use either the recommended or the most recent version until the next update (either 6 or 12 months). Analyst Studio will usually provide 6 months' notice to customers before driver removal, although we reserve the right to remove drivers with known security issues on less notice.

Driver deprecation

Analyst Studio will mark for deprecation and set removal dates for older versions of drivers. We generally set removal dates for the oldest drivers 6 months out, when adding new versions of drivers.

Drivers that are past their removal date cannot be used to connect to Analyst Studio. The removed driver will no longer be available in the Preferred JDBC driver dropdown of the connection’s details.

On the removal date, any connections still using that driver will be updated by Analyst Studio to use the latest recommended driver. This may cause connectivity issues. Analyst Studio encourages customers to update their preferred drivers shortly following announced updates.

Support levels

For the listed database types below, Analyst Studio aims to support all versions of the database actively supported by the database vendor or appropriate open-source community.

Tier 1: Preferred

These database and integration types are fully supported by Analyst Studio and drivers are upgraded at least semi-annually. We are committed to improving the experience of users connecting Analyst Studio to data sources of these types, and will fix issues based on severity and demand. As part of our release process, Analyst Studio runs exhaustive integration tests against all types in this tier to ensure quality.

Database / Integration Type	Database Version Support	Bridge
Amazon Redshift		Optional
PostgreSQL	9.5, 9.6, 10, 11, 12, 13, 14, 15	Optional
MySQL	5.6, 5.7, 8.0	Optional
Google BigQuery		Not supported
Snowflake		Optional
Amazon Athena		Not supported
Microsoft SQL Server	2012, 2014, 2016, 2017, 2019	Optional
Starburst	versions up to 413	Optional
Trino (formerly PrestoSQL)	versions up to 413	Optional
Hive	1.0.0 - 3.1	Optional
Google AlloyDB		Optional
Azure SQL Database		Optional
Azure Synapse SQL		Optional
dbt Semantic Layer		Not supported
Looker SQL		Not supported

Database / Integration Type

Database Version Support

Bridge

Amazon Redshift

Optional

PostgreSQL

9.5, 9.6, 10, 11, 12, 13, 14, 15

Optional

MySQL

5.6, 5.7, 8.0

Optional

Not supported

Optional

Not supported

2012, 2014, 2016, 2017, 2019

Optional

Starburst

versions up to 413

Optional

Trino (formerly PrestoSQL)

versions up to 413

Optional

Hive

1.0.0 - 3.1

Optional

Optional

Optional

Optional

Not supported

Not supported

Tier 2: Supported

These database types are fully supported by Analyst Studio and drivers are upgraded annually. We are committed to improving the experience of users connecting Analyst Studio to data sources of these types, and will fix issues based on severity and demand. As part of our release process, Analyst Studio runs integration tests against all types in this tier to ensure quality.

Database Type	Database Version Support	Bridge
Oracle	11.2, 12.1, 12.2, 18c, 19c, 21c	Required
Oracle Autonomous Database		Optional
ClickHouse		Optional
Cube		Not Supported
Dremio		Not Supported
Impala	2.8 - 3.2	Required
Google Cloud SQL MySQL		Optional
Google Cloud SQL PostgreSQL		Optional
Google Cloud SQL SQL Server		Optional
Amazon Aurora MySQL		Optional
Vertica	7.2 - 10.1.1	Required
Databricks		Optional
SingleStore		Optional
Teradata		Optional
Amazon Aurora PostgreSQL		Optional
Treasure Data		Optional
Apache Druid	0.20.1	Required
Denodo	8.0	Optional
Arrow Flight SQL		Not supported

Database Type

Database Version Support

Bridge

Oracle

11.2, 12.1, 12.2, 18c, 19c, 21c

Required

Oracle Autonomous Database

Optional

Optional

Not Supported

Not Supported

2.8 - 3.2

Required

Google Cloud SQL MySQL

Optional

Google Cloud SQL PostgreSQL

Optional

Google Cloud SQL SQL Server

Optional

Amazon Aurora MySQL

Optional

Vertica

7.2 - 10.1.1

Required

Databricks

Optional

SingleStore

Optional

Teradata

Optional

Amazon Aurora PostgreSQL

Optional

Treasure Data

Optional

Apache Druid

0.20.1

Required

Denodo

8.0

Optional

Arrow Flight SQL

Not supported

Tier 3: Works, not Supported

These database types are partially supported by Analyst Studio, but the database vendor’s recommended driver is not used. Analyst Studio is able to connect to these database types because of similarities of SQL dialects or shared database servers. Analyst Studio makes no commitment to improve implementation, fix issues, or regularly run tests against these database types.

Database Type	Database Version Support	Bridge
MariaDB		Optional
Tenjin		Optional
MemSQL		Not supported
Citus		Not supported
Timescale		Not supported
mParticle		Optional
Spark	1.6, 2.1 - 3.0	Optional
PrestoDB	up to version 0.240	Optional

Database Type

Database Version Support

Bridge

MariaDB

Optional

Tenjin

Optional

MemSQL

Not supported

Not supported

Not supported

Optional

1.6, 2.1 - 3.0

Optional

PrestoDB

up to version 0.240

Optional

Databases

If the database supports multiple Drivers, use the Manage Connections page to update to your preferred driver.

We support the following database connections:

Cloud environments

We support the following connections to Cloud environments:

Connect to Amazon Web Services in Analyst Studio

FAQs

Q: How to import Analyst Studio results into a Google Sheet

While we currently do not support the ability to natively export or import data to and from a Google Sheet, we do have a step-by-step guide on how to query Google Sheets using BigQuery in Analyst Studio. You can also follow the instructions below for a custom way to import data from an Analyst Studio report to a Google Sheet programmatically.

To enable this feature in a Google Sheet, you can begin by following these steps:

Go to the Analyst Studio report and click View Details.
Click on the query on the left to access the query token in the URL.
Copy the query token from the URL /queries/[QUERY_TOKEN].
Save the formula below somewhere safe because we will need to use it on our final step.

=importModeResult("https://app.mode.com/[WORKSPACE]/reports/[REPORT_TOKEN]/queries/[QUERY_TOKEN]")

In the formula above, please be sure to replace "WORKSPACE" with your workspace name as shown in any report URL, "REPORT_TOKEN" with the report token available in the URL, and "QUERY_TOKEN" with the query token that you just copied.

This formula will import the data from the Analyst Studio query into the Google Sheet once the following steps are completed. It will automatically use the last successful run of the query.

Setup

From a Google Sheet, click Extensions -> Apps Script from the top menu bar.

In the code editor, overwrite the existing code and paste the importModeResults function below ⬇️ into the code editor. Then, save it via the disk file icon.

function importModeResult(url) {

var USERNAME = PropertiesService.getScriptProperties().getProperty('username');
var PASSWORD = PropertiesService.getScriptProperties().getProperty('password');

var token = getTokenFromUrl(url);
var account = getAccountFromUrl(url);
var query = getQueryFromUrl(url);

var options = {
  headers: { 'Authorization': 'Basic ' + Utilities.base64Encode(USERNAME + ':' + PASSWORD, Utilities.Charset.UTF_8) }
};

var reportUrl = 'https://app.mode.com/api/' + account + '/reports/' + token;
var reportResponse = UrlFetchApp.fetch(reportUrl, options);
var reportJSON = JSON.parse(reportResponse);

var lastRunPath = reportJSON['_links']['last_successful_run']['href'];
var queryRunUrl = 'https://app.mode.com' + lastRunPath + '/query_runs'
var queryRunResponse = UrlFetchApp.fetch(queryRunUrl, options);
var queryRunJSON = JSON.parse(queryRunResponse);

var queryRuns = queryRunJSON['_embedded']['query_runs']

queryRuns.forEach(function(q) {
  if (q['query_token'] == query) {
    resultPath = q['_links']['result']['href']
  }
})

if (resultPath) {
  var csvPath = 'https://app.mode.com' + resultPath + '/content.csv'
  var csvResponse = UrlFetchApp.fetch(csvPath, options);
  var csvTextRaw = csvResponse.getContentText();
  var csvText = csvTextRaw.replace(/(["'])(?:(?=(\\?))\2[\s\S])*?\1/g, function(e){return e.replace(/\r?|\r/g, '') });

  return Utilities.parseCsv(csvText)

} else {
  return 'Invalid URL!'
}


function getTokenFromUrl(url) {
  return url.split('/reports/')[1].slice(0,12)
}

function getAccountFromUrl(url) {
  return url.split('.com/')[1].split('/')[0]
}

function getQueryFromUrl(url) {
  return url.split('/queries/')[1].slice(0,12)
}
}

In the code editor, click the gear icon -> Project Settings in the left-side menu, scroll down to the bottom, and click the Add Script Property tab.
Add a separate username and password property. The value of the username property should be an Analyst Studio API token. The value of the password property should be an Analyst Studio API secret. You can create a token and secret in the API Tokens tab of your Analyst Studio settings page. Here’s our documentation on how to generate API tokens.
Once you save these properties, navigate back to your Google Sheet and add the importModeResult formula into a cell in your Google Sheet. This will populate the query results.

Q: How to upload data via CSV?

Currently, we do not offer CSV uploads to private connections. You can utilize our Google Sheet integration to import flat files as Datasets into Analyst Studio.

We highly recommend that you register for the free Connecting Databases to Analyst Studio course in ThoughtSpot University that covers the information in this article, and watch the How to Connect a Database lesson.

See other training resources at ThoughtSpot University.