Supported databases

Connectivity policy

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

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

Mode will usually support or recommend only one driver per database type. However, to enable 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). Mode will usually provide 6 months’ notice to customers before driver deprecation, although we reserve the right to deprecate drivers with known security issues on less notice.

Driver deprecation

Mode will set deprecation dates for older versions of drivers. We generally set deprecation dates for the oldest drivers 6 months out, when adding new versions of drivers.

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

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

Support levels

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

Tier 1: Preferred

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

Database TypeDatabase Version SupportBridge
Amazon RedshiftOptional
PostgreSQL9.5, 9.6, 10, 11, 12Optional
MySQL5.6, 5.7, 8.0Optional
Google BigQueryNot Supported
SnowflakeNot Supported
Amazon AthenaNot Supported
Microsoft SQL Server2012, 2014, 2016, 2017, 2019Optional
Trino (formerly PrestoSQL)versions up to 350 (including Starburst equivalent)Optional
Hive1.0.0 - 3.1Required
Azure SQL DatabaseOptional
Azure Synapse SQLOptional

Tier 2: Supported

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

Database TypeDatabase Version SupportBridge
Oracle11.2, 12.1, 12.2, 18c, 19cRequired
Impala2.8 - 3.2Required
Amazon Aurora MySQLOptional
Vertica7.2 - 9.3Required
DatabricksOptional
Amazon Aurora PostgreSQLOptional
Treasure DataOptional
Apache Druid0.20.1Required

Tier 3: Works, not Supported

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

Database TypeDatabase Version SupportBridge
MariaDBOptional
HeapOptional
TenjinOptional
MemSQLNot Supported
CitusNot Supported
TimescaleNot Supported
mParticleOptional
Spark1.6, 2.1 - 3.0Optional
PrestoDBup to version 0.240Optional
Terradata Prestoversions 0.208-t and earlierOptional

Databases

NOTE:

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

Amazon Athena

Supported JDBC drivers and known issues
DriverDriver Release DateMode Deprecation Date
(Recommended) Simba 2.0.18.10001/29/2021
Simba v 2.0.13.10006/9/2020

Mode recommends connecting via the Simba Athena driver because it is the official driver endorsed by Amazon.

The following are known issues with the current supported drivers. Mode continues to advocate on our customers’ behalf for vendors & open source communities to resolve these issues.

DriverKnown IssueFurther Details
Simba v 2.0.13.1000Requires additional IAM Permissions for Queries
This version of the driver requires that the Mode user’s IAM permissions
include the athena:ListDataCatalogs permission. This permission is required for query execution via Mode (not just the Schema Browser).

Customers whose Mode user is managed via the AmazonAthenaFullAccess Managed Policy will not experience issues.
Simba v 2.0.13.1000
Schema refreshes fail with 
Null Pointer Exception for external data sources
Attempts to get or update schemas when Athena is connected to non-Hive (PostgreSQL confirmed) external data sources results in a Null Pointer Exception. For customers whose Athena instance includes external data sources, automatic refresh of the database’s schema data should be disabled and the Mode Schema Browser will be unable to display data.
Database configuration instructions

The Mode user’s IAM permissions must include athena:ListDataCatalogs & athena:ListDataCatalogs. In addition, ensure that port 444 is open to outbound traffic.

