Databases

Amazon Athena

  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

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

Amplitude Redshift

Connecting to an Amplitude Redshift instance is very similar to connected to a generic Redshift database.

Once you have an Enterprise-level account with Amplitude, your Customer Success Manager will provide you with your Redshift credentials for logging into your database. If you need your credentials, you can email support@amplitude.com.

Google BigQuery

Create a service account

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

    IMPORTANT: We suggest adding only the BigQuery Data Viewer and BigQuery Job User roles to your service account. Adding other roles to your service account may prevent Mode from being able to connect.

  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.

An organization 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 Organization 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.

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 Organization 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.

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!

Microsoft Azure SQL

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.

    Azure SQL

  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

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.

Treasure Data

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.

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 the addresses listed above.

Last updated May 17, 2018