Connecting to Mode
  1. Follow the steps to connect a database with Direct Connect.
  2. Select Amazon Athena from the list of databases.
  3. Fill out your AWS region to be formatted like us-west-2.
  4. The AWS Access Key and Access Secret should be for the AWS user you want to connect through. When creating an Athena connection, we recommend creating a user in AWS that only has access to the data you want to query through Athena. For data you want to query, the AWS user only needs read access.
  5. The S3 Results Directory should be a bucket where Athena can write query results. For this bucket, the AWS user specified above needs read and write access. The bucket should be a full S3 url (e.g., s3://acme/my/athena/bucket)
  6. Click Connect. You will land on a page that displays your database schema.

Amazon Redshift

Additionally supports

: mParticle Redshift, Tenjin Redshift

Supported JDBC drivers and known issues
DriverDriver Release DateMode Deprecation Date
(Recommended) Amazon Redshift 2.0.0.32/25/2021
Amazon Redshift v 1.2.53.10801/19/2021
Simba v 1.2.49.10739/8/2020
PostgreSQL v 42.2.58/27/20182/10/2020

The following are known issues with the current supported drivers. Mode continues to advocate on our customers’ behalf for vendors & open source communities to resolve these issues.

DriverKnown IssueFurther Details
PostgreSQL v 42.2.5Query result set size is limitedQueries sometimes result in the following SQL syntax error: ERROR: Invalid protocol sequence 'P' while in PortalSuspended state.

This error is frequently associated with queries with larger result sets (not just
number of rows). Users have had some success working around this error by adding a LIMIT clause to the query.
PostgreSQL v 42.2.5
Geometry datatypes 
are unsupported
Driver does not support spatial / geometry datatypes, released by Amazon in November 2019.

Users attempting to query spatial data will see a Null Pointer Exception in the SQL Editor UI.
Database configuration instructions
Amazon Redshift

Depending on your Amazon settings, you may need to grant Mode access to your Redshift security group:

  1. Navigate to the Redshift Management Console.
  2. Select

    Clusters

    from the left navigation bar.
  3. Click on the cluster you want to connect.

    Redshift

  4. Find Cluster Security Groups under Cluster Properties and click on the cluster's security group.

    Redshift

    Note: If you are using VPC Security Groups, you must also grant Mode access to that security group. Please click on your VPC Security Group name and follow this section of our Amazon RDS guide, starting with step 2.

  5. Click on the name of the security group.

    Redshift

  6. You'll see a list of authorized connections. Click the blue "Add Connection Type" button in the upper left.
  7. Select "CIDR/IP" from the "Connection Type" dropdown, and paste the address 54.68.30.98/32 in the "CIDR/IP to Authorize" field.

    Redshift

  8. Click the blue "Authorize" button
  9. Repeat steps 5 through 7 for each of the addresses listed below:

    • 54.68.30.98/32
    • 54.68.45.3/32
    • 54.164.204.122/32
    • 54.172.100.146/32
mParticle Redshift

mParticle supports both mParticle-hosted and client-hosted Redshift clusters. If you’re using an mParticle hosted Redshift cluster, log in to the mParticle platform and navigate to the Redshift Configuration tab. There, you’ll find your connection string and user credentials.

mParticle Redshift

You can also whitelist Mode's servers by editing the list of allowed IP addresses on mParticle UI, which will update the AWS security group settings associated with your Redshift cluster. Once you have your credentials, follow the steps below to connect Mode:

  1. Log in to Mode and follow to steps to connect a database to Mode.
  2. Select

    mParticle Redshift

    from the list of databases.
  3. Enter your mParticle credentials and click Connect. You'll land on a page that displays your database schema.
  4. Click “New Query” in the top navigation to start writing queries!
Tenjin Redshift

Once you’ve activated the DataVault feature on your Tenjin account, you can access your DataVault credentials by logging in to the dashboard and navigating to Menu -> DataVault Credentials.

For additional questions about getting your DataVault credentials, please free to contact support@tenjin.io.

Apache Druid

Mode has added support for Apache Druid as an experimental new connector as of Monday, March 1, 2021. Please reach out to Support with any concerns about the behavior of this connector.

Supported JDBC drivers and known issues
DriverDriver Release Date
Apache Avatica 1.17.06/21/2020

The following are known issues with the current supported drivers. Mode continues to advocate on our customers’ behalf for vendors & open source communities to resolve these issues.

DriverKnown IssueFurther Details
Avatica 1.17.0Druid Basic Authentication is unsupportedMode currently requires Druid connections over Bridge.
Avatica 1.17.0Connection context properties are unsupportedMode currently does not support changing the Druid broker’s setting for the connection via JDBC properties.
Avatica 1.17.0Only Druid SQL supportWhen connecting to Druid via JDBC, only Druid SQL syntax is supported. Native (json-type) query syntax will result in errors.
Avatica 1.17.0Druid SQL limitationsNot all Druid features are supported via Druid SQL and JDBC connectivity. Please see the Druid docs.
Avatica 1.17.0Single Schema SupportMode’s Schema Browser will surface only tables found in the default druid schema
Avatica 1.17.0Query cancellation is unsupportedDruid supports cancellation for native (json-type) queries, but not for Druid SQL queries. If users attempt to cancel a query in the Mode UI, the query run will not be interrupted. The driver does not return an error, so Mode is unable to surface anything to the user.
Connecting to Mode

When connecting Druid to Mode, fill out the connection form as follows:

  1. Display name:

    the name of the connection used in the Schema Browser.
  2. Description:

    optional additional context viewable in Connection Management.
  3. Host:

    the route of the Druid cluster Broker, as specified in the broker/runtime.properties file.
  4. Port:

    the port of the Broker. The default port is 8082. If your cluster is secured by SSL, the default port is 8182.
  5. Preferred JDBC Driver:

    the Avatica 1.17.0 driver is the only current driver.
  6. Disable transport encryption:

    Mode strongly recommends that transport encryption be enabled and that Druid be configured to use TLS v.1.2. If disable, connections between the Bridge connector and your database will not be encrypted.

Please note the following limitation of broker connection stickiness over JDBC:

“Druid's JDBC server does not share connection state between Brokers. This means that if you're using JDBC and have multiple Druid Brokers, you should either connect to a specific Broker, or use a load balancer with sticky sessions enabled. The Druid Router process provides connection stickiness when balancing JDBC requests, and can be used to achieve the necessary stickiness even with a normal non-sticky load balancer.”

Databricks

Additionally supports

Spark

Supported JDBC drivers and known issues
DriverDriver Release DateMode Deprecation Date
(Recommended) Simba Spark v 2.6.16.10208/11/2020
Apache Hive v 1.2.24/3/2017

Mode recommends connecting via the Simba Spark driver because it is the official driver endorsed by Databricks.

The following are known issues with the current supported drivers. Mode continues to advocate on our customers’ behalf for vendors & open source communities to resolve these issues.

DriverKnown IssueFurther Details
Simba Spark v 2.6.16.1020
Strings in Binary
Columns
Queries return binary results (not strings) for binary datatype columns.

For example, if I insert "hi" in a binary column, where I previously would see "hi" returned by the driver, I now see "6869".
Simba v 2.6.17.1021Deprecated VersionsSupport for Spark 1.6, 2.1, and 2.2 is deprecated, and will be removed in a future release of this driver. The driver only supports connections to Spark Thrift Server instances. It does not support connections to Shark Server instances.
Gather JDBC connection parameters

Connecting to Databricks requires a running Spark cluster configured with SSL enabled and using HTTPS as the underlying thrift protocol (this is the default configuration for new clusters).

Connecting to Mode
  1. Follow these steps to connect a Databricks database. Change the display name to something that helps you recognize the database behind the connection.
  2. Fill in the host, token, and HTTP path fields with the information you gathered above.
  3. Click "Connect" to save the new connection.
Supported SQL Dialect

Mode supports connecting to Databricks clusters via their embedded Hive server using Hive's open source JDBC driver. This restricts the supported SQL dialect to HiveQL; we currently do not support querying in Spark SQL, though we may add support for it at a later date.

Automatic cluster termination

Databricks "Standard" Spark clusters are configured by default to automatically terminate after 120 minutes of inactivity. The Hive JDBC driver cannot trigger the cluster to automatically restart, so you may want to adjust the timeout or disable automatic termination per Databricks' documentation. If the driver attempts to connect to the server after it's been shut down, the connection attempt will fail until the cluster is restarted manually.

Google BigQuery

Supported JDBC drivers and known issues
DriverDriver Release DateMode Deprecation Date
(Recommended) Simba v 1.2.12.101511/30/2020
Simba v 1.2.11.10148/28/2020
Mode v 0.0.125/1/20208/2/2021

Mode recommends connecting via the Simba Big Query driver because it is the official driver endorsed by Google.

The following are known issues with the current supported drivers. Mode continues to advocate on our customers’ behalf for vendors & open source communities to resolve these issues.

DriverKnown IssueFurther Details
Simba v 1.2.11.1014Batch priority is unsupportedThe specification of job priority is unsupported. All queries are executed as "interactive" priority, never "batch" priority.

Google Big Query batch priority is described here: https://cloud.google.com/bigquery/docs/running-queries#batch
Simba v 1.2.11.1014Necessary permissionsTo connect via this driver, the service account should have the roles BigQuery Data Viewer and BigQuery Job User.

In addition, the Mode GBQ service account must have the bigquery.jobs.list permission. This permission can be added via several roles. Mode recommends either BigQuery Resource Viewer or BigQuery User, although broader access will also function.

The Mode user would additionally need BigQuery Data Editor role if the queries users execute from Mode are expected to be able to write to the database (e.g. DROP, CREATE, UPDATE). Temporary functions use and temporary table creation & deletion do not require this permission.
Mode v 0.0.12
Slow queries with large
result sets
Large query results (e.g. 10^6 or 10^7 rows) are much slower to return via the Mode JDBC driver than via the Google Storage API.

Google released a Storage API that significantly improves the performance of queries with result sets this large: https://cloud.google.com/bigquery/docs/reference/storage
Mode v 0.0.12Timestamp truncationMilliseconds are truncated from Timestamp datatypes.
Mode v 0.0.12Batch priority is unsupportedThe specification of job priority is unsupported. All queries are executed as "interactive" priority, never "batch" priority.

Google Big Query batch priority is described here: https://cloud.google.com/bigquery/docs/running-queries#batch
Mode v 0.0.12Necessary permissionsTo connect via this driver, the service account should have the roles BigQuery Data Viewer and BigQuery Job User.
Database configuration instructions

To connect Mode to your BigQuery project, you will need to create a service account for Mode to use.

  1. Navigate to the Google Developers Console. Before proceeding, click on the drop down to the left of the search bar and select the project to which you want to connect.

    BigQuery

    NOTE

    : If you do not see the project you would like to connect to listed in the drop down, click on the account switcher in the upper right corner of the window and ensure you are logged in to a Google account that is a member of the project.

  2. Click on the hamburger menu in the upper left and select

    IAM & Admin

    and then

    Service accounts

    . Click the

    Create service account

    button.

    BigQuery

  3. Give your new service account a name. Under Project Role, add only the

    BigQuery Data Viewer

    and

    BigQuery Job User

    roles. Check

    Furnish a new private key

    and select

    P12

    as the key type. Finally, click

    Save

    to create your service account and download your private key file. Save the private key file to a secure place where you can easily retrieve it as you will need it in a subsequent step.

    BigQuery

  4. A dialogue box will display the password for your newly generated private key. Store this password in a secure place. You will not need this password to connect Mode to BigQuery. Click

    Close

    to dismiss the dialogue box. Note the email address listed for your newly created service account as you will need it in a subsequent step.
  5. Return to Mode, and begin the process to connect a BigQuery database. When you see the form to enter your BigQuery credentials, first enter a name for the new database connection.
  6. In the

    Project ID

    field, enter the BigQuery project ID. It can be found in the URL of your Google Developers Console. The URL should be structured like this: https://console.developers.google.com/apis/library?project=MY_PROJECT_ID
  7. In the

    Service Account Email Address

    field, add your service account's email address that you noted in step 4.
  8. Under the

    Key

    field, click

    Choose File

    and select the private key file you downloaded in step 3.
  9. Click Connect to complete the connection.
Standard vs. legacy SQL

When connecting to BigQuery, you will choose either legacy SQL or standard SQL as the default SQL dialect for that connection. Most users choose standard SQL, which is similar to industry standard SQL dialects used by other databases.

Regardless of your choice as the connection's default, users can specify which dialect they would like to use on a query-by-query basis by including either #standardSQL or #legacySQL in the Query Editor.

a Workspace admin can change the default SQL preference a BigQuery connection at any time by following these steps:

  1. Navigate to the Mode home page
  2. Click on your name in the upper left corner of the window.
  3. Click

    Workspace Settings

    .
  4. Under the Data section, click

    Connections

    .
  5. Click on the BigQuery connection you want to update.
  6. In the upper right corner of the page click on

    Settings

  7. Toggle standard SQL on or off.
  8. Before your settings are updated, you will need to re-upload your service account's P12 key. Learn more about BigQuery service accounts and P12 keys.
  9. Click

    Save Changes

    .

IMPORTANT

: Take care when changing the default SQL dialect for an existing connection, as any queries written using that connection that are written in the previous default SQL dialect may no longer work.

Connecting to Mode
  1. Return to Mode, and begin the process to connect a BigQuery database. When you see the form to enter your BigQuery credentials, first enter a name for the new database connection.
  2. In the

    Project ID

    field, enter the BigQuery project ID. It can be found in the URL of your Google Developers Console. The URL should be structured like this: https://console.developers.google.com/apis/library?project=MY_PROJECT_ID
  3. In the

    Service Account Email Address

    field, add your service account's email address that you noted in step 4.
  4. Under the

    Key

    field, click

    Choose File

    and select the private key file you downloaded in step 3.
  5. Click Connect to complete the connection.
Use BigQuery to query Google Sheets

You can use Mode to query Google Sheets in BigQuery. The first step, enabling your Google Drive API, only needs to be done once.

  1. Enable Google Drive API

    Navigate to the Google Developer Console and select your project from the project dropdown menu. From the left menu, click on Library. Under Google Apps API, click on Drive API to enable the API from that page.

  2. Create a table in BigQuery from your Google Sheet

    Go to the BigQuery Web UI. In the navigation, hover over a schema name and click the down arrow icon next to the name. Choose Create new table.

    GoogleSheets

    Select Google Drive for the new table's location and paste the Google Sheet URL (not the shared link but the actual URL you use to view the Sheet) into this field. Choose Google Sheets as the Format. Then add all the column names and types from your Google Sheet under Schema. Click Create Table when ready.

    GoogleSheets

  3. Enable Google Sheets for Mode Use

    • For Mode to query your new table, share your Google Sheet with the service account email address you used to connect Mode to BigQuery. You can also share folders in your drive with this email address, which will allow Mode to query every sheet in that folder.
    • To get your service account email address, go to your Google Cloud Console, switch to your Project, and click on Service Accounts. Copy the service account email address you used to connect Mode to BigQuery.
    • To share a sheet or folder, click Share on the Google Sheet or in the folder, and paste the service account email address into the Share email field.
    • Note that BigQuery schemas don't refresh automatically in Mode. To see this table appear in your schema browser, go to Workspace Settings and click

      Connections

      under the Data section. Choose your Big Query connection, and click the green

      Refresh

      button in the upper right corner to update the schema browser in Mode.

Hive

Supported JDBC drivers and known issues
DriverDriver Release DateMode Deprecation Date
(Recommended) Simba v 2.6.14.101712/3/2020
Simba v 2.6.12.10159/4/2020
(Legacy) Apache Hive v 2.3.211/9/20176/8/2021
(Legacy) Apache Hive v 2.2.07/25/20176/8/2021
(Legacy) Apache Hive v 2.1.06/17/20166/8/2021
(Legacy) Apache Hive v 1.2.24/3/20176/8/2021
(Legacy) Apache Hive v 0.13.16/2/20146/8/2021

The following are known issues with the current supported drivers. Mode continues to advocate on our customers’ behalf for vendors & open source communities to resolve these issues.

DriverKnown IssueFurther Details
Simba v 2.6.14.1017Deprecated VersionsThis driver removes support for Hive versions earlier than 1.0.0.

Impala

Supported JDBC drivers and known issues
DriverDriver Release DateMode Deprecation Date
(Recommended) Simba v 2.6.21.102512/31/2020
Simba v 2.6.18.10219/2/2020
Hive v 0.13.1 (Impala 2.0 - 2.1)4/3/20178/2/2021
Hive v 1.1.0 (Impala 2.2 - 2.6)6/2/20148/2/2021

Mode recommends connecting via the Simba Impala driver because it is the official driver endorsed by Cloudera.

Previously Mode’s connections flow messaged drivers as versions of Impala even though Mode connected to Impala using Hive drivers according to a compatibility matrix. The Impala server versions are noted in the table above as parenthetical notes.

DriverKnown IssueFurther Details
Hive v 1.1.0, 0.13.1Mode Schema Refresh PerformanceSchema refreshes in Mode are less performant (5-10x slower) when Impala is connected via a Hive driver because the JDBC API is incompletely implemented in these drivers.
Simba v 2.6.18.1021Leading white space in QueriesWhen attempting to run a query that begins with a space, the driver will fail to execute the query and will return an empty result set with an error. In the Mode UI, users will see “Sorry, your query was successful, but we ran into problems displaying the data.”

Microsoft SQL Server

Additionally supports:

Azure SQL Database, Azure Synapse SQL

Supported JDBC drivers and known issues
DriverDriver Release DateMode Deprecation Date
Microsoft v 9.2.01/29/2021
Microsoft v 8.2.23/24/2020

There are no known issues for this driver.

Database configuration instructions
Azure SQL Database

To connect your Microsoft Azure SQL instance you must grant Mode access to your database.

  1. Log into your Azure account and select

    SQL Databases

    from the left navigation.
  2. Select the SQL database you would like to connect. Azure SQL
  3. Click the Dashboard link at the top of the page and click

    Manage Allowed IP Addresses

    . Azure SQL
  4. Add a new rule for Mode's IP address using the following information:

    • Rule Name: Mode 1
    • Start IP Address: 54.68.30.98
    • End IP Address: 54.68.30.98 Azure SQL
  5. Add a rule for the remaining three Mode IP addresses:

    • 54.68.45.3
    • 54.164.204.122
    • 54.172.100.146

    Azure SQL

  6. Click

    Save

    at the bottom of the screen.

    Azure SQL

MySQL

Additionally supports:

Amazon Aurora MySQL, MariaDB

Supported JDBC drivers and known issues
DriverDriver Release DateMode Deprecation Date
(Recommended) MySQL Connector/J 8.0.231/18/2021
MySQL Connector/J 8.0.204/27/2020
(Legacy) MySQL Connector/J 5.1.487/29/20194/5/2021

The following are known issues with the current supported drivers. Mode continues to advocate on our customers’ behalf for vendors & open source communities to resolve these issues.

DriverKnown IssueFurther Details
MySQL Connector/J 8.0.20
Server Timezone Specification
is Requred
Customers without a specified serverTimezone
for their databases will see queries fail if any of the time zones (application/database servers) are not in the format UTC+xx or GMT+xx.

Queries will fail with the following error: java.sql.SQLException: The server time zone value 'PDT' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the 'serverTimezone' configuration property) to use a more specifc time zone value if you want to utilize time zone support.

Note that the error is directly from the JDBC driver

. At this time Mode does not support customers setting additional configuration properties (such as the suggested 'serverTimezone' property) via the JDBC driver.

More details in this StackOverflow thread.

Oracle

Supported JDBC drivers and known issues
DriverDriver Release DateMode Deprecation Date
Oracle v 12.1.0.210/30/2017
(Recommended) Oracle v 19.74/1/2019

There are no known issues for these drivers.

PostgreSQL

Additionally supports:

Amazon Aurora PostgreSQL, HeapSQL, Timescale, Citus

Supported JDBC drivers and known issues
DriverDriver Release DateMode Deprecation Date
(Recommended) PostgreSQL v 42.2.192/18/2021
PostgreSQL v 42.2.146/4/2020

There are no known issues with this driver.

Database configuration instructions
HeapSQL

Once you’ve signed up for Heap and enabled Heap SQL, your Customer Success Manager will provide you with your Heap credentials for connecting to your database. If you need your Heap credentials, please email support@heapanalytics.com for help.

Once you have your credentials, follow the steps below to connect Mode:

  1. Log in to Mode and follow to steps to connect a database to Mode.
  2. Select Heap from the list of databases.
  3. Enter your Heap credentials and click Connect. You'll land on a page that displays your database schema.
  4. Click “New Query” in the top navigation to start writing queries!

Snowflake

Supported JDBC drivers and known issues
DriverDriver Release DateMode Deprecation Date
(Recommended) Snowflake v 3.13.13/1/2021
Snowflake v 3.12.86/22/2020

There are no known issues with this driver.

Trino

Additionally supports:

Starburst, PrestoDB, Terradata Presto

Supported JDBC drivers and known issues
DriverDriver Release DateMode Deprecation Date
(Recommended) Trino v 35012/18/2021
(Recommended) Simba v 1.1.9.100910/2/2020
Simba v 1.1.18.10088/21/2020
PrestoSQL v 3388/31/2020
PrestoSQL v 3135/31/20198/2/2021
PrestoDB v 0.2088/8/20188/2/2021
PrestoDB v 0.1816/19/20178/2/2021
PrestoDB v 0.1475/6/20168/2/2021
PrestoDB v 0.1086/24/20158/2/2021

Mode recommends connecting via the Simba Presto driver because of the broad support it provides across the open-source projects. Simba v 1.1.8.1008 includes support for open-source Presto Server (PrestoDB) versions 0.235 and earlier, Teradata Presto Server versions 0.208-t and earlier, and Presto Foundation Server (PrestoSQL) 335 and earlier.

Simba 1.1.9.1009 includes support for open-source Presto Server (PrestoDB) versions 0.240 and earlier, Teradata Presto Server versions 0.208-t and earlier, and Presto Foundation Server (PrestoSQL) 341 and earlier.

Mode also recommends connecting via the latest PrestoSQL driver that has been designated by Starbust as a long-term support version (currently PrestoSQL v 338).

The following are known issues with the current supported drivers. Mode continues to advocate on our customers’ behalf for vendors & open source communities to resolve these issues.

DriverKnown IssueFurther Details
Simba v 1.1.18.1008Timestamp with Timezone datatype results in query failuresWhen running PrestoSQL server version 340, all queries against tables with a column of type timestamp with timezone fail with a Null Pointer Exception.

Notably, this occurs regardless of whether the column is populated with data and regardless of whether the column is referenced in the query.
Simba v 1.1.18.1008, Simba v 1.1.9.1009Time with Timezone as StringThe driver casts columns of type “time with timezone” as “string" instead of “time” types when returning data to Mode.
PrestoDB v 0.147No support for Query CancellationPresto didn’t implement the ability to cancel queries via JDBC drivers until mid-2017.
PrestoDB v 0.108No support for Query CancellationPresto didn’t implement the ability to cancel queries via JDBC drivers until mid-2017.

Treasure Data

Supported JDBC drivers and known issues
DriverDriver Release DateMode Deprecation Date
PrestoDB v 0.2088/8/2018

There are no known issues with this driver.

Database configuration instructions

Please follow the steps outlined in the Treasure Data documentation to connect Treasure Data to Mode.

If you need help finding your database credentials, please contact your Treasure Data account manager at info@treasure-data.com.

Vertica

Supported JDBC drivers and known issues
DriverDriver Release DateMode Deprecation Date
Vertica v 9.3.0-010/14/2010
(Recommended) Vertica v 10.0.0-08/19/2020

There are no known issues with this driver.

Cloud environments

Amazon Web Services

Overview

We have two ways of connecting to your RDS instance depending on your AWS settings. If your instance is publicly accessible then Mode can connect directly to it. For databases that are not accessible you can use the Bridge connector. We'll start by determining if your database is accessible to us.

Accessibility
  1. Navigate to your RDS Instance Console.
  2. Click the arrow and then magnifying glass view the details of the instance that you want to connect

    Amazon RDS Console

  3. Look under "Security and Network". Your instance is in a VPC if there's a entry labeled VPC. It is typically found below Availability Zone and above Security Groups. If you don't have an entry here then your instance is not in a VPC and you can jump to the "Security Groups" section below.

    RDS with a VPC

  4. Instances in VPCs can be public or private. We can find out by looking under "Security and Network" again. This time for an entry labeled Publicly Accessible. If it is followed by a Yes then you can jump to the "Security Groups" section below.

    RDS on a public VPC

  5. To connect to instances in a private VPC you'll need to install our Bridge connector.
Security Groups

All connections from Mode will come from one of the four IP addresses below. In most cases you'll need to add these addresses to your RDS instance's Security Group. We've broken the steps down into two sections: VPC and No VPC. Security Groups in a VPC are managed the same between EC2 and RDS. RDS instances outside of a VPC have a different process.

  • 54.68.30.98/32
  • 54.68.45.3/32
  • 54.164.204.122/32
  • 54.172.100.146/32
VPC
  1. Under "Security and Network" click the security group name.

    Amazon RDS

  2. A new tab will be opened. Click on the "Inbound" tab and then "Edit".

    Amazon RDS

  3. Find the RDS type that matches your instance (e.g. PostgreSQL), enter each of our addresses and then click "Save".

    Amazon RDS

No VPC
  1. Under "Security and Network" click the security group to which that database belongs.

    Amazon RDS

  2. At the bottom of the page, highlight the "Connection Type" selector and choose "CIDR/IP"

    Amazon RDS

  3. In the "CIDR/IP to Authorize" field paste address: 54.68.30.98/32

    Amazon RDS

  4. Click the blue "Authorize" button
  5. Repeat steps 1-4 for each of the addresses listed above.

Was this article helpful?

decorative particle

Get our weekly data newsletter

Work-related distractions for every data enthusiast.